Writing a JPA query against a member field that's a Set

[Dave Alvarado]

Hi,

I'm using JPA 2.0 and Hibernate 4.1.0.Final. I'm curious how I would write a specific entity manager query. I have a domain object

@Entity
@Table(name = "sb_user",
    uniqueConstraints = {@UniqueConstraint(columnNames={"USER_NAME","URL"})}
)
@Cacheable
public class User implements Serializable
{
   
	…

    @OneToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "pre_user_contracts",
              joinColumns = @JoinColumn(name = "user_id"),
              inverseJoinColumns = @JoinColumn(name = "contract_id"))
    private Set<Contract> contracts;

and then the Contract domain object has

@Entity
@Table(name = "pre_contract",
    uniqueConstraints = {@UniqueConstraint(columnNames={"OPPORTUNITY_ID","PRODUCT_ID"})}
)
@Cacheable
public class Contract {
	…
    
    @OneToOne(fetch = FetchType.EAGER, targetEntity = Product.class)
    @JoinColumn(name = "PRODUCT_ID")
    @NotNull
    private Product product;

Given a java.util.List of Products, how do I construct a query asking for Users who have at least one contract tied to a product in the given list?

Thanks, - Dave

[Emanuel Kadziela]

You say you have a List of Products already, so I assume you already queried the database for this, correct?

Now, is your question about how to find the users who have contracts related to the products in this list? Or do you want to write the full query which queries the database for a list of users who have contracts related to products in the list and pass the whole list as a query parameter?

[Dave Alvarado]

> Now, is your question about how to find the users who have contracts related to the products in this list?

Yes, that's what I want. I think I'm close. I have ...

        final CriteriaBuilder builder = m_entityManager.getCriteriaBuilder();
        final CriteriaQuery<User> criteria = builder.createQuery(User.class);
        final Root<User> user = criteria.from(User.class);
        ...
        criteria.where(user.get("contracts").in(products));

but I can't figure out how to write the last line. The above dodsn't work because "contracts" are different objects than "products".

[Emanuel Kadziela]

I believe you need to use a Restriction. I copied the following from hibernate docs:

List cats = sess.createCriteria(Cat.class)
    .add( Restrictions.in( "name", new String[] { "Fritz", "Izi", "Pk" } ) )
    .add( Restrictions.disjunction()
        .add( Restrictions.isNull("age") )
        .add( Restrictions.eq("age", new Integer(0) ) )
        .add( Restrictions.eq("age", new Integer(1) ) )
        .add( Restrictions.eq("age", new Integer(2) ) )
    ) )
    .list();

So in your case it should look like this:

...
criteria.add(Restrictions.in("contracts",products));

[Dave Alvarado]

Thanks but there are a couple of problems with that approach. In your clause, you are asking for products in contracts, but those two lists contain different objects. A contract has a product, and I'd like to know of the given list of products, what contracts contain a product that match.

Also, I'm trying to use the more generic JPA API, using entityManager, and so I'm trying to eliminate any Hibernate-specific APIs.