Java 将 Hibernate 查询结果映射到自定义类？

mapping Hibernate query results to custom class?

提问by Chris

Following up on a question I posted yesterday: How to populate POJO class from custom Hibernate query?

跟进我昨天发布的一个问题：How to populate POJO class from custom Hibernate query?

Can someone show me an example of how to code the following SQL in Hibernate, and get the results correctly?

有人可以向我展示如何在 Hibernate 中编写以下 SQL 代码并正确获得结果的示例吗？

SQL:

查询语句：

select firstName, lastName
from Employee

What I'd like to do, if it's possible in Hibernate, is to put the results in their own base class:

如果在 Hibernate 中可能的话，我想做的是将结果放在它们自己的基类中：

class Results {
    private firstName;
    private lastName;
    // getters and setters
}

I believe it's possible in JPA (using EntityManager), but I haven't figured out how to do it in Hibernate (using SessionFactoryand Session).

我相信在 JPA 中是可能的（使用EntityManager），但我还没有想出如何在 Hibernate 中做到这一点（使用SessionFactory和Session）。

I'm trying to learn Hibernate better, and even this "simple" query is proving confusing to know what form Hibernate returns the results, and how to map the results into my own (base) class. So at the end of the DAO routine, I'd do:

我正在尝试更好地学习 Hibernate，即使是这个“简单”的查询也被证明是令人困惑的，不知道 Hibernate 以什么形式返回结果，以及如何将结果映射到我自己的（基）类中。所以在 DAO 例程结束时，我会这样做：

List<Results> list = query.list();

returning a Listof Results(my base class).

返回List的Results（我的基类）。

回答by dur

See AliasToBeanResultTransformer:

请参阅AliasToBeanResultTransformer：

Result transformer that allows to transform a result to a user specified class which will be populated via setter methods or fields matching the alias names.

List resultWithAliasedBean = s.createCriteria(Enrolment.class)
            .createAlias("student", "st")
            .createAlias("course", "co")
            .setProjection( Projections.projectionList()
                    .add( Projections.property("co.description"), "courseDescription" )
            )
            .setResultTransformer( new AliasToBeanResultTransformer(StudentDTO.class) )
            .list();

StudentDTO dto = (StudentDTO)resultWithAliasedBean.get(0);

结果转换器，允许将结果转换为用户指定的类，该类将通过 setter 方法或匹配别名的字段填充。

List resultWithAliasedBean = s.createCriteria(Enrolment.class)
            .createAlias("student", "st")
            .createAlias("course", "co")
            .setProjection( Projections.projectionList()
                    .add( Projections.property("co.description"), "courseDescription" )
            )
            .setResultTransformer( new AliasToBeanResultTransformer(StudentDTO.class) )
            .list();

StudentDTO dto = (StudentDTO)resultWithAliasedBean.get(0);

Your modified code:

您修改后的代码：

List resultWithAliasedBean = s.createCriteria(Employee.class, "e")
    .setProjection(Projections.projectionList()
        .add(Projections.property("e.firstName"), "firstName")
        .add(Projections.property("e.lastName"), "lastName")
    )
    .setResultTransformer(new AliasToBeanResultTransformer(Results.class))
    .list();

Results dto = (Results) resultWithAliasedBean.get(0);

For native SQL queries see Hibernate documentation:

对于本机 SQL 查询，请参阅Hibernate 文档：

13.1.5. Returning non-managed entities

It is possible to apply a ResultTransformer to native SQL queries, allowing it to return non-managed entities.

sess.createSQLQuery("SELECT NAME, BIRTHDATE FROM CATS")
    .setResultTransformer(Transformers.aliasToBean(CatDTO.class))

This query specified:

• the SQL query string
• a result transformer The above query will return a list of CatDTOwhich has been instantiated and injected the values of NAME and BIRTHNAME into its corresponding properties or fields.

13.1.5. 返回非托管实体

可以将 ResultTransformer 应用于本机 SQL 查询，允许它返回非托管实体。

sess.createSQLQuery("SELECT NAME, BIRTHDATE FROM CATS")
    .setResultTransformer(Transformers.aliasToBean(CatDTO.class))

此查询指定：

• SQL 查询字符串
• 结果转换器上面的查询将返回一个列表，CatDTO其中已实例化并将 NAME 和 BIRTHNAME 的值注入到其相应的属性或字段中。

回答by Alejandra

You need to use a constructor and in the hql use new. I let you the code example taken from this question: hibernate HQL createQuery() list() type cast to model directly

您需要使用构造函数并在 hql 中使用 new。我让你从这个问题中获取代码示例： hibernate HQL createQuery() list() type cast to model direct

class Result {
    private firstName;
    private lastName;
    public Result (String firstName, String lastName){
      this.firstName = firstName;
      this.lastName = lastName;
   }
}

then your hql

那么你的 hql

select new com.yourpackage.Result(employee.firstName,employee.lastName) 
from Employee  

and your java (using Hibernate)

和你的 java（使用 Hibernate）

List<Result> results = session.createQuery("select new com.yourpackage.Result(employee.firstName,employee.lastName) from Employee").list();

回答by v.ladynev

select firstName, lastName from Employee

query.setResultTransformer(Transformers.aliasToBean(MyResults.class));

You can't use above code with Hibernate 5 and Hibernate 4 (at least Hibernate 4.3.6.Final), because of an exception

由于异常，您不能将上述代码与 Hibernate 5 和 Hibernate 4（至少是 Hibernate 4.3.6.Final）一起使用

java.lang.ClassCastException: com.github.fluent.hibernate.request.persistent.UserDto cannot be cast to java.util.Map
    at org.hibernate.property.access.internal.PropertyAccessMapImpl$SetterImpl.set(PropertyAccessMapImpl.java:102)

The problem is that Hibernate converts aliases for column names to upper case — firstNamebecomes FIRSTNAME. And it try to find a getter with name getFIRSTNAME(), and setter setFIRSTNAME()in the DTOusing such strategies

问题在于 Hibernate 将列名的别名转换为大写——firstName变成FIRSTNAME. 它试图找到名字一个getter getFIRSTNAME()，和settersetFIRSTNAME()在DTO使用这种策略

    PropertyAccessStrategyChainedImpl propertyAccessStrategy = new PropertyAccessStrategyChainedImpl(
            PropertyAccessStrategyBasicImpl.INSTANCE,
            PropertyAccessStrategyFieldImpl.INSTANCE,
            PropertyAccessStrategyMapImpl.INSTANCE
    );

Only PropertyAccessStrategyMapImpl.INSTANCEsuits, in opinion of Hibernate, well. So after that it tries to do conversion (Map)MyResults.

只PropertyAccessStrategyMapImpl.INSTANCE适合，在 Hibernate 看来，好吧。所以在那之后它会尝试进行转换(Map)MyResults。

public void set(Object target, Object value, SessionFactoryImplementor factory) {
    ( (Map) target ).put( propertyName, value );
}

Don't know, it is a bug or feature.

不知道，这是一个错误或功能。

How to solve

怎么解决

Using aliases with quotes

使用带引号的别名

public class Results {

    private String firstName;

    private String lastName;

    public String getFirstName() {
        return firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

}

String sql = "select firstName as \"firstName\", 
    lastName as \"lastName\" from Employee";

List<Results> employees = session.createSQLQuery(sql).setResultTransformer(
    Transformers.aliasToBean(Results.class)).list(); 

Using a custom result transformer

使用自定义结果转换器

Another way to solve the problem — using a result transformer that ignores method names case (treat getFirstName()as getFIRSTNAME()). You can write your own or use FluentHibernateResultTransformer. You will not need to use quotes and aliases (if you have column names equal to DTO names).

解决问题的另一种方法 - 使用忽略方法名称大小写的结果转换器（getFirstName()视为getFIRSTNAME()）。您可以自己编写或使用FluentHibernateResultTransformer。您不需要使用引号和别名（如果您的列名等于 DTO 名称）。

Just download the library from the project page (it doesn't need additional jars): fluent-hibernate.

只需从项目页面下载库（它不需要额外的 jars）：fluent-hibernate。

String sql = "select firstName, lastName from Employee";
List<Results> employees = session.createSQLQuery(sql)
        .setResultTransformer(new FluentHibernateResultTransformer(Results.class))
        .list();

This transformer can be used for nested projections too: How to transform a flat result set using Hibernate

此转换器也可用于嵌套投影：How to transform a flat result set using Hibernate

回答by Night Owl

YMMV but I've found that the key factor is you must make sure to alias every field in your SELECT clause with the SQL "AS" keyword. I've never had to use quotes around the alias names. Also, in your SELECT clause use the case and punctuation of the actual columns in your database and in the aliases use the case of the fields in your POJO. This has worked for me in Hibernate 4 and 5.

YMMV 但我发现关键因素是您必须确保使用 SQL“AS”关键字为 SELECT 子句中的每个字段设置别名。我从来不必在别名周围使用引号。此外，在 SELECT 子句中使用数据库中实际列的大小写和标点符号，在别名中使用 POJO 中字段的大小写。这在 Hibernate 4 和 5 中对我有用。

@Autowired
private SessionFactory sessionFactory;

...

String sqlQuery = "SELECT firstName AS firstName," +
        "lastName AS lastName from Employee";

List<Results> employeeList = sessionFactory
        .getCurrentSession()
        .createSQLQuery(sqlQuery)
        .setResultTransformer(Transformers.aliasToBean(Results.class))
        .list();

If you have multiple tables you can use table aliases in the SQL as well. This contrived example with an additional table named "Department" uses more traditional lower case and underscores in database field names with camel case in the POJO field names.

如果您有多个表，您也可以在 SQL 中使用表别名。这个带有名为“Department”的附加表的人为示例在数据库字段名称中使用更传统的小写和下划线，在 POJO 字段名称中使用驼峰式大小写。

String sqlQuery = "SELECT e.first_name AS firstName, " +
        "e.last_name AS lastName, d.name as departmentName" +
        "from Employee e, Department d" +
        "WHERE e.department_id - d.id";

List<Results> employeeList = sessionFactory
        .getCurrentSession()
        .createSQLQuery(sqlQuery)
        .setResultTransformer(Transformers.aliasToBean(Results.class))
        .list();

回答by Lisandro

In case you have a native query, all answers here use deprecated methods for newer versions of Hibernate, so if you are using 5.1+ this is the way to go:

如果您有本机查询，这里的所有答案都使用较新版本的 Hibernate 不推荐使用的方法，所以如果您使用 5.1+，这是要走的路：

// Note this is a org.hibernate.query.NativeQuery NOT Query.
NativeQuery query = getCurrentSession()
                .createNativeQuery(
                        "SELECT {y.*} , {x.*} from TableY y left join TableX x on x.id = y.id");


// This maps the results to entities. 
query.addEntity("x", TableXEntity.class);
query.addEntity("y", TableYEntity.class);

query.list()

回答by Pankaj Shinde

java.lang.ClassCastException: "CustomClass" cannot be cast to java.util.Map.

java.lang.ClassCastException: "CustomClass" cannot be cast to java.util.Map.

This issue appears when the columns specified in SQL Query doesn't match with the columns of the mapping class.

当 SQL 查询中指定的列与映射类的列不匹配时，会出现此问题。

It may be due to:

这可能是由于：

• Non-matching casing of column name or

• The column names are not matching or

• column exist in query but missing in class.

• 列名的大小写不匹配或

• 列名不匹配或

• 列存在于查询中，但在类中缺失。

回答by Musa

Writing (exist this type of Challenges working with hibernate)

写作（存在这种使用 hibernate 的挑战）

1. Custom Queries
2. Custom Queries with Optional Parameters
3. Mapping Hibernate Custom query results to Custom class.

1. 自定义查询
2. 带有可选参数的自定义查询
3. 将 Hibernate 自定义查询结果映射到自定义类。

I am not saying about custom EntityRepository interface which extends JpaRepository on SpringBoot which you can write custom Query with @Query -> here you can't write query with optional params e.g. if param is null don't append it in query string. And you can use Criteria api of hibernate but it not recommended in their documentation because of performance issue...

我不是在说自定义 EntityRepository 接口，它在 SpringBoot 上扩展了 JpaRepository，您可以使用 @Query 编写自定义查询 -> 在这里您不能使用可选参数编​​写查询，例如，如果 param 为空，请不要将其附加到查询字符串中。您可以使用 Hibernate 的 Criteria api 但由于性能问题，不建议在他们的文档中使用它...

But exist simple and error prone and performance good way...

但是存在简单且容易出错且性能良好的方法......

Write your own QueryService class which are methods will get string(answer for first and second problem) sql and will map result to Custom class (third problem) with it's any association @OneToMany, @ManyToOne ....

编写您自己的 QueryService 类，这些方法将获取字符串（第一个问题和第二个问题的答案）sql，并将结果映射到自定义类（第三个问题），它具有任何关联 @OneToMany、@ManyToOne ....

@Service
@Transactional
public class HibernateQueryService {

    private final Logger log = LoggerFactory.getLogger(HibernateQueryService.class);
    private JpaContext jpaContext;

    public HibernateQueryService(JpaContext jpaContext) {
        this.jpaContext = jpaContext;
    }

    public List executeJPANativeQuery(String sql, Class entity){
        log.debug("JPANativeQuery executing: "+sql);
        EntityManager entityManager = jpaContext.getEntityManagerByManagedType(Article.class);
        return entityManager.createNativeQuery(sql, entity).getResultList();
    }

/**
 * as annotation @Query -> we can construct here hibernate dialect 
 * supported query and fetch any type of data
 * with any association @OneToMany and @ManyToOne.....
 */
    public List executeHibernateQuery(String sql, Class entity){
        log.debug("HibernateNativeQuery executing: "+sql);
        Session session = jpaContext.getEntityManagerByManagedType(Article.class).unwrap(Session.class);
        return session.createQuery(sql, entity).getResultList();
    }

public <T> List<T> executeGenericHibernateQuery(String sql, Class<T> entity){
    log.debug("HibernateNativeQuery executing: "+sql);
    Session session = jpaContext.getEntityManagerByManagedType(Article.class).unwrap(Session.class);
    return session.createQuery(sql, entity).getResultList();
}


}

Use case - you can write any type condition about query params

用例 - 您可以编写有关查询参数的任何类型条件

 @Transactional(readOnly = true)
    public List<ArticleDTO> findWithHibernateWay(SearchFiltersVM filter){

        Long[] stores = filter.getStores();
        Long[] categories = filter.getCategories();
        Long[] brands = filter.getBrands();
        Long[] articles = filter.getArticles();
        Long[] colors = filter.getColors();

        String query = "select article from Article article " +
            "left join fetch article.attributeOptions " +
            "left join fetch article.brand " +
            "left join fetch article.stocks stock " +
            "left join fetch stock.color " +
            "left join fetch stock.images ";

boolean isFirst = true;

        if(!isArrayEmptyOrNull(stores)){
            query += isFirst ? "where " : "and ";
            query += "stock.store.id in ("+ Arrays.stream(stores).map(store -> store.toString()).collect(Collectors.joining(", "))+") ";
            isFirst = false;
        }

        if(!isArrayEmptyOrNull(brands)){
            query += isFirst ? "where " : "and ";
            query += "article.brand.id in ("+ Arrays.stream(brands).map(store -> store.toString()).collect(Collectors.joining(", "))+") ";
            isFirst = false;
        }

        if(!isArrayEmptyOrNull(articles)){
            query += isFirst ? "where " : "and ";
            query += "article.id in ("+ Arrays.stream(articles).map(store -> store.toString()).collect(Collectors.joining(", "))+") ";
            isFirst = false;
        }

        if(!isArrayEmptyOrNull(colors)){
            query += isFirst ? "where " : "and ";
            query += "stock.color.id in ("+ Arrays.stream(colors).map(store -> store.toString()).collect(Collectors.joining(", "))+") ";
        }

        List<Article> articles = hibernateQueryService.executeHibernateQuery(query, Article.class);


      /**
        *  MapStruct [http://mapstruct.org/][1]
        */
        return articles.stream().map(articleMapper::toDto).collect(Collectors.toList());

    }

回答by Stephen Gray

Below is a result transformer that ignores case:

下面是一个忽略大小写的结果转换器：

package org.apec.abtc.dao.hibernate.transform;

import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.property.access.internal.PropertyAccessStrategyBasicImpl;
import org.hibernate.property.access.internal.PropertyAccessStrategyChainedImpl;
import org.hibernate.property.access.internal.PropertyAccessStrategyFieldImpl;
import org.hibernate.property.access.internal.PropertyAccessStrategyMapImpl;
import org.hibernate.property.access.spi.Setter;
import org.hibernate.transform.AliasedTupleSubsetResultTransformer;

/**
 * IgnoreCaseAlias to BeanResult Transformer
 * 
 * @author Stephen Gray
 */
public class IgnoreCaseAliasToBeanResultTransformer extends AliasedTupleSubsetResultTransformer
{

    /** The serialVersionUID field. */
    private static final long serialVersionUID = -3779317531110592988L;

    /** The resultClass field. */
    @SuppressWarnings("rawtypes")
    private final Class resultClass;
    /** The setters field. */
    private Setter[] setters;
    /** The fields field. */
    private Field[] fields;
    private String[] aliases;

    /**
     * @param resultClass
     */
    @SuppressWarnings("rawtypes")
    public IgnoreCaseAliasToBeanResultTransformer(final Class resultClass)
    {
        if (resultClass == null)
        {
            throw new IllegalArgumentException("resultClass cannot be null");
        }
        this.resultClass = resultClass;
        this.fields = this.resultClass.getDeclaredFields();
    }

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

    /**
     * {@inheritDoc}
     */
    @Override
    public Object transformTuple(final Object[] tuple, final String[] aliases)
    {
        Object result;

        try
        {
            if (this.setters == null)
            {
                this.aliases = aliases;

                setSetters(aliases);
            }
            result = this.resultClass.newInstance();

            for (int i = 0; i < aliases.length; i++)
            {
                if (this.setters[i] != null)
                {
                    this.setters[i].set(result, tuple[i], null);
                }
            }
        }
        catch (final InstantiationException | IllegalAccessException e)
        {
            throw new HibernateException("Could not instantiate resultclass: " + this.resultClass.getName(), e);
        }

        return result;
    }

    private void setSetters(final String[] aliases)
    {
        PropertyAccessStrategyChainedImpl propertyAccessStrategy = new PropertyAccessStrategyChainedImpl(
                                                                                                         PropertyAccessStrategyBasicImpl.INSTANCE,
                                                                                                         PropertyAccessStrategyFieldImpl.INSTANCE,
                                                                                                         PropertyAccessStrategyMapImpl.INSTANCE
                                                                        );

        this.setters = new Setter[aliases.length];
        for (int i = 0; i < aliases.length; i++)
        {
            String alias = aliases[i];
            if (alias != null)
            {
                for (final Field field : this.fields)
                {
                    final String fieldName = field.getName();
                    if (fieldName.equalsIgnoreCase(alias))
                    {
                        alias = fieldName;
                        break;
                    }
                }
                setters[i] = propertyAccessStrategy.buildPropertyAccess( resultClass, alias ).getSetter();
            }
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @SuppressWarnings("rawtypes")
    public List transformList(final List collection)
    {
        return collection;
    }

    @Override
    public boolean equals(Object o) {
        if ( this == o ) {
            return true;
        }
        if ( o == null || getClass() != o.getClass() ) {
            return false;
        }

        IgnoreCaseAliasToBeanResultTransformer that = ( IgnoreCaseAliasToBeanResultTransformer ) o;

        if ( ! resultClass.equals( that.resultClass ) ) {
            return false;
        }
        if ( ! Arrays.equals( aliases, that.aliases ) ) {
            return false;
        }

        return true;
    }

    @Override
    public int hashCode() {
        int result = resultClass.hashCode();
        result = 31 * result + ( aliases != null ? Arrays.hashCode( aliases ) : 0 );
        return result;
    }
}