# Customize the SELECT query syntax generation for h2 database

**URL:** <https://discourse.hibernate.org/t/customize-the-select-query-syntax-generation-for-h2-database/7911>\
**Category:** Hibernate ORM\
**Created:** [July 3, 2023, 10:58am UTC](https://discourse.hibernate.org/t/customize-the-select-query-syntax-generation-for-h2-database/7911 "2023-07-03T10:58:40Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![dhanaren](https://avatars.discourse-cdn.com/v4/letter/d/f04885/32.png) [@dhanaren](https://discourse.hibernate.org/u/dhanaren)\
**Post date:** [July 3, 2023, 10:58am UTC](https://discourse.hibernate.org/t/customize-the-select-query-syntax-generation-for-h2-database/7911/1 "2023-07-03T10:58:41Z")

</div>

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 :

```auto
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 :

```auto
 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 :

```auto
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 :

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

```

---

<div class="post-metadata">

**Author:** ![dhanaren](https://avatars.discourse-cdn.com/v4/letter/d/f04885/32.png) [@dhanaren](https://discourse.hibernate.org/u/dhanaren)\
**Post date:** [July 5, 2023, 6:03am UTC](https://discourse.hibernate.org/t/customize-the-select-query-syntax-generation-for-h2-database/7911/2 "2023-07-05T06:03:05Z")

</div>

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()

```auto
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.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [July 14, 2023, 12:44pm UTC](https://discourse.hibernate.org/t/customize-the-select-query-syntax-generation-for-h2-database/7911/3 "2023-07-14T12:44:16Z")

</div>

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