|

JDBC Connection and Dialect Configuration in Hibernate

Avatar photoByThorben Janssen

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

To connect your persistence layer to your database, you need to configure a data source or provide the JDBC connection driver, URL, and login information to Hibernate directly. In addition to that, Hibernate needs to know the database-specific dialect it shall use to generate the SQL statements. Hibernate can detect the dialect based on the configured JDBC connection, or you can provide it as a configuration parameter.

You can set all this information in the persistence.xml or, if you’re using Spring Data JPA, in the application.properties file. In the following sections, I will shortly explain the different configuration parameters before I provide you with example configurations for the most commonly used RDBMS.

Configuration Parameters

Data Source

If you use a connection pool, you can reference its JNDI name as a JTA data source, if it’s compliant to the Java Transaction API, or as a non-JTA data source.

<persistence>
    <!-- Define persistence unit -->
    <persistence-unit name="my-persistence-unit">
        <jta-data-source>java:app/jdbc/MyDataSource</jta-data-source>
    </persistence-unit>
</persistence>

The connection to the database is already configured in the connection pool. Therefore, you don’t need to set the JDBC driver, URL, and user information in your Hibernate configuration. But you might want to configure the dialect.

JDBC Connection

You can use the following configuration parameters to configure the JDBC connection in Hibernate:

  • javax.persistence.jdbc.driver – The fully qualified class name of your JDBC driver
  • javax.persistence.jdbc.url – The connection URL of your database
  • javax.persistence.jdbc.user – The user name to login to your database
  • javax.persistence.jdbc.password – The password to login to your database

In the following configuration snippet, I use these parameters to connect to the PostgreSQL database jpaForBeginners on localhost using postgres as the user name and password.

<persistence>
    <persistence-unit name="my-persistence-unit">
        <properties>
            <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />
            <property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost:5432/jpaForBeginners" />
            <property name="javax.persistence.jdbc.user" value="postgres" />
            <property name="javax.persistence.jdbc.password" value="postgres" />
        </properties>
    </persistence-unit>
</persistence>

Hibernate Dialect

Hibernate uses dialects to adapt to the feature sets and syntactical differences of the various supported RDBMS. For example, a dialect defines which functions are supported, how data types need to be mapped, and if by default Hibernate shall use a sequence or an auto-incremented database column to generate primary key values.

Hibernate provides a huge set of dialect implementations to support various versions of popular RDBMS. You can find them in the package org.hibernate.dialect. You can specify the dialect you want to use by setting the fully qualified class name of its implementation as the org.hibernate.dialect.Dialect configuration property.

If you only need to support 1 RDBMS, I recommend setting this configuration parameter even though Hibernate can automatically detect the dialect based on the configured JDBC connection.

Configuration Values for Commonly Used Databases

Here is a list of example configurations for the most commonly used databases in alphabetical order. All of these configurations connect to the jpaForBeginners database that runs on the default port on localhost. In addition to the provided configuration, you also need to set the user name and password.

CockroachDB

As explained in a previous article, Hibernate 5.4.19 added a CockroachDB dialect. On the JDBC level, it still uses the PostgreSQL driver.

javax.persistence.jdbc.driver = org.postgresql.Driver
javax.persistence.jdbc.url = jdbc:postgresql://localhost:26257/jpaForBeginners?sslmode=disable
org.hibernate.dialect.Dialect = org.hibernate.dialect.CockroachDB201Dialect

Derby

javax.persistence.jdbc.driver = org.apache.derby.jdbc.EmbeddedDriver
javax.persistence.jdbc.url = jdbc:derby:target/tmp/derby/jfb;databaseName=jpaForBeginners;create=true
org.hibernate.dialect.Dialect = org.hibernate.dialect.DerbyTenSevenDialect

Db2 Express-C

javax.persistence.jdbc.driver = com.ibm.db2.jcc.DB2Driver
javax.persistence.jdbc.url = jdbc:db2://localhost/jpaForBeginners
org.hibernate.dialect.Dialect = org.hibernate.dialect.DB297Dialect

Firebird

javax.persistence.jdbc.driver = org.firebirdsql.jdbc.FBDriver
javax.persistence.jdbc.url = jdbc:firebirdsql://localhost:3050/c:/wrk/myProject/db/jpaForBeginners.fdb
org.hibernate.dialect.Dialect = org.hibernate.dialect.FirebirdDialect

H2

javax.persistence.jdbc.driver = org.hsqldb.jdbc.JDBCDriver
javax.persistence.jdbc.url = jdbc:h2:mem:jpaForBeginners
org.hibernate.dialect.Dialect = org.hibernate.dialect.H2Dialect

HSQLDB

javax.persistence.jdbc.driver = org.hsqldb.jdbc.JDBCDriver
javax.persistence.jdbc.url = jdbc:hsqldb:mem:jpaForBeginners
org.hibernate.dialect.Dialect = org.hibernate.dialect.HSQLDialect

Informix

javax.persistence.jdbc.driver = com.informix.jdbc.IfxDriver
javax.persistence.jdbc.url = jdbc:informix-sqli://localhost:9088/sysuser:INFORMIXSERVER=jpaForBeginners
org.hibernate.dialect.Dialect = org.hibernate.dialect.Informix10Dialect

MariaDB

javax.persistence.jdbc.driver = org.mariadb.jdbc.Driver
javax.persistence.jdbc.url = jdbc:mariadb://localhost/jpaForBeginners
org.hibernate.dialect.Dialect = org.hibernate.dialect.MariaDB103Dialect

MySQL

MySQL is a popular open-source database that offers some interesting proprietary features but also introduces some limitations. I explain all of that in great detail in 5 Things You Need to Know When Using Hibernate with Mysql.

javax.persistence.jdbc.driver = com.mysql.jdbc.Driver
javax.persistence.jdbc.url = jdbc:mysql://localhost/jpaForBeginners
org.hibernate.dialect.Dialect = org.hibernate.dialect.MySQL8Dialect

Oracle

javax.persistence.jdbc.driver = oracle.jdbc.OracleDriver
javax.persistence.jdbc.url = jdbc:oracle:thin:@localhost:1521:jpaForBeginners
org.hibernate.dialect.Dialect = org.hibernate.dialect.Oracle12cDialect

PostgreSQL

PostgreSQL is a feature-rich, open-source database. I explained how to get the most out of it and use some of its most interesting proprietary features in Hibernate with PostgreSQL – 6 things you need to know.

javax.persistence.jdbc.driver = org.postgresql.Driver
javax.persistence.jdbc.url = jdbc:postgresql://localhost/jpaForBeginners
org.hibernate.dialect.Dialect = org.hibernate.dialect.PostgreSQL95Dialect

SAP HANA

javax.persistence.jdbc.driver = com.sap.db.jdbc.Driver
javax.persistence.jdbc.url = 		jdbc:sap://localhost/jpaForBeginners
org.hibernate.dialect.Dialect = org.hibernate.dialect.HANAColumnStoreDialect

SQL Server

javax.persistence.jdbc.driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
javax.persistence.jdbc.url = 	jdbc:sqlserver://localhost;instance=SQLEXPRESS;databaseName=jpaForBeginners
org.hibernate.dialect.Dialect = org.hibernate.dialect.SQLServer2012Dialect

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.