Java – aliastobeanresulttransformer and hibernate sqlQuery
I have a fairly complex query (HQL or criteria queries have too many nesting levels), so I write it as sqlQuery I really want to use alias tobeanresulttransformer to convert my results to list, but I have some problems I have included code snippets under the code snippets I now have
When I record the results of the converted query, I can see that the converter does create a list, but all fields in each advancedclausesearchresultdto are null I think this means I'm doing something wrong with aliases... Aliastobeanresulttransformer can't find the correct setter to call However, the advancedclausesearchresultdto class does have a public setter for each column I alias in the SQL string If this is a criteria query, I will use projection to define an alias for each column to be returned, but I'm not sure how to use sqlQuery to do the same
Suggestions on how to set aliases so that resulttransformer can use them? I've seen some limited documentation indicating that the 'as alias name' method should be effective, but it doesn't seem to suit me
For the beginning fragment of the query string definition, please note the 'as' alias definition
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,");
Setting of sqlQuery creation & resulttransformer
Query query = session.createsqlQuery(clauseBaseQuery.toString()); query.setResultTransformer(new AdvancedClauseSearchResultTransformer()); return (List<AdvancedClauseSearchResultDTO>)query.list();
Advancedclausesearchresulttransformer class (use aliastobeanresulttransformer and then perform some additional processing):
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; } }
Solution
It depends on the backend you use, which you didn't mention in your post
Unless you escape them correctly, various database back ends will use case insensitive names for columns, so they will eventually be retrieved as clausedetailid or clausedetailid, even if you specify the column result name with the correct case
Using PostgreSQL (I also believe in Oracle), you must write your query like this (pay attention to column references):
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());
In this way, hibernate can correctly identify the attribute and map the result to the bean, provided that you also specify the transformation:
query.setResultTransformer(Transformers.aliasToBean(AdvancedClauseSearchResultDTO.class));
As @ Zinan As yumak suggested