7.2. Defining the database model and database connection

First of all, we will design the database model. (If a database model already existed, then we could skip this task and simply re-use the existing model). DbForms has no special requirements for the database model. Here is a simple model which addresses the requirements defined above.

7.2.1. Conceptual design

The following database model will be used in this tutorial.

Figure 7.1. Conceptual diagram of the database

Conceptual diagram of the database

7.2.2. Physical table creation

Here is the SQL code for creating the database model shown in the preceeding section. The example is for a MySQL database.

Example 7.1. SQL code for creating the database tables

   CREATE TABLE services 
     id          int        NOT NULL PRIMARY KEY,
     name        char (30)  NULL,
     description char (255) NULL

   CREATE TABLE customers 
     id        int       NOT NULL PRIMARY KEY,
     firstname char (50) NULL,
     lastname  char (50) NULL,
     address   char (30) NULL,
     pcode     char (10) NULL,
     city      char (40) NULL

   CREATE TABLE orders
     order_id     int       AUTO_INCREMENT,
     customer_id  int       NOT NULL,
     service_id   int       NULL,
     orderdate    char (20) NULL,
     PRIMARY KEY ( order_id, customer_id )

   CREATE TABLE complaints 
     complaint_id int        AUTO_INCREMENT,
     customer_id  int        NOT NULL,
     usermessage  char (255) NULL,
     incomingdate char (20)  NULL,
     priority     int        NULL,
     PRIMARY KEY ( complaint_id, customer_id )

   CREATE TABLE priorities
     id          int        NOT NULL PRIMARY KEY,
     shortname   char (12)  NULL,
     description char (100) NULL


It would be desirable to add referential integrity, by adding foreign keys and/or triggers) but to keep things simple for this tutorial on DbForms capabilities, we have not included them.

7.2.3. Defining a database description for DbForms

The next step is to create the XML-based description of this model, as follows.

Example 7.2. Defining tables in dbforms-config.xml

<?xml version="1.0" encoding="ISO-8859-1" ?>

  <table name="services">
    <field name="id"          fieldType="int" isKey="true" />
    <field name="name"        fieldType="char" />
    <field name="description" fieldType="char" />
  <table name="customers">
    <field name="id"        fieldType="int" isKey="true" />
    <field name="firstname" fieldType="char" />
    <field name="lastname"  fieldType="char" />
    <field name="address"   fieldType="char" />
    <field name="pcode"     fieldType="char" />
    <field name="city"      fieldType="char" />
  <table name="orders">
    <field name="order_id"    fieldType="int" isKey="true"
                              autoInc="true" />
    <field name="customer_id" fieldType="int" isKey="true" />
    <field name="service_id"  fieldType="char" sortable="true"/>
    <field name="orderdate"   fieldType="char" sortable="true"/>
  <table name="complaints">
    <field name="complaint_id" fieldType="int" isKey="true"
                               autoInc="true" />
    <field name="customer_id"  fieldType="int" isKey="true" />
    <field name="usermessage"  fieldType="char" />
    <field name="incomingdate" fieldType="char" sortable="true"/>
    <field name="priority"     fieldType="int"  sortable="true"/>
  <table name="priorities">
    <field name="id"          fieldType="int" isKey="true" />
    <field name="shortname"   fieldType="char" />
    <field name="description" fieldType="char" />

    name = "java:comp/env/jdbc/tutorial"
    isJndi = "true"
     isPow2 = "true"
     connectionProviderClass = 
     name = 
     isJndi = "false"
     conClass = "org.hsqldb.jdbcDriver"
     username = "sa"
     password = ""



There exists a DbForms tool which does all the painful work of transferring a database schema into XML format. See Chapter 5, DevGui for more information.

This example includes samples of two db:dbconnection elements. Refer to Chapter 2, DbForms Concepts for a discussion of the dbconnection element. The example that is not commented out uses an in-memory HSQL database that requires no setup time to run.