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.

Silverman

Answer 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.

Diplocardiac

Tryed with hibernate 3.6.7-Final and does not work (it seems to be a bug in hibernate). – Lemma

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

Batman

You can also use instead of "elements" the constant CollectionPropertyNames.COLLECTION_ELEMENTS. – Skyrocket

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

Quadrangular

Answer 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.

Benitabenites

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

Jakoba

Answer 6

The solution with the sqlRestriction from jira http://opensource.atlassian.com/projects/hibernate/browse/HHH-869 seemed the best way to go for me since i heavily use criteria api. I had to edit Thierry's code so it worked in my case

Model:

@Entity
public class PlatformData
{
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private long iID;

  private List<String> iPlatformAbilities = new ArrayList<String>();
}

Criteria call:

tCriteria.add(Restrictions.sqlRestriction(
        "{alias}.id in (select e.id from platformData e, platformdata_platformabilities v"
          + " where e.id = v.platformdata_id and v.element = ? )", aPlatformAbility.toString(),
        Hibernate.STRING));

Rationalism

Answer 7

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

Aspire

There's no problem in using a list of strings, just annotate it with @CollectionOfElements. Your solution requires me to make an extra entity class, something that I was hoping to avoid with this very specific question. – Silverman