Java AliasToBeanResultTransformer 和 Hibernate SQLQuery
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4436419/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
AliasToBeanResultTransformer and Hibernate SQLQuery
提问by elduff
I have a rather complex query (too many nested levels for either HQL or Criteria queries), so I've written it as a SQLQuery. I'd really like to use the AliasToBeanResultTransformer to transform my results into a List, but I'm having some issues. I've included code snippets below of what I've got right now.
我有一个相当复杂的查询（HQL 或 Criteria 查询的嵌套级别太多），因此我将其编写为 SQLQuery。我真的很想使用 AliasToBeanResultTransformer 将我的结果转换为列表，但我遇到了一些问题。我在下面包含了我现在所拥有的代码片段。
When I log the results for the transformed query, I can see that the transformer does create a List, however, all the fields in each AdvancedClauseSearchResultDTO are null. I assume that means that I'm doing something wrong with aliasing ... that the AliasToBeanResultTransformer can't find the correct setters to call. However, the AdvancedClauseSearchResultDTO class does have public setters for each of the columns that I've aliased in my sql string. If this was a Criteria query, I'd use projections to define an alias for each column to be returned, but I'm unsure of how to accomplish the same thing using a SQLQuery.
当我记录转换后的查询的结果时，我可以看到转换器确实创建了一个列表，但是，每个 AdvancedClauseSearchResultDTO 中的所有字段都为空。我认为这意味着我在别名方面做错了...... AliasToBeanResultTransformer 无法找到正确的 setter 来调用。但是，AdvancedClauseSearchResultDTO 类确实为我在 sql 字符串中设置别名的每个列都有公共设置器。如果这是一个 Criteria 查询，我会使用投影为要返回的每一列定义一个别名，但我不确定如何使用 SQLQuery 完成同样的事情。
Any advice on how to get the aliases set so that the ResultTransformer can use them? I've seen some limited documentation that suggested that using the 'as aliasName' method should work, but it doesn't seem to be for me.
关于如何设置别名以便 ResultTransformer 可以使用它们的任何建议？我看到一些有限的文档表明使用 'as aliasName' 方法应该可行，但它似乎不适合我。
Beginning snippet of query string definition, note the 'as' alias definitions
查询字符串定义的开始片段，注意“as”别名定义
StringBuffer clauseBaseQuery = new StringBuffer();
clauseBaseQuery.append("select ");
clauseBaseQuery.append(" clauseDetail.clause_detail_id as clauseDetailId,");
clauseBaseQuery.append(" clauseDetail.clause_id as clauseId,");
clauseBaseQuery.append(" providers.provider_name as provider, ");
clauseBaseQuery.append(" products.product_name as product, ");
SQLQuery creation & setting of resultTransformer
SQLQuery 创建和设置 resultTransformer
Query query = session.createSQLQuery(clauseBaseQuery.toString());
query.setResultTransformer(new AdvancedClauseSearchResultTransformer());
return (List<AdvancedClauseSearchResultDTO>)query.list();
AdvancedClauseSearchResultTransformer class (uses AliasToBeanResultTransformer and then does some extra processing):
AdvancedClauseSearchResultTransformer 类（使用 AliasToBeanResultTransformer 然后进行一些额外的处理）：
class AdvancedClauseSearchResultTransformer implements ResultTransformer {
//Use the aliasTransformer to do most of the work
ResultTransformer aliasTransformer = Transformers.aliasToBean(AdvancedClauseSearchResultDTO.class);
@Override
public List transformList(List list) {
log.debug("transforming CLAUSE results");
List<AdvancedClauseSearchResultDTO> result = aliasTransformer.transformList(list);
//for each row, set the status field
for (AdvancedClauseSearchResultDTO dto : result) {
log.debug("dto = " + dto);
String status = null;
Date effectiveDate = dto.getEffectiveDate();
Date terminationDate = dto.getTerminationDate();
Date now = new Date(System.currentTimeMillis());
if (now.before(effectiveDate)) {
status = "Pending";
} else if (now.after(terminationDate)) {
status = "Terminated";
} else {
status = "Active";
}
dto.setStatus(status);
if (StringUtils.isNotEmpty(dto.getReasonForAmendment())){
dto.setAmended(Boolean.TRUE);
}else{
dto.setAmended(Boolean.FALSE);
}
}
return result;
}
@Override
public Object transformTuple(Object[] os, String[] strings) {
Object result = aliasTransformer.transformTuple(os, strings);
return result;
}
}
采纳答案by elduff
I did some more research on this today, and finally noticed a good stack trace of the underlying error I was getting, and a hibernate forum entry that helped me get past this.
我今天对此进行了更多研究，最终注意到我得到的潜在错误的良好堆栈跟踪，以及帮助我解决此问题的休眠论坛条目。
The Exception I was getting is: Caused by: org.hibernate.PropertyNotFoundException: Could not find setter for CLAUSEDETAILID
我得到的异常是：引起：org.hibernate.PropertyNotFoundException：找不到 CLAUSEDETAILID 的设置器
It appears as if Hibernate is taking my camel case aliases & turning them into all uppercase, so it can't find the matching setters in my AdvancedClauseSearchResultDTO class.
似乎 Hibernate 正在使用我的驼峰式别名并将它们全部转换为大写，因此它无法在我的 AdvancedClauseSearchResultDTO 类中找到匹配的 setter。
Here's the forum entry that pointed me in the right direction:
这是为我指明正确方向的论坛条目：
https://forum.hibernate.org/viewtopic.php?f=1&t=1001608
https://forum.hibernate.org/viewtopic.php?f=1&t=1001608
I ended up using the approach detailed in that post for my own ResultTransformer, and that's working for me.
我最终将那篇文章中详述的方法用于我自己的 ResultTransformer，这对我有用。
回答by zinan.yumak
I think it is not a good method to write a result transformer to solve your problem. Try something like this,
我认为编写结果转换器来解决您的问题不是一个好方法。尝试这样的事情，
Query query = session.createSQLQuery(clauseBaseQuery.toString());
query.setResultTransformer(Transformers.aliasToBean(AdvancedClauseSearchResultDTO.class));
And in AdvancedClauseSearchResultDTO class, modify setter methods to set required fields for you. For example,
在 AdvancedClauseSearchResultDTO 类中，修改 setter 方法为您设置必填字段。例如，
class AdvancedClauseSearchResultDTO {
private Date effectiveDate;
private String status;
.
.
public void getEffectiveDate() {
return effectiveDate;
}
public void setEffectiveDate(Date aDate) {
Date now = new Date(System.currentTimeMillis());
if (now.before(effectiveDate)) {
this.status = "Pending";
} else if (now.after(terminationDate)) {
this.status = "Terminated";
} else {
this.status = "Active";
}
this.effectiveDate = aDate;
}
}
You got the idea...
你有这个想法...
回答by Camilo Díaz Repka
This depends on the backend you're using, which you don't mention in your post.
这取决于您使用的后端，您没有在帖子中提及。
Various DB backends use case insensitive naming for the columns unlessyou properly escape them, so they end being retrieved as CLAUSEDETAILIDor clausedetailid, even when you specify the column result name with the proper case.
除非您正确地对它们进行转义，否则各种数据库后端都会对列使用不区分大小写的命名，因此它们最终会被检索为CLAUSEDETAILIDor clausedetailid，即使您使用正确的大小写指定列结果名称也是如此。
With PostgreSQL (and I believe Oracle, too), you have to write your query like this (note the column quoting):
使用 PostgreSQL（我也相信 Oracle），您必须像这样编写查询（注意列引用）：
StringBuffer clauseBaseQuery = new StringBuffer();
clauseBaseQuery.append("select ");
clauseBaseQuery.append(" clauseDetail.clause_detail_id as \"clauseDetailId\",");
clauseBaseQuery.append(" clauseDetail.clause_id as \"clauseId\",");
clauseBaseQuery.append(" providers.provider_name as \"provider\", ");
clauseBaseQuery.append(" products.product_name as \"product\", ");
Query query = session.createSQLQuery(clauseBaseQuery.toString());
So that will allow Hibernate to properly recognize the property and map the result to a bean, provided you also specfied the tranformation:
因此，这将允许 Hibernate 正确识别属性并将结果映射到 bean，前提是您还指定了转换：
query.setResultTransformer(Transformers.aliasToBean(AdvancedClauseSearchResultDTO.class));
as was suggested by @zinan.yumak.
正如@zinan.yumak 所建议的那样。
回答by Neil
The easiest fix is to put quotation marks for the column alias like:
最简单的解决方法是为列别名添加引号，例如：
select first_name as "firstName" from employee
