7. Database access

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

6. else if any setting prefixed by hibernate.vibur. is set → Using Vibur DBCP

7. else if any setting prefixed by hibernate.agroal. is set → Using Agroal

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

9. else → User-provided Connections

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

7.3. Driver Configuration

hibernate.connection.driver_class

The name of the JDBC Driver class to use

hibernate.connection.url

The JDBC connection url

hibernate.connection.*

All such setting names (except the predefined ones) will have the hibernate.connection. prefix stripped. The remaining name and the original value will be passed to the driver as a JDBC connection property

7.4. Using c3p0

Hibernate also provides support for applications to use c3p0 connection pooling. When c3p0 support is enabled, a number of c3p0-specific configuration settings are recognized in addition to the general ones described in Driver Configuration.

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

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 the 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

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

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

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

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

7.6. Using HikariCP

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

7.7. Using Vibur DBCP

Hibernate also provides support for applications to use Vibur DBCP connection pool.

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

hibernate.connection.driver_class

Mapped to Vibur’s driverClassName setting

hibernate.connection.url

Mapped to Vibur’s jdbcUrl setting

hibernate.connection.username

Mapped to Vibur’s username setting

hibernate.connection.password

Mapped to Vibur’s password setting

hibernate.connection.isolation

Mapped to Vibur’s defaultTransactionIsolationValue setting. See ConnectionProvider support for transaction isolation setting.

hibernate.connection.autocommit

Mapped to Vibur’s defaultAutoCommit setting

7.8. Using Agroal

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

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

hibernate.connection.driver_class

Mapped to Agroal’s driverClassName setting

hibernate.connection.url

Mapped to Agroal’s jdbcUrl setting

hibernate.connection.username

Mapped to Agroal’s principal setting

hibernate.connection.password

Mapped to Agroal’s credential setting

hibernate.connection.isolation

Mapped to Agroal’s jdbcTransactionIsolation setting. See ConnectionProvider support for transaction isolation setting.

hibernate.connection.autocommit

Mapped to Agroal’s autoCommit setting

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

This section is here just for completeness.

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

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

7.12. Connection handling

The connection handling mode is defined by the PhysicalConnectionHandlingMode enumeration which provides the following strategies:

IMMEDIATE_ACQUISITION_AND_HOLD

The Connection will be acquired as soon as the Session is opened and held until the Session is closed.

DELAYED_ACQUISITION_AND_HOLD

The Connection will be acquired as soon as it is needed and then held until the Session is closed.

DELAYED_ACQUISITION_AND_RELEASE_AFTER_STATEMENT

The Connection will be acquired as soon as it is needed and will be released after each statement is executed.

DELAYED_ACQUISITION_AND_RELEASE_AFTER_TRANSACTION

The Connection will be acquired as soon as it is needed and will be released after each transaction is completed.

If you don’t want to use the default connection handling mode, you can specify a connection handling mode via the hibernate.connection.handling_mode configuration property. For more details, check out the Database connection properties section.

7.12.1. Transaction type and connection handling

By default, the connection handling mode is given by the underlying transaction coordinator. There are two types of transactions: RESOURCE_LOCAL (which involves a single database Connection and the transaction is controlled via the commit and rollback Connection methods) and JTA (which may involve multiple resources including database connections, JMS queues, etc).

RESOURCE_LOCAL transaction connection handling

For RESOURCE_LOCAL transactions, the connection handling mode is DELAYED_ACQUISITION_AND_RELEASE_AFTER_TRANSACTION meaning that the database connection is acquired when needed and released after the current running transaction is either committed or rolled back.

However, because Hibernate needs to make sure that the default autocommit mode is disabled on the JDBC Connection when starting a new transaction, the Connection is acquired and the autocommit mode is set to false.

JTA transaction connection handling

For JTA transactions, the connection handling mode is DELAYED_ACQUISITION_AND_RELEASE_AFTER_STATEMENT meaning that the database connection is acquired when needed and released after each statement execution.

The reason for releasing the database connection after statement execution is because some Java EE application servers report a connection leak when a method call goes from one EJB to another. However, even if the JDBC Connection is released to the pool, the Connection is still allocated to the current executing Thread, hence when executing a subsequent statement in the current running transaction, the same Connection object reference will be obtained from the pool.

7.12.2. User-provided connections

If the current Session was created using the SessionBuilder and a JDBC Connection was provided via the SessionBuilder#connection method, then the user-provided Connection is going to be used, and the connection handling mode will be IMMEDIATE_ACQUISITION_AND_HOLD.

Therefore for user-provided connection, the connection is acquired right away and held until the current Session is closed, without being influenced by the JPA or Hibernate transaction context.

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