SpringMVC与Mybatis集合实现分页功能(实际项目)

利用mybatis-paginator实现分页
1、mybatis-paginator简介

mybatis-paginator是gethub上的一个开源项目、用于java后台获取分页数据、该开源项目还提供一个列表组件(mmgrid)用于前端展示。

2、该开源项目的使用说明：
Maven中加入依赖

<dependencies>
  ...
    <dependency>
        <groupId>com.github.miemiedev</groupId>
        <artifactId>mybatis-paginator</artifactId>
        <version>1.2.10</version>
    </dependency>
  ...
</dependencies>

3、Mybatis配置文件添加分页插件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN"
        "http://ibatis.apache.org/dtd/ibatis-3-config.dtd">
<configuration>

    <settings>
        <setting name="cacheEnabled" value="false" />
        <setting name="lazyLoadingEnabled" value="false" />
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>


    <plugins>
        <plugin interceptor="com.github.miemiedev.mybatis.paginator.OffsetLimitInterceptor">
            <property name="dialectClass" value="com.github.miemiedev.mybatis.paginator.dialect.OracleDialect"/>
        </plugin>
    </plugins>
</configuration>

4、Mybatis中创建一个查询，内容可以是任何Mybatis表达式  

<sql id="Base_Column_List">
		SPXX01, SPXX02, GYS01, GYS02
	</sql>
	<select id="selectXqtSpxx" resultMap="xqtSpxx" parameterType="java.util.Map">
		select 
		<include refid="Base_Column_List" />
		from xqtspxx t1
		where 1 = 1
		<if test="spxx01 != null and spxx01 != ''" >
        	and t1.spxx01 like CONCAT(CONCAT('%','${spxx01}'),'%')
     	</if>
     	<if test="spxx02 != null and spxx02 != ''" >
        	and t1.spxx02 like CONCAT(CONCAT('%','${spxx02}'),'%')
     	</if>
     	<if test="gys01 != null and gys01 != ''" >
     		and t1.gys01 like CONCAT(CONCAT('%','${gys01}'),'%')
     	</if>
     	<if test="gys02 != null and gys02 != ''" >
     		and t1.gys02 like CONCAT(CONCAT('%','${gys02}'),'%')
     	</if>
     	order by t1.spxx01
	</select>

5、Dao层代码

package com.pcmall.dao.sale.xqt;

import java.util.List;
import java.util.Map;

import com.github.miemiedev.mybatis.paginator.domain.PageBounds;
import com.pcmall.domain.sale.xqt.XqtSpxx;

public interface XqtSpxxMapper {
	 List<XqtSpxx> selectXqtSpxx(Map<String,Object> map, PageBounds page);
}

6、Service层代码

接口：

package com.pcmall.service.sale.xqt;

import java.util.List;

import com.github.miemiedev.mybatis.paginator.domain.PageBounds;
import com.pcmall.domain.sale.xqt.XqtSpxx;
import com.pcmall.domain.vo.ResponseVO;

public interface IXqtSpxxService {
	List<XqtSpxx> queryXqtSpxx(String spxx01, String spxx02, String gys01,
			String gys02, PageBounds page);
}

实现类：

package com.pcmall.service.sale.xqt.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.github.miemiedev.mybatis.paginator.domain.PageBounds;
import com.pcmall.dao.sale.xqt.XqtSpxxMapper;
import com.pcmall.domain.sale.xqt.XqtSpxx;
import com.pcmall.domain.vo.ResponseVO;
import com.pcmall.service.sale.xqt.IXqtSpxxService;

@Service
public class XqtSpxxService implements IXqtSpxxService {
	@Resource
	private XqtSpxxMapper xqtSpxxMapper;
	
	@Override
	public List<XqtSpxx> queryXqtSpxx(String spxx01, String spxx02,
			String gys01, String gys02, PageBounds page) {
		List<XqtSpxx> xqtSpxxList = new ArrayList<XqtSpxx>();
		
		Map<String,Object> map=new HashMap<String, Object>();
		map.put("spxx01",  spxx01);
		map.put("spxx02",  spxx02);
		map.put("gys01", gys01);
		map.put("gys02", gys02);
		xqtSpxxList = xqtSpxxMapper.selectXqtSpxx(map, page);
		return xqtSpxxList;
	}
}

7、Controller层代码

@RequestMapping("/selXqtSpxx")
	@ResponseBody
	public List<XqtSpxx> selXqtSpxx(
			@RequestParam(required = false, defaultValue = "1") int startIndex,
			@RequestParam(required = false, defaultValue = "10") int pageSize,
			HttpServletRequest request) throws UnsupportedEncodingException {
		List<XqtSpxx> xqtSpxxList = new ArrayList<XqtSpxx>();

		request.setCharacterEncoding("ISO-8859-1");
		String spxx01 = request.getParameter("spxx01");
		String spxx02 = request.getParameter("spxx02")==null?null:new String(request.getParameter("spxx02").getBytes("ISO8859_1"),"UTF-8");
		String gys01 = request.getParameter("gys01");
		String gys02 = request.getParameter("gys02")==null?null:new String(request.getParameter("gys02").getBytes("ISO8859_1"),"UTF-8");
		xqtSpxxList = xqtSpxxServiceImpl.queryXqtSpxx(spxx01, spxx02,
				gys01, gys02, new PageBounds(startIndex / pageSize + 1,
						pageSize));
		return xqtSpxxList;
	}

8、前端jsp、js代码如下

<%@ page language="java" pageEncoding="UTF-8"%>
<%@ include file="/WEB-INF/views/core/global.jsp"%>
<c:set var="pageAlias" value="user" scope="page" />
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<meta name="renderer" content="webkit|ie-comp|ie-stand">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport"
	content="width=device-width,initial-scale=1,minimum-scale=1.0,maximum-scale=1.0,user-scalable=no" />
<meta http-equiv="Cache-Control" content="no-siteapp" />
<!--[if lt IE 9]>
<script type="text/javascript" src="lib/html5.js"></script>
<script type="text/javascript" src="lib/respond.min.js"></script>
<script type="text/javascript" src="lib/PIE_IE678.js"></script>
<![endif]-->
<link href="${ctx }/static/hui/css/H-ui.min.css" rel="stylesheet"
	type="text/css" />
<link href="${ctx }/static/hui/css/H-ui.admin.css" rel="stylesheet"
	type="text/css" />
<link href="${ctx }/static/hui/lib/Hui-iconfont/1.0.7/iconfont.css"
	rel="stylesheet" type="text/css" />
<!--[if IE 6]>
<script type="text/javascript" src="http://lib.h-ui.net/DD_belatedPNG_0.0.8a-min.js" ></script>
<script>DD_belatedPNG.fix('*');</script>
<![endif]-->
<title>新奇特商品维护</title>
</head>
<body>
<iframe id='exportFrame' style="display: none" src=""></iframe>
	<div class="pd-20">
	<form id="form" action="${ctx}/xqt/xqtmain/add">
		<div class="text-l">
			商品编码： <input type="text" class="input-text" style="width: 180px"
				placeholder="商品编码" id="spxx01" name="spxx01">
				      
				商品名称： <input type="text" class="input-text" style="width: 180px"
				placeholder="商品名称" id="spxx02" name="spxx02">
				      
				 供应商编码： <input
				type="text" class="input-text" style="width: 180px"
				placeholder="供应商编码" id="gys01" name="gys01">
				      
				 供应商名称： <input
				type="text" class="input-text" style="width: 180px"
				placeholder="供应商名称" id="gys02" name="gys02">
				      
			<button οnclick="search()" type="button" 
				class="btn btn-primary radius" id="" name="">
				<i class="Hui-iconfont"></i> 查询
			</button>
		</div>
	</form>
	<div class="cl pd-5 bg-1 bk-gray mt-20">
			<span class="l"><a href="javascript:;"
				οnclick="xqtspxx_add('添加新奇特商品','${ctx}/xqt/xqtmain/add','','510')"
				class="btn btn-primary radius"><i class="Hui-iconfont"></i>
					添加新奇特商品</a></span> <span class="r">
			</span>
		</div>
			<table id="xqtList" class="table table-border table-bordered table-bg">
				<thead>
					<tr>
						<th width="100px">商品编码</th>
						<th width="250px">商品名称</th>
						<th width="100px">供应商编码</th>
						<th width="250px">供应商名称</th>
						<th width="100px">操作</th>
					</tr>
				</thead>
				<tbody></tbody>
				<!-- tbody是必须的 -->
			</table>
	</div>
	<script type="text/javascript"
		src="${ctx }/static/hui/lib/jquery/1.9.1/jquery.min.js"></script>
	<script type="text/javascript"
		src="${ctx }/static/hui/lib/layer/2.1/layer.js"></script>

	<script type="text/javascript"
		src="${ctx }/static/hui/lib/My97DatePicker/WdatePicker.js"></script>
	<script type="text/javascript"
		src="${ctx }/static/hui/lib/datatables/1.10.0/jquery.dataTables.min.js"></script>
	<script type="text/javascript" src="${ctx }/static/hui/js/H-ui.js"></script>
	<script type="text/javascript"
		src="${ctx }/static/hui/js/H-ui.admin.js"></script>
	<script type="text/javascript">
		var table;
		$(function() {
			var $wrapper = $('#div-table-container');
			table = $('#xqtList').DataTable(
							{
								//"ajax" : '${ctx}/user/queryUser',
								"ajax" : function(data, callback, settings) {//ajax配置为function,手动调用异步查询
									//手动控制遮罩
									//$wrapper.spinModal();
									//封装请求参数
									var param = getQueryCondition(data);
									$.ajax({
												type : "GET",
												url : '${ctx}/xqt/xqtmain/selXqtSpxx',
												cache : false, //禁用缓存
												data : param, //传入已封装的参数
												dataType : "json",
												success : function(result) {
													var returnData = {};
													returnData.draw = data.draw;
													returnData.recordsTotal = result.totalCount;
													returnData.recordsFiltered = result.totalCount;//后台不实现过滤功能，每次查询均视作全部结果
													returnData.data = result.items;
													callback(returnData);
												},
												error : function(
														XMLHttpRequest,
														textStatus, errorThrown) {
													alert("查询失败");
													//$wrapper.spinModal(false);
												}
											});
								},
								"processing" : true,
								"serverSide" : true,
								"info" : true,
								"lengthChange" : false,
								"searching" : false,
								"ordering" : false,
								"columnDefs" : [{"render":function(data, type, row) { 
									return "<a title='编辑' href='javascript:;' οnclick=\"xqtspxx_edit('编辑新奇特商品','${ctx}/xqt/xqtmain/edit?spxx01="+ row.spxx01 +"','','510')\" class=\"ml-5\" style=\"text-decoration:none\"><i class=\"Hui-iconfont\"></i>编辑</a> "
				                    +"<a title=\"删除\" href=\"javascript:;\" οnclick=\"xqtspxx_del(this,'"+ row.spxx01 +"')\" class=\"ml-5\" style=\"text-decoration:none\"><i class=\"Hui-iconfont\"></i>删除</a>";
								},
								 "targets" : 4
								}],
								columns : [ {
									"data" : "spxx01"
								}, {
									"data" : "spxx02"
								}, {
									"data" : "gys01"
								}, {
									"data" : "gys02"
								}]
							});
		});
		
		/*添加*/
		function xqtspxx_add(title, url, w, h) {
			layer_show(title, url, w, h);
		}
		/*编辑*/
		function xqtspxx_edit(title, url, w, h) {
			layer_show(title, url, w, h);
		}
		/*删除*/
		function xqtspxx_del(obj, id) {
			layer.confirm('确认要删除吗？', function(index) {
				var param = {};
				param.spxx01 = id;
				
				$.ajax({
					type : "POST",
					url : '${ctx}/xqt/xqtmain/delete',
					data : param,  //传入已封装的参数
					dataType : "json",
					success : function(data) {
						if(data.success){
							layer.msg('删除成功', {
								icon : 1,
								time : 1000
							});
							search(); 
						}else {
							layer.msg('删除失败,原因如下:' + data.errorMsg, {
								icon : 5
							});
						}
						
					},
					error : function(
							XMLHttpRequest,
							textStatus, errorThrown) {
						layer.msg('删除失败');
						//$wrapper.spinModal(false);
					}
				});
			});
		}
		function search() {
			table.ajax.reload();
		}
		function getQueryCondition(data) {
			var param = {};
			//组装排序参数
			if (data.order && data.order.length && data.order[0]) {
				switch (data.order[0].column) {
				case 1:
					param.orderColumn = "spxx01";
					break;
				case 2:
					param.orderColumn = "spxx01";
					break;
				case 3:
					param.orderColumn = "spxx01";
					break;
				case 4:
					param.orderColumn = "spxx01";
					break;
				default:
					param.orderColumn = "spxx01";
					break;
				}
				param.orderDir = data.order[0].dir;
			}
			//组装查询参数
			if ($("#spxx01").val() != "") {
				param.spxx01 = $("#spxx01").val();
			}
			if ($("#spxx02").val() != "") {
				param.spxx02 = $("#spxx02").val();
			}
			if ($("#gys01").val() != "") {
				param.gys01 = $("#gys01").val();
			}
			if ($("#gys02").val() != "") {
				param.gys02 = $("#gys02").val();
			}
			//组装分页参数
			param.startIndex = data.start;
			param.pageSize = data.length;

			return param;
		}
	</script>
</body>
</html>