ObJectRelationalBridge
BRIDGING JAVA OBJECTS AND RELATIONAL DATABASES

how to use OJB with a specific relational database

introduction

OJB has been designed to smoothly integrate with any relational database that provides JDBC support. OJB uses only JDBC 1.0 API calls to avoid problems with restrictions of several JDBC drivers.
It uses a limited SQL subset to avoid problems with restrictions of certain RDBMS. This design allows to keep the OJB code generic and free from database specifics.

This document explains basic concepts and shows how OJB can be configured to run against a specific RDBMS.

basic concepts

OJB internal tables

OJB relies on several internal tables that must be present in the target rdbms to allow a proper functioning.

If you intend to use the PersistenceBroker API only you'll need only two tables: OJB_SEQ and OJB_HL_SEQ. If you intend to use the ODMG API you'll need eight more tables. The following figure lists all tables and their specific purpose.

Tablename	Purpose
OJB_SEQ	The SequenceManagers table used for maintaining unique primary key values across extents. The SequenceManager can be asked for a unique integer value for primary key attributes of persistent classes. If the managed class is an extent (i.e. a base class with several derived classes or an interface implemented by several classes) the Manager has to maintain that the id's are unique accross all the concrete classes.
OJB_HL_SEQ	Table for a high/low implementation of the sequence manager. These two are the only tables used for the PersistenceBroker. All other tables are used to implement things required by ODMG.
OJB_LOCKENTRY	This table is used to store Object locks if the LockManager is run in distributed mode.
OJB_NRM	The "Named Roots Map". ODMG allows to bind persistent objects to an user defined name. The Named roots map is used to store these bindings. It has NAME (String of arbitrary length) as primary key and keeps the serialized OID of the persistent object in the field OID (String of arbitrary length).
OJB_DLIST	The table used for the ODMG persistent DList collections.
OJB_DLIST_ENTRIES	stores the entries of DLists (a wrapper to objects stored in the DList)
OJB_DSET	The table used to store ODMG persistent DSET collections
OJB_DSET_ENTRIES	This table stores the entries of DSets.
OJB_DMAP	The table use to store the ODMG persistent DMap tables
OJB_DMAP_ENTRIES	The table containing the DMap entries. The Keys and Values of the map can be arbitrary persistent objects.

If you intend to run the OJB JUnit regression tests (build.sh junit) you will also need to provide several more tables (filled with the proper testdata) used for the tests.

db-setup.sql

All tables mentionened above are defined in the database script file src/test/setup/db-setup.sql. This script also contains code to generate the testdata.

This script contains commands compatible to the instantDB ScriptTool command processor. OJB provides a slightly modified version of this command processor in ojb.broker.util.ScriptTool.

The db-setup.sql file contains code to

load a specific JDBC driver with the command d. The following line loads the HSQLDB driver:
d org.hsqldb.jdbcDriver;
Open a specific database with the o command.
o jdbc:hsqldb:../OJB sa;
Execute SQL DDL code to setup the database tables mentionened above with the e command as in the following "drop table" statement:
e DROP TABLE OJB_SEQ;
Execute SQL DML code to populate the database with testdata for the JUnit tests with the e command as in the following "INSERT" statement:
e INSERT INTO Artikel VALUES (1,'Chai',1,1,'10 boxes x 20 bags',18.53,39,0,10,0);

pointing to your database

The db-setup.sql file contains sample entries for some of the most popular RDBMS systems. You must edit these entries manually to reflect the specifics of your environment.
Say you want to use OJB with an MS Access database. Assume there is a ODBC DSN entry on your machine named "MY_DB" that points to your .mdb file. You then have to edit the MS Access section of the db-setup.sql file accordingly. By default this section looks as follows:

//#ifdef MS_ACCESS
/*
d sun.jdbc.odbc.JdbcOdbcDriver;
o jdbc:odbc:OJB;
*/
//#endif

You have to change the o command to open the ODBC DSN pointing to your Access database:

//#ifdef MS_ACCESS
/*
d sun.jdbc.odbc.JdbcOdbcDriver;
o jdbc:odbc:MY_DB;
*/
//#endif

preprocessing the db-setup.sql script

If you have a look at the db-setup.sql file you will have noticed that the entry for HSQLDB differs from the other entries as it does not contain /* */ comment:

//#ifdef HSQLDB
d org.hsqldb.jdbcDriver;
o jdbc:hsqldb:../OJB sa;
//#endif

The OJB Scripttool ignores lines starting with ';' or '//'. Section outcommented by '/* */' are also ignored. Thus only the HSQLDB connection is opened. The commenting / uncommenting of certain sections in the db-setup.sql is done by a preprocessor.
Preprocessor statements start with //#.

OJB provides a preprocessing mechanism that allows to use rdbms specific switches. By default the switch HSQLDB is set to true by setting the property HSQLDB to "+HSQLDB" in the ant build script build.xml. This switch is used to activate the selection of the proper d statement to select the HSQLDB JDBC driver, to select the proper o statement that opens the default HSQLDB database and to modify the DDL scripts according to the column types supported by HSQLDB.

By setting the property HSQLDB to "-HSQLDB" the HSQLDB mode can be deactivated by placing it in a '/* */' comment.

Other RDBMS may be activated by setting the respective property to true. The following lines show a setup where HSQLDB is disabled and MS Access is enabled.

    <property name="HSQLDB" value="-HSQLDB"/>
    <property name="MS_ACCESS" value="+MS_ACCESS"/>

The preprocessor is executed automatically each time the db-setup.sql script is executed. Thus there is no need to invoke it manually.

executing the build script

The db-setup.sql script can be executed by simply calling build.sh tests.

Before executing this build target you have to ensure that the specified JDBC driver is added to the classpath property in the build.xml file.

Some Jdbc drivers throw exception if "drop table" is executed for non existing tables. Thus it is recommended to execute build.sh tests twice to be sure that there are no problems with the specific RDBMS and driver setting.

repository.xml

Now that the necessary tables are added to your database we have to prepare the OJB runtime environment to run against this rdbms.

This is done in the metadata repository src/test/ojb/repository.xml.

By default it contains an active JdbcConnectionDescriptor for the internal HSQLDB database:

   <JdbcConnectionDescriptor id="default">
      <dbms.name>hsql</dbms.name>
      <driver.name>org.hsqldb.jdbcDriver</driver.name>
      <url.protocol>jdbc</url.protocol>
      <url.subprotocol>hsqldb</url.subprotocol>
      <!--url.dbalias>../OJB</url.dbalias-->
      <url.dbalias>ojb/build/test/OJB</url.dbalias>
      <user.name>sa</user.name>
      <user.passwd></user.passwd>
   </JdbcConnectionDescriptor>

The repository contains outcommented entries for a variety of other RDBMS. To activate the above mentioned MS Access database you have to:

comment out the hsqldb entry (using xml comments:  ).
uncomment the MS Access entry and edit it to point to the specific DSN:

    <JdbcConnectionDescriptor id="default">
        <dbms.name>MS ACCESS</dbms.name>
        <jdbc.level>1.0</jdbc.level>
        <driver.name>sun.jdbc.odbc.JdbcOdbcDriver</driver.name>
        <url.protocol>jdbc</url.protocol>
        <url.subprotocol>odbc</url.subprotocol>
        <url.dbalias>MY_DB</url.dbalias>
    </JdbcConnectionDescriptor>

check the installation

Now everything is setup to run the junit regression tests against your target database.

Execute build.sh junit to if everything works as expected. You should see a console output as follows:

junit:
[junit] .[BOOT] INFO: OJB.properties: file:/home/tom/ojb/build/test/ojb/OJB.properties
[junit] ........................................
[junit] .
[junit] Time: 4,29
[junit]
[junit] OK (42 tests)
[junit]
[junit] [BOOT] INFO: OJB.properties: file:/home/tom/ojb/build/test/ojb/OJB.properties
[junit] .........................................
[junit] .........................................
[junit] .......
[junit] Time: 7,159
[junit]
[junit] OK (89 tests)
[junit]

BUILD SUCCESSFUL

If the Junit tests report errors or failures something does not work properly!

In such a case please check again if you followed all the above steps. If you still have problems you might place a support request at the OJB projects page at SourceForge (http://sourceforge.net/tracker/?group_id=13647&atid=213647).

compatibility list

OJB ships with out of the box support for:

Hypersonic SQL (HSQLDB), the OJB default RDBMS (property HSQLDB)
Lutris InstantDB (property INSTANTDB)
IBM DB2/UDB (property DB2)
Oracle (property ORACLE)
MS Access (property MS_ACCESS)
MS SQL Server 2000 (property MSSQLSERVER2000)

It has been reported to run with little or no problems against:

MySQL (property MYSQL)
PostgreSQL (property POSTGRESQL)
Interbase

If you don't see a specific RDBMS on this list it does not mean that it won't work with OJB. It simply states that I have not tested it or that nobody has reported a success story.

If you would like to have out of the box for a certain RDBMS please contact me. Contributing patches for specific databases is appreciated.

If you got OJB running with a RDBMS not listed here please drop me a line to include it on this list!

$FOOTER$