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

Question

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!

why using sql query? when you can use hql or criteria? you put `hql` instead of `sql` in 3 a mistake :P — nachokk, Jun 28 '13 at 01:59
haha!I just show a sample.In fact, Some bussiness must be implemented by sql. — YETI, Jun 28 '13 at 02:16

score 25 · Answer 1 · edited Oct 10 '16 at 12:49

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;
}

It should be usefull, but i'm so lazy, i was thought it can be transformed automatically. — YETI, Feb 18 '14 at 02:58

score 4 · Answer 2 · answered Mar 29 '15 at 12:06

I want "3" to work ok, and let the result can be mapped to Student.class

That's possible using
Query createNativeQuery(String sqlString, String resultSetMapping)

In the second argument you could tell the name of the result mapping. For example:

1) Let's consider a Student entity, the magic is going to be in the SqlResultSetMapping annotation:

import javax.persistence.Entity;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.Table;

@Entity
@Table(name = "student")
@SqlResultSetMapping(name = "STUDENT_MAPPING", classes = {@ConstructorResult(
    targetClass = Student.class, columns = {
      @ColumnResult(name = "name"),
      @ColumnResult(name = "address")
})})
public class Student implements Serializable {
   private String name;
   private String address;

   /* Constructor for the result mapping; the key is the order of the args*/ 
   public Student(String aName, String anAddress) {
       this.name = aName;
       this.address = anAddress;
   }

   // the rest of the entity
}

2) Now you can execute a query which results will be mapped by STUDENT_MAPPING logic:

String query = "SELECT s FROM student s";
String mapping = "STUDENT_MAPPING";
Query query = myEntityManager.createNativeQuery(query, mapping);
@SuppressWarnings("unchecked")
List<Student> students = query.getResultList();
for (Student s : students) {
   s.getName(); // ...
}

Note: I think it's not possible to avoid the unchecked warning.

Mikhail Peresypkin · Answer 3 · 2013-06-28T04:29:01.277

3

There is only two ways.

You can use 1st or 2nd snippet. According to Hibernate documentation you must prefer 2nd.

You can get just a list of object arrays, like this:

String sql = "select name, sex from t_student";
SQLQuery query = session.createSQLQuery(sql);
query.addScalar("name", StringType.INSTANCE); 
query.addScalar("sex", StringType.INSTANCE); 
query.list();

edited Jun 28 '13 at 04:29

answered Jun 28 '13 at 04:13

Mikhail Peresypkin

146
7

Thank you. Use query.setResultTransformer({custom transformer}) after addScalar, can convert to Student.class,but I don't want it this way. – YETI Jun 28 '13 at 07:04
I want it be a Object instance of Student, is there anyone know? – YETI Jul 06 '13 at 04:16

oopdev · Answer 4 · 2016-12-07T13:24:11.630

I had same problem on HQL Query. I solved the problem by changing the transformer.

The problem caused the code written to transform as Map. But it is not suitable for Alias Bean. You can see the error code at below. The code written to cast result as map and put new field to the map.

Class : org.hibernate.property.access.internal.PropertyAccessMapImpl.SetterImpl m Method: set

    @Override
    @SuppressWarnings("unchecked")
    public void set(Object target, Object value, SessionFactoryImplementor factory) {
        ( (Map) target ).put( propertyName, value );
    }

I solved the problem to duplicate the transformer and change the code.

You can see the code in the project.

Link : https://github.com/robeio/robe/blob/DW1.0-migration/robe-hibernate/src/main/java/io/robe/hibernate/criteria/impl/hql/AliasToBeanResultTransformer.java

Class:

import java.lang.reflect.Field;
import java.util.Map;

import io.robe.hibernate.criteria.api.query.SearchQuery;
import org.hibernate.HibernateException;
import org.hibernate.transform.AliasedTupleSubsetResultTransformer;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


public class AliasToBeanResultTransformer extends AliasedTupleSubsetResultTransformer {

    private static final Logger LOGGER = LoggerFactory.getLogger(AliasToBeanResultTransformer.class);

    private final Class resultClass;

    // Holds fields of Transform Class as Map. Key is name of field. 
    private Map<String, Field> fieldMap;

    public AliasToBeanResultTransformer(Class resultClass) {
        if ( resultClass == null ) {
            throw new IllegalArgumentException( "resultClass cannot be null" );
        }
        fieldMap = SearchQuery.CacheFields.getCachedFields(resultClass);
        this.resultClass = resultClass;
    }

    @Override
    public boolean isTransformedValueATupleElement(String[] aliases, int tupleLength) {
        return false;
    }

    @Override
    public Object transformTuple(Object[] tuple, String[] aliases) {
        Object result;
        try {
            result = resultClass.newInstance();
            for ( int i = 0; i < aliases.length; i++ ) {
                String name = aliases[i];
                Field field = fieldMap.get(name);

                if(field == null) {
                    LOGGER.error(name + " field not found in " + resultClass.getName() + " class ! ");
                    continue;
                }
                field.set(result,  tuple[i]);
            }
        }
        catch ( InstantiationException e ) {
            throw new HibernateException( "Could not instantiate resultclass: " + resultClass.getName() );
        } catch ( IllegalAccessException e ) {
            throw new HibernateException( "Could not instantiate resultclass: " + resultClass.getName() );
        }

        return result;
    }
}

After created new Transformer You can use like below.

query.setResultTransformer(new AliasToBeanResultTransformer(YOUR_DTO.class));

Can't find the io.robe.hibernate.criteria.api.query.SearchQuery Can you help in this regard? I used this link https://mvnrepository.com/artifact/io.robe/robe-hibernate/0.5.0.0-1039 — sarwar026, Apr 28 '20 at 08:13

score 0 · Answer 5 · answered Apr 24 '21 at 05:24

You can mapped it automatically:

Your Model Student.java

public class Student { private String name; private String address; }
Repository

String sql = "Select * from student"; Query query = em.createNativeQuery(sql, Student.class); List ls = query.getResultList();

so it will automatically mapped the result with the Student class

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

5 Answers5

Linked