Hibernate SQL Query result Mapping/Convert TO Object/Class/Bean

hibernate, hibernate-mapping, java, sql

Solution

You can go further and add `.setResultTransformer(Transformers.aliasToBean(YOUR_DTO.class));` and automatically map it to your custom dto object, see also Returning non-managed entities.

For example:

public List<MessageExtDto> getMessagesForProfile2(Long userProfileId) {
    Query query = getSession().createSQLQuery("  "
            + " select a.*, b.* "
            + " from messageVO AS a "
            + " INNER JOIN ( SELECT max(id) AS id, count(*) AS count FROM messageVO GROUP BY messageConversation_id) as b ON a.id = b.id "
            + " where a.id > 0 "
            + " ")
            .addScalar("id", new LongType())
            .addScalar("message", new StringType())
            ......... your mappings
            .setResultTransformer(Transformers.aliasToBean(MessageExtDto.class));

    List<MessageExtDto> list = query.list();
    return list;
}

Problem

1 2: select (table.*)/(all column) is OK ``` String sql = "select t_student.* from t_student"; //String sql = "select t_student.id,t_student.name,... from t_student"; //select all column SQLQuery query = session.createSQLQuery(sql); query.addEntity(Student.class);//or query.addEntity("alias", Student.class); //query.list();[Student@..., Student@..., Student@...] query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); //or other transformer query.list(); //[{Student(or alias)=Student@...},{Student=Student@...}] ``` 3: select some column(not all of), is Error ``` String sql = "select t_student.id,t_student.name.t_student.sex from t_student"; SQLQuery query = session.createSQLQuery(sql); query.addEntity(Student.class); query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); query.list(); //Exception:invalid column/no column ``` I want "3" to work ok, and let the result can be mapped to Student.class. Like: Student[id=?, name=?, sex=?, (other field are null/default)] I've no idea for this error, help me please!

Original source