我正在尝试从 Spring Boot 应用程序执行本机查询,但收到此错误“ org.postgresql.util.PSQLException:错误:运算符不存在:整数 = bytea ”
这是我为实现此目的而编写的代码
@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);
}
我还注意到如果我从查询中删除这部分
WHERE s.subject_id IN (:subjects) 或说我对主题值进行硬编码,如 s.subject_id IN (2,3,4) 代码成功运行。但如果该值来自请求,我就会收到错误。请求如下所示
本地主机:8080/评估/f3df0bc2-7b4c-49b9-86c9-6e6b01628623/3/4/1?subjects=2,3,4