mybatis分页
@Override public Pageable<CallList> findByCondition(Pageable<CallList> pager, CallList condition) { //CallList a = this.getById(0l); return findByPager(pager,"findByCondition", "countByCondition", condition); }
protected <X> Pageable<X> findByPager(Pageable<X> pager, String selectStatement, String countStatement, X condition) { return findByPager(pager, selectStatement, countStatement, condition, null); } @Override protected <X> Pageable<X> findByPager(Pageable<X> pager, String selectStatement, String countStatement, X condition, Map<String, Object> otherParams) { Pageable<X> pager2 = selectPagination(selectStatement, countStatement, condition, pager.getPageNo(), pager.getPageSize()); pager.clear(); pager.setTotal(pager2.getTotal()); pager.addAll(pager2); return pager; } @Deprecated protected <T> Pageable<T> selectPagination(String selectStatement, String countStatement, Object parameter, int pageNo, int pageSize) { Long count = (Long)selectOne(countStatement, parameter); if (count.longValue() > 0L) { List list = this.sqlSession.selectList(getStatement(selectStatement), parameter, new RowBounds(pageNo, pageSize)); return new Pagination(list, count.longValue(), pageNo, pageSize); } return Pagination.empty(); }
打印出来的sql语句:
SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY a.ID DESC) AS ROW_NUM, a.* FROM CONTACTS_CALL_LIST a WHERE 1=1 ) TT WHERE TT.ROW_NUM >0 AND TT.ROW_NUM <= 20
在自己的查询sql语句外面再包了一层。
附:Pageable类:
public interface Pageable <T> extends java.util.List<T> { int DEFAULT_PAGE_NO = 1; int DEFAULT_PAGE_SIZE = 20; long DEFAULT_TOTAL = 0L; int MAX_PAGE_SIZE = 1000; void setTotal(long l); void setPageNo(int i); void setPageSize(int i); int getPageNo(); int getPageSize(); long getTotal(); int getTotalPage(); boolean hasPrevPage(); boolean hasNextPage(); int getFirstPage(); int getLastPage(); int getPrevPage(); int getNextPage(); int getOffset(); int getOffsetEnd(); }
import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.serializer.SerializeConfig; import com.alibaba.fastjson.serializer.SerializerFeature; import com.alibaba.fastjson.serializer.SimpleDateFormatSerializer; import java.sql.Time; import java.sql.Timestamp; import java.util.ArrayList; import java.util.Collection; public class Pagination<T> extends ArrayList<T> implements Pageable<T>, Orderable, Jsonable { private static final long serialVersionUID = -5403359437615228683L; protected int pageNo = 1; protected int pageSize = 20; protected long total = 0L; protected OrderPart[] orderParts; private static final Pagination EMPTY = new Pagination(); private static SerializeConfig mapping = new SerializeConfig(); private static SerializerFeature[] features = { SerializerFeature.DisableCircularReferenceDetect }; public static <X> Pagination<X> empty() { return EMPTY; } public Pagination() { } public Pagination(Collection<? extends T> c) { super(c); } public Pagination(int pageNo) { this(pageNo, 20); } public Pagination(int pageNo, int pageSize) { setPageNo(pageNo); setPageSize(pageSize); } public Pagination(Collection<? extends T> c, long total, int pageNo, int pageSize) { this(c); setTotal(total); setPageNo(pageNo); setPageSize(pageSize); } public int getTotalPage() { if (this.total <= 0L) return 0; int n = (int)(this.total / this.pageSize); if (this.total % this.pageSize == 0L) return n; return n + 1; } public boolean hasPrevPage() { return this.pageNo > 1; } public boolean hasNextPage() { return this.pageNo < getTotalPage(); } public int getFirstPage() { return 1; } public int getLastPage() { return getTotalPage(); } public int getPrevPage() { if (hasPrevPage()) return this.pageNo - 1; return 1; } public int getNextPage() { if (hasNextPage()) return this.pageNo + 1; return getTotalPage(); } public int getPageNo() { return this.pageNo; } public int getPageSize() { return this.pageSize; } public long getTotal() { return this.total; } public void setPageNo(int pageNo) { if (pageNo < 1) pageNo = 1; else this.pageNo = pageNo; } public void setPageSize(int pageSize) { if (pageSize < 1) this.pageSize = 20; else this.pageSize = pageSize; } public void setTotal(long total) { this.total = total; } public OrderPart[] getOrderParts() { return this.orderParts; } public void setOrderParts(OrderPart[] orderParts) { this.orderParts = orderParts; } public int getOffset() { return (this.pageNo - 1) * this.pageSize; } public int getOffsetEnd() { return getOffset() + getPageSize(); } public String toJson() { String list = JSON.toJSONString(this, mapping, features); StringBuilder builder = new StringBuilder("{"); builder.append("\"rows\":").append(list); builder.append(",\"pageSize\":").append(getPageSize()); builder.append(",\"pageNo\":").append(getPageNo()); builder.append(",\"total\":").append(getTotal()); builder.append("}"); return builder.toString(); } static { mapping.put(java.sql.Date.class, new SimpleDateFormatSerializer("yyyy-MM-dd")); mapping.put(Timestamp.class, new SimpleDateFormatSerializer("yyyy-MM-dd HH:mm:ss")); mapping.put(Time.class, new SimpleDateFormatSerializer("HH:mm:ss")); mapping.put(java.util.Date.class, new SimpleDateFormatSerializer("yyyy-MM-dd HH:mm:ss")); } }
public abstract interface Orderable { public abstract OrderPart[] getOrderParts(); public abstract void setOrderParts(OrderPart[] paramArrayOfOrderPart); }
public abstract interface Jsonable { public abstract String toJson(); }
AbstractDaoSupport:
import java.util.List; import org.apache.ibatis.session.RowBounds; import org.apache.ibatis.session.SqlSession; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; public abstract class AbstractDaoSupport { protected SqlSession sqlSession; private static final char DOT = '.'; public SqlSession getSqlSession() { return this.sqlSession; } @Autowired(required=false) @Qualifier("sqlSessionTemplate") public void setSqlSession(SqlSession sqlSession) { this.sqlSession = sqlSession; } protected abstract String getNamespace(); protected String getStatement(String partStatement) { return getNamespace() + '.' + partStatement; } protected <T> T selectOne(String statement) { return getSqlSession().selectOne(getStatement(statement)); } protected <T> T selectOne(String statement, Object parameter) { return getSqlSession().selectOne(getStatement(statement), parameter); } protected <T> List<T> selectList(String statement) { return getSqlSession().selectList(getStatement(statement)); } protected <T> List<T> selectList(String statement, Object parameter) { return getSqlSession().selectList(getStatement(statement), parameter); } protected int insert(String statement) { return getSqlSession().insert(getStatement(statement)); } protected int insert(String statement, Object parameter) { return getSqlSession().insert(getStatement(statement), parameter); } protected int update(String statement) { return getSqlSession().update(getStatement(statement)); } protected int update(String statement, Object parameter) { return getSqlSession().update(getStatement(statement), parameter); } protected int delete(String statement) { return getSqlSession().delete(getStatement(statement)); } protected int delete(String statement, Object parameter) { return getSqlSession().delete(getStatement(statement), parameter); } @Deprecated protected <T> Pageable<T> selectPagination(String selectStatement, String countStatement, int pageNo, int pageSize) { Long count = (Long)selectOne(countStatement); if (count.longValue() > 0L) { List list = this.sqlSession.selectList(getStatement(selectStatement), null, new RowBounds(pageNo, pageSize)); return new Pagination(list, count.longValue(), pageNo, pageSize); } return Pagination.empty(); } @Deprecated protected <T> Pageable<T> selectPagination(String selectStatement, String countStatement, Object parameter, int pageNo, int pageSize) { Long count = (Long)selectOne(countStatement, parameter); if (count.longValue() > 0L) { List list = this.sqlSession.selectList(getStatement(selectStatement), parameter, new RowBounds(pageNo, pageSize)); return new Pagination(list, count.longValue(), pageNo, pageSize); } return Pagination.empty(); } }