The PreparedStatement object of the Polypheny JDBC driver is used to execute prepared statements. Among other things, it has methods for assigning values to the placeholders used in the statement and for fetching results from the statement.
executeQuery
public ResultSet executeQuery(String sql)
throws SQLException
The executeQuery() method of the PreparedStatement object in the JDBC Driver is tailored specifically for executing SQL queries that produce a result set. This method is different from the general-purpose execute method in the Statement object in that it expects the executed statement to be a query that returns a result set. Attempting to use this method for a statement that does not produce a result set, like an update or a delete, will result in an SQLException.
Unlike executeUpdate, which is meant for operations that modify data and return an update count, executeQuery is designed only for retrieving data and always returns a ResultSet.
Parameters:
This method does not take any parameters.
Return Values:
| Return Type | Description |
|---|---|
ResultSet |
The result set generated by the query; never null for a correctly executing executeQuery. |
This method throws an SQLException if a database access error occurs, the method is called on a closed PreparedStatement, or the given SQL statement does not produce a result set.
Usage:
try (
Connection connection = DriverManager.getConnection( "jdbc:polypheny://localhost:20590" );
PreparedStatement preparedStatement = connection.prepareStatement( "SELECT * FROM emps WHERE empid = ?" );
) {
reparedStatement.setInt( 1, 123 );
ResultSet resultSet = preparedStatement.executeQuery();
while ( resultSet.next() ) {
// Process the results...
}
resultSet.close();
} catch ( SQLException e ) {
e.printStackTrace();
}
executeLargeUpdate
public long executeLargeUpdate(String sql)
throws SQLException
public long executeLargeUpdate(String sql, int autoGeneratedKeys)
throws SQLException
public long executeLargeUpdate(String sql, int[] columnIndexes)
throws SQLException
public long executeLargeUpdate(String sql, String[] columnNames)
throws SQLException
executeLargeUpdate(String sql) is suppported. The other overloads throw a SQLFeatureNotImplementedException.
The executeLargeUpdate() method of the PreparedStatement object in the JDBC Driver is specially designed for executing SQL statements that modify a large number of rows, such as INSERT, UPDATE, or DELETE statements. This method differs from the conventional executeUpdate in that it returns the update count as a long instead of an int. This is particularly useful for operations that might affect more rows than can be represented by an int. As of now only the method executeLargeUpdate(String sql) is suppported. The other overloads throw a SQLFeatureNotImplementedException.
This method is strictly meant for operations that modify data and return an update count. Invoking this method with a statement that produces a result set will lead to an SQLException.
Parameters:
This method does not take any parameters.
Return Values:
| Return Type | Description |
|---|---|
long |
The number of rows affected by the update, insert, or delete; or 0 if no rows were affected or the statement executed was a DDL command like CREATE TABLE. |
This method throws an SQLException if a database access error occurs, the method is called on a closed PreparedStatement, or the given SQL statement produces a result set.
Usage:
try (
Connection connection = DriverManager.getConnection( "jdbc:polypheny://localhost:20590" );
PreparedStatement preparedStatement = connection.prepareStatement( "UPDATE large_table SET value = ? WHERE id > ?" );
) {
preparedStatement.setString( 1, "newValue" );
preparedStatement.setLong( 2, 1000000 );
long rowsAffected = preparedStatement.executeLargeUpdate();
System.out.println( "Number of rows updated: " + rowsAffected );
} catch ( SQLException e ) {
e.printStackTrace();
}
executeUpdate
public int executeUpdate(String sql)
throws SQLException
public int executeUpdate(String sql, int autoGeneratedKeys)
throws SQLException
public int executeUpdate(String sql, int[] columnIndexes)
throws SQLException
public int executeUpdate(String sql, String[] columnNames)
throws SQLException
executeUpdate(String sql) is suppported. The other overloads throw a SQLFeatureNotImplementedException.
The executeUpdate() method of the PreparedStatement object in the JDBC Driver is designed for executing SQL Data Manipulation Language (DML) statements such as INSERT, UPDATE, or DELETE, as well as SQL Data Definition Language (DDL) statements like CREATE TABLE or DROP TABLE. This method returns the number of rows affected by the DML statement or 0 for DDL statements. Unlike the executeQuery() method, which is meant for statements that return result sets, executeUpdate() is strictly for operations that modify data or change the database structure. As of now only the method executeUpdate(String sql) is suppported. The other overloads throw a SQLFeatureNotImplementedException.
It’s essential to use this method appropriately: invoking it with a statement that produces a result set will result in an SQLException.
Parameters:
This method does not take any parameters.
Return Values:
| Return Type | Description |
|---|---|
int |
The number of rows affected by the INSERT, UPDATE, or DELETE statement; or 0 if no rows were affected or the statement executed was a DDL command like CREATE TABLE. |
This method throws an SQLException if a database access error occurs, the method is called on a closed PreparedStatement, or the given SQL statement produces a result set.
Usage:
try (
Connection connection = DriverManager.getConnection( "jdbc:polypheny://localhost:20590" );
PreparedStatement preparedStatement = connection.prepareStatement( "UPDATE my_table SET value = ? WHERE id = ?" );
) {
preparedStatement.setString( 1, "updatedValue" ); // Set the value parameter
preparedStatement.setInt( 2, 12345 ); // Set the id parameter
int rowsAffected = preparedStatement.executeUpdate();
System.out.println( "Number of rows updated: " + rowsAffected );
} catch ( SQLException e ) {
e.printStackTrace();
}
clearParameters
public void clearParameters()
throws SQLException
The clearParameters() method of the PreparedStatement object in the JDBC Driver provides an efficient mechanism to clear all parameter values previously set in a prepared SQL statement. This allows for the reuse of the same PreparedStatement object with different parameter values, ensuring that no remnants of old parameter values affect subsequent executions.
When working with PreparedStatement, it is common to reuse the statement for multiple executions, especially in batch operations or iterative tasks. To ensure that every execution starts with a clean slate, you can use clearParameters() to reset all the parameter values to their default states.
Parameters:
This method does not take any parameters.
Return Values:
This method does not return any values.
This method throws an SQLException if a database access error occurs or the method is called on a closed PreparedStatement.
Usage:
try (
Connection connection = DriverManager.getConnection( "jdbc:polypheny://localhost:20590" );
PreparedStatement preparedStatement = connection.prepareStatement( "INSERT INTO my_table (name, age) VALUES (?, ?)" );
) {
// First insertion
preparedStatement.setString( 1, "Alice" );
preparedStatement.setInt( 2, 30 );
preparedStatement.executeUpdate();
// Clear parameters
preparedStatement.clearParameters();
// Second insertion with different values
preparedStatement.setString( 1, "Bob" );
preparedStatement.setInt( 2, 35 );
preparedStatement.executeUpdate();
} catch ( SQLException e ) {
e.printStackTrace();
}
execute
public boolean execute(String sql)
throws SQLException
The execute() method of the PreparedStatement object in the JDBC Driver is a flexible method tailored for executing precompiled SQL statements which may return either a result set or an update count. Unlike executeQuery or executeUpdate which are explicitly designed for queries or updates respectively, the execute method can cater to both. This capability is especially useful when the nature of the SQL statement isn’t known in advance. After invoking this method, one should retrieve either the result set or the update count, depending on the nature of the executed statement, using methods like getResultSet, getUpdateCount, or getLargeUpdateCount.
It’s crucial to remember that calling getUpdateCount or getLargeUpdateCount following the execution of a statement that yields a result set will return the value -1. Conversely, invoking getResultSet on an update will result in null.
Parameters:
This method does not take any explicit parameters, but relies on the previously set parameters of the PreparedStatement.
Return Values:
| Return Type | Description |
|---|---|
boolean |
true if the executed statement produces a ResultSet object (i.e., the executed statement was a query); false if it yields an update count or there are no results. |
This method throws an SQLException if a database access error occurs, the method is called on a closed PreparedStatement, or the given SQL statement generates a result that is neither a single result nor an update count.
Usage:
try (
Connection connection = DriverManager.getConnection( "jdbc:polypheny://localhost:20590" );
PreparedStatement preparedStatement = connection.prepareStatement( "SELECT * FROM my_table WHERE name = ?" );
) {
preparedStatement.setString( 1, "Alice" );
boolean isResultSet = preparedStatement.execute();
if ( isResultSet ) {
ResultSet resultSet = preparedStatement.getResultSet();
while ( resultSet.next() ) {
// Process the results...
}
resultSet.close();
} else {
int updateCount = preparedStatement.getUpdateCount();
// Process the update count if needed...
}
} catch ( SQLException e ) {
e.printStackTrace();
}
addBatch
public void addBatch(String sql)
throws SQLException
The addBatch() method of the PreparedStatement object within the JDBC Driver is primarily designed to facilitate batch processing of SQL commands. When dealing with a series of repetitive SQL operations (like multiple insertions), using this method can greatly optimize performance by reducing the number of separate round-trips to the database. After setting the desired parameters on the PreparedStatement, invoking addBatch() will enqueue the current set of parameters for batch processing. Later, you can execute all accumulated commands in the batch using the executeBatch() method.
Parameters:
This method does not have explicit parameters. However, before invoking addBatch(), you should set the desired parameters on the PreparedStatement using appropriate setter methods (e.g., setString, setInt, etc.).
Return Values:
This method does not return a value.
Exceptions:
SQLException: Thrown if a database access error occurs or the method is called on a closedPreparedStatement.
Usage:
try (
Connection connection = DriverManager.getConnection( "jdbc:polypheny://localhost:20590" );
PreparedStatement preparedStatement = connection.prepareStatement( "INSERT INTO my_table(name, age) VALUES (?, ?)" );
) {
// First set of parameters
preparedStatement.setString( 1, "Alice" );
preparedStatement.setInt( 2, 25 );
preparedStatement.addBatch();
// Second set of parameters
preparedStatement.setString( 1, "Bob" );
preparedStatement.setInt( 2, 30 );
preparedStatement.addBatch();
// Execute the batch
int[] updateCounts = preparedStatement.executeBatch();
for ( int count : updateCounts ) {
System.out.println( "Affected rows: " + count );
}
} catch ( SQLException e ) {
e.printStackTrace();
}
executeLargeBatch
public long[] executeLargeBatch()
throws SQLException
The executeLargeBatch() method of the PreparedStatement object within the JDBC Driver is tailored for batch processing of SQL commands where the number of operations or the update counts can exceed the range of the int type. By utilizing this method, you can handle large-scale batch operations efficiently, optimizing performance by reducing the number of individual round-trips to the database. After setting parameters on the PreparedStatement, use addBatch() to enqueue commands for batch processing. The executeLargeBatch() method can then be invoked to execute all accumulated commands in the batch.
Parameters:
This method does not require explicit parameters. However, prior to calling this method, ensure that you’ve set the necessary parameters on the PreparedStatement via the appropriate setter methods (e.g., setString, setInt, etc.) and added them using addBatch().
Return Values:
| Return Type | Description |
|---|---|
long[] |
An array of update counts, indicating the number of rows affected by each command in the batch. |
Exceptions:
SQLException: If a database access error occurs, if the method is called on a closedPreparedStatement, or if the batch contains commands that produce multiple counts or results.
Usage:
try (
Connection connection = DriverManager.getConnection( "jdbc:polypheny://localhost:20590" );
PreparedStatement preparedStatement = connection.prepareStatement( "INSERT INTO large_table(name, age) VALUES (?, ?)" );
) {
// Add a large number of parameters to the batch
for ( int i = 0; i < LARGE_NUMBER; i++ ) {
preparedStatement.setString( 1, "Name_" + i );
preparedStatement.setInt( 2, 20 + (i % 10) );
preparedStatement.addBatch();
}
// Execute the large batch
long[] updateCounts = preparedStatement.executeLargeBatch();
for ( long count : updateCounts ) {
System.out.println( "Affected rows: " + count );
}
} catch ( SQLException e ) {
e.printStackTrace();
}
executeBatch
public int[] executeBatch()
throws SQLException
The executeBatch() method of the PreparedStatement object in the JDBC Driver provides efficient batch processing capabilities for SQL commands. Batch processing is a powerful feature that optimizes performance by minimizing the number of round-trips to the database. After setting parameters for the PreparedStatement, you can use addBatch() to enqueue commands for batch execution. Once your batch of commands is ready, you can invoke the executeBatch() method to process them all at once.
Parameters:
This method doesn’t take any direct parameters. However, before invoking this method, you should have set the necessary parameters on the PreparedStatement using appropriate setter methods (e.g., setString, setInt, etc.) and added them with addBatch().
Return Values:
| Return Type | Description |
|---|---|
int[] |
An array of update counts, one for each command in the batch. Each count represents the number of rows affected by the corresponding command. |
Exceptions:
BatchUpdateException(a subtype ofSQLException): Thrown if one of the commands in the batch fails to execute properly. This exception provides an array of update counts for the commands that were executed before the error occurred.SQLException: If a database access error occurs or if the method is called on a closedPreparedStatement.
Usage:
try (
Connection connection = DriverManager.getConnection( "jdbc:polypheny://localhost:20590" );
PreparedStatement preparedStatement = connection.prepareStatement( "INSERT INTO my_table(name, age) VALUES (?, ?)" );
) {
// Add multiple sets of parameters to the batch
for ( int i = 1; i <= 10; i++ ) {
preparedStatement.setString( 1, "John_" + i );
preparedStatement.setInt( 2, 20 + i );
preparedStatement.addBatch();
}
// Execute the batch
int[] updateCounts = preparedStatement.executeBatch();
for ( int count : updateCounts ) {
System.out.println( "Affected rows: " + count );
}
} catch ( BatchUpdateException e ) {
int[] updateCounts = e.getUpdateCounts();
for ( int count : updateCounts ) {
System.out.println( "Affected rows before exception: " + count );
}
e.printStackTrace();
} catch ( SQLException e ) {
e.printStackTrace();
}
getMetaData
public ResultSetMetaData getMetaData()
throws SQLException
The getMetaData() method of the PreparedStatement object in the JDBC Driver would typically be used to retrieve a ResultSetMetaData object that provides information about the columns of the ResultSet generated by the execution of the PreparedStatement. However, in the current JDBC driver, this feature is not yet supported.
The intent behind the ResultSetMetaData object in a standard implementation would be to offer details such as the number of columns, column names, column data types, and many other characteristics of the result set.
Parameters:
This method doesn’t take any parameters.
Return Values:
| Return Type | Description |
|---|---|
ResultSetMetaData |
This would usually be an object that contains information about the columns of the ResultSet object. However, in the current JDBC driver, this method does not return any value and will throw an SQLFeatureNotSupportedException. |
Exceptions:
SQLFeatureNotSupportedException: This exception is always thrown when calling this method in the current JDBC driver since the feature is not yet supported.
getParameterMetaData
public ParameterMetaData getParameterMetaData()
throws SQLException
The getParameterMetaData() method of the PreparedStatement object in the JDBC Driver retrieves a ParameterMetaData object. This object provides detailed information about the number, types, and properties of the parameters in a PreparedStatement object. It allows developers to gain insight into the specifics of parameters such as their type, precision, scale, etc.
Parameters:
This method doesn’t take any parameters.
Return Values:
| Return Type | Description |
|---|---|
ParameterMetaData |
An object that provides information about the parameters of the PreparedStatement. |
Exceptions:
SQLException: If a database access error occurs or this method is called on a closedPreparedStatement.
Usage:
try (
Connection connection = DriverManager.getConnection( "jdbc:polypheny://localhost:20590" );
PreparedStatement preparedStatement = connection.prepareStatement( "INSERT INTO my_table (name, age) VALUES (?, ?)" );
) {
preparedStatement.setString( 1, "Alice" );
preparedStatement.setInt( 2, 30 );
ParameterMetaData paramMetaData = preparedStatement.getParameterMetaData();
int paramCount = paramMetaData.getParameterCount();
for ( int i = 1; i <= paramCount; i++ ) {
System.out.println( "Parameter Type for index " + i + ": " + paramMetaData.getParameterTypeName( i ) );
}
} catch ( SQLException e ) {
e.printStackTrace();
}
Setter methods for value
The table below lists all setter methods that can be used to assign values to the placeholders of a statement. Please note that the placeholders are numbered starting with 1.
| Signature | Description |
|---|---|
setArray( int parameterIndex, Array x ) |
Sets an Array value. |
setAsciiStream( int parameterIndex, InputStream x, int length ) |
Sets an ASCII stream value with a specified length. |
setAsciiStream( int parameterIndex, InputStream x, long length ) |
Sets an ASCII stream value with a specified long length. |
setAsciiStream( int parameterIndex, InputStream x ) |
Sets an ASCII stream value. |
setBigDecimal( int parameterIndex, BigDecimal x ) |
Sets a BigDecimal value. |
setBinaryStream( int parameterIndex, InputStream x, int length ) |
Sets a binary stream value with a specified length. |
setBinaryStream( int parameterIndex, InputStream x, long length ) |
Sets a binary stream value with a specified long length. |
setBinaryStream( int parameterIndex, InputStream x ) |
Sets a binary stream value. |
setBlob( int parameterIndex, Blob x ) |
Sets a Blob value. |
setBlob( int parameterIndex, InputStream inputStream, long length ) |
Sets a Blob from an input stream with a specified long length. |
setBlob( int parameterIndex, InputStream inputStream ) |
Sets a Blob from an input stream. |
setBoolean( int parameterIndex, boolean x ) |
Sets a boolean value. |
setByte( int parameterIndex, byte x ) |
Sets a byte value. |
setBytes( int parameterIndex, byte[] x ) |
Sets a byte array value. |
setCharacterStream( int parameterIndex, Reader reader, int length ) |
Sets a character stream value with a specified length. |
setCharacterStream( int parameterIndex, Reader reader, long length ) |
Sets a character stream value with a specified long length. |
setCharacterStream( int parameterIndex, Reader reader ) |
Sets a character stream value. |
setClob( int parameterIndex, Clob x ) |
Sets a Clob value. |
setClob( int parameterIndex, Reader reader, long length ) |
Sets a Clob from a reader with a specified long length. |
setClob( int parameterIndex, Reader reader ) |
Sets a Clob from a reader. |
setDate( int parameterIndex, Date x ) |
Sets a Date value. |
setDate( int parameterIndex, Date x, Calendar cal ) |
Sets a Date value using a specific calendar. |
setDouble( int parameterIndex, double x ) |
Sets a double value. |
setFloat( int parameterIndex, float x ) |
Sets a float value. |
setInt( int parameterIndex, int x ) |
Sets an int value. |
setLong( int parameterIndex, long x ) |
Sets a long value. |
setNCharacterStream( int parameterIndex, Reader value, long length ) |
Sets an N-character stream value with a specified long length. |
setNCharacterStream( int parameterIndex, Reader value ) |
Sets an N-character stream value. |
setNClob( int parameterIndex, NClob value ) |
Sets an NClob value. |
setNClob( int parameterIndex, Reader reader, long length ) |
Sets an NClob from a reader with a specified long length. |
setNClob( int parameterIndex, Reader reader ) |
Sets an NClob from a reader. |
setNString( int parameterIndex, String value ) |
Sets an N-string value. |
setNull( int parameterIndex, int sqlType ) |
Sets a null value with a specified SQL type. |
setNull( int parameterIndex, int sqlType, String typeName ) |
Sets a null value with a specified SQL type and type name. |
setObject( int parameterIndex, Object x, int targetSqlType ) |
Sets an object value with a specified target SQL type. |
setObject( int parameterIndex, Object x ) |
Sets an object value. |
setRef( int parameterIndex, Ref x ) |
Sets a Ref value. |
setRowId( int parameterIndex, RowId x ) |
Sets a RowId value. |
setSQLXML( int parameterIndex, SQLXML xmlObject ) |
Sets an SQLXML value. |
setShort( int parameterIndex, short x ) |
Sets a short value. |
setString( int parameterIndex, String x ) |
Sets a string value. |
setTime( int parameterIndex, Time x ) |
Sets a Time value. |
setTime( int parameterIndex, Time x, Calendar cal ) |
Sets a Time value using a specific calendar. |
setTimestamp( int parameterIndex, Timestamp x ) |
Sets a Timestamp value. |
setTimestamp( int parameterIndex, Timestamp x, Calendar cal ) |
Sets a Timestamp value using a specific calendar. |
setURL( int parameterIndex, URL x ) |
Sets a URL value. |
setUnicodeStream( int parameterIndex, InputStream x, int length ) |
Sets a Unicode stream value with a specified length. (Note: This method is deprecated in many JDBC versions). |
