org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = bytea

Question

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

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

Tim Biegeleisen · Accepted Answer · 2019-10-08 11:26:38Z

6

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

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:

ERROR: operator does not exist: integer = bytea

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.

edited Oct 8, 2019 at 11:26

answered Oct 8, 2019 at 5:33

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Anana Aristotle Over a year ago

Thanks for the response i am try to implement your solution but i am now getting this error Unknown entity: com.aprisoft.eagle_eye.dto.StudentAssessmentDTO, you should know that this is just a class like so public class StudentAssessmentDTO implements Serializable { private static final long serialVersionUID = 1L; private String subject_title; @JsonRawValue private String assessment; }

Tim Biegeleisen Over a year ago

What is the name of the entity class? I wasn't sure of this, so I just took the DTO class.

Anana Aristotle Over a year ago

The DTO class is to deserialize the result set i get from the query, so does entity manager deserialize a result set using a class that is not an entity?

Tim Biegeleisen Over a year ago

Is that class annotated with @Entity at the very top of the class definition? Just do a search in your codebase for @Entity to see which classes you have.

Anana Aristotle Over a year ago

This is the error i am having now org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = integer[]

|

Anana Aristotle · Accepted Answer · 2019-10-08 23:57:23Z

This might not be the best solution to this, but it is working for me, i will be glad if anyone can still provide a better solution.

Here is what i did:

I wrote a method that will take the array gotten from the request and generate a string of this nature "(a,b,c)" and then added this string to my query string and it work

CODE

This is the method that build the string NOTE: I can afford to use this function because i know for sure them element of this array does not grow rapidly, max for my case will be 15

 public String generateInSearchParameter(Integer[] inputArr){
    StringBuilder search = new StringBuilder("(");

    IntStream.range(0, inputArr.length).forEach(i -> {
        if (i != inputArr.length - 1) {
            search.append(inputArr[i]).append(',');
        } else {
            search.append(inputArr[i]);
        }
    });

     search.append(")");

     return search.toString();
  }

Here is the controller code

 @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,generateInSearchParameter(subjects));

    }

Here is the code in my Service

public List<StudentAssessmentDTO> getStudentAssessments2(
            String student_id, Integer academy_year_id,
            Integer section_id, Integer term_id, String subjects
    ){

        String sqlString = "SELECT" +
                " subject.subject_title," +
                " j.*" +
                " FROM  assessment s" +
                " JOIN   LATERAL jsonb_array_elements(s.assessment) j(elem) ON (j.elem->>'student_id') = :student_id" +
                " JOIN subject ON subject.id = s.subject_id" +
                " WHERE s.academy_year_id = :academy_year_id" +
                " AND s.section_id = :section_id" +
                " AND s.subject_id IN " + subjects +
                " AND s.term_id = :term_id";

        Query query = entityManager.createNativeQuery(sqlString, "StudentAssessmentValue");
        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));
        query.setParameter("student_id", new TypedParameterValue(StringType.INSTANCE, student_id));

        return query.getResultList();

    }

I will be guard to welcome a better solution if provided

Collectives™ on Stack Overflow

org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = bytea

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related