Hibernate Criteria API - adding a criterion: string should be in collection

Question

I have to following entity object

@Entity
public class Foobar {
    ...
    private List<String> uuids;
    ...
}

Now I'd like to make a criteria query which would fetch all Foobar pojos whose uuids list contains the string "abc123", I'm just not sure how to make the appropriate criterion.

Solution 1

I assume you are using a version of Hibernate that implements JPA 2.0. Here's a JPA 2.0 solution that should work with any compliant implementation.

Please annotate uuids with JPA's @ElementCollection annotation. Don't use Hibernate's @CollectionOfElements as mentioned in some of the other answer comments. The latter has equivalent functionality but is being deprecated.

Foobar.java will look approximately like this:

@Entity
public class Foobar implements Serializable {

    // You might have some other id
    @Id
    private Long id;

    @ElementCollection
    private List<String> uuids;

    // Getters/Setters, serialVersionUID, ...

}

Here's how you can build a CriteriaQuery to select all Foobars whose uuids contain "abc123".

public void getFoobars() {
{
    EntityManager em = ... // EM by injection, EntityManagerFactory, whatever

    CriteriaBuilder b = em.getCriteriaBuilder();
    CriteriaQuery<Foobar> cq = b.createQuery(Foobar.class);
    Root<Foobar> foobar = cq.from(Foobar.class);

    TypedQuery<Foobar> q = em.createQuery(
            cq.select(foobar)
              .where(b.isMember("abc123", foobar.<List<String>>get("uuids"))));

    for (Foobar f : q.getResultList()) {
        // Do stuff with f, which will have "abc123" in uuids
    }
}

I made a self-contained proof-of-concept program while playing with this. I can't push it out right now. Please comment if you want the POC pushed to github.

Solution 2

I know this is old question, but I have just encountered this issue and found solution.

If you want to use Hibernate Criteria you can join your uuids collection and use its property elements to match elements. Just like that:

session.createCriteria(Foobar.class)
    .createAlias("uuids", "uuids")
    .add(Restrictions.eq("uuids.elements", "MyUUID"))
    .list() 

Solution 3

You could use a Query as in the example below or you could convert this to a NamedQuery. Unfortunately there doesn't seem to be a way to do this with Criteria.

List<Foobar> result = session
     .createQuery("from Foobar f join f.uuids u where u =: mytest")
     .setString("mytest", "acb123")
     .list();

Solution 4

I've found this post from one year ago, and I've made this method, if it can help anybody with the same problem I had a few hours ago.

    Public List<EntityObject> getHasString(String string) {
        return getSession().createCriteria(EntityObject.class)
                               .add(Restriction.like("property-name", string, MatchMode.ANYWHERE).ignoreCase();
                           .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
                           .list();

Made the same with a group of strings too.

    public List<EntityObject> getByStringList(String[] tab) {
        Criterion c = Restrictions.like("property-name", tab[tab.length-1], MatchMode.ANYWHERE).ignoreCase();
        if(tab.length > 1) {
            for(int i=tab.length-2; i >= 0 ; i--) {
                c = Restrictions.or(Restrictions.like("property-name",tab[i], MatchMode.ANYWHERE).ignoreCase(), c);
            }
        }
        return getSession().createCriteria(EntityObject.class)
                               .add(c)
                           .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
                           .list();
    }

It works with "or" statements, but can easily be replaced by "and" statements.

Solution 5

What you are asking is not supported out of the box by hibernate. See http://opensource.atlassian.com/projects/hibernate/browse/HHH-869

Here is a workaround available in the jira ticket :

entityCriteria.add(Restrictions.sqlRestriction(
  "fooAlias.id in (select e.id from foobar_table e, values_table v" + 
  " where e.id = v.entity_id and v.field = ?)", "abc123"), Hibernate.String)) ;

View more solutions