一、Mapper.xml（xml） <?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.sojson.common.dao.XXMapper" > <resultMap id="BaseResultMap" type="com.sojson.common.model.XX" > <result column="url" property="url" jdbcType="VARCHAR" /> <result column="title" property="title" jdbcType="VARCHAR" /> <result column="time" property="time" jdbcType="VARCHAR" /> <result column="picture_address" property="picture_address" jdbcType="VARCHAR" /> </resultMap> <sql id="Base_Column_List" > url,title,time,picture_address </sql> <sql id="limit_sql"> <if test="page_sql != null and page_sql != ''"> ${page_sql} </if> </sql> <sql id="where_all"> <where> <if test="findContent != null and findContent !='' " > distinguish_type = 0 and ( LOWER(字段1) like LOWER(CONCAT("%",#{findContent,jdbcType=VARCHAR},"%")) or LOWER(字段2) like LOWER(CONCAT("%",#{findContent,jdbcType=VARCHAR},"%")) )</if> </where> </sql> <select id="findCount" resultMap="BaseResultMap" > select count(url) from zp </select> <select id="findAll" resultMap="BaseResultMap" > select <include refid="Base_Column_List" /> from zp <include refid="limit_sql" /> </select> </mapper>二、Mapper.java(dao)package com.sojson.common.dao;public interface XXMapper {}三、BaseMybatisDao.java package com.sojson.core.mybatis;import java.lang.reflect.ParameterizedType;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import org.apache.ibatis.mapping.BoundSql;import org.apache.ibatis.mapping.ParameterMapping;import org.apache.ibatis.session.Configuration;import org.mybatis.spring.support.SqlSessionDaoSupport;import com.sojson.common.utils.LoggerUtils;import com.sojson.common.utils.StringUtils;import com.sojson.core.mybatis.page.MysqlDialect;import com.sojson.core.mybatis.page.Pagination;@SuppressWarnings( { "unchecked" })public class BaseMybatisDao<T> extends SqlSessionDaoSupport { private String NAMESPACE; final static Class<? extends Object> SELF = BaseMybatisDao.class; protected final Log logger = LogFactory.getLog(BaseMybatisDao.class); /**默认的查询Sql id*/ final static String DEFAULT_SQL_ID = "findAll"; /**默认的查询Count sql id**/ final static String DEFAULT_COUNT_SQL_ID = "findCount"; public BaseMybatisDao() { try { Object genericClz = getClass().getGenericSuperclass(); if (genericClz instanceof ParameterizedType) { Class<T> entityClass = (Class<T>) ((ParameterizedType) genericClz) .getActualTypeArguments()[0]; NAMESPACE = entityClass.getPackage().getName() + "." + entityClass.getSimpleName(); } } catch (RuntimeException e) { LoggerUtils.error(SELF, "初始化失败，继承BaseMybatisDao，没有泛型！"); } } /** * 分页 * * @param sqlId * 主语句 * @param countId * Count 语句 * @param params * 参数 * @param pageNo * 第几页 * @param pageSize每页显示多少条 * @param requiredType返回的类型[可以不传参] * @return */ public Pagination findPage(String sqlId, String countId, Map<String, Object> params, Integer pageNo, Integer pageSize) { pageNo = null == pageNo ? 1 : pageNo; pageSize = null == pageSize ? 10 : pageSize; Pagination page = new Pagination(); page.setPageNo(pageNo); page.setPageSize(pageSize); Configuration c = this.getSqlSession().getConfiguration(); int offset = (page.getPageNo() - 1) * page.getPageSize(); String page_sql = String.format(" limit %s , %s ", offset,pageSize); params.put("page_sql", page_sql); sqlId = String.format("%s.%s", NAMESPACE,sqlId) ; BoundSql boundSql = c.getMappedStatement(sqlId).getBoundSql(params); String sqlcode = boundSql.getSql(); LoggerUtils.fmtDebug(SELF, "findPage sql : %s",sqlcode ); String countCode = ""; BoundSql countSql = null; if (StringUtils.isBlank(countId)) { countCode = sqlcode; countSql = boundSql; } else { countId = String.format("%s.%s", NAMESPACE,countId) ; countSql = c.getMappedStatement(countId).getBoundSql(params); countCode = countSql.getSql(); } try { Connection conn = this.getSqlSession().getConnection(); List resultList = this.getSqlSession().selectList(sqlId, params); page.setList(resultList); /** * 处理Count */ PreparedStatement ps = getPreparedStatement4Count(countCode, countSql .getParameterMappings(), params, conn); ps.execute(); ResultSet set = ps.getResultSet(); while (set.next()) { page.setTotalCount(set.getInt(1)); } } catch (Exception e) { LoggerUtils.error(SELF, "jdbc.error.code.findByPageBySqlId",e); } return page; } /** * 重载减少参数DEFAULT_SQL_ID, "findCount" * * @param params * @param pageNo * @param pageSize * @return */ public Pagination findPage(Map<String, Object> params, Integer pageNo, Integer pageSize) { return findPage(DEFAULT_SQL_ID, DEFAULT_COUNT_SQL_ID, params, pageNo, pageSize); } /** * 组装 * @param sql * @param parameterMappingList * @param params * @param conn * @return * @throws SQLException */ private PreparedStatement getPreparedStatement(String sql, List<ParameterMapping> parameterMappingList, Map<String, Object> params, Connection conn) throws SQLException { /** * 分页根据数据库分页 */ MysqlDialect o = new MysqlDialect(); PreparedStatement ps = conn.prepareStatement(o.getCountSqlString(sql)); int index = 1; for (int i = 0; i < parameterMappingList.size(); i++) { ps.setObject(index++, params.get(parameterMappingList.get(i) .getProperty())); } return ps; } /** * 分页查询Count 直接用用户自己写的Count sql * @param sql * @param parameterMappingList * @param params * @param conn * @return * @throws SQLException */ private PreparedStatement getPreparedStatement4Count(String sql, List<ParameterMapping> parameterMappingList, Map<String, Object> params, Connection conn) throws SQLException { PreparedStatement ps = conn.prepareStatement(StringUtils.trim(sql)); int index = 1; for (int i = 0; i < parameterMappingList.size(); i++) { ps.setObject(index++, params.get(parameterMappingList.get(i) .getProperty())); } return ps; }}四、分页实体实体 1、Dialect package com.sojson.core.mybatis.page; public interface Dialect {public static final String RS_COLUMN = "totalCount"; public boolean supportsLimit(); /** * 以传入SQL为基础组装分页查询的SQL语句，传递给myBatis调用 * @param sql 原始SQL * @param offset 分页查询的记录的偏移量 * @param limit 每页限定记录数 * @return 拼装好的SQL */ public String getLimitSqlString(String sql, int offset, int limit); /** * 以传入SQL为基础组装总记录数查询的SQL语句 * @param sql 原始SQL * @return 拼装好的SQL */ public String getCountSqlString(String sql);} 2、MysqlDialectpackage com.sojson.core.mybatis.page;public class MysqlDialect implements Dialect {protected static final String SQL_END_DELIMITER = ";";public String getLimitSqlString(String sql, int offset, int limit) {sql = sql.trim();boolean isForUpdate = false;if ( sql.toLowerCase().endsWith(" for update") ) {sql = sql.substring( 0, sql.length()- 11 );isForUpdate = true;}if(offset < 0){ offset = 0;}StringBuffer pagingSelect = new StringBuffer();pagingSelect.append(sql +" limit "+offset+"," +limit);if ( isForUpdate ) {pagingSelect.append( " for update" );}return pagingSelect.toString();}public String getCountSqlString(String sql) {sql = trim(sql);StringBuffer sb = new StringBuffer(sql.length() + 10);sb.append("SELECT COUNT(1) AS " + RS_COLUMN + " FROM ( ");sb.append(sql);sb.append(")a");return sb.toString();}public boolean supportsLimit() {return true;}private static String trim(String sql) {sql = sql.trim();if (sql.endsWith(SQL_END_DELIMITER)) {sql = sql.substring(0, sql.length() - 1- SQL_END_DELIMITER.length());}return sql;}} 3、Paginable package com.sojson.core.mybatis.page;/** * * 开发公司：SOJSON在线工具 <p> * 版权所有：© www.sojson.com<p> * 博客地址：http://www.sojson.com/blog/ <p> * <p> * * 分页实体 * * <p> * * 区分 责任人 日期 说明<br/> * 创建 周柏成 2016年6月2日 <br/> * * @author zhou-baicheng * @email so@sojson.com * @version 1.0,2016年6月2日 <br/> * */public interface Paginable {/** * 总记录数 * * @return */public int getTotalCount();/** * 总页数 * * @return */public int getTotalPage();/** * 每页记录数 * * @return */public int getPageSize();/** * 当前页号 * * @return */public int getPageNo();/** * 是否第一页 * * @return */public boolean isFirstPage();/** * 是否最后一页 * * @return */public boolean isLastPage();/** * 返回下页的页号 */public int getNextPage();/** * 返回上页的页号 */public int getPrePage();}4、SimplePagepackage com.sojson.core.mybatis.page;public class SimplePage implements Paginable {private static final long serialVersionUID = 1L;public static final int DEF_COUNT = 20;public SimplePage() {}public SimplePage(int pageNo, int pageSize, int totalCount) {if (totalCount <= 0) {this.totalCount = 0;} else {this.totalCount = totalCount;}if (pageSize <= 0) {this.pageSize = DEF_COUNT;} else {this.pageSize = pageSize;}if (pageNo <= 0) {this.pageNo = 1;} else {this.pageNo = pageNo;}if ((this.pageNo - 1) * this.pageSize >= totalCount) {this.pageNo = totalCount / pageSize;if(this.pageNo==0){this.pageNo = 1 ;}}}/** * 调整分页参数，使合理化 */public void adjustPage() {if (totalCount <= 0) {totalCount = 0;}if (pageSize <= 0) {pageSize = DEF_COUNT;}if (pageNo <= 0) {pageNo = 1;}if ((pageNo - 1) * pageSize >= totalCount) {pageNo = totalCount / pageSize;}}public int getPageNo() {return pageNo;}public int getPageSize() {return pageSize;}public int getTotalCount() {return totalCount;}public int getTotalPage() {int totalPage = totalCount / pageSize;if (totalCount % pageSize != 0 || totalPage == 0) {totalPage++;}return totalPage;}public boolean isFirstPage() {return pageNo <= 1;}public boolean isLastPage() {return pageNo >= getTotalPage();}public int getNextPage() {if (isLastPage()) {return pageNo;} else {return pageNo + 1;}}public int getPrePage() {if (isFirstPage()) {return pageNo;} else {return pageNo - 1;}}protected int totalCount = 0;protected int pageSize = 20;protected int pageNo = 1;public void setTotalCount(int totalCount) {this.totalCount = totalCount;}public void setPageSize(int pageSize) {this.pageSize = pageSize;}public void setPageNo(int pageNo) {this.pageNo = pageNo;}protected int filterNo;public int getFilterNo() {return filterNo;}public void setFilterNo(int filterNo) {this.filterNo = filterNo;}}5、Paginationpackage com.sojson.core.mybatis.page;import java.util.List;/** * * 开发公司：SOJSON在线工具 <p> * 版权所有：© www.sojson.com<p> * 博客地址：http://www.sojson.com/blog/ <p> * <p> * * 分页的对象，以及分页页码输出 * * <p> * * 区分 责任人 日期 说明<br/> * 创建 周柏成 2016年6月2日 <br/> * * @author zhou-baicheng * @email so@sojson.com * @version 1.0,2016年6月2日 <br/> * */@SuppressWarnings("serial")public class Pagination<T> extends SimplePage implements java.io.Serializable,Paginable { public Pagination() { } public Pagination(int pageNo, int pageSize, int totalCount) { super(pageNo, pageSize, totalCount); } @SuppressWarnings("unchecked") public Pagination(int pageNo, int pageSize, int totalCount, List list) { super(pageNo, pageSize, totalCount); this.list = list; } public int getFirstResult() { return (pageNo - 1) * pageSize; } /** * 当前页的数据 */ private List<T> list; public List<T> getList() { return list; } public void setList(List<T> list) { this.list = list; } /**SOJSON SEO 翻页版本*/ public String getWebPage(String page){ StringBuffer pageHtml = new StringBuffer("<ul class='pagination'>"); if(this.getPageNo()>1){ if(this.getPageNo()>5){ pageHtml.append("<li class='prev'><a href='javascript:;' onclick='" + page + "'>首页</a></li>"); } pageHtml.append("<li class='prev'><a href='" + page + "" + (this.getPageNo() - 1) + "'>上一页</a></li>"); } for (int i = this.getPageNo()-2<=0?1:this.getPageNo()-2,no = 1; i <= this.getTotalPage()&& no <6 ; i++,no++) { if (this.getPageNo() == i) { pageHtml.append("<li class='active'><a href='javascript:void(0);' >" + i + "</a></li>"); }else{ pageHtml.append("<li><a href='" + page + "" + i + "'>" + i + "</a></li>"); } } if(this.getPageNo() < this.getTotalPage()){ pageHtml.append("<li class='next'><a href='" + page + "" + (this.getPageNo() + 1) + "'>下一页</a></li>"); } pageHtml.append("</ul>"); return pageHtml.toString(); } /**Ajxa翻页*/ public String getSiAjaxPageHtml(){ StringBuffer pageHtml = new StringBuffer("<ul class='pagination'>"); if(this.getPageNo()>1){ if(this.getPageNo()>5){ pageHtml.append("<li class='prev'><a href='javascript:;' onclick='goPageByAjax(1)'>首页</a></li>"); } pageHtml.append("<li class='prevd'><a href='javascript:;' onclick='goPageByAjax(" + (this.getPageNo() - 1) + ")'>上一页</a></li>"); } for (int i = this.getPageNo()-2<=0?1:this.getPageNo()-2,no = 1; i <= this.getTotalPage()&& no <6 ; i++,no++) { if (this.getPageNo() == i) { pageHtml.append("<li class='active'><a href='javascript:void(0);' >" + i + "</a></li>"); }else{ pageHtml.append("<li><a href='javascript:;' onclick='goPageByAjax(" + i + ")'>" + i + "</a></li>"); } } if(this.getPageNo() < this.getTotalPage()){ pageHtml.append("<li class='next'><a href='javascript:;' onclick='goPageByAjax(" + (this.getPageNo() + 1) + ")'>下一页</a></li>"); } pageHtml.append("</ul>"); return pageHtml.toString(); } /**普通翻页*/ public String getPageHtml(){ StringBuffer pageHtml = new StringBuffer("<ul class='pagination'>"); if(this.getPageNo()>1){ if(this.getPageNo()>5){ pageHtml.append("<li class='prev'><a href='javascript:;' onclick='_submitform(1)'>首页</a></li>"); } pageHtml.append("<li class='prev'><a href='javascript:;' onclick='_submitform(" + (this.getPageNo() - 1) + ")'>上一页</a></li>"); } for (int i = this.getPageNo()-2<=0?1:this.getPageNo()-2,no = 1; i <= this.getTotalPage()&& no <6 ; i++,no++) { if (this.getPageNo() == i) { pageHtml.append("<li class='active'><a href='javascript:void(0);' >" + i + "</a></li>"); }else{ pageHtml.append("<li><a href='javascript:;' onclick='_submitform(" + i + ")'>" + i + "</a></li>"); } } if(this.getPageNo() < this.getTotalPage()){ pageHtml.append("<li class='next'><a href='javascript:;' onclick='_submitform(" + (this.getPageNo() + 1) + ")'>下一页</a></li>"); } pageHtml.append("</ul>"); pageHtml.append("<script>"); pageHtml.append("function _submitform(pageNo){"); pageHtml.append("$(\"#formId\").append($(\"<input type='hidden' value='\" + pageNo +\"' name='pageNo'>\")).submit();"); pageHtml.append("}"); pageHtml.append("</script>"); return pageHtml.toString(); }}五、分页的内容实体 model(根据具体业务来定XX.java) 六、Service.java(接口)package com.sojson.user.service;import java.util.Map;import com.sojson.common.model.XX;import com.sojson.core.mybatis.page.Pagination;public interface XXService { Pagination<XX> findByPage(Map<String, Object> params, Integer pageNo, Integer pageSize);}七、ServiceImpl.java/** * * * @author Administrator 2017年3月20日 下午2:31:35 * @version 1.0 */@Servicepublic class XXServiceImpl extends BaseMybatisDao<XXMapper> implements XXService { /** * @param pageNo * @param pageSize * * @return * * @author Administrator Mar 21, 2017 10:09:09 AM * @version 1.0 */ @Override public Pagination<ZP> findByPage(Map<String, Object> resultMap, Integer pageNo, Integer pageSize) { return super.findPage(resultMap, pageNo, pageSize); }}八、Controller@Controller@Scope(value="prototype")@RequestMapping("member")public class MemberController extends BaseController { /*** * 用户手动操作Session * */ @Autowired XXService xxService; /** * 用户列表管理 * @return */ @RequestMapping(value="list") public ModelAndView list(ModelMap map, Integer pageNo, String findContent, String findContentc) { map.put("findContent", findContent);//模糊查询 Pagination<UUserAndExt> page = userService.findByPage(map, pageNo, pageSize); map.put("page", page); return new ModelAndView("member/list"); } }
九、jsp页面/ftl页面 <div class="page-heading"> <form method="get" action="" id="formId" class="form-inline"> <div class="wrapper"> <div class="directory-info-row"> <div class="row"> <#if page?exists && page.list?size gt 0> <#list page.list as it> <div class="col-md-4 col-sm-4"> <div class="panel"> <div class="panel-body"> <h4>${it.title?default(' ')}</h4> <div class="media"> <a class="pull-left" href="#"> <img class="" style="width: 150px; height: 120px;" src="${it.picture_address?default(' ')}" alt=""> </a> <div class="media-body"> <address> <strong> 更新时间： </strong> <br> <br> <abbr title="Phone"> ${it.time?default(' ')} </abbr> </address> <ul class="social-links"> <li><a title="" data-placement="top" data-toggle="tooltip" class="tooltips" href=" ${it.url?default(' ')}"> <i class="fa fa-video-camera"> </i> </a></li> </ul> </div> </div> </div> </div> </div> </tr> </#list><#else> </#if> <input type="hidden" name="channel" value="zp"> </div> </div> </div> <#if page?exists> <div class="row" > <div class="col-md-12 text-center" > 第${page.pageNo}页/共${page.totalCount}条 </div> </div> <div class="row"> <div class="col-md-12 text-center clearfix"> ${page.pageHtml} </div> </div> <div class="row" style="height: 50px;"> </div> </#if> </form> </div>
0 0
