jdbc PreparedStatement Setting parameters for PreparedStatement

Example

Placeholders in the query string need to be set by using the set* methods:

String sql = "SELECT * FROM EMP WHERE JOB = ? AND SAL > ?";

//Create statement to make your operations
PreparedStatement statement = connection.prepareStatement(sql);

statement.setString(1, "MANAGER"); // String value
statement.setInt(2, 2850);         // int value

Special cases

Setting NULL value:

Setting a null value can not be accomplished using for example the setInt and setLong methods, as these use primitive types (int and long) instead of objects (Integer and Long), and would cause a NullPointerException to be thrown:

void setFloat(int parameterIndex, float x) 
void setInt(int parameterIndex, int x) 
void setLong(int parameterIndex, long x)  

These cases can be handled by using setNull.

setNull(int parameterIndex, int sqlType)

It is typed, so the second parameter has to be provided, see java.sql.Types

//setting a NULL for an integer value
statement.setNull(2, java.sql.Types.INTEGER);

Setting LOBs

LOBs require special objects to be used.

Clob longContent = connection.createClob();
Writer longContentWriter = longContent.setCharacterStream(1); // position: beginning
longContentWriter.write("This will be the content of the CLOB");

pstmt = connection.prepareStatement("INSERT INTO CLOB_TABLE(CLOB_VALUE) VALUES (?)");
pstmt.setClob(1, longContent);

Exceptions on set* methods

SQLException - if parameterIndex does not correspond to a parameter marker in the SQL statement; if a database access error occurs or this method is called on a closed PreparedStatement

SQLFeatureNotSupportedException - if sqlType is a ARRAY, BLOB, CLOB, DATALINK, JAVA_OBJECT, NCHAR, NCLOB, NVARCHAR, LONGNVARCHAR, REF, ROWID, SQLXML or STRUCT data type and the JDBC driver does not support this data type