Spring JPA repoistory findBy IN List-allow null

Question

简短说明

假设您正在创建一个搜索用户页面。

在应用程序中。您有多种选项可供筛选。

• 已创建（始终给定日期范围）
• 国家/地区（为空时忽略并搜索所有国家/地区）
• Agerange
• 职务
• 等..

刀

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

    public List<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");   // 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

您可以有两种方法：

beatRateDao.findByCreatedBetweenAndRentalCountryIn(from, to, countryList);

而且

beatRateDao.findByCreatedBetweenAndRental(from, to);

然后根据countrylist选择一个：

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

在PostgreSQL上，如果尝试运行如下所示的查询：

select * 
from product 
where quantity in ( )

则会出现以下错误：

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

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