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_ <= ?