JPQL LIKE expression with attributes of target entity in a null valued relationship

Question

I have an entity named Item with relationship to another entity Category which can be null. The two entities are as follows:

Item

@Entity
public class Item {
     @Id
     private String id;
     private String name;
     private String code;
     @ManyToOne
     private Category category;
}

Category

@Entity
public class Category {
     @Id
     private String id;
     private String name;
}

Now, I have to select items having name, code or category name similar to a search term, for which I tried the following query:

"SELECT item FROM Item item "
                + "WHERE item.code LIKE :searchTerm OR item.name LIKE :searchTerm "
                + "OR item.category.name LIKE :searchTerm"

searchTerm is set using

query.setParameter("searchTerm", "%" + searchTerm + "%");

But it doesn't result will be empty if category is null (otherwise works). I tried the following query also.

"SELECT item FROM Item item "
                + "WHERE item.code LIKE :searchTerm OR item.name LIKE :searchTerm "
                + "OR (item.category IS NOT NULL AND item.category.name LIKE :searchTerm)"

This also didn't work. How can I make it to check for category name only if category is not null?

score 2 · Accepted Answer · answered Mar 15 '13 at 08:36

Try to use a left join:

"SELECT item FROM Item item LEFT JOIN item.category cat"
            + "WHERE item.code LIKE :searchTerm OR item.name LIKE :searchTerm "
            + "OR (cat.categoryID IS NOT NULL AND cat.name LIKE :searchTerm)"

JPQL documentation

score 1 · Answer 2 · answered Mar 15 '13 at 07:41

1

Simply check null for the above parameterized value. The jpa does not return null pointer exception because we give the null only within the double quotes. So like java, it take null as a string and tries the value of '%null%' from your db. So that, it returns empty list.

answered Mar 15 '13 at 07:41

MGPJ

1,062
1
8
15

thanks for spending ur time. the parameter is not null. it's `item.category` which has null value so that causing unexpected result when comparing value of `item.category.name`. u have misunderstood the problem. the title i gave might be the reason. – Naveed S Mar 15 '13 at 08:27

JPQL LIKE expression with attributes of target entity in a null valued relationship

2 Answers2