1、ibatis理解：
iBatis属于半自动化的ORM框架，我们需要编写SQL语句，由iBatis进行数据库访问，返回结果。而iBatis可以为我们做的更多，比如对查询参数集合、结果、分页查询、事务管理的封装等。虽然不如全自动SQL方便，但是SQL的主动权却在我们开发人员的手中，对SQL优化的掌控则是很直接的。
备注：iBatis的版本发展到3.0时，其名称也更改为MyBatis。而Spring更新到3.1都没有对MyBatis进行支持，但是MyBatis团队已经自行开发了Spring的支持。我们以Spring为主，仍然使用对iBatis2的支持来进行说明。
ibatis在线API教程可以参看 https://www.w3cschool.cn/ibatis/
2、jsp页面分页代码
<div align="right">
当前第<label class="page" id="currentpage" ></label>页/<label class="page" id="allSize" ></label>页
<label id="first" style="display: inline;">首页 前一页</label>
<span id="first1" style="display: none;">
<a style="display: inline" onclick="javascript:seachPage('first')" href="##" class="a1">首页</a>
<a onclick="javascript:seachPage('previous')" href="##" class="a1">前一页</a>
</span>
<label id="last" style="display: inline">后一页 末页</label>
<span id="last1" style="display: none;">
<a onclick="javascript:seachPage('next')" href="##" class="a1">后一页</a>
<a onclick="javascript:seachPage('last')" href="##" class="a1">末页</a>
</span>
<input id="currentpagevalue" type="hidden" value="0">
</div>
3、JavaScript函数提交分页查询请求
function seachPage(pageDirection) { var currentpagevalue = $("#currentpagevalue").val();var custname = $("#custname").val(); initLoading();// 启动加载动画 $.post("DataAction.do?action=findAudiList",{ pageDirection : pageDirection, currentPage : currentpagevalue, custName : encodeURI(custname) }, function(data) { var arr = data[0].auditList; if (arr.length > 0) { var html = ""; for ( var i = 0; i < arr.length; i++) {// 更新列表 var ReportBean = arr[i]; html += "<tr><td><input type='radio' name='Reportid' value='" + ReportBean.Reportid + "'/> </td>"; html += "<td align='center'>" + ReportBean.Reportid + " </td>"; html += "<td align='center'>" + ReportBean.custName + " </td>"; html += "<td align='center'>" + ReportBean.sbType + " </td>"; html += "<td align='center'>" + ReportBean.jyType + " </td>"; html += "<td align='center'>" + ReportBean.branchCode + " </td>"; html += "<td align='center'>" + ReportBean.branchName + " </td>"; html += "<td align='center'>" + ReportBean.exchStamp + " <input type='hidden' name='desc_reason' id='desc_reason' value='"+ ReportBean.exchStamp +"' /></td>"; html += "<td align='center'>" + ReportBean.exchType + " </td>"; html += "<td align='center'>" + ReportBean.timestamp + " </td>"; html += "<td align='center'>" + ReportBean.checkDate + " </td>"; html += "<td align='center'><input type='button' value='查看' onclick=SearchAudit('DataAuditAction.do?action=searchAuditDetail&insurCode="+ReportBean.Reportid+"')>" + " </td></tr>"; } // var allsize = data[0].allSize; var currentpage = data[0].currentpage; cleartable(0);// 清空表格 $("#tablelist").append(html); hideLoading();// 取消动画 $("#allSize").append(data[0].allSize); $("#currentpage").append((parseInt(data[0].currentpage) + parseInt(1))); changePage(allsize, currentpage);// 更新翻页 $("#currentpagevalue").val(currentpage); } else cleartable(0); hideLoading();// 取消动画 }, "json"); }
4.ibatis分页查询
Action层
import org.biframework.common.Tools; import org.biframework.dao.ibatis.Paper; Paper paper = new Paper(); String pageDirection = Tools.nulltostring((String) request.getParameter("pageDirection")); String currentPage = request.getParameter("currentPage"); List auditList = dataAuditService.getAuditAllDataList(map, paper, currentPage, pageDirection, 10); map.put("orgLevels", userBean.getOrgLevels()); map.put("auditList", auditList); map.put("currentpage", String.valueOf(paper.getCurrentpage())); map.put("allSize", String.valueOf(paper.getAllSize())); response.getWriter().write(JSONArray.fromObject(map).toString()); response.getWriter().flush(); response.getWriter().close();
dao层
public class DataAuditDao extends BaseDao{ protected static Log log = LogFactory.getLog(DataAuditDao.class); public List getAuditAllDataList(Map map,Paper paper,String currentPage,String pageDirection,int line) throws DaoException{ List list = super.getList("getAllAuditDataList", map); return paper.getPaperList(list, pageDirection, currentPage,line); } }
BaseDao
package org.biframework.dao.ibatis; import com.ibatis.common.util.PaginatedList; import java.io.PrintStream; import java.util.ArrayList; import java.util.List; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.biframework.exception.DaoException; import org.springframework.orm.ibatis.SqlMapClientTemplate; import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport; public class BaseDao extends SqlMapClientDaoSupport implements IBaseDao { private static Log log = LogFactory.getLog(BaseDao.class); protected static final int PAGE_SIZE = 15; public List getList(String statementName, Object parameterObject) throws DaoException { List list = getSqlMapClientTemplate().queryForList(statementName, parameterObject); return list; } public List getListUseSameStmt(String statementName, Object[] objectParam) throws DaoException { List list = null; List temp = null; if ((statementName == null) || (objectParam == null) || (objectParam.length == 0)) { return list; } for (int i = 0; i < objectParam.length; i++) { if (list == null) { list = new ArrayList(); } temp = getSqlMapClientTemplate().queryForList(statementName, objectParam[i]); if (temp != null) { list.addAll(temp); } } return list; } public Object getObject(String statementName, Object parameterObject) throws DaoException { Object result = null; List list = getSqlMapClientTemplate().queryForList(statementName, parameterObject); if ((list != null) && (list.size() > 0)) { result = list.get(0); } return result; } public PaginatedList getPgntList(String statementName, Object parameterObject, String pageDirection) throws DaoException { PaginatedList list = getSqlMapClientTemplate().queryForPaginatedList(statementName, parameterObject, 15); if ("next".equals(pageDirection)) { list.nextPage(); } else if ("previous".equals(pageDirection)) { list.previousPage(); } else if ("first".equals(pageDirection)) { list.isFirstPage(); } else if ("last".equals(pageDirection)) { list.isLastPage(); } return list; } public PaginatedList getPgntList(String statementName, Object parameterObject, String pageDirection, int pageSize) throws DaoException { PaginatedList list = getSqlMapClientTemplate().queryForPaginatedList(statementName, parameterObject, pageSize); if ("next".equals(pageDirection)) { System.out.println("下一页"); list.nextPage(); } else if ("previous".equals(pageDirection)) { System.out.println("上一页"); list.previousPage(); } else if ("first".equals(pageDirection)) { System.out.println("首页"); list.isFirstPage(); } else if ("last".equals(pageDirection)) { System.out.println("末页"); list.isLastPage(); } return list; } public int transUpdate(Object[][] statementAndparameter) throws DaoException { Object[] statements = statementAndparameter[0]; Object[] parameters = statementAndparameter[1]; int result = 0; for (int i = 0; i < statements.length; i++) { String name = (String)statements[i]; Object param = parameters[i]; result += getSqlMapClientTemplate().update(name, param); } return result; } public int transUpdateSameOpt(String statementName, Object[] objectParam) throws DaoException { int result = 0; if ((statementName == null) || (objectParam == null) || (objectParam.length == 0)) { return result; } for (int i = 0; i < objectParam.length; i++) { result += getSqlMapClientTemplate().update(statementName, objectParam[i]); } return result; } public int update(String statementName, Object parameterObject) throws DaoException { int result = getSqlMapClientTemplate().update(statementName, parameterObject); return result; } }
接口IBaseDao
package org.biframework.dao.ibatis; import com.ibatis.common.util.PaginatedList; import java.util.List; import org.biframework.exception.DaoException; public abstract interface IBaseDao { public abstract Object getObject(String paramString, Object paramObject) throws DaoException; public abstract List getList(String paramString, Object paramObject) throws DaoException; public abstract PaginatedList getPgntList(String paramString1, Object paramObject, String paramString2) throws DaoException; public abstract PaginatedList getPgntList(String paramString1, Object paramObject, String paramString2, int paramInt) throws DaoException; public abstract List getListUseSameStmt(String paramString, Object[] paramArrayOfObject) throws DaoException; public abstract int update(String paramString, Object paramObject) throws DaoException; public abstract int transUpdateSameOpt(String paramString, Object[] paramArrayOfObject) throws DaoException; public abstract int transUpdate(Object[][] paramArrayOfObject) throws DaoException; }
DaoException
package org.biframework.exception; public class DaoException extends Exception { public DaoException() {} public DaoException(Throwable cause) { super(cause); } public DaoException(String message) { super(message); } public DaoException(String message, Throwable cause) { super(message, cause); } }
Paper
package org.biframework.dao.ibatis; import java.util.ArrayList; import java.util.List; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; public class Paper { public Paper() { this.currentpage = 0; this.allSize = 0; } public void getCurrentPageNum(List allList, String pageDirection, String _currentpage) { if ("next".equals(pageDirection)) { if ((Integer.parseInt(_currentpage) + 1) * 20 < allList.size()) { this.currentpage = (Integer.parseInt(_currentpage) + 1); } else { this.currentpage = Integer.parseInt(_currentpage); } } else if ("previous".equals(pageDirection)) { if (Integer.parseInt(_currentpage) - 1 <= 0) { this.currentpage = 0; } else { this.currentpage = (Integer.parseInt(_currentpage) - 1); } } else if ("first".equals(pageDirection)) { this.currentpage = 0; } else if ("last".equals(pageDirection)) { if (allList.size() == 0) { this.currentpage = 0; } else if (allList.size() % 20 == 0) { this.currentpage = (allList.size() / 20 - 1); } else { this.currentpage = (allList.size() / 20); } } else { this.currentpage = 0; } } public void getCurrentPageNum(List allList, String pageDirection, String _currentpage, int pageSize) { if ("next".equals(pageDirection)) { if ((Integer.parseInt(_currentpage) + 1) * pageSize < allList.size()) { this.currentpage = (Integer.parseInt(_currentpage) + 1); } else { this.currentpage = Integer.parseInt(_currentpage); } } else if ("previous".equals(pageDirection)) { if (Integer.parseInt(_currentpage) - 1 <= 0) { this.currentpage = 0; } else { this.currentpage = (Integer.parseInt(_currentpage) - 1); } } else if ("first".equals(pageDirection)) { this.currentpage = 0; } else if ("last".equals(pageDirection)) { if (allList.size() == 0) { this.currentpage = 0; } else if (allList.size() % pageSize == 0) { this.currentpage = (allList.size() / pageSize - 1); } else { this.currentpage = (allList.size() / pageSize); } } else { this.currentpage = 0; } } public List getPaperList(List allList, String pageDirection, String _currentpage) { getCurrentPageNum(allList, pageDirection, _currentpage); List rsList = new ArrayList(); for (int i = this.currentpage * 20; i < (this.currentpage + 1) * 20; i++) { if (i == allList.size()) { break; } rsList.add(allList.get(i)); } if (allList.size() % 20 == 0) { this.allSize = (allList.size() / 20); } else { this.allSize = (allList.size() / 20 + 1); } if (allList.size() == 0) { this.allSize = 1; } return rsList; } public List getPaperList(List allList, String pageDirection, String _currentpage, int pageSize) { getCurrentPageNum(allList, pageDirection, _currentpage, pageSize); List rsList = new ArrayList(); for (int i = this.currentpage * pageSize; i < (this.currentpage + 1) * pageSize; i++) { if (i == allList.size()) { break; } rsList.add(allList.get(i)); } if (allList.size() % pageSize == 0) { this.allSize = (allList.size() / pageSize); } else { this.allSize = (allList.size() / pageSize + 1); } if (allList.size() == 0) { this.allSize = 1; } return rsList; } public int getCurrentpage() { return this.currentpage; } public void setCurrentpage(int currentpage) { this.currentpage = currentpage; } private static Log log = LogFactory.getLog(Paper.class); protected static final int PAGE_SIZE = 20; private int currentpage; private int allSize; public int getAllSize() { return this.allSize; } public void setAllSize(int allSize) { this.allSize = allSize; } }
sql：中无需使用分页查询
<select id="getAllAuditDataList" resultMap="auditAllDataResult" parameterClass="map">
<![CDATA[
select distinct
a.report_id,
case when count(distinct b.cust_name)>1 then
max(b.cust_name) || ' 等'
else
max(b.cust_name)
end as cust_name,
max(decode(a.tran_type,
'N',
'正常',
'C',
'改错')) as jy_type,
max(a.exch_stamp || ' ' || a.action_desc) as desc,
max(to_char(a.TIMESTAMP,'yyyy-MM-dd')) TIMESTAMP
from report a,customer b,exch c
group by a.report_id,b.cust_name
order by check_date desc NULLS LAST, cust_name
]]>
</select>
如果还有不太清楚的地方可以参看另一篇博文： spring集成ibatis进行项目中dao层基类封装 https://www.cnblogs.com/jiarui-zjb/p/9534810.html
写的不清楚的地方，欢迎留言指出！！！