2018-08-18 10:27:40.842 WARN 6492 --- [nio-8082-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 22003
2018-08-18 10:27:40.842 ERROR 6492 --- [nio-8082-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: numeric field overflow
详细:A field with precision 3, scale 2 must round to an absolute value less than 10^1.
2018-08-18 10:27:40.842 ERROR 6492 --- [nio-8082-exec-2] 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.DataIntegrityViolationException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not extract ResultSet] with root cause
org.postgresql.util.PSQLException: ERROR: numeric field overflow
详细:A field with precision 3, scale 2 must round to an absolute value less than 10^1.
1、ERROR: numeric field overflow 数字字段溢出 ==== 这是前端传过来的时候,报的错误
【这是在一段时间之内的分段时间的多点查询语句所报的错误】
2、然后本人自己测试报错如下:
2018-08-18 10:40:46.583 WARN 6492 --- [nio-8082-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42883
2018-08-18 10:40:46.583 ERROR 6492 --- [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.
位置:155
2018-08-18 10:40:46.586 ERROR 6492 --- [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.
位置:155
controller 代码改为:
@GetMapping("/selectReport/day")
public List selectMoreTimeDay(@RequestParam("tag") String tag){
//截取数组
String[] tags = tag.split(",");
//转化成list类型
List<String> tagList = Arrays.asList(tags);
return metricsRepository.selectMoreTimeDay(tagList);
顺利的出结果数据!!
3、再和前端进行测试: controller 代码
@GetMapping("/selectReport/day")
public List selectMoreTimeDay(@RequestParam("tag") String tag){
//json 字符串转换成java对象数组
JSONArray json = JSONArray.fromObject(tag);
List<String> tags = (List<String>)JSONArray.toCollection(json, String.class);
return metricsRepository.selectMoreTimeDay(tags);
}
总结:
因为tag 值是多个的,且tag 过长、在查询的过程中,SQL语句在数据库里进行查询【查询多个tag值】,就会出现报错
但是查询一个tag值就可以查询出数据来。 遇到数字字段溢出 这种问题的时候,可以先去debug一下【如果是与前端做数据交互的时候,自己用postman也是可以测试的】,看看接收参数的类型等对不对。然后再查询一下自己写的SQL语句有没有问题。
出现问题的原因:
SQL 语句中 : cast(avg(value) as decimal(3,2)) as avg_value 这个的问题 应改为round(cast(avg(value) as decimal),2)就可以查出数据了