How can I construct a SetJoin in JPA when there's no Set in my entity member field?

Question

I’m using JPA 2.0, Hibernate 4.1.0.Final, and MySQL 5.5.37. I have the following entities

@Entity
@Table(name = "user_subscription",
    uniqueConstraints = { @UniqueConstraint(columnNames = { "USER_ID", “SUBSCRIPTION_ID" }) }
)
public class UserSubscription
{

    @Id
    @Column(name = "ID")
    @GeneratedValue(generator = "uuid-strategy")
    private String id;

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

    @ManyToOne
    @JoinColumn(name = “SUBSCRIPTION_ID", nullable = false, updatable = true)
    private Subscription subscription;

and

@Entity
@Table(name = "Subscription")
public class Subscription implements Serializable 
{

    @Id
    @Column(name = "ID")
    @GeneratedValue(generator = "uuid-strategy")
    private String id;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "PRODUCT_ID")
    @NotNull
    private Product product;

Without altering the entities, how do I construct a JPA CriteriaBuilder query in which I look for User entities that do not have a particular Subscription entity “A”, but have other subscription entities that match the same product as entity “A”? I have tried this to no avail …

public List<User> findUsersWithSubscriptions(Subscription Subscription)
{
    final List<User> results = new ArrayList<User>();
    final CriteriaBuilder builder = m_entityManager.getCriteriaBuilder();
    final CriteriaQuery<UserSubscription> criteria = builder.createQuery(UserSubscription.class);
    final Root<UserSubscription> root = criteria.from(UserSubscription.class);
    
    Join<UserSubscription, Subscription> SubscriptionRoot = root.join(UserSubscription_.subscription);
    
    criteria.select(root).where(builder.equal(root.get(UserSubscription_.Subscription).get(Subscription_.product),subscription.getProduct()),
                                builder.notEqual(root.get(UserSubscription_.subscription), subscription));

I thought if I could build a SetJoin from the user -> subscription entities, I could say something like “not.in”, but I’m not sure how to do that given the constraints.

Edit: This is the SQL produced by Vlad's post:

SELECT user1_.id                       AS id97_,
       user1_.creator_id               AS CREATOR15_97_,
       user1_.dob                      AS DOB97_,
       user1_.enabled                  AS ENABLED97_,
       user1_.expiration               AS EXPIRATION97_,
       user1_.first_name               AS first5_97_,
       user1_.grade_id                 AS GRADE16_97_,
       user1_.incorrect_logins         AS INCORRECT6_97_,
       user1_.last_name                AS last7_97_,
       user1_.middle_name              AS middle8_97_,
       user1_.organization_id          AS organiz17_97_,
       user1_.password                 AS password97_,
       user1_.reset_state              AS RESET10_97_,
       user1_.salutation               AS salutation97_,
       user1_.temporary_password       AS temporary12_97_,
       user1_.url                      AS url97_,
       user1_.user_demographic_info_id AS USER18_97_,
       user1_.user_name                AS user14_97_
FROM   sb_user_subscription subscription0_
       INNER JOIN sb_user user1_
               ON subscription0_.user_id = user1_.id
       INNER JOIN cb_subscription subscription2_
               ON subscription0_.subscription_id = subscription2_.id
       INNER JOIN sb_product product3_
               ON subscription2_.product_id = product3_.id
                  AND product3_.id = ?
                  AND subscription2_.id <>?

Pameliapamelina

So in JPQL, would something like "SELECT user FROM User user where exists(select userSub from UserSubscription userSub where userSub.user = user and userSub.subscription.product = :product) and not exists (select userSub from UserSubscription userSub where userSub.user = user and userSub.subscription = :subscription)" work? This could be simplified if Product and User have a 1:m references to Subscription and UserSubscription respectively – Knish

Answer 1

Check this query:

final CriteriaBuilder builder = m_entityManager.getCriteriaBuilder();
final CriteriaQuery<User> criteria = builder.createQuery(User.class);
final Root<UserSubscription> root = criteria.from(UserSubscription.class);

Join<UserSubscription, User> userJoin = root.join(UserSubscription_.user);
Join<UserSubscription, Subscription> subscriptionJoin = root.join(UserSubscription_.subscription);
Join<Subscription, Product> productJoin = subscriptionJoin.join(Subscription_.product);

criteria
    .select(userJoin)
    .where(cb.and(
         builder.equal(productJoin, subscription.getProduct()),
         builder.notEqual(subscriptionJoin, subscription)
);
return entityManager.createQuery(criteria).getResultList();

The output query looks fine and it should select Users with a given subscription.product and with a different subscription than the product parent's one.

You could try it in your SQL console, but it looks fine and it validates the initial requirement:

that do not have a particular Subscription entity “A”, but have other subscription entities that match the same product as entity “A”

Aspinwall

Hi, THis line "Join<UserSubscription, Subscription> productJoin = subscriptionJoin.join(Subscription_.product);" doesn't compile. Also I want to return User entities (as opposed to UserSubscription entities). – Pameliapamelina

true that, check my updated query, it should select Users and I revised the joins. I did it in Notepad++ so no compile check for me :) – Aspinwall

Cool, thx for fixing that. So I ran the query but it fails in this case -- if there are two subscritions A and B in the system (with the same product) and you have a user tied who has both of those subscriptions, that user gets returned in the query if you pass in either "A" or "B" as the parameter. I want to return users that do not have the parameter (e.g. "A") at all, but rather have similar subscriptions. Hope this makes sense. – Pameliapamelina

I need you to take the actual SQL and post it here to figure out why is not working in your case, thanks. – Aspinwall

The SQL was too much to put in comments but I added it as an Edit to my original question. – Pameliapamelina

Hi, I'm reading your edit, but the query does not solve the initial requirement 'do not have a particular Subscription entity “A”', because its returning Users who do have the subscription entity "A". – Pameliapamelina