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

	create or replace type hr_department_row_type as object
	( department_id number(4,0)
	, department_name varchar2(30)
	, manager_id number(6,0)
	, location_id number(4,0)
	);
	/
	create or replace type hr_department_type as table of hr_department_row_type;
	/
	create or replace package hr_departments
	is
	type department_rec_type is record ( department_id number(4,0)
	, department_name varchar2(30)
	, manager_id number(6,0)
	, location_id number(4,0)
	);
	type department_tab_type is table of department_rec_type index by binary_integer;
	procedure insertdepartments(p_records in department_tab_type);
	procedure insertdepartment(p_record in department_rec_type);
	function selectdepartments return department_tab_type;
	procedure test;
	procedure test2;
	procedure test3;
	end hr_departments;
	/
	create or replace package body hr_departments
	is
	procedure insertdepartments(p_records in department_tab_type)
	is
	begin
	forall i in p_records.first..p_records.last
	insert into departments ( department_id
	, department_name
	, manager_id
	, location_id
	)
	values ( p_records(i).department_id
	, p_records(i).department_name
	, p_records(i).manager_id
	, p_records(i).location_id
	);
	end;
	procedure insertdepartment(p_record in department_rec_type)
	is
	begin
	insert into departments ( department_id
	, department_name
	, manager_id
	, location_id
	)
	values ( p_record.department_id
	, p_record.department_name
	, p_record.manager_id
	, p_record.location_id
	);
	end;
	function selectdepartments
	return department_tab_type
	as
	v_records department_tab_type;
	begin
	select * bulk collect into v_records from departments;
	return v_records;
	end;
	procedure test
	is
	v_records department_tab_type;
	v_record1 department_rec_type;
	begin
	v_record1.department_id := 501;
	v_record1.department_name := 'arnhem';
	v_record1.location_id := 1700;
	v_record1.manager_id := 100;
	v_records(1) := v_record1;
	v_record1.department_id := 502;
	v_record1.department_name := 'amersfoort';
	v_record1.location_id := 1700;
	v_record1.manager_id := 100;
	v_records(2) := v_record1;
	insertdepartments(p_records => v_records);
	end;
	procedure test2
	is
	result hr_departments.department_tab_type;
	begin
	result := selectdepartments;
	for i in result.first..result.last
	loop
	dbms_output.put_line( result(i).department_id);
	end loop;
	end;
	procedure test3
	is
	v_record1 department_rec_type;
	begin
	v_record1.department_id := 501;
	v_record1.department_name := 'arnhem';
	v_record1.manager_id := 100;
	v_record1.location_id := 1700;
	insertdepartment(p_record => v_record1);
	end;
	end hr_departments;
	/

view raw plsql.sql hosted with ❤ by GitHub

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.

	package nl.amis.jpa.entities;
	import java.io.Serializable;
	import javax.persistence.Column;
	import javax.persistence.Embeddable;
	import org.eclipse.persistence.annotations.Struct;
	@Embeddable
	@Struct( name = "HR_DEPARTMENT_ROW_TYPE" ,
	fields = {"DEPARTMENT_ID",
	"DEPARTMENT_NAME",
	"MANAGER_ID",
	"LOCATION_ID"
	}
	)
	public class Departments implements Serializable {
	private static final long serialVersionUID = 1L;
	@Column(name="DEPARTMENT_ID")
	private Long departmentId;
	@Column(name="DEPARTMENT_NAME")
	private String departmentName;
	@Column(name="LOCATION_ID")
	private Long locationId;
	@Column(name="MANAGER_ID")
	private Long managerId;
	public Departments() {
	}
	public Long getDepartmentId() {
	return departmentId;
	}
	public void setDepartmentId(Long departmentId) {
	this.departmentId = departmentId;
	}
	public String getDepartmentName() {
	return departmentName;
	}
	public void setDepartmentName(String departmentName) {
	this.departmentName = departmentName;
	}
	public Long getLocationId() {
	return locationId;
	}
	public void setLocationId(Long locationId) {
	this.locationId = locationId;
	}
	public Long getManagerId() {
	return managerId;
	}
	public void setManagerId(Long managerId) {
	this.managerId = managerId;
	}
	}

view raw Departments.java hosted with ❤ by GitHub

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.

	package nl.amis.jpa;
	import java.util.ArrayList;
	import nl.amis.jpa.entities.Departments;
	import org.eclipse.persistence.mappings.structures.ObjectRelationalDataTypeDescriptor;
	import org.eclipse.persistence.platform.database.jdbc.JDBCTypes;
	import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLCollection;
	import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLrecord;
	public class DepartmentUtils {
	public DepartmentUtils() {
	}
	private static String departmentPLSQLRecType = "HR_DEPARTMENTS.department_rec_type";
	private static String departmentPLSQLTabType = "HR_DEPARTMENTS.department_tab_type";
	private static String departmentRecType = "HR_DEPARTMENT_ROW_TYPE";
	private static String departmentTabType = "HR_DEPARTMENT_TYPE";
	private static String departmentIdColumn = "DEPARTMENT_ID";
	private static String departmentNameColumn = "DEPARTMENT_NAME";
	private static String departmentLocationColumn = "LOCATION_ID";
	private static String departmentManagerColumn = "MANAGER_ID";
	private static String departmentIdEntity = "departmentId";
	private static String departmentNameEntity = "departmentName";
	private static String departmentLocationEntity = "locationId";
	private static String departmentManagerEntity= "managerId";
	public static PLSQLrecord departmentRecord() {
	PLSQLrecord record = new PLSQLrecord();
	record.setTypeName(departmentPLSQLRecType);
	record.setCompatibleType(departmentRecType);
	record.setJavaType(Departments.class);
	record.addField(departmentIdColumn,
	JDBCTypes.NUMERIC_TYPE, 4,0);
	record.addField(departmentNameColumn,
	JDBCTypes.VARCHAR_TYPE, 30);
	record.addField(departmentLocationColumn,
	JDBCTypes.NUMERIC_TYPE, 4, 0);
	record.addField(departmentManagerColumn,
	JDBCTypes.NUMERIC_TYPE, 6, 0);
	return record;
	}
	public static PLSQLCollection departmentCollection() {
	PLSQLCollection collection = new PLSQLCollection();
	collection.setTypeName(departmentPLSQLTabType);
	collection.setCompatibleType(departmentTabType);
	collection.setJavaType(ArrayList.class);
	// add the department PLSQLRecord
	collection.setNestedType(departmentRecord());
	return collection;
	}
	public static ObjectRelationalDataTypeDescriptor departmentDescriptor(){
	ObjectRelationalDataTypeDescriptor descriptor =
	new ObjectRelationalDataTypeDescriptor();
	descriptor.setJavaClass(Departments.class);
	descriptor.setTableName(departmentPLSQLRecType);
	descriptor.setStructureName(departmentRecType);
	descriptor.addFieldOrdering(departmentIdColumn);
	descriptor.addFieldOrdering(departmentNameColumn);
	descriptor.addFieldOrdering(departmentManagerColumn);
	descriptor.addFieldOrdering(departmentLocationColumn);
	descriptor.addDirectMapping(departmentIdEntity,
	departmentIdColumn);
	descriptor.addDirectMapping(departmentNameEntity,
	departmentNameColumn);
	descriptor.addDirectMapping(departmentManagerEntity,
	departmentManagerColumn);
	descriptor.addDirectMapping(departmentLocationEntity,
	departmentLocationColumn);
	descriptor.addPrimaryKeyFieldName(departmentIdColumn);
	return descriptor;
	}
	}

view raw DepartmentUtils.java hosted with ❤ by GitHub

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.

	package nl.amis.jpa;
	import java.util.Vector;
	import javax.persistence.EntityManager;
	import javax.persistence.EntityManagerFactory;
	import javax.persistence.Persistence;
	import nl.amis.jpa.entities.Departments;
	import org.eclipse.persistence.jpa.JpaEntityManager;
	import org.eclipse.persistence.jpa.JpaHelper;
	import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLStoredProcedureCall;
	import org.eclipse.persistence.queries.DataReadQuery;
	import org.eclipse.persistence.sessions.server.ServerSession;
	public class TestInsert {
	public static void main(String[] a) throws Exception {
	EntityManagerFactory emf =
	Persistence.createEntityManagerFactory("HrMulitDeptService");
	EntityManager em = emf.createEntityManager();
	em.getTransaction().begin();
	Departments dept1 = new Departments();
	dept1.setDepartmentId(505L);
	dept1.setDepartmentName("amersfoort");
	dept1.setManagerId(200L);
	dept1.setLocationId(1700L);
	PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
	call.addNamedArgument("P_RECORD", DepartmentUtils.departmentRecord());
	call.setProcedureName("HR_DEPARTMENTS.INSERTDEPARTMENT");
	DataReadQuery databaseQuery = new DataReadQuery();
	databaseQuery.addArgument("P_RECORD");
	databaseQuery.setCall(call);
	Vector args = new Vector();
	args.add(dept1);
	ServerSession session =
	((JpaEntityManager)em.getDelegate()).getServerSession();
	session.addDescriptor(DepartmentUtils.departmentDescriptor());
	JpaHelper.getEntityManager(em).getActiveSession().executeQuery(databaseQuery,
	args);
	em.getTransaction().commit();
	em.close();
	emf.close();
	}
	}

view raw TestInsert.java hosted with ❤ by GitHub

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.

	package nl.amis.jpa;
	import java.util.ArrayList;
	import java.util.List;
	import java.util.Vector;
	import javax.persistence.EntityManager;
	import javax.persistence.EntityManagerFactory;
	import javax.persistence.Persistence;
	import nl.amis.jpa.entities.Departments;
	import org.eclipse.persistence.jpa.JpaEntityManager;
	import org.eclipse.persistence.jpa.JpaHelper;
	import org.eclipse.persistence.platform.database.jdbc.JDBCTypes;
	import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLStoredProcedureCall;
	import org.eclipse.persistence.queries.DataReadQuery;
	import org.eclipse.persistence.sessions.server.ServerSession;
	public class TestMultiInsert {
	public TestMultiInsert() {
	}
	public static void main(String[] a) throws Exception {
	EntityManagerFactory emf = Persistence.createEntityManagerFactory("HrMulitDeptService");
	EntityManager em = emf.createEntityManager();
	em.getTransaction().begin();
	List dept = new ArrayList();
	Departments dept1 = new Departments();
	dept1.setDepartmentId(505L);
	dept1.setDepartmentName("amersfoort");
	dept1.setLocationId(1700L);
	dept1.setManagerId(100L);
	dept.add(dept1);
	Departments dept2 = new Departments();
	dept2.setDepartmentId(506L);
	dept2.setDepartmentName("utrecht");
	dept2.setLocationId(1700L);
	dept2.setManagerId(100L);
	dept.add(dept2);
	PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
	call.addNamedArgument("P_RECORDS",
	DepartmentUtils.departmentCollection(),
	JDBCTypes.ARRAY_TYPE.getSqlCode());
	call.setProcedureName("HR_DEPARTMENTS.INSERTDEPARTMENTS");
	DataReadQuery databaseQuery = new DataReadQuery();
	databaseQuery.addArgument("P_RECORDS",ArrayList.class);
	databaseQuery.setCall(call);
	Vector args=new Vector();
	args.add(dept);
	ServerSession session =
	((JpaEntityManager)em.getDelegate()).getServerSession();
	session.addDescriptor(DepartmentUtils.departmentDescriptor());
	// execute
	JpaHelper.getEntityManager(em).getActiveSession().executeQuery(databaseQuery,args);
	em.getTransaction().commit();
	em.close();
	emf.close();
	}
	}

view raw TestMultiInsert.java hosted with ❤ by GitHub

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.

	package nl.amis.jpa;
	import java.util.List;
	import javax.persistence.EntityManager;
	import javax.persistence.EntityManagerFactory;
	import javax.persistence.Persistence;
	import javax.persistence.Query;
	import nl.amis.jpa.entities.Departments;
	import org.eclipse.persistence.jpa.JpaEntityManager;
	import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLStoredFunctionCall;
	import org.eclipse.persistence.queries.DataReadQuery;
	import org.eclipse.persistence.sessions.DatabaseRecord;
	import org.eclipse.persistence.sessions.server.ServerSession;
	public class TestMultiSelect {
	public static void main(String[] args) {
	EntityManagerFactory emf =
	Persistence.createEntityManagerFactory("HrMulitDeptService");
	EntityManager em = emf.createEntityManager();
	PLSQLStoredFunctionCall call =
	new PLSQLStoredFunctionCall(DepartmentUtils.departmentCollection());
	call.setProcedureName("HR_DEPARTMENTS.selectdepartments");
	DataReadQuery databaseQuery = new DataReadQuery();
	databaseQuery.setCall(call);
	ServerSession session =
	((JpaEntityManager)em.getDelegate()).getServerSession();
	session.addDescriptor(DepartmentUtils.departmentDescriptor());
	Query query =
	((JpaEntityManager)em.getDelegate()).createQuery(databaseQuery);
	DatabaseRecord result = (DatabaseRecord)query.getSingleResult();
	List<Departments> departments = (List<Departments>)result.get("RESULT");
	for ( Departments dept : departments ) {
	System.out.println(dept.getDepartmentName());
	}
	em.close();
	emf.close();
	}
	}

view raw TestMultiSelect.java hosted with ❤ by GitHub

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