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

Posts tagged: database

JavaFX, JVx and data binding

We made great progress with our JavaFX UI for JVx. The whole UI stuff was already implemented and worked with some known problems. The next thing was data binding because this should fill the gap between UI and business logic. This means: bindings for a table, tree, chart, editors, cell editors, cell renderers, ... (hard work).

The data controls of JavaFX were very useful and we didn't implement our own controls because standard controls were working. Sure, we had a different model in JVx than the "model" in JavaFX, but also Swing and vaadin had different models. We're experts in such things.

We started with the table, because a table implementation should be easy without CRUD operations and of course, it was easy. The next thing was the integration of our cell renderers and cell editors because a date field should automatically use a date editor and a number field should use a number(only) editor. The same applies for checkboxes and comboboxes. This was a hard job because JavaFX' table had different concepts than e.g. swing. We're still working on the last bits but most functionality was implemented.

It's already possible to bind a database table/view to a JavaFX TableView without additional JDBC, ORM. Thanks to JVx, this works with all supported architectures (mem, 2-tier, 3-tier). Here's a first impression, followed by the source code:

TableView bound to JVx' DataBook

TableView bound to JVx' DataBook

UITable table = new UITable();
table.setDataBook(dataBook);

UIFormLayout editorsPaneLayout = new UIFormLayout();
editorsPaneLayout.setNewlineCount(2);

UIPanel editorsPane = new UIPanel();
editorsPane.setLayout(editorsPaneLayout);

addEditor(editorsPane, dataBook, "ID");
addEditor(editorsPane, dataBook, "BOOLEAN");
addEditor(editorsPane, dataBook, "STRING");
addEditor(editorsPane, dataBook, "CHOICE");
addEditor(editorsPane, dataBook, "DATETIME");
addEditor(editorsPane, dataBook, "NUMBER");
addEditor(editorsPane, dataBook, "TYPE_ID");
addEditor(editorsPane, dataBook, "TYPE_NAME");

UISplitPanel splitPanel = new UISplitPanel(UISplitPanel.SPLIT_LEFT_RIGHT);
splitPanel.setDividerAlignment(UISplitPanel.DIVIDER_BOTTOM_RIGHT);
splitPanel.setFirstComponent(table);
splitPanel.setSecondComponent(editorsPane);

UIPanel content = new UIPanel();
content.setLayout(new UIBorderLayout());
content.add(splitPanel, UIBorderLayout.CENTER);

(see Kitchensink application, hosted on github)

This was the whole source code for the table binding (= UI). The missing piece is the model. In our example, we've used the member dataBook. A databook is the model and controller of JVx. We have different implementations: MemDataBook, RemoteDataBook. A mem is like a database table, but in memory. A remote databook is connected to a remote/local storage. The storage provides data (from a database, filesystem, twitter, ...).

We didn't use a database in our kitchensink, so the dataBook was defined as MemDataBook:

RowDefinition rowdef = new RowDefinition();            
rowdef.addColumnDefinition(new ColumnDefinition("ID",
                                new BigDecimalDataType()));
rowdef.addColumnDefinition(new ColumnDefinition("STRING",
                                new StringDataType(new UITextCellEditor())));
rowdef.addColumnDefinition(new ColumnDefinition("BOOLEAN",
                                new BooleanDataType(new UICheckBoxCellEditor(
                                                    Boolean.TRUE, Boolean.FALSE))));
rowdef.addColumnDefinition(new ColumnDefinition("CHOICE",
                                new StringDataType(choiceCellEditor)));
rowdef.addColumnDefinition(new ColumnDefinition("DATETIME",
                                new TimestampDataType(new UIDateCellEditor("dd.MM.yyyy"))));
rowdef.addColumnDefinition(new ColumnDefinition("NUMBER",
                                new BigDecimalDataType(new UINumberCellEditor())));
rowdef.addColumnDefinition(new ColumnDefinition("TYPE_ID",
                                new BigDecimalDataType()));
rowdef.addColumnDefinition(new ColumnDefinition("TYPE_NAME",
                                new StringDataType(
                                    new UILinkedCellEditor(referenceDefinition))));
               
IDataBook dataBook = new MemDataBook(rowdef);
dataBook.setName("DATABINDING");               
dataBook.open();

It's the definition of a table with some columns and different column types, like String, Date, Number, ComboBox. It's easy to use a real database table if you read following article or this one.

The difference

What's the difference to standard JavaFX without JVx and why should you use JVx?

Here's the official documentation of Table View from Oracle. It's very complex to work with tables ;-) (seems to be so). The example in the official documentation doesn't use a database, like our example above!

The first difference: Save time and LoC.
This will reduce complexity and saves your dev time. It'll be easier to maintain an application with 1.000 LoC instead of 50.000.

Second difference: JVx already is a framework and library - don't create your own and waste dev time.

Advantage of JVx: Simply bind a database table with 10 LoC:

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

DBStorage dbs = new DBStorage();
dbs.setDBAccess(dba);
dbs.setWritebackTable("testtable");
dbs.open();

StorageDataBook dataBook = new StorageDataBook(dbs);
dataBook.open();

This is enough to get full CRUD support for your tables.

This article was the first intro of our databinding implementation for JavaFX. The next artile will cover our chart implementation. Here's first a impression:

FX Chart binding

FX Chart binding

We love the clean UI of JavaFX and our maximized internal frames :)

categories Development | rjahn | 04/04/2015 | comments Comments (0)

categories , , ,

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 | comments Comments (0)

categories ,

© SIB Visions GmbH