Create JDBC PreparedStatement (PS)

Create PreparedStatement?

  • The java.sql.Connection interface has several methods used to create a java.sql.PreparedStatement interface, which we can use to execute parameterized SQL statements against the database.
  • 6 Methods from java.sql.Connection interface that creates a PreparedStatement object:
    Methods in Connection interface Description
    PreparedStatement prepareStatement(String sql) Creates a default PreparedStatement object for sending parameterized SQL statements to the database. Can be used to query for a ResultSet (using the executeQuery() method) that is non-updateable and non-scrollable.
    The next two methods are intended to be used when you want an updatable ResultSet
    PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) When you use this method, the returned PreparedStatement object can be used to query for a ResultSet (using the executeQuery() method) that can be updateable and can be scrollable.
    PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) This will create a PreparedStatement object as the previous one. Use this method when you also want to decide whether you want the resultset to stay open (resultSetHoldability) after a transaction is committed. (Only available in JDBC 3.0)
    The next three methods are only intended to be used in conjunction with an SQL insert statement.
    PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) Creates a default PreparedStatement object that has the capability to retrieve auto-generated keys. This parameter is ignored if the SQL statement is not an INSERT statement, or an SQL statement able to return auto-generated keys
    PreparedStatement prepareStatement(String sql, int[] columnIndexes) Creates a default PreparedStatement object capable of returning the auto-generated keys designated by the given array. The array contains the indexes (starting with 1 for the first) of the columns. The driver will ignore the array if the SQL statement is not an INSERT statement, or an SQL statement able to return auto-generated keys.
    PreparedStatement prepareStatement(String sql, String[] columnNames) Creates a default PreparedStatement object capable of returning the auto-generated keys designated by the given array. The array contains the names of the columns. The driver will ignore the array if the SQL statement is not an INSERT statement, or an SQL statement able to return auto-generated keys.

    The resultSetHoldability constant will indicates that a open ResultSet objects shall remain open or closed when the current transaction is committed.
  • Steps to create and execute a SQL using prepareStatement() method which existing in the Connection object is:
    1. Using one of the prepareStatement() methods to create a PreparedStatement.
    2. Maybe use some of the "SET methods" found in the table below and exists in the PreparedStatement object.
    3. Execute one of the "execute" methods found in the table below which also exists in the PreparedStatement object.
    Example code:
    String sdl = "insert into COUNTRIES values (?, ?, ?)"
    // Placeholder index: 1 2 3
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setString(1, "Canada");
    ps.setString(2, "CA");
    ps.setString(3, "North America");
    ps.executeUpdate();

Execute SQL methods with PreparedStatement object?

  • When you have got a java.sql.PreparedStatement object several methods on that object can be used to execute a parameterized SQL statements against the database.
    Execute methods in java.sql.PreparedStatement object to use:
    Methods in PreparedStatement interface Description
    int executeUpdate() Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.
    ResultSet executeQuery() Executing SQL statement in this PreparedStatement object and returns a ResultSet object that is generated based on the SQL statement.
    boolean execute() Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.

SET methods with PreparedStatement object.

  • When you have got a java.sql.PreparedStatement object several methods on that object can be used to set the value for placeholder in SQL statements.
    SET methods in java.sql.PreparedStatement object to use:
    Methods in PreparedStatement interface Description
    void setArray(int parameterIndex, Array x) Sets the designated parameter to the given java.sql.Array object.
    void setAsciiStream(int parameterIndex, InputStream x) Sets the designated parameter to the given input stream.
    void setAsciiStream(int parameterIndex, InputStream x, int length) Sets the designated parameter to the given input stream, which will have the specified number of bytes.
    void setAsciiStream(int parameterIndex, InputStream x, long length) Sets the designated parameter to the given input stream, which will have the specified number of bytes.
    void setBigDecimal(int parameterIndex, BigDecimal x) Sets the designated parameter to the given java.math.BigDecimal value.
    void setBinaryStream(int parameterIndex, InputStream x) Sets the designated parameter to the given input stream.
    void setBinaryStream(int parameterIndex, InputStream x, int length) Sets the designated parameter to the given input stream, which will have the specified number of bytes.
    void setBinaryStream(int parameterIndex, InputStream x, long length) Sets the designated parameter to the given input stream, which will have the specified number of bytes.
    void setBlob(int parameterIndex, Blob x) Sets the designated parameter to the given java.sql.Blob object.
    void setBlob(int parameterIndex, InputStream inputStream) Sets the designated parameter to a InputStream object.
    void setBlob(int parameterIndex, InputStream inputStream, long length) Sets the designated parameter to a InputStream object.
    void setBoolean(int parameterIndex, boolean x) Sets the designated parameter to the given Java boolean value.
    void setByte(int parameterIndex, byte x) Sets the designated parameter to the given Java byte value.
    void setBytes(int parameterIndex, byte[] x) Sets the designated parameter to the given Java array of bytes.
    void setCharacterStream(int parameterIndex, Reader reader) Sets the designated parameter to the given Reader object.
    void setCharacterStream(int parameterIndex, Reader reader, int length) Sets the designated parameter to the given Reader object, which is the given number of characters long.
    void setCharacterStream(int parameterIndex, Reader reader, long length) Sets the designated parameter to the given Reader object, which is the given number of characters long.
    void setClob(int parameterIndex, Clob x) Sets the designated parameter to the given java.sql.Clob object.
    void setClob(int parameterIndex, Reader reader) Sets the designated parameter to a Reader object.
    void setClob(int parameterIndex, Reader reader, long length) Sets the designated parameter to a Reader object.
    void setDate(int parameterIndex, Date x) Sets the designated parameter to the given java.sql.Date value using the default time zone of the virtual machine that is running the application.
    void setDate(int parameterIndex, Date x, Calendar cal) Sets the designated parameter to the given java.sql.Date value, using the given Calendar object.
    void setDouble(int parameterIndex, double x) Sets the designated parameter to the given Java double value.
    void setFloat(int parameterIndex, float x) Sets the designated parameter to the given Java float value.
    void setInt(int parameterIndex, int x) Sets the designated parameter to the given Java int value.
    void setLong(int parameterIndex, long x) Sets the designated parameter to the given Java long value.
    void setNCharacterStream(int parameterIndex, Reader value) Sets the designated parameter to a Reader object.
    void setNCharacterStream(int parameterIndex, Reader value, long length) Sets the designated parameter to a Reader object.
    void setNClob(int parameterIndex, NClob value) Sets the designated parameter to a java.sql.NClob object.
    void setNClob(int parameterIndex, Reader reader) Sets the designated parameter to a Reader object.
    void setNClob(int parameterIndex, Reader reader, long length) Sets the designated parameter to a Reader object.
    void setNString(int parameterIndex, String value) Sets the designated paramter to the given String object.
    void setNull(int parameterIndex, int sqlType) Sets the designated parameter to SQL NULL.
    void setNull(int parameterIndex, int sqlType, String typeName) Sets the designated parameter to SQL NULL.
    void setObject(int parameterIndex, Object x) Sets the value of the designated parameter using the given object.
    void setObject(int parameterIndex, Object x, int targetSqlType) Sets the value of the designated parameter with the given object.
    void setObject(int parameterIndex, Object x, int targetSqlType, int scaleOrLength) Sets the value of the designated parameter with the given object.
    void setRef(int parameterIndex, Ref x) Sets the designated parameter to the given REF(<structured-type>) value.
    void setRowId(int parameterIndex, RowId x) Sets the designated parameter to the given java.sql.RowId object.
    void setShort(int parameterIndex, short x) Sets the designated parameter to the given Java short value.
    void setSQLXML(int parameterIndex, SQLXML xmlObject) Sets the designated parameter to the given java.sql.SQLXML object.
    void setString(int parameterIndex, String x) Sets the designated parameter to the given Java String value.
    void setTime(int parameterIndex, Time x) Sets the designated parameter to the given java.sql.Time value.
    void setTime(int parameterIndex, Time x, Calendar cal) Sets the designated parameter to the given java.sql.Time value, using the given Calendar object.
    void setTimestamp(int parameterIndex, Timestamp x) Sets the designated parameter to the given java.sql.Timestamp value.
    void setTimestamp(int parameterIndex, Timestamp x, Calendar cal) Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object.
    void setURL(int parameterIndex, URL x) Sets the designated parameter to the given java.net.URL value.

© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.