Search code examples
javaspringspring-boothibernatejpa

Hibernate 6.1.x / Spring Boot : avoid global temporary table creation

Hibernate creates temporary tables for bulk operations.

We're using Oracle (v19.0.0.0.0) and our user won't have the privilege to create temporary table.

I've tried a lot of different configurations, but HTE_ tables are still created on startup :

09:07:43.658 INFO  [com.zaxxer.hikari.HikariDataSource      ] HikariPool-1 - Starting...
09:07:44.285 INFO  [com.zaxxer.hikari.pool.HikariPool       ] HikariPool-1 - Added connection oracle.jdbc.driver.T4CConnection@6dc5e857
09:07:44.286 INFO  [com.zaxxer.hikari.HikariDataSource      ] HikariPool-1 - Start completed.
09:07:44.596 INFO  [SQL dialect                             ] HHH000400: Using dialect: org.hibernate.dialect.Oracle12cDialect
Hibernate: create global temporary table HTE_xxxxx(...) on commit delete rows
Hibernate: create global temporary table HTE_yyyyy(...) on commit delete rows
09:07:45.551 INFO  [j.LocalContainerEntityManagerFactoryBean] Initialized JPA EntityManagerFactory for persistence unit 'default'

These configurations don't work :

spring.jpa.properties.hibernate.query.mutation_strategy=org.hibernate.query.sqm.mutation.internal.temptable.LocalTemporaryTableMutationStrategy

OR 

spring.jpa.properties.hibernate.query.mutation_strategy=org.hibernate.query.sqm.mutation.internal.inline.InlineMutationStrategy
spring.jpa.properties.hibernate.query.insert_strategy=org.hibernate.query.sqm.mutation.internal.temptable.LocalTemporaryTableInsertStrategy

I'm using Hibernate 6.1.7.Final and Spring Boot 3.0.6.

I did not find other ways in the documentation : https://docs.jboss.org/hibernate/stable/orm/userguide/html_single/Hibernate_User_Guide.html

Solution

  • This was an Hibernate issue that has been solved in 6.2.0 :

    In addition to the hibernate.hql.bulk_id_strategy.global_temporary.drop_tables and hibernate.hql.bulk_id_strategy.persistent.drop_tables settings, add a create_tables variant which allows to control whether to create tables. Keep it true by default to retain backwards compatibility.

    Spring Boot 3.1 upgrades to Hibernate 6.2, so upgrading SB to 3.1.x fixes the issue.