Spring JPA repoistory findBy IN List - allow null

Question

Short Description

How do I make findBy<Field>In work with IN when the array list input is null. e.g. ignore it. What would your DAO for this look like?

Longer description.

Imagine you have creating a search for users page.

in the application. You have various options to filter on.

• created (date range always given)
• Country (when null ignore and search all countries)
• AgeRange
• Job Title
• etc...

Now say you want to search for all users in a given date range in a list of countries.

When searching for users I will always search for a date joined however if I have not selected a country I want it to search for all countries.

I am planning on adding several more filter options other than country. So I don't really want to create lots of findBy methods for each possible field combination.

DAO

@Repository
public interface UserDao extends JpaRepository<User, Long> {

    public List<BeatRate> findByCreatedBetweenAndCountryIn(Date from, Date to, ArrayList<String> countryList );

}

Test

@Test
public void test() throws ParseException {

    Date from = new SimpleDateFormat( "yyyy-MM-dd" ).parse( "2015-01-01" );
    Date to   = new SimpleDateFormat("yyyy-MM-dd").parse("2015-05-15");

    //ArrayList<String> countryList = new ArrayList<String>();
    //countryList.add("UK");
    //countryList.add("Australia");
    //countryList.add("Japan");   // works ok when I have a list

    countryList = null;  // I want it to search for all countries when this is null -- this errors and doesnt work..  

    List<BeatRate> beatRates = beatRateDao.findByCreatedBetweenAndRentalCountryIn(from, to, countryList);

    Assert.assertTrue(beatRates.size()>0);

}

Answer 1

You can have two methods:

beatRateDao.findByCreatedBetweenAndRentalCountryIn(from, to, countryList);

and

beatRateDao.findByCreatedBetweenAndRental(from, to);

Then simply pick one based on countryList:

List<BeatRate> beatRates = (countryList != null && !countryList.isEmpty())
    ?  beatRateDao.findByCreatedBetweenAndRentalCountryIn(from, to, countryList)
    : beatRateDao.findByCreatedBetweenAndRental(from, to);

The IN clause requires a non-nullable and non empty argument list as otherwise the query will fail.

On PostgreSQL, if you try to run a query like this:

select * 
from product 
where quantity in ( )

you get the following error:

ERROR:  syntax error at or near ")"
LINE 3: where quantity in ( )
                            ^
********** Error **********

ERROR: syntax error at or near ")"
SQL state: 42601
Character: 45