JPA CriteriaQuery including 2 Subqueries

Question

I am having problems building a working complex CriteriaQuery with JPA including two subquerys.

In SQL the query would look like this:

SELECT * FROM photos p WHERE ((p.userID = 1) AND ((p.privacy = 0) 
OR (p.privacy = 1 AND EXISTS (SELECT * FROM friendships fs WHERE ((fs.userID = p.userID AND fs.userID2 = 2) OR (fs.userID = 2 AND fs.userID2 = p.userID))))
OR (p.privacy = 2 AND EXISTS (SELECT * FROM photo_privacy_users ppu WHERE (ppu.photoID = p.photoID AND ppu.userID = 2)))
));

To clarify the sql query:
photos.privacy = 0 means PrivacyType.PUBLIC
photos.privacy = 1 means PrivacyType.PRIVATE
photos.privacy = 2 means PrivacyType.COSTUM

Currently I am getting the following errors:
org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Operand should contain 1 column(s) org.hibernate.exception.DataException: could not extract ResultSet

The following code generates the query:

CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaQuery<Photo> cq = cb.createQuery(Photo.class);
Root<Photo> photo = cq.from(Photo.class);
cq.select(photo);

Subquery<Friendship> sq1 = cq.subquery(Friendship.class);
Root<Friendship> friendship = sq1.from(Friendship.class);
sq1.select(friendship);
sq1.where(cb.or(cb.and(cb.equal(friendship.get(Friendship_.userID), photo.get(Photo_.userID)), cb.equal(friendship.get(Friendship_.userID2), loggedinUserID)), cb.and(cb.equal(friendship.get(Friendship_.userID), loggedinUserID), cb.equal(friendship.get(Friendship_.userID2), photo.get(Photo_.userID)))));

Subquery<PhotoPrivacyUser> sq2 = cq.subquery(PhotoPrivacyUser.class);
Root<PhotoPrivacyUser> privacyUser = sq2.from(PhotoPrivacyUser.class);
sq2.select(privacyUser);
sq2.where(cb.and(cb.equal(privacyUser.get(PhotoPrivacyUser_.photoID), photo.get(Photo_.photoID)), cb.equal(privacyUser.get(PhotoPrivacyUser_.userID), loggedinUserID)));

Predicate predicate1 = cb.equal(photo.get(Photo_.userID), userID);
Predicate predicate2 = cb.equal(photo.get(Photo_.privacy), PrivacyType.PUBLIC);
Predicate predicate3 = cb.and(cb.equal(photo.get(Photo_.privacy), PrivacyType.PRIVATE), cb.exists(sq1));
Predicate predicate4 = cb.and(cb.equal(photo.get(Photo_.privacy), PrivacyType.COSTUM), cb.exists(sq2));

cq.where(cb.and(predicate1, cb.or(predicate2, predicate3, predicate4)));
return em.createQuery(cq).getResultList();

The variables userID and loggedInUserID are method parameters of the type Long.

Metamodel Photo_:

@StaticMetamodel(Photo.class)
public abstract class Photo_ {
    public static volatile SingularAttribute<Photo, Long> photoID;
    public static volatile SingularAttribute<Photo, PrivacyType> privacy;
    public static volatile ListAttribute<Photo, PhotoPrivacyUser> photoPrivacyUsers;
    public static volatile SingularAttribute<Photo, String> name;
    public static volatile SingularAttribute<Photo, User> user;

}

Metamodel Friendship_:

@StaticMetamodel(Friendship.class)
public abstract class Friendship_ {

    public static volatile SingularAttribute<Friendship, User> user2;
    public static volatile SingularAttribute<Friendship, Date> createdAt;
    public static volatile SingularAttribute<Friendship, Long> userID2;
    public static volatile SingularAttribute<Friendship, Long> userID;
    public static volatile SingularAttribute<Friendship, User> user;

}

Metamodel PhotoPrivacyUser_:

@StaticMetamodel(PhotoPrivacyUser.class)
public abstract class PhotoPrivacyUser_ {

    public static volatile SingularAttribute<PhotoPrivacyUser, Date> createdAt;
    public static volatile SingularAttribute<PhotoPrivacyUser, Long> photoID;
    public static volatile SingularAttribute<PhotoPrivacyUser, Photo> photo;
    public static volatile SingularAttribute<PhotoPrivacyUser, Long> userID;
    public static volatile SingularAttribute<PhotoPrivacyUser, User> user;

}

Metamodel User_:

@StaticMetamodel(User.class)
public abstract class User_ {
    public static volatile SingularAttribute<User, Long> userID;
    public static volatile ListAttribute<User, Photo> photos;
    public static volatile ListAttribute<User, Friendship> friends;
    public static volatile SingularAttribute<User, Date> createdAt;
    public static volatile ListAttribute<User, PhotoPrivacyUser> photoPrivacyUsers;
    public static volatile SingularAttribute<User, String> name;
    public static volatile SingularAttribute<User, String> email;
    public static volatile SingularAttribute<User, String> username;

}

My query works fine if I remove predicate3 and predicate4 from the query. I am not sure if I should use subquerys or maybe instead joins for that.

jpschack · Accepted Answer · 2015-09-23T16:55:52.387

I solved my issue.

As I suspected the right way to do this is with joins.

Using subquerys with exists is not working as suspected, because exists returns more than true or false. If exists is true, the result can be multiple rows.

The following code generates the query with three joins:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();

CriteriaQuery<Photo> cq = cb.createQuery(Photo.class);
Root<Photo> photo = cq.from(Photo.class);

Join<Photo, User> userJoin = photo.join(Photo_.user, JoinType.LEFT);
Join<Photo, PhotoPrivacyUser> ppuJoin = photo.join(Photo_.photoPrivacyUsers, JoinType.LEFT);
Join<User, Friendship> fsJoin = userJoin.join(User_.friends, JoinType.LEFT);

cq.select(photo);

Predicate predicateUserID = cb.equal(photo.get(Photo_.userID), userID);
Predicate predicatePublic = cb.equal(photo.get(Photo_.privacy), PrivacyType.PUBLIC);

Predicate predicateFriendship = cb.or(cb.and(cb.equal(fsJoin.get(Friendship_.userID), photo.get(Photo_.userID)), cb.equal(fsJoin.get(Friendship_.userID2), loggedinUserID)), cb.equal(fsJoin.get(Friendship_.userID), loggedinUserID), cb.equal(fsJoin.get(Friendship_.userID2), photo.get(Photo_.userID)));
Predicate predicatePrivate = cb.and(cb.equal(photo.get(Photo_.privacy), PrivacyType.PRIVATE), predicateFriendship);

Predicate predicatePpu = cb.and(cb.equal(ppuJoin.get(PhotoPrivacyUser_.photoID), photo.get(Photo_.photoID)), cb.equal(ppuJoin.get(PhotoPrivacyUser_.userID), loggedinUserID));
Predicate predicateCostum = cb.and(cb.equal(photo.get(Photo_.privacy), PrivacyType.COSTUM), predicatePpu);

cq.where(cb.and(predicateUserID, cb.or(predicatePublic, predicatePrivate, predicateCostum)));

return entityManager.createQuery(cq).getResultList();

Maybe this will help someone with the same problem or question how to do something like that. :)

JPA CriteriaQuery including 2 Subqueries

1 Answers1