JSP实现分页_jsp模拟百度分页-CSDN博客

本文链接：https://blog.csdn.net/fljxzxb/article/details/6885656

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page import="model.Members"%>
<%@page import="dao.MembersDao"%>
<%
	int currentPage = 1;
	if (request.getParameter("page")!=null){
		currentPage = Integer.parseInt(request.getParameter("page"));
	}
	MembersDao dao = new MembersDao();
	List<Members> list = dao.getAllByPage(currentPage, "");
	int totalPage = dao.getAllPage("");
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title>Title</title>
  </head>

  <body>
    <table border="1">
    <tr><td>id</td><td>username</td><td>password</td></tr>
    <%
    	for (int i = 0; i < list.size(); i++) {
    		Members m = list.get(i);
    %>
    <tr><td><%=m.getId()%></td><td><%=m.getUsername()%></td><td><%=m.getPassword()%></td></tr>
    <%
    	}
    %>
    <tr><td colspan="3">
    <% if (currentPage==1) { %>
    	上一页
    <% }else {%>
    <a href="index.jsp?page=<%=currentPage-1%>">上一页</a>
    <% } %>
    |
	<% if (currentPage==totalPage) { %>
    	下一页
    <% }else {%>
    <a href="index.jsp?page=<%=currentPage+1%>">下一页</a>
    <% } %>
	</td></tr>
    </table>
  </body>
</html>

package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DbFactory {
	private Connection conn = null;

	private void getConnection() {
		try {
			Class.forName("net.sourceforge.jtds.jdbc.Driver");
			conn = DriverManager.getConnection(
					"jdbc:jtds:sqlserver://192.168.1.31:1433/test", "sa", "");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void execSqlWithoutResult(String sql, Object[] params) {
		try {
			if (conn == null)
				getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				pstmt.setObject(i + 1, params[i]);
			}
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public ResultSet execSqlWithResult(String sql, Object[] params) {
		ResultSet rs = null;
		try {
			if (conn == null)
				getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				pstmt.setObject(i + 1, params[i]);
			}
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	public void close() {
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

package dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Members;

public class MembersDao {
	private DbFactory df = new DbFactory();

	public void insert(Members member) {
		String sql = "insert into members values(?,?)";
		Object[] params = new Object[] { member.getUsername(),
				member.getPassword() };
		df.execSqlWithoutResult(sql, params);
	}

	public void delete(int id) {
		String sql = "delete from members where id=?";
		Object[] params = new Object[] { id };
		df.execSqlWithoutResult(sql, params);
	}

	public void update(Members member) {
		String sql = "update members set password=? where id=?";
		Object[] params = new Object[] { member.getPassword(), member.getId() };
		df.execSqlWithoutResult(sql, params);
	}

	public Members getById(int id) {
		Members member = null;
		try {
			String sql = "Select * from members where id=?";
			Object[] params = new Object[] { id };
			ResultSet rs = df.execSqlWithResult(sql, params);
			while (rs.next()) {
				member = new Members();
				member.setId(rs.getInt("id"));
				member.setUsername(rs.getString("username"));
				member.setPassword(rs.getString("password"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return member;
	}

	public List<Members> getAll() {
		List<Members> list = new ArrayList<Members>();
		try {
			String sql = "Select * from members";
			Object[] params = new Object[] {};
			ResultSet rs = df.execSqlWithResult(sql, params);
			while (rs.next()) {
				Members m = new Members();
				m.setId(rs.getInt("id"));
				m.setUsername(rs.getString("username"));
				m.setPassword(rs.getString("password"));
				list.add(m);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public List<Members> getAllByPage(int page, String condition) {
		int pageSize = 4;
		List<Members> list = new ArrayList<Members>();
		try {
			int startId = (page - 1) * pageSize + 1, endId = page * pageSize;

			String sql = "select * from (select *,row_number() over(order by id) as rowid from members where username like ?) a "
					+ "where rowid>=" + startId + " and rowid<=" + endId;
			Object[] params = new Object[] { "%" + condition + "%" };
			ResultSet rs = df.execSqlWithResult(sql, params);
			while (rs.next()) {
				Members m = new Members();
				m.setId(rs.getInt("id"));
				m.setUsername(rs.getString("username"));
				m.setPassword(rs.getString("password"));
				list.add(m);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public int getAllPage(String condition) {
		int totalPage = 0, pageSize = 4;
		try {

			String sql = "select (count(*)-1)/" + pageSize
					+ "+1 totalPage from members where username like ?";
			Object[] params = new Object[] { "%" + condition + "%" };
			ResultSet rs = df.execSqlWithResult(sql, params);
			while (rs.next()) {
				totalPage = rs.getInt("totalPage");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return totalPage;
	}
}

package model;

public class Members {
	private int id;
	private String username;
	private String password;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}
}