Overview

This document describes the AIS.PL database mapping tool (called DB Mapping Tool) - a set of Ant tasks intended to facilitate database access from Java applications.

General Configuration

DB Mapping Tool is a set of Ant tasks (and Java classes) that generate specific Java source code. When invoked, they generate classes which handle database access. The DB Mapping Tool classes are packed into aislib-tools-mapping-0.1dev.jar. The tool is configured through an XML file (typically called structure.xml). The DTD for the file is available at http://www.ais.pl/dtds/mapping_0_4.dtd. Public ID for this file is -//AIS.PL//DTD Mapping Description 0.4//EN. Additionally, each Ant task requires some parameters set in the build XML file (e.g. build.xml).

Ant Tasks

The following Ant tasks are available:

  • generateBeans (runs BeanGenerator class).
  • generateHelpers (runs BeanHelperGenerator class).
  • generateDatabase (runs DatabaseGenerator class).
  • generateMapHelpers (runs MapHelperGenerator class).
The first three tasks must be called in order to generate the database related source code. The fourth task may be called optionally to generate additional describe/populate methods. Note: The names of the tasks depend on Ant configuration and are subject to change. In case of problems contact your system administrator.

Each of the four Ant tasks requires the following parameters:

  • destinationDir - directory which will hold the generated source files;
  • mappingFile - path to the structure.xml file (see below);
  • packageName - package name of the application; the generated classes will be placed in sub-packages.
The optional parameters are as follows:
  • databaseClassName - may be used with generateDatabase task to specify the name of the main class. Defaults to ApplicationDatabase.
  • objectsSubpackage - may be used with all tasks to specify the name of subpackage holding classes which are the counterparts of database records. More precisely, the generated abstract classes will be placed in objectsSubpackage.base subpackage and the programmer will be responsible for providing concrete implementations in objectsSubpackage subpackage. Note, that all tasks must have the same value of this parameter, otherwise the code won't compile. Defaults to objects.

Here is a sample target from build.xml file for the Projector application:

  <target name="db">
    <property name="src.dir"  value="src/java/pl/aislib/apps/projector"/>
    <property name="map.file" value="ddl/structure.xml"/>
    <property name="package"  value="pl.aislib.apps.projector"/>
    <generateBeans       destinationDir="${src.dir}" mappingFile="${map.file}" packageName="${package}"/>
    <generateHelpers     destinationDir="${src.dir}" mappingFile="${map.file}" packageName="${package}"/>
    <generateDatabase    destinationDir="${src.dir}" mappingFile="${map.file}" packageName="${package}"/>
    <generateMapHelpers  destinationDir="${src.dir}" mappingFile="${map.file}" packageName="${package}"/>
  </target>
      
Now ant db generates suitable source code assuming you want the source code to be placed under src/java/pl/aislib/apps/projector, you have well formed ddl/structure.xml file and your application package is pl.aislib.apps.projector. DB Mapping Tool generates the following directory structure under src/java/pl/aislib/apps/projector:
  • objects/base - directory containing BaseX classes. These abstract classes are generated by generateBeans task (BeanGenerator class). The corresponding subclasses should be added by the programmer to the objects directory. This directory location (and corresponding Java subpackage) may be changed by using objectsSubpackage parameter.
  • dbhandlers - directory containing classes with static methods defined mainly in operations element (see below). These classes are generated by generateHelpers task (BeanHelperGenerator class).
  • handlers - directory containing classes with static describe/populate methods. These classes are generated by generateMapHelpers task (MapHelperGenerator class).
  • ApplicationDatabase.java - this class, generated by generateDatabase task (DatabaseGenerator class), represents database in the application. Typically this class is extended to provide application-specific methods. The name of this class (and file) may be changed by specifying databaseClassName parameter when invoking generateDatabase task.

Locally, at AIS, we use build_pl.xml and antpl to reflect our local configuration.

XML File (structure.xml)

The DTD can be reached at http://www.ais.pl/dtds/mapping_0_4.dtd. structure.xml defines relations between Java classes and database tables/columns. This file may also contain some types of SQL-DML statements.

The topmost element in structure.xml is the database element. It can embody one or more structure elements.

structure element

Defines mapping between the Java class and the underlying SQL table.

Attribute: name - name of structure, required.

sql-table element

The underlying SQL table. Multiple classes (structures) may share the same table. Note that you can use views here if the database engine supports them. This element is obligatory.

The attributes control the generation of delete/insert/select/update methods. Delete, select and update methods use primary keys, so if the structure has no primary keys then delete, select and update attributes should be set to false.

Attributes:

  • name - name of SQL table, required.
  • delete - when true delete-related methods will be generated, default true.
  • insert - when true insert-related methods will be generated, default true.
  • select - when true select-related methods will be generated, default true.
  • update - when true update-related methods will be generated, default true.

java-class element

Defines name of the Java class corresponding to this structure.

Attributes:

  • name - name of Java class, required.
  • propertyChangeSupport - if true then the generated class will support adding and removal of PropertyChangeListener objects (see the dedicated section). Defaults to false.

fields element

Defines fields shared between an SQL table and the corresponding Java class. Must contain at least one field element. One or more fields can be marked as the primary key. This affects delete, insert and select related-methods.

field element

Defines field shared between SQL column and Java class.

Attributes:

  • name - name of field, required.
  • notNull - currently not used but required (true or false). The future versions may use this attribute - it is recommended to set it to true if the underlying SQL column doesn't allow null values and to set it to false otherwise.
  • primaryKey - if true then field is treated as a (part of) primary key, required.

sql-field element

Defines the database part of field.

Attributes:

  • name - name of database column, required.
  • type - type of database column. When set to CLOB the corresponding java-field must have its type attribute set to String. In such a case the tool will handle mapping between String class and CLOB column. When set to something other than CLOB the Tool will ignore it and depend on conversions done by JDBC driver. Required attribute.
  • default - default value of database column, optional.
  • onInsert - may contain two values: use or omit. When set to omit this field will be omitted during new record insertion. This allows to use things like MySQL's auto_increment option. When set to use this field is always inserted. Defaults to use.

java-field element

Defines the Java part of field.

Attributes:

  • name - name of Java variable, required.
  • type - class name of Java variable, required. The following classes are accepted: BigDecimal, Double, Float, Integer, Long, String, and Timestamp. The Array class from java.sql package is partially supported.
  • default - default value of Java variable. The variable will be set to this value during object creation. Optional attribute.

operations element

Within this element it is possible to define some types of DML statements and the corresponding Java method signatures. You don't define here statements that operate on primary key (e.g. a query returning objects for structure's primary key) - these are generated automatically.

This element is optional and if defined, it may contain any combination of aggregate, call, count, delete, select and update elements. Each of these elements consists of sql-query and java-method sub-elements. sql-query controls the DML string. java-method controls (partially) the signature of Java method associated with the DML string. Additionally, a call element requires a call-params sub-element.

aggregate element

Defines SELECT statement which should return only one column.

Attribute: multipleRows - defines the possibility of returning multiple values by the generated method. May be either true or false (default).

multipleRows = false

Defines SELECT statement which should return only one column and one row as a result. The generated DML string looks like this: SELECT [value of columns attribute in sql-query] FROM [sql-table]. DISTINCT, WHERE, GROUP BY, HAVING and ORDER BY clauses may be used. The [sql-table] is controlled by from attribute of sql-query element if such an attribute is defined. The generated Java method returns the value located in the first column of first row in the result. The return type of the Java method is determined in java-method element. The purpose of this element is to generate queries like SELECT MAX(amount) FROM table. It may also be used to return sequence values: SELECT my_seq.nextval FROM DUAL.

multipleRows = true

Defines SELECT statement which should return only one column and any number of rows as a result. The generated DML string looks like this: SELECT [value of columns attribute in sql-query] FROM [sql-table]. DISTINCT, WHERE, GROUP BY, HAVING and ORDER BY clauses may be used. The [sql-table] is controlled by from attribute of sql-query element. The generated Java method returns the values located in the first column of each row in the result. The return type of the Java method is List. The type of its elements is determined in java-method element. The purpose of this element is to generate queries like SELECT DISTINCT years FROM billingrollup.

call element

Defines query which is executed by using a CallableStatement object. The query may be a stored procedure call or a procedure body. The sql-query sub-element must contain body attribute and no other attributes, it doesn't support '??' markers. The value of body is directly passed to Connection.prepareCall method. The return type of the generated Java method depends on call-params sub-element which is a required sub-element. The generated method always have at least one parameter - a parameter of type defined in java-class. The call-params and call-param sub-elements define input and output parameters to pass to the stored procedure.

count element

Defines query returning number of rows in a table. The generated DML string looks like this: SELECT COUNT(*) FROM [sql-table]. A WHERE clause may be used. The [sql-table] is controlled by from attribute of sql-query element if such an attribute is defined. The generated Java method returns number of counted rows.

delete element

Defines DELETE statement. The generated DML string looks like this: DELETE FROM [sql-table]. A WHERE clause may be used. The generated Java method returns number of deleted rows.

select element

Defines SELECT statement which is used to create one or more java-class instances. The generated DML string looks like this: SELECT [fields] FROM [sql-table]. DISTINCT, WHERE, GROUP BY, HAVING and ORDER BY clauses may be used. The [sql-table] is controlled by from attribute of sql-query element if such an attribute is defined. The [fields] may be overriden by specifying columns attribute of sql-query element.

Attribute: multipleRows - defines the possibility of returning multiple values by the generated method. May be either true (default) or false.

multipleRows = false

The generated Java method returns a java-class instance or null.

multipleRows = true

The generated Java method returns a List of java-class instances.

update element

Defines UPDATE statement. The generated DML string looks like this: UPDATE [sql-table] SET [value of set attribute]. A WHERE clause may be used. The generated Java method returns number of updated rows.

sql-query element

Controls the DML statement. Not all attributes are valid in every context, i.e. in every sub-element of operations. Attribute values are used to build DML statement executed by SQL engine. Attribute values may contain parameter markers. '?' markers denote parameters filled in by PreparedStatement implementation in a common way. '??' markers denote parameters which are filled in before constructing a PreparedStatement object, no escaping nor quoting is applied. The values for question marks should be provided by the corresponding Java method.

Attributes:

  • distinct - when set to true, the query will return only distinct records. It simply adds a DISTINCT keyword to the query string. Defaults to false and valid only in select and aggregate elements.
  • from - when defined, its value will be pasted just after FROM keyword of the DML string. This attribute is optional and valid only in aggregate, count and select elements. If omitted, the query will use name of table defined in sql-table element.
  • columns - when defined, its value will be pasted between SELECT and FROM keywords of the DML string. This attribute is valid only in aggregate (required) and select (optional) elements.
  • where - when defined, its value will be pasted into WHERE part of the DML string. This attribute is optional and valid in every sub-element of operations with the exception of call.
  • group-by - when defined, its value will be pasted into GROUP BY part of DML string. This attribute is optional and valid only in select and aggregate elements.
  • having - when defined, its value will be pasted into HAVING part of DML string. This attribute is optional and valid only in select and aggregate elements.
  • order-by - when defined, its value will be pasted into ORDER BY part of DML string. This attribute is optional and valid only in select and aggregate elements.
  • other - when defined, its value will be appended at the end of the DML string. This attribute is optional and valid in every sub-element of operations with the exception of call.
  • set - when defined, its value will be pasted into SET part of the DML string (UPDATE statement). This attribute is valid (and required) only in update element.
  • body - defines the whole body of query. This attribute is valid (and required) only in call element.

java-method and java-param elements

Defines name of the method, return type and the list of method parameters. These parameters are used to substitute question marks in attribute values of sql-query element. The method will execute a PreparedStatement or CallableStatement which will be constructed using attributes of sql-query element.

Attributes of java-method:

  • name - defines part of the name of the method. The generated method will be named [operation type][java-class name][name], where [operation type] will be count, delete, etc., depending on the context. Required attribute.
  • returnType - defines return type of the method or type of List elements if the method returns a List. The allowed values for this attribute are the same as for type attribute in java-field element. This attribute is valid (and required) only in aggregate element.

java-method may contain java-param elements. Each java-param must contain name and type attributes. The allowed values for type are the same as for type attribute in java-field element.

call-params and call-param elements

call-params is used only as a sub-element of call. It has no attributes and contains zero or more call-param sub-elements. Each call-param defines a parameter to pass to or get from a stored procedure call. Each call-param must have a corresponding question mark in body attribute of sql-query element. There are three types of parameters: in, out and inout (see accessType attribute). The values for in and inout parameters may come from method parameters defined in java-method (see methodRef attribute) or from fields of object (see java-class and fields elements) which always figures in the generated method's signature (see fieldRef attribute).

Attributes of call-param:

  • accessType - may be one of in, out and inout. This defines a type of stored procedure parameter. At most one call-param can have accessType of value out or inout - that means it is impossible to get more than one output parameter from a stored procedure. If no out or inout parameter is defined, the generated Java method will be of return type void, otherwise the return type is determined by type attribute (out parameter) or fieldRef or methodRef attributes (in and inout parameters). Required attribute, defaults to in.
  • fieldRef - must contain a value of name attribute of an existing field element. It binds the call-param with a field of object. Each in and inout parameter must define either fieldRef or methodRef and no type attribute. The type of parameter is inherited from the corresponding java-field element and determines the return type of the generated Java method (if applies to an inout parameter).
  • methodRef - must contain a value of name attribute of an existing java-param sub-element of java-method. It binds the call-param with a java-param. Each in and inout parameter must define either fieldRef or methodRef and no type attribute. The type of parameter is inherited from the corresponding java-param element and determines the return type of the generated Java method (if applies to an inout parameter).
  • type - defines type of an out parameter and return type of the generated method. The allowed values for type are the same as for type attribute in java-field element. This attribute is allowed and required only for out parameters.

Exception Logging

The ApplicationDatabase class has a protected method called exceptionCaught. This method is invoked whenever an SQLException is thrown in any of the methods in ApplicationDatabase. This method has the following parameters:

  • SQLException - the SQLException thrown from any other method in ApplicationDatabase.
  • String - the name of the method where SQLException has been thrown.
  • Map - the Map containing names and values of arguments passed to the method where SQLException has been thrown.
The generated implementation of this method logs all the passed arguments on FATAL level. This behaviour may be changed by overloading this method.

PropertyChange Support in BaseX Classes

It is possible to generate BaseX classes which support adding of java.beans.PropertyChangeListener objects. To create such a class, a propertyChangeSupport attribute of java-class element must be set to true. When this is done, a set of additional methods will be generated to support adding and removal of listeners and firing PropertyChangeEvents. This behaviour is reached by internally using a java.beans.PropertyChangeSupport object, so the generated methods are tightly coupled with these from PropertyChangeSupport class. A BaseX class offers a possibility to register a listener which will be notified if a property of that BaseX class' changes its value. For more info see JavaDoc of PropertyChangeSupport and comments generated along with methods.

External Dependencies

  • Java API for XML Processing version 1.1
  • Apache Ant
  • Commons IO version 1.0

Contact

Please send any remarks to Milosz Tylenda - milosz@ais.pl.