org.postgresql.util.PSQLException:错误:运算符不存在:整数= bytea [英] org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = bytea

问题描述

我正在尝试从Spring Boot应用程序执行本机查询，但是却收到此错误"org.postgresql.util.PSQLException:错误:运算符不存在:integer = bytea"

I am trying to execute a Native Query from a Spring Boot application, but i am getting this error " org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = bytea "

这是我为实现此目的而编写的代码

Here are the codes i have written to implement this

  @SqlResultSetMapping(
    name = "StudentAssessmentValue",
    classes = @ConstructorResult(
            targetClass = StudentAssessmentDTO.class,
            columns = {
                    @ColumnResult(name = "subject_title", type = String.class),
                    @ColumnResult(name = "assessment", type = String.class),
            }
    )
  )


  @NamedNativeQuery(
                    name = "getStudentSubjectsAssessment",
                    query = "SELECT\n" +
                            "   subject.subject_title,\n" +
                            "   j as assessment\n" +
                            "FROM   assessment s\n" +
                            "JOIN   LATERAL jsonb_array_elements(s.assessment) j(elem) ON (j.elem->>'student_id') = :student_id\n" +
                            "JOIN subject ON subject.id = s.subject_id\n" +
                            "WHERE s.subject_id IN (:subjects)\n" +
                            "AND s.academy_year_id = :academy_year_id\n" +
                            "AND s.term_id = :term_id\n" +
                            "AND s.section_id = :section_id"
                    ,
                    resultSetMapping = "StudentAssessmentValue"
            )

这是我的存储库中的代码

This is the code in my Respository

 @Query(nativeQuery = true, name = "getStudentSubjectsAssessment")
  List<StudentAssessmentDTO> getStudentAssessments2(
        @Param("student_id") String student_id,
        @Param("academy_year_id") Integer academy_year_id,
        @Param("section_id") Integer section_id,
        @Param("term_id") Integer term_id,
        @Param("subjects") Integer[] subjects
);

我在我的控制器中有这个

And i have this in my controller

   @GetMapping("/{student_id}/{academy_year_id}/{section_id}/
   term_id}")
    public List<StudentAssessmentDTO> getStudentAssessment2(
        @PathVariable("student_id") String student_id,
        @PathVariable("academy_year_id") Integer academy_year_id,
        @PathVariable("section_id") Integer section_id,
        @PathVariable("term_id") Integer term_id,
        @RequestParam(value = "subjects") Integer[] subjects
   ){
    return assessmentService.getStudentAssessments2(student_id, academy_year_id, section_id, term_id, subjects);
   }

我也注意到是否从查询中删除了这部分在s.subject_id IN(:subjects)那里，或者说我像s.subject_id IN(2,3,4)这样对主题值进行硬编码，代码成功运行.但是，如果值来自请求，那么我会得到错误.这是请求的样子

I have also notice if i remove this part from the query WHERE s.subject_id IN (:subjects) or say i hard code the subjects value like so s.subject_id IN (2,3,4) the code runs successfully. But if the value is coming from the request i then get the error. Here is how the request looks like

localhost:8080/assessment/f3df0bc2-7b4c-49b9-86c9-6e6b01628623/3/4/1?subjects = 2,3,4

localhost:8080/assessment/f3df0bc2-7b4c-49b9-86c9-6e6b01628623/3/4/1?subjects=2,3,4

推荐答案

我最近有一个

I recently had a similar problem to yours, while also working with a native JPA query on Postgres. Here is what worked for me:

// sqlString contains your native query

Query query = entityManager.createNativeQuery(sqlString, StudentAssessmentDTO.class);
query.setParameter("subjects", subjects);
query.setParameter("academy_year_id", new TypedParameterValue(IntegerType.INSTANCE, academy_year_id));
query.setParameter("term_id", new TypedParameterValue(IntegerType.INSTANCE, term_id));
query.setParameter("section_id", new TypedParameterValue(IntegerType.INSTANCE, section_id));
List< StudentAssessmentDTO > = query.getResultList();

您看到的错误可以由Postgres JDBC驱动程序无法正确将所需的类型信息传递给数据库来解释.例如，以下错误:

The error you are seeing can be explained by the Postgres JDBC driver's inability to correctly pass needed type information to the database. For instance, the following error:

错误:运算符不存在:integer = bytea

ERROR: operator does not exist: integer = bytea

会发生，因为驱动程序将参数作为字节数组传递给Postgres，但是目标列是整数类型.通过使用上述类型的提示"，我们可以强制驱动程序传递正确的类型信息.

would occur because the driver is passing the parameter to Postgres as a byte array, but the target column is integer type. By using the above type "hints," we can force the driver to pass the correct type information.

这篇关于org.postgresql.util.PSQLException:错误:运算符不存在:整数= bytea的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！