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

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