hibernate之sql查询实体映射

1、“强”表间关系实体映射

主要是指设置了外键或其他关联键的相关表，po内已经加入了关联的实体成员，hbm中也配置了many-to-one、one-to-one等

此类表的查询上经常会采用hql的查询写法，这种映射直接使用hql相对很容易

如po UserInfo包含User成员，查询即可如下写法：

StringBuffer sb = new StringBuffer("from UserInfo u where u.id=" + uid);
Session session = this.getSession();
Query query = session.createQuery(sb.toString());
recordList = query.list();
this.releaseSession(session);

hql的映射方式相对比较容易，这里不做过多说明

2、“弱”表间关系实体映射

各表间存在关联键，但是并没有设置外键关联，那么这类表对应出的po就只包含单一属性(字段)成员

hbm中也只配置字段与成员的映射，不加入 many-to-one、one-to-one等

这类表的sql语句经常需要采用原生sql的写法，那应该如何更容易的映射给bean？

一般都会想到方案一的处理方式：

方案一：

一般的方式是查询返回数组列表(或者map列表)，然后遍历列表在手动一一映射(set/get)给bean

List<?> recordList = null;
StringBuffer sb = new StringBuffer(
  "select t.id.t.username,t.logindate,t.regdate");
sb.append(",t2.tbl_attr1,t2.tbl_attr2,,t2.tbl_attr3");
sb.append(" from tbl_name t,tbl_name2 t2");
sb.append(" where t.id<10");
sb.append(" and t2.uid=t.id");
Session session = this.getSession();
SQLQuery sqlQuery = session.createSQLQuery(sb.toString());
List<Object[]> list = sqlQuery.list();
this.releaseSession(session);
if(list!=null){
 recordList=new ArrayList<Tbl1Object>();
 for(Object[] objArr : list){
  Integer id = (Integer) objArr[0];
  String username = (String) objArr[1];
  ...
  //get objArr[n]
  
  Tbl1Object obj=new Tbl1Object();
  //obj.set
  ...
  recordList.add(obj);
 }
}
return recordList;

最近本人做项目(数据库未设置任何外键，hbm也未加任何关联)中主要负责前台工作，开始也是采用的方案一处理方式，由于界面经常调整，导致数据库也经常的加一些字段；比如：1）界面上之前没显示性别，现在要显示性别；2）数据库之前没有年龄，现在要加年龄字段；最后导致我的n多sql需要重新检查

弄得很是无语，不得不琢磨着换种方式，于是就有了方案2 的诞生

方案二：

主要思路是为每个模块建立“伪视图”数据bean，包含该模块涉及的数据

如：

Auction模块，相关的表po（Auction、ProductType、ProductOrigin、Oper）

1、建立Auction模块的“伪视图”bean  AuctionForgedView

public class AuctionForgedView{
	private int bidpriceTimes;
	private String newBidprice;

	private Auction auction;
	private ProductType productType;
	private ProductOrigin productOrigin;
	private Oper oper;
	//get/set
	...

2、将所有查询映射给AuctionForgedView

1）提取auction、productType、productOrigin、newBidprice、bidpriceTimes

List<AuctionForgedView> recordList = null;
StringBuffer sb = new StringBuffer();
sb.append(" select {auction.*},{productType.*},{productOrigin.*},");
sb.append(" (select top 1 t2.bidprice from dbo.auction_bidprice t2 where t2.auction_id=auction.id order by t2.biddate desc) as newBidprice,");
sb.append(" (select count(*) from auction_bidprice t where t.auction_id=auction.id) as bidpriceTimes");
sb.append(" from auction auction ");
sb.append(" left join  product_type productType ");
sb.append(" on  auction.pro_type_id = productType.id ");
sb.append(" left join  product_origin productOrigin ");
sb.append(" on  auction.orign_id = productOrigin.id ");
sb.append(" where auction.audit_flag='1'");
sb.append(" and auction.status<>'3'");
sb.append(" order by bidpriceTimes desc,auction.startdate desc ");
Session session = this.getSession();
SQLQuery sqlQuery = session.createSQLQuery(sb.toString());
sqlQuery.setMaxResults(count);

sqlQuery.setResultTransformer(Transformers
		.aliasToBean(AuctionForgedView.class));
sqlQuery.addScalar("newBidprice", Hibernate.STRING);
sqlQuery.addEntity("auction", Auction.class);
sqlQuery.addEntity("productType", ProductType.class);
sqlQuery.addEntity("productOrigin", ProductOrigin.class);
recordList = sqlQuery.list();

this.releaseSession(session);

2）提取auction、newBidprice、bidpriceTimes

List<AuctionForgedView> recordList = null;
StringBuffer sb = new StringBuffer();
sb.append(" select {auction.*},");
sb.append(" (select top 1 t2.bidprice from dbo.auction_bidprice t2 where t2.auction_id=auction.id order by t2.biddate desc) as newBidprice,");
sb.append(" (select count(*) from auction_bidprice t where t.auction_id=auction.id) as bidpriceTimes");
sb.append(" from auction auction ");
if (auction.getProTypeId() == null) {
	sb.append(" where auction.pro_type_pid=" + auction.getProTypePid());
} else {
	sb.append(" where auction.pro_type_id=" + auction.getProTypeId());
}
sb.append(" and auction.id<>" + auction.getId());
sb.append(" order by auction.id desc ");
Session session = this.getSession();
SQLQuery sqlQuery = session.createSQLQuery(sb.toString());
sqlQuery.setMaxResults(count);

sqlQuery.setResultTransformer(Transformers
		.aliasToBean(AuctionForgedView.class));
sqlQuery.addScalar("newBidprice", Hibernate.STRING);
sqlQuery.addScalar("bidpriceTimes", Hibernate.INTEGER);
sqlQuery.addEntity("auction", Auction.class);
recordList = sqlQuery.list();

this.releaseSession(session);

另，分页时的总条数的处理：