Na empresa onde trabalho houve uma discussão sobre a possibilidade de passar um objeto Java para dentro de uma procedure ou function do Oracle, então resolvi pesquisar e aqui está uma forma bem simples de atingir este objetivo.
Este tutorial só funciona com a versão 9i do Oracle, ou superior, e usando o driver JDBC ojdbc14g, ou superior.
Primeiro precisaremos criar as tabelas, objetos e procedures. Lembrando que os tipos tbl_users e user_type deverão ser declarados como globais para funcionar, ou seja, fora de packages:
-- Criando a tabela
create table tbl_user(user_name varchar2(100), height number, b_date date);
/
--Criando o tipo user_type (nosso bean)
create or replace type user_type as object (user_name varchar2(100), height number, birth_date date);
/
--Criando o tipo arr_users, que é do tipo table of user_type (array de user_type)
create or replace type arr_users as table of user_type;
/
Criando o spec e body da package que conterá as procedures
--Spec
create or replace package PAC_BEAN is
-- REF CURSOR deve ser declarado dentro da package
type ref_cur is ref cursor;
-- Procedure usada para o insert
procedure pro_insert_user(usu in user_type);
-- Procedure usada para o 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
--A procedure de insert receberá o tipo user_type (nosso bean)
--e o cadastrará na tabela 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;
--Procedure de select que receberá um user_type com a informação do nome
--(cláusula where) e retornará o array arr_users (array de user_type)
procedure pro_select_user(usu in user_type, user_return in out arr_users)is
user_ref_cur ref_cur;
--Instanciando o array
users arr_users := arr_users();
begin
--Abrindo o cursor que retornará nosso 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;
--Jogando o retorno do cursor dentro da instância de arr_users
fetch user_ref_cur into users;
--Retornando a instância através da variável OUT
user_return := users;
end pro_select_user;
end PAC_BEAN;
/
Consulte sobre o funcionamento do CAST e MULTISET.
Construído os objetos de banco precisamos prepara o JavaBean. Ele será uma implementação de java.sql.SQLData, por isso será necessário implementar os métodos:
getSQLTypeName() – É o getter usado para obter o nome do tipo.
readSQL(SQLInput, String) – Usado para converter o objeto SQL em objeto Java.
writeSQL(SQLOutput stream) – Usado para montar o objeto SQL, usado pelo Driver JDBC.
public class TypeUser implements SQLData{
//O nome do tipo declarado no Oracle
public static final String ORACLE_OBJECT_NAME = "USER_TYPE";
//O nome do array declarado no Oracle
public static final String ORACLE_USER_ARRAY_NAME = "ARR_USERS";
//Os atributos
private String name;
private Float height;
private Date birth;
public TypeUser() {
height = 0F;
}
//Getter retorna o nome do tipo ao JDBC
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 e setters omitidos
}
Para definir que um tipo poderá ser enviado à procedure é necessário adicioná-lo ao mapa de tipos através de Connection.getTypeMap(). Este método retorna um Map<String,Class<?>>, onde a chave é o nome do tipo e o valor será a classe SQLData implementada, no nosso caso a TypeUser definida acima. Exemplo:
Map> typeMaps = connection.getTypeMap();
typeMaps.put(TypeUser.ORACLE_OBJECT_NAME, TypeUser.class);
A conexão ficará desta forma:
//Fazendo a conexão
Class.forName("oracle.jdbc.driver.OracleDriver");
connection = DriverManager.getConnection("jdbc:oracle:thin:@<host>:<porta>:<bd>","<usuario>","<senha>");
//Mapeando o tipo necessário
Map<String,Class<?>> typeMaps = connection.getTypeMap();
typeMaps.put(TypeUser.ORACLE_OBJECT_NAME, TypeUser.class);
Agora que temos a nossa conexão podemos fazer o método de insert, que receberá uma instância TypeUser e uma Connection:
CallableStatement cs = null;
try {
//chamando a procedure de insert
cs = conn.prepareCall("{call PAC_BEAN.PRO_INSERT_USER(?)}");
//definindo a instância de TypeUser como paramêtro "usu" da procedure
cs.setObject("usu", typeUser);
cs.execute();
} catch (SQLException e) {
e.printStackTrace();
}
Agora que temos o método de insert, partiremos para o método de select. Como este método retorna um array de objetos, é necessário inserir o tipo do array TypeMap da conexão. O nome passado como chave deve ser o nome do tipo do array no Oracle e o valor será a classe do array que esperamos, desta forma:
connection.getTypeMap().put(TypeUser.ORACLE_USER_ARRAY_NAME, TypeUser[].class);
Para a chamada da procedure e 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();
O método para recuperar o array deverá ser chamado desta forma:
//user_return é o nome da variável OUT da procedure
Object[] array = (Object[])cs.getArray("user_return").getArray();
Se até aqui deu tudo certo fique contente, para resgatar os valores do array é só iterá-lo e fazer cast para a classe TypeUser.
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()));
}
Desta forma poderemos passar e resgatar objetos simples de uma procedure/function do Oracle.
Próximo passo: Recuperando coleção de objetos de uma procedure Oracle.
Até lá!
Baixe o código fonte deste tutorial e da segunda parte aqui
