JPA 自定义对象 postgresql数据库

àì夳堔傛蜴生んèń 2022-05-13 04:26 198阅读 0赞

**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 测试结果

![70][]

**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>{ }

实体类

![70 1][]

**自定义 model 类 【只从这个表中查询字段】，****这个sourceTime 时间我自定义的是Date类型的，而我的数据库表里是时间戳**

![70 2][]

### **这个时候使用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);
    
    }

[70]: /images/20220513/d7fc1773b46e456c971298513b1c3669.png
[70 1]: /images/20220513/87becffa9aeb4754b2963be274514a58.png
[70 2]: /images/20220513/c35307352fae4efdb82769c6ca0926d1.png