Java Database Connectivity (JDBC)

Hi all ,

I am posting a working example of Passing Array of java objects to and from oracle database . I have struggled a lot to get it working and since finally its working , postinmg it here so that it coudl be helpful to the rest of the folks.

First thinsg first

i) Create a Java Value Object which you want to pass .

***************************************************************************
create or replace and compile java source named Person as
import java.sql.*;
import java.io.*;
public class Person implements SQLData
{
private String sql_type = "PERSON_T";

public int person_id;
public String person_name;

public Person () {}

public String getSQLTypeName() throws SQLException { return sql_type; }

public void readSQL(SQLInput stream, String typeName) throws SQLException
{
sql_type = typeName;
person_id = stream.readInt();
person_name = stream.readString();
}

public void writeSQL(SQLOutput stream) throws SQLException
{
stream.writeInt (person_id);
stream.writeString (person_name);
}

}
***************************************************************************
ii) Once you created a Java class compile this class in sql plus. Just Copy paste and run it in SQL .


you should see a message called "Java created."
***************************************************************************
iii) Now create your object Types

CREATE TYPE person_t AS OBJECT
EXTERNAL NAME 'Person' LANGUAGE JAVA
USING SQLData (
person_id NUMBER(9) EXTERNAL NAME 'person_id',
person_name VARCHAR2(30) EXTERNAL NAME 'person_name'
)
/

***************************************************************************
iv) Now create a table of Objects

CREATE TYPE person_tab IS TABLE OF person_t;

***************************************************************************
v) Now create your procedure . Ensure that you create dummy table called "person_test" for loggiing values.

create or replace
procedure give_me_an_array( p_array in person_tab,p_arrayout out person_tab)
as
l_person_id Number;
l_person_name Varchar2(200);
l_person person_t;
l_p_arrayout person_tab;
errm Varchar2(2000);

begin
l_p_arrayout := person_tab();
for i in 1 .. p_array.count
loop
l_p_arrayout.extend;
insert into person_test values(p_array(i).person_id, 'in Record '||p_array(i).person_name);
l_person_id := p_array(i).person_id;
l_person_name := p_array(i).person_name;
l_person := person_t(null,null);
l_person.person_id := l_person_id + 5;
l_person.person_name := 'Out Record ' ||l_person_name ;
l_p_arrayout(i) := l_person;
end loop;
p_arrayout := l_p_arrayout;
l_person_id := p_arrayout.count;
for i in 1 .. p_arrayout.count
loop
insert into person_test values(l_person_id, p_arrayout(i).person_name);
end loop;
commit;
EXCEPTION WHEN OTHERS THEN
errm := SQLERRM;
insert into person_test values(-1, errm);
commit;
end;
/
***************************************************************************
vi) Now finally create your java class which will invoke the pl/sql procedure and get the updated value array and then display it on your screen>Alternatively you can also check the "person_test" tbale


import java.util.Date;
import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;

public class ArrayDemo
{

public static void passArray() throws SQLException
{
Connection conn = getConnection();

ArrayDemo a = new ArrayDemo();

Person pn1 = new Person();
pn1.person_id = 1;
pn1.person_name = "SunilKumar";

Person pn2 = new Person();
pn2.person_id = 2;
pn2.person_name = "Superb";

Person pn3 = new Person();
pn3.person_id = 31;
pn3.person_name = "Outstanding";

Person[] P_arr = {pn1, pn2, pn3};

Person[] P_arr_out = new Person[3];


ArrayDescriptor descriptor =
ArrayDescriptor.createDescriptor( "PERSON_TAB", conn );

ARRAY array_to_pass =
new ARRAY( descriptor, conn, P_arr);


OracleCallableStatement ps =
(OracleCallableStatement )conn.prepareCall
( "begin give_me_an_array(?,?); end;" );

ps.setARRAY( 1, array_to_pass );

ps.registerOutParameter( 2, OracleTypes.ARRAY,"PERSON_TAB" );
ps.execute();

oracle.sql.ARRAY returnArray = (oracle.sql.ARRAY)ps.getArray(2);
Object[] personDetails = (Object[]) returnArray.getArray();
Person person_record = new Person();
for (int i = 0; i < personDetails.length; i++) {
person_record = (Person)personDetails;
System.out.println( "row " + i + " = '" + person_record.person_name +"'" );
}

}

public static void main (String args[]){

try
{
ArrayDemo tfc = new ArrayDemo();
tfc.passArray();
}
catch(Exception e) {
e.printStackTrace();

}
}

public static Connection getConnection() {
try
{
Class.forName ("oracle.jdbc.OracleDriver");
return DriverManager.getConnection("jdbc:oracle:thin:@<<HostNanem>>:1523:VIS",
"username", "password");
}
catch(Exception SQLe) {
System.out.println("IN EXCEPTION BLOCK ");
return null;
}

}
}


and thats it. you are done.

Hope it atleast helps people to get started. Comments are appreciated. I can be reached at (sunilgct@yahoo.com) or ssunil@trianz.com

Thanks
Sunil.s