com.healthmarketscience.sqlbuilder
Class QueryPreparer

java.lang.Object
  extended by 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.ListPlaceHolder
          A SqlObject which outputs 0 or more '?' separated by commas, and records the current indexes at the times the appendTo method is called.
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.ListPlaceHolder getNewListPlaceHolder()
           
 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

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.

getNewListPlaceHolder

public QueryPreparer.ListPlaceHolder getNewListPlaceHolder()
Returns:
a new ListPlaceHolder 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.

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

toString

public String toString()
outputs the static place holders in order

Overrides:
toString in class Object


Copyright © 2006–2016 Health Market Science. All rights reserved.