环境: hibernate+spring
1.如果sql语句写在映射文件里,如下:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
Mapping file autogenerated by MyEclipse - Hibernate Tools
-->
<hibernate-mapping>
<class name="com.jgcgov.model.Contract" table="CONTRACT" schema="JGCUSER">
<id name="contractNo" type="string">
<column name="CONTRACT_NO" length="11" />
<generator class="assigned" />
</id>
............
<set name="TbTaxpayment2104Add" table="TB_TAXPAYMENT_2104_ADD" cascade="all">
<key column="CONTRACT_NO"></key>
<one-to-many class="com.jgcgov.model.TbTaxpayment2104Add" />
</set>
</class>
<!-- 查询合同的计税金额百分比-->
<sql-query name="contractPrealarm">
<![CDATA[
select con.CONTRACT_NO as contractNo,con.REGISTER_DATE as registerDate,
con.CONSTRUCT_UINT as constructUint,trunc(con.WORKLOAD*10000,2) as workload,
trunc(con1.TaxNum,2) as taxnum,trunc(con1.conPercent,1) as conPercent,
null as attribute7,null as attribute8,null as attribute9,null as attribute10
from CONTRACT con,(
select con.CONTRACT_NO as contractNo,sum(nvl(tb2104.TAX_BASE,0)/(con.WORKLOAD*100)) as conPercent,
sum(nvl(tb2104.TAX_BASE,0)) as TaxNum
from CONTRACT con ,Jgctax.TB_TAXPAYMENT_2104_ADD tb2104
where con.REGISTER_DATE >= :conBeg
and con.REGISTER_DATE <= :conEnd
and con.CONTRACT_NO = tb2104.contract_No(+)
and nvl(tb2104.OP_DATE,to_date('1900-01-01','yyyy-MM-dd')) <= :taxDate
and nvl(con.WORKLOAD,0)<>0
group by con.CONTRACT_NO
having sum(nvl(tb2104.TAX_BASE,0)/(con.WORKLOAD*100)) >= :percent) con1
where con.CONTRACT_NO=con1.contractNo
]]>
<return alias="con" class="com.jgcgov.model.HibernateSqlResult">
<return-property name="attribute1" column="contractNo" />
<return-property name="attribute2" column="registerDate" />
<return-property name="attribute3" column="constructUint" />
<return-property name="attribute4" column="workload" />
<return-property name="attribute5" column="taxnum" />
<return-property name="attribute6" column="conPercent" />
<return-property name="attribute7" column="attribute7" />
<return-property name="attribute8" column="attribute8" />
<return-property name="attribute9" column="attribute9" />
<return-property name="attribute10" column="attribute10" />
</return>
</sql-query>
</hibernate-mapping>
可以这样分页:
/***************************************************************************
* * 函数名: getPrealarm * 输 入: conBeg,conEnd,taxDate,operatorStr,percent *
* conBeg---合同起始时间 * conEnd---合同结束时间 * taxDate---计税截止时间 * operatorStr---运算符 *
* percent---计税占合同额百分比 * 输 出: List * 功能描述: 得到计税占合同额百分比 * 全局变量: * 调用模块: * 作者:
*
**************************************************************************/
}
/**分页操作
* 使用hql 语句进行操作
* @param offset
* @param length
* @return List
*/
public List getListForPage(int offset,int length,Date conBeg, Date conEnd, Date taxDate,
Float percent) {
final int offset3 = offset;
final int length3 = length;
final Date conBeg3 = conBeg;
final Date conEnd3 = conEnd;
final Date taxDate3 = taxDate;
final Float percent3 = percent;
List list = getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Query query = session.getNamedQuery("contractPrealarm").setFirstResult(offset3).setMaxResults(length3);
query.setDate(0,conBeg3);
query.setDate(1,conEnd3);
query.setDate(2,taxDate3);
query.setFloat(4,percent3.floatValue());
List list = query.list();
return list;
}
});
return list;
}
/**
* 获得记录的总个数
*/
public int getCountForPage(){
List list = getHibernateTemplate().find(hql3);
int count = ((Integer)list.get(0)).intValue();
return count;
}
/**
* 获得分页类的对象
* SimplePager(显示内容,记录总个数,页面最大个数,当前页)
*/
public Pageable getPageable(int offset,int length,Date conBeg, Date conEnd, Date taxDate,
Float percent,int currentPage){
final int offset1 = offset;
final int length1 = length;
final int currentPage1 = currentPage;
final int count = getCountForPage();
List list = getListForPage(int offset,int length,Date conBeg, Date conEnd, Date taxDate,
Float percent);
Pageable pageable = new SimplePager(list,count,length1,currentPage1);
return pageable;
}
2.直接写在java类中,则可以用session的creatQuery()进行分页,具体如下:
private static String hql3 = "select count(*) from Jgcuser as user order by user.id ";
private static String hql4 = "select u from Jgcuser as u order by u.id ";
/**分页操作
* 使用hql 语句进行操作
* @param offset
* @param length
* @return List
*/
public List getListForPage(int offset,int length) {
final int offset3 = offset;
final int length3 = length;
List list = getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
List list = session.createQuery(hql4).setFirstResult(offset3).setMaxResults(length3).list();
return list;
}
});
return list;
}
/**
* 获得记录的总个数
*/
public int getCountForPage(){
List list = getHibernateTemplate().find(hql3);
int count = ((Integer)list.get(0)).intValue();
return count;
}
/**
* 获得分页类的对象
* SimplePager(显示内容,记录总个数,页面最大个数,当前页)
*/
public Pageable getPageable(int offset,int length,int currentPage){
final int offset1 = offset;
final int length1 = length;
final int currentPage1 = currentPage;
final int count = getCountForPage();
List list = getListForPage(offset1,length1);
Pageable pageable = new SimplePager(list,count,length1,currentPage1);
return pageable;
}