springMVC+spring+mybatis 框架下分页查询

青旅半醒 2022-09-30 12:53 204阅读 0赞

一、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>