以前处理复杂逻辑时,使用过几种不同的方法,但是今天发现了一个hibernate中使用sql更方便的方法,前提是hibernate必须是3.2或者以上版本,废话不说了,下面看介绍:
普通的实体JavaBean(请注意,这个javabean不需要持久化,就是一个普通的JavaBean,只需要将数据库返回的字段对象上,有 set、get方法即可,要求属性名称都变为大写,因为返回的结果字段都是默认大写的,为了对应上javabean的属性)代码如下:
-------------------------------------------------------------------------------
public class ordercount {
private String PHONENAME;
private Integer ORDERCOUNT;
private Integer KEHUCOUNT;
private Integer ORDERALLCOUNT;
private Integer ORDERWANCHENG;
private Integer ORDERTUIDING;
public String getPHONENAME() {
return PHONENAME;
}
public void setPHONENAME(String phonename) {
PHONENAME = phonename;
}
public Integer getORDERCOUNT() {
return ORDERCOUNT;
}
public void setORDERCOUNT(Integer ordercount) {
ORDERCOUNT = ordercount;
}
public Integer getKEHUCOUNT() {
return KEHUCOUNT;
}
public void setKEHUCOUNT(Integer kehucount) {
KEHUCOUNT = kehucount;
}
public Integer getORDERALLCOUNT() {
return ORDERALLCOUNT;
}
public void setORDERALLCOUNT(Integer orderallcount) {
ORDERALLCOUNT = orderallcount;
}
public Integer getORDERWANCHENG() {
return ORDERWANCHENG;
}
public void setORDERWANCHENG(Integer orderwancheng) {
ORDERWANCHENG = orderwancheng;
}
public Integer getORDERTUIDING() {
return ORDERTUIDING;
}
public void setORDERTUIDING(Integer ordertuiding) {
ORDERTUIDING = ordertuiding;
}
}
业务逻辑代码如下:
-----------------------------代码如下---------------------------------------
//较为复杂的sql,注意返回的字段名称都是大写的
String sql= " select t.phonename as PHONENAME, (select count(*) "
+ " from g3_phonescan p "
+ " where p.telephoneid = t.telephoneid) as ORDERCOUNT, "
+ " ( select count(distinct(e.mobile)) "
+ " from g3_phonescan e "
+ " where e.telephoneid = t.telephoneid ) as KEHUCOUNT, "
+ " (select count(w.orderid) from g3_goods w where w.telephoneid=t.telephoneid) as ORDERALLCOUNT, "
+ " (select count(w.orderid) from g3_goods w where w.telephoneid=t.telephoneid and w.state=4) as ORDERWANCHENG, "
+ " (select count(w.orderid) from g3_goods w where w.telephoneid=t.telephoneid and w.state=7) as ORDERTUIDING"
+ " from g3_telephone t";
//生成SQLQuery 对象,请注意是:SQLQuery ,不是Query(一定不能错,因为Query没有addScalar方法)
SQLQuery query = this.getSession().createSQLQuery(sql);
//为每个sql中的查询的字段设置返回的类型,当然这个地方那个不设置也可以,不过如果结果出现返回count()函数的字段就不需设置,不然会报错,最好这个地方还是设置一下类型
query.addScalar("PHONENAME", new org.hibernate.type.StringType());
query.addScalar("ORDERCOUNT", new org.hibernate.type.IntegerType());
query.addScalar("KEHUCOUNT", new org.hibernate.type.IntegerType());
query.addScalar("ORDERALLCOUNT", new org.hibernate.type.IntegerType());
query.addScalar("ORDERWANCHENG", new org.hibernate.type.IntegerType());
query.addScalar("ORDERTUIDING", new org.hibernate.type.IntegerType());
//设置返回的结果集字段映射的java实体(注意这个java实体类是普通的JavaBean,不需要持久化,这也正是我认为的方便之处)
List<ordercount> orderlist = query.setResultTransformer(Transformers.aliasToBean(ordercount.class)).list();
以下为JSP页面用标签获取数据,代码如下:
---------------------------------------------------------------------
<s:iterator value="ordercountlist">
<tr>
<td><s:property value="PHONENAME" /></td>
<td><s:property value="ORDERCOUNT" /></td>
<td><s:property value="KEHUCOUNT" /></td>
<td><s:property value="ORDERALLCOUNT" /></td>
<td><s:property value="ORDERWANCHENG" /></td>
<td><s:property value="ORDERTUIDING"/> </td>
</tr>
</s:iterator>
以上就是所有的代码了,呵呵 我感觉真的是太方便了,以后再碰到复杂的业务逻辑就这样用,很方便!