JPA NamedQuery with a null parameter

Question

I have the following NamedQuery:

@Entity
@NamedQuery(name = "query", query = "SELECT e FROM Entity e WHERE e.a = :a AND e.b = :b")
public class Entity {
    private String a;
    private String b;
    // Getters and Setters
}

I want set the parameter a to someString and b to null.

This is the method that use the NamedQuery:

@PersistenceContext(unitName = "persistence-unit")
private EntityManager manager;

public List<Entity> getEntities(String a, String b) {
    TypedQuery<Entity> query = manager.createNamedQuery("query", Entity.class);
    query.setParamenter("a", a);
    query.setParamenter("b", b);
    return query.getResultList();
}

But, when I call getEntities("someString", null); don't work.

I guess it's caused because the correct is e.b IS NULL and this method is setting e.b = NULL.

How can I make it works?

Answer 1

You could modify your example as follows to achieve kind of a valid workaround

@Entity
@NamedQueries ({
      @NamedQuery(name = "query", query = "SELECT e FROM Entity e WHERE e.a = :a AND e.b = :b"),
      @NamedQuery(name = "queryParamANull", query = "SELECT e FROM Entity e WHERE e.a IS NULL AND e.b = :b"),
      @NamedQuery(name = "queryParamBNull", query = "SELECT e FROM Entity e WHERE e.a = :a AND e.b IS NULL")
})
public class Entity {
    private String a;
    private String b;
    // Getters and Setters
}

Thereby, you could query the data as follows (Note: simplified example with the assumption that only either a OR b can be null per method call/query):

@PersistenceContext(unitName = "persistence-unit")
private EntityManager manager;

public List<Entity> getEntities(String a, String b) {
    TypedQuery<Entity> query;
    if(a==null) // we got no value for a
    {
        query = manager.createNamedQuery("queryParamANull", Entity.class);
        query.setParamenter("b", b);
    }
    else if(b==null) // we got no value for b
    {
        query = manager.createNamedQuery("queryParamBNull", Entity.class);
        query.setParamenter("a", a);
    }
    else // default case
    {
        query = manager.createNamedQuery("query", Entity.class);
        query.setParamenter("a", a);
        query.setParamenter("b", b);
    }
    return query.getResultList();
}