Chapter 4. JDBC
Prev | Part 6. Kodo JPA/JDO Reference Guide | Next

## Chapter 4. JDBC

Kodo JPA/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 DataSource

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

| Note
JDO users can specify the equivalent JDO properties rather than the Kodo properties below.

- `kodo.ConnectionUserName`: The JDBC user name for connecting to the database.

- `kodo.ConnectionPassword`: The JDBC password for the above user.

- `kodo.ConnectionURL`: The JDBC URL for the database.

- `kodo.ConnectionDriverName`: The JDBC driver class.

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.5, “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 `Driver.connect(String url, Properties props)` method. If, on the other hand, the listed 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's built-in `DataSource` allows you to set the following options 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. This allows for unexpected or atypical load while still maintaining a relatively small pool when there is less load on the system.

  - `MaxTotal`: The maximum number of database connections in the pool, whether active or idle. Defaults to -1, meaning no limit (the limit will be dictated by `MaxActive` and `MaxIdle` for each unique user name).

  - `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 have been any transactional updates 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. 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.

  - `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.

  - `TrackParameters`: When `true`, Kodo will track the parameters that were set for all `PreparedStatement`s that are executed or batched so that they can be included in error messages. Defaults to `true`.

  - `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 for the Kodo DataSource”

  - `ClosePoolSQL`: A simple SQL statement to execute when the connection pool is completely closed. This can be used, for example, to cleanly issue a shutdown statement to a file-based database.

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

  - `WarningAction`: The action to take when a `SQLWarning` is detected on a connection. Possible values are:

    - `ignore`: Warnings will not be checked for, and will be ignored. This is the default.

    - `trace`: The warning will be logged on the `TRACE` channel of the JDBC log.

    - `info`: The warning will be logged on the `INFO` channel of the JDBC log.

    - `warn`: The warning will be logged on the `WARN` channel of the JDBC log.

    - `error`: The warning will be logged on the `ERROR` channel of the JDBC log.

    - `throw`: All `SQLWarning` instances will be thrown as if they were errors.

    - `handle`: The `SQLWarning` instance will be passed through the `handleWarning` method of `kodo.jdbc.sql.DBDictionary`, which allows a custom extension of the dictionary to use heuristic-based warning handling.

  - `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'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 `DataSource`, and 0 for third-party `DataSource`s. Most third-party `DataSource`s 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 for the Kodo DataSource

JPA XML format:

    <property name="kodo.ConnectionUserName" value="user"/>
    <property name="kodo.ConnectionPassword" value="pass"/>
    <property name="kodo.ConnectionURL" value="jdbc:hsqldb:db-hypersonic"/>
    <property name="kodo.ConnectionDriverName" value="org.hsqldb.jdbcDriver"/>
    <property name="kodo.ConnectionFactoryProperties"
        value="MaxActive=50, MaxIdle=10, ValidationTimeout=50000, MaxCachedStatements=100, ValidationSQL=''"/>

JDO properties format:

    kodo.ConnectionUserName: user
    kodo.ConnectionPassword: pass
    kodo.ConnectionURL: jdbc:hsqldb:db-hypersonic
    kodo.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