Simple DB application with JavaFX

Post to Twitter

In our last article, we told you that we're working on a JavaFX UI implementation for JVx. It's getting better every day and we think it's time to show you some source code :)

We use a very simple application for database access tests, it's this one:

javafx_DBaccess

It's a simple screen that shows a table of contacts with Search/Insert/Delete/Edit/Export options. There's a detail form that shows an image and all available information as editors. The editors were bound to the table and are always in sync.
You can change the value of an editor and the table will be updated automatically. The editors will be updated, if you change a cell in the table.

The Oracle database tables were created with following statements:

CREATE TABLE ACADEMICTITLES
(
  id             NUMBER(16) NOT NULL,
  academic_title VARCHAR2(200) NOT NULL
);
ALTER TABLE ACADEMICTITLES
  ADD PRIMARY KEY (ID);
ALTER TABLE ACADEMICTITLES
  ADD constraint ACTI_UK UNIQUE (ACADEMIC_TITLE);
CREATE TABLE COUNTRIES
(
  id      NUMBER(16) NOT NULL,
  country VARCHAR2(200) NOT NULL,
  eu      CHAR(1) DEFAULT 'N' NOT NULL
);
ALTER TABLE COUNTRIES
  ADD PRIMARY KEY (ID);
ALTER TABLE COUNTRIES
  ADD constraint CTRY_UK UNIQUE (COUNTRY);
CREATE TABLE HEALTHINSURANCES
(
  id               NUMBER(16) NOT NULL,
  health_insurance VARCHAR2(200) NOT NULL
);
ALTER TABLE HEALTHINSURANCES
  ADD PRIMARY KEY (ID);
ALTER TABLE HEALTHINSURANCES
  ADD constraint HEIN_UK UNIQUE (HEALTH_INSURANCE);
CREATE TABLE SALUTATIONS
(
  id         NUMBER(16) NOT NULL,
  salutation VARCHAR2(200)
)
ALTER TABLE SALUTATIONS
  ADD PRIMARY KEY (ID);
ALTER TABLE SALUTATIONS
  ADD constraint SALU_UK UNIQUE (SALUTATION);
CREATE TABLE CONTACTS
(
  id          NUMBER(16) NOT NULL,
  salu_id     NUMBER(16),
  acti_id     NUMBER(16),
  firstname   VARCHAR2(200) NOT NULL,
  lastname    VARCHAR2(200) NOT NULL,
  street      VARCHAR2(200),
  nr          VARCHAR2(200),
  zip         VARCHAR2(4),
  town        VARCHAR2(200),
  ctry_id     NUMBER(16),
  birthday    DATE,
  hein_id     NUMBER(16),
  filename    VARCHAR2(200),
  image       BLOB
);
ALTER TABLE CONTACTS
  ADD PRIMARY KEY (ID);
ALTER TABLE CONTACTS
  ADD constraint CONT_ACTI_ID_FK FOREIGN KEY (ACTI_ID)
  REFERENCES ACADEMICTITLES (ID);
ALTER TABLE CONTACTS
  ADD constraint CONT_CTRY_ID_FK FOREIGN KEY (CTRY_ID)
  REFERENCES COUNTRIES (ID);
ALTER TABLE CONTACTS
  ADD constraint CONT_HEIN_ID_FK FOREIGN KEY (HEIN_ID)
  REFERENCES HEALTHINSURANCES (ID);
ALTER TABLE CONTACTS
  ADD constraint CONT_SALU_ID_FK FOREIGN KEY (SALU_ID)
  REFERENCES SALUTATIONS (ID);

CREATE sequence SEQ_CONTACTS_ID
minvalue 1
maxvalue 9999999999999999
start WITH 1
increment BY 1
nocache
ORDER;
/

CREATE OR REPLACE TRIGGER TR_CONTACTS_BR_I
  before INSERT ON CONTACTS
  FOR each row
begin

  IF :new.id IS NULL then
    SELECT seq_contacts_id.NEXTVAL INTO :new.id FROM dual;
  end IF;

end;
/

You can see that the table definition contains foreign keys for every selection (like salutations or academic titles). It's a clean relational database model. All foreign keys are combobox editors editors in our GUI:

FK Editors

FK Editors

The birthday field is a date editor:

Date editor

Date editor

Here's the full source code of the application and I'll explain all relevant parts in detail:

package com.sibvisions.apps.javafx.db;

import javax.rad.application.IContent;
import javax.rad.application.genui.Application;
import javax.rad.application.genui.UILauncher;
import javax.rad.genui.celleditor.UIDateCellEditor;
import javax.rad.genui.celleditor.UIImageViewer;
import javax.rad.genui.celleditor.UINumberCellEditor;
import javax.rad.genui.component.UILabel;
import javax.rad.genui.container.UIGroupPanel;
import javax.rad.genui.container.UIPanel;
import javax.rad.genui.container.UISplitPanel;
import javax.rad.genui.control.UIEditor;
import javax.rad.genui.layout.UIBorderLayout;
import javax.rad.genui.layout.UIFormLayout;
import javax.rad.model.ModelException;
import javax.rad.ui.IContainer;
import javax.rad.util.event.IExceptionListener;

import com.sibvisions.apps.components.FilterEditor;
import com.sibvisions.apps.components.NavigationTable;
import com.sibvisions.rad.persist.StorageDataBook;
import com.sibvisions.rad.persist.jdbc.DBAccess;
import com.sibvisions.rad.persist.jdbc.DBStorage;

public class SimpleDBApplication extends Application
                                 implements IExceptionListener
{
    //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    // Class members
    //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    private static final String            NO_IMAGE    =
                       "/com/sibvisions/apps/vxdemo/images/nobody.gif";
    private static final UIImageViewer    IMAGE_VIEWER =
                       new UIImageViewer(NO_IMAGE);

    private StorageDataBook sdbContacts;
    private UIBorderLayout  blThis             = new UIBorderLayout();
    private UISplitPanel    splitMain          = new UISplitPanel();
    private NavigationTable navContacts        = new NavigationTable();

    private UIFormLayout    flDetails          = new UIFormLayout();
    private UIFormLayout    layoutDetails      = new UIFormLayout();
    private UIPanel         panDetails         = new UIPanel();
    private UIGroupPanel    gpanDedails        = new UIGroupPanel("Contact");
    private UIBorderLayout  blContacts         = new UIBorderLayout();
    private UIPanel         panContacts        = new UIPanel();
    private UIFormLayout    layoutSearch       = new UIFormLayout();
    private UIPanel         panSearch          = new UIPanel();
    private UILabel         lblSalutation      = new UILabel("Salutation");
    private UILabel         lblAcademicTitle   = new UILabel("Academic title");
    private UILabel         lblFirstName       = new UILabel("First name");
    private UILabel         lblLastName        = new UILabel("Last name");
    private UILabel         lblStreet          = new UILabel("Street");
    private UILabel         lblNr              = new UILabel("Nr");
    private UILabel         lblZip             = new UILabel("ZIP");
    private UILabel         lblTown            = new UILabel("Town");
    private UILabel         lblBirthday        = new UILabel("DoB");
    private UILabel         lblHealthInsurance = new UILabel("Health insurance");
    private UILabel         lblSearch          = new UILabel("Search");
    private UIEditor        edtSalutation      = new UIEditor();
    private UIEditor        edtAcademicTitle   = new UIEditor();
    private UIEditor        edtFirstName       = new UIEditor();
    private UIEditor        edtLastName        = new UIEditor();
    private UIEditor        edtStreet          = new UIEditor();
    private UIEditor        editNr             = new UIEditor();
    private UIEditor        edtZip             = new UIEditor();
    private UIEditor        edtTown            = new UIEditor();
    private UIEditor        edtCountry         = new UIEditor();
    private UIEditor        edtBirthday        = new UIEditor();
    private UIEditor        edtHealthInsurance = new UIEditor();
    private FilterEditor    edtSearch          = new FilterEditor();
    private UIEditor        icoImage           = new UIEditor();
   
    //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    // Initialization
    //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    /**
     * Creates a new instance of <code>Showcase</code>.
     *
     * @param pLauncher the launcher
     * @throws Throwable if the initialization failed
     */

    public SimpleDBApplication(UILauncher pLauncher) throws Throwable
    {
        super(pLauncher);
       
        setName("Simple DB application");
       
        initModel();
        initUI();
    }

    /**
     * Initializes model.
     *
     * @throws ModelException
     */

    private void initModel() throws ModelException
    {
        DBAccess dba = DBAccess.getDBAccess("jdbc:oracle:thin:@localhost:1521:xe");
        dba.setUsername("vxdemo");
        dba.setPassword("vxdemo");
        dba.open();
       
        DBStorage dbsContacts = new DBStorage();
        dbsContacts.setDBAccess(dba);
        dbsContacts.setWritebackTable("CONTACTS");
        dbsContacts.open();
       
        sdbContacts = new StorageDataBook(dbsContacts);
        sdbContacts.open();
       
        sdbContacts.getRowDefinition().getColumnDefinition("IMAGE").
             getDataType().setCellEditor(IMAGE_VIEWER);

        sdbContacts.getRowDefinition().getColumnDefinition("SOCIALSECNR").
             getDataType().setCellEditor(new UINumberCellEditor("0000"));
        sdbContacts.getRowDefinition().getColumnDefinition("BIRTHDAY").
             getDataType().setCellEditor(new UIDateCellEditor("dd.MM.yyyy"));
    }

    /**
     * Initializes UI.
     *
     * @throws Throwable if the initialization failed
     */

    private void initUI() throws Throwable
    {
        navContacts.setDataBook(sdbContacts);
        navContacts.setAutoResize(false);

        icoImage.setDataRow(sdbContacts);
        icoImage.setColumnName("IMAGE");
        icoImage.setPreferredSize(200, 140);

        edtSearch.setDataRow(sdbContacts);
        edtSalutation.setDataRow(sdbContacts);
        edtSalutation.setColumnName("SALU_SALUTATION");
        edtAcademicTitle.setDataRow(sdbContacts);
        edtAcademicTitle.setColumnName("ACTI_ACADEMIC_TITLE");
        edtFirstName.setDataRow(sdbContacts);
        edtFirstName.setColumnName("FIRSTNAME");
        edtLastName.setDataRow(sdbContacts);
        edtLastName.setColumnName("LASTNAME");
        edtStreet.setDataRow(sdbContacts);
        edtStreet.setColumnName("STREET");
        editNr.setDataRow(sdbContacts);
        editNr.setColumnName("NR");
        edtZip.setDataRow(sdbContacts);
        edtZip.setColumnName("ZIP");
        edtTown.setDataRow(sdbContacts);
        edtTown.setColumnName("TOWN");
        edtCountry.setDataRow(sdbContacts);
        edtCountry.setColumnName("CTRY_COUNTRY");
        edtBirthday.setDataRow(sdbContacts);
        edtBirthday.setColumnName("BIRTHDAY");
        edtHealthInsurance.setDataRow(sdbContacts);
        edtHealthInsurance.setColumnName("HEIN_HEALTH_INSURANCE");

        panSearch.setLayout(layoutSearch);
        panSearch.add(lblSearch, layoutSearch.getConstraints(0, 0));
        panSearch.add(edtSearch, layoutSearch.getConstraints(1, 0, -1, 0));

        panContacts.setLayout(blContacts);
        panContacts.add(panSearch, UIBorderLayout.NORTH);
        panContacts.add(navContacts, UIBorderLayout.CENTER);

        gpanDedails.setLayout(flDetails);
        gpanDedails.add(icoImage, flDetails.getConstraints(0, 0, 1, 7));
        flDetails.setHorizontalGap(15);
        gpanDedails.add(edtSalutation, flDetails.getConstraints(3, 0));
        flDetails.setHorizontalGap(5);
        gpanDedails.add(edtAcademicTitle, flDetails.getConstraints(3, 1));

        flDetails.setHorizontalGap(15);
        gpanDedails.add(lblSalutation, flDetails.getConstraints(2, 0));
        flDetails.setHorizontalGap(5);
        gpanDedails.add(lblAcademicTitle, flDetails.getConstraints(2, 1));
        gpanDedails.add(lblFirstName, flDetails.getConstraints(2, 2));
        gpanDedails.add(edtFirstName, flDetails.getConstraints(3, 2, -1, 2));
        gpanDedails.add(lblLastName, flDetails.getConstraints(2, 3));
        gpanDedails.add(edtLastName, flDetails.getConstraints(3, 3, -1, 3));
        gpanDedails.add(lblBirthday, flDetails.getConstraints(2, 4));
        gpanDedails.add(edtBirthday, flDetails.getConstraints(3, 4, 3, 4));
        gpanDedails.add(lblHealthInsurance,
                        flDetails.getConstraints(2, 5));
        gpanDedails.add(edtHealthInsurance,
                        flDetails.getConstraints(3, 5, -1, 5));
        gpanDedails.add(lblStreet, flDetails.getConstraints(2, 6));
        gpanDedails.add(edtStreet, flDetails.getConstraints(3, 6, -3, 6));
        gpanDedails.add(lblNr, flDetails.getConstraints(-2, 6));
        gpanDedails.add(editNr, flDetails.getConstraints(-1, 6));
        gpanDedails.add(lblZip, flDetails.getConstraints(2, 7));
        gpanDedails.add(edtZip, flDetails.getConstraints(3, 7));
        gpanDedails.add(lblTown, flDetails.getConstraints(4, 7));
        gpanDedails.add(edtTown, flDetails.getConstraints(5, 7, -1, 7));

        panDetails.setLayout(layoutDetails);
        panDetails.add(gpanDedails,
                       layoutDetails.getConstraints(0, 0, -1, 0));

        splitMain.setDividerPosition(250);
        splitMain.setDividerAlignment(UISplitPanel.DIVIDER_TOP_LEFT);
        splitMain.setFirstComponent(panContacts);
        splitMain.setSecondComponent(panDetails);

        setLayout(blThis);
        add(splitMain, UIBorderLayout.CENTER);
       
        setPreferredSize(1024, 768);
    }
   
    //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    // Interface implementation
    //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    public IContainer getContentPane()
    {
        return this;
    }

    public <OP> IContent showMessage(OP pOpener, int pIconType,
                                     int pButtonType, String pMessage,
                                     String pOkAction, String pCancelAction)
                            throws Throwable
    {
        System.out.println(pMessage);
       
        return null;
    }

    public void handleException(Throwable pThrowable)
    {
        pThrowable.printStackTrace();
    }
   
}

The application is really minimal and doesn't use a menu or toolbar, it's naked and only a frame with a panel in it. The class has 3 important parts. The first is the constructor where it gets the UILauncher:

public SimpleDBApplication(UILauncher pLauncher) throws Throwable
{
    super(pLauncher);
       
    setName("Simple DB application");
       
    initModel();
    initUI();
}

The launcher is responsible for starting an application, in our case it's the JavaFX implementation and the application will be started as real JavaFX application. The constructor sets the name (= frame title) and initializes our model and user interface.

The model is "mixed" in our simple application because we have a fat client that connects directly to the database without communication or application server. The initialization is short:

DBAccess dba = DBAccess.getDBAccess("jdbc:oracle:thin:@localhost:1521:xe");
dba.setUsername("demo");
dba.setPassword("demo");
dba.open();
               
DBStorage dbsContacts = new DBStorage();
dbsContacts.setDBAccess(dba);
dbsContacts.setWritebackTable("CONTACTS");
dbsContacts.open();
               
sdbContacts = new StorageDataBook(dbsContacts);
sdbContacts.open();

We create a new connection to our Oracle database via DBAccess and create a DBStorage for our CONTACTS table. The "real model" is our StorageDataBook because it's the class which is needed from our UI. The rest of the code:

sdbContacts.getRowDefinition().getColumnDefinition("IMAGE").
     getDataType().setCellEditor(IMAGE_VIEWER);

sdbContacts.getRowDefinition().getColumnDefinition("SOCIALSECNR").
     getDataType().setCellEditor(new UINumberCellEditor("0000"));
sdbContacts.getRowDefinition().getColumnDefinition("BIRTHDAY").
     getDataType().setCellEditor(new UIDateCellEditor("dd.MM.yyyy"));

is just column configuration and formatting, e.g. use an image viewer instead of a text editor.

The UI initialization is trivial, because it creates UI components, sets a layout and adds the components to the main panel.The interesting part in the initUI method is the model binding:

navContacts.setDataBook(sdbContacts);
...
edtSalutation.setDataRow(sdbContacts);
edtSalutation.setColumnName("SALU_SALUTATION");
edtAcademicTitle.setDataRow(sdbContacts);
edtAcademicTitle.setColumnName("ACTI_ACADEMIC_TITLE");
edtFirstName.setDataRow(sdbContacts);
edtFirstName.setColumnName("FIRSTNAME");

The first command sets the contacts databook as "datasource" for the Table view. A navigation table is a simple Table view with additional buttons for Inserting/Deleting/Editing/Exporting records.

The next command is interesting because it binds the contacts databook to an editor:

edtSalutation.setDataRow(sdbContacts);
edtSalutation.setColumnName("SALU_SALUTATION");

but with a column name which doesn't exist in our database table: SALU_SALUTATION.

This is a special feature of DBStorage because it checks foreign key columns and creates dynamic "not existing" columns for referenced tables. It's a little bit magic but it reduces source code to a bare minimum. The contacts storage also knows how to fetch such "not existing" columns because we don't have storages or databooks for our SALUTATIONS or ACADEMICTITLES table. We don't need references because our contacts storage handles everything. Oh.. this magic can be deactivated and it's always possible to do the same programatically, but we love the so called "automatic link detection".

The rest of the class is not relevant, e.g.

public IContainer getContentPane()
{
    return this;
}

public <OP> IContent showMessage(OP pOpener, int pIconType,
                                 int pButtonType, String pMessage,
                                 String pOkAction, String pCancelAction)
                        throws Throwable
{
    System.out.println(pMessage);
       
    return null;
}

public void handleException(Throwable pThrowable)
{
    pThrowable.printStackTrace();
}

It's only the implementation of some abstract and interface methods, but it has no functionality.

And that was all you need to create a simple CRUD application. It's fully functional and allows Insert/Update/Edit operations without additional code. It makes no difference if you edit directly in the table or in the form via editors. The editors work automatically with the right datatype and show a combobox in case of FK columns and a date chooser in case of a date datatype, e.g.:

Combo Box

Combo Box

There's no need to create a java.sql.Connection manually or use java.sql.PreparedStatement for insert/update/delete. This funcationality was encapsulated in JVx' DBStorage and we simply use the functionality for our JavaFX UI and in all other UI technologies as well.

Leave a Reply

Spam protection by WP Captcha-Free