JPA @Query with Complex nullable object

Question

I'm building a repository with a getAll query that uses a complex type parameter to filter, like:

@Query("""
    SELECT c FROM City c
    WHERE :filter IS NULL OR c.code LIKE %:#{#filter.code}%
    """)
fun getAllCitiesFiltered(@Param("filter") filter: MyFilter?) : List<City>

The MyFilter class is a simple POJO:

class MyFilter {
    var code: String? = null
    var description: String? = null
}

At some point in my code I call getAllCitiesFiltered(filter) and this filter may be null or may have one of it's properties set to null. I have two questions:

How can I deal with the fact that filter is nullable? The way it is now (like above), whenever a null value is passed to it I get an exception EL1007E: Property or field 'code' cannot be found on null
Is there a less ugly way in HQL to deal with the fact the the properties code and description may be null and when they are null I don't want to filter by them? The only way I think of now is doing things like ... WHERE filter.code IS NULL or filter.code LIKE %c.code%

I'm new to JPA and I'm not sure if using @Query is the best approach here. I'm open to suggestions to change that as well.

Thanks!

EDIT after Alan Hay suggestion

I'm using QueryDSL and, coming from C#/LINQ background, finding it great. The "problem" is that I'm doing things like this:

val city = QCity.city
var query = JPAQuery<City>(entityManager).from(city)

if (filter?.code != null)
    query = query.where(city.code.eq("BH"))

if (filter?.description != null)
    query = query.where(city.description.eq("Belo Horizonte"))

Is there a better way of writing this other than switching and if/else'ing?

Thanks!

You could ditch your filter class and use the inbuilt support for querying using dynamic criteria. See: https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/. Example: https://stackoverflow.com/a/26450224/1356423 — Alan Hay, Oct 17 '18 at 15:09
Thanks much for the answer, Alan. Really digging into QueryDSL. Can you check my edit on the question please? — João Menighin, Oct 17 '18 at 20:58
You don't need to be using the EntityManager directly. If this is a web app then see here on how you get the whole thing working with almost zero code: https://stackoverflow.com/a/46971053/1356423 — Alan Hay, Oct 18 '18 at 07:32

score 0 · Answer 1 · answered Oct 18 '18 at 12:01

The best way is to use interface default method :

interface CityJpaRepository : JpaRepository<City, Int> {
  fun findByCodeLike(code: String) : List<City>

  fun findByFilter(filter: MyFilter?) : List<City> {
    return filter?.code?.let { findByCodeLike(it.code) } ?: findAll()
  }
}

JPA @Query with Complex nullable object

1 Answers1