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
  1. O Primeiro Contato
  2. Criação de Estruturas
  3. Recuperação dos Dados
  4. Interação com Coleções
  5. Reestruturação do Mapa

    6. Nível Intermediário
  6. Trabalhando com Stream
  7. Trabalhando com XML
  8. Trabalhando com XML Grande
  9. Acessando Banco de Dados
  10. Acessando Preferências

    11. Nível Avançado
  11. Preenchimento de Template (inglês)
  12. Interoperabilidade com .NET (inglês)
  13. Runtime schema validation (inglês)

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

msql.setStatement("create sequence id_book");
msql.execute();

msql.setStatement("create sequence id_order");
msql.execute();

//load plpgsql if it was not loaded yet
msql.setStatement("create language 'plpgsql'");
msql.execute();

//now we can create stored procedures
msql.setStatement("create or replace function "+
        "insert_book(isbn_bk in varchar, "+
        "    title_bk in varchar, "+
        "    price_bk in numeric) returns void as $$ "+
        "DECLARE"+
        "    id numeric;"+
        "BEGIN"+
        "   id := nextval('id_book');"+
        ""+
        "   insert into book (id_book, isbn_book, "+
        "               title_book, price_book) "+
        "    values(id, isbn_bk, title_bk, price_bk);"+
        ""+
        "END;"+
        "$$ LANGUAGE plpgsql "+
        "SECURITY DEFINER;");        
msql.execute();

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. 

msql.setStatement("{ call insert_book(?, ?, ?) }");
HierarchicalMap hmap = new BasicHierarchicalMap();
        
hmap.put("isbn", "978-1441408488");
hmap.put("title", "Treasure Island");
hmap.put("price", new BigDecimal("6.94"));
msql.execute(hmap);

hmap.put("isbn", "978-0670062645");
hmap.put("title", "The Story of Ferdinand");
hmap.put("price", new BigDecimal("9.99"));
msql.execute(hmap);

hmap.put("isbn", "978-0689711732");
hmap.put("title", "Alexander and the Terrible, "+
                  "Horrible, No Good, Very Bad Day");
hmap.put("price", new BigDecimal("7.99"));
msql.execute(hmap);

hmap.put("isbn", "978-0060229351");
hmap.put("title", "Harold and the Purple Crayon");
hmap.put("price", new BigDecimal("11.55"));
msql.execute(hmap);

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: 

//Create stored procedure
msql.setStatement(
    "create or replace function list_book() "+
    "     returns refcursor as $$ "+
    "DECLARE"+
    "     r refcursor;"+
    "BEGIN"+
    "    open r for select id_book as id, "+
    "        isbn_book as isbn, "+
    "        title_book as title, "+
    "        price_book as price "+
    "    from book limit 10;"+
    ""+
    "    return r;"+
    "END;"+
    "$$ LANGUAGE plpgsql "+
    "SECURITY DEFINER;");        
msql.execute();

//Call stored procedure
msql.setStatement("{ ? = call list_book() }");

HierarchicalMap param = new BasicHierarchicalMap(); 
param.put("book", new MapSQLStatement.
              OutParameter(java.sql.Types.OTHER));

//Disable auto commit to preserve cursor 
con.setAutoCommit(false);
HierarchicalMap resp = msql.execute(param);

System.out.println(resp);

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