Database package

Introduction

This package implements a simple framework to work with a database over JDBC. The idea of this simple framework is to write fast robust code.

Design

The `DatabaseManager`

This class supports an additional abstraction. The DatabaseManager holds an instance of the DataSource. The idea of the the DatabaseManager is to work with SQLProcessor's.

A class which implements the SQLProcessor interface defines a specific SQL statement which can be get with the method getStatement. Each SQLProcess also implements the processing of the JDBC ResultSet. After the execution of a SQLProcessor an Object array is returned. The SQL parameters can be set outside of the SQLProcessor class with the method setStatmentValues. The sort order of the Object array correspond to the SQL parameter order.

The control over a transaction with a SQLProcessor is part of the DatabaseManager which works together with a TransactionMonitor (more details see in the transaction chapter).

The class AbstractSQLProcessor implements the main method of the SQLProcessor interface. Each subclass of the class AbstractSelectSQLProcessor has also to define the SQL statment (by calling the method setSQLStatement) and has to implement the method process which implements the processing of the data. The ResultSet will closed after process method!

The sequence diagram below shows the connection between the database classes:

There is a simple a select example:
(IMPORTANT: SQLProcessor can be generated by the ProcessorGenerator class from a given SQL code)

public class TestSelectProcessor extends AbstractSelectSQLProcessor
{
    /**
     * Constructor
     */
    public TestSelectProcessor()
    {
    	setSQLStatement( "select address from usertable where username like '?'" );
    }


    /**
     * Returns the data
     * @return the read data
     */
    protected Object[] process( ResultSet rs )
        throws SQLException
    {
        List data = new ArrayList();
        String message;

        while( rs.next() )
        {
            message = rs.getString( 1 );
            data.add( message );
        }
            
        return data.toArray( new String[ data.size() ] );
    }
}

There is a simple insert example:

public class TestInsertProcessor extends AbstractUpdateSQLProcessor
{
    /**
     * Constructor
     */
    public TestInsertProcessor()
    {
    	setSQLStatement( "insert into address (username) values (?)" );
    }
}

There next examples show the initialisation of the DatabaseManager and the the use of it:

Initialisation of the DatabaseManager with the DatabasePoolManager:

    ...
    FileManager fileManager = new BootManager( new PropertiesManager() );
    config = new DatabaseConfig( (Properties)fileManager.getFile("databasetest.properties") );
    DatabaseManager dbManager = new DatabaseManager( new DatabasePoolManager( config ) );
    ...
    Object[] param = new Object[1];
    param[0] = new String( "myName" );
    dbManager.executeQuery( new TestInsertProcessor(), null );
    ...

Initialisation of the DatabaseManager with a DataSource:

    ...
    String dataSourceName = "...";
    Context ctx = new InitialContext();
    DataSource dataSource = (DataSource)ctx.lookup( dataSourceName );
    DatabaseManager dbManager = new DatabaseManager( dataSource );
    ...
    Object[] param = new Object[1];
    param[0] = new String( "myName" );
    dbManager.executeQuery( new TestInsertProcessor(), null );
    ...

Initialisation of the DatabaseManager with a DataSource. The lookup works with the ServiceLocator:

    ...
    String dataSourceName = "...";
    ServiceLocator localtor = new ServiceLocator();
    DataSource dataSource = localtor.getDataSource( dataSourceName );
    DatabaseManager dbManager = new DatabaseManager( dataSource );
    ...
    Object[] param = new Object[1];
    param[0] = new String( "myName" );
    dbManager.executeQuery( new TestInsertProcessor(), null );
    ...

The `BatchProcessor`

The class BatchProcessor implements a processor which contains multiple processors which can executed in one transaction context (more details see in the transaction chapter). The return value contains a list with the result of each processor (a list with Object arrays!). If one processor fails then the connection will rollback. The method addSQLProcessor adds a new SQLProcessor with the execution values (null if the statement has no values). The execution order of the processors is the same they are added with the method addSQLProcessor.

There is an example:

...
    String createTestTable = "create table jptoolsTest( name varchar(10), theDate date )";
    String insertNameToTestTable = "insert into jptoolsTest( NAME ) values( ? )";

    BatchSQLProcessor batch = new BatchSQLProcessor( "Batch Execution", null );
    batch.addSQLProcessor( new TestSQLProcessor( createTestTable ), null );
    batch.addSQLProcessor( new TestInsertSQLProcessor( insertNameToTestTable ), new Object[] { "jptools1" } );
    batch.addSQLProcessor( new TestInsertSQLProcessor( insertNameToTestTable ), new Object[] { "jptools2" } );
    batch.addSQLProcessor( new TestInsertSQLProcessor( insertNameToTestTable ), new Object[] { "jptools3" } );
    
    List result = manager.executeBatch( batch );
...

Transactions

The DatabaseManager use a TransactionMonitor to control a transaction which starts and close a transaction. Each processor defines the result values of the execution. If the defined values do not match with the result of the execution of the processor a defined error message will logged and a rollback will done. Only if the expected result values match the data processing will commited.

The method setExpectedResult of a processor defines the expected result values and the error message if the values do not match. The default value is set to null which informs the DatabaseManager to not control the result values of the processor.

The framework currently implements the following transaction monitors:

jptools.database.SimpleTransactionMonitor
jptools.j2ee.database.BMTransactionMonitor
jptools.j2ee.database.CMTransactionMonitor

The DatabaseManager class use as default the SimpleTransactionMonitor except an other the transaction monitor is set in the constructor. An other possibility to use a different transaction monitor is to hand over a transaction monitor by each executeQuery or executeBatch method call.

There is an example:

...
    String createTestTable = "create table jptoolsTest( name varchar(10), theDate date )";
    String insertNameToTestTable = "insert into jptoolsTest( NAME ) values( ? )";

    BatchSQLProcessor batch = new BatchSQLProcessor( "Batch Execution", null );
    batch.addSQLProcessor( new TestSQLProcessor( createTestTable ), null );
    batch.addSQLProcessor( new TestInsertSQLProcessor( insertNameToTestTable ), new Object[] { "jptools1" } );
    batch.addSQLProcessor( new TestInsertSQLProcessor( insertNameToTestTable ), new Object[] { "jptools2" } );
    batch.addSQLProcessor( new TestInsertSQLProcessor( insertNameToTestTable ), new Object[] { "jptools3" } );
    
    UserTransaction ut = context.getUserTransaction(); // context = SessionContext
    List result = manager.executeBatch( new BMTransactionMonitor( ut ), 
                                        batch );
...

The `DataSource` `DatabasePoolManager`

This class implements a simple JDBC connection pooling. It implements the DataSource interface. Each call of the method getConnection returns a valid JDBC Connection. The class Connection extends the SQL Connection. Itself holds a real Connection to the database. After calling close on the connection the connection will not real closed. The Connection will pushed back to the ConnectionStack which is under control of the DatabasePoolManager.

Configuration

The class DatabaseConfig defines the database configuration like the database url to connect, the user, the password. In the table below the key and default values are described.

There are some example configuration:

Oracle (over SQL*Net) (classes12.zip or 9.2 and greater ojdbc14.jar)

database.driver   = oracle.jdbc.driver.OracleDriver
database.url      = jdbc:oracle:oci8:/@SID

Oracle (Type 4 driver) (classes12.zip or 9.2 and greater ojdbc14.jar)

database.driver   = oracle.jdbc.driver.OracleDriver
database.url      = jdbc:oracle:thin:@server:1521:SID
database.username = myUser
database.password = myPassword

MySql

database.driver   = com.mysql.jdbc.Driver
database.url      = jdbc:mysql://server/databaseName
database.username = myUser
database.password = myPassword

DB2 (db2jcc.jar and db2jcc_license_cu.jar)

database.driver   = com.ibm.db2.jcc.DB2Driver
database.url      = jdbc:db2://server:50000/databaseName
database.username = myUser
database.password = myPassword

Microsoft SQL Server (msbase.jar, mssqlserver.jar and msutil.jar)

database.driver   = com.microsoft.jdbc.sqlserver.SQLServerDriver
database.url      = jdbc:microsoft:sqlserver://server:1433;DatabaseName=databaseName;SelectMethod=Cursor
database.username = myUser
database.password = myPassword

Microsoft Access

database.driver   = sun.jdbc.odbc.JdbcOdbcDriver
database.url      = jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=directory\\filename.mdb;}

Key

Default value

Description

database.driver

The jdbc driver of the database.

database.url

The jdbc url.

database.username

The username of the database to connect to.

database.password

The password of the database user.

database.errorTimeout

1000

This timeout defines the interval how long the pool should wait of a retry if the creation of a connection was failed.

database.minConnections

1

The min. connections to the database. After initializing the DatabasePoolManager it initialize min numbers connections to the database and hold this connections in the pool.

database.maxConnections

10

The max. connections to the database. If this number of connection is reached and a Connection is need it waits until connection will pushed back to the pool.

database.connectionTimeout

300000

This defines the timeout of a free Connection in the pool. The value defines the number of miliseconds.

database.shrinkTimeout

60000

Defines the interval to cleanup the connections in the pool.

database.numberOfConnectionsToShrink

half

Defines the behavior to shrink the connections in the pool. The following keys are valid:

*Key*	*Description*
`max`	Shrink pool to min. connections and remove all remaining connections from the pool.
`half`	Shrink the pool to the half size of free connections. This means: `(conn. in pool)-(min. conn.)/2.`
`third`	Shrink the pool to a third of the size. This means: `(conn. in pool)-(min. conn.)/3.`
`quarter`	Shrink the pool to a quarter of the size. This means: `(conn. in pool)-(min. conn.)/4.`
`1...`	Defines the number of connections to shrink if there are more than min. connections in the pool.
`0, no`	Disable automatic shrinking of the pool.

database.logStatistic

true

Switch to log database statistics when something in the connection pool changes.

database.logConfig

true

Switch to log the database configuration by initializing the DatabasePoolManager.

database.verbose

false

Sets the verbose mode.

jpTools

Your swissknife java library.

Database package

Introduction

Design

The `DatabaseManager`

The `BatchProcessor`

Transactions

The `DataSource` `DatabasePoolManager`

Configuration

jpTools

Your swissknife java library.

Database package

Introduction

Design

The DatabaseManager

The BatchProcessor

Transactions

The DataSource DatabasePoolManager

Configuration

The `DatabaseManager`

The `BatchProcessor`

The `DataSource` `DatabasePoolManager`