Freddy Freddy - 5 months ago 118
MySQL Question

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 :)

Answer

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, '%'')"
Comments