休眠 SQL 查询结果映射/转换为对象/类/Bean

java sql hibernate hibernate-mapping

2022-09-02 12:33:56

1 2：选择（表.*）/（所有列）是否正常

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：选择一些列（不是全部），是错误

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

我希望“3”工作正常，并让结果可以映射到学生.class。
比如：Student[id=？， name=？，=？，（其他字段为空/默认值）]
我不知道这个错误，请帮帮我！

答案 1

您可以更进一步，添加并自动将其映射到自定义 dto 对象，另请参阅返回非托管实体。.setResultTransformer(Transformers.aliasToBean(YOUR_DTO.class));

例如：

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

答案 2

我希望“3”工作正常，并让结果可以映射到学生.class

使用
Query createNativeQuery（String sqlString，String resultSetMapping）可以做到这一点。

在第二个参数中，您可以告诉结果映射的名称。例如：

1）让我们考虑一个学生实体，魔术将在SqlResultSetMapping注释中：

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）现在，您可以执行一个查询，其结果将由逻辑映射：STUDENT_MAPPING

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(); // ...
}

注意：我认为无法避免未经检查的警告。