Chapter 4. JDBC

4.1. Using the Kodo JDO DataSource
4.2. Using a Third-Party DataSource
4.2.1. Enlisted Data Sources
4.3. Database Support
4.3.1. MySQLDictionary parameters
4.3.2. OracleDictionary parameters
4.4. Configuing the DBDictionary
4.5. Accessing Multiple Databases
4.6. Setting the Transaction Isolation
4.7. Setting the SQL Join Syntax
4.8. Configuring the Use of JDBC Connections
4.9. Runtime Access to JDBC Connections
4.10. Large Result Sets
4.11. SQL Statement Ordering & Foreign Keys

Kodo JDO uses a relational database for object persistence. It communicates with the database using the Java DataBase Connectivity (JDBC) APIs. This chapter describes how to configure Kodo to work with the JDBC driver for your database, and how to access JDBC functionality at runtime.

4.1. Using the Kodo JDO DataSource

Kodo JDO includes its own javax.sql.DataSource implementation, complete with configurable connection pooling, SQL logging, and prepared statement caching. If you choose to use Kodo JDO's DataSource, then you must specify the properties below.

Required properties:

  • javax.jdo.option.ConnectionUserName: The JDBC user name for connecting to the database.

  • javax.jdo.option.ConnectionPassword: The JDBC password for the above user.

  • javax.jdo.option.ConnectionURL: The JDBC URL for the database.

  • javax.jdo.option.ConnectionDriverName: The JDBC driver.

To configure advanced features such as connection pooling and prepared statement caching, or to configure the underlying JDBC driver, use the following optional properties. The syntax of these property strings follows the syntax of Kodo plugin parameters described in Section 2.4, “Plugin Configuration”.

  • kodo.ConnectionProperties: If the listed driver is an instance of java.sql.Driver, this string will be parsed into a Properties instance, which will then be used to obtain database connections through the java.sql.Driver.connect(String url, Properties props) method. If, on the other hand, the driver is a javax.sql.DataSource, the string will be treated as a plugin properties string, and matched to the bean setter methods of the DataSource instance.

  • kodo.ConnectionFactoryProperties: Kodo JDO's built-in DataSource has the following configuration options you can set via this plugin string:

    • ExceptionAction: The action to take when when a connection that has thrown an exception is returned to the pool. Set to destroy to destroy the connection. Set to validate to validate the connection (subject to the TestOnReturn, TestOnBorrow, and other test settings). Set to none to ignore the fact that the connection has thrown an exception, and assume it is still usable. Defaults to destroy.

    • MaxActive: The maximum number of database connections in use at one time. A value of 0 disables connection pooling. Defaults to 8. This is the maximum number of connections that Kodo will give out to your application. If a connection is requested while MaxActive other connections are in use, Kodo will wait for MaxWait milliseconds for a connection to be returned, and will then throw an exception if no connection was made available.

    • MaxIdle: The maximum number of idle database connections to keep in the pool. Defaults to 8. If this number is less than MaxActive, then Kodo will close extra connections that are returned to the pool if there are already MaxIdle available connections in the pool. This allows for unexpected or atypical load while still maintaining a relatively small pool when there is less load on the sytsem.

    • MaxTotal: The maximum number of database connections in the pool, whether active or idle. Defaults to -1, meaning no limit (limit will be controlled by MaxActive and MaxIdle for each login criteria).

    • MaxWait: The maximum number of milliseconds to wait for a free database connection to become available before giving up. Defaults to 3000.

    • MinEvictableIdleTimeMillis: The minimum number of milliseconds that a database connection can sit idle before it becomes a candidate for eviction from the pool. Defaults to 30 minutes. Set to 0 to never evict a connection based on idle time alone.

    • RollbackOnReturn: Force all connections to be rolled back when they are retuned to the pool. If false, the datasource will only roll back connections when it detects that there has been any transactional activity on the connection.

    • TestOnBorrow: Whether to to validate database connections before obtaining them from the pool. Note that validation only consists of a call to the connection's isClosed method unless you specify a ValidationSQL string to use to send a quick query. Defaults to true.

    • TestOnReturn: Set to true to validate database connections when they are returned to the pool. Note that validation only consists of a call to the connection's isClosed method unless you specify a ValidationSQL string to use to send a quick query.

    • TestWhileIdle: Set to true to periodically validate idle database connections.

    • TimeBetweenEvictionRunsMillis: The number of milliseconds between runs of the eviction thread. Defaults to -1, meaning the eviction thread will never run.

    • ValidationSQL: A simple SQL query to issue to validate a database connection. If this property is not set, then the only validation performed is to use the Connection.isClosed method. The following table shows the default settings for different databases. If a database is not shown, this property defaults to null.

      Table 4.1. Validation SQL Defaults

      Postgres SELECT NOW()
      Sybase SELECT GETDATE()

      To disable validation SQL, set this property to an empty string, as in Example 4.1, “Properties File for the Kodo JDO DataSource”

    • ValidationTimeout: The minimum number of milliseconds that must elapse before a connection will ever be re-validated. This property is usually used with TestOnBorrow or TestOnReturn to reduce the number of validations performed, because the same connection is often borrowed and returned many times in short periods of time. Defaults to 300000 (5 minutes).

    • WhenExhaustedAction: The action to take when there are no available database connections in the pool. Set to exception to immediately throw an exception. Set to block to block until a connection is available or the maximum wait time is exceeded. Set to grow to automatically grow the pool. Defaults to block.

    Additionally, the following properties are available whether you use Kodo JDO's built-in DataSource or a third-party's:

    • MaxCachedStatements: The maximum number of java.sql.PreparedStatements to cache. Statement caching can dramatically speed up some databases. Defaults to 50 for Kodo's data source, and 0 for third-party data sources. Most third-party data sources do not benefit from Kodo's prepared statement cache, because each returned connection has a unique hash code, making it impossible for Kodo to match connections to their cached statements.

    • QueryTimeout: The maximum number of seconds the JDBC driver will wait for a statement to execute.

Example 4.1. Properties File for the Kodo JDO DataSource

javax.jdo.option.ConnectionUserName: user
javax.jdo.option.ConnectionPassword: pass
javax.jdo.option.ConnectionURL: jdbc:hsqldb:db-hypersonic
javax.jdo.option.ConnectionDriverName: org.hsqldb.jdbcDriver
kodo.ConnectionFactoryProperties: MaxActive=50, MaxIdle=10, \
    ValidationTimeout=50000, MaxCachedStatements=100, ValidationSQL=""