SpringMVC结合easyUI中datagird实现分页
DataGrid以表格形式展示数据,并提供了丰富的选择、排序、分组和编辑数据的功能支持。轻量级,单元格合并、多列标题、冻结列和页脚只是其中一小部分功能。
一、提前
1、SpringMVC注解@RequestMapping(value = "/listUser.htm"),返回值ModelView,解析返回一页面。 注解@ResponseBody用于返回一个模型对象(数据结构),如JSONObject,自定义DataGrid…… 等等。
2、需要引入相应的js和css,这是利用
<link rel="stylesheet" type="text/css"href="js/themes/default/easyui.css" />
<script type="text/javascript" src="/js/jquery.min.js"></script>
<script type="text/javascript" src="js/jquery.easyui.min.1.2.2.js"></script>
<script type="text/javascript" src="js/json2.js"></script>
引入文件
datagrid中以及其他easyui元素文字默认都是英文,如果换成中文引入easyui-lang-zh_CN.js
3、加载DataGrid时,回台接收page=1$rows=10的参数数据,可以通过request.getParamter()函数获取参数,也可以定义到函数参数中,如下图:
@RequestMapping(value = "/queryListUser.json")
@ResponseBody
public DataGrid quyerListUser(HttpServletRequest request,
HttpServletResponse response, int page, int rows) {
}
二、DataGrid简述及实现
下面为一种方法定义datagrid,在js中声名,也可以在table节点中定义。
$('#userGrid').datagrid({
loadMsg : '数据加载中....',
title : "管理员信息一览表",
url : '/queryListUser.json',
method : 'POST',
queryParams : {
'params' : JSON.stringify(params)
},
striped : true, //设置为true将交替显示行背景。相邻行不同颜色
//fit:false,//自适应大小
//fitColumns:true,//列宽适应浏览器,无左右滚动条
rownumbers : true,//行number
pagination : true,//显示页码
pageSize : 10,//初始化每页显示的条数
pageList : [ 10, 20, 50, 100 ],//可以调节的每页显示条数
columns : [ [ {
field : 'ck',
width : fixWidth(0.05),
checkbox : true
}, {
field : 'username',
title : '用户名',
width : 300,
sortable : true,
align : 'center'
}, {
field : 'password',
title : '密码',
width : 100,
sortable : true,
align : 'center'
}, {
field : 'usertype',
title : '类型',
width : 300,
sortable : true,
align : 'center',
formatter : typeColumnsFormatter
}, {
field : 'createtime',
title : '创建时间',
width : 100,
sortable : true,
align : 'center',
formatter : timeColumnsFormatter
} ] ],
sortOrder : "desc",
sortName : "createtime", //初始化时按Id升序排序
toolbar : [ {
iconCls : 'icon-add',
text : '添加',
handler : function() {
alert('Add')
}
}, '-', { //分隔符
iconCls : 'icon-edit',
text : '编辑',
handler : function() {
alert('edit')
}
}, '-', {
iconCls : 'icon-remove',
text : '删除',
handler : function() {
alert('delete')
}
}, '-', {
iconCls : 'icon-search',
text : '查询',
handler : function() { }
} ]
});
DataGrid
注意:pageSize : 10,//初始化每页显示的条数,不起作用, jquey easyui 版本问题, 统一修改为1.5的版本。
针对其属性可以找api文档进行了解。
三、后台实现
1、通过传递前台page,rows,sort,order,后台拼接sql语句进行数据的分页、排序。每次下一页、上一页等操作都会传递后台这些参数;
/**
* shopUser分页查询
*
* @param request
* @param response
* @param page
* 页码 参数传递或者getParameter()
* @param rows
* 每页的大小(条数)
* @return
*/
@RequestMapping(value = "/queryListUser.json")
@ResponseBody
public DataGrid quyerListUser(HttpServletRequest request,
HttpServletResponse response, int page, int rows) {
String paramsObj = request.getParameter("params");
String sort = request.getParameter("sort");//默认排序列名称
String order = request.getParameter("order");//排序方式
JSONObject paramsJson = JSONObject.fromObject(paramsObj);
int start = (page - 1) * rows; // limit 开始位置
int nums = rows; // limit 查询数量
paramsJson.put("start", start);
paramsJson.put("nums", nums);
if(StringUtils.isNotBlank(sort)){
paramsJson.put("sortName", sort);
}else{
paramsJson.put("sortName", "userid");
}
if(StringUtils.isNotBlank(order)){
paramsJson.put("sortOrder", order);
}else{
paramsJson.put("sortOrder", "asc");
} List<ShopUser> shopUsers = new ArrayList<ShopUser>();
int total = rows;
try {
shopUsers = shopUserService.quyeryShopUserWithPage(paramsJson);
total = shopUserService.quyerForCount(paramsJson);
} catch (Exception e) {
e.printStackTrace();
return null;
} if (shopUsers != null && shopUsers.size() > 0) { }
DataGrid dataGrid = new DataGrid();
dataGrid.setRows(shopUsers);
dataGrid.setTotal(total);
return dataGrid;
}
queryList
返回值shopUsers为数据对象,和total查询数量;返回到前台展示页码和条数。
2、这里我使用的MyBatis+Mysql,通过limit实现查询分页
@Repository(value="shopUserMapper")
public interface ShopUserMapper {
int deleteByPrimaryKey(Integer userid); int insert(ShopUser record); //选择性新增
int insertSelective(ShopUser record); ShopUser selectByPrimaryKey(Integer userid); List<ShopUser> selectByName(String name); int updateByPrimaryKeySelective(ShopUser record); int updateByPrimaryKey(ShopUser record); List<ShopUser> queryWithPage(JSONObject jsonObject); int queryForCount(JSONObject paramsJson);
}
ShopUserMapper.java
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.shop.web.dao.ShopUserMapper">
<resultMap id="BaseResultMap" type="com.shop.web.entity.ShopUser">
<id column="userid" property="userid" jdbcType="INTEGER" />
<result column="username" property="username" jdbcType="VARCHAR" />
<result column="password" property="password" jdbcType="VARCHAR" />
<result column="usertype" property="usertype" jdbcType="INTEGER" />
<result column="createtime" property="createtime" jdbcType="BIGINT" />
</resultMap>
<!-- 定义查询结果列 -->
<sql id="Base_Column_List">
userid, username, password, usertype, createtime
</sql> <!-- 分页查询 -->
<select id="queryWithPage" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"></include>
from shop_user
<trim prefix="where 1=1">
<if test="userid != null and userid !=''">
and userid = #{userid,jdbcType=INTEGER}
</if>
<if test="username != null and username != ''">
and username = #{username,jdbcType=VARCHAR}
</if>
<if test="subStartTime != null and subStartTime !=''">
and LEFT(createtime,8) >= DATE_FORMAT(#{subStartTime},'%Y%m%d')
</if>
<if test="subEndTime != null and subEndTime !=''">
and LEFT(createtime,8) <= DATE_FORMAT(#{subEndTime},'%Y%m%d')
</if>
order by ${sortName} ${sortOrder}
</trim>
<trim>
<if test="start != null and nums != null">
limit #{start,jdbcType=INTEGER},#{nums,jdbcType=INTEGER}
</if>
</trim>
</select> <!-- 查询所有count -->
<select id="queryForCount" resultType="Integer">
select count(1) as counts from shop_user
<trim prefix="where 1=1">
<if test="userid != null">
and userid = #{userid,jdbcType=INTEGER}
</if>
<if test="username != null">
and username = #{username,jdbcType=VARCHAR}
</if>
</trim>
</select> <!-- 通过id查询数据 -->
<select id="selectByPrimaryKey" resultMap="BaseResultMap"
parameterType="java.lang.Integer">
select
<include refid="Base_Column_List" />
from shop_user
where userid = #{userid,jdbcType=INTEGER}
</select> <!-- 通过name查询数据 -->
<select id="selectByName" resultMap="BaseResultMap"
parameterType="java.lang.String">
select
<include refid="Base_Column_List" />
from shop_user
where username = #{username,jdbcType=VARCHAR}
</select> <!-- 删除数据 -->
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from shop_user
where userid = #{userid,jdbcType=INTEGER}
</delete> <!-- 新增 -->
<insert id="insert" parameterType="com.shop.web.entity.ShopUser">
insert into shop_user (userid, username, password,
usertype, createtime)
values (#{userid,jdbcType=INTEGER}, #{username,jdbcType=VARCHAR},
#{password,jdbcType=VARCHAR},
#{usertype,jdbcType=INTEGER}, #{createtime,jdbcType=BIGINT})
</insert> <!-- 选择性的新增 -->
<insert id="insertSelective" parameterType="com.shop.web.entity.ShopUser">
<selectKey keyProperty="userid" resultType="Integer">
select LAST_INSERT_ID()
</selectKey>
insert into shop_user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="username != null">
username,
</if>
<if test="password != null">
password,
</if>
<if test="usertype != null">
usertype,
</if>
<if test="createtime != null">
createtime,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="username != null">
#{username,jdbcType=VARCHAR},
</if>
<if test="password != null">
#{password,jdbcType=VARCHAR},
</if>
<if test="usertype != null">
#{usertype,jdbcType=INTEGER},
</if>
<if test="createtime != null">
#{createtime,jdbcType=BIGINT},
</if>
</trim>
</insert> <update id="updateByPrimaryKeySelective" parameterType="com.shop.web.entity.ShopUser">
update shop_user
<set>
<if test="username != null">
username = #{username,jdbcType=VARCHAR},
</if>
<if test="password != null">
password = #{password,jdbcType=VARCHAR},
</if>
<if test="usertype != null">
usertype = #{usertype,jdbcType=INTEGER},
</if>
<if test="createtime != null">
createtime = #{createtime,jdbcType=BIGINT},
</if>
</set>
where userid = #{userid,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.shop.web.entity.ShopUser">
update shop_user
set username = #{username,jdbcType=VARCHAR},
password = #{password,jdbcType=VARCHAR},
usertype = #{usertype,jdbcType=INTEGER},
createtime = #{createtime,jdbcType=BIGINT}
where userid = #{userid,jdbcType=INTEGER}
</update>
</mapper>
ShopUserMapper.xml
具体的mybatis用法,可以自己查看其他博文。