CODEWITHSUNDEEP
javasqlhibernatehibernate-mapping
YETI
YETI

Reputation: 938

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

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!

Upvotes: 19

Views: 103231

Answers (5)

FlyingTurtle
FlyingTurtle

Reputation: 155

You can mapped it automatically:

  1. Your Model Student.java

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

  2. Repository

    String sql = "Select * from student";
Query query = em.createNativeQuery(sql, Student.class);
List<Student> ls = query.getResultList();
  • so it will automatically mapped the result with the Student class

Upvotes: 0

oopdev
oopdev

Reputation: 11

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

Upvotes: 1

Paweł Woźniak
Paweł Woźniak

Reputation: 587

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

Upvotes: 25

Manu Artero
Manu Artero

Reputation: 10293

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.

Upvotes: 4

Mikhail Peresypkin
Mikhail Peresypkin

Reputation: 146

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();

Upvotes: 3

Related Questions