Hibernate v6 generates a query with IN clause upon EntityManager.merge() call. Is there a way to avoid IN clause in favor of equals+AND?

Hibernate ORM
1

Hi
We noticed that after a migration from Hibernate v5 to v6 the code

EntityManager.merge(entity)

started generating a query looking differently (and it is not working for our DB).
V5 query was like:

select t.field1, t.field2 from entity_table t where t.id1 = ? and t.id2 = ?

V6 query is like:

select t.field1, t.field2 from entity_table t where (t.id1, t.id2) IN ((?, ?))

Entity has a composed ID from 2 fields id1, id2.
So, as we see v5 query is simpler and it is supported by more databases.
Is there a way to stay on V6 but to switch back to the old way of query generation avoiding IN clause?

P.S. We are using PostgreSQLDialect and Netezza database below.

2

Hibernate supports a large set of database vendors including most of the popular ones and handles cross-compatibility natively.

I believe Netezza is based on a very old version of PostgreSQL, which is no longer supported by Hibernate ORM. You could try using org.hibernate.community.dialect.PostgreSQLLegacyDialect instead of the standard one, but we cannot assure it will solve your issues.

The only other alternative is creating a custom org.hibernate.dialect.Dialect and/or org.hibernate.sql.ast.SqlAstTranslator implementations with logic that works with your DBMS.

3

Good afternoon.
We encountered a similar problem when migrating to version 6.
Oracle DB.
We went through possible dialects for this DB, but the problem unfortunately did not go away.
Are there any other solutions besides creating a custom org.hibernate.dialect.Dialect and/or org.hibernate.sql.ast.SqlAstTranslator implementations?

Thank you.

4

After searching the Hibernate forum for an answer, I found the answer to my question. The performance issue when using composite keys for Oracle DB was fixed in versions 6.4.10.Final and 6.5.3.Final, as noted here:
https://hibernate.atlassian.net/browse/HHH-18352