CODEWITHSUNDEEP
mysqlspring-data-jpaspring-datajpql
Freddy
Freddy

Reputation: 148

JpaRepository with optional parameters

I have a MySQL table for customers like this:

name | country | many more columns
----------------------------------
John   USA
null   France
Max    null
null   null
...

In my application I want to query this table. The user can specify different search fields, but it is not necessary that every value is set. I assamble the query in the Repository Class and I want to ignore empty parameters.

Example, what results are expected with several search parameters:

Name      | Country       Result
--------------------------------
John      | (not set)     first table line
(not set) | (not set)     all entries
(not set) | a             line one and two (case insensitive)

I know I can create queries like:
findByNameAndByCountry( @Param("name") String name, @Param("country") String country);
but in the application I have seven search parameters. Because it isn't necessary to specify them all, there are 128 different searches, which I don't want to implement.

So my idea was to query the database like this:

public interface CustomerRepository extends JpaRepository<Customer, java.lang.String> {

   @Query("SELECT c FROM customer c WHERE "
          + "(:name = '' OR c.name LIKE %:name%) "
          + "AND (:country = '' OR c.country LIKE %:country%)")
   List<Customer> findByParams(@Param("name") name, @Param("country") country);

}

The problem is, this doesn't work. If I only check the parameter with LIKE I do not get the expected results because '%' != null. But I also want database entries with null values, if the search parameter is empty. I tried to check if the parameter is empty with :param = '' but for some reason this does not work.

I also tested with IF(:param != '', c.param, '%') LIKE %:param% for every parameter, sadly with the same result.

Strange thing is, if I try this command directly on my database it works just fine, but not with JpaRepository.

Has someone an idea why this doesn't work. Or is there a better solution for my problem, I didn't think of? Thanks for every help :)

Upvotes: 2

Views: 4662

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

Try using a proper like formating using concat 

    "SELECT c FROM customer c WHERE "
      + "(:name = '' OR c.name LIKE concat('%', :name ,'%'') "
      + "AND (:country = '' OR c.country LIKE concat ('%', :country, '%'')"

Upvotes: 2

Related Questions