数据库使用的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>