Java Hibernate createSQLQuery using addEntity

Question

I need to apply a SQL Query similiar to this.

SELECT
    id as id,
    c03 as c03,
    c34 as c34 
FROM
    (SELECT
        id,
        c03,
        c34 
    FROM
        students
    where
        c34 in(
            ?, ?,?,?
        ) 
    order by
        id desc) o 
group by
    c34;

And My Java code.

private final void retrieveStudents(){
    final List result = currentSession()
            .createSQLQuery("SELECT id as id,c03 as c03,c34 as c34 FROM (SELECT id,c34,c03 FROM students where c34 in(:filters) order by id desc) o group by c34;")
            .setParameterList("filters",Arrays.asList(74,1812))
            .list();
    result.forEach(this::consumer1);
}

The query is Just OK. Returns a array of objets which i can iterate but i would like to return a Student object so i add.

   .addEntity(Student.class)

But a error is throw which says

Column 'ACTIVE' not found.

Also i have try with .addScalar but the same thing happens.

.addScalar("id",org.hibernate.type.IntegerType.INSTANCE)
.addScalar("c03",org.hibernate.type.DateType.INSTANCE)

Which is a column from Student but is not on the projection my question how can i do that i just thought that applying in some way the alias would Hibernate populate the student entity.

All i want is a Student object with id,c03,c34 values populate.

What i am doing wrong is that possible?

Answer 1

For this use case, you don't want hibernate to treat Student as an entity, but as a DTO.

For this, do not use the addEntity method, but the setResultTransfomer :

final List result = currentSession()
        .createSQLQuery("SELECT id as id,c03 as c03,c34 as c34 " + 
                         "FROM (SELECT id,c34,c03 FROM students " + 
                               "where c34 in(:filters) " + 
                               "order by id desc) o group by c34")
        .setParameterList("filters",Arrays.asList(74,1812))
        .addScalar("id",org.hibernate.type.IntegerType.INSTANCE)
        .addScalar("c03",org.hibernate.type.DateType.INSTANCE)
        .addScalar("c34",org.hibernate.type.DateType.INSTANCE)
        .setResultTransformer(Transformers.aliasToBean(Student.class))
        .list();

This is working for non entity classes, provided the class has setters that match the names of the projected columns, and there is a no-arg constructor. I never tested this on an entity class.

If you do not have setters, but a constructor for those 3 fields, you can use :

// choose here the right constructor
java.lang.reflect.Constructor constructor = Student.class.getConstructors()...
// ...
.setResultTransformer(new AliasToBeanConstructorResultTransformer(constructor));

instead.

EDIT : I would not use an entity as a DTO (but create a specific DTO for this use case) : what if one of your service think the entity was loaded the regular way (so is fully initialized), do some modifications on it (update a field for example), and save the entity ?

In the best case, a not-nullable field (on db side) will not have been initialized, and you'll get a ConstraintViolationException and trigger a rollback, keeping your data safe.

In the worst case, you'll be corrupting your data by setting to null all the fields of the entity but the three loaded ones.