Subquery on element collection

Hibernate ORM
1

I am trying to build a query on an entity which contains an elementcollection. I have reduced my query down to only the relevant parts to make it simple. I would like to find all documents that have an entry in the elementcollection that is “like” a search term. Below is my code:

CriteriaBuilder criteriaBuilder = session.getCriteriaBuilder();
CriteriaQuery<Document> criteriaQuery = criteriaBuilder.createQuery(Document.class);
Root<Document> root = criteriaQuery.from(Document.class);
criteriaQuery.select(root);
Predicate constraints = cb.conjunction();
constraints.getExpressions().add(cb.equal(root.get("deleted"), false));

Subquery sub = cq.subquery(Document.class);
Root subRoot = sub.from(Document.class);
sub.select(subRoot.get("id"));
sub.where(cb.like(subRoot.get(Document.DOCTAGS_PROPERTY), "%mysearchtoken%"));

constraints.getExpressions().add(root.get("id").in(sub));
Query<HesDocument> query = session.createQuery(criteriaQuery);
    List<Document> documents = query.getResultList();

It translates to the following query:

select documen0_.id as col_0_0_ 
from document documen0_ 
where documen0_.deleted=? and (documen0_.id in (
	    select documen1_.id 
    from document documen1_ cross join document_tag doctags2_ 
    where documen1_.id=doctags2_.doc_id and (. like ?)
))

WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - logExceptions: SQL Error: 0, SQLState: null
ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - logExceptions: The value is not set for the parameter number 2.

I do not understand how I am not setting a search parameter. Can anyone tell me what is wrong with my code?

2

I would recommend you to use an exists predicate instead like this:

CriteriaBuilder criteriaBuilder = session.getCriteriaBuilder();
CriteriaQuery<Document> criteriaQuery = criteriaBuilder.createQuery(Document.class);
Root<Document> root = criteriaQuery.from(Document.class);
criteriaQuery.select(root);
Predicate constraints = cb.conjunction();
constraints.getExpressions().add(cb.equal(root.get("deleted"), false));

Subquery sub = cq.subquery(Document.class);
Root subRoot = sub.correlate(Document.class);
sub.select(cb.literal(1));
sub.where(cb.like(subRoot.get(Document.DOCTAGS_PROPERTY), "%mysearchtoken%"));

constraints.getExpressions().add(cb.exists(sub));
Query<HesDocument> query = session.createQuery(criteriaQuery);
    List<Document> documents = query.getResultList();
3

Thanks for the suggestion. I gave it a try but it didn’t solve my problem. The error is coming from the line above the EXISTS:

sub.where(cb.like(subRoot.get(Document.DOCTAGS_PROPERTY), “%mysearchtoken%”));

If I remove this line my query runs without error. I do not understand how any parameter is not being bound. And is it the DOCTAGS_PROPERTY that is not being populated or the parameter for the like?

4

Which Hibernate version are you using?

There is a configuration property hibernate.criteria.literal_handling_mode in Hibernate that allows to configure if a JPA Criteria literals should be rendered as parameters or as literals. This might be the source of the problem. Try using the INLINE configuration to see if that fixes your issue. Also see the documentation for more information: https://docs.jboss.org/hibernate/stable/orm/userguide/html_single/Hibernate_User_Guide.html#configurations-query