Peripheral Home

Foreword

This is a collection of examples from my programming practice. They are a reference source for me and for anyone who happens to visit.

This book is maintained using the rust mdbook tool.

Last update 31. Aug. 2019

JDBC Examples

Examples of the use of Oracle-specific features in Java Database Connectivity (JDBC).

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.

JDBC Oracle Type Example

If you code your database queries in JDBC by hand, rather than having them generated by a framework, you can use the database-specific features of JDBC -- the JDBC flavour of your database. This is more work, but allows you to optimise the queries and the data access logic. As with anything, there is a learning curve in knowing how to use the specific features. In one project I decided it was time to learn how to access the custom data structures in Oracle using JDBC. This article gives a code example of the solution.

Let us have a simple data structure, a key-value pair. We want to pass a table of these key-value objects in and out of the database.

Oracle Code:

create or replace type attr_value_type as object (
   attr varchar2(255)
  ,value varchar2(255)
  ,member function to_string return varchar2
)
/
create or replace type body attr_value_type
is
  member function to_string return varchar2
  is
  begin
    return '['||self.attr||','''||self.value||''']';
  end;
end;
/
create or replace type attr_value_tbl as table of attr_value_type
/

The three statements create:

type specification for attr_value_type
type body for attr_value_type
table type attr_value_tbl of type attr_value_type

The type body is not needed for the logic. It only provides a to_string() function for debugging.

Java Code:

In Java, we need to define a class that is the counterpart to the record type we created in Oracle. Note that we do not need the oracle.sql package. The standard java.sql is sufficient.

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.util.Arrays;

public class AttributeValue implements SQLData{
	
	private String sqlTypeName;
	public String name;
	public String value;

	// to construct object manually
	public AttributeValue(String name, String value) {
		this.name = name;
		this.value = value;
	}

	@Override
	public String getSQLTypeName() throws SQLException { 
		return sqlTypeName; 
	}

	@Override
	public void readSQL(SQLInput stream, String typeName) throws SQLException {
		sqlTypeName = typeName;
		name = stream.readString();
		value = stream.readString();
	}

	@Override
	public void writeSQL(SQLOutput stream) throws SQLException {
		stream.writeString(name);
		stream.writeString(value);
	}
	
	@Override
	public String toString() {
		return Arrays.toString(new String[] {name, value});
	}
}

All but the last method in this class are implementations of the java.sql.SQLData interface. In my record both values are Strings, so I use the methods stream.readString() and stream.writeString(). The toString() function is again my debugging addition.

The only slightly counterintuitive thing about the implementation is the use of thesqlTypeName attribute. Although we know the value, it is set by the JDBC code.

We tell JDBC what Oracle record type the class maps to by an entry in the database connection's TypeMap.

	Map<String, Class<?>> myMap = conn.getTypeMap();
      	myMap.put("ATTR_VALUE_TYPE", AttributeValue.class);
        conn.setTypeMap(myMap);

This code must be run once after getting the connection object, before executing any statement involving the AttributeValue class.

We are now ready to execute an SQL statement.

    ArrayList<AttributeValue> results = new ArrayList<AttributeValue>();
    final String sql = 
        "select attr_value_type(q.attribute, q.value) attr_value from data_table q";
    ...
    rs = stmt.getResultSet();
    while (rs.next()) {
        results.add((AttributeValue) rs.getObject(1));
    }

In the SQL we select one column as a record attr_value_type(q.attribute, q.value) instead of two separate columns q.attribute, q.value.

In data fetching we use (AttributeValue) rs.getObject(1) instead of new AttributeValue(rs.getString(1), rs.getString(2)).

There is no immediate benefit other than cleaner code. We have encapsulated the mapping between Java and Oracle in the AttributeValue class and this the only thing our data fetching code now needs to know about.

The complication is that we need the Oracle record types in the database and it would probably be overkill to create them just for JDBC. If the query is heavily used, the performance should be evaluated. In my project, I used the mapping logic because I already had the Oracle record type in the SQL select statement to encapsulate the logic on the database side.

Class mapping can also help if you want to send data in the other direction and pass a Java object or its array as a bind variable to an SQL statement -- as described in the post JDBC Criteria Array. I do not have a code example, but it should not be difficult to figure out.

Programming Knowledge Base