AliasToBeanResultTransformer and Hibernate SQLQuery

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 resultTran开发者_运维百科sformer

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

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.

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

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.

Here's the forum entry that pointed me in the right direction:

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.

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,

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...

The easiest fix is to put quotation marks for the column alias like:

select first_name as "firstName" from employee