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.

Answer 1

For starters, I don't think Hibernate can map a List<String>. However, it can map a list of other entities.

So if your code was something like this:

@Entity
public class Foobar {

    private List<EntityObject> uuids;
    ...
}

And the EntityObject has a String-property called str, the criteria could look like this:

List<Foobar> returns = (List<Foobar>) session
                .createCriteria.(Foobar.class, "foobars")
                .createAlias("foobars.uuids", "uuids")
                  .add(Restrictions.like("uuids.str", "%abc123%"))
                .list();

Answer 2

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();

Answer 3

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)) ;

Answer 4

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.

Answer 5

How do you extend the above example for the following mapping:

@Entity
public class Foobar implements Serializable {

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

    @ElementCollection
    private List<Tag> tags;

    // Getters/Setters, serialVersionUID, ...

}

@Entity
public class Tag implements Serializable {

   String name;
   String value;

   // Getters/Setters, serialVersionUID, ...

}

I need to fetch all Foobar pojos whose tag list contains the tag name = category and tag value "sample"?

In short, how do you access a collection of Types other than String?