分页查询所有文章
(一) 前端文章表
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>全部文章列表</title>
<script src="jquery-1.9.1.min.js"></script>
<script>
jQuery(function () {
jQuery.getJSON("list",{},function (data) {
if(data != null && data.succ == 1) {
//查询成功
var html = "";
for(var i = 0; i < data.list.length; i++) {
var art = data.list[i];
html += "<tr>\n" +
" <td>"+art.id+"</td>\n" +
" <td>"+art.title+"</td>\n" +
" <td>"+art.username+"</td>\n" +
" <td>"+art.createtime+"</td>\n" +
" <td>"+art.rcount+"</td>\n" +
" </tr>";
}
jQuery("#tab").append(html);
}else {
alert("查询失败："+data.msg);
}
});
})
</script>
</head>
<body>
<div style="margin-top: 50px">
<table id="tab" border="1" style="width: 80%" align="center">
<tr>
<td>编号</td>
<td>标题</td>
<td>作者</td>
<td>发布时间</td>
<td>阅读量</td>
</tr>
</table>
</div>
</body>
</html>
(二) 新增VO层
实体类
- 基础数据类UserInfo、ArticleInfo
- VO(view object)层：展示层对象，是基础数据类的扩充
因为我们需要把作者姓名也查询出来，所以需要联合查询，自然需要一个又有文章属性，也有用户的作者属性，就增加一个展示层对象。
package models.vo;
import models.ArticleInfo;
/**
* Created with IntelliJ IDEA.
* Description:增加作者属性
* User: starry
* Date: 2021 -04 -13
* Time: 14:51
*/
public class ArticleInfoVO extends ArticleInfo {
private String username;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
}
(三) 操作数据库进行联合查询
在ArticleInfoDao中添加新的方法
/**
* 操作数据库查询文章及作者
* @return
* @throws SQLException
*/
public List<ArticleInfoVO> getAllArticle() throws SQLException {
Connection connection = DBUtils.getConnection();
String sql = "select a.*,u.username from articleinfo a,userinfo u where a.uid = u.id";
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet resultSet = statement.executeQuery();
List<ArticleInfoVO> list = new ArrayList<>();
while(resultSet.next()) {
ArticleInfoVO art = new ArticleInfoVO();
art.setId(resultSet.getInt("id"));
art.setTitle(resultSet.getString("title"));
art.setUsername(resultSet.getString("username"));
art.setCreatetime(resultSet.getDate("createtime"));
art.setRcount(resultSet.getInt("rcount"));
list.add(art);
}
DBUtils.close(resultSet,statement,connection);
return list;
}
(四) 编写servlet进行业务处理，响应给前端
package services;
import dao.ArticleInfoDao;
import models.vo.ArticleInfoVO;
import utils.ResultJSONUtils;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
/**
* Created with IntelliJ IDEA.
* Description:所有文章列表
* User: starry
* Date: 2021 -04 -13
* Time: 14:40
*/
@WebServlet("/list")
public class ListServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request, response);
}
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
int succ = -1; // succ=1 表示操作成功
String msg = ""; // 错误说明信息
List<ArticleInfoVO> list = null;
// 1.从前端获取参数
// 2.调用数据库执行相应的业务逻辑
ArticleInfoDao dao = new ArticleInfoDao();
try {
list = dao.getAllArticle();
succ = 1;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
// 3.将上一步操作的结果返回给前端
HashMap<String, Object> result = new HashMap<>();
result.put("succ", succ);
result.put("msg", msg);
result.put("list",list);
ResultJSONUtils.write(response, result);
}
}
这样没有分页的查询已经做出来了
接下来我们就加上分页功能
(五) 构建正确的url地址参数
要想时间分页功能，首先要把url地址改成？p=n这样子，代表这是第n页
点击上一页或者下一页跳转到正确的url地址
- 增加上一页下一页跳转链接
- 设置全局变量page，编写操作页码函数
- 更新全局页码信息
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>全部文章列表</title>
<script src="jquery-1.9.1.min.js"></script>
<script>
//当前页码数
var page = 1;
//操作页码函数
function mypage(type) {
if(type == 1) {
//上一页
if(page > 1) {
location.href = "list.html?p="+(page-1);
}else {
alert("已经在首页了");
}
}else {
//下一页
location.href = "list.html?p="+(page+1);
}
}
//页面加载完成之后执行该函数
jQuery(function () {
//更新全局的页码信息
var param = location.search;
if(param != "") {
//不在首页
page = parseInt(param.split("=")[1]);
}
jQuery.getJSON("list",{
"page":page, //当前页码数
"psize":2 //每页显示两条数据
},function (data) {
if(data != null && data.succ == 1) {
//查询成功
var html = "";
for(var i = 0; i < data.list.length; i++) {
var art = data.list[i];
html += "<tr>\n" +
" <td>"+art.id+"</td>\n" +
" <td>"+art.title+"</td>\n" +
" <td>"+art.username+"</td>\n" +
" <td>"+art.createtime+"</td>\n" +
" <td>"+art.rcount+"</td>\n" +
" </tr>";
}
jQuery("#tab").append(html);
}else {
alert("查询失败："+data.msg);
}
});
})
</script>
</head>
<body>
<div style="margin-top: 50px; text-align: center" >
<table id="tab" border="1" style="width: 80%" align="center">
<tr>
<td>编号</td>
<td>标题</td>
<td>作者</td>
<td>发布时间</td>
<td>阅读量</td>
</tr>
</table>
<p></p>
<a href="javascript:mypage(1)">上一页</a>
<a href="javascript:mypage(2)">下一页</a>
</div>
</body>
</html>
(六) 获取当前url中的页码，请求后端查询相应的数据
package services;
import dao.ArticleInfoDao;
import models.vo.ArticleInfoVO;
import utils.ResultJSONUtils;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
/**
* Created with IntelliJ IDEA.
* Description:所有文章列表
* User: starry
* Date: 2021 -04 -13
* Time: 14:40
*/
@WebServlet("/list")
public class ListServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request, response);
}
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
int succ = -1; // succ=1 表示操作成功
String msg = ""; // 错误说明信息
List<ArticleInfoVO> list = null;
// 1.从前端获取参数
int page = Integer.parseInt(request.getParameter("page"));
int psize = Integer.parseInt(request.getParameter("psize"));
// 2.调用数据库执行相应的业务逻辑
ArticleInfoDao dao = new ArticleInfoDao();
try {
list = dao.getAllArticleByPage(page,psize);
succ = 1;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
// 3.将上一步操作的结果返回给前端
HashMap<String, Object> result = new HashMap<>();
result.put("succ", succ);
result.put("msg", msg);
result.put("list",list);
ResultJSONUtils.write(response, result);
}
}
(七) 操作数据库实现分页查询
在ArticleInfoDao类添加有分页查询功能函数
这里limit ?,?
- 偏移量——公式：（页码数page - 1）* 查询条数
- 查询条数——psize
/**
* 分页：
* 操作数据库分页查询文章及作者
* @param page
* @param psize
* @return
* @throws SQLException
*/
public List<ArticleInfoVO> getAllArticleByPage(int page, int psize) throws SQLException {
Connection connection = DBUtils.getConnection();
String sql = "select a.*,u.username from articleinfo a,userinfo u where a.uid = u.id limit ?,?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1,(page-1)*psize);
statement.setInt(2,psize);
ResultSet resultSet = statement.executeQuery();
List<ArticleInfoVO> list = new ArrayList<>();
while(resultSet.next()) {
ArticleInfoVO art = new ArticleInfoVO();
art.setId(resultSet.getInt("id"));
art.setTitle(resultSet.getString("title"));
art.setUsername(resultSet.getString("username"));
art.setCreatetime(resultSet.getDate("createtime"));
art.setRcount(resultSet.getInt("rcount"));
list.add(art);
}
DBUtils.close(resultSet,statement,connection);
return list;
}
这样我们就实现了简单分页的查询功能了！！！
290
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
