JDBC Criteria Array

JDBC is an application programming interface for accessing relational databases from Java. In the Java world, the task of reading and writing data to a database tends to be handled by a persistence framework such as Hibernate or JPA (Java Persistence API). This means that a program logic developer may never need to look at the database SQL queries. The price of this convenience is that the framework cannot make optimal use of the database. It cannot take advantage of database-specific features, and the schema design itself is limited to choices that accommodate the generated SQL. However, you may have (an opportunity) to hand code your database queries in Java. This article looks at a fairly common problem in this scenario: how to pass an arbitrary number of criteria values to an SQL query.

Suppose you have a web application. The display logic of this application is such that for a tabular view of records, it first queries the object IDs of the records to be displayed. Each attribute column in the result table is a separate database query to select the values for that column. So we have one query to select the object IDs, and then as many queries as there are columns for which we need to select data. (This is framework logic, and the idea is that we can configure the data source independently for each column.)

Say the first query is done and we are about to select the column data. In Java, we are now presented with an integer array of object IDs. The size of the array is the page size of the table in the web application, typically between 20 and a few hundred. However, it can be much larger. Our application ‒ this is less than ideal ‒ does no processing at all in the database SQL. It sorts and filters records in the middle tier.

To display the first 20 records sorted/filtered by a single attribute column, the web tier must:

Select all attribute values for the sort/filter column.
Sort/filter the selected values.
Take the first 20 record IDs from these values.
Select values for the other table columns using the gotten IDs.

The application framework handles the step 1 in the following way. It queries the data in batches of about 40 values, generating SQL like this:

select * from lxstring 
where lxoid in (:va, :vb, :vc, :vd, :ve, :vf, :vg, :vh, :vi, :vj, :vk, :vl, :vm, :vn, :vo, :vp, :vq, :vr, :vs, :vt, :vu)
  and lxtype in (:vv,:vw,:vx)

In this query, the lxstring is the target table for attribute values that need to be sorted/filtered. The elements :va,:vb,:vc etc. are the bind variables for the query ID criteria.

The framework executes the query as many times as necessary and adds up the values from each execution. As the number of record ID values can be in the thousands, the query can be run hundreds of times. What we want to do is replace all this processing with a single SQL query that handles an unlimited number of criteria values. In defining this problem, we need to address three concerns:

A technique for passing a variable number of criteria values to a JDBC SQL query.
Proper handling of nulls for objects not found by the query.
Optimal performance for both low and high cardinality (the number of criteria values).

Oracle Part:

First, we need a table structure in Oracle in which to pass the criteria values. This is an Oracle nested table type, not a PL/SQL table (Oracle 12 allows also PL/SQL tables). In our case, the criteria values are integers and we need a table of integers.

create or replace type ID_TABLE_TYP as table of integer;

The following SQL is the JDBC query using the above defined Oracle table type:

select
   x.idx, t.target_column value
from
   (select /*+ cardinality(z 20) */ rownum-1 idx, z.column_value oid
    from table(cast(:1 as ID_TABLE_TYP)) z
   ) x
  ,target_table t
where x.oid = t.object_id;

The interesting part of this SQL is the subquery:

select /*+ cardinality(z 20) */
  rownum-1 idx,
  z.column_value
from
  table(cast(:1 as ID_TABLE_TYP)) z

The select statement contains one bind variable :1 to which the array of object IDs is assigned in Java. In SQL, the table() function is used to cast the array and use it as an ordinary table row source. At a minimum, this is all that is needed and we have a solution to concern 1 of the problem.

The rownum-1 idx column from the criteria table subquery addresses the concern number 2. It corresponds to the index of the value in the Java array ‒ in SQL we start with 1, in Java with 0. The rownum-1 thus gives us the index of the value in the Java criteria array, allowing us to associate the selected values with the corresponding object IDs.

The cardinality hint /*+ cardinality(z 20) */ addresses concern number 3. When we issue our SQL query from Java, we are in the enviable position of knowing exactly how many rows there are for the database to work with ‒ we have obtained the object IDs through a separate query. This is the most important important piece of information the database optimizer needs. We don't have this in a regular database query. A regular query selects, filters and sorts the data in one go.

I use the cardinality hint whenever I am dynamically building SQL statements and I am in a similar position where the criteria is available as an in-memory table or in a database temporary table. It is a good idea to round the value to the order of magnitude (e.g. 1, 2, 10, 100 ...) to reduce the number of unique queries the database has to parse.

Note that the cardinality hint is undocumented, but it is de facto production grade (see Ask Tom).

Java Part:

Below is a sample Java code to select an array of string values from a database for a criteria array of object IDs.

public void executeSingleColumnSelect(String sqlStmt, String[] values, int[] objIDs)
throws Exception {
    ...
    conn = getConnection();
    ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor("ID_TABLE_TYP", conn);
    ARRAY oraObjIDs = new ARRAY(arrayDesc, conn, objIDs);
    PreparedStatement stmt = conn.prepareStatement(sqlStmt);
    ((OraclePreparedStatement) stmt).setARRAY(1, oraObjIDs);
    stmt.execute();
    ResultSet rs = stmt.getResultSet();
    while (rs.next()) {
        values[rs.getInt(1)] = rs.getString(2);
    }
    ...
}

To use the ID_TABLE_TYP Oracle table type in JDBC, we need an oracle.sql.ArrayDescriptor, which converts a Java integer array into an oracle.sql.ARRAY, which we can use as a bind variable in the statement. The rest of the logic executes the statement and loops through the result set to collect values into the target Java String array. rs.getInt(1) gets the index of the selected value, rs.getString(2) gets the value.

The method relies on a correct SQL statement. The first column must return the index of the criteria value (SQL rownum-1). The SQL itself must return as many rows as there are values in the criteria array. The String[] values array must be initialized to the size of int[] objIDs.

Multi-column keys:

To complete the example, let us see how to handle composite criteria values. That is, we have a multi-column primary or unique key for the objects in the database. Here we have two options as to where we assemble the individual column values into a composite key record. One is to pass the criteria values as separate single-column tables and do the assembly in the database. The other is to do the assembly in Java and pass a table of key records to the query. We will look at the first option here. (For an example of how to map a Java class to an Oracle record type, see the article JDBC Oracle Type Example.)

Let the primary key of the record be three columns: two integers and one string. Below is the extra database code for this.

create or replace type CD_TABLE_TYP as table of varchar2(100);
/
create or replace type ididcd_table_typ as table of ididcd_typ;
/
create or replace function ididcd_table_func(
   ids in ID_TABLE_TYP
  ,ids2 in ID_TABLE_TYP
  ,cds in cd_table_typ
) return ididcd_table_typ
is
  t ididcd_table_typ := new ididcd_table_typ();
begin
  t.extend(ids.count);
  for i in 1 .. ids.count loop
    t(i) := new ididcd_typ(ids(i), ids2(i), cds(i));
  end loop;
  return t;
end;
/

And the Java part:

public void executeSingleColumnSelect(String sqlStmt, String[] values, int[] ids, int[] ids2, String[] codes)
throws Exception {
    ...
    ArrayDescriptor idArrayDesc = ArrayDescriptor.createDescriptor("ID_TABLE_TYP", conn);
    ArrayDescriptor codeArrayDesc = ArrayDescriptor.createDescriptor("CODE_TABLE_TYP", conn);
    ARRAY oraIdTable = new ARRAY(idArrayDesc, conn, ids);
    ARRAY oraIdTable2 = new ARRAY(idArrayDesc, conn, ids2);
    ARRAY oraCodeTable = new ARRAY(codeArrayDesc, conn, codes);
    ((OraclePreparedStatement) stmt).setARRAY(1, oraIdTable);
    ((OraclePreparedStatement) stmt).setARRAY(2, oraIdTable2);
    ((OraclePreparedStatement) stmt).setARRAY(3, oraCodeTable);
    stmt.execute();
    fetchValues(stmt, values);
    ResultSet rs = stmt.getResultSet();
    while (rs.next()) {
        int idx = rs.getInt(1);
        values[idx] = rs.getString(2);
        values[idx] = (values[idx] == null) ? "" : values[idx];
        }
    }
    rs.close();
    ...
}

The special assembly function is the ididcd_table_func() function, which creates and returns a table of ididcd_typ records. The returned three-column table is then joined to the target_table in SQL with a three-column join (no example here).

The table() function, either standard or custom, is a powerful technique in Oracle for using value arrays in SQL. The Oracle-specific SQL packages in Java allow it to be used in JDBC.

Programming Knowledge Base

JDBC Criteria Array

Oracle Part:

Java Part:

Multi-column keys: