javasqlhibernatehqlhibernate-native-query

how to get list of long values in hibernate from the table while casting from bigint to long


I get the following exception when using the getEvents method:

org.hibernate.MappingException: Unknown entity: java.lang.Long

public List<Long> getEvents(Person person) {

    String q = "select new java.lang.Long(te.event_id) "
        + "from teachers_event te"
        + "where te.teachers_id = :personId ";
    Query query = entityManager.createNativeQuery(q, Long.class);
    query.setParameter("personId", person.getId());
    return (List<Long>) query.getResultList();
}

The teachers_event table in database connects the id of teacher with the event. I don't map this table to an entity. I only want to get the ids from it. The ids in the PostgreSQL table are bigint. Is there any way to achieve it without making a separate class to map the TeacherEvents entity?


Solution

  • In JPA 2.1 there is a feature called "result set mapping".

    Basically you have to define a POJO class which would hold the result values (all the values must be passed using the constructor):

    public class IdResult{
    
        private Object id;
    
        public IdResult(Object id){
            this.id = id;
        }
    
         public Long getLongId(){
            return (Long)id;
         }
    }
    

    Then you have to declare the mapping on one of your entities (does not matter on which, it just has to be a declated @Entity):

    @SqlResultSetMapping(name="IdMapping", classes = {
        @ConstructorResult(targetClass = IdResult.class, 
        columns = {@ColumnResult(name="id")})
    })
    

    You would have to use an alias in your query to match the @ColumnResult name:

    select te.event_id as id
    ...
    

    And finally use in the query creation:

    Query query = entityManager.createNativeQuery(q, IdMapping.class);
    return (List<IdResult>) query.getResultList();
    

    Then in your service layer you would just use getLongId();