1、返回值为List<Object[]>,由于我们之前说过Jpa无法自动完成查询结果到自定义实体的映射,所以我们要使用改对象接收。
nativeQuery=true,属性的设置,是表明该方法中的sql以数据库的sql语句格式对待。
//查询某个tag在某段时间内的avg 、min 、 max 、 sum 、 count 等
@Query(value = "SELECT tag,round(cast(avg(value) as decimal),2) as value ,to_char(time1,'yyyy-MM-dd hh24:MI:ss') as time1" +
" FROM user where tag =?1 and " +
" time1 between ?2 and ?3 group by time1 ,tag " ,nativeQuery = true)
List<Object[]> findTag(String tag , Timestamp startTime , Timestamp endTime );
controller 类中
@RequestMapping(value = "/selectTableTag/one")
public List<Object[]> findByMetrics(@RequestParam("tag")String tag, @RequestParam("startTime") Timestamp startTime,
@RequestParam("endTime") Timestamp endTime){
return metricsRepository.findTag(tag ,startTime ,endTime);
}
postman 测试结果
2、查询全部信息
①、带SQL语句
@Query(value = "SELECT tag,round(cast(avg(value) as decimal),2) as value ," +
"to_char(time1,'yyyy-MM-dd hh24:MI:ss') as time1,to_char(time2,'yyyy-MM-dd hh24:MI:ss') as time2" +
" FROM 表名 where tag =?1 and time1 between ?2 and ?3 group by time1,tag,time2",nativeQuery = true)
List<xxxEntity> findTag(String tag , Timestamp startTime , Timestamp endTime );
②、jpa 语法 (建议不知道jpa 语法的区查一下,这个是jpa命名查询规则)
List<MetricsEntity> findByTagAndSourceTimeBetween(String tag , Timestamp startTime , Timestamp endTime);
3、只查询几个字段
public interface MetricsRepository extends JpaRepository<MetricsEntity,Timestamp>,
JpaSpecificationExecutor<MetricsEntity>{ }
实体类
自定义 model 类 【只从这个表中查询字段】,这个sourceTime 时间我自定义的是Date类型的,而我的数据库表里是时间戳
这个时候使用hql语句,注意是HQL语句,不是SQL语句了,nativeQuery = false
public interface xxxRepository extends Repository<yyyyEntity, Timestamp> {
//查询 yyyyEntity 这张表 在某段时间之内的tag的全部信息
@Query(value = "select new com.yyyy.zzzzSummary(m.sourceTime, m.tag ,m.value) " +
"FROM yyyyEntity m where m.tag =?1 and m.sourceTime between ?2 and ?3 order by m.sourceTime asc")
List<zzzzSummary> find(String tag, Timestamp startTime, Timestamp endTime);
}