Tutorial Page - Intermediate Level

Tutorial do HierarchicalMap - Nível Intermediário

HierarchicalMap é uma interface. Assim, qualquer um pode implementá-la. Este tutorial é baseado em BasicHierarchicalmap, uma implementação referência em Java da interfae HierarchicalMap.

Este tutorial abrange os seguintes assuntos:

Nível Básico

Nível Intermediário

Nível Avançado

(O recurso de Javascript precisa estar habilitado, no seu navegador, para permitir a visualização dos códigos utilizados nos exemplos deste tutorial)

Nível Intermediário

9. Acessando Banco de Dados

Neste capítulo, exploraremos a classe org.dhmp.io.MapSQLStatement. Ela é similar à classe java.sql.CallableStatement e pode ser usada para enviar comandos SQL passando os parâmetros em HierarchicalMap e obter o resultset retornado mapeado para HierarchicalMap.

Executando Instruções

Vamos começar criadno um modelo como abaixo:

Ele é composto por 3 tabelas simples: livro, pedido e item. O código seguinte criará a tabela em Postgres, assumindo que a porta, o usuário e a senha estejam corretos. Poderão encontrar os exemplos em Oracle Express e SQL Server Express, no caso de teste do código fonte deste projeto.

try {
    Class.forName("org.postgresql.Driver");
    Connection con=DriverManager.getConnection(
            "jdbc:postgresql://localhost:5432/"+
            "postgres",
            "postgres",
            "dhmp.org");
    
    MapSQLStatement msql = new MapSQLStatement(con);
    msql.setStatement("create table book ("+
        "id_book numeric(10),"+
        "isbn_book varchar(50),"+
        "title_book varchar(250),"+
        "price_book numeric(15,2),"+
        "constraint book_pk primary key (id_book))");
    msql.execute();
    
    msql.setStatement("create table book_order ("+
        "id_order numeric(10),"+
        "dt_order TIMESTAMP,"+
        "constraint order_pk "+
        "primary key (id_order))");
    msql.execute();
    
    msql.setStatement("create table order_item ("+
        "id_item numeric(4),"+
        "id_order numeric(10),"+
        "id_book numeric(10),"+
        "qty_item numeric(4),"+
        "constraint order_item_pk "+
        "primary key (id_item))");
    msql.execute();
}
catch(Exception ex) {
    ex.printStackTrace();
}

Agora, podemos inserir alguns valores na tabela recém criada. Antes, queremos que os id's sejam único para livros e para pedidos. O objeto sequencia, ou o identity no caso de SQL Server, serve para esta finalidade. Frequentemente, utilizamos o stored procedure para encapsular estas operações. Segue abaixo um stored procedure para inserir um livro na tabela de livros.

Acionando Stored Procedures

Uma vez criado o stored procedure, podemos acionar através do MapSQLStatement. Para aumentar a segurança de um sistema, é uma boa prática, criar os objetos de base de dados sob usuários privilegiados e, acionar o stored procedure com usuário de aplicação com mínimo de privilégios. Um outro ponto é a utilização de instruções preparadas, prepared statement, em vez de execução de comandos montados com concatenação de strings. Isto é muito importante para evitar a vulnerabilidade de injeção de SQL.

Note que a instrução SQL não identifica o parâmetro de entrada com nomes. Assim, os paramêtros são recuperados sequencialmente do HierarchicalMap. As chaves associadas aos valores dos parâmetros são somente para melhorar a legibilidade do código fonte e pode ser omitida.

Um ponto relevante é que a classe dos parâmetros precisam coincidir com os tipos esperados pelo banco de dados.

O código abaixo criará um outro stored procedure, para listar o conteúdo da tabela, executa o mesmo e retorna o resultado numa HierarchicalMap:

O resultado será algo parecido com a seguinte listagem:

<book>
   <id>1</id>
   <isbn>978-1441408488</isbn>
   <title>Treasure Island</title>
   <price>6.94</price>
</book>
<book>
   <id>2</id>
   <isbn>978-0670062645</isbn>
   <title>The Story of Ferdinand</title>
   <price>9.99</price>
</book>

...

Recebendo Parâmetros de Saída

A classe MapSQLStatement.OutParameter serve para identificar os parâmetros de saída. Seu construtor recebe o java.sql.Type que indica o tipo que será retornado pelo banco de dados.

O valor retornado será adicionado ao HierarchicalMap com a chave passada na definição dos parâmetros.

A classe MapSQLStatement.InOutParameter tem o propósito similar, mas indica parâmetros que servem como entrada e também como saída. O seu construtor recebe o valor de entrada além do tipo.

//Create stored procedure
msql.setStatement(
    "create or replace function insert_order"+
    "  (order_id out numeric, order_dt out timestamp)"+
    "  as $$"+
    "BEGIN"+
    "       order_id := nextval('id_order');"+
    "       order_dt := now();"+
    "       insert into book_order "+
    "                  (id_order, dt_order)"+
    "       values(order_id, order_dt);"+
    ""+
    "END;"+
    "$$ LANGUAGE plpgsql "+
    "SECURITY DEFINER;"+
    ""+
    ""+
    "create or replace function insert_order_item"+
    "  (order_id in numeric, "+
    "   item_id inout numeric, book_id in numeric, "+
    "   item_qty in numeric) as $$"+
    "BEGIN"+
    "       item_id := item_id + 1;"+
    "       insert into order_item (id_item,"+
    "              id_order, id_book, qty_item)"+
    "          values(item_id, order_id, "+
    "              book_id, item_qty);"+
    ""+
    "END;"+
    "$$ LANGUAGE plpgsql "+
    "SECURITY DEFINER;");
            
msql.execute();

//activate auto commit
con.setAutoCommit(true);

//Call stored procedure
//insert order
MapSQLStatement msql = new MapSQLStatement(con);
msql.setStatement("{ call insert_order(?, ?) }");
HierarchicalMap param = new BasicHierarchicalMap();
param.put("id", new MapSQLStatement.
    OutParameter(java.sql.Types.NUMERIC));
param.put("date", new MapSQLStatement.
    OutParameter(java.sql.Types.TIMESTAMP));
HierarchicalMap resp = msql.execute(param);

//retrieve the id of the inserted order
BigDecimal id = (BigDecimal) resp.get("id");

//add items to the order
msql.setStatement(
     "{ call insert_order_item(?, ?, ?, ?) }");
param = new BasicHierarchicalMap();

param.put("order_id", id);
param.put("seq", new MapSQLStatement.
    InOutParameter(new Integer("0"), 
         java.sql.Types.NUMERIC));
param.put("book_id", new Integer("2"));
param.put("qty", new Integer("2"));
resp = msql.execute(param);

param.put("order_id", id);
param.put("seq", new MapSQLStatement.
InOutParameter(resp.get("seq"), 
    java.sql.Types.NUMERIC));
param.put("book_id", new Integer("3"));
param.put("qty", new Integer("1"));
resp = msql.execute(param);

//order inserted
//now retrieve them calling list_order procedure

//must set autocommit off before using cursors
//in some data base 
con.setAutoCommit(false);
param = new BasicHierarchicalMap();

param.put("order_id", id);
param.put("date", new 
   MapSQLStatement.OutParameter(java.sql.Types.TIMESTAMP));

//SQLServer can not return refcursor
//instead, it simply returns a resultset
//for all registries retieved in a select statement
//e.g.
//create procedure list_order @order_id numeric,
//    @order_dt datetime output as
//BEGIN
//    select @order_dt = dt_order from book_order where
//        id_order = @order_id;
//
//    select a.id_item as seq, b.title_book as title,
//            b.isbn_book as isbn, a.qty_item as quantity
//        from order_item a, book b
//        where a.id_order = 
//            @order_id and b.id_book = a.id_book;
//
//END;
//
//grant execute on dbo.list_order to hmapuser
//
//and call it as below
//
//msql.setStatement("{ call list_order(?, ?) }");
//param.put("items",
//    new MapSQLStatement.ResultSetParameter());

//now for Postgres
msql.setStatement("{ call list_order(?, ?, ?) }");
param.put("items", 
   new MapSQLStatement.OutParameter(java.sql.Types.OTHER));

//oracle is similar to Postgres though it defines 
//OracleTypes.CURSOR to be used as output type
//for refcursors instead of java.sql.Types.OTHER

resp = msql.execute(param);

//remove date that will differ on each execution
resp.remove("date");

System.out.println(resp);

Note que a classe MapSQLStatement.ResultSet é usada para associar uma chave ("items" no exemplo acima) aos registros retornados no resultset. A chave padrão é "resultset" para o primeiro conjunto de resultset, "resultset1" para o segundo e assim por diante.

O resultado será algo como abaixo:

<items>
   <seq>1</seq>
   <title>The Story of Ferdinand</title>
   <isbn>978-0670062645</isbn>
   <quantity>2</quantity>
</items>
<items>
   <seq>2</seq>
   <title>Alexander and the Terrible, 
      Horrible, No Good, Very Bad Day</title>
   <isbn>978-0689711732</isbn>
   <quantity>1</quantity>
</items>

Nota: quebras de linha foram adicionadas na listagem acima, para melhorar a visualização da página, mas elas não são retornadas ao executar o código.

Resultsets Múltiplos

Em algumas ocasiões, é desejável que mais de um resultset seja retornado numa única chamada ao stored procedure. A classe MapSQLStatement é capaz de identificar estes resultsets retornados e mapear para a HierarchicalMap.

Lembre-se que cada banco de dados possui formas diferentes para tratar o resultset. Por exemplo, Postgres e Oracle só conseguem retornar resultsets como parâmetros de saída, já a única maneira para o SQL Server é retornar como sequência de selects.

O código seguinte precisa ser executado após rodar o último exemplo, isto é, depois de preencher corretamente o pedido e os itens do pedido.

//Create stored procedure
msql.setStatement(
  "create or replace function list_all("+
  "    order_id in numeric, order_dt out timestamp, "+
  "    books out refcursor, items out refcursor) as $$ "+
  "BEGIN"+
  "    select dt_order into order_dt from book_order"+
  "       where id_order = order_id;"+
  ""+
  "    open books for select id_book as id,"+
  "          isbn_book as isbn, title_book as title,"+
  "          price_book as price"+
  "       from book limit 10;"+
  ""+
  "    open items for select a.id_item as seq,"+
  "          b.title_book as title, b.isbn_book as isbn,"+
  "          a.qty_item as quantity "+
  "       from order_item a, book b "+
  "       where a.id_order = order_id"+
  "          and b.id_book = a.id_book;"+
  "END;"+
  "$$ LANGUAGE plpgsql "+
  "SECURITY DEFINER;");
            
msql.execute();

BigDecimal id = (BigDecimal) new BigDecimal("1");

HierarchicalMap param = new BasicHierarchicalMap();

param.put("order_id", id);
param.put("date",
    new MapSQLStatement.
        OutParameter(java.sql.Types.TIMESTAMP));

//SQLServer can not return refcursor
//instead, it simply returns a resultset
//for all registries retieved in a select statement
//e.g.
//create procedure list_all @order_id numeric,
//@order_dt datetime output as
//BEGIN
//   select @order_dt = dt_order from book_order where 
//      id_order = @order_id;
//
//   select top 10 id_book as id, isbn_book as isbn,
//         title_book as title, price_book as price
//      from book;
//
//   select a.id_item as seq, b.title_book as title,
//         b.isbn_book as isbn, a.qty_item as quantity
//      from order_item a, book b
//      where a.id_order = 
//         @order_id and b.id_book = a.id_book;
//
//END
//
//grant execute on dbo.list_all to hmapuser
//
//and call it as below
//
//msql.setStatement("{ call list_all(?, ?) }");
//param.put("list", 
//    new MapSQLStatement.ResultSetParmaeter());
//param.put("items",
//    new MapSQLStatement.ResultSetParmaeter());

//Postgres and Oracle can not return several resultsets
//though can return ref cursors
msql.setStatement("{ call list_all(?, ?, ?, ?) }");

param.put("list", new MapSQLStatement.
    OutParameter(java.sql.Types.OTHER));
param.put("items", new MapSQLStatement.
    OutParameter(java.sql.Types.OTHER));

HierarchicalMap resp = msql.execute(param);

//remove date that will differ on each execution
resp.remove("date");

System.out.println(resp);

O resultado será parecido com a listagem a seguir:

<list>
    <id>1</id>
    <isbn>978-1441408488</isbn>
    <title>Treasure Island</title>
    <price>6.94</price>
</list>
<list>
    <id>2</id>
    <isbn>978-0670062645</isbn>
    <title>The Story of Ferdinand</title>
    <price>9.99</price>
</list>
<list>
    <id>3</id>
    <isbn>978-0689711732</isbn>
    <title>Alexander and the Terrible, 
    Horrible, No Good, Very Bad Day
    </title>
    <price>7.99</price>
</list>
<list>
    <id>4</id>
    <isbn>978-0060229351</isbn>
    <title>Harold and the Purple Crayon</title>
    <price>11.55</price>
</list>
<items>
    <seq>1</seq>
    <title>The Story of Ferdinand</title>
    <isbn>978-0670062645</isbn>
    <quantity>2</quantity>
</items>
<items>
    <seq>2</seq>
    <title>Alexander and the Terrible, 
    Horrible, No Good, Very Bad Day
    </title>
    <isbn>978-0689711732</isbn>
    <quantity>1</quantity>
</items>

Nota: quebras de linha foram adicionadas na listagem acima, para melhorar a visualização da página, mas elas não são retornadas ao executar o código.

Continua para Nível Intermediário - Acessando Preferências