如果使用SQL 通过Hibernate查询数据库,如果SQL中使用了别名,则所有的查询字段必须使用别名,并且使用addScalar方法告诉hibernate使用了那些别名,否则,查询会出错。
StringBuffer sql = new StringBuffer();
sql.append(
"SELECT sc.id as commentId, sc.content AS commentContent, sc.comment_uid as commentatorUId,sc.created_time as commentCreatedTime,")
.append(" geo.region_desc as regionDesc, bmForCommentator.realname AS commentatorRealName, bfForCommentator.avatar_mobile AS commentatorAvatar,")
.append(" sh.content AS shareContent,shc.img_url as shareCoverImgUrl, bmForSharer.realname AS sharerRealName, bfForSharer.avatar_mobile AS sharerAvatar")
.append(" FROM snsec_axis_share_comment sc")
.append(" JOIN snsec_axis_share sh ON (sc.share_id = sh.id AND sh.id = ?)")
.append(" LEFT JOIN snsec_members_geo geo ON (sc.member_geo_id = geo.id)")
.append(" LEFT JOIN bbs_members bmForCommentator ON (sc.comment_uid = bmForCommentator.uid)")
.append(" LEFT JOIN bbs_memberfields bfForCommentator ON (sc.comment_uid = bfForCommentator.uid)")
.append(" LEFT JOIN snsec_axis_share_cover shc ON (sh.uid = shc.uid)")
.append(" LEFT JOIN bbs_members bmForSharer ON (sh.uid = bmForSharer.uid)")
.append(" LEFT JOIN bbs_memberfields bfForSharer ON (sh.uid = bfForSharer.uid)")
.append(" WHERE sc.created_time > ?");
List<Map> resultList = queryWithAlias(sql.toString(), page,
new Object[] { shareId, maxTime });
private List<Map> queryWithAlias(final String sql, final Page page,
final Object[] params) {
StringBuffer sqlForCountReturnResult = new StringBuffer("select count(*) ");
sqlForCountReturnResult.append(sql.substring(sql.toUpperCase().indexOf("FROM")));
Integer totalCount = super.countBySQLArgs(sqlForCountReturnResult.toString(),
params);
page.setRecordCount(totalCount);
if (totalCount == 0) {
return new ArrayList<Map>();
}
return getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
SQLQuery query = session.createSQLQuery(sql);
query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
query.addScalar("commentId", Hibernate.BIG_INTEGER);
query.addScalar("commentContent", Hibernate.STRING);
query.addScalar("commentatorUId", Hibernate.STRING);
query.addScalar("commentCreatedTime", Hibernate.TIMESTAMP);
query.addScalar("regionDesc", Hibernate.STRING);
query.addScalar("commentatorRealName", Hibernate.STRING);
query.addScalar("commentatorAvatar", Hibernate.STRING);
query.addScalar("shareContent", Hibernate.STRING);
query.addScalar("shareCoverImgUrl", Hibernate.STRING);
query.addScalar("sharerRealName", Hibernate.STRING);
query.addScalar("sharerAvatar", Hibernate.STRING);
if (params != null && params.length > 0) {
for (int i = 0; i < params.length; i++) {
query.setParameter(i, params[i]);
}
}
return query.setFirstResult(page.getFirstResult())
.setMaxResults(page.getRows()).list();
}
});
}