com.groiss.store
Class BulkQuery

java.lang.Object
  com.groiss.store.BulkQuery

public class BulkQuery
extends Object

Allows efficient execution of queries for a set of items.

Suppose one has a Collection of items and wants to pose a query to the database for each of those items. This can be quite a performance and throughput hog, since the overhead of a query execution is payed for each single item:

 select 1 from atable where y='red' and x=1;
 select 1 from atable where y='red' and x=2;
 select 1 from atable where y='red' and x=3;
 ...
 

Often, the situation can be dealt with quite efficiently by using an IN-query.

 select x from atable where y='red' and x IN (1,2,3,...);
 

The BulkQuery class provides a convenient abstraction to use this metaphor. The typical use would be as follows:

• First a BulkQuery instance is created.
• Then, the parameters which are different for each item are added via add.
• Afterwards, the query is specified along with the position (1-based) of the column of the result set one is interested in; this typically is 1.
• The result of the query execution is a Set instance which can be used to determine if the query had a result for a particular item.

Example:

 BulkQuery bQ = new BulkQuery();
 for (Enumeration elems = v.elements(); elems.hasMoreElements();) {
   MyElement e = (MyElement) elems.nextElement();
   bQ.add(Long.toString(MyElement.getOid()));
 }
 String query = "select x from atable where y='red' and x IN(?)";
 Set result = bQ.executeSet(query,1);
 for (Enumeration elems = v.elements(); elems.hasMoreElements();) {
   MyElement e = (MyElement) elems.nextElement();
   if (result.contains(Long.toString(MyElement.getOid()))) {
     ...
   } else {
     ...
 }
 

But the BulkQuery can also be used if a collection of persistent objects should be retrieved using an IN-query for better performance. The usage is quite similar to the example above, only a different 'execute' method (execute(Class,String)) must be used to achieve this goal.

Example:

 BulkQuery bQ = new BulkQuery();
 for (Enumeration elems = v.elements(); elems.hasMoreElements();) {
   MyElement e = (MyElement) elems.nextElement();
   MyReferencedElement ref = e.getReferencedElement();
   bQ.add(Long.toString(ref.getOid()));
 }
 String condition = "oid in (?)";
 Collection result = bQ.execute(MyReferencedElement.class, condition);
 ...
 

If you happen to have a conveniently structured pre-splitted list of values already at hand (like the splitResult when Worklist.getAdditionalData(List, List) is called), you can construct a BulkQuery with the alternative Constructor. The elements of the Collection are Strings which contain comma separated values.

Example:

 BulkQuery bQ = new BulkQuery(splitResult,true);
 String aquery = "select oid from a_table where process in (?)";
 Map result1 = bQ.execute(aquery,1);
 ...
 String bquery = "select oid from b_table where process in (?)";
 Map result2 = bQ.execute(bquery,1);
 ...
 

For all execute methods there exists a variant with an extended parameter list where an object array containing additional bind variables can be specified. These variables are incorporated in the query as parameters in a prepared JDBC statement.

Since the placeholders for the JDBC bind variables must be specified by ?, the in-list in a query given to such an execute method must be exactly denoted by IN(?) (in contrast to the case without bind variables where it can be denoted just by a single ?). It is recommended to use the IN constant, since it provides a value which is appropriate in every case.

Example:

 BulkQuery bQ = new BulkQuery();
 for (Enumeration elems = v.elements(); elems.hasMoreElements();) {
   MyElement e = (MyElement) elems.nextElement();
   bQ.add(Long.toString(MyElement.getOid()));
 }
 String query = "select x from atable where started>=? and started <= ? and x "+BulkQuery.IN";
 Map result = bQ.execute(query,1,new Object[]{fromDate,toDate});
 for (Enumeration elems = v.elements(); elems.hasMoreElements();) {
   MyElement e = (MyElement) elems.nextElement();
   if (result.containsKey(Long.toString(MyElement.getOid()))) {
     ...
   } else {
     ...
 }
 

Note: due to restrictions in the implementations of SQL by the various DBMS, a BulkQuery with a long list of IN parameters might be executed more than once. E.g. first with the first hundred in-list parameters then again with the next hundred parameters and so on. This behavior alters the semantics of the query when using NOT IN(?).

Field Summary

static String

IN Marker for the in-List in the BulkQuery

Constructor Summary

BulkQuery()
Construct a fresh BulkQuery.

BulkQuery(Collection c)
Construct a fresh BulkQuery and add each of the elements of the Collection.

BulkQuery(Collection c, boolean isSplitted)
Construct a fresh BulkQuery and add the elements of the Collection.

Method Summary

void	add(String s) Add a String to the query.
<T> Collection<T>	execute(Class<? extends T> c, String condition) Execute the condition based on the current elements.
<T> Collection<T>	execute(Class<? extends T> c, String condition, Object[] bindVars) Execute the condition based on the current elements.
Map<Object,Object>	execute(String query, int pos) Deprecated. since 8.0; use executeSet(String,int) instead
Map<Object,Object>	execute(String query, int pos, Object[] bindVars) Deprecated. since 8.0; use executeSet(String,int,Object[]) instead
<P> Collection<P>	execute2(Class<? extends P> c, String q) Execute the query based on the current elements.
<P> Collection<P>	execute2(Class<? extends P> c, String q, Object[] bindVars) Execute the query based on the current elements.
Set<String>	executeSet(String query, int pos) Execute the query based on the current elements.
Set<String>	executeSet(String query, int pos, Object[] bindVars) Execute the query based on the current elements.
static int	getSplitSize()

Methods inherited from class java.lang.Object

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

Field Detail

IN

public static final String IN

Marker for the in-List in the BulkQuery

See Also:

Constant Field Values

Constructor Detail

BulkQuery

public BulkQuery()

Construct a fresh BulkQuery.

BulkQuery

public BulkQuery(Collection c)

Construct a fresh BulkQuery and add each of the elements of the Collection. The elements must either be String objects or implement the Persistent interface.

Parameters:

c - the Collection whose elements are added to the BulkQuery.

BulkQuery

public BulkQuery(Collection c,
                 boolean isSplitted)

Construct a fresh BulkQuery and add the elements of the Collection.

Parameters:

c - the Collection whose elements are added to the BulkQuery.

isSplitted - if false, each of the (String or Persistent) elements of the Collection c are added; behaves like BulkQuery(Collection). If isSplitted is true, the Collection is assumed to be already splitted to a convenient size. Each of the elements of the Collection must be a String in the form of a comma separated list of values. No single String should have more than getSplitSize() values, else the query might fail.

Method Detail

add

public void add(String s)

Add a String to the query.

Parameters:

s - the String to be added.

getSplitSize

public static int getSplitSize()

execute

@Deprecated
public Map<Object,Object> execute(String query,
                                             int pos)

Deprecated. since 8.0; use executeSet(String,int) instead

Execute the query based on the current elements.

The query text must be a valid SQL statement. A question mark ? value in this string indicates the position of the varying parameters (the IN-List). The pos argument indicates which column of the SQL ResultSet should be used to construct the returned Map object.

Parameters:

query - The query text.

pos - The position in the queries ResultSet which should be used for construction of the result.

Returns:

A Map of the results of the query..

executeSet

public Set<String> executeSet(String query,
                              int pos)

Execute the query based on the current elements.

The query text must be a valid SQL statement. A question mark ? value in this string indicates the position of the varying parameters (the IN-List). The pos argument indicates which column of the SQL ResultSet should be used to construct the returned Set object.

Parameters:

query - The query text.

pos - The position in the queries ResultSet which should be used for construction of the result.

Returns:

A Set of the results of the query..

execute

@Deprecated
public Map<Object,Object> execute(String query,
                                             int pos,
                                             Object[] bindVars)

Deprecated. since 8.0; use executeSet(String,int,Object[]) instead

Execute the query based on the current elements.

The query text must be a valid SQL statement. The substring IN(?) in this string indicates the position of the varying parameters (the IN-List). The pos argument indicates which column of the SQL ResultSet should be used to construct the returned Map object.

Parameters:

query - The query text.

pos - The position in the queries ResultSet which should be used for construction of the result.

bindVars - if the condition contains place holders for binding variables this array should contain the values

Returns:

A Map of the results of the query..

executeSet

public Set<String> executeSet(String query,
                              int pos,
                              Object[] bindVars)

Execute the query based on the current elements.

The query text must be a valid SQL statement. The substring IN(?) in this string indicates the position of the varying parameters (the IN-List). The pos argument indicates which column of the SQL ResultSet should be used to construct the returned Set object.

Parameters:

query - The query text.

pos - The position in the queries ResultSet which should be used for construction of the result.

bindVars - if the condition contains place holders for binding variables this array should contain the values

Returns:

A Set of the results of the query..

execute

public <T> Collection<T> execute(Class<? extends T> c,
                                 String condition)

Execute the condition based on the current elements.

The condition text must be a valid SQL condition statement (without keyword 'where'). A question mark ? value in this string indicates the position of the varying parameters (the IN-List). The c argument indicates on which persistent object the condition should be applied

Parameters:

c - the class which instances are wanted as the result (c must implement PersistentObject)

condition - the condition for filtering within the instances.

Returns:

A collection of instances of the passed class which match the specified condition

execute

public <T> Collection<T> execute(Class<? extends T> c,
                                 String condition,
                                 Object[] bindVars)

Execute the condition based on the current elements.

The condition text must be a valid SQL condition statement (without keyword 'where'). The substring IN(?) in this string indicates the position of the varying parameters (the IN-List). The c argument indicates on which persistent object the condition should be applied

Parameters:

c - the class which instances are wanted as the result (c must implement PersistentObject)

condition - the condition for filtering within the instances.

bindVars - if the condition contains place holders for binding variables this array should contain the values

Returns:

A collection of instances of the passed class which match the specified condition

execute2

public <P> Collection<P> execute2(Class<? extends P> c,
                                  String q)

Execute the query based on the current elements.

The query text must be a valid SQL query. A question mark ? value in this string indicates the position of the varying parameters (the IN-List). The c argument indicates on which persistent object the query should be applied

Parameters:

c - the class which instances are wanted as the result (c must implement PersistentObject)

q - a SQL query like in Store.list2(Class, String, Object[])

Returns:

A collection of instances of the passed class which match the specified condition

execute2

public <P> Collection<P> execute2(Class<? extends P> c,
                                  String q,
                                  Object[] bindVars)

Execute the query based on the current elements.

The query text must be a valid SQL query. The substring IN(?) in this string indicates the position of the varying parameters (the IN-List). The c argument indicates on which persistent object the query should be applied

Parameters:

c - the class which instances are wanted as the result (c must implement PersistentObject)

q - a SQL query like in Store.list2(Class, String, Object[])

bindVars - if the condition contains place holders for binding variables this array should contain the values

Returns:

A collection of instances of the passed class which match the specified condition