Freddy Freddy - 1 year ago 448
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 LIKE %:name%) "
+ "AND (:country = '' OR 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
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 Source

Try using a proper like formating using concat

    "SELECT c FROM customer c WHERE "
      + "(:name = '' OR LIKE concat('%', :name ,'%'') "
      + "AND (:country = '' OR LIKE concat ('%', :country, '%'')"
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download