Let's suppose that you have a web app where you get some records from the user interface and from your DAO, you are trying to pass the records as Oracle ARRAYS to database. Here is a step by step example.
Yes, it's a nightmare as you have to take care of some steps. But, once you understand what to do and what are the issues, it's pretty easy.
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" is object(
emp_id Varchar2(500),
emp_name varchar2(500));
*/
/*
Step 2 : Create a type EMP_TYPE_TABLE
CREATE OR REPLACE TYPE "EMP_TYPE_TAB";
*/
/*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