一、iBatis进行分页查询

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>

View Code

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 + "&nbsp;</td>";
                                html += "<td align='center'>" + ReportBean.custName + "&nbsp;</td>";
                                html += "<td align='center'>" + ReportBean.sbType + "&nbsp;</td>";
                                html += "<td align='center'>" + ReportBean.jyType + "&nbsp;</td>";
                                html += "<td align='center'>" + ReportBean.branchCode + "&nbsp;</td>";
                                html += "<td align='center'>" + ReportBean.branchName + "&nbsp;</td>";
                                html += "<td align='center'>" + ReportBean.exchStamp + "&nbsp;<input type='hidden' name='desc_reason' id='desc_reason' value='"+ ReportBean.exchStamp +"' /></td>";
                                html += "<td align='center'>" + ReportBean.exchType + "&nbsp;</td>";
                                html += "<td align='center'>" + ReportBean.timestamp + "&nbsp;</td>";
                                html += "<td align='center'>" + ReportBean.checkDate + "&nbsp;</td>";
                                html += "<td align='center'><input type='button' value='查看' onclick=SearchAudit('DataAuditAction.do?action=searchAuditDetail&insurCode="+ReportBean.Reportid+"')>" + "&nbsp;</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");
}

View Code

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();

View Code

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);
    }
}

View Code

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;
  }
}

View Code

接口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;
}

View Code

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);
  }
}

View Code

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;
  }
}

View Code

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>

View Code

如果还有不太清楚的地方可以参看另一篇博文： spring集成ibatis进行项目中dao层基类封装 https://www.cnblogs.com/jiarui-zjb/p/9534810.html

写的不清楚的地方，欢迎留言指出！！！

posted @ 2018-04-03 16:05 爱笑的berg 阅读(4088) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

爱笑的berg

一、iBatis进行分页查询

公告