Package de.hshannover.dqgui.execution.database.util

Class NamedParameterStatement

java.lang.Object

de.hshannover.dqgui.execution.database.util.NamedParameterStatement

All Implemented Interfaces:

java.lang.AutoCloseable

public class NamedParameterStatement
extends java.lang.Object
implements java.lang.AutoCloseable

This class wraps around a PreparedStatement and allows the programmer to set parameters by name instead of by index. This eliminates any confusion as to which parameter index represents what. This also means that rearranging the SQL statement or adding a parameter doesn't involve renumbering your indices. Code such as this: Connection con=getConnection(); String query="select * from my_table where name=? or address=?"; PreparedStatement p=con.prepareStatement(query); p.setString(1, "bob"); p.setString(2, "123 terrace ct"); ResultSet rs=p.executeQuery(); can be replaced with: Connection con=getConnection(); String query="select * from my_table where name=:name or address=:address"; NamedParameterStatement p=new NamedParameterStatement(con, query); p.setString("name", "bob"); p.setString("address", "123 terrace ct"); ResultSet rs=p.executeQuery();

Constructor Summary

Constructors

Constructor	Description
NamedParameterStatement(java.sql.Connection connection, java.lang.String query)	Creates a NamedParameterStatement.
NamedParameterStatement(java.sql.Connection connection, java.lang.String query, java.lang.String primaryKeyColumnName)	Creates a NamedParameterStatement.

Method Summary

Modifier and Type	Method	Description
NamedParameterStatement	addBatch()	Adds the current set of parameters as a batch entry.
void	close()	Closes the statement.
boolean	execute()	Executes the statement.
int[]	executeBatch()	Executes all of the batched statements.
java.sql.ResultSet	executeQuery()	Executes the statement, which must be a query.
int	executeUpdate()	Executes the statement, which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement.
java.sql.ResultSet	executeUpdateAndGetGeneratedKeys()	Executes the statement, which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement.
java.sql.PreparedStatement	getStatement()	Returns the underlying statement.
NamedParameterStatement	setInt(java.lang.String name, int value)	Sets a parameter.
NamedParameterStatement	setLong(java.lang.String name, long value)	Sets a parameter.
NamedParameterStatement	setObject(java.lang.String name, java.lang.Object value)	Sets a parameter.
NamedParameterStatement	setOptionally(java.lang.String name, java.lang.Object value)	Sets a parameter if it exists.
NamedParameterStatement	setString(java.lang.String name, java.lang.String value)	Sets a parameter.
NamedParameterStatement	setTimestamp(java.lang.String name, java.sql.Timestamp value)	Sets a parameter.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

NamedParameterStatement

public NamedParameterStatement(java.sql.Connection connection,
                               java.lang.String query)

Creates a NamedParameterStatement. Wraps a call to c.prepareStatement.

Parameters:

connection - the database connection

query - the parameterized query

NamedParameterStatement

public NamedParameterStatement(java.sql.Connection connection,
                               java.lang.String query,
                               java.lang.String primaryKeyColumnName)

Creates a NamedParameterStatement. Wraps a call to c.prepareStatement.

Parameters:

connection - the database connection

query - the parameterized query

primaryKeyColumnName - if primary key should be provided

Method Detail

setObject

public NamedParameterStatement setObject(java.lang.String name,
                                         java.lang.Object value)

Sets a parameter.

Parameters:

name - parameter name

value - parameter value

Returns:

this for fluent api

See Also:

PreparedStatement.setObject(int, java.lang.Object)

setString

public NamedParameterStatement setString(java.lang.String name,
                                         java.lang.String value)

Sets a parameter.

Parameters:

name - parameter name

value - parameter value

Returns:

this for fluent api

See Also:

PreparedStatement.setString(int, java.lang.String)

setInt

public NamedParameterStatement setInt(java.lang.String name,
                                      int value)

Sets a parameter.

Parameters:

name - parameter name

value - parameter value

Returns:

this for fluent api

Throws:

java.lang.IllegalArgumentException - if the parameter does not exist

See Also:

PreparedStatement.setInt(int, int)

setLong

public NamedParameterStatement setLong(java.lang.String name,
                                       long value)

Sets a parameter.

Parameters:

name - parameter name

value - parameter value

Returns:

this for fluent api

Throws:

java.lang.IllegalArgumentException - if the parameter does not exist

See Also:

PreparedStatement.setInt(int, int)

setTimestamp

public NamedParameterStatement setTimestamp(java.lang.String name,
                                            java.sql.Timestamp value)

Sets a parameter.

Parameters:

name - parameter name

value - parameter value

Returns:

this for fluent api

Throws:

java.lang.IllegalArgumentException - if the parameter does not exist

See Also:

PreparedStatement.setTimestamp(int, java.sql.Timestamp)

setOptionally

public NamedParameterStatement setOptionally(java.lang.String name,
                                             java.lang.Object value)

Sets a parameter if it exists.

Parameters:

name - parameter name

value - parameter value

Returns:

this for fluent api

getStatement

public java.sql.PreparedStatement getStatement()

Returns the underlying statement.

Returns:

the statement

execute

public boolean execute()

Executes the statement.

Returns:

true if the first result is a ResultSet

See Also:

PreparedStatement.execute()

executeQuery

public java.sql.ResultSet executeQuery()

Executes the statement, which must be a query.

Returns:

the query results

See Also:

PreparedStatement.executeQuery()

executeUpdate

public int executeUpdate()

Executes the statement, which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement.

Returns:

number of rows affected

See Also:

PreparedStatement.executeUpdate()

executeUpdateAndGetGeneratedKeys

public java.sql.ResultSet executeUpdateAndGetGeneratedKeys()

Executes the statement, which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement. Additionally returns a ResultSet containing the generated keys.

Returns:

generated keys

Throws:

java.lang.IllegalArgumentException - if called without setting a primary key column within the constructor first

See Also:

PreparedStatement.executeUpdate()

close

public void close()

Closes the statement.

Specified by:

close in interface java.lang.AutoCloseable

See Also:

Statement.close()

addBatch

public NamedParameterStatement addBatch()

Adds the current set of parameters as a batch entry.

Returns:

this for fluent api

executeBatch

public int[] executeBatch()

Executes all of the batched statements. See Statement.executeBatch() for details.

Returns:

update counts for each statement