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
– Thomas.L, Commented Aug 24, 2017 at 15:38

Youcef LAIDANI · Accepted Answer · 2017-08-24 17:02:59Z

1

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

edited Aug 24, 2017 at 17:02

answered Aug 24, 2017 at 14:48

Youcef LAIDANI

60.3k21 gold badges111 silver badges178 bronze badges

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

Collectives™ on Stack Overflow

JPA native sql query mapping error?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related