Spring JPA repoistory findBy IN List - 允许null [英] Spring JPA repoistory findBy IN List - allow null

问题描述

在数组列表输入为空时使用 IN 。例如忽略它。你的DAO会是什么样子？

更长的描述。 p

想象一下您已创建搜索用户页面。

在应用程序中。你有不同的选择来过滤。

• 创建（日期范围始终为空）
  
• 国家（当null忽略并搜索所有国家/地区时）


• AgeRange


• 职位


• etc ...

ul>



现在说你想在国家列表中搜索给定日期范围内的所有用户。





当搜索对于用户，我会一直搜索加入的日期，但是如果我没有选择我希望它搜索所有国家/地区的国家/地区的话。





我打算再增加几个过滤器国家以外的选择。所以我不想为每种可能的字段组合创建大量的findBy方法。





DAO

  @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屋！