Customize the SELECT query syntax generation for h2 database

Hibernate ORM
1

We are using JPA save() function for Inserting data in h2 table. We do see SELECT query runs before execution of INSERT query to verify the given data is already present or not. However we could see two different SELECT query syntax has generated in two different setup.

Please find the details below. The performance of Environment#1’s SELECT query is very low when comparing with Environment#2 performance. Since Environment#1 is generating the query syntax SELECT with IN clause. We would like to disable the query syntax SELECT with IN clause in the hibernate layer and would like to generate the SELECT query syntax as per Environment #2 always in Environment #1.

Kindly advise.

Environment #1:
Java 17 + Spring Boot 3.x (Spring JPA 3.0.0 + Hibernate 6.1.5.Final) + H2 database 2.1.214

2023-06-29T17:50:57.057+05:30 INFO 91686 — [ main] JpaRepositoryTest : Started JpaRepositoryTest in 1.711 seconds (process running for 2.437)
2023-06-29T17:50:57.169+05:30 DEBUG 91686 — [ main] org.hibernate.SQL :

select
        p1_0.dest_addr,
        p1_0.src_address,
        p1_0.tunnel_id,
        p1_0.uuid 
    from
        "policy" p1_0 
    where
        (
            p1_0.dest_addr,p1_0.src_address,p1_0.tunnel_id
        )  in((?,?,?))

2023-06-29T17:50:57.192+05:30 DEBUG 91686 — [ main] org.hibernate.SQL :

 insert 
    into
        "policy"
        (uuid, dest_addr, src_address, tunnel_id) 
    values
        (?, ?, ?, ?)

Environment #2:
Java 11 + Spring Boot 2.7.5 (JPA 2.7.5 + Hibernate core - 5.6.12.Final) + H2 database 2.1.214

2023-06-29 17:50:38.655 INFO 91630 — [ main] JpaRepositoryTest : Started JpaRepositoryTest in 2.14 seconds (JVM running for 2.652)
2023-06-29 17:50:38.739 DEBUG 91630 — [ main] org.hibernate.SQL :

select
        policy0_.dest_addr as dest_add1_0_0_,
        policy0_.src_address as src_addr2_0_0_,
        policy0_.tunnel_id as tunnel_i3_0_0_,
        policy0_.uuid as uuid4_0_0_ 
    from
        "policy" policy0_ 
    where
        policy0_.dest_addr=? 
        and policy0_.src_address=? 
        and policy0_.tunnel_id=?

2023-06-29 17:50:38.768 DEBUG 91630 — [ main] org.hibernate.SQL :

 insert 
    into
        "policy"
        (uuid, dest_addr, src_address, tunnel_id) 
    values
        (?, ?, ?, ?)
2

Is there a way to control the SELECT query syntax by custom H2Dialect? We would like to generate below SELECT query syntax when calling JPA save()

select
        policy0_.dest_addr as dest_add1_0_0_,
        policy0_.src_address as src_addr2_0_0_,
        policy0_.tunnel_id as tunnel_i3_0_0_,
        policy0_.uuid as uuid4_0_0_ 
    from
        "policy" policy0_ 
    where
        policy0_.dest_addr=? 
        and policy0_.src_address=? 
        and policy0_.tunnel_id=?

Please advise.

3

Update to Hibernate 6.2.6.Final, which should fix this.