This website uses cookies for visitor traffic analysis. By using the website, you agree with storing the cookies on your computer.More information

Plain JDBC vs. DBStorage

Did you ever use plain JDBC? For sure.

Wasn't it horrible because of so much boilerplate code? There are different solutions like Hibernate, EclipseLink, MyBatis, .... But such libraries create mappings, annotations, xml files, POJOs, ....

The idea behind JDBC is great but the API generates a lot of code and work.

I want to show you how easy DB access could be, without overhead.
First I want to define the table for our test:

CREATE TABLE t_calculate
(
  id integer GENERATED BY DEFAULT AS IDENTITY(start WITH 1) PRIMARY KEY,
  costs decimal(10) DEFAULT 0,
  factor decimal(2) DEFAULT 10,
  STATUS char(1) DEFAULT '?',
  description varchar(100)
)

The following snippet opens a HSQLDB connection inserts 3 records, checks the auto-generated id of the first inserted record, counts the number of records and does some filtering by id and value.

Here's the code:

//OPEN DB connection        
Class.forName("org.hsqldb.jdbcDriver");

Connection connection = DriverManager.getConnection(
                           "jdbc:hsqldb:hsql://localhost/testdb", "sa", "");

try
{
    //pre-create statements
    PreparedStatement psInsert = connection.prepareStatement(
                                       "insert into t_calculate (costs) values (?)");
    PreparedStatement psFetchById  = connection.prepareStatement(
                                       "select * from t_calculate where id = ?");

    try
    {
        //INSERT first record
        psInsert.setObject(1, BigDecimal.valueOf(35));
        psInsert.execute();
       
        //check generated ID
        CallableStatement csId = connection.prepareCall("CALL IDENTITY()");
        ResultSet resKey = csId.executeQuery();
       
        if (resKey.next())
        {
            Long id = (Long)resKey.getObject(1);
           
            psFetchById.setObject(1, id);
           
            ResultSet res = psFetchById.executeQuery();
           
            try
            {
                if (res.next())
                {
                    Assert.assertEquals(Long.valueOf(1), id);
                    Assert.assertEquals(BigDecimal.valueOf(10), res.getBigDecimal("FACTOR"));
                    Assert.assertEquals("?", res.getString("STATUS"));
                }
                else
                {
                    Assert.fail("Couldn't fetch record!");
                }
            }
            finally
            {
                res.close();
            }
        }
        else
        {
            Assert.fail("Couldn't fetch identity!");
        }
       
        //INSERT second record
        psInsert.setObject(1, BigDecimal.valueOf(40));
        psInsert.execute();
       
        //INSERT third record
        psInsert.setObject(1, BigDecimal.valueOf(45));
        psInsert.execute();
 
        //Check record count
        Statement stmt = connection.createStatement();
       
        ResultSet res = stmt.executeQuery("select count(*) from t_calculate");
       
        try
        {
            if (res.next())
            {
                Assert.assertEquals(3, res.getInt(1));
            }
            else
            {
                Assert.fail("Couldn't fetch record count!");
            }
        }
        finally
        {
            res.close();
        }
       
        //FILTER by ID
        psFetchById.setObject(1, Long.valueOf(2));
       
        try
        {
            res = psFetchById.executeQuery();

            if (res.next())
            {
                Assert.assertEquals(40, res.getInt("COSTS"));
            }
            else
            {
                Assert.fail("Couldn't fetch records with id = 1!");
            }
        }
        finally
        {
            res.close();
        }
       
        //FILTER by value (COSTS)
        PreparedStatement psFetchByCosts  = connection.prepareStatement(
                                     "select count(*) from t_calculate where costs >= ?");
        psFetchByCosts.setObject(1, BigDecimal.valueOf(40));
       
        try
        {
            res = psFetchByCosts.executeQuery();
           
            try
            {
                if (res.next())
                {
                    Assert.assertEquals(2, res.getInt(1));
                }
                else
                {
                    Assert.fail("Couldn't fetch records with costs >= 40!");
                }
            }
            finally
            {
                res.close();
            }
        }
        finally
        {
            psFetchByCosts.close();
        }
    }
    finally
    {
        psInsert.close();
        psFetchById.close();
    }
}
finally
{
    connection.close();
}

Nice, isn't it. Hopefully I didn't forget a close() call. The use-case was simple and not really complex compared to real world problems, but the LoC are extreme. All together 144 lines (comments included).

Sure you could write a small utility to auto-close statements and to read result sets, but why re-inventing the wheel? The most problems seem to be trivial but gets really complex.

I want to show you different solutions with DBStorage from JVx framework. It's a small class with extreme power. It handles whole JDBC for you, solves CRUD and more. The class is a server-side class and was designed for multi-tier environments but can also be used directly on client-side.

Here's the code, for the same use-case as before:

DBAccess dba = DBAccess.getDBAccess("jdbc:hsqldb:hsql://localhost/testdb");
dba.setUsername("sa");
dba.setPassword("");
dba.open();

try
{
    DBStorage dbs = new DBStorage();
    dbs.setDBAccess(dba);
    dbs.setWritebackTable("t_calculate");
    dbs.open();
   
    //inserting records
    Bean bean35 = new Bean();
    bean35.put("COSTS", BigDecimal.valueOf(35));
   
    bean35 = dbs.insert(bean35);

    Bean bean40 = new Bean();
    bean40.put("COSTS", BigDecimal.valueOf(40));
   
    bean40 = dbs.insert(bean40);

    Bean bean45 = new Bean();
    bean45.put("COSTS", BigDecimal.valueOf(45));

    bean45 = dbs.insert(bean45);
   
    Assert.assertEquals(BigDecimal.valueOf(1), bean35.get("ID"));
    Assert.assertEquals(BigDecimal.valueOf(10), bean35.get("FACTOR"));
    Assert.assertEquals("?", bean35.get("STATUS"));
   
    //filtering
    List<IBean> liBeans = dbs.fetchBean(null, null, 0, -1);
   
    Assert.assertEquals(3, liBeans.size());
   
    IBean bean = dbs.fetchBean(new Equals("ID", BigDecimal.valueOf(2)));
   
    Assert.assertEquals(BigDecimal.valueOf(40), bean.get("COSTS"));
   
    liBeans = dbs.fetchBean(new GreaterEquals("COSTS", BigDecimal.valueOf(40)), null, 0, -1);
   
    Assert.assertEquals(2, liBeans.size());
}
finally
{
    CommonUtil.close(dba);
}

Better? Only 49 LoC.

As I told you before, the DBStorage is a server-side class and the API wasn't designed as client API. But there are some very useful classes in JVx. We call them DataBooks. A databook can be compared to a database table because it is row and column oriented. We have different DataBooks like MemDataBook, RemoteDataBook or StorageDataBook. MemDataBook is the base of all our databooks and RemoteDataBook extends it. The StorageDataBook extends the RemoteDataBook. A remote databook gets its records from a remote storage. The storage databook directly gets its records from a DBStorage.
Sounds tricky? A little bit, but these classes allow using multi-tier architectures without making a difference how many tiers you use.

Here's the same example again, but using a StorageDataBook for using a DBStorage with client API:

DBAccess dba = DBAccess.getDBAccess("jdbc:hsqldb:hsql://localhost/testdb");
dba.setUsername("sa");
dba.setPassword("");
dba.open();

try
{
    DBStorage dbs = new DBStorage();
    dbs.setDBAccess(dba);
    dbs.setWritebackTable("t_calculate");
    dbs.open();

    StorageDataBook sdb = new StorageDataBook(dbs);
    sdb.open();
   
    //inserting records
    sdb.insert(false);
    sdb.setValues(new String[] {"COSTS", "DESCRIPTION"},
                  new Object[] {BigDecimal.valueOf(35), "Record with costs = 35"});
    sdb.insert(false);
    sdb.setValues(new String[] {"COSTS", "DESCRIPTION"},
                  new Object[] {BigDecimal.valueOf(40), "Record with costs = 40"});
    sdb.insert(false);
    sdb.setValues(new String[] {"COSTS", "DESCRIPTION"},
                  new Object[] {BigDecimal.valueOf(45), "Record with costs = 45"});
   
    sdb.saveAllRows();
   
    sdb.setSelectedRow(0);
   
    Assert.assertEquals(BigDecimal.valueOf(1), sdb.getValue("ID"));
    Assert.assertEquals(BigDecimal.valueOf(10), sdb.getValue("FACTOR"));
    Assert.assertEquals("?", sdb.getValue("STATUS"));

    //filtering
    Assert.assertEquals(3, sdb.getRowCount());
   
    sdb.setFilter(new Equals("ID", BigDecimal.valueOf(2)));
   
    Assert.assertEquals(1, sdb.getRowCount());
   
    Assert.assertEquals(BigDecimal.valueOf(40), sdb.getValue("COSTS"));
   
    sdb.setFilter(new GreaterEquals("COSTS", BigDecimal.valueOf(40)));

    Assert.assertEquals(2, sdb.getRowCount());
}
finally
{
    CommonUtil.close(dba);
}

The LoC: 51 (but we could save 3 lines).
The code doesn't use a remote storage, because we don't use a multi-tier architecture, but how does it look with a multi-tier architecture?

The server in our example is a Tomcat application server and we connect via HttpConnection. You could also use different server implementations like vert.x or a plain socket server.

Here is the code:

HttpConnection con = new HttpConnection("http://localhost/myapp/services/Server");

MasterConnection macon = new MasterConnection(con);
macon.open();

try
{
    RemoteDataSource rds = new RemoteDataSource(macon);
    rds.open();
   
    RemoteDataBook rdb = new RemoteDataBook();
    rdb.setDataSource(rds);
    rdb.setName("calculate");
   
    //inserting records
    rdb.insert(false);
    rdb.setValues(new String[] {"COSTS", "DESCRIPTION"},
                  new Object[] {BigDecimal.valueOf(35), "Record with costs = 35"});
    rdb.insert(false);
    rdb.setValues(new String[] {"COSTS", "DESCRIPTION"},
                  new Object[] {BigDecimal.valueOf(40), "Record with costs = 40"});
    rdb.insert(false);
    rdb.setValues(new String[] {"COSTS", "DESCRIPTION"},
                  new Object[] {BigDecimal.valueOf(45), "Record with costs = 45"});
   
    rdb.saveAllRows();
   
    rdb.setSelectedRow(0);
   
    Assert.assertEquals(BigDecimal.valueOf(1), rdb.getValue("ID"));
    Assert.assertEquals(BigDecimal.valueOf(10), rdb.getValue("FACTOR"));
    Assert.assertEquals("?", rdb.getValue("STATUS"));

    //filtering
    Assert.assertEquals(3, rdb.getRowCount());
   
    rdb.setFilter(new Equals("ID", BigDecimal.valueOf(2)));
   
    Assert.assertEquals(1, rdb.getRowCount());
   
    Assert.assertEquals(BigDecimal.valueOf(40), rdb.getValue("COSTS"));
   
    rdb.setFilter(new GreaterEquals("COSTS", BigDecimal.valueOf(40)));

    Assert.assertEquals(2, rdb.getRowCount());
}
finally
{
    CommonUtil.close(macon);
}

LoC: 50 (but we could save the same 3 lines as before)

The API calls are the same, but we used a HttpConnection, a MasterConnection, a RemoteDataSource and a RemoteDataBook. The HttpConnection could be replaced with e.g. NetConnection or any other transport protocol. The MasterConnection is protocol independent and handles the communication between client and server-tier. The RemoteDataSource is the datasource for RemoteDataBook. The RemoteDataBook communicates indirectly with a DBStorage on the server-tier.

The DBStorage definition for the server-side is the same as in all other examples:

DBAccess dba = DBAccess.getDBAccess("jdbc:hsqldb:hsql://localhost/testdb");
dba.setUsername("sa");
dba.setPassword("");
dba.open();

but the server-tier contains the definition. The client doesn't know connection settings and credentials in that case.

Sure, there's a difference between fat-client, client/server and multi-tier architecture, but have you ever seen an easier API to use a multi-tier architecture?

The DBStorage and DataBooks have a lot of useful APIs for you and your database applications. It solves all CRUD operations, take care of column metadata and much more.

JVx is Apache 2.0 licensed.

categories Development | rjahn | 21/02/2015

categories ,

Leave a Reply

Spam protection by WP Captcha-Free

© SIB Visions GmbH