Spring JPA repoistory findBy IN List - 允许null [英] Spring JPA repoistory findBy IN List - allow null
问题描述
IN
。例如忽略它。你的DAO会是什么样子? 更长的描述。 p
想象一下您已创建搜索用户页面。
在应用程序中。你有不同的选择来过滤。 现在说你想在国家列表中搜索给定日期范围内的所有用户。 当搜索对于用户,我会一直搜索加入的日期,但是如果我没有选择我希望它搜索所有国家/地区的国家/地区的话。 我打算再增加几个过滤器国家以外的选择。所以我不想为每种可能的字段组合创建大量的findBy方法。 DAO
ul>
@Repository
public interface UserDao扩展JpaRepository< User,Long> {
公开列表< BeatRate> findByCreatedBetweenAndCountryIn(Date from,Date to,ArrayList< String> countryList);
测试
@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); //工作正常,当我有一个列表
countryList = null; //我希望当它为空时搜索所有国家 - 这个错误并且不起作用。
List< BeatRate> beatRates = beatRateDao.findByCreatedBetweenAndRentalCountryIn(from,to,countryList);
Assert.assertTrue(beatRates.size()> 0);
有两种方法:
beatRateDao.findByCreatedBetweenAndRentalCountryIn(from,to,countryList);
和
beatRateDao.findByCreatedBetweenAndRental(from,to);
然后根据 countryList
选择一个:
列表< BeatRate> beatRates =(countryList!= null&&!countryList.isEmpty())
? beatRateDao.findByCreatedBetweenAndRentalCountryIn(from,to,countryList)
:beatRateDao.findByCreatedBetweenAndRental(from,to);
IN子句需要一个非空和非空的参数列表,否则查询将失败。 / p>
在PostgreSQL上,如果您尝试运行如下查询:
select *
from产品
其中数量为()
以下错误:
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $
^
**********错误**********
错误:语法错误处于或接近)
SQL状态:42601
字符:45
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);
}
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
这篇关于Spring JPA repoistory findBy IN List - 允许null的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!