RimuDB Logo
Database Access Framework for Java

Configuration

There are several artifacts that must be created to use the framework. This section describes how those artifacts are created and maintained.

For each database table your application uses, you should use the editor to:

  • Create a Table Descriptor (usually by importing from the database)
  • Generate the Implementation Classes (xxxDO and xxxFinder)

Once all these artifacts have been created, create the compound-database.xml file.

1. Check the JDBC Driver is configured

To import from a database, a JDBC driver must be in the classpath and configured for use. RimuDB does not include any JDBC drivers in the build file, so these will need to be downloaded separately from the database vendor's web site. Once downloaded, they should be copied into the RimuDB install directory.

By default, the editor has JDBC configurations for all the supported databases. These are set up in the preferences dialog. If the driver was found in the classpath, a green icon is displayed next to the driver name.

Editor Preferences Drivers

If a new driver needs to be added for some reason, use the 'Add...' button to add a new entry.

The new driver class name can be added manually, or the 'Discover' button can be used to find all the JDBC Drivers in the classpath. Note that some users have reported getting deadlocks with the Discover function. If you encounter a deadlock and must add a new driver, then enter the class name manually.

Editor Add Driver

2. Create a Table Descriptor XML

The Table Descriptor contains meta data about the database table that the DO class and Finder class refer to. The first step is to create the Table Descriptor and then this can be used to generate the classes. RimuDB also uses the document at runtime to generate the SQL that will be used for basic select, insert, update and delete functions.

Although the table descriptor can be edited by hand, the RimuDB Editor can create it for you by examining the table in your database. You can also use the editor to create the table descriptor from scratch if necessary. The editor can even generate a create table statement from the data entered, although this will be a fairly generic table definition and may not be optimized for your database.



Here's the process for creating a Table Descriptor by importing from a database:

(a) Start the RimuDB Editor

When the editor first starts, it creates a classpath from all the jar files it finds in the same directory as the batch script.

Once the editor is ready, click the Import from Database button or menu item.

Editor Main

(b) Connect to the database

The Import Wizard will be displayed.

Editor Import 1


If you've used the Import Wizard before, you will have created a data source and you can select this from the Data Source drop-down. If this is the first time, you will need to create a data source for your database. Click on the ellipsis ('...') and the Maintain Data Sources dialog will open.

Maintain Data Sources


Click the 'Add...' button to add a new data source.

Maintain Data Sources


You will need to name the data source, pick the JDBC driver, enter a URL and optionally enter a user and password and click OK to save the data source.

Note that the passwords are not stored securely.


Once the data source is configured you will be able to select it in the drop-down. Enter a password if you did not store one with the data source and click next. Editor Import 1

(c) Choose Catalog, Schema and Table Type

If the connection was successful, the 'Choose Catalog, Schema and Table Type' wizard page will be displayed. Some databases use catalogs, some use schemas, and some use both. The wizard will only enable what is permissible for your database, and it will use the correct terminology for your database. For instance DB2 for iSeries uses the name of 'Library' not 'Schema'. In the example, schema is not enabled because MySQL does not have them.

Next, select the table type for your table, and click next.

Editor Import 2

(d) Choose Table

A list of table names is displayed. Select the table you want to import and click next.

Editor Import 3

(e) Property Name Generation

Finally, choose a method of assigning property names. Property names are the names of the getter and setter methods the framework will create for each column in the table. It is very time consuming to have to manually assign property names for each column. The editor has several algorithms that can be used to pick property names that will be close to, if not precisely what you want.

  • Use column names as is - This method will use the column names for property names. It is rarely ever a good choice, but is provided in case your property names must match column names precisely.
  • Derive from column names - This method is usually the best, especially if the column names for the table use meaningful names.
  • Derive from column text - This method is only available when the import database is DB2 for iSeries. Typically the column names on iSeries tables are not meaningful, but often the column text or heading is suitable to derive a property name from.
  • Derive from column heading - This method is only available when the import database is DB2 for iSeries. Typically the column names on iSeries tables are not meaningful, but often the column text or heading is suitable to derive a property name from.

Click Finish to import the column definitions. The columns will be displayed in the main editor window.

Editor Import 4

(f) The table descriptor information is imported

The columns are loaded into the editor. By default, property names are generated from the column names. Some information can be retrieved from the database: primary columns, null capable columns, and in some databases, identity columns. Other information cannot be determined from the database, and it must be entered: Sequence name, Version columns, Right Justify.

Sequence name - Some databases use sequences linked to columns to determine the next value for an identity column.

Version columns - Some databases use special columns for controlling optimistic row locking. For these databases, the version column is usually either an integer or a timestamp that is updated each time a record is updated or added. See the Row Locking section for more details

Right Justify - This is not used, but was originally intended for columns that must always have their data right justified. This is relatively common in iSeries tables.

Editor

(g) Set the package name and the data object name

The data object name is the prefix used for the classes that will be created, and the name of the table descriptor. This name should be in proper case i.e. OrderAddress.

Editor

(h) Save the table descriptor

Choose the Save menu option to save the table descriptor in your Java project directory. By default, the table descriptor is named with the data object name. In the following example the file name is OrderAddress.xml. In order for the editor to be able to generate the classes in the correct directory, save the file in the same source directory structure as the generated classes will be stored.


Directory structure:

com/mydomain/test/db/OrderAddress.xml


The table descriptor file will look something like the following:


<?xml version="1.0" encoding="UTF-8"?>
<tabledescriptor xsi:schemaLocation="http://www.rimudb.org/schemas http://www.rimudb.org/schemas/tabledescriptor-3.1.xsd"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://www.rimudb.org/schemas">
    <information package="org.rimudb.testdb" name="OrderAddress" table="OrderAddress"/>
    <column columnname="OrderNr" propertyname="orderNr" size="10" keynumber="1" type="INTEGER"/>
    <column columnname="Sequence" propertyname="sequence" size="26" keynumber="2" type="INTEGER"/>
    <column columnname="Name" propertyname="name" size="45" type="VARCHAR"/>
    <column columnname="Address1" propertyname="address1" size="45" type="VARCHAR"/>
    <column columnname="Address2" propertyname="address2" size="45" type="VARCHAR"/>
    <column columnname="QuantityCity" propertyname="city" size="45" type="VARCHAR"/>
    vcolumn columnname="State" propertyname="state" size="10" type="VARCHAR"/>
    <column columnname="Zip" propertyname="zip" size="7" type="VARCHAR"/>
</tabledescriptor>


3. Generate the Implementation Classes

Use the RimuDB Editor to generate the implementation classes from the table descriptor. In this example we chose the Java name of OrderAddress for the data object.

Because the table descriptor was saved to the directory c:\eclipse\workspace\rimudb\test\org\rimudb\testdb the editor uses the same directory as the default location for saving the implementation classes. The table descriptor and implementation classes must be saved in the same directory for the classes to work correctly.

Create Classes

Now you should have the table descriptor and three classes generated in the following directory structure:


Directory structure:

com/mydomain/test/db/OrderAddressDO.java
                    /OrderAddressFinder.java
                    /OrderAddress.xml

Two of the classes, the OrderAddressFinder and the OrderAddressDO class can be modified by developer to add functionality.

By adding data access code to the Finder and DO classes, the data access logic is separated from application code.

  • OrderAddressDO.java
    This class represents a record in the table.
  • OrderAddressFinder.java
    This class returns instances of OrderAddressDOs. It is initially created with a get() method that takes the parameters that represent the primary key and returns a single record. A variety of getAll() methods are created. These methods return more than one record, depending on the parameters passed. A delete() method that takes the primary key parameters is created, as well as a deleteAll() methods that deletes more than one record depending upon parameters.

If you ever add columns to the database table, the table descriptor and DO class have to be changed in order to access this column. Changing these manually is not particularly difficult, but it is easier to just add the new column using the editor and regenerate the DO class.

If the primary key structure of the table changes then the table descriptor and Finder will need to be changed. The find() and delete() methods all take parameters that make up the primary key.

If you do regenerate the classes, any methods you have added will be replaced.


4. Create compounddatabase.xml

Create a configuration XML file that defines the database connections and data objects used. This file name will be passed as a parameter to the CompoundDatabase class within your application.

Normally, you would create a new compounddatabase.xml for each application


<?xml version="1.0" encoding="ISO-8859-1"?>
<compounddatabase
	xmlns="http://www.rimudb.org/schemas"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 	xsi:schemaLocation="http://www.rimudb.org/schemas http://www.rimudb.org/schemas/compounddatabase-3.2.xsd">

	<database id="dbid-1" group="groupa">
		<datasource>
			<c3p0_connection_pool>
				<driver>org.h2.Driver</driver>
				<url>jdbc:h2:mem:dbtest;user=sa;password=</url>
				<acquire_retry_attempts>1</acquire_retry_attempts>
				<max_statements>100</max_statements>
			</c3p0_connection_pool>
		</datasource>
		<logging type="sql-only" threshold_ms="500" />
	</database>

	<dataobject class="org.rimudb.testdb.OrderAddressDO" group="groupa" />
	<dataobject class="org.rimudb.testdb.OrderHeaderDO" group="groupa" />
	<dataobject class="org.rimudb.testdb.OrderLineDO" group="groupa" />
	<dataobject class="org.rimudb.testdb.OrderTransactionDO" group="groupa" />

</compounddatabase>


CompoundDatabase Settings

  • database - one or more database elements can be defined in the configuration. Each database element defines a connection to a different database.
    • id - The unique identifier for the database. Your application will refer to this identifier to connect to the database.
    • group - The group for this database. The dataobject elements refer to this group. Multiple databases can be configured for the same group. For instance one database may be for a test server, another for a production server. Each database would use the same group.
  • datasource - The datasource parameters. Either the C3P0, DBCP or JNDI connection pool can be configured.
    • c3p0_connection_pool - Parameters for the c3p0 connection pool. For more details see the definition at http://www.mchange.com/projects/c3p0/index.html
      • driver - The JDBC driver class.
      • url - The database url.
      • default_transaction_isolation - (none, read-uncommitted, read-committed, repeatable-read, serializable) The default transaction isolation level.
      • default_autocommit - true(default)/false The default autocommit value.
      • user - The user ID. Some JDBC drivers permit this to be passed on the URL.
      • password - The password. Some JDBC drivers permit this to be passed on the URL.
      • use_quotes - true/false (default). Use quotes for column and table names in generated SQL statements. If the column and table names require case sensitivity, then this may be necessary.
      • acquire_increment - default = 3. Determines how many connections at a time c3p0 will try to acquire when the pool is exhausted.
      • acquire_retry_attempts - Default = 30. Defines how many times c3p0 will try to acquire a new Connection from the database before giving up. If this value is less than or equal to zero, c3p0 will keep trying to fetch a Connection indefinitely.
      • acquire_retry_delay - Default = 2000. time in milliseconds that c3p0 will wait between acquire attempts.
      • checkout_timeout - Default = 0. The number of milliseconds a client calling getConnection() will wait for a Connection to be checked-in or acquired when the pool is exhausted. Zero means wait indefinitely. Setting any positive value will cause the getConnection() call to time-out and break with an SQLException after the specified number of milliseconds.
      • initial_pool_size - Default = 3. Number of Connections a pool will try to acquire upon startup. Should be between minPoolSize and maxPoolSize.
      • login_timeout -
      • max_connection_age - Default = 0. Effectively a time to live in seconds. A Connection older than maxConnectionAge will be destroyed and purged from the pool. This differs from maxIdleTime in that it refers to absolute age. Even a Connection which has not been much idle will be purged from the pool if it exceeds maxConnectionAge. Zero means no maximum absolute age is enforced.
      • max_idle_time - Default = 0. Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire.
      • max_idle_time_excess_connections - Default = 0. Number of seconds that Connections in excess of minPoolSize should be permitted to remain idle in the pool before being culled.
      • max_pool_size - Default = 15. Maximum number of Connections a pool will maintain at any given time.
      • max_statements - Default = 0. Number of prepared statements to cache. If set to zero then no caching is performed.
      • max_statements_per_connection - Default = 0. The number of PreparedStatements c3p0 will cache for a single pooled Connection.
      • min_pool_size - Default = 3. Minimum number of Connections a pool will maintain at any given time.
      • property_cycle - Default = 0. Maximum time in seconds before user configuration constraints are enforced.
      • num_helper_threads - Default = 3. c3p0 is very asynchronous. Slow JDBC operations are generally performed by helper threads that don't hold contended locks. Spreading these operations over multiple threads can significantly improve performance by allowing multiple operations to be performed simultaneously.
    • dbcp_connection_pool - Parameters for the DBCP connection pool.
      • driver - The JDBC driver class.
      • url - The database url.
      • default_transaction_isolation - (none, read-uncommitted, read-committed, repeatable-read, serializable) The default transaction isolation level.
      • default_autocommit - true(default)/false The default autocommit value.
      • user - The user ID. Some JDBC drivers permit this to be passed on the URL.
      • password - The password. Some JDBC drivers permit this to be passed on the URL.
      • use_quotes - true/false (default). Use quotes for column and table names in generated SQL statements. If the column and table names require case sensitivity, then this may be necessary.
      • time_between_eviction_runs_secs - Default = 300. The number of seconds to sleep between runs of the idle object evictor thread. When non-positive, no idle object evictor thread will be run.
      • min_evictable_idle_time_secs - Default = 1800. The minimum amount of time an object may sit idle in the pool before it is eligible for eviction by the idle object evictor (if any).
      • max_active_connections - Default = 8. The maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit.
      • min_idle_connections - Default = 0. The maximum number of connections that can remain idle in the pool, without extra ones being released, or negative for no limit.
      • max_idle_connections - Default = 8. The minimum number of connections that can remain idle in the pool, without extra ones being created, or zero to create none.
      • max_wait_secs - Default = -1. The maximum number of seconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, or -1 to wait indefinitely.
      • statement_caching - Default = false. true/false
    • jndi_connection_pool - Parameters for the jndi connection pool.
      • initial_context - The property used for the initial context lookup. This is an optional property. If not provided, the datasource_name will be used for the lookup.
      • datasource_name - The property used to lookup the data source in the context.
      • default_transaction_isolation - (none, read-uncommitted, read-committed, repeatable-read, serializable) The default transaction isolation level.
      • default_autocommit - true(default)/false The default autocommit value.
      • use_quotes - true/false (default). Use quotes for column and table names in generated SQL statements. If the column and table names require case sensitivity, then this may be necessary.
  • logging - Optional logging parameters
    • type - sql-only/statistics. sql-only logs all SQL statements. statistics logs more details for the statement execution, and uses the threshold_ms attribute.
    • threshold_ms - The threshold to trigger statistics logging. If the execution of an SQL statement takes more than this number of milliseconds, then the statement is logged. Set to zero to log all SQL statements. This value is not used for the sql-only setting.
  • dataobject - The dataobject name and the associated group
    • class - The fully qualified name of the DO class
    • group - The database group that this data object belongs to.
    • optimistic_locking - all/version/dirty/last-commit-wins/prior-read
    • uselockinghints - true/false