Oracle大记录数分页借助rownum
select * from ( select a.*, rownum num from (select * from
guestbook order by name desc) a where rownum <= 20 )
where num >=10;
Mysql大记录数分页借助rownum
select * from (select * from guestbook ) aa limit 0,10
借助Pager标签实现分页功能
<dependency>
<groupId>jsptags</groupId>
<artifactId>pager-taglib</artifactId>
<version>2.0</version>
</dependency>
使用方法:
1.MysqlPagination.java
2. GuestbookDAOJdbc.java
3.GetMessagesServlet.java
4.getMessages.jsp
select * from ( select a.*, rownum num from (select * from
guestbook order by name desc) a where rownum <= 20 )
where num >=10;
Mysql大记录数分页借助rownum
select * from (select * from guestbook ) aa limit 0,10
借助Pager标签实现分页功能
<dependency>
<groupId>jsptags</groupId>
<artifactId>pager-taglib</artifactId>
<version>2.0</version>
</dependency>
使用方法:
1.MysqlPagination.java
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.regex.Pattern;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
public class MysqlPagination implements Pagination {
private int pageSize = 3;
private int pageNumber = 1;
private int maxPages;
private int maxElements;
private String sql;
private MysqlUtil db;
public MysqlPagination(String sql) {
this.sql = sql;
init();
}
public MysqlPagination(String sql, int pageSize, int pageNumber) {
this.sql = sql;
this.pageSize = pageSize;
this.pageNumber = pageNumber;
init();
this.setPageNumber(pageNumber);
}
private void init() {
db = MysqlUtil.getInstance();
setMaxElements();
setMaxPages();
}
private void setMaxElements() {
// select * from xxx order by desc
// select count(1) from xxx order by desc
String regex = "select((.)+)from";
Pattern p = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
String[] s = p.split(this.sql);
String newSql = "select count(1) as total from " + s[1];
ResultSetHandler handler = new ResultSetHandler() {
public Object handle(ResultSet rs) throws SQLException {
if (rs.next()) {
return new Integer(rs.getInt("total"));
} else {
return null;
}
}
};
this.maxElements = (Integer) db.query(newSql, null, handler);
}
private void setMaxPages() {
if (maxElements != 0 && (maxElements % pageSize == 0)) {
maxPages = maxElements / pageSize;
} else {
maxPages = maxElements / pageSize + 1;
}
}
private String sqlModify(String source,int begin, int size){
System.out.println(begin+", " + size);
StringBuffer target = new StringBuffer(200);
target.append("select * from (");
target.append(sql);
target.append(") aa limit ");
target.append(begin);
target.append(", ");
target.append(size);
return target.toString();
}
private int getEndElement() {
int endElement = pageNumber * pageSize;
if (endElement >= maxElements) {
return maxElements;
} else {
return endElement;
}
}
private int getBeginElement() {
return (pageNumber - 1) * pageSize;
}
@Override
public List<Object> getList() {
String newSql = this.sqlModify(this.sql, this.getBeginElement(), this.pageSize);
return (List) db.query(newSql, null, new MapListHandler());
}
@Override
public int getMaxElements() {
return maxElements;
}
@Override
public int getMaxPages() {
return maxPages;
}
@Override
public int getNext() {
if (pageNumber + 1 >= this.getMaxPages()) {
return getMaxPages();
}
return pageNumber + 1;
}
@Override
public int getPageNumber() {
return pageNumber;
}
@Override
public int getPageSize() {
return pageSize;
}
@Override
public int getPrevious() {
if(pageNumber-1<=1){
return 1;
}else{
return pageNumber-1;
}
}
@Override
public boolean hasNext() {
return pageNumber<this.getMaxPages();
}
@Override
public boolean hasPrevious() {
return pageNumber>1;
}
@Override
public boolean isFirst() {
return pageNumber==1;
}
@Override
public boolean isLast() {
return pageNumber>=this.getMaxPages();
}
@Override
public void setPageNumber(int pageNumber) {
if(pageNumber>maxPages){
this.pageNumber=maxPages;
}else if(pageNumber<1){
this.pageNumber=1;
}else{
this.pageNumber=pageNumber;
}
}
@Override
public void setPageSize(int pageSize) {
this.pageSize=pageSize;
}
// Pagination p = new OraclePagination(sql,20,3);
//List list=p.getList();
}
2. GuestbookDAOJdbc.java
private static final String SELECT_GUESTBOOKS_SQL="select * from guestbook order by id desc";
@Override
public Pagination getGuestbookByPage(int pageSize, int pageNumber) {
Pagination p = new MysqlPagination(SELECT_GUESTBOOKS_SQL,pageSize,pageNumber);
return p;
}
3.GetMessagesServlet.java
public class GetMessagesServlet extends HttpServlet {
private static final long serialVersionUID = 5964428201228635704L;
private int pageSize;
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
int pageNumber;
try {
pageNumber = Integer.parseInt(request.getParameter("p"));
} catch (Exception e) {
pageNumber = 1;
}
GuestbookDAO dao = new GuestbookDAOJdbc();
Pagination pager=dao.getGuestbookByPage(pageSize, pageNumber);
request.setAttribute("guestbook.pager", pager);
request.getRequestDispatcher("/getMessages.jsp").forward(request, response);
}
public void init(ServletConfig config)throws ServletException{
super.init(config);
try{
this.pageSize=Integer.parseInt(config.getServletContext().getInitParameter("pageSize"));
}catch(Exception e){
this.pageSize=10;
}
}
}
4.getMessages.jsp
<%@ taglib uri="http://jsptags.com/tags/navigation/pager" prefix="pg"%>
<pg:pager url="${ctx}/servlet/getMessages" items="${requestScope['guestbook.pager'].maxElements}" maxPageItems="${initParam.pageSize}" maxIndexPages="5">
<pg:index>
<pg:first unless="current">
<a href="${pageUrl}&p=${pageNumber}">首页</a>
</pg:first>
<pg:prev>
<a href="${pageUrl}&p=${pageNumber}">上一页(${pageNumber})</a>
</pg:prev>
<pg:pages>
<a href="${pageUrl}&p=${pageNumber}">${pageNumber}</a>
</pg:pages>
<pg:next>
<a href="${pageUrl}&p=${pageNumber}">下一页(${pageNumber})</a>
</pg:next>
<pg:last unless="current">
<a href="${pageUrl}&p=${pageNumber}">尾页</a>
</pg:last>
</pg:index>
</pg:pager>