Java by API/java.sql/PreparedStatement — различия между версиями

Текущая версия на 14: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()

 
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: executeUpdate()

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

PreparedStatement: getParameterMetaData()

 

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

PreparedStatement: getWarnings()

 

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", "");
  }
}

PreparedStatement: setAsciiStream(int parameterIndex, InputStream x, int length)

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

PreparedStatement: setBigDecimal(int parameterIndex, BigDecimal x)

 
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", "");
  }
}

PreparedStatement: setBinaryStream(int parameterIndex, InputStream x, int length)

 

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", "");
  }
}

PreparedStatement: setBoolean(int parameterIndex, boolean x)

 
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", "");
  }
}

PreparedStatement: setByte(int parameterIndex, byte x)

 
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", "");
  }
}

PreparedStatement: setCharacterStream(int parameterIndex, Reader reader, int length)

 

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", "");
  }
}

PreparedStatement: setClob(int parameterIndex, Clob x)

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

PreparedStatement: setDate(int parameterIndex, Date x)

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

PreparedStatement: setDouble(int parameterIndex, double x)

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

PreparedStatement: setFetchSize(int rows)

 

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", "");
  }
}

PreparedStatement: setFloat(int parameterIndex, float x)

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

PreparedStatement: setInt(int parameterIndex, int x)

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

PreparedStatement: setLong(int parameterIndex, long x)

 
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", "");
  }
}

PreparedStatement: setNull(int parameterIndex, int sqlType)

 

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", "");
  }
}

PreparedStatement: setObject(int parameterIndex, Object x)

 

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

PreparedStatement: setRef(int parameterIndex, Ref x)

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

PreparedStatement: setShort(int parameterIndex, short x)

 
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", "");
  }
}

PreparedStatement: setString(int parameterIndex, String x)

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

PreparedStatement: setTime(int parameterIndex, Time x)

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

PreparedStatement: setTimestamp(int parameterIndex, Timestamp x)

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

PreparedStatement: setURL(int parameterIndex, URL x)

 
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", "");
  }
}

Java by API/java.sql/PreparedStatement — различия между версиями

Текущая версия на 14:46, 31 мая 2010

Содержание

PreparedStatement: addBatch()

PreparedStatement: executeUpdate()

PreparedStatement: getParameterMetaData()

PreparedStatement: getWarnings()

PreparedStatement: setAsciiStream(int parameterIndex, InputStream x, int length)

PreparedStatement: setBigDecimal(int parameterIndex, BigDecimal x)

PreparedStatement: setBinaryStream(int parameterIndex, InputStream x, int length)

PreparedStatement: setBoolean(int parameterIndex, boolean x)

PreparedStatement: setByte(int parameterIndex, byte x)

PreparedStatement: setCharacterStream(int parameterIndex, Reader reader, int length)

PreparedStatement: setClob(int parameterIndex, Clob x)

PreparedStatement: setDate(int parameterIndex, Date x)

PreparedStatement: setDouble(int parameterIndex, double x)

PreparedStatement: setFetchSize(int rows)

PreparedStatement: setFloat(int parameterIndex, float x)

PreparedStatement: setInt(int parameterIndex, int x)

PreparedStatement: setLong(int parameterIndex, long x)

PreparedStatement: setNull(int parameterIndex, int sqlType)

PreparedStatement: setObject(int parameterIndex, Object x)

PreparedStatement: setRef(int parameterIndex, Ref x)

PreparedStatement: setShort(int parameterIndex, short x)

PreparedStatement: setString(int parameterIndex, String x)

PreparedStatement: setTime(int parameterIndex, Time x)

PreparedStatement: setTimestamp(int parameterIndex, Timestamp x)

PreparedStatement: setURL(int parameterIndex, URL x)

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты

Текущая версия на 14:46, 31 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)	Текущая версия на 14:46, 31 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)
(нет различий)