psqlException：错误：操作符不存在:integer=bytea

Question

  @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"
            )

这是我的存储库中的代码

 @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
);

我的控制器里有这个

   @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);
   }

我还注意到，如果我从（：subjects）中的s.subject_id的查询中删除这一部分，或者说我像（2,3,4）中的s.subject_id那样硬编码subjects值，代码将成功运行。但是，如果值来自请求，我就会得到错误。下面是请求的样子

本地主机:8080/评估/F3DF0BC2-7B4C-49B9-86C9-6E6B01628623/3/4/1？主题=2,3,4

Answer 1

我最近在使用Postgres上的本地JPA查询时遇到了与您类似的问题。以下是对我有效的方法：

// 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驱动程序无法正确地将所需的类型信息传递到数据库来解释。例如，以下错误：

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