Chapter 4. JDBC

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

4.1. Using the Kodo DataSource
4.2. Using a Third-Party DataSource
4.3. Runtime Access to DataSource
4.4. Database Support
4.5. Setting the Transaction Isolation
4.6. Setting the SQL Join Syntax
4.7. Accessing Multiple Databases
4.8. Configuring the Use of JDBC Connections
4.9. Statement Batching
4.10. Large Result Sets
4.11. Default Schema
4.12. Schema Reflection
4.13. Schema Tool
4.14. XML Schema Format
4.15. The SQLLine Utility

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 PreparedStatements 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.PreparedStatements to cache. Statement caching can dramatically speed up some databases. Defaults to 50 for Kodo's DataSource, and 0 for third-party DataSources. Most third-party DataSources 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