Java/Database SQL JDBC/PreparedStatement

Материал из Java эксперт
Версия от 06:33, 1 июня 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Count Records Using PreparedStatement

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class CountRecordsUsingPreparedStatement {
  public static Connection getConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "name";
    String password = "password";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static void main(String[] args) {
    ResultSet rs = null;
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      String query = "select count(*) from tableName";
      pstmt = conn.prepareStatement(query);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        int numberOfRows = rs.getInt(1);
        System.out.println("numberOfRows= " + numberOfRows);
      } else {
        System.out.println("error: could not get the record counts");
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        rs.close();
        pstmt.close();
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}





Count Records using the Prepared Statement

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Main {
  public static void main(String[] argv) throws Exception {
    int records = 0;
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
        "root", "root");
    String sql = "SELECT COUNT(*) FROM mytable" ;
    PreparedStatement prest = con.prepareStatement(sql);
    ResultSet rs = prest.executeQuery();
    while (rs.next()) {
      records = rs.getInt(1);
    }
    System.out.println("Number of records: " + records);
    con.close();
  }
}





DELETE data in a table

  
import java.sql.Connection;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] argv) throws Exception {
    Connection con = null;
    PreparedStatement prepstmt;
    prepstmt = con.prepareStatement("DELETE FROM tCust " + " WHERE custId = ?");
    prepstmt.setString(1, "1");
    prepstmt.executeUpdate();
    prepstmt.close();
    con.close();
  }
}





Delete Records Using PreparedStatement

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DeleteRecordsUsingPreparedStatement {
  public static Connection getConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "name";
    String password = "password";
    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 = "delete from tableName";
      pstmt = conn.prepareStatement(query);
      pstmt.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      pstmt.close();
      conn.close();
    }
  }
}





Deleting Records using the Prepared Statement

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] argv) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
        "root", "root");
    String sql = "DELETE FROM product where year_made = ?";
    PreparedStatement prest = con.prepareStatement(sql);
    prest.setInt(1, 2008);
    int del = prest.executeUpdate();
    System.out.println("Number of deleted records: " + del);
    con.close();
  }
}





Demo Prepared Statement Set BigDecimal

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DemoPreparedStatementSetBigDecimal {
  public static Connection getConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "name";
    String password = "password";
    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;
    String query = null;
    try {
      conn = getConnection();
      query = "insert into  BIG_DECIMAL_TABLE(id, big_decimal) values(?, ?)";
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, "001");
      pstmt.setBigDecimal(2, new java.math.BigDecimal("123456789"));
      // execute query, and return number of rows created
      int rowCount = pstmt.executeUpdate();
      System.out.println("rowCount=" + rowCount);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      pstmt.close();
      conn.close();
    }
  }
}





Demo Prepared Statement Set Blob

 
/*
Defining the Table: Oracle and MySql
create table MyPictures (
   id INT PRIMARY KEY,
   name VARCHAR(0),
   photo BLOB
);
*/
       
       
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class DemoPreparedStatementSetBlob {
  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;
    ResultSet rs = null;
    java.sql.Blob blob = null;
    try {
      conn = getConnection();
      // prepare blob object from an existing binary column
      pstmt = conn.prepareStatement("select photo from my_pictures where id = ?");
      pstmt.setString(1, "0001");
      rs = pstmt.executeQuery();
      rs.next();
      blob = rs.getBlob(1);
      // prepare SQL query for inserting a new row using setBlob()
      String query = "insert into blob_table(id, blob_column) values(?, ?)";
      // begin transaction
      conn.setAutoCommit(false);
 
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, "0002");
      pstmt.setBlob(2, blob);
 
      int rowCount = pstmt.executeUpdate();
      System.out.println("rowCount=" + rowCount);
      // end transaction
      conn.rumit();
    } finally {
      rs.close();
      pstmt.close();
      conn.close();
    }
  }
}





Demo PreparedStatement Set Boolean

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DemoPreparedStatementSetBoolean {
  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 {
    boolean booleanValue = true;
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      String query = "insert into boolean_table(id, boolean_column) values(?, ?)";
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, "0001");
      pstmt.setBoolean(2, booleanValue);
      int rowCount = pstmt.executeUpdate();
      System.out.println("rowCount=" + rowCount);
    } finally {
      pstmt.close();
      conn.close();
    }
  }
}





Demo PreparedStatement Set Byte

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DemoPreparedStatementSetIntegers {
  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";
    byte byteValue = 1;
    short shortValue = 1;
    int intValue = 12345;
    long longValue = 100000000L;
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      String query = "insert into integer_table(id, byte_column, "
          + "short_column, int_column, long_column) values(?, ?, ?, ?, ?)";
      // create PrepareStatement object
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, id);
      pstmt.setByte(2, byteValue);
      pstmt.setShort(3, shortValue);
      pstmt.setInt(4, intValue);
      pstmt.setLong(5, longValue);
      // execute query, and return number of rows created
      int rowCount = pstmt.executeUpdate();
      System.out.println("rowCount=" + rowCount);
    } finally {
      pstmt.close();
      conn.close();
    }
  }
}





Demo PreparedStatement Set Bytes

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DemoPreparedStatementSetBytes {
  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 {
    byte[] shortData = "www.jexp.ru".getBytes();
    byte[] longData = "www.jexp.ru".getBytes();
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      String query = "insert into bytes_table (id, short_data, long_data) values(?, ?, ?)";
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, "0001");
      pstmt.setBytes(2, shortData);
      pstmt.setBytes(3, longData);
      int rowCount = pstmt.executeUpdate();
      System.out.println("rowCount=" + rowCount);
    } finally {
      pstmt.close();
      conn.close();
    }
  }
}





Demo PreparedStatement Set Clob

 
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);
      // execute query, and return number of rows created
      int rowCount = pstmt.executeUpdate();
      System.out.println("rowCount=" + rowCount);
      // end transaction
      conn.rumit();
    } finally {
      rs.close();
      pstmt.close();
      conn.close();
    }
  }
}





Demo PreparedStatement Set Date

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DemoPreparedStatementSetDate {
  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();
    }
  }
}





Demo PreparedStatement Set Float And Double

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DemoPreparedStatementSetFloatAndDouble {
  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();
    }
  }
}





Demo PreparedStatement Set Integers

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DemoPreparedStatementSetIntegers {
  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";
    byte byteValue = 1;
    short shortValue = 1;
    int intValue = 12345;
    long longValue = 100000000L;
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      String query = "insert into integer_table(id, byte_column, "
          + "short_column, int_column, long_column) values(?, ?, ?, ?, ?)";
      // create PrepareStatement object
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, id);
      pstmt.setByte(2, byteValue);
      pstmt.setShort(3, shortValue);
      pstmt.setInt(4, intValue);
      pstmt.setLong(5, longValue);
      // execute query, and return number of rows created
      int rowCount = pstmt.executeUpdate();
      System.out.println("rowCount=" + rowCount);
    } finally {
      pstmt.close();
      conn.close();
    }
  }
}





Demo PreparedStatement Set Long

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DemoPreparedStatementSetIntegers {
  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";
    byte byteValue = 1;
    short shortValue = 1;
    int intValue = 12345;
    long longValue = 100000000L;
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      String query = "insert into integer_table(id, byte_column, "
          + "short_column, int_column, long_column) values(?, ?, ?, ?, ?)";
      // create PrepareStatement object
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, id);
      pstmt.setByte(2, byteValue);
      pstmt.setShort(3, shortValue);
      pstmt.setInt(4, intValue);
      pstmt.setLong(5, longValue);
      // execute query, and return number of rows created
      int rowCount = pstmt.executeUpdate();
      System.out.println("rowCount=" + rowCount);
    } finally {
      pstmt.close();
      conn.close();
    }
  }
}





Demo PreparedStatement Set Null for char/string column

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DemoPreparedStatementSetNull {
  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";
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      String query = "insert into nullable_table(id,string_column, int_column) values(?, ?, ?)";
      // create PrepareStatement object
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, id);
      pstmt.setNull(2, java.sql.Types.VARCHAR);
      pstmt.setNull(3, java.sql.Types.INTEGER);
      // execute query, and return number of rows created
      int rowCount = pstmt.executeUpdate();
      System.out.println("rowCount=" + rowCount);
    } finally {
      pstmt.close();
      conn.close();
    }
  }
}





Demo PreparedStatement Set Null for int value column

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DemoPreparedStatementSetNull {
  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";
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      String query = "insert into nullable_table(id,string_column, int_column) values(?, ?, ?)";
      // create PrepareStatement object
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, id);
      pstmt.setNull(2, java.sql.Types.VARCHAR);
      pstmt.setNull(3, java.sql.Types.INTEGER);
      // execute query, and return number of rows created
      int rowCount = pstmt.executeUpdate();
      System.out.println("rowCount=" + rowCount);
    } finally {
      pstmt.close();
      conn.close();
    }
  }
}





Demo PreparedStatement Set Reference

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class DemoPreparedStatementSetRef {
  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();
    }
  }
}





Demo PreparedStatement Set Short

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DemoPreparedStatementSetIntegers {
  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";
    byte byteValue = 1;
    short shortValue = 1;
    int intValue = 12345;
    long longValue = 100000000L;
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      String query = "insert into integer_table(id, byte_column, "
          + "short_column, int_column, long_column) values(?, ?, ?, ?, ?)";
      // create PrepareStatement object
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, id);
      pstmt.setByte(2, byteValue);
      pstmt.setShort(3, shortValue);
      pstmt.setInt(4, intValue);
      pstmt.setLong(5, longValue);
      // execute query, and return number of rows created
      int rowCount = pstmt.executeUpdate();
      System.out.println("rowCount=" + rowCount);
    } finally {
      pstmt.close();
      conn.close();
    }
  }
}





Demo PreparedStatement Set String

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DemoPreparedStatementSetString {
  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 stringValue = "stringValueToBeInserted";
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      String query = "insert into string_table(string_column) values(?)";
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, stringValue);
      // execute query, and return number of rows created
      int rowCount = pstmt.executeUpdate();
      System.out.println("rowCount=" + rowCount);
    } finally {
      pstmt.close();
      conn.close();
    }
  }
}





Demo PreparedStatement Set Time

 
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();
    }
  }
}





Demo PreparedStatement Set Timestamp

 
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();
    }
  }
}





Demo PreparedStatement Set URL

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DemoPreparedStatementSetURL {
  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 urlValue = "http://www.jexp.ru";
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      String query = "insert into url_table(id, url) values(?, ?)";
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, id);
      pstmt.setURL(2, new java.net.URL(urlValue));
      // execute query, and return number of rows created
      int rowCount = pstmt.executeUpdate();
      System.out.println("rowCount=" + rowCount);
    } finally {
      pstmt.close();
      conn.close();
    }
  }
}





Inserting Records using the Prepared Statement

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] argv) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial", "root",
        "root");
    String sql = "INSERT product VALUES(?,?)";
    PreparedStatement prest = con.prepareStatement(sql);
    prest.setString(1, "asdf");
    prest.setInt(2, 2009);
    int count = prest.executeUpdate();
    System.out.println(count + "row(s) affected");
    con.close();
  }
}





Inserting with a prepared statement that uses the various setXXX() methods.

  
import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Time;
import java.sql.Timestamp;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    String sql = "INSERT INTO mysql_all_table("
      + "col_boolean,"
      + "col_byte,"
      + "col_short,"
      + "col_int,"
      + "col_long,"
      + "col_float,"
      + "col_double,"
      + "col_bigdecimal,"
      + "col_string,"
      + "col_date,"
      + "col_time,"
      + "col_timestamp,"
      + "col_asciistream,"
      + "col_binarystream,"
      + "col_blob) "
      + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
  PreparedStatement pstmt = connection.prepareStatement(sql);
  pstmt.setBoolean(1, true);
  pstmt.setByte(2, (byte)123);
  pstmt.setShort(3, (short)123);
  pstmt.setInt(4, 123);
  pstmt.setLong(5, 123L);
  pstmt.setFloat(6, 1.23F);
  pstmt.setDouble(7, 1.23D);
  pstmt.setBigDecimal(8, new BigDecimal(1.23));
  pstmt.setString(9, "a string");
  pstmt.setDate(10, new java.sql.Date(System.currentTimeMillis()));
  pstmt.setTime(11, new Time(System.currentTimeMillis()));
  pstmt.setTimestamp(12, new Timestamp(System.currentTimeMillis()));
  File file = new File("infilename1");
  FileInputStream is = new FileInputStream(file);
  pstmt.setAsciiStream(13, is, (int)file.length());
  file = new File("infilename2");
  is = new FileInputStream(file);
  pstmt.setBinaryStream(14, is, (int)file.length());
  file = new File("infilename3");
  is = new FileInputStream(file);
  pstmt.setBinaryStream(15, is, (int)file.length());
  pstmt.executeUpdate();
  }
}





Insert Records Using PreparedStatement

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class InsertRecordsUsingPreparedStatement {
  public static Connection getConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "name";
    String password = "password";
    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 dept(deptnum, deptname, deptloc) values(?, ?, ?)";
      pstmt = conn.prepareStatement(query); // create a statement
      pstmt.setInt(1, 1); // set input parameter 1
      pstmt.setString(2, "deptname"); // set input parameter 2
      pstmt.setString(3, "deptLocation"); // set input parameter 3
      pstmt.executeUpdate(); // execute insert statement
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      pstmt.close();
      conn.close();
    }
  }
}





Modify data in a table

  
import java.sql.Connection;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] argv) throws Exception {
    Connection con = null;
    PreparedStatement prepstmt;
    prepstmt = con.prepareStatement("UPDATE employee SET Name = ? "
        + " WHERE Id = ?");
    prepstmt.setString(1, "Smith");
    prepstmt.setString(2, "1");
    prepstmt.executeUpdate();
    prepstmt.close();
    con.close();
  }
}





Prepared Statement Batch Update

 
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
      }
    }
  }
}





PreparedStatement Set Array

 
/*
JDBC Recipes: A Problem-Solution Approach (Problem-Solution Approach) (Hardcover)
by Mahmoud Parsian 

# Publisher: Apress (September 15, 2005)
# Language: English
# ISBN: 1590595203
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import oracle.sql.ArrayDescriptor;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = null;
    PreparedStatement pstmt = null;
    java.sql.Array sqlArray = null;
    conn = getOracleConnection();
    // For oracle you need an array descriptor specifying
    // the type of the array and a connection to the database
    // the first parameter must match with the SQL ARRAY type created
    ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("CHAR_ARRAY", conn);
    // then obtain an Array filled with the content below
    String[] content = { "v1", "v2", "v3", "v4" };
    sqlArray = new oracle.sql.ARRAY(arrayDescriptor, conn, content);
    String query = "insert into CHAR_ARRAY_TABLE(id, array) values(?, ?)";
    pstmt = conn.prepareStatement(query);
    pstmt.setString(1, "0001");
    pstmt.setArray(2, sqlArray);
    int rowCount = pstmt.executeUpdate();
    System.out.println("rowCount=" + rowCount);
    System.out.println("--Demo_PreparedStatement_SetArray end--");
    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:databaseName";
    String username = "userName";
    String password = "password";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}





PreparedStatement Set Object

 
/*
JDBC Recipes: A Problem-Solution Approach (Problem-Solution Approach) (Hardcover)
by Mahmoud Parsian 

# Publisher: Apress (September 15, 2005)
# Language: English
# ISBN: 1590595203
*/

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 {
    ResultSet rs = null;
    Connection conn = null;
    PreparedStatement pstmt = null;
    PreparedStatement pstmt2 = null;
    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());
    // prepare blob object from an existing binary column
    String insert = "insert into resume (id, name, content, date_created ) values(?, ?, ?, ?)";
    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=?";
    pstmt2 = conn.prepareStatement(query);
    pstmt2.setObject(1, inputValues[0]);
    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;
  }
}





Rows affected when updating data in database table

  
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] args) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/book", "root", "");
    PreparedStatement ps = connection.prepareStatement("UPDATE books SET title = ? WHERE id = ?");
    ps.setString(1, "Java");
    ps.setInt(2, 1);
    int rows = ps.executeUpdate();
    System.out.printf("%d row(s) updated!", rows);
    connection.close();
  }
}





SELECT data from a table

  
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Main {
  public static void main(String[] argv) throws Exception {
    Connection con = null;
    PreparedStatement prepstmt = con
        .prepareStatement("select Name, Addr from Employee where Id = ?");
    prepstmt.setString(1, "1");
    ResultSet rs;
    rs = prepstmt.executeQuery();
    boolean found = rs.next();
    if (found)
      System.out.println(rs.getString(1));
    prepstmt.close();
  }
}





Select Records Using PreparedStatement

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SelectRecordsUsingPreparedStatement {
  public static Connection getConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "name";
    String password = "password";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static void main(String[] args) {
    ResultSet rs = null;
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      String query = "select deptno, deptname, deptloc from dept where deptno > ?";
      pstmt = conn.prepareStatement(query); // create a statement
      pstmt.setInt(1, 1001); // set input parameter
      rs = pstmt.executeQuery();
      // extract data from the ResultSet
      while (rs.next()) {
        int dbDeptNumber = rs.getInt(1);
        String dbDeptName = rs.getString(2);
        String dbDeptLocation = rs.getString(3);
        System.out.println(dbDeptNumber + "\t" + dbDeptName + "\t" + dbDeptLocation);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        rs.close();
        pstmt.close();
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}





Update Records Using PreparedStatement

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class UpdateRecordsUsingPreparedStatement {
  public static Connection getConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "name";
    String password = "password";
    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 = "update dept set DEPT_LOC = ? where DEPT_NUM = ? ";
      pstmt = conn.prepareStatement(query); // create a statement
      pstmt.setString(1, "deptLocation"); // set input parameter 1
      pstmt.setInt(2, 1001); // set input parameter 2
      pstmt.executeUpdate(); // execute update statement
    } catch (Exception e) {
      e.printStackTrace();
      System.exit(1);
    } finally {
      pstmt.close();
      conn.close();
    }
  }
}





Use PreparedStatement Twice

 
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 = getHSQLConnection();
    System.out.println("Got Connection.");
    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")");
    st.executeUpdate("insert into survey (id,name ) values (2,"anotherValue")");    
    ResultSet rs = null;
    PreparedStatement ps = null;
    String query = "select id, name from survey where id = ?";
    ps = conn.prepareStatement(query);
    // specify values for all input parameters
    ps.setInt(1, 001); // set the first parameter: id
    // now, PreparedStatement object is ready to be executed.
    rs = ps.executeQuery();
    // iterate the result set object
    while (rs.next()) {
      int id = rs.getInt(1);
      String name = rs.getString(2);
      System.out.println("[id=" + id + "][name=" + name + "]");
    }
    // NOTE: you may use PreparedStatement as many times as you want
    // here we use it for another set of parameters:
    ps.setInt(1, 002); // set the first parameter: id
    // now, PreparedStatement object is ready to be executed.
    rs = ps.executeQuery();
    // iterate the result set object
    while (rs.next()) {
      int id = rs.getInt(1);
      String name = rs.getString(2);
      System.out.println("[id=" + id + "][name=" + name + "]");
    }
    rs.close();
    ps.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;
  }
}





Using the Prepared Statement Twice

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Main {
  public static void main(String[] argv) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
        "root", "root");
    String sql = "SELECT * FROM product WHERE year_made = ?";
    PreparedStatement prest = con.prepareStatement(sql);
    prest.setInt(1, 2002);
    ResultSet rs1 = prest.executeQuery();
    while (rs1.next()) {
      String mov_name = rs1.getString(1);
      int mad_year = rs1.getInt(2);
      System.out.println(mov_name + "\t- " + mad_year);
    }
    prest.setInt(1, 2003);
    ResultSet rs2 = prest.executeQuery();
    while (rs2.next()) {
      String mov_name = rs2.getString(1);
      int mad_year = rs2.getInt(2);
      System.out.println(mov_name + "\t- " + mad_year);
    }
  }
}


Источник — «http://jexp.ru/index.php?title=Java/Database_SQL_JDBC/PreparedStatement&oldid=6895»