Sunday, March 25, 2012

Oracle bulk insert or select from Java with Eclipselink

Database Bulk operations like a 10.000 rows insert operation from java can be hard and costly plus you won't come near the time when you do the same in PL/SQL. So the solution is to pass on the array of java JPA entities to a PL/SQL package and let the optimized Oracle Database do the hard work.

To make this work in java we need to use eclipselink at least version 2.3.2, this has the required java classes.

In this blogpost I will show you, how you can retrieve all the departments from the HR demo schema. Add a new department and do a multi insert.

On the PLSQL side we need to define PLSQL row and table types in our Oracle Package. Because Eclipselink can't handle these PLSQL table types directly we also need to define the matching Oracle Types counter parts.

So let's create these objects in the Oracle Database under the HR schema



We are ready with the Oracle Database.

So the first step is to create the Departments entity. This entity is not based on a Table but on the Oracle Type, so we need to add @Embeddable and @Struct annotations.



We also need to create some department utils in which we define the eclipselink PLSQLrecord and PLSQLCollection variables and an ObjectRelationalDataTypeDescriptor descriptor for the entitymanager session.
We need to do this else we can't map the Oracle Types to PLSQL table types. Also eclipselink need to link the Deparments class to the Oracle Type.


Off course we need to create a persistence unit which connects to the HR demo schema.

We are ready to test our solutions.

First we start with a single insert where we pass a department as parameter of a procedure.
These are the steps to make this work.
Define a PLSQLStoredProcedureCall with your Oracle procedure name and all the parameters. Because we only pass on a record so we only need to use the department PLSQLrecord. Create DataReadQuery and add our department entity. Retrieve the ServerSession and add the department descriptor.
And at last fire the procedure.


In the second test we will do a multi insert. This works the same as the single result but only now we need to use the department PLSQLCollection and set our parameters to the Array type.


At last we will retrieve all the departments and we will use a function which returns an array of departments. In this case we will use PLSQLStoredFunctionCall and also use the department PLSQLCollection. As result we get a DatabaseRecord where we can read the RESULT key which contains all our departments.


Here you can download the project on github https://github.com/biemond/jdev11g_examples/tree/master/PLSQLWrapper



  

7 comments:

  1. What jdbc driver to use? I read that oracle jdbc driver does not currently support this functionality.
    I did something wrong?
    Error:
    Caused by: java.sql.SQLException: Unsupported feature
    at oracle.jdbc.driver.PhysicalConnection.createArrayOf(PhysicalConnection.java:9175)
    at com.sun.gjc.spi.jdbc40.ConnectionHolder40.createArrayOf(ConnectionHolder40.java:434)
    at org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.createArray(DatabasePlatform.java:2942)
    at org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.createArray(DatabasePlatform.java:2923)
    at org.eclipse.persistence.internal.databaseaccess.BindCallCustomParameter.convert(BindCallCustomParameter.java:142)
    at org.eclipse.persistence.internal.databaseaccess.InParameterForCallableStatement.set(InParameterForCallableStatement.java:30)
    at org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.setParameterValueInDatabaseCall(DatabasePlatform.java:2229)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:716)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:585)
    ... 100 more

    ReplyDelete
    Replies
    1. Hi,

      My example works on the oracle 11g xe database. Maybe its your database version. The Oracle JDBC driver version 6 works perfectly.

      thanks

      Delete
  2. If you are using Oracle 10 is only supported bulk insert like:

    Forall j in ....
    Insert into tale values array(j)

    You have to chance jour stored procedure to make it working ... (seeOracle 10 and bulk bind )

    ReplyDelete
  3. I was trying to use reproduce this code and i got same issue
    Unsupported feature
    at oracle.jdbc.driver.PhysicalConnection.createArrayOf...
    I was using OJDBC5 chnaged to OJDBC6 and to last version of OJDBC6(11.2.0.3) and oracle 11 and eclipselink 2.3 so I think that was not wrong so I decided to decompile oracle driver and i found this:
    public Array createArrayOf(String paramString, Object[] paramArrayOfObject)
    throws SQLException
    {
    SQLException localSQLException = DatabaseError.createUnsupportedFeatureSqlException();
    localSQLException.fillInStackTrace();
    throw localSQLException;
    }

    so basically oracle ALWAYS throws and exception when createArrayOf iss called they not even TRY to do something.
    so if you god this code working I see 2 possibilities:
    1) you have a rare piece of code in you OJDBC.jar where this method actually has code, or
    2) your configuration causes that this method is not been call.
    In any case i would like to know if you can spoecify which versions (release build or nightly build date) are you using for Oracle JDBC. Eclipselink, JPA, and oracle db

    thank you

    ReplyDelete
    Replies
    1. Hi,

      I used the oracle 11g XE database and Jdeveloper 11.1.1.6 with Eclipselink 2.3 , the libs and project is on github.

      How did you test is, if you test it on the weblogic server then you should update the internal eclipselink and persistence jar.

      Good luck

      Delete
    2. Hello Edwin

      Sorry it took me some time, because I had to work on other issues but finally found the solution.

      Well basically Eclipselink in DatabasePlatform.createArray calls to OJDBC createArrayOf(), that is fine beacuse that is the method defined in java.sql.Connection interface. What it is not fine is the OJDBC never implements that method (it only throws the exception as we were talking before) intead OJDBC defines a new method called createARRAY in OracleConnection interface (seems Oracle is trying to not be compatible with anyone) than method actuanlly works.

      I had analized Eclipselink code 2.3.1 2.4.0 and 2.4.1 and seems createArrayOf is always called.

      So what i did is to change Eclipselink method to validate if Connection is an instance of OracleConnection then I call to createARRAY instead of the standar createArrayOf. and works like a charm, even works with the relative new @Struct, @PLSQLRecords and @PLSQLTable annotations.

      I know that change Eclipselink code seems like force a litlle bit this solution but luckly I already have a custumized version of Eclipselink so it wasn't a big issue for me.

      Delete
    3. Thanks for your feedback and your solution.
      Can you report this issue to EclipseLink or give me your code and then I will add this to this blogpost and give you the credits :-)

      Thanks

      Delete