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 <>?

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”