Implementing Multi-Column IN Clause Using ROW Function

I wanted to share an approach I’ve been working on using CriteriaBuilder to leverage PostgreSQL’s tuple comparison feature with the ROW function enabling us to compare combinations of fields directly within an IN clause. I would like your feedback on its suitability for production use.

Here’s how I implemented this using Hibernate’s CriteriaBuilder:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
Root<User> root = query.from(User.class);

Expression<?> userTuple = cb.function("ROW", Object.class, root.get("username"), root.get("email"));

List<Expression<?>> values = new ArrayList<>();
values.add(cb.function("ROW", Object.class, cb.literal("test1"), cb.literal("test1@email.com")));
values.add(cb.function("ROW", Object.class, cb.literal("test2"), cb.literal("test2@email.com")));

Predicate inClause = userTuple.in(values);

This method generates the following HQL and SQL queries:

select Entity_0 
from dev.joserg.app.entity.User Entity_0 
where ROW(Entity_0.username, Entity_0.email) in (
    ROW('test1', 'test1@email.com'), 
    ROW('test2', 'test2@email.com')
)
select u1_0.id, u1_0.email, u1_0.password, u1_0.username 
from users u1_0 
where ROW(u1_0.username, u1_0.email) in (
    ROW('test1', 'test1@email.com'), 
    ROW('test2', 'test2@email.com')
)

Has anyone else implemented this method in their projects?
Would you recommend using this method in a production environment?

Thank you!

Interestingly, if you replace the ROW function with an empty string, the query still generates correctly, simulating tuple behavior without using the word ROW.

Expression<?> userTuple = cb.function("", Object.class, root.get("username"), root.get("email"));

List<Expression<?>> values = new ArrayList<>();
values.add(cb.function("", Object.class, cb.literal("test1"), cb.literal("test1@email.com")));
values.add(cb.function("", Object.class, cb.literal("test2"), cb.literal("test2@email.com")));
select Entity_0 
from dev.joserg.app.entity.User Entity_0 
where (Entity_0.username, Entity_0.email) in (
  ('test1', 'test1@email.com'), 
  ('test2', 'test2@email.com')
)
select u1_0.id, u1_0.email, u1_0.password, u1_0.username 
from users u1_0 
where (u1_0.username, u1_0.email) in (
   ('test1', 'test1@email.com'), 
   ('test2', 'test2@email.com')
)

You should be able to use Hibernate ORM builtin functions to achieve this in a database independent manner:

HibernateCriteriaBuilder cb = (HibernateCriteriaBuilder) entityManager.getCriteriaBuilder();
Root<User> root = query.from(User.class);

Expression<Tuple> userTuple = (Expression<Tuple>) cb.tuple(root.get("username"), root.get("email"));

List<Expression<?>> values = new ArrayList<>();
values.add((Expression<Tuple>) cb.tuple(cb.literal("test1"), cb.literal("test1@email.com")));
values.add((Expression<Tuple>) cb.tuple(cb.literal("test2"), cb.literal("test2@email.com")));

Predicate inClause = userTuple.in(values);

Thanks for your suggestion, but it seems to be an Exception when casting.

java.lang.ClassCastException: class org.hibernate.query.sqm.tree.select.SqmJpaCompoundSelection cannot be cast to class org.hibernate.sql.ast.tree.expression.Expression (org.hibernate.query.sqm.tree.select.SqmJpaCompoundSelection and org.hibernate.sql.ast.tree.expression.Expression are in unnamed module of loader 'app')

You should be casting to jakarta.persistence.criteria.Expression