-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Error: Spring-Hibernate: Criteria with SqlProjection + Order
PostPosted: Tue Aug 30, 2011 5:14 am 
Newbie

Joined: Tue Aug 30, 2011 4:27 am
Posts: 2
Hi, I' work with hibernate 3.6.1 and srping 3.0.5
I have a problem doing a consultation mixing SqlProjection, order and Pagination
Mi error is: org.springframework.orm.hibernate3.HibernateQueryException: could not resolve property: nameBankFather of:
com.work.model.entities.banks.Bank; nested exception is org.hibernate.QueryException:
could not resolve property: nameBankFather of: com.empresa.model.entities.bancos.Bank
when order by nameBankFather
From already thank you very much

Code:
Code:
@Entity
@Table(name="BANK", schema="WORK")
public class Bank  implements java.io.Serializable {
    /** serialVersionUID */
private static final long serialVersionUID = 306698605262976491L;
private Integer idBank;
private Country country;
private String name;
   
public Bank() {}
public Bank(Integer idBank, String name, Country country) {
    this.idBank = idBank;
    this.name = name;
    this.country = country;
}

@Id
@Column(name="ID_BANK", unique=true, nullable=false)
public Integer getIdBank() {
   return this.idBank;
}

@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="ID_COUNTRY")
public Country getCountry() {
   return this.country;
}

@Column(name="NAME", nullable=false, length=80)
public String getName() {
   return this.name;
}
}

and your CTO..
public class FindBankCTO implements java.io.Serializable {
/** serialVersionUID */
private static final long serialVersionUID = 2653100208688226855L;
/** idBank */
private Integer idBank;
/** name */
private String name;
/** idCountry */
private Integer idCountry;
/** nameCountry */
private String nameCountry;
/** nameBankFather */
private String nameBankFather;

//with setters and getters
...
}

// Dao code

@Override
public List<FindBankCTO> findBank(Integer idBankParam,
   String nameBank, Integer[] listCountry) {
final DetachedCriteria query = DetachedCriteria.forClass(Bank.class,"b");
query.createAlias("country", "p", DetachedCriteria.INNER_JOIN);

//Add Filters
query.add(Restrictions.eq("idBank", idBankParam));
query.add(Restrictions.ilike("name", nameBank, MatchMode.ANYWHERE));
bancoCriteria.add(Restrictions.in("p.idCountry", listCountry));

String sqlBankPadre = 
  "(SELECT GF.DESCRIPTION " +
  " FROM GRUOP_FINANCIER GF, " +
  "      BANK_GRUOP_FINANCIER BGF " +
  " WHERE GF.ID_GRUOP_FINANCIER = BGF.ID_GRUOP_FINANCIER " +
  "   AND BGF.ID_BANK = {alias}.ID_BANK " +
  " ) as nameBankFather";
      
//Projections
query.setProjection(Projections.projectionList()
   .add(Projections.property("b.idBank"), "idBank")
   .add(Projections.property("b.name"), "name")
   .add(Projections.property("p.idCountry"), "idCountry")
   .add(Projections.property("p.descripcion"), "nameCountry")
   .add(Projections.sqlProjection(sqlBankPadre,
      new String[]{"nameBankFather"},
      new Type[] { StringType.INSTANCE })));

//Transformer to CTO            
query.setResultTransformer(Transformers.aliasToBean(FindBankCTO.class));
List<FindBankCTO> listBankCTO = (List<FindBankCTO>) getHibernateTemplate().execute(
   new HibernateCallback() {
      public List<FindBankCTO> doInHibernate(final Session session) throws SQLException {
        final CriteriaImpl hibernateCriteriaImpl = (CriteriaImpl) query.getExecutableCriteria(session);
        hibernateCriteriaImpl.setFirstResult(0).setMaxResults(50);
        //Add Order
        String ordenBy = "nameBankFather";
        hibernateCriteriaImpl.addOrder(Order.asc(ordenBy));
        return hibernateCriteriaImpl.list();
      }
   });
  return listBankCTO;
}

--Sql generate Hibernate when the order by "name"
select * from (
  select rownumber() over(order by this_.NAME asc) as rownumber_,
   this_.ID_BANK as y0_,
   this_.NAME as y1_,
   p1_.ID_PAIS as y4_,
   p1_.DESCRIPTION as y5_,
   (SELECT GF.DESCRIPTION
    FROM GRUOP_FINANCIER GF,
         BANK_GRUOP_FINANCIER BGF
   WHERE GF.ID_GRUOP_FINANCIER = BGF.ID_GRUOP_FINANCIER
   AND BGF.ID_BANK = this_.ID_BANK) as nameBankFather,
  from WORK.BANK this_
  inner join WORK.COUNTRY p1_ on this_.ID_COUNTRY=p1_.ID_COUNTRY
  where p1_.ID_COUNTRY in (?)
  order by this_.NAME asc ) as temp_
where rownumber_ <= ?


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.