AliasToBeanResultTransformer and Hibernate SQLQuery

Question

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.

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.

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.

Beginning snippet of query string definition, note the 'as' alias definitions

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

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

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

Answer 1

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 unless you properly escape them, so they end being retrieved as CLAUSEDETAILID or clausedetailid, even when you specify the column result name with the proper case.

With PostgreSQL (and I believe Oracle, too), you have to write your query like this (note the column quoting):

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:

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

as was suggested by @zinan.yumak.