André L. S. Desenvolvimento de Softwares, Tecnologia e Games

Passando Objetos Java para uma procedure do Oracle

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 aqui.