Ibatis分页 实例(转载)

Ibatis分页  实例
查询分页ibtais  SQL语句 支持模糊查询后分页

分页查询需要两条SQL语句

第一部查询所有记录总数
  <select id="selectForCount-SysParam" resultClass="int">
      <![CDATA[
          SELECT count(*) from PUB_SYSPARAM
<!--下面动态构成查询条件，以及表名，可自行更改-->
      FROM SM_Employee
]]>
<dynamic prepend="where">
<isNotEmpty prepend="and" property="employeeCode">
EmployeeCode like '%$ employeeCode$ %'
</isNotEmpty>
<isNotEmpty prepend="and" property="employeeName">
EmployeeName like '%$ employeeName$ %'
</isNotEmpty>
</dynamic>
</select>

分页查询 ，此SQL分页，采用3层嵌套查询。
用户之需要修改表名与字段名，和最里层（红字）的代码即可。（rownum rn）蓝色字体是所必须得。
此处传入的是一个MAP对象 MAP对象由 bean
  <!--  查询分页 -->
  <select id="selectForPage-SysParam" resultClass="sysParam" parameterClass="hashmap">
    <![CDATA[
      SELECT *
      FROM (SELECT row_.*, rownum rownum_ FROM(SELECT  PUB_SYSPARAM.*,rownum rn from PUB_SYSPARAM 
      ]]>
          <![CDATA[
              ) row_ WHERE rownum <=#end#)WHERE rownum_ >#start#
            ]]>
                <dynamic prepend="where">
<isNotEmpty prepend="and" property="employeeCode">
EmployeeCode like '%$ employeeCode$ %'
</isNotEmpty>
<isNotEmpty prepend="and" property="employeeName">
EmployeeName like '%$ employeeName$ %'
</isNotEmpty>
</dynamic>

<![CDATA[
) row_ WHERE rownum <= #end#) WHERE rownum_ > #start#
]]>
</select> 


以下为源代码
package com.exeerp.pub.util;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.springframework.orm.ibatis.SqlMapClientTemplate;
/**
* ibatis 分页组件 
* 
* 测试版1.1
*  */
public class Paginated {
/**
* 得到sqlClient模版
*/
private SqlMapClientTemplate sqlClient;
/**
* 分页action URL地址
*/
private String url;
/**
* 页面显示条数
*/
private int pageSize;
/**
* 当前页面树
*/
private int currentPage;
/**
* 总记录数
*/
private int recordSum;
/**
* 页面总数
*/
private int pageSum;
/**
* 数据取读起点
*/
private int start;
/**
* 终点
*/
private int end;
/**
* 记录集对象
*/
private List<Object> recordList;
/**
* 分页对象
*/
private Object obj;
/**
* bean 数组对象
*/
private Object[] object;
/**
* 封装
*/
private Map<Object, Object> map;
/**
* 上下页
*/
private StringBuffer toHtml;
/**
* 查询总记录数sql名
*/
private String sqlCount;
/**
* 分页sql名
*/
private String sqlPage;

/**
* 构造函数
* @param sqlClient sqlMapClient 模版
* @param obj 查询对象
* @param currentPage 当前页面
* @param pageSize 页面大小
* @param url action地址
*/
public Paginated(SqlMapClientTemplate sqlClient, Object obj,
int currentPage, int pageSize, String url) {
this.sqlClient = sqlClient;
this.obj = obj;
this.currentPage = currentPage;
this.url = url;
this.pageSize = pageSize;
init();
execute();
}

/**
* 构造函数
* 
* @param sqlClient
*            sqlMapClient 模版
* @param currentPage
*            当前页面
* @param pageSize
*            分页每页显示数目
* @param url
*            action 连接地址
* @param object
*            可以传入0个或多个对象（对应多对多的分页情况） 第一个对象为要查询的对象 其他为辅助
*/
public Paginated(SqlMapClientTemplate sqlClient, int currentPage,
int pageSize, String url, Object... object) {
this.sqlClient = sqlClient;
this.object = object;
this.currentPage = currentPage;
this.url = url;
this.pageSize = pageSize;
init(); // 初始化
}

/**
* 初始化信息
*/
private void init() {
try {
if (obj != null) {
objToMap();
} else {
obj2Map();
}
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchMethodException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

/**
* 执行分页
*/
public void execute() 
{
try
{
pageInfo();
toHtml();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

/**
* 执行bean 对象封装 封装成map
*/
private void obj2Map() throws IllegalArgumentException,
IllegalAccessException, InvocationTargetException,
SecurityException, NoSuchMethodException {
map = new HashMap<Object, Object>();
for (Object objMap : object) {
Class clazz = null;
Method method;
String fieldNames[];
clazz = objMap.getClass();
Field[] fields = clazz.getDeclaredFields();
fieldNames = new String[fields.length];
for (int i = 0; i < fields.length; i++) {
fieldNames = fields.getName();
}
for (int i = 0; i < fields.length; i++) {
String methodName = "get"
+ Character.toUpperCase(fieldNames.charAt(0))
+ fieldNames.substring(1);
method = clazz.getMethod(methodName, new Class[] {});
if (method.invoke(objMap, null) != null
&& !"".equals(method.invoke(objMap, null))) {
map.put(fieldNames, method.invoke(objMap, null));
}
}
}
}

/**
* 执行bean 对象封装 封装成map 
*/
private void objToMap() throws SecurityException, NoSuchMethodException,
IllegalArgumentException, IllegalAccessException,
InvocationTargetException {
map = new HashMap<Object, Object>();
Class clazz = null;
Method method;
String fieldNames[];
clazz = obj.getClass();
Field[] fields = clazz.getDeclaredFields();
fieldNames = new String[fields.length];
for (int i = 0; i < fields.length; i++) {
fieldNames = fields.getName();
}
for (int i = 0; i < fields.length; i++) {
String methodName = "get"
+ Character.toUpperCase(fieldNames.charAt(0))
+ fieldNames.substring(1);
method = clazz.getMethod(methodName, new Class[] {});
if (method.invoke(obj, null) != null
&& !"".equals(method.invoke(obj, null))) {
map.put(fieldNames, method.invoke(obj, null));
}
}
}

/**
* 页面信息的计算
*/ 
private void pageInfo() throws SQLException 
{
if (sqlCount == null) 
{
if (obj == null)//分页对象为空
{
sqlCount = "selectForCount-"
+ object[0].getClass().getSimpleName();

} 
else 
{
sqlCount = "selectForCount-" + obj.getClass().getSimpleName();
}
}
//查询总记录数
this.recordSum = (Integer) sqlClient.queryForObject(sqlCount, map);
if (currentPage <= 0) {
currentPage = 1;
}
if (pageSize < 5) {
pageSize = 5;
}
this.pageSum = (recordSum + pageSize - 1) / pageSize;
if (currentPage > pageSum) {
currentPage = pageSum;
}
//进行翻页时序号数控制。
this.start = (currentPage - 1) * pageSize;
this.end = currentPage * pageSize;
}

/**
* 生成上下页
*/
private void toHtml() 
{
toHtml = new StringBuffer();
String tempUrl = "";
Iterator iterator = map.entrySet().iterator();
String entryName;
if (obj != null) 
{
entryName = obj.getClass().getSimpleName();
} else {
entryName = object[0].getClass().getSimpleName();
}
entryName = Character.toLowerCase(entryName.charAt(0))
+ entryName.substring(1);
while (iterator.hasNext()) {
Map.Entry entry = (Entry) iterator.next();
tempUrl += entryName + "." + entry.getKey().toString() + "="
+ entry.getValue().toString() + "&";
}
tempUrl = tempUrl + "currentPage=";
toHtml.append("共" + recordSum + "条记录  " + "当前是第" + currentPage
+ "页    共" + pageSum + "页");
if (currentPage > 1 && pageSum >= 2)//在当前页面大于1和总页面数大于2时才显示首页
{
toHtml.append("<a href='" + url + "?" + tempUrl + "1'>首页</a>  ");
}
if (currentPage > 1) //在当前页面大于1显示上一页
{
toHtml.append("<a href='" + url + "?" + tempUrl + (currentPage - 1)
+ "'>上一页</a>  ");
}
if (currentPage < pageSum && currentPage > 0) //在当前页面小于总页数，且当前页面大于零时，显示下一页。
{
toHtml.append("<a href='" + url + "?" + tempUrl + (currentPage + 1)
+ "'>下一页</a>  ");
}
if (currentPage < pageSum) //在当前页面大于总页数时显示尾页
{
toHtml.append("<a href='" + url + "?" + tempUrl + pageSum
+ "'>尾页</a>  ");
}
toHtml.append("<script>");
toHtml.append("function goto(){");
toHtml.append("var currentpage=document.getElementById('currentPage').value;");
toHtml.append("window.location.href='"+url+"?"+tempUrl+"'+currentpage;");
toHtml.append("}");
toHtml.append("function num(){");
toHtml.append("return ((event.keyCode >= 48) && (event.keyCode <= 57));");
toHtml.append("}");
toHtml.append("</script>");
//选择页面进行转页
toHtml.append(" 转到第<input type='text' name='currentPage' id='currentPage' value='"+currentPage+"'size='2' onkeypress='event.returnValue=num();'>页 <input type='button' value='跳转'onclick='goto()'>");
}

/**
* 取读记分页后录集
* @return
*/
public List getRecordList() 
{
map.put("start", start);
map.put("end", end);
if (sqlPage == null)
{
if (obj == null) 
{
sqlPage = "selectForPage-"
+ object[0].getClass().getSimpleName();
} else {
sqlPage = "selectForPage-" + obj.getClass().getSimpleName();
}
}
this.recordList = sqlClient.queryForList(sqlPage, map);
return recordList;
}

public int getPageSize() {
return pageSize;
}

public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}

public int getCurrentPage() {
return currentPage;
}

public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}

public int getPageSum() {
return pageSum;
}

public void setPageSum(int pageSum) {
this.pageSum = pageSum;
}

public Object getObj() {
return obj;
}

public void setObj(Object obj) {
this.obj = obj;
}

public int getRecordSum() {
return recordSum;
}

public void setRecordSum(int recordSum) {
this.recordSum = recordSum;
}

public StringBuffer getToHtml() {
return toHtml;
}

public String getSqlCount() {
return sqlCount;
}

public void setSqlCount(String sqlCount) {
this.sqlCount = sqlCount;
}

public String getSqlPage() {
return sqlPage;
}

public void setSqlPage(String sqlPage) {
this.sqlPage = sqlPage;
}

}
Action类：
package com.exeerp.sm.ctrl;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import org.apache.struts2.ServletActionContext;
import com.exeerp.pub.action.BaseAction;
import com.exeerp.pub.exception.BizException;
import com.exeerp.pub.exception.DaoException;
import com.exeerp.pub.util.ExceptionMessage;
import com.exeerp.pub.util.Paginated;
import com.exeerp.sm.bean.SysParam;
import com.exeerp.sm.biz.SysParamManager;
import com.opensymphony.xwork2.ActionContext;
import com.exeerp.pub.dao.SwitchDao;
/**
* Action类 调用系统参数业务层的方法。
* @author： 
* 创建时间：2009-04-30
*/
public class SysParamAction extends BaseAction 
{
//日志对象
static Logger logger = Logger.getLogger(SysParamAction.class);
//业务层SysParamManager对象
private SysParamManager sysParamManager;
private SysParam sysParam;
//设置当前页
private int currentPage=1;
//分页对象
private Paginated paging; 
private List<SysParam> list;

/** 查询所有的系统参数信息
* @return 如果更新成功，则返回SUCCESS
*/
public String GetPage()
{
try 
{
if(sysParam==null)
{
sysParam=new SysParam();
}
//调用业务层的获取所有页并进行分页的数据
paging = sysParamManager.getAllSysParamMgr(sysParam,20,currentPage,"sm/sm_sysParam/getAllSysParam.action"); //分别传递分页对象，页面记录数，当前页面数，和要访问的url地址
list = (List<SysParam>)(paging.getRecordList());//获取对象并进行保存
    //保存获得的当前的数据
this.getRequest().setAttribute("list", list);
    return SUCCESS;
}

catch (BizException e) 
{
logger.error(e.getMessage());
e.printStackTrace();
this.setMessage(e.getMessage());
return INPUT;
}
catch (DaoException e)
{
logger.error(e.getMessage());
this.setMessage(e.getMessage());
return INPUT;
} 
catch (Exception e) 
{
e.printStackTrace();
logger.error(ExceptionMessage.getMessage("common.serverException"));
this.setMessage(e.getMessage());
return INPUT;
}
}
public int getCurrentPage() 
{
return currentPage;
}

public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}

public Paginated getPaging() {
return paging;
}

public void setPaging(Paginated paging)
{
this.paging = paging;
}

public SysParamManager getSysParamManager() {
return sysParamManager;
}

public void setSysParamManager(SysParamManager sysParamManager) {
this.sysParamManager = sysParamManager;
}

public SysParam getSysParam() {
return sysParam;
}

public void setSysParam(SysParam sysParam) {
this.sysParam = sysParam;
}
public List<SysParam> getList() {
return list;
}
public void setList(List<SysParam> list) {
this.list = list;
}
}

3.Ibetis文件设置：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
  "http://ibatis.apache.org/dtd/sql-map-2.dtd">
                      <!--  系统参数sql语句 -->
<sqlMap>
  <typeAlias alias="sysParam" type="com.exeerp.sm.bean.SysParam"/>

  <!--  查询分页 -->
  <select id="selectForPage-SysParam" resultClass="sysParam" parameterClass="hashmap">
    <![CDATA[
      SELECT *
      FROM (SELECT row_.*, rownum rownum_ FROM(SELECT  PUB_SYSPARAM.*,rownum rn from PUB_SYSPARAM 
      ]]>
          <![CDATA[
              ) row_ WHERE rownum <=#end#)WHERE rownum_ >#start#
            ]]>
  </select>
  <!-- 系统参数模糊查询记录数 -->
  <select id="selectForCount-SysParam" resultClass="int" >
      <![CDATA[
          SELECT count(*) from PUB_SYSPARAM
      ]]>
        
  </select>
  <!-- 查询所有系统参数 -->
  <select id="select-SysParam" resultClass="sysParam" parameterClass="sysParam">
      <![CDATA[
      SELECT *
      FROM (SELECT row_.*, rownum rownum_ FROM(SELECT  PUB_SYSPARAM.*,rownum rn from PUB_SYSPARAM 
      ]]>
  </select>
</sqlMap>

Struts.xml配置：

<!-- 系统参数管理 -->
  <package name="com.exeerp.sm.ctrl.SysParamAction" extends="struts-default">
<!-- 查询所有的系统参数 -->
<action name="getAllSysParam" class="com.exeerp.sm.ctrl.SysParamAction"
method="GetPage">
<result name="success">/sm/sysparam/SysParamManage.jsp</result>
<result name="input">/sm/sm_sysPa</result>
</action>
</package>


5.业务方法和实现：
package com.exeerp.sm.biz;
import java.util.List;
import com.exeerp.pub.biz.BaseManager;
import com.exeerp.pub.exception.BizException;
import com.exeerp.pub.exception.DaoException;
import com.exeerp.pub.util.Paginated;
import com.exeerp.sm.bean.SysLog;
import com.exeerp.sm.bean.SysParam;
/**
* 系统参数业务层接口
* @author： 
* 创建时间:2009-04-30
*/
public interface SysParamManager extends BaseManager {

/**
* 查询所有的系统参数信息
* @return 
* @throws DaoException
*/
public Paginated getAllSysParamMgr(SysParam sysLog,int pageSize,int currentPage,String url)throws DaoException,BizException;

}

实现：
package com.exeerp.sm.biz.impl;
import java.util.List;
import org.apache.log4j.Logger;
import com.exeerp.pub.biz.AbstractBaseManager;
import com.exeerp.pub.exception.BizException;
import com.exeerp.pub.exception.DaoException;
import com.exeerp.sm.bean.SysLog;
import com.exeerp.sm.bean.SysParam;
import com.exeerp.sm.biz.SysParamManager;
import com.exeerp.sm.dao.SysParamDAO;
import com.exeerp.pub.util.ExceptionMessage;
import com.exeerp.pub.util.Paginated;
/**
* 系统参数业务实现层实现
* @author: 
* 创建时间:2009-04-30
*
*/
public class SysParamManagerImpl extends AbstractBaseManager implements
SysParamManager
{
/**
* log4j用于在程序错误时，在控制台打印错误信息
*/
static Logger logger=Logger.getLogger(SysParamManagerImpl.class);
/**
* 将SysParamDAO接口作为属性
*/
private SysParamDAO sysParamDAO;

/**
* 查询所有的系统参数信息
* @param SysParam 系统参数对象
* @return 如果查找成功，则返回SysParam对象，否则返回null
*/
public Paginated getAllSysParamMgr(SysParam sysLog,int pageSize,int currentPage,String url)
{
try
{
return sysParamDAO.getAllSysParam(sysLog, pageSize, currentPage, url);
}catch(Exception ex){
logger.error(ex.getMessage());
ex.printStackTrace();
return null;
}
}
public SysParamDAO getSysParamDAO() 
{
return sysParamDAO;
}
public void setSysParamDAO(SysParamDAO sysParamDAO) {
this.sysParamDAO = sysParamDAO;
}
}

Dao层接口和实现：
package com.exeerp.sm.dao;
import java.util.List;
import com.exeerp.pub.dao.BaseDAO;
import com.exeerp.pub.exception.DaoException;
import com.exeerp.pub.util.Paginated;
import com.exeerp.sm.bean.SysLog;
import com.exeerp.sm.bean.SysParam;
/**
* 系统参数操作持久层DAO接口
* @author:孙启华
* 创建时间:2009-04-29
*/
public interface SysParamDAO extends BaseDAO 
{
/**
* 查询所有的系统参数信息
* @return
* @throws DaoException
*/
public Paginated getAllSysParam(SysParam sysLog,int pageSize,int currentPage,String url)throws DaoException;


}
package com.exeerp.sm.dao.impl;
import java.util.ArrayList;
import java.util.List;
import com.exeerp.pub.dao.BaseDAOImpl;
import com.exeerp.pub.exception.DaoException;
import com.exeerp.pub.util.Paginated;
import com.exeerp.sm.bean.SysLog;
import com.exeerp.sm.bean.SysParam;
import com.exeerp.sm.dao.SysParamDAO;
/**
* 系统参数持久层接口的实现
* @author： 
* 创建时间:2009-04-30
*/
public class SysParamDAOImpl extends BaseDAOImpl implements SysParamDAO 
{
/**
* 查询所有的系统参数信息
* @param sysPar 系统参数对象
* @return 如果查找成功，则返回List<SysParam>对象
*/
public Paginated getAllSysParam(SysParam sysLog,int pageSize,int currentPage,String url)throws DaoException
{
return this.queryForPaginatedList(sysLog, pageSize, currentPage, url);
}
}
baseDAo公用方法
package com.exeerp.pub.dao;
import java.util.List;
import org.apache.log4j.Logger;
import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
import com.exeerp.pub.exception.DaoException;
import com.exeerp.pub.util.ExceptionMessage;
import com.exeerp.pub.util.Paginated;
/*
* 抽象实现baseDAo公用方法
* 
* @author 创建时间：(2009-04-10)
*/
public class BaseDAOImpl extends SqlMapClientDaoSupport implements BaseDAO {
static Logger logger = Logger.getLogger(BaseDAOImpl.class);

/*
* (non-Javadoc)
* 
* @see com.exeerp.pub.dao.BaseDAO#addObject(java.lang.Object)
*/
/*
* (non-Javadoc)
* 
* @see com.exeerp.pub.dao.BaseDAO#queryForPaginatedList(java.lang.Object,
*      int, int, java.lang.String)
*/
public Paginated queryForPaginatedList(Object obj, int pageSize,
int currentPage, String url) {
return new Paginated(this.getSqlMapClientTemplate(), obj, currentPage,
pageSize, url);
}

/*
* (non-Javadoc)
* 
* @see com.exeerp.pub.dao.BaseDAO#queryForPaginatedList(int, int,
*      java.lang.String, java.lang.Object[])
*/
public Paginated queryForPaginatedList(int pageSize, int currentPage,
String url, Object... obj) {
return new Paginated(this.getSqlMapClientTemplate(), currentPage,
pageSize, url, obj);//调用Paginated的方法，进行分页的处理。
}

}