Chapter 4. JDBC
Prev | Part V. Kodo JDO Reference Guide | Next

## Chapter 4. JDBC

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

    Database | SQL
    --- | ---
    DB2 | SELECT DISTINCT(CURRENT TIMESTAMP) FROM SYSIBM.SYSTABLES
    Empress | SELECT DISTINCT(TODAY) FROM SYS_TABLES
    Informix | SELECT DISTINCT CURRENT TIMESTAMP FROM INFORMIX.SYSTABLES
    MySQL | SELECT NOW()
    Oracle | SELECT SYSDATE FROM DUAL
    Postgres | SELECT NOW()
    SQLServer | SELECT GETDATE()
    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.PreparedStatement`s 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=""

Prev | Up | Next
3.6. Custom Log | Home | 4.2. Using a Third-Party DataSource