JPA@Query常见查询传参方式，jpa in查询，like模糊查询

JPA常用查询

（1）通过传入参数索引查询：

 @Query(value = "select * from aa t  where t.comcode=?1  and to_char(t.checkday,'yyyy-MM-DD' )=?2 ",nativeQuery = true)
    public List <aa>  findPrpacheckconfinfo(String comCode,String vaDate);

根据comcode vaDate查询结果集

（2）用参数名称进行查询

    @Query(value = "select t.* from bb t where t.usercode=:usercode and  t.yearmonth  like :year% order by t.yearmonth asc ",nativeQuery = true)
    public List<bb> findCountNumber(@Param("usercode") String usercode, @Param("year")String year);

根据usercode,year查询对应结果集

（3）修改，删除数据

    @Transactional
    @Modifying
    @Query(value = "update cc t set t.checkstatus='1'  where t.comcode=?1 and t.checktype =?2 and to_char(t.checkday,'yyyy-MM-DD' )=?3  ",nativeQuery = true)
    public void  UpdatePrpacheckconfinfo(String comcode,String  checktype,String checkday);

    @Transactional
    @Modifying
    @Query(value = "delete dd t    where t.comcode=?1 and t.checktype =?2 and to_char(t.checkday,'yyyy-MM-DD' )=?3  ",nativeQuery = true)
    public void  DeletePrpacheckconfinfo(String comcode,String  checktype,String checkday);

根据 comcode,  checktype,checkday 修改对应值

    @Transactional 表示事物提交

    @Modifying 注解完成修改操作（注意：不支持新增）

（4）in like模糊查询

in查询，把需要的参数封装成一个集合，传入一个集合即可 如查询 in（'1'，'2'）则可以写：

List<String> syssource=new ArrayList<String>();
syssource.add("1");
syssource.add("2");

   @Query(value = "select * from ee where identifynumber=?1 and yearmonth=?2 and syssource in (?3)  and (freetype='0' or freetype='' or  freetype is null) ", nativeQuery = true)
    public List<ee> findNoFreeByIdentifynumber( String identifynumber,String yearmonth, List<String> syssource);

 

like 模糊 查询 传入参数式查询, （完全模糊  like  %:year%）

    @Query(value = "select t.* from bb t where    t.yearmonth  like :year% order by t.yearmonth asc ",nativeQuery = true)
    public List<bb > findCountNumber(  @Param("year")String year);

like 模糊 查询 索引式查询（ 完全模糊 like   %?1%）

    @Query(value = "select * from ff t where t.comcode like   ?1% and to_char(t.paydate,'yyyy-MM-dd')=?2  ",nativeQuery = true)
    public List <ff>  findPrpabalancepayinfo(String comcode, String date);