Passing empty list as parameter to JPA query throws error

If I pass an empty list into a JPA query, I get an error.

For example:

List<Municipality> municipalities = myDao.findAll();  // returns empty list
em.createQuery("SELECT p FROM Profile p JOIN p.municipality m WHERE m IN (:municipalities)")
    .setParameter("municipalities", municipalities)
    .getResultList();

Because the list is empty, Hibernate generates this in SQL as "IN ()", which gives me error with Hypersonic database.

There is a ticket for this in Hibernate issue tracking but there are not many comments/activity there. I 开发者_如何学JAVAdon't know about support in other ORM products or in JPA spec either.

I don't like the idea of having to manually check for null objects and empty lists every time. Is there some commonly known approach/extension to this? How do you handle these situations?

---

According to the section 4.6.8 In Expressions from the JPA 1.0 specification:

There must be at least one element in the comma separated list that defines the set of values for the IN expression.

In other words, regardless of Hibernate's ability to parse the query and to pass an IN(), regardless of the support of this syntax by particular databases (PosgreSQL doesn't according to the Jira issue), you should use a dynamic query here if you want your code to be portable (and I usually prefer to use the Criteria API for dynamic queries).

---

Assuming the SQL query to be like

(COALESCE(:placeHolderName,NULL) IS NULL OR Column_Name in (:placeHolderName))

Now, If the List is of type String then you can pass as

query.setParameterList("placeHolderName", 
!CollectionUtils.isEmpty(list)? list : new ArrayList<String>(Arrays.asList("")).

And If the List is containing the Integer values then the syntax is like below:

If(!CollectionUtils.isEmpty(list)){
query.setParameterList("placeHolderName",list)
}else{
query.setParameter("placeHolderName",null, Hibernate.INTEGER)
}

---

I struggled with this issue, too. I found out that the Hibernate community has RESOLVED the issue in Hibernate version 5.4.10, here is the ticket: https://hibernate.atlassian.net/browse/HHH-8091

You can check your Hibernate version System.out.println(org.hibernate.Version.getVersionString());

And you can UPDATE the version of Hibernate to the latest, here is a useful link: https://hibernate.org/orm/releases/

---

Solution:

if (municipalities==null || municipalities.isEmpty())
    .setParameter("municipalities", "''")
else
    .setParameter("municipalities", municipalities)

---

My simple solution was to add an aux parameter to flag the condition like in the below example:

where (:doNotCheckList = 1)
   or (fieldName in :valuesList)

query
  .setParameter("doNotCheckList", (!valuesList.isEmpty() ? 1 : 0))
  .setParameter("valuesList",     valuesList);

---

I ran into the same problem today and @r_divyas's response almost did it for me, but there was a small change I had to do in order to make it work.

I have a query that I save in the database so I couldn't change it on the fly. This query triggers the build of a dynamic UI according to the fields in the result set.

In the UI I have a multiple choice dropdown, which is optional. So there can be 0, 1 or more elements selected from it.

Therefore, I needed to find a way to make this parameter optional in my query. Initially, I set it up as:

(:assignedUsers IS NULL OR wr.USR_ID IN (:assignedUsers))

Unfortunately, this worked with only a null parameter or a list of a single element. At the point of design, I didn't test it with multiple elements in the list. When I finally got there, I encountered the following error:

SQL Error [4145] [S0001]: An expression of non-boolean type specified in a context where a condition is expected, near ','.

Which was caused by the following code:

AND (('id1', 'id2', 'id3') IS NULL OR wr.USR_ID IN ('id1', 'id2', 'id3'))

I then tried his solution, which was indeed working when my list was not null or empty. But when it was, the query was becoming this:

AND (COALESCE('', NULL) IS NULL OR wr.USR_ID IN (''))

Which was causing the result set to be empty.

And now, proceeding to the solution I found. All I had to do was to change the condition in the query:

AND (COALESCE(:assignedUsers, NULL) = '' OR wr.USR_ID IN (:assignedUsers))

And this is working for an empty list, a null value, a list with a single or more elements.

---

After having no actual solution as replies, I created a proxy class to handle these situations. The idea is to retain native syntax where possible.

WARNING: This is work-in-progress and very hazardous approach. The code below is by no mean meant as complete solution and quite possibly contains zillions of bugs and scary cases.

That being said, the BlankAwareQuery class wraps the javax.persistence Query and is initialized with EntityManager and the core query string (which cannot contain empty lists or lists of enumerations).

BlankAwareQuery query = new BlankAwareQuery(em, "SELECT p FROM Profile p");

After creation of class, dynamic parts are inserted with

query.from("p.address a");
query.where("a IN (:addresses)");

Parameters are inserted as always:

query.setParameter("addresses", addresses);

The point here is that the class removes these (their from-part as well) from query if they are empty lists or manipulates them if they are lists of enumerations.

Then call:

query.getResultList();

So, for example:

List<Profile> profiles = new BlankAwareQuery(em, "SELECT p FROM Profile p")
    .from("p.address a JOIN a.municipality m").where("m IN (:municipalities)")
    .where("p.gender IN (:genders)")
    .where("p.yearOfBirth > :minYear")
    .where("p.yearOfBirth < :maxYear")
    .from("p.platforms f").where("f IN (:platforms)")
    .setParameter("municipalities", municipalities)
    .setParameter("genders", genders)
    .setParameter("minYear", minYear)
    .setParameter("maxYear", maxYear)
    .setParameter("platforms", platforms)
    .getResultList();

The actual code (code uses Lombok for @Data and @NonNull annotations and Apache commons lang for StringUtils):

public class BlankAwareQuery {

    private @Data class Parameter {
        private @NonNull String fieldName;
        private @NonNull Object value;
    }

    private @Data class ClausePair {
        private @NonNull String from;
        private @NonNull String where;
    }

    private EntityManager em;

    private List<String> select = Lists.newArrayList();
    private List<ClausePair> whereFrom = Lists.newArrayList();
    private String from;
    private List<Parameter> parameters = Lists.newArrayList();
    Query query;

    public BlankAwareQuery(EntityManager em, String query) {

        this.em = em;

        /** Select **/
        int selectStart = StringUtils.indexOf(query, "SELECT ") + 7;
        int selectEnd = StringUtils.indexOf(query, " FROM ");
        select(StringUtils.substring(query, selectStart, selectEnd));

        /** From **/
        int fromStart = selectEnd + 6;
        int fromEnd = StringUtils.indexOf(query, " WHERE ");
        if (fromEnd == -1) fromEnd = query.length();
        from(StringUtils.substring(query, fromStart, fromEnd));

        /** Where **/
        String where = "";
        if (StringUtils.contains(query, " WHERE ")) {
            where = StringUtils.substring(query, fromEnd + 7);
        }
        where(where);
    }

    private BlankAwareQuery select(String s) {
        select.add(s);
        return this;
    }

    public BlankAwareQuery from(String s) {
        from = s;
        return this;
    }

    public BlankAwareQuery where(String s) {
        ClausePair p = new ClausePair(from, s);
        whereFrom.add(p);
        from = "";
        return this;
    }

    public BlankAwareQuery setParameter(String fieldName, Object value) {

        /** Non-empty collection -> include **/
        if (value != null && value instanceof List<?> && !((List<?>) value).isEmpty()) {

            /** List of enums -> parse open (JPA doesn't support defining list of enums as in (:blaa) **/
            if (((List<?>) value).get(0) instanceof Enum<?>) {

                List<String> fields = Lists.newArrayList();

                /** Split parameters into individual entries **/
                int i = 0;
                for (Enum<?> g : (List<Enum<?>>) value) {
                    String fieldSingular = StringUtils.substring(fieldName, 0, fieldName.length() - 1) + i;
                    fields.add(":" + fieldSingular);
                    parameters.add(new Parameter(fieldSingular, g));
                    i++;
                }

                /** Split :enums into (:enum1, :enum2, :enum3) strings **/
                for (ClausePair p : whereFrom) {
                    if (p.getWhere().contains(":" + fieldName)) {
                        int start = StringUtils.indexOf(p.getWhere(), ":" + fieldName);
                        int end = StringUtils.indexOfAny(StringUtils.substring(p.getWhere(), start + 1), new char[] {')', ' '});
                        String newWhere = StringUtils.substring(p.getWhere(), 0, start) + StringUtils.join(fields, ", ") + StringUtils.substring(p.getWhere(), end + start + 1);
                        p.setWhere(newWhere);
                    }
                }
            }
            /** Normal type which doesn't require customization, just add it **/ 
            else {
                parameters.add(new Parameter(fieldName, value));
            }
        }

        /** Not to be included -> remove from and where pair from query **/
        else {
            for (Iterator<ClausePair> it = whereFrom.iterator(); it.hasNext();) {
                ClausePair p = it.next();
                if (StringUtils.contains(p.getWhere(), fieldName)) {
                    it.remove();
                }
            }
        }

        return this;
    }

    private String buildQueryString() {

        List<String> from = Lists.newArrayList();
        List<String> where = Lists.newArrayList();

        for (ClausePair p : whereFrom) {
            if (!p.getFrom().equals("")) from.add(p.getFrom());
            if (!p.getWhere().equals("")) where.add(p.getWhere());
        }

        String selectQuery = StringUtils.join(select, ", ");
        String fromQuery = StringUtils.join(from, " JOIN ");
        String whereQuery = StringUtils.join(where, " AND ");

        String query = "SELECT " + selectQuery + " FROM " + fromQuery + (whereQuery == "" ? "" : " WHERE " + whereQuery);

        return query;
    }

    public Query getQuery() {
        query = em.createQuery(buildQueryString());
        setParameters();
        return query;
    }

    private void setParameters() {
        for (Parameter par : parameters) {
            query.setParameter(par.getFieldName(), par.getValue());
        }
    }

    public List getResultList() {
        return getQuery().getResultList();
    }

    public Object getSingleResult() {
        return getQuery().getSingleResult();
    }
}

---

Since you're querying DB sequence IDs that typically start at 1, you can add 0 to the list

if (excludeIds.isEmpty()) {
    excludeIds.add(new Long("0"));
}
List<SomeEntity> retval = someEntityRepo.findByIdNotIn(excludeIds);

Maybe -1 works too. Small work around for the use of jpa repos.

---

I use two parameters municipalitiesb and municipalities. municipalitiesb activate or desactivate the IN condition. In case municipalities is empty, I use a dummy Municipality.

//protection against NPE
if(municipalities == null) municipalities = Collections.emptyList();
em.createQuery("SELECT p FROM Profile p JOIN p.municipality m WHERE (:municipalitiesb IS NULL OR m IN (:municipalities))")
    .setParameter("municipalitiesb", municipalities.isEmpty() ? null : 1)
    .setParameter("municipalities", municipalities.isEmpty() ? Arrays.asList(new Municipality()), municipalities)
    .getResultList();

---

If you are using spring/hibernate annotations then one of the easiest ways of working around this is having two parameters. First lets define our JPA annotation:

        + "AND (p.myVarin :state OR :nullCheckMyVar is null) "

Then pass those parameters in as normal:

    @Param("myVarin") List<Integers> myVarin,
    @Param("nullCheckMyVar") Integer nullCheckMyVar,

Finally in what ever service call you can do the following:

    Integer nullCheckInteger = null;
    if (myVarIn != null && !myVarIn.isEmpty()) {
        // we can use any integer
        nullCheckInteger = 1;
    }
    repo.callService(myVarIn, nullCheckInteger);

And then pass those two parms into the call.

What does this do? If myVarIn is not null and filled then we can specify "all" as 1 != null. Otherwise we pass in our list and null is null and therefore ignored. This either selects "all" when empty or something when not empty.