3 JDBC Example

JOTM can be used with any database (with a JDBC driver) to provide distributed transactional access to databases.
The JDBC example is a very simple example showing how to use JTA transactions with XAPool to provide transactional access to a database (configuration files for PostgreSQL and MySQL are included).

All Ant commands are to be executed from the examples/jdbc/ directory of a JOTM distribution (examples won't work from JOTM source directory).

3.1 Scenario

• a database is started and configured
• a RMI registry is started
• the JdbcExample object is started
• it starts a DatabaseHelper object with an embedded JOTM. It setups the JDBC objects (i.e. java.sql.Connection with XAPool thanks to a configuration file and sets JOTM as their transaction manager. It also binds UserTransaction in JNDI
• JdbcExample prints a table from the database (without transaction)
• a transaction is started thanks to UserTransaction
• an update statement is sent to the database
• the transaction is completed (either commited or rolled back)
• JdbcExample prints once again the table from the database (without transaction)

3.2 Setup and compilation

Before starting the example, the database needs to be properly configured. The JDBC example can work with any database providing a JDBC driver. It uses XAPool to take care of the transactional behaviors of JDBC objects. The setup is explained for MySQL. For other databases, it should be straightforward to configure them properly.

3.2.1 Database setup

The example expects:

• a database named javatest
• a user of login mojo and password jojo
• a transactional table named testdata which looks like

  ID	FOO
  1	1

  with
  • id being an int (prtimary key)
  • foo also being an int

For example on MySQL:

mysql> GRANT ALL PRIVILEGES ON *.* TO mojo
    ->   IDENTIFIED BY 'jojo' WITH GRANT OPTION;
mysql> create database javatest;
mysql> use javatest;
mysql> create table testdata (
    ->   id int not null auto_increment primary key,
    ->   foo int)type=InnoDB;
mysql> insert into testdata values(null, 1);

Do not forget to set testdata type to InnoDB to enable transaction support.
Database configuration are stored in properties file (e.g. mysql.properties and postgresql.properties) which contains the following properties:

• driver - Name of the JDBC driver
• url - URL to connect to the data base
• login - user login
• password - user password

3.2.2 Compilation

In examples/jdbc/ directory, type

   $ ant compile

to compile the example

3.3 Run the example

Set JOTM_HOME to the directory of your JOTM distribution (e.g., .../jotm/output/dist from CVS).

To run the example, first check that only RMI protocol will be activated (in the ../../config/carol.properties, carol.protocols should be set to jrmp); then type in $JOTM_HOME/lib/ directory

   $ rmiregistry -J-classpath -Jjotm.jar:jotm_jrmp_stubs.jar \
     -J-Djava.security.policy=../config/java.policy &

to start a RMI registry on default port (i.e. 1099).

Set the classpath

   $ export CLASSPATH=../../lib/jotm.jar:../../lib/jotm_jrmp_stubs.jar\
     :../../lib/xapool.jar:../../config:.:$JDBC_JARS

where JDBC_JARS is the location of the JDBC driver jar file(s) you want to use

• pg73jdbc2.jar for PostgreSQL 7.3
• mysql-connector-java-2.0.14-bin.jar for MySQL 3.23

They are respectively downloadable from

• http://www.mysql.com/downloads/api-jdbc-stable.html/
• http://jdbc.postgresql.org/download/

Start the example

   $ java JdbcExample postgresql commit 2

to set foo value to 2 and commit the transaction on PostgreSQL

   $ java JdbcExample mysql rollback 0

to set foo value to 0 but rollback the transaction on MySQL

3.3.1 Usage

   $ java JdbcExample [database] [completion] [number]

where

• database can be
  • postgresql
  • mysql (example will look for a configuration file name [database].properties)
• completion can be
  • commit to commit the transaction
  • rollback to rollback the transaction
• number has to be a integer

3.4 Output

For example, command line

   $ java JdbcExample postgresql commit 2

will ouptut something like

start server

postgresql configuration:
-- listing properties --
login=mojo
url=jdbc:postgresql://localhost/javatest
password=jojo
driver=org.postgresql.Driver
------------------------

create initial context
lookup UserTransaction at : UserTransaction
get a connection
before transaction, table is:
        id      foo
        1       0
begin a transaction
update the table
*commit* the transaction
after transaction, table is:
        id      foo
        1       2
close connection
stop server
JDBC example is ok.

As stated, the transaction has been committed and foo value has been set to 2 in the database.

Another command line like

   $ java JdbcExample mysql rollback 3

will output something like

start server

mysql configuration:
-- listing properties --
login=mojo
url=jdbc:mysql://localhost/javatest
password=jojo
driver=org.gjt.mm.mysql.Driver
------------------------

create initial context
lookup UserTransaction at : UserTransaction
get a connection
before transaction, table is:
        id      foo
        1       1
begin a transaction
update the table
*rollback* the transaction
after transaction, table is:
        id      foo
        1       0
close connection
stop server
JDBC example is ok.

Here, the value of foo has not been changed in the database because the transaction has been rolled back.