Java by API/java.sql/PreparedStatement

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>