Hql Query to Check If Size of Collection Is 0 or Empty

HQL Query to check if size of collection is 0 or empty

Using IS EMPTY should work (I would favor a JPQL syntax):

SELECT u FROM User u WHERE u.status = 1 AND u.appointments IS EMPTY

If it doesn't, please show the generated SQL.

References

• Hibernate Core Reference Guide
  
  
  • 14.10. Expressions
• JPA 1.0 specification
  
  
  • Section 4.6.11 "Empty Collection Comparison Expressions"

How to check if a collection parameter is null in hql?

:titles is a list.

You want to search books, which have theses "titles".

The user,

• Might have select one title
• Might have selected multiple titles
• Or might have selected no titles at all

So this list might be null, might have one or more elements in it.

In any case, you will use Query.setParameterList(), in order to pass the titles collection into the query, as described in this answer.

Now then, you wouldn't want to use a set method, if the parameter you are trying to pass could be null. After all it's Java that we are talking on here.

So, what you need, is to check if this list is null or not.

Also, you do not want to have hibernate check if the list of titles, that the user has selected, is null.

You also need to have one query only, there is no need for multiple queries.

The way to do this is by using a query builder.

There are many ways to implement this methodology. But in general the idea is that you

• Either use a framework, specialized for these kind of jobs, like Querydsl, check here

• Or you simple use a StringBuilder to build the select, from and where clauses of your query, for example:

  Map<String,Object> params = new HashMap<String,Object>();
  StringBuilder queryBuilder = new StringBuilder();
  queryBuilder.append(" from Book book ");
  if(!titlesList.isEmpty()){
      queryBuilder.append(" where book.title in (:titles) ");
      params.put("titles", titlesList);
  }
  Query query = entityManager.createQuery(queryBuilder.toString());
  for ( Map.Entry<String,Object>; param : params.entrySet()) {
      if(param instanceof Collection<?>){
          query.setParameterList(param.getKey(),param.getValue());
      }
      //if param is of type String then query.setString etc. 
      //else setParameter, you get the idea, use the docs
  }
  List<Book> results = (List<Book>) query.list();
  

How to check if the collection is empty in NHibernate (HQL)?

I think this syntax works here:

from stack s where exists elements(s.category)

Unable to use size function in HQL

Hibernate 6.0 is not stable now. The size expression perfectly works in the latest stable hibernate 5.4 release (5.4.24.Final).

The following hql:

List<User> users = em.createQuery(
   "select u from User u where size(u.messages) > 1",
   User.class).getResultList();

will generate the following sql:

select
   user0_.usr_id as usr_id1_1_,
   user0_.usr_name as usr_name2_1_ 
from TEST_SCHEMA.TST3_USERS user0_ 
where (
   select count(messages1_.msg_user_id) 
   from TEST_SCHEMA.TST3_MESSAGE messages1_ 
   where user0_.usr_id = messages1_.msg_user_id
) > 1

P.S. I would suggest you to use jpa 2.0 typed query:

TypedQuery<T> createQuery(String qlString, Class<T> resultClass)

instead of old-fashioned

Query createQuery(String qlString)

---

Related Topics