Package 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 Details

  • Constructor Details

    • 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 Details

    • 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