6. Database access

6.1. ConnectionProvider

As an ORM tool, probably the single most important thing you need to tell Hibernate is how to connect to your database so that it may connect on behalf of your application. This is ultimately the function of the org.hibernate.engine.jdbc.connections.spi.ConnectionProvider interface. Hibernate provides some out of the box implementations of this interface. ConnectionProvider is also an extension point, so you can also use custom implementations from third parties or written yourself. The ConnectionProvider to use is defined by the hibernate.connection.provider_class setting. See the org.hibernate.cfg.AvailableSettings#CONNECTION_PROVIDER`

Generally speaking applications should not have to configure a ConnectionProvider explicitly if using one of the Hibernate-provided implementations. Hibernate will internally determine which ConnectionProvider to use based on the following algorithm:

1. If hibernate.connection.provider_class is set, it takes precedence

2. else if hibernate.connection.datasource is set → Using DataSources

3. else if any setting prefixed by hibernate.c3p0. is set → Using c3p0

4. else if any setting prefixed by hibernate.proxool. is set → Using Proxool

5. else if any setting prefixed by hibernate.hikari. is set → Using Hikari

6. else if hibernate.connection.url is set → Using Hibernate’s built-in (and unsupported) pooling

7. else → User-provided Connections

6.2. Using DataSources

Hibernate can integrate with a javax.sql.DataSource for obtaining JDBC Connections. Applications would tell Hibernate about the DataSource via the (required) hibernate.connection.datasource setting which can either specify a JNDI name or would reference the actual DataSource instance. For cases where a JNDI name is given, be sure to read JNDI

The DataSource ConnectionProvider also (optionally) accepts the hibernate.connection.username and hibernate.connection.password. If specified, the DataSource#getConnection(String username, String password) will be used. Otherwise the no-arg form is used.

6.3. Using c3p0

Hibernate also provides support for applications to use c3p0 connection pooling. When using this c3p0 support, a number of additional configuration settings are recognized.

Transaction isolation of the Connections is managed by the ConnectionProvider itself. See ConnectionProvider support for transaction isolation setting.

• hibernate.connection.driver_class

• The name of the JDBC Driver class to use

• hibernate.connection.url

• The JDBC connection url.

• Any settings prefixed with hibernate.connection. (other than the "special ones")

• These all have the hibernate.connection. prefix stripped and the rest will be passed as JDBC connection properties

• hibernate.c3p0.min_size or c3p0.minPoolSize

• The minimum size of the c3p0 pool. See c3p0 minPoolSize

• hibernate.c3p0.max_size or c3p0.maxPoolSize

• The maximum size of the c3p0 pool. See c3p0 maxPoolSize

• hibernate.c3p0.timeout or c3p0.maxIdleTime

• The Connection idle time. See c3p0 maxIdleTime

• hibernate.c3p0.max_statements or c3p0.maxStatements

• Controls the c3p0 PreparedStatement cache size (if using). See c3p0 maxStatements

• hibernate.c3p0.acquire_increment or c3p0.acquireIncrement

• Number of connections c3p0 should acquire at a time when pool is exhausted. See c3p0 acquireIncrement

• hibernate.c3p0.idle_test_period or c3p0.idleConnectionTestPeriod

• Idle time before a c3p0 pooled connection is validated. See c3p0 idleConnectionTestPeriod

• hibernate.c3p0.initialPoolSize

• The initial c3p0 pool size. If not specified, default is to use the min pool size. See c3p0 initialPoolSize

• Any other settings prefixed with hibernate.c3p0.

• Will have the hibernate. portion stripped and be passed to c3p0.

• Any other settings prefixed with c3p0.

• Get passed to c3p0 as is. See c3p0 configuration

6.4. Using Proxool

Hibernate also provides support for applications to use Proxool connection pooling.

Transaction isolation of the Connections is managed by the ConnectionProvider itself. See ConnectionProvider support for transaction isolation setting.

6.5. Using existing Proxool pools

Controlled by the hibernate.proxool.existing_pool setting. If set to true, this ConnectionProvider will use an already existing Proxool pool by alias as indicated by the hibernate.proxool.pool_alias setting.

6.6. Configuring Proxool via XML

The hibernate.proxool.xml setting names a Proxool configuration XML file to be loaded as a classpath resource and loaded by Proxool’s JAXPConfigurator. See proxool configuration. hibernate.proxool.pool_alias must be set to indicate which pool to use.

6.7. Configuring Proxool via Properties

The hibernate.proxool.properties setting names a Proxool configuration properties file to be loaded as a classpath resource and loaded by Proxool’s PropertyConfigurator. See proxool configuration. hibernate.proxool.pool_alias must be set to indicate which pool to use.

6.8. Using Hikari

Hibernate also provides support for applications to use Hikari connection pool.

Set all of your Hikari settings in Hibernate prefixed by hibernate.hikari. and this ConnectionProvider will pick them up and pass them along to Hikari. Additionally, this ConnectionProvider will pick up the following Hibernate-specific properties and map them to the corresponding Hikari ones (any hibernate.hikari. prefixed ones have precedence):

• hibernate.connection.driver_class

• Mapped to Hikari’s driverClassName setting

• hibernate.connection.url

• Mapped to Hikari’s jdbcUrl setting

• hibernate.connection.username

• Mapped to Hikari’s username setting

• hibernate.connection.password

• Mapped to Hikari’s password setting

• hibernate.connection.isolation

• Mapped to Hikari’s transactionIsolation setting. See ConnectionProvider support for transaction isolation setting. Note that Hikari only supports JDBC standard isolation levels (apparently).

• hibernate.connection.autocommit

• Mapped to Hikari’s autoCommit setting

6.9. Using Hibernate’s built-in (and unsupported) pooling

This section is here just for completeness.

6.10. User-provided Connections

It is possible to use Hibernate by simply passing a Connection to use to the Session when the Session is opened. This usage is discouraged and not discussed here.

6.11. ConnectionProvider support for transaction isolation setting

All of the provided ConnectionProvider implementations, other than DataSourceConnectionProvider, support consistent setting of transaction isolation for all Connections obtained from the underlying pool. The value for hibernate.connection.isolation can be specified in one of 3 formats:

• the integer value accepted at the JDBC level

• the name of the java.sql.Connection constant field representing the isolation you would like to use. For example, TRANSACTION_REPEATABLE_READ for java.sql.Connection#TRANSACTION_REPEATABLE_READ. Not that this is only supported for JDBC standard isolation levels, not for isolation levels specific to a particular JDBC driver.

• a short-name version of the java.sql.Connection constant field without the TRANSACTION_ prefix. For example, REPEATABLE_READ for java.sql.Connection#TRANSACTION_REPEATABLE_READ. Again, this is only supported for JDBC standard isolation levels, not for isolation levels specific to a particular JDBC driver.

6.12. Database Dialect

Although SQL is relatively standardized, each database vendor uses a subset and superset of ANSI SQL defined syntax. This is referred to as the database’s dialect. Hibernate handles variations across these dialects through its org.hibernate.dialect.Dialect class and the various subclasses for each database vendor.

In most cases Hibernate will be able to determine the proper Dialect to use by asking some questions of the JDBC Connection during bootstrap. For information on Hibernate’s ability to determine the proper Dialect to use (and your ability to influence that resolution), see Dialect resolution.

If for some reason it is not able to determine the proper one or you want to use a custom Dialect, you will need to set the hibernate.dialect setting.

Table 3. Provided Dialects