public class CSPreparedStatementBuilder
extends java.lang.Object
The order in which parameters are added to the query is important. Given the following query:
psb.append("SELECT *");
psb.append(" FROM customers c");
psb.append(" WHERE c.age IN (?)");
psb.append(" AND c.name = ?");
The parameters must be added in the same order or an SQLException will be thrown upon calling
createPreparedStatement(boolean)
, e.g.
psb.addInStatementParam(JDBCType.INTEGER, listOfInts);
psb.addParam(theNameAsStr);
Modifier and Type | Class and Description |
---|---|
static class |
CSPreparedStatementBuilder.JDBCType
The JDBC types that the CSPreparedStatementBuilder currently supports
|
Constructor and Description |
---|
CSPreparedStatementBuilder(java.sql.Connection con,
CSDatabaseConnectionPool dbConPool)
Creates a
PreparedStatementObject specifying the connection and the connection's connection pool. |
CSPreparedStatementBuilder(java.sql.Connection con,
CSDatabaseConnectionPool dbConPool,
java.lang.String propertyFile)
Creates a
PreparedStatementObject , specifying the connection, the connection's connection pool, and
a path to the property file used by the API that contains all of the database connection details. |
Modifier and Type | Method and Description |
---|---|
void |
addInStmtParam(CSPreparedStatementBuilder.JDBCType type,
CSDelimList param)
Adds an IN statement parameter, supplying a list of values and their type.
|
void |
addLongStringParam(java.lang.String param)
Adds a parameter of type LongString.
|
void |
addNullParam(CSPreparedStatementBuilder.JDBCType type) |
void |
addParam(java.math.BigDecimal param)
Adds a parameter of type BigDecimal.
|
void |
addParam(java.lang.Boolean param)
Adds a parameter of type Boolean.
|
void |
addParam(java.sql.Clob param)
Adds a parameter of type Clob.
|
void |
addParam(java.sql.Date param)
Adds a parameter of type java.sql.Date.
|
void |
addParam(java.util.Date param)
Adds a parameter of type java.util.Date.
|
void |
addParam(java.lang.Double param)
Adds a parameter of type Double.
|
void |
addParam(java.lang.Float param)
Adds a parameter of type Float.
|
void |
addParam(java.lang.Integer param)
Adds a parameter of type Integer.
|
void |
addParam(java.lang.Long param)
Adds a parameter of type Long.
|
void |
addParam(java.lang.Short param)
Adds a parameter of type Short.
|
void |
addParam(java.lang.String param)
Adds a parameter of type String.
|
void |
addParam(java.sql.Time param)
Adds a parameter of type Time.
|
void |
addParam(java.sql.Timestamp param)
Adds a parameter of type Timestamp.
|
void |
addTypedParam(CSPreparedStatementBuilder.JDBCType type,
java.lang.Object param)
Adds a parameter of the given type to the query.
|
void |
addTypedParam(CSPreparedStatementBuilder.JDBCType type,
java.lang.Object param,
java.util.Map<java.lang.String,java.lang.Object> extraData)
Adds a parameter of the given type to the query along with some optional extra data stored in a hash map, e.g.
|
void |
appendLongInStmt(java.lang.String inClause,
CSPreparedStatementBuilder.JDBCType type,
CSDelimList values)
Constructs an IN statement, splitting IN statements with over 1000 values (Oracle's limit per IN statement)
into multiple IN statements.
|
void |
appendPreparedStatementBuilder(CSPreparedStatementBuilder psb)
Appends the SQL and parameters contained in the provided
CSPreparedStatementBuilder instance to this instance,
retaining parameter order. |
CSPreparedStatementBuilder |
appendSQL(java.lang.String sql)
Appends the given string to the internal SQL buffer.
|
void |
clearParameters()
Resets all parameters in this
CSPreparedStatementBuilder instance. |
void |
clearSQL()
Clears the SQL in this
CSPreparedStatementBuilder instance. |
java.sql.PreparedStatement |
createPreparedStatement(boolean resetPSB)
Returns a new PreparedStatement built from the SQL string and parameters provided.
|
java.sql.PreparedStatement |
createPreparedStatement(boolean resetPSB,
int resultSetType,
int resultSetConcurrency)
Returns a new PreparedStatement built from the SQL string and parameters provided.
|
void |
dispose()
Clears up any resources used by the
CSPreparedStatementBuilder and the
PreparedStatement s that it has generated. |
java.util.List<gi.controlsite.objects.CSPreparedStatementBuilder.Parameter> |
getParameters()
Returns the list of parameters for this
CSPreparedStatement . |
java.lang.String |
getSQL()
Returns the SQL currently held within the internal SQL buffer.
|
CSPreparedStatementBuilder |
prependSQL(java.lang.String sql)
Prepends the given string to the head of the internal SQL buffer.
|
void |
reset()
Resets the
CSPreparedStatementBuilder instance so that it
may be used again to generate another PreparedStatement. |
void |
setSQL(java.lang.String sql)
Sets the internal SQL buffer to the given string.
|
java.lang.String |
toString() |
public CSPreparedStatementBuilder(java.sql.Connection con, CSDatabaseConnectionPool dbConPool) throws java.sql.SQLException
PreparedStatementObject
specifying the connection and the connection's connection pool.
IMPORTANT NOTE: This constructor does not supply a property file path and as such JDBCType.LongString cannot be used as the class does not know how to stream characters into such a field for the given database (Postgres / Oracle / MSSQL all use different types of character streams.)
con
- A connection.dbConPool
- The connection's connection pool.java.sql.SQLException
- SQLExceptionpublic CSPreparedStatementBuilder(java.sql.Connection con, CSDatabaseConnectionPool dbConPool, java.lang.String propertyFile) throws java.sql.SQLException
PreparedStatementObject
, specifying the connection, the connection's connection pool, and
a path to the property file used by the API that contains all of the database connection details.con
- A connection.dbConPool
- The connection's connection pool.propertyFile
- The path to the property file.java.sql.SQLException
- SQLExceptionpublic java.sql.PreparedStatement createPreparedStatement(boolean resetPSB, int resultSetType, int resultSetConcurrency) throws java.sql.SQLException
resetPSB
- True to reset this CSPreparedStatementBuilder instance after constructing the prepared statement, false not to.resultSetType
- One of ResultSet.TYPE_FORWARD_ONLY
(default), ResultSet.TYPE_SCROLL_INSENSITIVE
, or ResultSet.TYPE_SCROLL_SENSITIVE
.resultSetConcurrency
- One of ResultSet.CONCUR_READ_ONLY
(default) or ResultSet.CONCUR_UPDATABLE
.java.sql.SQLException
- SQLExceptionpublic java.sql.PreparedStatement createPreparedStatement(boolean resetPSB) throws java.sql.SQLException
Defaults to use ResultSet.TYPE_FORWARD_ONLY
for the result set type and ResultSet.CONCUR_READ_ONLY
for result set concurrency.
resetPSB
- True to reset this CSPreparedStatementBuilder instance after constructing the prepared statement, false not to.java.sql.SQLException
- SQLExceptionpublic void addParam(java.lang.String param)
The order in which statements are added must match the order of the "?"s in the SQL query. See class doc for more information.
param
- The parameter value.public void addParam(java.lang.Boolean param)
The order in which statements are added must match the order of the "?"s in the SQL query. See class doc for more information.
param
- The parameter value.public void addParam(java.sql.Clob param)
The order in which statements are added must match the order of the "?"s in the SQL query. See class doc for more information.
param
- The parameter value.public void addParam(java.lang.Short param)
The order in which statements are added must match the order of the "?"s in the SQL query. See class doc for more information.
param
- The parameter value.public void addParam(java.lang.Integer param)
The order in which statements are added must match the order of the "?"s in the SQL query. See class doc for more information.
param
- The parameter value.public void addParam(java.lang.Long param)
The order in which statements are added must match the order of the "?"s in the SQL query. See class doc for more information.
param
- The parameter value.public void addParam(java.lang.Float param)
The order in which statements are added must match the order of the "?"s in the SQL query. See class doc for more information.
param
- The parameter value.public void addParam(java.lang.Double param)
The order in which statements are added must match the order of the "?"s in the SQL query. See class doc for more information.
param
- The parameter value.public void addParam(java.math.BigDecimal param)
The order in which statements are added must match the order of the "?"s in the SQL query. See class doc for more information.
param
- The parameter value.public void addParam(java.sql.Date param)
The order in which statements are added must match the order of the "?"s in the SQL query. See class doc for more information.
param
- The parameter value.public void addParam(java.util.Date param)
The order in which statements are added must match the order of the "?"s in the SQL query. See class doc for more information.
param
- The parameter value.public void addParam(java.sql.Time param)
The order in which statements are added must match the order of the "?"s in the SQL query. See class doc for more information.
param
- The parameter value.public void addParam(java.sql.Timestamp param)
The order in which statements are added must match the order of the "?"s in the SQL query. See class doc for more information.
param
- The parameter value.public void addLongStringParam(java.lang.String param)
The order in which statements are added must match the order of the "?"s in the SQL query. See class doc for more information.
param
- The parameter value.public void addInStmtParam(CSPreparedStatementBuilder.JDBCType type, CSDelimList param)
psb.append(" WHERE c.age IN (?)");
psb.addInStatementParam(JDBCType.INTEGER, listOfInts);
Note that IN statements are special in that a ? must be supplied per value, e.g.
for a list of three parameters the SQL statement must read "WHERE c.age IN (?,?,?)
".
Calling this function with a list will expand the single (?)
into a ? for each item in the list.
Simply calling addParam("1,2,3") will not work.
Supported JDBC types: STRING, LONGSTRING, BOOLEAN, SHORT, INTEGER, LONG, FLOAT, DOUBLE, and BIGDECIMAL.
The order in which statements are added must match the order of the "?"s in the SQL query. See class doc for more information.
Note: CSDelimList isn't a typed List, and instead just overrides add(int)
to return add(String.valueOf(i));
.
This class assumes that it is a List of strings.
type
- The JDBCType that corresponds to the type of each of the values in the list.param
- A list of parameters, typically a CSDelimList.public void addNullParam(CSPreparedStatementBuilder.JDBCType type)
public void addTypedParam(CSPreparedStatementBuilder.JDBCType type, java.lang.Object param)
The order in which statements are added must match the order of the "?"s in the SQL query. See class doc for more information.
type
- The JDBCType that corresponds to the type of the parameter value.param
- The parameter value.public void addTypedParam(CSPreparedStatementBuilder.JDBCType type, java.lang.Object param, java.util.Map<java.lang.String,java.lang.Object> extraData)
The order in which statements are added must match the order of the "?"s in the SQL query. See class doc for more information.
type
- The JDBCType that corresponds to the type of the parameter value.param
- The parameter value.extraData
- Optional extra data stored in a hash map. e.g. type information for an INSTATEMENT.public void appendPreparedStatementBuilder(CSPreparedStatementBuilder psb)
CSPreparedStatementBuilder
instance to this instance,
retaining parameter order.psb
- The CSPreparedStatementBuilder
public CSPreparedStatementBuilder appendSQL(java.lang.String sql)
sql
- The string to be appended to the internal SQL buffer.public CSPreparedStatementBuilder prependSQL(java.lang.String sql)
sql
- The string to be appended to the internal SQL buffer.public void appendLongInStmt(java.lang.String inClause, CSPreparedStatementBuilder.JDBCType type, CSDelimList values) throws java.sql.SQLException
Example:
psb.appendSQL(" WHERE ");
psb.appendLongInStmt("ObjectID IN", JDBCType.INTEGER, m_objObjectList);
inClause
- The IN clause to use, e.g. "ObjectID IN"
type
- The JDBCType of the values stored in the CSDelimList.values
- A CSDelimList of values to be inserted into the IN statement.java.sql.SQLException
- SQLExceptionpublic void setSQL(java.lang.String sql)
sql
- The string to be set as SQL.public java.lang.String getSQL()
public java.util.List<gi.controlsite.objects.CSPreparedStatementBuilder.Parameter> getParameters()
CSPreparedStatement
.CSPreparedStatement
.public void reset()
CSPreparedStatementBuilder
instance so that it
may be used again to generate another PreparedStatement.
Clears all parameters and resets the SQL, along with all other relevant state.
public void clearParameters()
CSPreparedStatementBuilder
instance.public void clearSQL()
CSPreparedStatementBuilder
instance.public void dispose() throws java.sql.SQLException
CSPreparedStatementBuilder
and the
PreparedStatement
s that it has generated.
All PreparedStatements (and their ResultSets) built by this instance are closed.
java.sql.SQLException
- SQLExceptionpublic java.lang.String toString()
toString
in class java.lang.Object
Copyright © 2022 Goss Interactive Ltd. All Rights Reserved.