Using Data-Base-Access (DBA) Objects for low level access to database-tables –
Java Base Knowledge.

This document deals with Data-Base-Access (DBA) Objects.
DBA Objects contain the code to perform a low level access to database-tables.

The idea is:
one class for each database-table where the table-name and the attribute-names are defined as constants.
This minimizes the possibility to get runtime-errors due to typing errors at attribute-names.

Why design a separate object for Data-Base-Access (and not integrate the DBA into Business Objects) :

The classes for Business-Objects contain enough sophisticated code to handle the business case – to add code for database access would make it more complex :

• Not really a good point – shouldn't a code be mainly written for using the resources at a maximum ;-).
  True – but if the performance is the same I prefer the better readable code.
  As values are usually accessed 'by reference', there is no extra CPU-time requiered to move values within the memory if a method in another class is called.

• As a database-table might be accessed by more than one Business Object, the code to access the database-system might be present multiply in several Business Objects.
  An example is data for a 'Product', which is accessed by Business Objects for the 'Product', the 'Order', the 'Delivery Notice' and the 'Invoice'.
  As the time to write code multiply is not for free, it is also cost-efficient to keep a philosophy 'write once – use as often a possible'.

Business-Objects reflect more complex data-structures and do not relate 1:1 to a database-table:

• Variables of Business Objects – if they are not extremely simple - are usually collected from more than one database-table.
  To incorporate the database-access into Business Objects (the 'Server-Side' one) would mean, that code to be read from different database-tables would be put in one Business Object.

• Access to a database-table might be from different Business Objects.
  Aside from the time (and costs) of writing the same code at multiple places, the system to delegate low level database access to DBA Objects has also the advantage that adding new attributes to a database-table needs only a change of the related DBA Object.
  The risk of getting run-time-errors as some Business Objects were forgotten to be re-coded is minimized.

• Here is a scheme how several Business Objects might use one DBA Object.
  

top.

What makes up a Data-Base-Access (DBA) Object:

A Base Class for defining and handling the Common Attributes

For a discussion, why Common Attributes are introduced, please refer to the document Common Attributes for all database-tables.

Before reading ahead, I recommend to browse through the code of the Base Class JSBS_DBA.
It contains

• Constants for
* the names of the attributes,
* strings with the attributes that will be part of the commands to SELECT, INSERT and UPDATE.

• Variables that hold the values for the Common Attributes.

• Methods that
* transfer the values from the Result-Set (a java-specific class containing the result-values after a SQL-query) to the variables of the class,
* fill the the PreparedStatement (a Java-specific class that contain the statement and the values needed to perform a INSERT or UPDATE) with the values from the variables of the class.

A Class taylored for the database-table with methods for DELETE. INSERT, UPDATE and SELECT where (by definition) only one database-record can be found.

If you want to see the code of a DBA class (with not too many variables) please follow this link.

The class inherits the base class (JSBS_DBA) and adds the following:

• Constants for
* the name for the database-table,
* the names of the attributes (specific for this table, additional to the common attributes),
* strings with the attributes that will be part of the commands to SELECT, INSERT and UPDATE.

• Variables that hold the values for the attributes specific for this table.

• Methods that
* transfer the values from the Result-Set (a java-specific class containing the result-values after a SQL-query) to the variables of the class,
* fill the the PreparedStatement (a Java-specific class that contain the statement and the values needed to perform a INSERT or UPDATE) with the values from the variables of the class,
* perform DELETE, INSERT, UPDATE and SELECT operations on the database – where the SELECT operations are according to selection criterias that deliver a single database-record as result.

A Class taylored for the database-table with SELECT-operations that may deliver more than one database-record as result

If you want to see the code of a DBA class (with not to many variables) where the selection may deliver more than one database-record, please follow this link.

The class consists mainly of:

• A Vector (from package java.util) which will hold the DBA objects (each representing a database-record):
The objects in the vector are of the class that reflects the database-table the SELECT-operation targets.

• Methods to perform SELECT-operations against the database-table.
There are no methods for DELETE, INSERT and UPDATE operations implemented in this class.

top.

To-Do-List for coding a Data-Base-Access Object (and the object with the 'Vector'):

It is a relatively long way from having the GUI (Graphic User Interface) designed to store the entered data in the database, retrieve it (according to some selection criteria) and finally make the values visible on the GUI again.
Depending on the number of attributes in the table the turn-around may take 10 hours and more without getting a visible feedback if the code is implemented error-free.
In my experience, the following sequence (including the coding of the Business Object that needs the Data-Base-Access) has proven as best to get a feedback about the correct operation of the code as soon as possible.

Implement the Business Object (General Class) and start with the Business Object (Client-Side Class)

The To-Do List to code the mentioned Classes for the Business Object is out of the scope of this document.
Please refer to the following links if you are just browsing to this document.
If you are following a To-Do-List to write an application, you should have gone already through these documents. ;-).
Business Object, General Class | To-Do-List
Business Object, Client-Side-Class | To-Do-List

Implement this DBA Class so far that the Business Object (Server-Side Class) can call the minimum set of methods.

Steps to code the class:

• Create a plain java-class with the class-name 'Application_Table_DBA' under the package 'application.dba'.

• Inherit from the class JSBS_DBA :
  Example:
  public class JS_ErrDB_Project_DBA extends JSBS_DBA {

• Code the variables specific for this DBA class.
The variables have to reflect the attributes of the database-table this DBA class is performing operations onto.
Example (to see the relevant code of a complete class please follow this link):
/*
 * Project-Code, that is the shortcut for the Project. */
    public String ProjectCode = "";

• Code the constants with the name of the database-table and the attributes of the table.
This is done, as the Java-Compiler can not detect typing errors within strings with commands for database-operations.
To have the names of the table and the attributes defined in constants minimises the possibility of typing errors which will lead to run-time-errors – and being therefore detected pretty late.
Example (to see the relevant code of a complete class please follow this link):
/*
 * Name of the database-table */
    public final static String CONST_TABLE_NAME = "Project";
/* Project-Code */
    public final static String CONST_ProjectCode = "ProjectCode";

• Code the constants with the SQL-commands (or parts of it) that are multiply used within the implementation.
Defining this strings has the advantage to minimize the possibility if typing errors on seldom used SQL-commands which can not be detected by the Java-compiler and might not be caught by a test-case.
Please obey, that CONST_COMMON_ATTRIBUTES_LIST and CONST_COMMON_ATTRIBUTES_LIST_FOR_UPDATE is defined in the base-class JSBS_DBA.
Example (to see the relevant code of a complete class please follow this link):
/*
 * String with all attributes;
 * can be used when accessing database-tables for SELECT and INSERT. */
    final static String CONST_ALL_ATTRIBUTES_LIST =
        CONST_COMMON_ATTRIBUTES_LIST + ", " +
        CONST_ProjectCode + ", " +
        CONST_LanguageCode + ", " +
        CONST_TargetDirectory;
/*
 * String with all attributes to be used for UPDATE on database-records. */
    final static String CONST_ALL_ATTRIBUTES_LIST_FOR_UPDATE =
        "update " + CONST_TABLE_NAME + " set " +
        CONST_COMMON_ATTRIBUTES_LIST_FOR_UPDATE +
        CONST_ProjectCode + " =?, " +
        CONST_LanguageCode + " =?, " +
        CONST_TargetDirectory + " =? "+
        " where ";
/*
 * String to get all attributes at a SELECT operation. */
    final static String CONST_SELECT_ALL_ATTRIBUTES =
        "select " +
        CONST_ALL_ATTRIBUTES_LIST +
        " from " + CONST_TABLE_NAME;

• Code the methods to transfer the variables of the class to the (JAVA-internal)classes used for the database-operation.
The code in these methods is used by all other methods performing SQL-operations toward the database.
There are two classes in the (Java-internal) java.sql-package for transfering a SQL-command to the database-system and getting the result-values from the database-system.
The class 'PreparedStatement' holds the SQL-command and the class 'ResultSet' holds the retrieved values after a SELECT command.
The method setValuesToSQLStatement(PreparedStatement parmSQLStatement) transfers the values of the object to the class 'PreparedStatement' so that the values can be inserted into the database-table or update an existing database-record.
The method getValuesFromSQLResultSet(ResultSet parmSQLResultSet) transfers the values from the class 'ResultSet' – which is filled after a SELECT command - to the DBA object.
As this code is needed in every method performing INSERT, UPDATE or SELECT commands toward the database-system, it makes sense to implement it only once – that saves work and makes it more resistant against errors.
Please see the methods ( getValuesFromSQLResultSet(ResultSet parmSQLResultSet) and setValuesToSQLStatement(PreparedStatement parmSQLStatement)) at the class used for the example.

• Code the method to INSERT the values of the object into the database.
As some data has to be entered into the database-table before it can be read again, it is advisible to code this method first.
Please see insert(Connection parmDBCon) at the class used for the example.

• Code the method to UPDATE the values of the object in the database.
Similar to the code for INSERT is the one for an UPDATE of a record of the database-table.
Please see update(Connection parmDBCon) at the class used for the example.

• Code the methods to SELECT unique database-records.
Per definition this DBA object handles just operations that concern a single database-record.
There are two SELECT operations implemented here, that – by definition – deliver the values of a single record:
readByDataSetID(Connection parmDBCon, double parmDataSetID) and
readByUserKnownKey(Connection parmDBCon, JSBS_MinimalParameters parmMinParm,
                   String parmProjectCode, String parmLanguageCode).
For the code please refer to the class used for the example.
N.B.: To see th documentation of a DBA object to SELECT a set of database-records, please follow this link.

The implementation steps (for this class) are complete to allow the implementation of the Server-Side class of the Business Object.
Depending on the requirement of the Business Object using the DBA object, there might arise a demand for more complex methods to be implemented.

top.

Related Documents:

Detailed descriptions concerning Business Objects can be found in the following documents:

• Common Attributes for all database-tables - Advanced Java Fat-Client-Development.