使用@Query
注解自定义查询
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD, ElementType.ANNOTATION_TYPE})
@QueryAnnotation
@Documented
public @interface Query {
String value() default "";//sql 或者JPQL
//指定count的查询语句,不指定会根据query自动生成
String countQuery() default "";
//指定使用什么字段来count()
String countProjection() default "";
//标记是否用原生sql
boolean nativeQuery() default false;
//指定一个query的名字,必须唯一
String name() default "";
//指定一个count的query的名字,必须唯一
String countName() default "";
}
需求:联表查询,并且根据idj进行分组统计,并获取时间最新的一条数据,传入的参数有的肯为空
解决:
1.对时间使用MAX函数,分组后自然就会获取时间最新的记录
2.使用if函数过滤参数为null的情况
@Query(nativeQuery = true, value = "SELECT b.id," +
"b.title," +
"SUM(a.total_count) AS totalCount," +
"SUM(a.negative_count) AS negativeCount," +
"b.start_time,b.endtime," +
"a.test_count AS testCount,MAX(a.analysis_date), " +
" FROM result a ,config b " +
" WHERE a.id=b.id AND b.id=?1 " +
//" AND a.analysis_date BETWEEN ?2 AND ?3 AND a.code=?4 AND a.key=?5 " +
" AND if(?2 is null,1=1,a.analysis_date>=?2 ) AND a.analysis_date<=?3 AND a.code=?4 AND a.key=?5 " +
" GROUP BY b.id")
List<Map<String, Object>> search(@Param("id") Long id, @Param("start") String start, @Param("end") String end, @Param("code") String code, @Param("key") String key);
返回整个表字段时才能使用表的实体来接收,自定义返回哪些字段时可以用Map
或者Object[]来接收
这个方式分页有些问题
@Query(nativeQuery = true,
value = "SELECT * FROM works a WHERE if(?1 IS NULL,1=1,a.id=?1) AND a.delete_flag=0 /* #pageable# */ ",
countQuery = "select count(*) from trsom_collected_works a WHERE if(?1 IS NULL,1=1,a.product_id=?1) AND a.delete_flag=0")
@Query(nativeQuery = true,value = "SELECT * FROM works a WHERE if(?1 IS NULL,1=1,a.id=?1) AND a.deleted=0")
Page<WorksEntity> pageByTest1(@Param("id") Long id, Pageable pageable);
或者使用Page<Map<String,Object>>来接收
Page<Map<String,Object>> pageByTest2(@Param("id") Long id, Pageable pageable);
@Query(value = "select u from WorksEntity u where u.id=?1 and u.deleted=0")
@Query(value = "select u from WorksEntity u where u.id=:id and u.deleted=0")
Page<WorksEntity> pageById(@Param("id") Long id, Pageable pageable);