In the enterprise where I work was a discussion about possibility to pass a Java objects into an Oracle procure or function, then I looked for and here is a simple solution to reach this objective.
This tutorial only work with Oracle9i , or above, and using the JDBC driver ojdbc14g, or above.
First, we create the tables, objects and procedures. Remember: the types tbl_users and user_type must be declared out of packages, as global types:
-- Creating table
create table tbl_user(user_name varchar2(100), height number, b_date date);
/
--Creating type user_type (own bean)
create or replace type user_type as object (user_name varchar2(100), height number, birth_date date);
/
--Creating type arr_users, table of user_type (array of user_type)
create or replace type arr_users as table of user_type;
/
Creating specification and body of package.
--Spec
create or replace package PAC_BEAN is
type ref_cur is ref cursor;
-- Procedure used to insert values
procedure pro_insert_user(usu in user_type);
-- Procedure used to select
procedure pro_select_user(usu in user_type, user_return in out arr_users);
end PAC_BEAN;
/
--Body
create or replace package body PAC_BEAN is
--The insert procedure will receive user_type and put him into table tbl_user.
procedure pro_insert_user(usu in user_type) is
begin
insert into tbl_user (user_name, height, b_date)
values (usu.user_name, usu.height, usu.birth_date);
commit;
exception
when others then
rollback;
end pro_insert_user;
--The procedure used for select will receive a user_type (where clause) and will return the array arr_users
procedure pro_select_user(usu in user_type, user_return in out arr_users)is
user_ref_cur ref_cur;
--Instancing the array
users arr_users := arr_users();
begin
--Opening the cursor that will return the array
open user_ref_cur for
select cast(
multiset(
select user_name,
height,
b_date
from tbl_user
where user_name like '%'||usu.user_name||'%'
) as arr_users
) arr
from dual;
--Putting the cursor into arr_users instance.
fetch user_ref_cur into users;
--Returning the instance through OUT variable
user_return := users;
end pro_select_user;
end PAC_BEAN;
/
See CAST and MULTISET about how they work.
Built database objects, we need prepare the JavaBean. It’ll an implementation of java.sql.SQLData, because it will be necessary to implement the methods:
getSQLTypeName() – Getter used to obtain the name of type.
readSQL(SQLInput, String) – Used to convert an object in Java object.
writeSQL(SQLOutput stream) – Used to mount a SQL object, used byJDBC Driver.
public class TypeUser implements SQLData{
//Name declared in Oracle
public static final String ORACLE_OBJECT_NAME = "USER_TYPE";
//Array name declared in Oracle
public static final String ORACLE_USER_ARRAY_NAME = "ARR_USERS";
//The attributes
private String name;
private Float height;
private Date birth;
public TypeUser() {
height = 0F;
}
public String getSQLTypeName() throws SQLException {
return ORACLE_OBJECT_NAME;
}
public void readSQL(SQLInput stream, String typeName) throws SQLException {
setName(stream.readString());
setHeight(stream.readFloat());
setBirth(stream.readDate());
}
public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeString(getName());
stream.writeFloat(getHeight());
stream.writeDate(getBirth() != null ?
new java.sql.Date(getBirth().getTime()) : null);
}
//getters and setters omitted
}
To define a type that can will be send to procedure be necessary add him into type map through Connection.getTypeMap(). This method return the Map<String,Class<?>>, where type name is the key and class of SQLData implementation is the value, in own case the type TypeUser. Sample:
Map<String,Class<?>> typeMaps = connection.getTypeMap();
typeMaps.put(TypeUser.ORACLE_OBJECT_NAME, TypeUser.class);
Then the connection will be:
//Making connection
Class.forName("oracle.jdbc.driver.OracleDriver");
connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:<host>:<port>:<db>","<user>","<pass>");
//Mapping necessary tipes
Map<String,Class<?>> typeMaps = connection.getTypeMap();
typeMaps.put(TypeUser.ORACLE_OBJECT_NAME, TypeUser.class);
Now, we create the insert method, it receive an instance of TypeUser and Connection:
CallableStatement cs = null;
try {
//call the procedure
cs = conn.prepareCall("{call PAC_BEAN.PRO_INSERT_USER(?)}");
//defining the instance of TypeUser as variable IN "usu"
cs.setObject("usu", typeUser);
cs.execute();
} catch (SQLException e) {
e.printStackTrace();
}
Now, the select method. This method return a object array, then be necessary insert the type of array in Connection TypeMap. The name passed as key should be equals of Oracle’s array name and the values will be the array class, like this:
connection.getTypeMap().put(TypeUser.ORACLE_USER_ARRAY_NAME, TypeUser[].class);
To call procedure and registerOutParameter:
cs = conn.prepareCall("{call PAC_BEAN.PRO_SELECT_USER(?,?)}");
cs.registerOutParameter("user_return", OracleTypes.ARRAY, TypeUser.ORACLE_USER_ARRAY_NAME);
cs.setObject("usu", typeUserQry);
cs.execute();
To obtain the array, do it:
//user_return is the OUT variable name
Object[] array = (Object[])cs.getArray("user_return").getArray();
If until here is alright, smile, to obtain array values just iterate him and cast each index to Type User.
for(Object obj : array){
System.out.println("Nome: " + ((TypeUser)obj).getName());
System.out.println("Altura: " + ((TypeUser)obj).getHeight());
System.out.println("Data de Nascimento: " + sdf.format(((TypeUser)obj).getBirth()));
}
So we can pass and retrieve simple Java objects of a Oracle procedure or function. The next step, Retrieving objects collection from Oracle procedure.
See ya!
Download the source code of this tutorial here.
<host>:<porta>:<bd>","<usuario>","<senha>"