springmvc+mybatis+easyui分页 |
您所在的位置：网站首页 › 车工两元背面图案 › springmvc+mybatis+easyui分页 |
springmvc+mybatis+easyui分页
|
道德三黄五帝，功名夏侯商周。五霸七雄闹春秋，顷刻兴亡过手。清时几行名姓，北芒无数荒丘。前人播种后人收，说什么原创与否。 今天和大家分享一下springmvc+mybatis+easyui的分页实现。springmvc，mybatis的优缺点不做太多敖述大家都比较了解了，ssm框架整合的例子网上也有很多了，为什么还要写这篇文章那，主要是觉得大多过于零散配置方式又是千差万别，总结一下本文希望对遇到此问题的人有所帮助，前人播种后人收。当然程序开发没有觉得正确，谁也说不出一个正确的实现第N行代码是什么，由于水平有限，难免有纰漏之处，希望大家多多指点。 正文开始之前和大家说说为什么选择easyui，个人从13年开始管理系统前端技术使用easyui非常多，easyui很对得起easy这个词，上手很容易，浏览器兼容性优良，文档内容完善且有中文，datagrid请求数据参数简单方便。 本文应用到技术相关版本如下： 技术 版本 Spring 4.0.2.RELEASE Mybatis 3.2.6 jquery jquery -1.11.1.js EasyUI 1.4 Maven 2.5
技术思路： 1. 用Springmvc构建 Web应用程序的全功能 MVC 模块 2. ORM框架选用mybatis，mybatis相对ibatis增加了mapper功能，大大简化了开发的工作量，但是spring和mybatis整合时没有了类似整合ibatis时的queryForList(String statementName,Object parameterObject, int skipResults, intmaxResults)方法，对于数据范围的控制需要借助mybatis的Interceptor实现。 3. 结合思路2中提到的特点，需要定义StatementHandler类型的Interceptor，同时本实例将分页相关的查询参数封装为PageUtil类。在该拦截器中判断如果参数类型为PageUtil则需要对sql进行处理，将其构造为分页模式的sql，oracle可用利用rownum，mysql利用limit函数。同时我们还可以在拦截器中获取到查询数据的总记录数，这样是相对于spring整合ibatis的又一方便之处。 4. PageUtil中封装分页相关的参数。由于easyui在请求时使用rows（Int类型）表示每页显示的记录数，page表示当前页，而响应时需要使用rows（List类型）表示查询结果，total表示记录数，所以PageUtil中的setRows，getRows方法不能简单的依靠IDE自动生成了。同时为了方便将PageUtil作为了控制层返回值，所以PageUtil类还需注意对其转换为符合easyuidatagrid标准的json格式时，不必要参数的控制。
5. Spring自动扫描mybatis的xml文件，自动扫描mapper类，service层控制事务。 程序代码如下： 1. Maven pom.xml文件内容如下 4.0.0 com.asiainfo cbar 0.0.1-SNAPSHOT war bomcreport2 4.0.2.RELEASE 3.2.6 1.7.7 1.2.17 org.apache.maven.plugins maven-resources-plugin 2.4.1 junit junit 4.11 test org.springframework spring-core ${spring.version} org.springframework spring-web ${spring.version} org.springframework spring-oxm ${spring.version} org.springframework spring-tx ${spring.version} org.springframework spring-jdbc ${spring.version} org.springframework spring-webmvc ${spring.version} org.springframework spring-aop ${spring.version} org.springframework spring-aspects ${spring.version} jar compile org.springframework spring-context-support ${spring.version} org.springframework spring-test ${spring.version} org.mybatis mybatis ${mybatis.version} org.mybatis mybatis-spring 1.2.2 commons-dbcp commons-dbcp 1.2.2 jstl jstl 1.2 log4j log4j ${log4j.version} org.slf4j slf4j-api ${slf4j.version} org.slf4j slf4j-log4j12 ${slf4j.version} commons-fileupload commons-fileupload 1.3.1 commons-io commons-io 2.4 commons-codec commons-codec 1.9 javax.servlet javax.servlet-api 3.0.1 provided commons-beanutils commons-beanutils 1.9.2 com.fasterxml.jackson.core jackson-databind 2.5.0 org.codehaus.jackson jackson-mapper-asl 1.9.13 compile src/main/java src/main/java **/*.java maven-compiler-plugin 2.3.2 1.6 1.6 maven-war-plugin 2.2 ${basedir}/WebRoot 3.0 false2. spring-config.xml文件 databaseType=oracle
3. spring-servlet.xml文件
4. web.xml文件 cbar contextConfigLocation classpath:spring-config.xml org.springframework.web.context.ContextLoaderListener Encoding org.springframework.web.filter.CharacterEncodingFilter encoding utf8 Encoding /* spring org.springframework.web.servlet.DispatcherServlet contextConfigLocation classpath:spring-servlet.xml 1 spring *.do java.lang.Exception /error.jsp report/report.jsp5. PageUtil类 package com.asia.util.mybatispage; import java.util.List; import com.fasterxml.jackson.annotation.JsonIgnore; public class PageUtil { private int rows; private int total; private int page; private String orderStr; private String sort; private String order; private Object queryObj; private List data; @JsonIgnore public String getOrderStr() { orderStr = ""; if (!org.springframework.util.StringUtils.isEmpty(order)) { String[] orders = order.split(","); String[] sorts = sort.split(","); for (int i = 0; i < sorts.length; i++) { orderStr += sorts[i] + " " + orders[i] + ", "; } orderStr = orderStr.endsWith(", ") ? orderStr.substring(0, orderStr.length() - 2) : orderStr; } return orderStr; } @JsonIgnore public int getPageSize() { return rows; } public List getRows(){ return data; } public void setRows(int rows) { this.rows = rows; } public int getTotal() { return total; } public void setTotal(int total) { this.total = total; } @JsonIgnore public int getPage() { return page; } public void setPage(int page) { this.page = page; } @JsonIgnore public String getSort() { return sort; } public void setSort(String sort) { this.sort = sort; } @JsonIgnore public String getOrder() { return order; } public void setOrder(String order) { this.order = order; } @JsonIgnore public Object getQueryObj() { return queryObj; } public void setQueryObj(Object queryObj) { this.queryObj = queryObj; } public void setOrderStr(String orderStr) { this.orderStr = orderStr; } public void setData(List data) { this.data = data; } @Override public String toString() { return "PageUtil [rows=" + rows + ", total=" + total + ", page=" + page + ", orderStr=" + orderStr + ", sort=" + sort + ", order=" + order + ", queryObj=" + queryObj + ", data=" + data + "]"; } }
6. Mybatis过滤器PageInterceptor类 package com.asia.util.mybatispage; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import java.util.Properties; import org.apache.ibatis.executor.parameter.ParameterHandler; import org.apache.ibatis.executor.statement.RoutingStatementHandler; import org.apache.ibatis.executor.statement.StatementHandler; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.mapping.ParameterMapping; import org.apache.ibatis.plugin.Interceptor; import org.apache.ibatis.plugin.Intercepts; import org.apache.ibatis.plugin.Invocation; import org.apache.ibatis.plugin.Plugin; import org.apache.ibatis.plugin.Signature; import org.apache.ibatis.scripting.defaults.DefaultParameterHandler; import com.asia.util.ReflectUtil; /** * 分页拦截器 * * @author SCY * * @CreateTime 2016年2月19日下午2:21:26 * * @Vsersion 1.0 */ @Intercepts({@Signature(method = "prepare", type = StatementHandler.class, args = {Connection.class})} ) public class PageInterceptor implements Interceptor { private String databaseType; @Override public Object intercept(Invocation invocation) throws Throwable { final RoutingStatementHandler handler = (RoutingStatementHandler) invocation.getTarget(); final StatementHandler delegate = (StatementHandler) ReflectUtil.getFieldValue(handler, "delegate"); final BoundSql boundSql = delegate.getBoundSql(); final Object obj = boundSql.getParameterObject(); //如果参数类型为自定义的PageUtil类型则执行分页处理 if (obj instanceof PageUtil) { final PageUtil page = (PageUtil) obj; MappedStatement mappedStatement = (MappedStatement) ReflectUtil.getFieldValue(delegate, "mappedStatement"); Connection connection = (Connection) invocation.getArgs()[0]; final String sql = boundSql.getSql();//获取原sql final String pageSql = this.getPageSql(page, sql);//转换为分页SQL System.out.println(pageSql); //获取总记录数 int rowCount = getRowCount(page, mappedStatement, connection); System.out.println("rowCount:"+rowCount); page.setTotal(rowCount); ReflectUtil.setFieldValue(boundSql, "sql", pageSql); } return invocation.proceed(); } private String getCountSql(String sql) { return "select count(*) from (" + sql+") a"; } /** * 生成分页数据sql * @param page * @param sql * @return */ private String getPageSql(PageUtil page, String sql) { final StringBuffer sqlBuffer = new StringBuffer(sql); if ("mysql".equalsIgnoreCase(databaseType)) { return getMysqlPageSql(page, sqlBuffer); } else if ("oracle".equalsIgnoreCase(databaseType)) { return getOraclePageSql(page, sqlBuffer); } return sqlBuffer.toString(); } /** * 生产mysql数据分页sql * @param page * @param sqlBuffer * @return */ private String getMysqlPageSql(PageUtil page, StringBuffer sqlBuffer) { int offset = (page.getPage() - 1) * page.getPageSize() ; sqlBuffer.append(" limit ").append(offset).append(",").append(page.getPageSize()); return sqlBuffer.toString(); } /** * 获取Oracle数据库的分页查询语句 */ private String getOraclePageSql(PageUtil page, StringBuffer sqlBuffer) { System.out.println(page); int offset = (page.getPage() - 1) * page.getPageSize() + 1; System.out.println(offset); sqlBuffer.insert(0, "select u.*, rownum r from (").append(") u where rownum < ") .append(offset + page.getPageSize()); sqlBuffer.insert(0, "select * from (").append(") where r >= ").append(offset); return sqlBuffer.toString(); } /** * 获取总数据量 * @param page * @param mappedStatement * @param connection * @return * @throws SQLException */ private int getRowCount(PageUtil page, MappedStatement mappedStatement, Connection connection) throws SQLException { int totalRecord = 0; BoundSql boundSql = mappedStatement.getBoundSql(page); String sql = boundSql.getSql(); String countSql = this.getCountSql(sql); List parameterMappings = boundSql.getParameterMappings(); BoundSql countBoundSql = new BoundSql(mappedStatement.getConfiguration(), countSql, parameterMappings, page); ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, page, countBoundSql); PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = connection.prepareStatement(countSql); parameterHandler.setParameters(pstmt); rs = pstmt.executeQuery(); if (rs.next()) { totalRecord = rs.getInt(1); } } finally { try { if (rs != null) rs.close(); } finally{ if (pstmt != null) pstmt.close(); } } return totalRecord; } @Override public Object plugin(Object target) { return Plugin.wrap(target, this); } @Override public void setProperties(Properties properties) { this.databaseType = properties.getProperty("databaseType"); } }
以下相关内容为样例程序代码，已简单的用户信息管理为例 7. UserInfo类 package com.asia.demo.bean; public class UserInfo { private int id; private String uname; private String birthday; private String sex; private String address; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUname() { return uname; } public void setUname(String uname) { this.uname = uname; } public String getBirthday() { return birthday; } public void setBirthday(String birthday) { this.birthday = birthday; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } @Override public String toString() { return "UserInfo [id=" + id + ", uname=" + uname + ", birthday=" + birthday + ", sex=" + sex + ", address=" + address + "]"; } }
8. UserController类 package com.asia.demo.controller; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.annotation.Resource; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.ResponseBody; import com.asia.demo.bean.UserInfo; import com.asia.demo.service.IUserService; import com.asia.util.mybatispage.PageUtil; @Controller @RequestMapping("/user") public class UserController { @Resource private IUserService userService; @RequestMapping(value="/searchUser") public @ResponseBody PageUtil search(UserInfo uesrInfo, PageUtil pu) { pu.setQueryObj(uesrInfo);//设置查询条件 List data= userService.searchUser(pu); pu.setData(data);//设置反馈结果 return pu; } public IUserService getUserService() { return userService; } public void setUserService(IUserService userService) { this.userService = userService; } }
9. UserInfoMapper类 package com.asia.demo.mapper; import java.util.List; import org.springframework.stereotype.Repository; import com.asia.demo.bean.UserInfo; import com.asia.util.mybatispage.PageUtil; @Repository("userInfoMapper") public interface UserInfoMapper { public List queryUser(); public List searchUser(PageUtil spu); } 10. Mybatis配置文件UserMapper.xml select id, uname, birthday, sex, address from userinfo select id, uname, birthday, sex, address from userinfo AND uname = #{queryObj.uname} AND sex = #{queryObj.sex}
11. UserServiceImpl类，接口代码省略 package com.asia.demo.service.impl; import java.util.List; import javax.annotation.Resource; import org.springframework.stereotype.Service; import com.asia.demo.bean.UserInfo; import com.asia.demo.mapper.UserInfoMapper; import com.asia.demo.service.IUserService; import com.asia.util.mybatispage.PageUtil; @Service("userServiceImpl") public class UserServiceImpl implements IUserService { @Resource private UserInfoMapper userInfoMapper; @Override public List queryUser() { return userInfoMapper.queryUser(); } @Override public List searchUser(PageUtil spu) { return userInfoMapper.searchUser(spu); } public UserInfoMapper getUserInfoMapper() { return userInfoMapper; } public void setUserInfoMapper(UserInfoMapper userInfoMapper) { this.userInfoMapper = userInfoMapper; } }
12. UserList.jsp 查询 var basePath="";
13. UserList.js /** * 设置列 * * @return */ function getColumns() { return [[ {field:'id', title:"编号", checkbox:true}, {field:'user', title:"用户名", halign:'center', align:'center',sortable:true, width:278}, {field:'birthday', title:"出生日期", halign:'center', align:'center', sortable:true, width:150}, {field:'sex', title:"性别", halign:'center', align:'center',sortable:true, width:80}, /* {field:'address', title:"状态", halign:'center', align:'center',sortable:true, width:80, formatter: function(value,row,index){ return taskState.get(value); } },*/ {field:'address', title:"地址", halign:'center', align:'center',sortable:true, width:180} ]]; } /** * 初始化页面 */ jQuery(function() { var gridParam = { rownumbers:true, loadMsg : "数据加载中", pagination : true, multiSort : true, pageSize : 10, pageList : [ 10, 20, 30, 50, 100 ], striped : true, columns : getColumns(), //queryParams : queryParams, url:basePath+"/user/searchUser.do", collapsible : true, singleSelect : true, idField : "id" // 支持跨页选择 }; $('#userInfo').datagrid(gridParam); $('#userInfo').datagrid("getPager").pagination( { pageSize : 10, displayMsg : '当前数据范围 {from} 到 {to} ，共 {total} 条记录', beforePageText : '当前', afterPageText : ' ,共 {pages}页' }); });
补充ReflectUtil package com.asia.util; import java.lang.reflect.Field; /** * 反射工具类 * @author SCY * * @createDate 2013-12-19上午09:29:45 * * @version 1.0 */ public class ReflectUtil { private ReflectUtil(){ } /** * 反射获取对象String类型属性值 * @param obj * @param paramName * @return String 对象属性值 * @throws Exception */ public static String getObjValue(Object obj, String paramName) throws Exception{ String getMethodName = "get" + Character.toUpperCase(paramName.charAt(0)) + paramName.substring(1); return (String) obj.getClass().getMethod(getMethodName).invoke(obj); } /** * 为对象某String类型属性设置指定值 * @param obj 待赋值对象 * @param paramName 属性名称 * @param paramValue 属性值 * @throws Exception */ public static void setObjValue(Object obj, String paramName, String paramValue) throws Exception { String getMethodName = "set" + Character.toUpperCase(paramName.charAt(0)) + paramName.substring(1); obj.getClass().getMethod(getMethodName, String.class).invoke(obj, paramValue); } /** * 利用反射获取指定对象的指定属性 * @throws IllegalAccessException * @throws IllegalArgumentException */ public static Object getFieldValue(Object obj, String fieldName) throws IllegalArgumentException, IllegalAccessException { Object result = null; final Field field = ReflectUtil.getField(obj, fieldName); if (field != null) { field.setAccessible(true); result = field.get(obj); } return result; } /** * 利用反射获取指定对象里面的指定属性 */ private static Field getField(Object obj, String fieldName) { Field field = null; for (Class clazz = obj.getClass(); clazz != Object.class; clazz = clazz.getSuperclass()) { try { field = clazz.getDeclaredField(fieldName); break; } catch (NoSuchFieldException e) { // 这里不用做处理，子类没有该字段可能对应的父类有，都没有就返回null。 } } return field; } /** * 利用反射设置指定对象的指定属性为指定的值 * @throws IllegalAccessException * @throws IllegalArgumentException */ public static void setFieldValue(Object obj, String fieldName, String fieldValue) throws IllegalArgumentException, IllegalAccessException { final Field field = ReflectUtil.getField(obj, fieldName); if (field != null) { field.setAccessible(true); field.set(obj, fieldValue); } } } demo下载地址http://download.csdn.net/download/suijiarui/9515485 欢迎大家拍砖 |
【本文地址】