一个简单的mysql分页查询

数据库使用的mysql
使用分批查询，分批显示的方法
mysql排序是从0开始，页面显示10条，则：

第一页	显示第0条到第9条
第二页	显示第10条到第19条
第三页	显示第20条到第29条
第二页	显示第30条到第39条
第N页	显示第（N-1）乘10条到第N乘10-1条

mysql查询代码：SELECT * FROM XXX LIMIT （从这个序号开始查询）,（查询序号后的N条数据）;
例：SELECT * FROM user LIMIT 0,10;
查询第一页的数据，从第1条开始查询，查询10条数据

关键代码：
javabean

public class User {
	private Integer id;
	private String name;
	private String question;
	private String answer;
	private Boolean isanswer;

	public User() {
	}
	......
	//省略相应的getter和setter方法
	}

Jdbc

package com.util;

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

public class JdbcUtil {
	// 加载DB驱动
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	private static Connection conn;

	// 获取Connection对象
	public static Connection getConnection() throws SQLException {
		String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT";
		String user = "root";
		String password = "123456";
		if (conn == null || conn.isClosed()) {
			conn = DriverManager.getConnection(url, user, password);
		}
		return conn;
	}

	// 关闭资源对象
	public static void close(Connection conn, Statement stmt, ResultSet rs) throws SQLException {
		if (conn != null && !conn.isClosed()) {
			conn.close();
		}
		if (stmt != null && !stmt.isClosed()) {
			stmt.close();
		}
		if (rs != null && !rs.isClosed()) {
			rs.close();
		}
	}
}

配置web.xml

	<servlet>
		<servlet-name>UserSelectServlet</servlet-name>
		<servlet-class>com.servlet.UserSelectServlet</servlet-class>
	</servlet>
	<servlet-mapping>
		<servlet-name>UserSelectServlet</servlet-name>
		<url-pattern>/userSelectServlet</url-pattern>
	</servlet-mapping>
</web-app>

servlet代码，获取到jsp传的页码，调用方法在数据库查询，将查询到的结果返回到list，将list的值放进域，跳转到jsp页面显示

package com.servlet;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.bean.User;
import com.service.IUserSelectService;
import com.service.UserSelectServiceImpl;

public class UserSelectServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		// 获取页码
		String num = request.getParameter("num");
		
	
		// 创建Service对象
		IUserSelectService service = new UserSelectServiceImpl();

		// 调用service对象上的方法
		List<User> list = service.userSelcet(num);

		// 将结果添加到域中
		request.setAttribute("user", list);

		// 跳转页面
		request.getRequestDispatcher("/fenye.jsp").forward(request, response);

	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doGet(request, response);
	}

}

dao层代码：将数据库查询结果返回

package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.bean.User;
import com.util.JdbcUtil;

public class UserSelectDaoImpl implements IUserSelectDao {

	private Connection conn;
	private Statement stmt;
	private PreparedStatement ps;
	private ResultSet rs;

	@Override
	public List<User> userSelect(String num) {
		
		//将页码参数的类型转换成Integer
		Integer Num = Integer.parseInt(num);
		System.out.println(Num);
		//将页码参数*每页显示的数目
		Num = Num * 2;

		List<User> list = new ArrayList<>();

		try {
			conn = JdbcUtil.getConnection();
			//查询从页码*每页显示的数目之后的2条数据
			String sql = "SELECT * FROM message LIMIT ?,2";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, Num);
			rs = ps.executeQuery();
			//将查询到的数据装入list
			while (rs.next()) {
				User user = new User();
				user.setName(rs.getString("name"));
				user.setQuestion(rs.getString("question"));
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				JdbcUtil.close(conn, stmt, rs);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		//返回list
		return list;
	}
}

页面显示：

<%@ page language="java" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html>
<html>
<head>
<title>首页</title>
</head>
<body>
	<table border: 1px>
		<tr>
			<th>姓名</th>
			<th>问题</th>
		</tr>
		<tr>
		//遍历结果并打印
			<c:forEach items="${user }" var="user">
				<td>${user.name }</td>
				<td>${user.question }</td>
			</c:forEach>
		</tr>
	</table>
	//分页导航栏，跳转servlet并传参数（页码的值-1）
	<ul>
		<li><a href="/fenyeSelect/userSelectServlet?num=0">1</a></li>
		<li><a href="/fenyeSelect/userSelectServlet?num=1">2</a></li>
		<li><a href="/fenyeSelect/userSelectServlet?num=2">3</a></li>
	</ul>
</body>
</html>