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<String> 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<String> 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) 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 parameter list 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,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

Fields

Modifier and Type	Field and Description
static String	IN Marker for the in-List in the BulkQuery

Constructor Summary

Constructors

Constructor and Description
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.
BulkQuery(int splitSize) Construct a fresh BulkQuery and set a split size

Method Summary

Modifier and Type	Method and Description
void	add(Collection<?> c, boolean isSplitted) Add the elements of the Collection to a BulkQuery.
void	add(long l) Add a long to the query.
void	add(Long l) Add a Long to the query.
void	add(Persistent p) Add the oid of a Persistent to the query.
void	add(String s) Add a String to the query.
void	addQuoted(String s) Add a String to the query.
int	count(Class<?> c, String condition, Object... bindVars) Count the number of members of a given class matching the given condition based on the current elements.
<T> List<T>	execute(Class<? extends T> c, String condition) Execute the condition based on the current elements.
<T> List<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> List<P>	execute2(Class<? extends P> c, String q) Execute the query based on the current elements.
<P> List<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.
int	getSplitSize()
int	setSplitSize(int splitSize)

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(int splitSize)

Construct a fresh BulkQuery and set a split size

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, Long 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(Collection<?> c,
                boolean isSplitted)

Add the elements of the Collection to a BulkQuery.

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.

add

public void add(String s)

Add a String to the query. The String is added without any conversions or quoting.

Parameters:

s - the String to be added.

addQuoted

public void addQuoted(String s)

Add a String to the query.

Parameters:

s - the String to be added. Single quotes within the string will be properly escaped for SQL (they will be duplicated). The string will be enclosed in single quotes.
addQuotes("Name: 'Erwin'") is the same as add("'Name: ''Erwin'''")

add

public void add(Persistent p)

Add the oid of a Persistent to the query.

Parameters:

p - the Persistent, which oid is to be added.

add

public void add(Long l)

Add a Long to the query.

Parameters:

l - the Long to be added.

add

public void add(long l)

Add a long to the query.

Parameters:

l - the long to be added.

getSplitSize

public int getSplitSize()

setSplitSize

public int setSplitSize(int splitSize)

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 parameter list 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 parameter list should contain the values

Returns:

A Set of the results of the query..

execute

public <T> List<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 list of instances of the passed class which match the specified condition

execute

public <T> List<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 parameter list should contain the values

Returns:

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

execute2

public <P> List<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 list of instances of the passed class which match the specified condition

execute2

public <P> List<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 query contains place holders for binding variables this parameter list should contain the values

Returns:

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

count

public int count(Class<?> c,
                 String condition,
                 Object... bindVars)

Count the number of members of a given class matching the given 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 query should be applied

Parameters:

c - the class which instances are counted (c must implement PersistentObject)

condition - a SQL condition like in Store.count(Class, String, Object...)

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

Returns:

The number of instances of the passed class which match the specified condition