多重错误：operator does not exist: character varying = bytea

￡神魔★判官ぃ 2022-05-16 07:05 144阅读 0赞

在进行多点查询的时候报错 : postgresql 数据库 ，idea ，SpringBoot ，restful 接口

@Query(value = "  SELECT time_bucket('1 hour', source_time) AS bucket, 
                     avg(value) AS avg_value , count(value) as count_value" +
                   " FROM 表名 WHERE tag in(?1) and  source_time " +
                   " between ?2 AND ?3 GROUP BY bucket ORDER BY bucket DESC" ,
                    nativeQuery = true)
    List select1(String[] tag ,Timestamp startTime , Timestamp endTime );
    
    @GetMapping("/select1")
    public  List select1(@RequestParam("tag") String[] tag ,
                         @RequestParam("startTime") Timestamp startTime ,
                         @RequestParam("endTime") Timestamp endTime){
        return metricsRepository.selectMoreTimeDay(tag,startTime,endTime);
    }

![70][]

2018-08-17 10:56:48.721  WARN 9116 --- [nio-8082-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42883
    2018-08-17 10:56:48.721 ERROR 9116 --- [nio-8082-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: operator does not exist: character varying = bytea
      建议：No operator matches the given name and argument type(s). You might need to add explicit type casts.
      位置：133
    2018-08-17 10:56:48.725 ERROR 9116 --- [nio-8082-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause
    
    org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = bytea
      建议：No operator matches the given name and argument type(s). You might need to add explicit type casts.
      位置：133

#  原因： #

是要做一个转化类型

@Query(value = "  SELECT time_bucket('1 hour', source_time) AS bucket, 
                     avg(value) AS avg_value , count(value) as count_value" +
                   " FROM 表名 WHERE tag in(?1) and  source_time " +
                   " between ?2 AND ?3 GROUP BY bucket ORDER BY bucket DESC" ,
                    nativeQuery = true)
    List select1(List<String> tag ,Timestamp startTime , Timestamp endTime );
    
    @GetMapping("/select1")
    public  List select1(@RequestParam("tag") String[] tag ,
                         @RequestParam("startTime") Timestamp startTime ,
                         @RequestParam("endTime") Timestamp endTime){
        //json 字符串转换成java对象数组
            JSONArray json = JSONArray.fromObject(tag);
            List<String> tags = (List<String>)JSONArray.toCollection(json, String.class);
            return metricsRepository.selectMoreTimeDay(tags,startTime,endTime);
    }

运行结果，报错 （postmman 做测试）

![70 1][]

# 最终：  #

但是前端可以查询到数据。最后自己仔细回想了一下，我自己在controller做了个debug测试一下，前端传给我的数据，其中有一个值是多个的，也就是数组的形式 \[ \]。但是我接收到的却是\{“\[ \]”\} 这种类型的，所以用json字符串做了转化。

而我自己做测试的时候，在Chrome直接输入请求的地址，并没有\{ \} 的产生，所以可以测试到数据。我这里不识别\{ \} ，原因是Tomcat的问题，在[Tomcat 7 的某个版本不支持\{ \}][Tomcat 7 _] ，会报错，地址给了大家可以查看！！

[70]: /images/20220516/87d9bfe46a7c42dba58dc939210b468c.png
[70 1]: /images/20220516/f129c128594340c2b20263593150a1ea.png
[Tomcat 7 _]: https://blog.csdn.net/qq_28289405/article/details/81661818