Java by API/java.sql/PreparedStatement — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 17:46, 31 мая 2010
Содержание
- 1 PreparedStatement: addBatch()
- 2 PreparedStatement: executeUpdate()
- 3 PreparedStatement: getParameterMetaData()
- 4 PreparedStatement: getWarnings()
- 5 PreparedStatement: setAsciiStream(int parameterIndex, InputStream x, int length)
- 6 PreparedStatement: setBigDecimal(int parameterIndex, BigDecimal x)
- 7 PreparedStatement: setBinaryStream(int parameterIndex, InputStream x, int length)
- 8 PreparedStatement: setBoolean(int parameterIndex, boolean x)
- 9 PreparedStatement: setByte(int parameterIndex, byte x)
- 10 PreparedStatement: setCharacterStream(int parameterIndex, Reader reader, int length)
- 11 PreparedStatement: setClob(int parameterIndex, Clob x)
- 12 PreparedStatement: setDate(int parameterIndex, Date x)
- 13 PreparedStatement: setDouble(int parameterIndex, double x)
- 14 PreparedStatement: setFetchSize(int rows)
- 15 PreparedStatement: setFloat(int parameterIndex, float x)
- 16 PreparedStatement: setInt(int parameterIndex, int x)
- 17 PreparedStatement: setLong(int parameterIndex, long x)
- 18 PreparedStatement: setNull(int parameterIndex, int sqlType)
- 19 PreparedStatement: setObject(int parameterIndex, Object x)
- 20 PreparedStatement: setRef(int parameterIndex, Ref x)
- 21 PreparedStatement: setShort(int parameterIndex, short x)
- 22 PreparedStatement: setString(int parameterIndex, String x)
- 23 PreparedStatement: setTime(int parameterIndex, Time x)
- 24 PreparedStatement: setTimestamp(int parameterIndex, Timestamp x)
- 25 PreparedStatement: setURL(int parameterIndex, URL x)
PreparedStatement: addBatch()
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class MainClass {
public static void main(String[] args) { Connection connection = null; PreparedStatement statement = null; try { Class.forName("com.mysql.jdbc.Driver").newInstance(); String url = "jdbc:mysql://localhost/database"; connection = DriverManager.getConnection(url, "username", "password"); String sql = "UPDATE employees SET email = ? WHERE employee_id = ?"; statement = connection.prepareStatement(sql); statement.setString(1, "a@a.ru"); statement.setLong(2, 1); statement.addBatch(); statement.setString(1, "b@b.ru"); statement.setLong(2, 2); statement.addBatch(); statement.setString(1, "c@c.ru"); statement.setLong(2, 3); statement.addBatch(); statement.executeBatch(); } catch (Exception e) { e.printStackTrace(); } finally { if (statement != null) { try { statement.close(); } catch (SQLException e) { } // nothing we can do } if (connection != null) { try { connection.close(); } catch (SQLException e) { } // nothing we can do } } }
}
</source>
PreparedStatement: executeUpdate()
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class Main {
public static void main(String[] args) throws Exception { try { String url = "jdbc:odbc:databaseName"; String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; String user = "guest"; String password = "guest"; Class.forName(driver); Connection connection = DriverManager.getConnection(url,user, password); String changeLastName = "UPDATE authors SET lastname = ? WHERE authid = ?"; PreparedStatement updateLastName = connection.prepareStatement(changeLastName); updateLastName.setString(1, "Martin"); // Set lastname placeholder value updateLastName.setInt(2, 4); // Set author ID placeholder value int rowsUpdated = updateLastName.executeUpdate(); // execute the update System.out.println("Rows affected: " + rowsUpdated); connection.close(); } catch (ClassNotFoundException cnfe) { System.err.println(cnfe); } catch (SQLException sqle) { System.err.println(sqle); } }
}
</source>
PreparedStatement: getParameterMetaData()
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ParameterMetaData; import java.sql.PreparedStatement; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getHSQLConnection(); Statement st = conn.createStatement(); st.executeUpdate("create table survey (id int,name varchar);"); st.executeUpdate("create view surveyView as (select * from survey);"); st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")"); String query = "select * from survey where id > ? and name = ?"; PreparedStatement pstmt = conn.prepareStatement(query); ParameterMetaData paramMetaData = pstmt.getParameterMetaData(); if (paramMetaData == null) { System.out.println("db vendor does NOT support ParameterMetaData"); } else { System.out.println("db vendor supports ParameterMetaData"); // find out the number of dynamic parameters int paramCount = paramMetaData.getParameterCount(); System.out.println("paramCount=" + paramCount); System.out.println("-------------------"); for (int param = 1; param <= paramCount; param++) { System.out.println("param number=" + param); String paramTypeName = paramMetaData.getParameterTypeName(param); System.out.println("param SQL type name=" + paramTypeName); } } pstmt.close(); conn.close(); } private static Connection getHSQLConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); System.out.println("Driver Loaded."); String url = "jdbc:hsqldb:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); } public static Connection getMySqlConnection() throws Exception { String driver = "org.gjt.mm.mysql.Driver"; String url = "jdbc:mysql://localhost/demo2s"; String username = "oost"; String password = "oost"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); return conn; } public static Connection getOracleConnection() throws Exception { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@localhost:1521:caspian"; String username = "mp"; String password = "mp2"; Class.forName(driver); // load Oracle driver Connection conn = DriverManager.getConnection(url, username, password); return conn; }
}
</source>
PreparedStatement: getWarnings()
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLWarning; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getConnection(); conn.setAutoCommit(false); Statement st = conn.createStatement(); st.executeUpdate("create table survey (id int, name VARCHAR(30) );"); String INSERT_RECORD = "insert into survey(id, name) values(?,?)"; PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD); pstmt.setInt(1, 1); pstmt.setString(2, "name"); pstmt.executeUpdate(); // Get warnings on PreparedStatement object SQLWarning warning = pstmt.getWarnings(); while (warning != null) { // Process statement warnings... String message = warning.getMessage(); String sqlState = warning.getSQLState(); int errorCode = warning.getErrorCode(); warning = warning.getNextWarning(); } ResultSet rs = st.executeQuery("SELECT * FROM survey"); outputResultSet(rs); rs.close(); st.close(); conn.close(); } private static void outputResultSet(ResultSet rs) throws Exception { ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); for (int i = 1; i < numberOfColumns + 1; i++) { String columnName = rsMetaData.getColumnName(i); System.out.print(columnName + " "); } System.out.println(); System.out.println("----------------------"); while (rs.next()) { for (int i = 1; i < numberOfColumns + 1; i++) { System.out.print(rs.getString(i) + " "); } System.out.println(); } } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}
</source>
PreparedStatement: setAsciiStream(int parameterIndex, InputStream x, int length)
<source lang="java">
import java.io.FileInputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { try { String url = "jdbc:odbc:databaseName"; String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; String user = "guest"; String password = "guest"; FileInputStream fis = new FileInputStream("somefile.txt"); Class.forName(driver); Connection connection = DriverManager.getConnection(url, user, password); Statement createTable = connection.createStatement(); createTable.executeUpdate("CREATE TABLE source_code (name CHAR(20), source LONGTEXT)"); String ins = "INSERT INTO source_code VALUES(?,?)"; PreparedStatement statement = connection.prepareStatement(ins); statement.setString(1, "TryInputStream"); // Set first field statement.setAsciiStream(2, fis, fis.available()); // Stream is source int rowsUpdated = statement.executeUpdate(); System.out.println("Rows affected: " + rowsUpdated); connection.close(); } catch (Exception e) { System.err.println(e); } }
}
</source>
PreparedStatement: setBigDecimal(int parameterIndex, BigDecimal x)
<source lang="java">
import java.math.BigDecimal; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getConnection(); Statement stmt = conn.createStatement();
stmt.executeUpdate("create table survey (id DECIMAL, name BINARY );"); String sql = "INSERT INTO survey (id) VALUES(?)"; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setBigDecimal(1, new BigDecimal("1.00000")); // insert the data pstmt.executeUpdate(); ResultSet rs = stmt.executeQuery("SELECT * FROM survey"); while (rs.next()) { System.out.print(rs.getString(1)); } rs.close(); stmt.close(); conn.close(); } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}
</source>
PreparedStatement: setBinaryStream(int parameterIndex, InputStream x, int length)
<source lang="java">
import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getConnection(); Statement stmt = conn.createStatement();
stmt.executeUpdate("create table survey (id int, name BINARY );"); String sql = "INSERT INTO survey (name) VALUES(?)"; PreparedStatement pstmt = conn.prepareStatement(sql); // prepare small binary stream File smallFile = new File("yourFileName.txt"); int smallFileLength = (int) smallFile.length(); InputStream smallStream = (InputStream) new FileInputStream(smallFile); pstmt.setBinaryStream(2, smallStream, smallFileLength); // insert the data pstmt.executeUpdate(); ResultSet rs = stmt.executeQuery("SELECT * FROM survey"); while (rs.next()) { System.out.print(rs.getString(1)); } rs.close(); stmt.close(); conn.close(); } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}
</source>
PreparedStatement: setBoolean(int parameterIndex, boolean x)
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getConnection(); Statement stmt = conn.createStatement(); stmt.executeUpdate("create table survey (id int, register int );"); String sql = "INSERT INTO survey (register) VALUES(?)"; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setBoolean(1, true); pstmt.executeUpdate(); pstmt.setBoolean(1, false); pstmt.executeUpdate(); ResultSet rs = stmt.executeQuery("SELECT * FROM survey"); while (rs.next()) { System.out.println(rs.getString(2)); } rs.close(); stmt.close(); conn.close(); } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}
</source>
PreparedStatement: setByte(int parameterIndex, byte x)
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getConnection(); Statement stmt = conn.createStatement(); stmt.executeUpdate("create table survey (id int, id2 tinyint, id3 smallint, id4 bigint, id5 real);"); String sql = "INSERT INTO survey (id2,id3,id4,id5) VALUES(?,?,?,?)"; PreparedStatement pstmt = conn.prepareStatement(sql); byte b = 1; short s = 2; pstmt.setByte(1, b); pstmt.setShort(2, s); pstmt.setInt(3, 3); pstmt.setLong(4, 4L); pstmt.executeUpdate(); ResultSet rs = stmt.executeQuery("SELECT * FROM survey"); while (rs.next()) { System.out.println(rs.getString(2)); } rs.close(); stmt.close(); conn.close(); } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}
</source>
PreparedStatement: setCharacterStream(int parameterIndex, Reader reader, int length)
<source lang="java">
import java.io.BufferedReader; import java.io.File; import java.io.FileReader; import java.io.Reader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getConnection(); Statement stmt = conn.createStatement(); stmt.executeUpdate("create table survey (id int, name BINARY);"); String sql = "INSERT INTO survey (name) VALUES(?)"; PreparedStatement pstmt = conn.prepareStatement(sql); File file = new File("yourFileName.txt"); long fileLength = file.length(); Reader fileReader = (Reader) new BufferedReader(new FileReader(file)); pstmt.setCharacterStream(1, fileReader, (int)fileLength); int rowCount = pstmt.executeUpdate(); ResultSet rs = stmt.executeQuery("SELECT * FROM survey"); while (rs.next()) { System.out.println(rs.getBytes(2)); } rs.close(); stmt.close(); conn.close(); } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}
</source>
PreparedStatement: setClob(int parameterIndex, Clob x)
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class DemoPreparedStatementSetClob {
public static Connection getConnection() throws Exception { String driver = "org.gjt.mm.mysql.Driver"; String url = "jdbc:mysql://localhost/databaseName"; String username = "root"; String password = "root"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); return conn; } public static void main(String[] args)throws Exception { String id = "0001"; String newID = "0002"; ResultSet rs = null; Connection conn = null; PreparedStatement pstmt = null; try { conn = getConnection(); // begin transaction conn.setAutoCommit(false); String query1 = "select clob_column from clob_table where id = ?"; pstmt = conn.prepareStatement(query1); pstmt.setString(1, id); rs = pstmt.executeQuery(); rs.next(); java.sql.Clob clob = (java.sql.Clob) rs.getObject(1); String query = "insert into clob_table(id, clob_column) values(?, ?)"; pstmt = conn.prepareStatement(query); pstmt.setString(1, newID); pstmt.setClob(2, clob); int rowCount = pstmt.executeUpdate(); System.out.println("rowCount=" + rowCount); conn.rumit(); } finally { rs.close(); pstmt.close(); conn.close(); } }
}
</source>
PreparedStatement: setDate(int parameterIndex, Date x)
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class Main {
public static java.sql.Date getCurrentJavaSqlDate() { java.util.Date today = new java.util.Date(); return new java.sql.Date(today.getTime()); } public static Connection getConnection() throws Exception { String driver = "org.gjt.mm.mysql.Driver"; String url = "jdbc:mysql://localhost/databaseName"; String username = "root"; String password = "root"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); return conn; } public static void main(String[] args) throws Exception { Connection conn = null; PreparedStatement pstmt = null; try { conn = getConnection(); String query = "insert into date_table(id, date_column) values(?, ?)"; pstmt = conn.prepareStatement(query); pstmt.setString(1, "0001"); java.sql.Date date = getCurrentJavaSqlDate(); pstmt.setDate(2, date); // execute query, and return number of rows created int rowCount = pstmt.executeUpdate(); System.out.println("rowCount=" + rowCount); } finally { pstmt.close(); conn.close(); } }
}
</source>
PreparedStatement: setDouble(int parameterIndex, double x)
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class Main {
public static Connection getConnection() throws Exception { String driver = "org.gjt.mm.mysql.Driver"; String url = "jdbc:mysql://localhost/databaseName"; String username = "root"; String password = "root"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); return conn; } public static void main(String[] args) throws Exception { String id = "0001"; float floatValue = 0001f; double doubleValue = 1.0001d; Connection conn = null; PreparedStatement pstmt = null; try { conn = getConnection(); String query = "insert into double_table(id, float_column, double_column) values(?, ?, ?)"; pstmt = conn.prepareStatement(query); pstmt.setString(1, id); pstmt.setFloat(2, floatValue); pstmt.setDouble(3, doubleValue); // execute query, and return number of rows created int rowCount = pstmt.executeUpdate(); System.out.println("rowCount=" + rowCount); } finally { pstmt.close(); conn.close(); } }
}
</source>
PreparedStatement: setFetchSize(int rows)
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLWarning; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getConnection(); conn.setAutoCommit(false); Statement st = conn.createStatement(); st.executeUpdate("create table survey (id int, name VARCHAR(30) );"); String INSERT_RECORD = "select * from survey where id < ?"; PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD); pstmt.setInt(1, 1); pstmt.setFetchSize(200); ResultSet rs = pstmt.executeQuery(); outputResultSet(rs); rs.close(); st.close(); conn.close(); } private static void outputResultSet(ResultSet rs) throws Exception { ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); for (int i = 1; i < numberOfColumns + 1; i++) { String columnName = rsMetaData.getColumnName(i); System.out.print(columnName + " "); } System.out.println(); System.out.println("----------------------"); while (rs.next()) { for (int i = 1; i < numberOfColumns + 1; i++) { System.out.print(rs.getString(i) + " "); } System.out.println(); } } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}
</source>
PreparedStatement: setFloat(int parameterIndex, float x)
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class Main {
public static Connection getConnection() throws Exception { String driver = "org.gjt.mm.mysql.Driver"; String url = "jdbc:mysql://localhost/databaseName"; String username = "root"; String password = "root"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); return conn; } public static void main(String[] args) throws Exception { String id = "0001"; float floatValue = 0001f; double doubleValue = 1.0001d; Connection conn = null; PreparedStatement pstmt = null; try { conn = getConnection(); String query = "insert into double_table(id, float_column, double_column) values(?, ?, ?)"; pstmt = conn.prepareStatement(query); pstmt.setString(1, id); pstmt.setFloat(2, floatValue); pstmt.setDouble(3, doubleValue); // execute query, and return number of rows created int rowCount = pstmt.executeUpdate(); System.out.println("rowCount=" + rowCount); } finally { pstmt.close(); conn.close(); } }
}
</source>
PreparedStatement: setInt(int parameterIndex, int x)
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class Main {
public static void main(String[] args) throws Exception { try { String url = "jdbc:odbc:databaseName"; String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; String user = "guest"; String password = "guest"; Class.forName(driver); Connection connection = DriverManager.getConnection(url,user, password); String changeLastName = "UPDATE authors SET lastname = ? WHERE authid = ?"; PreparedStatement updateLastName = connection.prepareStatement(changeLastName); updateLastName.setString(1, "Martin"); // Set lastname placeholder value updateLastName.setInt(2, 4); // Set author ID placeholder value int rowsUpdated = updateLastName.executeUpdate(); // execute the update System.out.println("Rows affected: " + rowsUpdated); connection.close(); } catch (ClassNotFoundException cnfe) { System.err.println(cnfe); } catch (SQLException sqle) { System.err.println(sqle); } }
}
</source>
PreparedStatement: setLong(int parameterIndex, long x)
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getConnection(); Statement stmt = conn.createStatement(); stmt.executeUpdate("create table survey (id int, id2 tinyint, id3 smallint, id4 bigint, id5 real);"); String sql = "INSERT INTO survey (id2,id3,id4,id5) VALUES(?,?,?,?)"; PreparedStatement pstmt = conn.prepareStatement(sql); byte b = 1; short s = 2; pstmt.setByte(1, b); pstmt.setShort(2, s); pstmt.setInt(3, 3); pstmt.setLong(4, 4L); pstmt.executeUpdate(); ResultSet rs = stmt.executeQuery("SELECT * FROM survey"); while (rs.next()) { System.out.println(rs.getString(2)); } rs.close(); stmt.close(); conn.close(); } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}
</source>
PreparedStatement: setNull(int parameterIndex, int sqlType)
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getConnection(); Statement st = conn .createStatement(); st.executeUpdate("create table survey (id int,myDate DATE);"); String INSERT_RECORD = "insert into survey(id, myDate) values(?, ?)"; PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD); pstmt.setString(1, "1"); pstmt.setNull(2, java.sql.Types.DATE); pstmt.executeUpdate(); ResultSet rs = st.executeQuery("SELECT * FROM survey"); outputResultSet(rs); rs.close(); st.close(); conn.close(); } private static void outputResultSet(ResultSet rs) throws Exception { ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); for (int i = 1; i < numberOfColumns + 1; i++) { String columnName = rsMetaData.getColumnName(i); System.out.print(columnName + " "); } System.out.println(); System.out.println("----------------------"); while (rs.next()) { for (int i = 1; i < numberOfColumns + 1; i++) { System.out.print(rs.getString(i) + " "); } System.out.println(); } } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}
</source>
PreparedStatement: setObject(int parameterIndex, Object x)
<source lang="java">
import java.sql.Clob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Timestamp; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getOracleConnection(); String[] columnNames = { "id", "name", "content", "date_created" }; Object[] inputValues = new Object[columnNames.length]; inputValues[0] = new java.math.BigDecimal(100); inputValues[1] = new String("String Value"); inputValues[2] = new String("This is my resume."); inputValues[3] = new Timestamp((new java.util.Date()).getTime()); String insert = "insert into resume (id, name, content, date_created ) values(?, ?, ?, ?)"; PreparedStatement pstmt = conn.prepareStatement(insert); pstmt.setObject(1, inputValues[0]); pstmt.setObject(2, inputValues[1]); pstmt.setObject(3, inputValues[2]); pstmt.setObject(4, inputValues[3]); pstmt.executeUpdate(); String query = "select id, name, content, date_created from resume where id=?"; PreparedStatement pstmt2 = conn.prepareStatement(query); pstmt2.setObject(1, inputValues[0]); ResultSet rs = pstmt2.executeQuery(); Object[] outputValues = new Object[columnNames.length]; if (rs.next()) { for (int i = 0; i < columnNames.length; i++) { outputValues[i] = rs.getObject(i + 1); } } System.out.println("id=" + ((java.math.BigDecimal) outputValues[0]).toString()); System.out.println("name=" + ((String) outputValues[1])); System.out.println("content=" + ((Clob) outputValues[2])); System.out.println("date_created=" + ((java.sql.Date) outputValues[3]).toString()); rs.close(); pstmt.close(); pstmt2.close(); conn.close(); } private static Connection getHSQLConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); System.out.println("Driver Loaded."); String url = "jdbc:hsqldb:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); } public static Connection getMySqlConnection() throws Exception { String driver = "org.gjt.mm.mysql.Driver"; String url = "jdbc:mysql://localhost/demo2s"; String username = "oost"; String password = "oost"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); return conn; } public static Connection getOracleConnection() throws Exception { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@localhost:1521:databaseName"; String username = "userName"; String password = "password"; Class.forName(driver); // load Oracle driver Connection conn = DriverManager.getConnection(url, username, password); return conn; }
}
</source>
PreparedStatement: setRef(int parameterIndex, Ref x)
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class Main {
public static Connection getConnection() throws Exception { String driver = "org.gjt.mm.mysql.Driver"; String url = "jdbc:mysql://localhost/databaseName"; String username = "root"; String password = "root"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); return conn; } public static void main(String[] args) throws Exception { String deptName = "oldName"; String newDeptName = "newName"; ResultSet rs = null; Connection conn = null; PreparedStatement pstmt = null; PreparedStatement pstmt2 = null; try { conn = getConnection(); // prepare query for getting a REF object and PrepareStatement object String refQuery = "select manager from dept_table where dept_name=?"; pstmt = conn.prepareStatement(refQuery); pstmt.setString(1, deptName); rs = pstmt.executeQuery(); java.sql.Ref ref = null; if (rs.next()) { ref = rs.getRef(1); } if (ref == null) { System.out.println("error: could not get a reference for manager."); System.exit(1); } String query = "INSERT INTO dept_table(dept_name, manager)values(?, ?)"; pstmt2 = conn.prepareStatement(query); pstmt2.setString(1, newDeptName); pstmt2.setRef(2, ref); // execute query, and return number of rows created int rowCount = pstmt2.executeUpdate(); System.out.println("rowCount=" + rowCount); } finally { pstmt.close(); pstmt2.close(); conn.close(); } }
}
</source>
PreparedStatement: setShort(int parameterIndex, short x)
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getConnection(); Statement stmt = conn.createStatement(); stmt.executeUpdate("create table survey (id int, id2 tinyint, id3 smallint, id4 bigint, id5 real);"); String sql = "INSERT INTO survey (id2,id3,id4,id5) VALUES(?,?,?,?)"; PreparedStatement pstmt = conn.prepareStatement(sql); byte b = 1; short s = 2; pstmt.setByte(1, b); pstmt.setShort(2, s); pstmt.setInt(3, 3); pstmt.setLong(4, 4L); pstmt.executeUpdate(); ResultSet rs = stmt.executeQuery("SELECT * FROM survey"); while (rs.next()) { System.out.println(rs.getString(2)); } rs.close(); stmt.close(); conn.close(); } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}
</source>
PreparedStatement: setString(int parameterIndex, String x)
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class Main {
public static void main(String[] args) throws Exception { try { String url = "jdbc:odbc:databaseName"; String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; String user = "guest"; String password = "guest"; Class.forName(driver); Connection connection = DriverManager.getConnection(url,user, password); String changeLastName = "UPDATE authors SET lastname = ? WHERE authid = ?"; PreparedStatement updateLastName = connection.prepareStatement(changeLastName); updateLastName.setString(1, "Martin"); // Set lastname placeholder value updateLastName.setInt(2, 4); // Set author ID placeholder value int rowsUpdated = updateLastName.executeUpdate(); // execute the update System.out.println("Rows affected: " + rowsUpdated); connection.close(); } catch (ClassNotFoundException cnfe) { System.err.println(cnfe); } catch (SQLException sqle) { System.err.println(sqle); } }
}
</source>
PreparedStatement: setTime(int parameterIndex, Time x)
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetTimeAndTimestamp {
public static Connection getConnection() throws Exception { String driver = "org.gjt.mm.mysql.Driver"; String url = "jdbc:mysql://localhost/databaseName"; String username = "root"; String password = "root"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); return conn; } public static java.sql.Timestamp getCurrentJavaSqlTimestamp() { java.util.Date date = new java.util.Date(); return new java.sql.Timestamp(date.getTime()); } public static java.sql.Time getCurrentJavaSqlTime() { java.util.Date date = new java.util.Date(); return new java.sql.Time(date.getTime()); } public static void main(String[] args) throws Exception { String id = "0001"; Connection conn = null; PreparedStatement pstmt = null; try { conn = getConnection(); String query = "insert into time_table(id,time_column, timestamp_column) values(?, ?, ?)"; pstmt = conn.prepareStatement(query); pstmt.setString(1, id); java.sql.Time time = getCurrentJavaSqlTime(); System.out.println("time=" + time); pstmt.setTime(2, time); java.sql.Timestamp timestamp = getCurrentJavaSqlTimestamp(); System.out.println("timestamp=" + timestamp); pstmt.setTimestamp(3, timestamp); // execute query, and return number of rows created int rowCount = pstmt.executeUpdate(); System.out.println("rowCount=" + rowCount); } finally { pstmt.close(); conn.close(); } }
}
</source>
PreparedStatement: setTimestamp(int parameterIndex, Timestamp x)
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetTimeAndTimestamp {
public static Connection getConnection() throws Exception { String driver = "org.gjt.mm.mysql.Driver"; String url = "jdbc:mysql://localhost/databaseName"; String username = "root"; String password = "root"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); return conn; } public static java.sql.Timestamp getCurrentJavaSqlTimestamp() { java.util.Date date = new java.util.Date(); return new java.sql.Timestamp(date.getTime()); } public static java.sql.Time getCurrentJavaSqlTime() { java.util.Date date = new java.util.Date(); return new java.sql.Time(date.getTime()); } public static void main(String[] args) throws Exception { String id = "0001"; Connection conn = null; PreparedStatement pstmt = null; try { conn = getConnection(); String query = "insert into time_table(id,time_column, timestamp_column) values(?, ?, ?)"; pstmt = conn.prepareStatement(query); pstmt.setString(1, id); java.sql.Time time = getCurrentJavaSqlTime(); System.out.println("time=" + time); pstmt.setTime(2, time); java.sql.Timestamp timestamp = getCurrentJavaSqlTimestamp(); System.out.println("timestamp=" + timestamp); pstmt.setTimestamp(3, timestamp); // execute query, and return number of rows created int rowCount = pstmt.executeUpdate(); System.out.println("rowCount=" + rowCount); } finally { pstmt.close(); conn.close(); } }
}
</source>
PreparedStatement: setURL(int parameterIndex, URL x)
<source lang="java">
import java.net.URL; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getConnection(); Statement st = conn .createStatement(); st.executeUpdate("create table survey (id int,myURL CHAR);"); String INSERT_RECORD = "insert into survey(id, myURL) values(?, ?)"; PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD); pstmt.setString(1, "1"); pstmt.setURL(2, new URL("http://www.jexp.ru")); pstmt.executeUpdate(); ResultSet rs = st.executeQuery("SELECT * FROM survey"); outputResultSet(rs); rs.close(); st.close(); conn.close(); } private static void outputResultSet(ResultSet rs) throws Exception { ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); for (int i = 1; i < numberOfColumns + 1; i++) { String columnName = rsMetaData.getColumnName(i); System.out.print(columnName + " "); } System.out.println(); System.out.println("----------------------"); while (rs.next()) { for (int i = 1; i < numberOfColumns + 1; i++) { System.out.print(rs.getString(i) + " "); } System.out.println(); } } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}
</source>