具有多个条件的JPA查找方法

Question

这里我试图用jpa从mysql数据库中获取数据，但是我被findby绊倒了，如何使用Crudepository spring数据jpa为下面的查询编写findby方法？

select t.id, t.MSISDN, t.Param1, t.param2
  from BULK_REPOSITORY t
where t.Camp_Start_Date between Sysdate - 2 and sysdate
   and t.status = 0
   and t.camp_type = 1;

我试过这个，但它不工作：

List<Bulk_repository> findByStatusInAndfindByCamp_typeIn(int status, int camp_type);

我有这样的实体类：

@Entity
@Table(name = "BULK_REPOSITORY")
public class Bulk_repository {

   @Id
   @GeneratedValue(strategy=GenerationType.AUTO)
   @Column(name = "id")
   private long id;

   @Column(name = "msisdn")
   private String msisdn;

   @Column(name = "camp_start_date")   
   private Date camp_start_date;

   @Column(name = "camp_end_date")
   private Date camp_end_date;

   @Column(name = "camp_type")
   private int camp_type;

   @Column(name = "camp_cd")
   private String camp_cd;

   @Column(name = "status")
   private int status;

   @Column(name = "process_date")
   private Date process_date;

   @Column(name = "entry_date")
   private Date entry_date;

   @Column(name = "entry_user")
   private String entry_user;

   @Column(name = "param1")
   private String param1;

   @Column(name = "param2")
   private String param2;

   @Column(name = "param3")
   private String param3;

   @Column(name = "param4")
   private String param4;

   @Column(name = "param5")
   private String param5;

   @Column(name = "error_desc")
   private String error_desc;

Answer 1

您可以将Spring数据JPA中的@Query与JPQL一起使用：

JPQL不支持句号。所以你必须自己处理：

java prettyprint-override">@Query("SELECT t from Bulk_repository t " + 
    "WHERE t.camp_start_date BETWEEN CURRENT_DATE AND :currentDateMinus2 " + 
    "AND t.status = :status " + 
    "AND t.camp_type = :campType")
List<Bulk_repository> findByStatusInAndfindByCamp_typeIn(
    @Param("status") int status, 
    @Param("campType") int camp_type, 
    @Param("currentDateMinus2") Date currentDateMinus2);

Answer 2

我建议将日期范围作为参数传递给方法。

 List< Bulk_repository> findByStatusIsAndCamp_typeIsAndCamp_start_dateBetween(int status, int campType, Date start, Date end)

Answer 3

试试下面

 List<Bulk_repository>  findAllByStatusAndCamp_typeAndCamp_start_dateBetween(
int status, int camp_type,Date camp_start_dateStart, Date camp_start_dateEnd)

无论你想叫它到哪里

@Autowired
private Bulk_repository bulkRepository;


public List<Bulk_repository> getListRepositoryByConditions(){
Calendar cal = Calendar.getInstance();
cal.add(Calendar.DATE, -2);

Date startDate = cal.getTime();

Date endDate = new Date();

return bulkRepository.findAllByStatusAndCamp_typeAndCamp_start_dateBetween(
0, 1,startDate, endDate);

}