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:

1. type specification for attr_value_type
2. type body for attr_value_type
3. 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.