CODEWITHSUNDEEP
javajpa
User0911
User0911

Reputation: 1582

Issue with JPA @Query with native = true

I have this strange issue that I am not able to fix and not sure what is going wrong. I have below JPA Query,

@Query(nativeQuery=true, value = "select new com.mypackage.StudentDetailsDTO(e.STUDENT_ID as studentId, e.SUBJECT_ID as subjectId, e.SUBJECT_TYPE as subjectType, "+
"e.RESULT_STATUS as resultStatus, nvl(e.ASSIGNED_STUDENT_ID, -1) as assignedStudentId, nvl(e.TAKEN_BY_STUDENT_ID, -1) as takenByStudentId , count(1) as totalCount) from STUDENT e "
        + "WHERE e.STUDENT_ROLLNO = :studentRollNumber AND e.EXAM_TIME between :startTime AND :endTime ")
public List<StudentDetailsDTO> fetchStudentDetailsUsingGroupBy(@Param("studentRollNumber") String iStudentRollNumber, @Param("startTime") Date iStartTime, @Param("endTime") Date iEndTime);

When fired on oracle, this is the query that gets fired,

select
   new com.mypackage.StudentDetailsDTO(e.STUDENT_ID as studentId,
   e.SUBJECT_ID as subjectId,
   e.SUBJECT_TYPE as subjectType,
   e.RESULT_STATUS as resultStatus,
   nvl(e.ASSIGNED_STUDENT_ID,
   -1) as assignedStudentId,
   nvl(e.TAKEN_BY_STUDENT_ID,
   -1) as takenByStudentId ,
   count(1) as totalCount) 
from
   STUDENT e 
WHERE
   e.STUDENT_ROLLNO = ? 
   AND e.EXAM_TIME between ? AND ? 
GROUP BY
   e.STUDENT_ID,
   e.SUBJECT_ID,
   e.SUBJECT_TYPE,
   e.RESULT_STATUS,
   nvl(e.ASSIGNED_STUDENT_ID,
   -1),
   nvl(e.TAKEN_BY_STUDENT_ID,
   -1)

Here is my constructor from DTO for mapping,

public StudentDetailsDTO(long studentId, long subjectId, String subjectType, String resultStatus, String assignedStudentId, String takenByStudentId, int totalCount) {
       this.studentId = studentId;
       this.subjectId = subjectId;
       this.subjectType = subjectType;
       this.resultStatus = resultStatus;
       this.assignedStudentId = assignedStudentId;
       this.takenByStudentId = takenByStudentId;
       this.totalCount = totalCount;   
   }

Every time I am getting below exception,

java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected

Any help will be appreciated, I am trying this from quite long but with no luck.

Upvotes: 3

Views: 2851

Answers (1)

Forketyfork
Forketyfork

Reputation: 7810

You seem to be using Spring Data JPA. If that is correct, you shoudn't put the call to constructor of your DTO (new com.mypackage.StudentDetailsDTO()) inside your native query. You should write a usual Oracle SQL query. Spring Data JPA will create your DTO object automatically based on the query results.

Also there seems to be an error in your DTO object. The assignedStudentId and takenByStudentId parameters are of type String, but your query selects them as long.

Upvotes: 3

Related Questions