Spring-data-jpa/spring orm/spring tx

Hi,

Seems like the Pooled datasource bean declaration is not that compatible with hibernate framework.

My code:

    @Bean("sourceDataSource")
    public DataSource sourceDataSource(
            @Qualifier("sourceDataSourceProperties") DataSourceProperties sourceDataSourceProperties, //any datasource that needs a username and a password
            @Qualifier("sourceHikariConfigProperties") HikariConfig hikariConfigProperties
    ) {
        HikariDataSource ds = sourceDataSourceProperties.initializeDataSourceBuilder().type(CustomHikariDatasource.class).build();
        if (null != hikariConfigProperties.getDataSourceClassName()) {
            ds.setDataSourceClassName(hikariConfigProperties.getDataSourceClassName());
        }
        ds.setAutoCommit(hikariConfigProperties.isAutoCommit());
        ds.setPoolName(hikariConfigProperties.getPoolName());
        return ds;
    }

    @Bean("sourceEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean sourceEntityManagerFactory(
            @Qualifier("sourceDataSource") DataSource dataSource,
            EntityManagerFactoryBuilder builder, @Qualifier("sourcePerDsJpaProperties")PerDSJpaProperties jpaPropertiesWrapper) {
        return builder
          .dataSource(dataSource)
                .persistenceUnit("source")
                .properties(jpaPropertiesWrapper.getProperties())
          .packages(dataMigratorConfiguration.getModelBasePackage())
          .build();
    }
    @Bean("sourceTransactionManager")
    public PlatformTransactionManager sourceTransactionManager(
        @Qualifier("sourceEntityManagerFactory") LocalContainerEntityManagerFactoryBean sourceEntityManagerFactory
    ) {
        JpaTransactionManager jpaTransactionManager = new JpaTransactionManager(Objects.requireNonNull(sourceEntityManagerFactory.getObject()));
        return jpaTransactionManager;
    }

Unfortunately, when using hikari, username and password are delegated to delegated db, not to use with connection, see the code of HikariDatasource.java#133

   /** {@inheritDoc} */
   @Override
   public Connection getConnection(String username, String password) throws SQLException
   {
      throw new SQLFeatureNotSupportedException();
   }

And the org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl#107 useCredentials = user != null || pass != null;

Taking these two pieces of code into consideration, Spring TX (or even the simplest @PersistenceContext em.commit()) won't work. I guess that there's a hook to include in spring's code to handle it.

Comment From: scottfrederick

Thanks for getting in touch. Unfortunately the code snippet you've provided isn't enough for us to completely understand what's going on. If you would like us to spend some time investigating, please provide a complete minimal sample that reproduces the problem. You can share it with us by pushing it to a separate repository on GitHub or by zipping it and attaching it to this issue.

Comment From: spring-projects-issues

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

Comment From: Tcharl

Sure sure, I will, please let us wait 'til sunday

Comment From: Tcharl

Hi,

I created a project illustrating the issue: it's not really minimal, but can illustrate the issue.

To test it it's easy: 1. ./mvnw clean install (java17 or 21 and docker installed on the machine) 2. cd sample-mono 3. ./mvnw clean verify -Pentities-from-changelog

The project will start two datasources, import the changelog in the source datasource via liquibase changelog, then generate hibernate beans & spring repositories, execute some data transformation magic and load the result into a target database.

Spring configuration

  • application.yml declares the source and sink dbs
  • The DatasourceConfiguration declares both datasources (source & sink): The issue is here: I had to create a CustomHikariDataSource because spring's Pojo declaration of datasource and jpa declaration will forward dbuserand dbpasswd to hikari connection, which is unsupported by the technology.
  • The JpaConfiguration declares spring-data-jpa and txmanager for both databases

Is it enough for the spring team to investigate and identify a clever way to tackle this?

Additional idea: I had to specify multiple satellite configurations due to the rigidity of the spring-data-jpa properties configuration: unlike the jdbc spec, which supports named configuration (e.g. spring.datasource..), the jpa configuration doesn't have the notion of <entitymanageralias> which forces me to create a custom configuration. I guess that this behavior could be a bit improved

Comment From: wilkinsona

I'm afraid that's far too much code for us to look through and understand. We're a small team and we can't justify the time that it will take to do that to investigate a problem that may have nothing to do with Spring Boot directly. Given that you're configuring DataSources, EntityManagers and so on manually, Spring Boot will be doing very little as much of its auto-configuration will have backed off.

If you can remove the code that isn't relevant to the problem that you're facing and produce something minimal we can take another look. It sounds like it should focus on the call to javax.sql.DataSource.getConnection(String, String). As far as I can tell there's nothing in Spring Boot (or Spring Data JPA and Spring Framework) that will call this method and only Hibernate will do so. That can be avoided by not configuring Hibernate with a username and password.

Comment From: Tcharl

I will, next sunday please, I'm an humble individual ;-).

That can be avoided by not configuring Hibernate with a username and password.

Sure, but unfortunately Spring or Hibernate configuration is not that fined-grained in term of configuration: the pooling datasource (Hikari) takes the same arguments as the underlying datasource (i.e. Postgres) takes. I guess that end users will always need credentials for underlying db (Pg), while that pooling vendors (i.e. Hikari) won't care.

Comment From: wilkinsona

I don't think your problem has anything to do with configuring the username and password on the DataSource. The problem is that you've also configured Hibernate with the same username and password. This results in it trying to use per-connection credentials which Hikari doesn't support. I don't think any of your Hibernate-specific JDBC connection configuration is needed. Instead, Hibernate should just use as-is the DataSource that has already been configured with the same settings.

I'm going to close this one now as there's nothing to suggest that this is a Spring Boot problem. If you require further guidance on configuring Hikari and Hibernate in a Spring Boot app, please follow up on Stack Overflow. As mentioned in the guidelines for contributing, we prefer to use GitHub issues only for bugs and enhancements. If you believe that you have found a Spring Boot bug and can provide a minimal example that demonstrates it, we can re-open this issue and take another look.