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
写的不清楚的地方，欢迎留言指出！！！