RimuDB Logo
Database Access Framework for Java

Compound Database XML Document

This configuration XML file 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

The filename specified on the constructor for CompoundDatabase can exist in the local file system, an HTTP server or the classpath.

If the file is on an HTTP server, then a URL can be used i.e. http://docserver.rimudb.org/docs/some_app_cdb.xml.

If the file is in the local file system, a file URL can be used i.e. file:///usr/local/docs/some_app_cdb.xml.

If the file is in the classpath, the RimuDB-specific protocol 'classpath:' can be used. i.e. classpath:/some_app_cdb.xml. This forces RimuDB to attempt to find the file using getClass().getResourceAsStream(filename)

If no protocol is specified, RimuDB will first search the local file system. If it is not found there, it will search the classpath using getClass().getResourceAsStream(filename).

Note that the getResourceAsStream() method behaves in a somewhat unexpected way if the resource name does not start with a "/". If the resource name starts with "/", the directory structure from the root is followed, but if it does not, the following process is used to find the file: Java alters the name before searching the classpath. The package name of the class issuing the method call is prepended to the resource name after converting "." to "/". Since the getResourceAsStream() call is made from the class org.rimudb.configuration.CompoundDatabaseLoader, if you omit the leading "/", it will only find the file if it is located in the classpath under "/org/rimudb/configuration/"

A Simple Example

The following example demonstrates how an application would be configured to connect to a mySQL database. In this case the application would connect to the database id named 'mars'.


CompoundDatabase cdb = new CompoundDatabase("/testconfig/mysql-cdb.xml", true);
cdb.connect('mars');

This minimal example will work for most small applications. When scaling up applications it may be necessary to adjust pool connection settings.


<?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="mars" group="groupa">
		<datasource>
			<c3p0_connection_pool>
				<driver>com.mysql.jdbc.Driver</driver>
				<url>jdbc:mysql://mars.mydomain.com:3306/dbtest?user=dbtest&password=dbtest</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>


Multiple Homogeneous Database Servers

In the previous example, four DataObject classes are mapped to the group named 'groupa'. The database connection 'mars' is assigned to 'groupa'. The group settings allow multiple database servers to be associated with a set of Data Objects.

The next example shows a configuration with multiple database servers that each have the same set of tables. Note that in this example the database servers are both MySQL, but as long as the table structure is identical, they could be different different types of databases.


<?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="jupiter" group="groupa">
		<datasource>
			<c3p0_connection_pool>
				<driver>com.mysql.jdbc.Driver</driver>
				<url>jdbc:mysql://jupiter.mydomain.com:3306/dbtest?user=dbtest&password=dbtest</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>

	<database id="mars" group="groupa">
		<group>groupa</group>
		<datasource>
			<c3p0_connection_pool>
				<driver>com.mysql.jdbc.Driver</driver>
				<url>jdbc:mysql://mars.mydomain.com:3306/dbtest?user=dbtest&password=dbtest</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>

In this case the application could connect to either the database id named 'mars' or 'jupiter'.


CompoundDatabase cdb = new CompoundDatabase("/testconfig/mysql-cdb.xml", true);
if (in_new_york)
  cdb.connect('mars');
else
  cdb.connect('jupiter');
  

Multiple Hetrogeneous Database Servers

The next example shows a configuration with multiple database servers. It is assumed the mars and jupiter servers each have the same set of tables, but the venus server has a different set.


<?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="jupiter" group="groupa">
		<datasource>
			<c3p0_connection_pool>
				<driver>com.mysql.jdbc.Driver</driver>
				<url>jdbc:mysql://jupiter.mydomain.com:3306/dbtest?user=dbtest&password=dbtest</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>

	<database id="mars" group="groupa">
		<datasource>
			<c3p0_connection_pool>
				<driver>com.mysql.jdbc.Driver</driver>
				<url>jdbc:mysql://mars.mydomain.com:3306/dbtest?user=dbtest&password=dbtest</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>

	<database id="venus" group="groupb">
		<datasource>
			<c3p0_connection_pool>
				<driver>com.mysql.jdbc.Driver</driver>
				<url>jdbc:mysql://venus.mydomain.com:3306/dbtest?user=dbtest&password=dbtest</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" />

	<dataobject class="org.rimudb.testdb.ItemDO" group="groupb" />
	<dataobject class="org.rimudb.testdb.SkuDO" group="groupb" />

</compounddatabase>


In this example the Data Objects ItemDO and SkuDO are assigned to groupb, indicating those tables can only be accessed on the 'venus' server.

The application has to perform two connection calls:


CompoundDatabase cdb = new CompoundDatabase("/testconfig/mysql-cdb.xml", true);
cdb.connect('venus');
if (in_new_york)
  cdb.connect('mars');
else
  cdb.connect('jupiter');
  

Additional JDBC Properties

Most JDBC drivers allow additional properties to be specified to control the way in which database connections are made. Almost all of the drivers allow these properties to be passed on the connection URL. For instance the MySQL syntax is:


jdbc:mysql://[host][,failoverhost...][:port]/[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]... 

However some drivers (like Oracle's) do not permit properties to be passed this way. Instead they must be stored in a Properties object and passed on the JDBC connect() method. From version 1.3.6 onwards, RimuDB supports properties passed in this way. The c3p0_connection_pool and dbcp_connection_pool elements support a properties element that can contain multiple property elements:


   <database group="groupa" id="dbid-1">
      <datasource>
         <c3p0_connection_pool>
            <acquire_retry_attempts>3</acquire_retry_attempts>
            <max_statements>100</max_statements>
            <driver>oracle.jdbc.driver.OracleDriver</driver>
            <url>jdbc:oracle:thin:@//localhost:1521/XE</url>
            <password>dbtest</password>
            <user>dbtest</user>
            <use_quotes>true</use_quotes>
            <properties>
            	<property name="fixedString" value="true"/>
            </properties>
         </c3p0_connection_pool>
      </datasource>
      <logging threshold_ms="0" type="sql-only"/>
   </database>

Connection Pool Configuration

Three different connection pools can be used in RimuDB.

C3P0

This connection pool is very reliable and is the recommended connection pool for RimuDB. The jar file for c3p0 is provided in the downloaded zip file. The connection pool details can be found here

An example of configuring C3P0 in the CompoundDatabase.xml document:


<database id="jupiter" group="groupa">
	<datasource>
		<c3p0_connection_pool>
			<driver>com.mysql.jdbc.Driver</driver>
			<url>jdbc:mysql://jupiter.mydomain.com:3306/dbtest?user=dbtest&password=dbtest</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>

JNDI

This pool configuration allows an application running in a container (i.e. a Servlet or Spring application) to use the container's connection pool.

The values for initial_context and datasource_name depend upon the container and how your application is deployed within the container

The following example is used for a servlet running in a Tomcat servlet engine.


<database id="jupiter" group="groupa">
	<datasource>
		<jndi_connection_pool>
			<initial_context>java:/comp/env/</initial_context>
			<datasource_name>jdbc/TestRimuDB</datasource_name>
		</jndi_connection_pool>
	</datasource>
	<logging type="sql-only" threshold_ms="500" />
</database>

Apache DBCP

This connection pool does not seem to be very reliable and in testing did not appear to actually cache prepared statements, despite claiming that it did. More information on the pool can be found here. Note that it relies upon the commons pooling which can be found here


<database id="jupiter" group="groupa">
	<datasource>
		<dbcp_connection_pool>
			<driver>com.mysql.jdbc.Driver</driver>
			<url>jdbc:mysql://jupiter.mydomain.com:3306/dbtest?user=dbtest&password=dbtest</url>
			<time_between_eviction_runs_secs>600</time_between_eviction_runs_secs>
			<min_evictable_idle_time_secs>75</min_evictable_idle_time_secs>
			<min_idle_connections>1</min_idle_connections>
			<max_idle_connections>10</max_idle_connections>
			<max_active_connections>50</max_active_connections>
			<statement_caching>true</statement_caching>
		</dbcp_connection_pool>
	</datasource>
	<logging type="sql-only" threshold_ms="500" />
</database>

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