Accessing Oracle User-Defined Type with Spring JDBC

It's not so long I wrote some single-purpose utility which called a stored procedure in Oracle database. The procedure returned as its result an user-defined data type.

Definition of the type looked approximately as this one:

CREATE OR REPLACE TYPE Summary as OBJECT 
( 
  ID Number(10),
  Sum1 NUMBER(20,3),
  Sum2 NUMBER(20,3)

  CONSTRUCTOR FUNCTION Summary RETURN SELF AS RESULT
);

I could have specified the mappings between Oracle object and Java class by implementing the SQLData interface, the approach supported by Spring Data. But I had only Spring JDBC available so I used the SqlReturnType.

First Maven dependencies:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>

 <groupId>org.bithill.projectx</groupId>
 <artifactId>MyTestUtil</artifactId>
 <version>1.0</version>

 <dependencies>

  <dependency>
    <groupId>com.oracle.oracle</groupId>
    <artifactId>ojdbc14</artifactId>
    <version>10.2.0.4</version>
  </dependency>

  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>3.0.7.RELEASE</version>
  </dependency>

 </dependencies>
   
</project> 

The utility to read the data is really simple - just load the driver, define the SimpleJdbcCall and call it and get the result. Only thing you have to remember,is to register the hendler for the user-defined type.

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import java.sql.SQLException;

import static java.lang.System.out;
import static oracle.jdbc.OracleTypes.*;

public class Main
{
   public static void main (String... args) throws SQLException 
   {
      String connectionString = args[0]; // JDBC URL

      DriverManagerDataSource ds = new DriverManagerDataSource();
      ds.setDriverClassName("oracle.jdbc.OracleDriver");
      ds.setUrl(connectionString);

      SimpleJdbcCall sumProcCall = new SimpleJdbcCall(new JdbcTemplate(ds))
        .withSchemaName("SCHEMA").withCatalogName("PKG")
        .withProcedureName("GET_SUMMARY")
        .declareParameters
        (
          // IN params
          new SqlParameter("P_Id", NUMBER),
          new SqlParameter("P_OrgUnit", VARCHAR),

          // OUT params
          new SqlOutParameter("P_Summary", STRUCT, "SUMMARY", new SummaryHandler() ),
          new SqlOutParameter("P_err_msg", VARCHAR),
          new SqlOutParameter("P_err_code", NUMBER)
        );

       (Summary)sumProcCall.execute
       ( Integer.valueOf(1),"ou.subsidiary1").get("P_Summary");

} 

Here is the type handler converting the raw information from Oracle's STRUCT to a Java class.

import oracle.sql.STRUCT;
import org.springframework.jdbc.core.SqlReturnType;

import java.sql.CallableStatement;
import java.sql.SQLException;

public class SummaryHandler implements SqlReturnType
{
    // struct data from jdbc
    private Object[] data;

    public Object getTypeValue(CallableStatement cs, int paramIndex, int sqlType, String typeName)
    throws SQLException
    {
        data = ((STRUCT)cs.getObject(INDEX_OF_SUMMARY_PARAMETER)).getAttributes();

        Summary result = new Summary
        (new SummaryLine( (BigDecimal)data[0]) /*, ...*/);

        return result;
    }
}

Fast Inserts to PostgreSQL with JDBC and COPY FROM

I was reading some materials on how to make database inserts as efficient as possible from Java. it was motivated by an already existing application for storing of some measurements into PosgtreSQL. So I decided to compare known approaches to see if there is some way how to improve the application already using batched inserts.

For the purpose of  the test I created following table:

CREATE TABLE measurement

(
  measurement_id bigint NOT NULL,
  valid_ts timestamp with time zone NOT NULL,
  measurement_value numeric(19,4) NOT NULL,
  CONSTRAINT pk_mv_raw PRIMARY KEY (measurement_id, valid_ts)
)
WITH (OIDS=FALSE)

I decided to test the insertion of 1000 records to the table. The data for the recors was generated before running of any of test methods. Four test methods were created to reflect ususal approaches:

• VSI (Very Stupid Inserts) - executing queries made of concatenated Strings one by one
• SPI  (Stupid Prepared Inserts) - similar to VSI but using prepared statements
• BPI (Batched Prepared Inserts) - prepared inserts, executed in batches of various length
• CPI (Copy Inserts) - inserts based on COPY FROM, executed in batches of various length

Prior to each inserts the table is cleared, the same after all data are succesfully inserted. Commit is called only once in each test method, following all the insert calls.  The following code exerpts illustrate the above listed approaches:

VSI

for (int i=0; i<testSize; i++)
{
  insertSQL = "insert into measurement values (" 
            + measurementIds[i] +",'"+ timestamps[i] +"',"+values[i]+")";
  insert.execute(insertSQL);
}

SPI

PreparedStatement insert = conn.prepareStatement("insert into measurement values (?,?,?)");

for (int i=0; i<testSize; i++)
{
  insert.setLong(1,measurementIds[i]);
  insert.setTimestamp(2, timestamps[i]);
  insert.setBigDecimal(3, values[i]);
  insert.execute();
}

BPI

PreparedStatement insert = conn.prepareStatement("insert into measurement values (?,?,?)");

for (int i=0; i<testSize; i++)
{
  insert.setLong(1,measurementIds[i]);
  insert.setTimestamp(2, timestamps[i]);
  insert.setBigDecimal(3, values[i]);
  insert.addBatch();
  if (i % batchSize == 0) { insert.executeBatch(); }
}
insert.executeBatch();

CPI

StringBuilder sb = new StringBuilder();
CopyManager cpManager = ((PGConnection)conn).getCopyAPI();
PushbackReader reader = new PushbackReader( new StringReader(""), 10000 );
for (int i=0; i<testSize; i++)
{
    sb.append(measurementIds[i]).append(",'")
      .append(timestamps[i]).append("',")
      .append(values[i]).append("\n");
    if (i % batchSize == 0)
    {
      reader.unread( sb.toString().toCharArray() );
      cpManager.copyIn("COPY measurement FROM STDIN WITH CSV", reader );
      sb.delete(0,sb.length());
    }
}
reader.unread( sb.toString().toCharArray() );
cpManager.copyIn("COPY measurement FROM STDIN WITH CSV", reader );

I hoped to get some improvements for using COPY FROM instead of batched inserts but not expected no big gain. But the results were a pleasant surprise. For a batch of size 50 (as defined in the original aplication I wanted to improve) the COPY FROM gave 40% improvement.  I expect some improvements when data come from a stream and skip the StringBuffer-with-PushbackReader exercise.

See the graphs yourself - the number following the method abbreviation is the size of the batch.

Average time in milliseconds

All the 200 runs individually

source code for the "benchmark"