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