AliasToBeanResultTransformer and Hibernate SQLQuery

Question

I have a rather complicated query (there are too many nested levels for HQL or Criteria queries), so I wrote it as SQLQuery. I would really like to use AliasToBeanResultTransformer to convert my results to a list, but I am having some problems. I have included code snippets below what I have right now.

When I log the results for a converted query, I see that the transformer is actually creating a list, however all the fields in each AdvancedClauseSearchResultDTO are null. I guess this means that I'm doing something wrong with aliasing ... that AliasToBeanResultTransformer cannot find the correct setters for the call. However, the AdvancedClauseSearchResultDTO class has public setters for each of the columns that I used in my sql row. If it was a Criteria query, I would use predictions to define an alias for each column to be returned, but I'm not sure how to accomplish the same thing using SQLQuery.

Any tips on how to get the aliases set to use ResultTransformer? I saw some limited documentation saying that using a method like aliasName should work, but it doesn't seem to be for me.

Beginning of the query string definition snippet, pay attention to the "like" definitions of the alias

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, ");

Creating SQLQuery and tuning 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

, , , .

, , : : org.hibernate.PropertyNotFoundException: setter CLAUSEDETAILID

, Hibernate , AdvancedClauseSearchResultDTO.

, :

https://forum.hibernate.org/viewtopic.php?f=1&t=1001608

, , ResultTransformer, .

Answer 2

, .

, , CLAUSEDETAILID CLAUSEDETAILID, .

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());

, Hibernate bean, :

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

@zinan.yumak.

Answer 3

, . - ,

Query query  = session.createSQLQuery(clauseBaseQuery.toString());
query.setResultTransformer(Transformers.aliasToBean(AdvancedClauseSearchResultDTO.class));

And in the AdvancedClauseSearchResultDTO class, change the setting methods to set the required fields for you. For instance,

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

Do you have an idea ...

Answer 4

The easiest solution is to put quotes for the column alias, for example:

select first_name as "firstName" from employee