How do I write a JPA criteria query that matches a collection exactly?

Question

I’m using JPA 2.0 with Hibernate 4.1.0.Final. I have a couple of classes, Groups and GroupMembers. Each GroupMember is tied to a user object

@Entity
@Table(name = "group")
public class Group    
{
    @Id
    @NotNull
    @GeneratedValue(generator = "uuid-strategy")
    @Column(name = "ID")
    private String id;
    …


    @OneToMany(mappedBy = "group")
    private Set<GroupMember> members;



@Entity
@Table(name = "sb_msg_group_member")
public class GroupMember
{
    …

    @ManyToOne
    @JoinColumn(name = "USER_ID", nullable = false, updatable = true)
    private User user;

Is it possible to write a JPA criteria query that given a java.util.Set of User objects, would return the groups whose members match exactly that Set? I tried the below …

    final CriteriaBuilder builder = m_entityManager.getCriteriaBuilder();
    CriteriaQuery<Group> criteria = builder.createQuery(Group.class);
    final Root<Group> groupRoot = criteria.from(Group.class);
    final Set<GroupMember> groupMembers = new HashSet<GroupMember>();
    for (final User user : users)
    {
        final GroupMember groupMember = new GroupMember();
        groupMember.setUser(user);
        groupMembers.add(groupMember);
    }   // for
    criteria.where(builder.equal(groupRoot.get(Group_.members), groupMembers));
    final TypedQuery<Group> results = m_entityManager.createQuery(criteria);

but it fails with the exception …

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2611)
    at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2586)
    at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2510)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2259)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:56)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2040)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1837)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1816)
    at org.hibernate.loader.Loader.doQuery(Loader.java:900)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342)
    at org.hibernate.loader.Loader.doList(Loader.java:2526)
    at org.hibernate.loader.Loader.doList(Loader.java:2512)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2342)
    at org.hibernate.loader.Loader.list(Loader.java:2337)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:495)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:357)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1275)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:287)
    at org.hibernate.ejb.criteria.CriteriaQueryCompiler$3.getSingleResult(CriteriaQueryCompiler.java:258)
    at org.mainco.subco.messaging.repo.MessageDaoImpl.findGroupByMembers(MessageDaoImpl.java:144)
    at org.mainco.subco.messaging.repo.MessageDaoIT.testFindGroupByMembers(MessageDaoIT.java:83)

Tassos Bassoukos · Accepted Answer · 2014-06-17T22:44:49.750

1

You will need different roots for each and every user, as each of them is different from the others (untested):

final CriteriaBuilder builder = m_entityManager.getCriteriaBuilder();
CriteriaQuery<Group> criteria = builder.createQuery(Group.class);
final List<Predicate> predicates = new ArrayList<Predicate>();
final Root<Group> group = criteria.from(Group.class);
for (final User user : users)
{
    final Root<GroupMember> memberRoot = group.join(Group_.members);
    final Predicate p = builder.equal(memberRoot.get(GroupMember_.user), user);
    predicates.add(p);
}   // for
predicates.add(builder.equals(builder.count(group.get(Group_.members)),users.size()))
criteria.where(builder.and(predicates.toArray(new Predicate[predicates.size()])));
final TypedQuery<Group> results = m_entityManager.createQuery(criteria);

edited Jun 17 '14 at 22:44

answered Jun 10 '14 at 20:06

Tassos Bassoukos

16,017
2
36
40

Unfortunately, this results in a "javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: No value specified for parameter 2" exception. The query produced was "select group0_.ID as ID68_, group0_.CLASSROOM_ID as CLASSROOM3_68_, group0_.NAME as NAME68_, group0_.CREATOR as CREATOR68_ from sb_msg_group group0_ cross join sb_msg_group_member members1_ where group0_.ID=members1_.GROUP_ID and .=? limit ?" – Dave Jun 11 '14 at 20:24
Well, parameter 2 is the `limit ?` bit, have you tried adding a `query.setMaxResults(...)` to the query? More to the point, what is a GroupMember (I assume it's a JPA entity), and why does the `and .=? ` bit get produced? – Tassos Bassoukos Jun 12 '14 at 05:55
I don't know what the ".=?" is, I cut and pasted your code and that is what it produced. Yes, the GroupMember is a JPA entity, I defined it in the question but if I need to add more information, let me know and I can take care of that. – Dave Jun 12 '14 at 13:33
To correct compilation errors, I changed "final Root group = criteria.from(Group.class);" to "final Root group = criteria.from(Group.class);" and "final Predicate p = builder.equal(groupRoot.get(GroupMember_.user), user);" to "final Predicate p = builder.equal(memberRoot.get(GroupMember_.user), user);" but got the exception, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) and groupmembe2_.USER_ID='userid' and (groupmembe2_.ID in (.))' at line 1". – Dave Jun 12 '14 at 18:37
Can you post here the full SQL produced? – Tassos Bassoukos Jun 13 '14 at 11:01
Sure teh SQL is "select group0_.ID as ID70_, group0_.CLASSROOM_ID as CLASSROOM3_70_, group0_.NAME as NAME70_, group0_.CREATOR as CREATOR70_ from sb_msg_group group0_ cross join sb_msg_group_member groupmembe1_ cross join sb_msg_group_member groupmembe2_ cross join sb_msg_group_member members3_ cross join sb_msg_group_member members4_ where group0_.ID=members3_.GROUP_ID and group0_.ID=members4_.GROUP_ID and groupmembe1_.USER_ID=? and (groupmembe1_.ID in (.)) and groupmembe2_.USER_ID=? and (groupmembe2_.ID in (.))" – Dave Jun 13 '14 at 13:10
Ok, we're getting close; Replace `final Root memberRoot = criteria.from(GroupMember.class);` with `? = group.join(GroupMember.class);` and remove the last `predicates.add()` line; that should construct the proper SQL – Tassos Bassoukos Jun 13 '14 at 13:40
Could you update your code? I tried your suggestions but it produced a query where none of the users were passed as parameters. (SQL was too long pass in comments) – Dave Jun 13 '14 at 14:03
I've applied them to the question, have a look now. A similar thing happens in my own code and it works just fine. – Tassos Bassoukos Jun 17 '14 at 12:36
Thx so its close but it fails in the following situation: The group, G, contains users A and B, but you do a search with a Set containing only user A. The code you have will return group G, even though it should not (G's members are not exactly "user A" only). – Dave Jun 17 '14 at 18:47
For that, you just need to check that the size of the group is equal to the size of the group you seek (barring degenerate cases where the same user is in that group twice). I've added the line `predicates.add(builder.equals(builder.count(group.get(Group_.members)),users.size()))` (untested) that expresses that. – Tassos Bassoukos Jun 17 '14 at 22:44
The predicate produces the SQL " and count(.)=2" (in my example I was searching with "2" users in the Set. Trying to figure out why the "." is getting put in there, but if you have any insights, wanted to let you know. I appreciate your help with all this. – Dave Jun 18 '14 at 15:56
Ah, that's weird - I might be misunderstanding the Root> class; I'll take a shot at it again later when I'm at home; the idea was to check that the group had exactly these members (by counting them all). – Tassos Bassoukos Jun 18 '14 at 16:14
It's ok ... changing the "count" to "size" allows it to work. Thanks again for hte help. – Dave Jun 18 '14 at 18:07

score 0 · Answer 2 · answered Feb 25 '20 at 05:20

How about creating a procedure in SQL and calling the same using JPA? I created the following for calling login procedure I created.

    @Query(nativeQuery = true,value = "{call Login_User(:username,:pass)}")  // call stored procedure 
    int loginUser(@Param("username")String username,@Param("pass")String pass);

And the SQL Procedure I had created was as following:

DELIMITER //
CREATE PROCEDURE Login_User(IN username CHAR(12), IN pass text(255))
BEGIN
    DECLARE password text(18);
    SELECT User_Password INTO password  FROM user WHERE User_ID = username;
    select if(STRCMP(pass,password)= 0,1,0) as str_compare;
END//
DELIMITER ;

Hope this helps. Cheers! I'm open to know more about the same. I know mine is more like a workaround rather than the solution :)

How do I write a JPA criteria query that matches a collection exactly?

2 Answers2

Linked