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:
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?
Interestingly, if you replace the ROW function with an empty string, the query still generates correctly, simulating tuple behavior without using the word ROW.
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')
)
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')