Hibernate using an instance of javax.sql.DataSource

Question

Is it possible to configure Hibernate to use a javax.sql.DataSource instance?

My application already has an instance of javax.sql.DataSource and I'd rather not re-configure the database url, user, password, driver etc just for hibernate.

Similar: https://stackoverflow.com/q/24175307/642706 & https://stackoverflow.com/q/4406935/642706 — Basil Bourque, Aug 14 '23 at 08:40

score 4 · Accepted Answer · answered May 19 '09 at 18:03

If the data source is available through JNDI, you just need to set the hibernate.connection.datasource property of the configuration - mtpettyp's answer demonstrates that.

If you're not in an environment where your data sources come from JNDI, provide your own ConnectionProvider implementation, and either pass it to the Settings object before building the session factory, or specify its class name in the hibernate.connection.provider_class property. If you provide an instance to Settings, you can probably instantiate the standard DatasourceConnectionProvider and give it your DataSource.

mtpettyp · Answer 2 · 2010-01-16T18:10:07.947

In your hibernate.cfg.xml:

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
          "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
          "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>
        <property name="dialect">
            org.hibernate.dialect.MySQLDialect
        </property>    
        <property name="connection.datasource">SampleDS</property>        
    </session-factory>

</hibernate-configuration>

score 1 · Answer 3 · answered May 19 '09 at 18:03

If you're using spring initialization and a spring session factory bean you can simply pass in the data source like this

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    ...
</bean>

where the dataSource reference is defined elsewhere.

Basil Bourque · Answer 4 · 2023-08-14T22:54:13.450

Caveat: I don’t really know what I am doing here. I am new to Hibernate and Jakarta Persistence. I cobbled together this code with various pieces from various sources, and monkeyed around until it worked.

I could not find anything close to being a complete example of how to configure Hibernate/Jakarta Persistence in code-only (no XML files, no JNDI) using an existing DataSource instance. So I posted this here. Hopefully it can help others.

Here is an example using Java SE, without any Jakarta EE application server.

I used:

Server: MySQL 8.1.0 on Linux in Docker Desktop 4.22.0 app for Apple Silicon (aarch64)
Client: Hibernate Core 6.2.7.Final which brought in Jakarta Persistence 3.1.0, with the JDBC driver MySQL Connector/J 8.1.0, in IntelliJ IDEA 2023.2.1 Preview, in Java 20.0.2, on macOS Ventura on a MacBook Pro with Apple M1 Pro chip.

I configured Hibernate strictly with the code seen below. I did not write any configuration XML file.

`DataSource` code

javax.sql.DataSource in the interface bundled with Java SE. We need an implementation of that interface. In this example, we use an implementation provided by the JDBC driver, MySQL Connector/J, in the class com.mysql.cj.jdbc.MysqlDataSource.

We set up an instance of that DataSource implementation in the following method.

private DataSource configureDataSource ( )
{
    com.mysql.cj.jdbc.MysqlDataSource dataSource = new com.mysql.cj.jdbc.MysqlDataSource ( );
    dataSource.setDatabaseName ( "bogus_" );
    dataSource.setServerName ( "localhost" );
    dataSource.setPort ( 8090 );
    dataSource.setUser ( "root" );
    dataSource.setPassword ( "" );  // WARNING: Risky business, leaving your server unprotected, without security. 
    return dataSource;
}

For more info, see the Configuration Properties of the Connector/J manual.

Hibernate code

Having that DataSource instance in hand, we can use Hibernate to connect to the database server, execute a query, and package the retrieved data as an object.

Configuration cfg = new Configuration ( );
cfg.getProperties ( ).put ( Environment.DATASOURCE , this.dataSource );
cfg.addAnnotatedClass ( Book.class );
ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder ( ).applySettings ( cfg.getProperties ( ) ).build ( );

try ( SessionFactory sessionFactory = cfg.buildSessionFactory ( serviceRegistry ) )
{
    Session session = sessionFactory.openSession ( );
    Query jpqlQuery = session.createQuery ( "SELECT b FROM Book b WHERE b.title=:title" , Book.class );
    jpqlQuery.setParameter ( "title" , "Our First Book" );
    Book book = ( Book ) jpqlQuery.getSingleResult ( );
    System.out.println ( "Retrieved via Hibernate… book = " + book );
}

Retrieved via Hibernate… book = Book{id=1, title='Our First Book', createdOn=2023-08-14T20:26:20.708102Z}

Full example code

First the Book class. We expect Hibernate to instantiate an object of this class.

package work.basil.example.hibernate;

import jakarta.persistence.* ;

import java.time.OffsetDateTime;

@Entity
@Table(name = "book")
public class Book {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;

    private String title;

    @Column(name = "created_on")
    private OffsetDateTime createdOn;

    // Accessors

    public Long getId ( )
    {
        return id;
    }

    public void setId ( Long id )
    {
        this.id = id;
    }

    public String getTitle ( )
    {
        return title;
    }

    public void setTitle ( String title )
    {
        this.title = title;
    }

    public OffsetDateTime getCreatedOn ( )
    {
        return createdOn;
    }

    public void setCreatedOn ( OffsetDateTime createdOn )
    {
        this.createdOn = createdOn;
    }

    // Object overrides

    @Override
    public String toString ( )
    {
        return "Book{" +
               "id=" + id +
               ", title='" + title + '\'' +
               ", createdOn=" + createdOn +
               '}';
    }
}

And our complete example application code. This code:

Re-creates a single table, book. Then repopulates that table with a single row. Both steps use straight JDBC, no Hibernate.
Uses Hibernate to query for our single row, and instantiate a Book object with that retrieved data.

package work.basil.example.hibernate;

import jakarta.persistence.*;
import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.hibernate.Session;
import org.hibernate.cfg.Environment;
import org.hibernate.service.ServiceRegistry;

import javax.sql.DataSource;
import java.sql.*;
import java.time.Instant;
import java.time.OffsetDateTime;
import java.time.*;
import java.time.ZonedDateTime;

public class App
{
    final DataSource dataSource;

    private void demo ( )
    {
        this.dropTable ( );
        this.createTable ( );
        this.insertRow ( );
        this.dumpRows ( );
        this.dumpHibernate ( );
    }

    private void dumpHibernate ( )
    {
        Configuration cfg = new Configuration ( );
        cfg.getProperties ( ).put ( Environment.DATASOURCE , this.dataSource );
        cfg.addAnnotatedClass ( Book.class );
        ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder ( ).applySettings ( cfg.getProperties ( ) ).build ( );

        try ( SessionFactory sessionFactory = cfg.buildSessionFactory ( serviceRegistry ) )
        {
            Session session = sessionFactory.openSession ( );
            Query jpqlQuery = session.createQuery ( "SELECT b FROM Book b WHERE b.title=:title" , Book.class );
            jpqlQuery.setParameter ( "title" , "Our First Book" );
            Book book = ( Book ) jpqlQuery.getSingleResult ( );
            System.out.println ( "Retrieved via Hibernate… book = " + book );
        }
    }

    private void dumpRows ( )
    {
        String sql = """
                SELECT *
                FROM book
                 ;
                 """;
        try (
                Connection connection = this.dataSource.getConnection ( ) ;
                Statement statement = connection.createStatement ( ) ;
                ResultSet resultSet = statement.executeQuery ( sql ) ;
        )
        {
            record BookRecord( Long id , String title , OffsetDateTime odt ) { }
            while ( resultSet.next ( ) )
            {
                Long id = resultSet.getLong ( "id" );
                String title = resultSet.getString ( "title" );
                OffsetDateTime odt = resultSet.getObject ( "created_on" , OffsetDateTime.class );
                System.out.println ( new BookRecord ( id , title , odt ).toString ( ) );
            }
        }
        catch ( SQLException e )
        {
            throw new RuntimeException ( e );
        }
    }

    private void insertRow ( )
    {
        String sql = """
                INSERT INTO book
                ( title , created_on )
                VALUES
                ( ? , ? )
                 ;
                 """;
        try (
                Connection connection = this.dataSource.getConnection ( ) ;
                PreparedStatement preparedStatement = connection.prepareStatement ( sql ) ;
        )
        {
            ZonedDateTime zdt = ZonedDateTime.now ( ZoneId.of ( "America/Edmonton" ) ); // Capture the current moment as seen in a particular time zone.
            OffsetDateTime odt = zdt.toOffsetDateTime ( );  // Discard the time zone, keeping only a mere offset-from-UTC. Not an improvement, but standard SQL does not support only offsets, not time zones.
            Instant instant = odt.toInstant ( );  // Adjust to an offset from UTC of zero hours-minutes-seeconds.
            System.out.println ( "zdt = " + zdt );  // Be aware: All three of these date-time objects represent the very same moment, the same point on the timeline.
            System.out.println ( "odt = " + odt );
            System.out.println ( "instant = " + instant );
            preparedStatement.setString ( 1 , "Our First Book" );
            preparedStatement.setString ( 2 , odt.toString ( ) );
            preparedStatement.executeUpdate ( );
        }
        catch ( SQLException e )
        {
            throw new RuntimeException ( e );
        }
    }

    private void dropTable ( )
    {
        String sql = """
                DROP TABLE IF EXISTS book
                ;
                """;
        try (
                Connection connection = this.dataSource.getConnection ( ) ;
                Statement statement = connection.createStatement ( ) ;
        )
        {
            statement.executeUpdate ( sql );
        }
        catch ( SQLException e )
        {
            throw new RuntimeException ( e );
        }
    }

    private void createTable ( )
    {
        String sql = """
                CREATE TABLE book (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY ,
                    title VARCHAR(255) NOT NULL ,
                    created_on TIMESTAMP(6) NOT NULL
                ) ;
                """;
        try (
                Connection connection = this.dataSource.getConnection ( ) ;
                Statement statement = connection.createStatement ( ) ;
        )
        {
            statement.executeUpdate ( sql );
        }
        catch ( SQLException e )
        {
            throw new RuntimeException ( e );
        }
    }


    // Constructor
    public App ( )
    {
        this.dataSource = this.configureDataSource ( );
    }

    private DataSource configureDataSource ( )
    {
        com.mysql.cj.jdbc.MysqlDataSource dataSource = new com.mysql.cj.jdbc.MysqlDataSource ( );
        dataSource.setDatabaseName ( "bogus_" );
        dataSource.setServerName ( "localhost" );
        dataSource.setPort ( 8090 );
        dataSource.setUser ( "root" );
        dataSource.setPassword ( "" );  // WARNING: Risky business, leaving your server unprotected, without security.
        return dataSource;
    }

    public static void main ( String[] args )
    {
        App app = new App ( );
        app.demo ( );
    }

    private void dumpTime ( )
    {
        String sql = "SELECT CURRENT_TIMESTAMP  ;";
        try (
                Connection connection = this.dataSource.getConnection ( ) ;
                Statement statement = connection.createStatement ( ) ;
                ResultSet resultSet = statement.executeQuery ( sql ) ;
        )
        {
            while ( resultSet.next ( ) )
            {
                String now = resultSet.getString ( 1 );
                System.out.println ( now + "\n" );
            }
        }
        catch ( SQLException e )
        {
            throw new RuntimeException ( e );
        }
    }
}

When run:

zdt = 2023-08-14T14:26:20.708102-06:00[America/Edmonton]
odt = 2023-08-14T14:26:20.708102-06:00
instant = 2023-08-14T20:26:20.708102Z
BookRecord[id=1, title=Our First Book, odt=2023-08-14T20:26:20.708102-07:00]
Aug 14, 2023 1:26:20 PM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate ORM core version 6.2.7.Final
Aug 14, 2023 1:26:20 PM org.hibernate.cfg.Environment <clinit>
INFO: HHH000406: Using bytecode reflection optimizer
Aug 14, 2023 1:26:21 PM org.hibernate.bytecode.internal.BytecodeProviderInitiator buildBytecodeProvider
INFO: HHH000021: Bytecode provider name : bytebuddy
Aug 14, 2023 1:26:21 PM org.hibernate.engine.transaction.jta.platform.internal.JtaPlatformInitiator initiateService
INFO: HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
Retrieved via Hibernate… book = Book{id=1, title='Our First Book', createdOn=2023-08-14T20:26:20.708102Z}

Hibernate using an instance of javax.sql.DataSource

4 Answers4

`DataSource` code

Hibernate code

Full example code

Linked

Hibernate using an instance of javax.sql.DataSource

4 Answers4

DataSource code

Hibernate code

Full example code

Linked

`DataSource` code