com.healthmarketscience.sqlbuilder
Class QueryPreparer

java.lang.Object
  com.healthmarketscience.sqlbuilder.QueryPreparer

public class QueryPreparer
extends Object

Helper class which keeps track of '?' positions in dynamically generated prepared statements so that the query user can easily set the parameters correctly, especially useful where the code which generates the prepared query is separate from the code which uses the prepared query. A QueryPreparer may only be used for a single query generation. Also, the PlaceHolders will not have a valid position until the query is actually converted to a string. After that, the PlaceHolders will never change their stored index(es), so reuse in a new query is impossible. Likewise, the state of the QueryPreparer is altered by the query generation, so, it cannot be used in a new query either. However, the QueryPreparer utility is designed so that it is not modified after query generation, so it can safely be used concurrently with the original query string as long as desired (so it can safely be used in a static context along with the associated query string). There are two main types of placeholders supported normal (dynamic) and static. Normal placeholders consist of two sub-types (the QueryPreparer.PlaceHolder and QueryPreparer.MultiPlaceHolder classes) and should be used for values which are going to change for each invocation of the PreparedStatement. A PlaceHolder may only be used in one place in a query, while a MultiPlaceHolder can be re-used multiple times in the same query. Static placeholders (subclasses of the QueryPreparer.StaticPlaceHolder class) are a convenience placeholder which can be used for values which will not change across invocations of a PreparedStatement (and can also only be used in one place in a query, like a PlaceHolder). Examples:

 
   // example using placeholders where another class is generating
   // the actual query
   QueryPreparer preparer = new QueryPreparer();
   QueryPreparer.PlaceHolder param1PH = preparer.getNewPlaceHolder();
   QueryPreparer.PlaceHolder param2PH = preparer.getNewPlaceHolder();
   String prepQueryStr = otherObj.createQuery(param1PH, param2PH);
   PreparedStatement ps = conn.prepareStatement(prepQueryStr);
   for(Param param : paramList) {
     param1PH.setLong(param.getValue1(), ps);
     param2PH.setString(param.getValue2(), ps);
     ResultSet rs = ps.executeQuery();
     // ... parse results ...
   }

   // example using static placeholders and regular placeholders where
   // this class is generating the query
   Long param1 = 13;
   String param2 = "foo";
   QueryPreparer preparer = new QueryPreparer();
   QueryPreparer.PlaceHolder param3PH = preparer.getNewPlaceHolder();
   String prepQueryStr = new SelectQuery()
     .addAllColumns()
     .addCustomTable(table)
     .setCondition(
       ComboCondition.and(
         BinaryCondition.eq(col1,
                            preparer.addStaticPlaceHolder(param1)),
         BinaryCondition.eq(col2,
                            preparer.addStaticPlaceHolder(param2)),
         BinaryCondition.eq(col3, param3PH)))
     .validate.toString();
   PreparedStatement ps = conn.prepareStatement(prepQueryStr);
   for(String param3 : param3List) {
     preparer.setStaticValues(ps);  // sets param1, param2
     param3PH.setString(param3, ps);
     ResultSet rs = ps.executeQuery();
     // ... parse results ...
   }
   
 

Author:

James Ahlborn

Nested Class Summary

static class	QueryPreparer.BooleanStaticPlaceHolder StaticPlaceHolder which calls setInt on the PreparedStatement with the saved value.
static class	QueryPreparer.IntegerStaticPlaceHolder StaticPlaceHolder which calls setInt on the PreparedStatement with the saved value.
static class	QueryPreparer.LongStaticPlaceHolder StaticPlaceHolder which calls setLong on the PreparedStatement with the saved value.
static class	QueryPreparer.MultiPlaceHolder A SqlObject which outputs a '?', and records the current index at the time(s) the appendTo method is called.
static class	QueryPreparer.NullStaticPlaceHolder StaticPlaceHolder which always calls setNull on the PreparedStatement with the saved sql type.
static class	QueryPreparer.ObjectStaticPlaceHolder<ObjType> StaticPlaceHolder which calls setObject on the PreparedStatement with the saved value.
static class	QueryPreparer.PlaceHolder A SqlObject which outputs a '?', and records the current index at the time the appendTo method is called.
static class	QueryPreparer.StaticPlaceHolder Convenience PlaceHolder which also maintains a value which will always be inserted into the PreparedStatement when setValue is called.
static class	QueryPreparer.StringStaticPlaceHolder StaticPlaceHolder which calls setString on the PreparedStatement with the saved value.
static class	QueryPreparer.TypedStaticPlaceHolder StaticPlaceHolder which calls setObject on the PreparedStatement with the saved value and the saved sql type.

Field Summary

static int

DEFAULT_START_INDEX the default first index that will be assigned to a placeholder

Constructor Summary

QueryPreparer()

QueryPreparer(int startIndex)
Creates a QueryPreparer with a different startIndex from the default.

Method Summary

QueryPreparer.StaticPlaceHolder	addStaticPlaceHolder(boolean val)
QueryPreparer.StaticPlaceHolder	addStaticPlaceHolder(Boolean val)
QueryPreparer.StaticPlaceHolder	addStaticPlaceHolder(int val)
QueryPreparer.StaticPlaceHolder	addStaticPlaceHolder(Integer val)
QueryPreparer.StaticPlaceHolder	addStaticPlaceHolder(long val)
QueryPreparer.StaticPlaceHolder	addStaticPlaceHolder(Long val)
QueryPreparer.StaticPlaceHolder	addStaticPlaceHolder(Object obj)
QueryPreparer.StaticPlaceHolder	addStaticPlaceHolder(Object obj, int sqlType)
QueryPreparer.StaticPlaceHolder	addStaticPlaceHolder(QueryPreparer.StaticPlaceHolder ph) Adds a new StaticPlaceHolder to the list maintained by this class
QueryPreparer.StaticPlaceHolder	addStaticPlaceHolder(String val)
QueryPreparer.MultiPlaceHolder	getNewMultiPlaceHolder()
QueryPreparer.PlaceHolder	getNewPlaceHolder()
void	setStaticValues(PreparedStatement ps) Calls QueryPreparer.StaticPlaceHolder.setValue(java.sql.PreparedStatement) on all the StaticPlaceHolders held by this class with the given PreparedStatement.
String	toString() outputs the static place holders in order

Methods inherited from class java.lang.Object

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

Field Detail

DEFAULT_START_INDEX

public static final int DEFAULT_START_INDEX

the default first index that will be assigned to a placeholder

See Also:

Constant Field Values

Constructor Detail

QueryPreparer

public QueryPreparer()

QueryPreparer

public QueryPreparer(int startIndex)

Creates a QueryPreparer with a different startIndex from the default. This may be useful if there are other parameters in the PreparedStatement which are not owned by this QueryPreparer.

Method Detail

toString

public String toString()

outputs the static place holders in order

Overrides:

toString in class Object

getNewPlaceHolder

public QueryPreparer.PlaceHolder getNewPlaceHolder()

Returns:

a new PlaceHolder tied to this QueryPreparer. Its internal state is not valid until the query is converted to an actual string. Also, it can only be used in one place in the query.

getNewMultiPlaceHolder

public QueryPreparer.MultiPlaceHolder getNewMultiPlaceHolder()

Returns:

a new MultiPlaceHolder tied to this QueryPreparer. Its internal state is not valid until the query is converted to an actual string. It can be used in multiple places in the same query.

addStaticPlaceHolder

public QueryPreparer.StaticPlaceHolder addStaticPlaceHolder(String val)

Returns:

a new StaticPlaceHolder which will always insert the given String value

See Also:

setStaticValues(java.sql.PreparedStatement)

addStaticPlaceHolder

public QueryPreparer.StaticPlaceHolder addStaticPlaceHolder(Long val)

Returns:

a new StaticPlaceHolder which will always insert the given Long value

See Also:

setStaticValues(java.sql.PreparedStatement)

addStaticPlaceHolder

public QueryPreparer.StaticPlaceHolder addStaticPlaceHolder(Integer val)

Returns:

a new StaticPlaceHolder which will always insert the given Integer value

See Also:

setStaticValues(java.sql.PreparedStatement)

addStaticPlaceHolder

public QueryPreparer.StaticPlaceHolder addStaticPlaceHolder(Boolean val)

Returns:

a new StaticPlaceHolder which will always insert the given Boolean value

See Also:

setStaticValues(java.sql.PreparedStatement)

addStaticPlaceHolder

public QueryPreparer.StaticPlaceHolder addStaticPlaceHolder(long val)

Returns:

a new StaticPlaceHolder which will always insert the given long value

See Also:

setStaticValues(java.sql.PreparedStatement)

addStaticPlaceHolder

public QueryPreparer.StaticPlaceHolder addStaticPlaceHolder(int val)

Returns:

a new StaticPlaceHolder which will always insert the given int value

See Also:

setStaticValues(java.sql.PreparedStatement)

addStaticPlaceHolder

public QueryPreparer.StaticPlaceHolder addStaticPlaceHolder(boolean val)

Returns:

a new StaticPlaceHolder which will always insert the given boolean value

See Also:

setStaticValues(java.sql.PreparedStatement)

addStaticPlaceHolder

public QueryPreparer.StaticPlaceHolder addStaticPlaceHolder(Object obj)

Returns:

a new StaticPlaceHolder which will always insert the given Object value

See Also:

setStaticValues(java.sql.PreparedStatement)

addStaticPlaceHolder

public QueryPreparer.StaticPlaceHolder addStaticPlaceHolder(Object obj,
                                                            int sqlType)

Returns:

a new StaticPlaceHolder which will always insert the given Object value as the given sql type

See Also:

setStaticValues(java.sql.PreparedStatement)

addStaticPlaceHolder

public QueryPreparer.StaticPlaceHolder addStaticPlaceHolder(QueryPreparer.StaticPlaceHolder ph)

Adds a new StaticPlaceHolder to the list maintained by this class

Returns:

the given StaticPlaceHolder

See Also:

setStaticValues(java.sql.PreparedStatement)

setStaticValues

public void setStaticValues(PreparedStatement ps)
                     throws SQLException

Calls QueryPreparer.StaticPlaceHolder.setValue(java.sql.PreparedStatement) on all the StaticPlaceHolders held by this class with the given PreparedStatement.

Throws:

SQLException