Upscaling your JDBC app using Oracle object type collection

As an addendum to his excellent points, please find attached a comparison with using an Oracle collection of an Oracle object type – forgive my Java. Using StructDescriptor, STRUCT, ArrayDescriptor and ARRAY structures is unsightly and unintuitive but they can deliver some further performance gains. If only we could wrap this approach up in some user-friendly layer then I reckon we could kick some of these iterative row-by-row ORM tools into touch.

First up, for the baseline, based On Greg’s example, this is what my batch size performance was like inserting 10000 rows into emp on my system:

Regarding the comment about a nice encapsulation, if there is an ORM framework that does this it will most probably be Oracle TopLink or Eclipse Link, which is a JPA provider based on Toplink. Otherwise there is no reason why you cannot use raw JDBC + JPA or the ORM of your choice. I’m reliably informed that there are people who do this out there and make a good job of it.

The cleanest way to incorporate this into your software is to place the JDBC within a data access object corresponding to the domain object, the thing that contains the business logic, a bog standard Java design pattern.