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.
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);
}
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