JPA native sql query mapping error?

Question

I have a JPA entity MyEntity which includes a composite primary key in a @Embeddable class MyEntityPK.
I am using a native sql query in method getThreeColumnsFromMyEntity():

 public List<MyEntity> getThreeColumnsFromMyEntity() {

  List<MyEntity> results = em.createNativeQuery("select  pid,name,dateofbirth from (select pid,name, dateofbirth,max(dateofbirth) "
            + "over(partition by pid) latest_dateofbirth from my_entity_table) where"
            + " dateofbirth = latest_dateofbirth;","myEntityMapping").getResultList();

    return results;

My @SqlResultSetMapping:

@SqlResultSetMapping(
    name = "myEntityMapping",
    entities = {
        @EntityResult(
                entityClass = MyEntityPK.class,
                fields = {
                    @FieldResult(name = "PID", column = "pid"),
                    @FieldResult(name = "NAME", column = "name")}),
        @EntityResult(
                entityClass = MyEntity.class,
                fields = {
                    @FieldResult(name = "dateofbirth", column = "dateofbirth")})})

My JPA columns are named : @Column(name="DATEOFBIRTH"), "PID" and "NAME".
I tested my sql statement straight on the db and it works fine.
When i run it on Eclipse I get an Oracle error:

ORA-00911 and "Error code 911 , Query: ResultSetMappingQuery [..]

My guess is there is something wrong with the mapping but I cannot find out what it is.

Always check what the Oracle error message is, in this case `ORA-00911: invalid character` That gives you a hint to start your investigation — Thomas.L, Aug 24 '17 at 15:38

Youcef LAIDANI · Answer 1 · 2017-08-24T17:02:59.717

I assume you get this error because you are missing the alias name for the subquery, so instead you can try this :

select
   pid,
   name,
   dateofbirth 
from
   (
      select
         pid,
         name,
         dateofbirth,
         max(dateofbirth) over(partition by pid) AS latest_dateofbirth 
      from
         my_entity_table
   ) second_result 
--        ^--------------- use an aliase name to the subquery 
where
   second_result.dateofbirth = latest_dateofbirth
--  ^----use the aliase name to reference to any of its fields, in your case 'dateofbirth'

Take a look about the error meaning here ORA-00911: invalid character tips

JPA native sql query mapping error?

1 Answers1