How to pass arrays using JDBC to database?

[Nilesh Raje]

How to pass arrays using JDBC to database?

[Jeanne Boyarsky]

There's no array type in a database. Do you mean to a stored procedure? If so, what stored procedure language?

[Bauke Scholtz]

Insert every value in a new row. If those values are to be related to a single entity, then use a chain table. This way you can retrieve it back into the entity using a query with a JOIN clause.

[Nilesh Raje]

Well What i meant is that instead of entering single rows in the table every time can we directly pass a arraylist from java which holds say 5 employee objects which correspond to employee records/rows in emp table in the database.

I understand that we can use prepared statement and then pass the objects one by one. But my question is how do we pass the entire array to database so all records gets inserted at once.

[Bauke Scholtz]

You can store Java objects serialized in a binary field of the database. But that's a bad practice and bad datamodel. You're going to tight couple Java with a RDBMS. I wouldn't recommend that.

Just create a chain table where in you insert every entity as a new row referenced with a foreign key to the parent entity -if any.

You should not design from Java to datamodel, but from datamodel to Java.

[Nilesh Raje]

Yeah i know this way. Do you mean the one below right?

Use Case : Let us suppose that we have a java bean Employee and we are trying to send an array of employee records at a time to database.

   

/*Step 1 : Create a object type in the database*/
/*
CREATE OR REPLACE TYPE “EMP_TYPE” AS object(
emp_id Varchar2(500),
emp_name varchar2(500));
*/
/*
Step 2 : Create a type EMP_TYPE_TABLE

CREATE OR REPLACE TYPE “EMP_TYPE_TAB” AS TABLE OF EMP_TYPE;
*/

/*Step 3 : Create a Java bean which maps the attributes of the above object type in Step 2.*/

import java.io.Serializable;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

public class Employee implements SQLData, Serializable{
static final long serialVersionUID = 4070409649129120458L;
public Employee(){}

// constructor that takes parameters
// getters and setters for emp_id, emp_name
// You have to implement readSQL() and writeSQL() methods, as shown below.
// This is where you are mapping the Employee table’s columns to the Employee
//java bean.

public void readSQL(SQLInput stream, String typeName) throws SQLException {
this.emp_id = stream.readString();
this.emp_name = stream.readString();
}
public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeString(emp_id);
stream.writeString(emp_name);
}
}

//EmpDAO class gets a connection to the database and passes the data.

public class EmpDAO{
java.sql.Connection conn;
java.sql.Connection dconn;
/*
Step 1 : Get database connection
This is a very important step. To pass your records of data as Arrays, you need to get a oracle.jdbc.driver.T4CConnection and then use ArrayDescriptor’s. So, how do you get a T4CConnection ?

To get T4CConnection from java.sql.Connection, you need to cast like this :
t4cConn = ((DelegatingConnection)conn).getInnermostDelegate();

If you are working on tomcat, you have two options to get a DataSource in your context.xml.
a) By using apache commons-dbcp
OR
b) by directly using javax.sql.DataSource.
Let’s see how to get the T4CConnection in both these cases.
*/

public void sendRecordsToDB(){

//Use Case (a) : if you configured apache commons-dbcp
BasicDataSource ds = (BasicDataSource)ctx.lookup(jndiName);
ds.setAccessToUnderlyingConnectionAllowed(true);
conn = ds.getConnection();
dconn = ((DelegatingConnection)conn).getInnermostDelegate();

//Use Case (b) : if you are directly using javax.sql.DataSource

BasicDataSource bds = new BasicDataSource();
bds.setDriverClassName(”");
bds.setUsername(”");
bds.setPassword(”
“);
bds.setUrl(”jdbc:oracle:thin:@”);
bds.setAccessToUnderlyingConnectionAllowed(true);
conn = bds.getConnection();
dconn = ((DelegatingConnection)conn).getInnermostDelegate();

/*So, using either of the above approaches we got dconn, which is an instance of T4CConnection.*/

/* Now let’s build an array list of employees.
*/
final List listOfEmployees = new LinkedList();

Employee e1 = new Employee();
e.setEmpId(1);
e.setEmpName(”Ravi”);

listOfEmployees.add(e1);

Employee e2 = new Employee();
e.setEmpId(2);
e.setEmpName(”Vedala”);

listOfEmployees.add(e2);

// Now, create an array descriptor

ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor( “EMP_TYPE_TAB”, dconn );

ARRAY array_to_pass = new ARRAY( descriptor, dconn, (Object[]) listOfEmployees.toArray());
ps = (OracleCallableStatement)dconn.prepareCall(”begin insert_employees(:1); end;”);
ps.setARRAY( 1, array_to_pass );
ps.execute();
conn.commit();
}

/*
- See how simple and beautiful is the procedure.
- Using the TABLE() function, you can treat the whole array as a table as EMP_TYPE_TAB is a nested table.
*/

PROCEDURE insert_employees(p_emparray in EMP_TYPE_TAB) AS
BEGIN
/* INSERT ARRAY OF RECORDS IN TO THE EMP TABLE*/
INSERT INTO scd_company_staging
(emp_id,emp_name)
SELECT * FROM TABLE(p_emparray );
END insert_employees;
 

Is this bad way though? I am asking this question as it was asked in one of the interviews. I googled and found this solution.