HierarchicalMap Tutorial - Intermediate Level

HierarchicalMap is an interface. So anyone can implement it. This tutorial is based on BasicHierarchicalmap, a reference implementation of HierarchicalMap.

Following subjects are covered in this tutorial:

    Basic Level
  1. Getting Started
  2. Creating Structure
  3. Recovering the Data
  4. Interacting with Collections
  5. Restructuring the Map

    6. Intermediate Level
  6. Working with Stream
  7. Working with XML
  8. Handling Large XML
  9. Accessing Data Base
  10. Accessing Preferences

    11. Advanced Level
  11. Template Filling
  12. .NET Interoperability
  13. Runtime schema validation
  14. Writing Application

    15. Planned for Future
  15. IDE's plugin for coding-time schema validation and code completion

(Javascript must be enabled to allow syntax highlighting for source code snippets in this tutorial)

Intermediate Level

9. Accessing DataBase

For this chapter, we will explore the org.dhmp.io.MapSQLStatement class. It is similar to java.sql.CallableStatement and can be used to issue SQL commands receiving HierarchicalMap as input and retrieving resultset mapped to HierarchicalMap.

Executing Statements

Start creating a model like below:

It is composed by 3 simple tables: book, book order and order item. The following code can create the table in Postgres assuming that port number, database user and password are correct. (There is a testcase in the source code where you can find a example for Oracle Express and SQL Server Express). 

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();
}

Now, we can insert some values on recently created tables. Also we want a unique id for books and orders. The sequence (or use identity for SQL Server) suits for that purpose. It is good practice to create a stored procedure to encapsulate these operations. 

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();

Calling Procedures

Then, we can invoke the stored procedure using MapSQLStatement. Actually, it is best practice to create database object using system user and execute the stored procedure using ordinary user to grant minimum privilege to the application user. Another point is the use of prepared statement instead of direct execution from concatenated String command. This is very important to avoid SQL Injection vulnerability. 

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 that SQL statement has no named parameter. Thus the stored procedures parameters are retrieved sequentially from HierarchicalMap. The key, associated to the parameter value, is only for improving the readability of source code and could be omitted.

A relevant point is that parameter's class type must match the SQL type expected from data base.

The following code will create another stored procedure to list the table content and execute it, retrieving the result into 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);

The result should be something like the following: 

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

...

Receiving Output Parameters

The MapSQLStatement.OutParameter class is an wrapper to identify output type parameter. The constructor receives the java.sql.Type that indicates the type of output parameter to be registered.

The return value will be added to HierarchicalMap under the key passed in parameter.

The MapSQLStatement.InOutParameter class can be used in similar way for stored procedure with a in/out parameter. Check the sample code below: 

//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 that MapSQLStatement.ResultSet is used to label the returning resultset as "items". The default label is "resultset" for the first resultset, "resultset1" for the second and so on.

The result should be like the following: 

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

Note: line break was applied to the title element for better viewing of this page.

Multiple Resultsets

Sometimes, it is desirable to retrieve more than one resultset in a single stored procedure call. The MapSQLStatement can identify the resultsets and map it into HierarchicalMap.

Note that each database has different approach for resultset handling. For instance, Postgres and Oracle can only return resultsets as output parameters while SQL Server must return them as sequence of selects.

The next code must be executed after last sample code, i.e. after filling the order and order items properly. 

//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);

The result should be like the following: 

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

Note: line break was applied to the title element for better viewing of this page.

Continue to Intermediate Level - Accessing Preferences