一、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、MysqlDialect package 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、SimplePage package 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、Pagination package 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:;' οnclick='" + 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:;' οnclick='goPageByAjax(1)'>首页</a></li>"); } pageHtml.append("<li class='prevd'><a href='javascript:;' οnclick='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:;' οnclick='goPageByAjax(" + i + ")'>" + i + "</a></li>"); } } if(this.getPageNo() < this.getTotalPage()){ pageHtml.append("<li class='next'><a href='javascript:;' οnclick='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:;' οnclick='_submitform(1)'>首页</a></li>"); } pageHtml.append("<li class='prev'><a href='javascript:;' οnclick='_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:;' οnclick='_submitform(" + i + ")'>" + i + "</a></li>"); } } if(this.getPageNo() < this.getTotalPage()){ pageHtml.append("<li class='next'><a href='javascript:;' οnclick='_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 */ @Service public 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>
springMVC+spring+mybatis 框架下分页查询
最新推荐文章于 2024-08-30 09:54:06 发布