分页查询概述
概述
- 如果不做分页查询,一次会查询过多的数据,加载速度过慢,浏览器渲染速度过慢,程序容易崩溃。
分页查询分类
- 逻辑分页
- 一次性将所有的数据查询出来,查出来以后存放到一个List集合中.使用List集 合中方法subList()对List集合进行截取.
- 优点:减少与数据库交互次数.
- 缺点:数据量非常大.查询时间比较长.
- 物理分页(主流)
- 不是一次性将所有数据全部查询出来.查询第一页:发送一条查询10条的SQL语句.查询下 一页数据:又发送一条查询后10条的SQL.
- 优点:数据量非常大.查询时间比较短.
- 缺点:与数据库交互次数变多.
MYSQL数据库进行分页操作:
使用关键字:limit.
- select * from 表 where 条件 limit begin,size;
- begin:从哪开始查询.
- size:每次查询记录数
总结
- begin = (currentPage - 1) * pageSize;
分页查询思路分析
-
效果图:
-
分析图
代码实现:
-
首先创建一个数据库test,里面创建一个tb_user表
-
接着在idea中构建一个web项目
-
创建User实体类(这里我用lombok插件简化了get和set方法等)
@Data
@NoArgsConstructor
@AllArgsConstructor
@RequiredArgsConstructor
public class User {
private Integer id;
@NotNull
private String name;
@NotNull
private String password;
}
- PageBean.java(将分页所需要的参数封装成一个分页类)
@Data
public class PageBean<T> {
private Long currentPage; // 当前页
private Long pageSize = 3L; // 每页记录数
private Long totalCount; // 总记录数
private Long totalPage; // 总页数
private Long pre; // 上一页
private Long next; // 下一页
private Long beginIndex; // 开始索引
private Boolean hasPre; // 是否还有上一页
private Boolean hasNext; // 是否有下一页
private List<T> data; // 列表数据
public PageBean() {
}
public PageBean(Long currentPage, Long totalCount) {
this.currentPage = currentPage;
this.totalCount = totalCount;
totalPage = totalCount/pageSize == 0 ? totalCount/pageSize : totalCount/pageSize+1;
hasPre = currentPage - 1 > 0;
hasNext = currentPage + 1 <= totalPage;
pre = hasPre ? currentPage-1 : 1;
next = hasNext ? currentPage+1 : totalPage;
beginIndex = (currentPage-1) * pageSize;
}
}
- c3p0-config.xml (连接数据库的配置)
<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/数据库名称?useSSL=false&characterEncoding=utf-8</property>
<property name="user">连接数据库账号</property>
<property name="password">连接数据库密码</property>
<!--增加连接数-->
<property name="acquireIncrement">10</property>
<!--初始连接数-->
<property name="initialPoolSize">10</property>
<!--最大空闲时间-->
<property name="maxIdleTime">30</property>
<!--最大连接数-->
<property name="maxPoolSize">100</property>
<!--最小连接数-->
<property name="minPoolSize">5</property>
</default-config>
</c3p0-config>
- 因为项目分三层架构,所以接下来分别创建三层的接口和实现类
- dao层(主要操纵数据库的数据)这里我用了dbutis工具类和c3p0连接池
public interface UserDao {
List<User> selectAll(Long startPage, Long pageSize) throws SQLException;
Long queryCount() throws SQLException;
}
public class UserDaoImpl implements UserDao {
@Override
public List<User> selectAll(Long startPage, Long pageSize) throws SQLException {
return new QueryRunner(JDBCUtils.getDataSource()).query("select * from tb_user limit ?,?",
new BeanListHandler<User>(User.class),startPage,pageSize);
}
@Override
public Long queryCount() throws SQLException {
return new QueryRunner(JDBCUtils.getDataSource()).query("select count(*) from tb_user",new ScalarHandler<Long>());
}
}
- service层(处理一些业务逻辑的)
public interface UserService {
PageBean<User> selectAll(Long currentPage)throws SQLException;
}
- 在业务层这里处理分页所需要的数据
public class UserServiceImpl implements UserService {
private UserDao userDao = new UserDaoImpl();
@Override
public PageBean<User> selectAll(Long currentPage) throws SQLException {
PageBean<User> userPageBean = new PageBean<>(currentPage,userDao.queryCount());
List<User> users = userDao.selectAll(userPageBean.getBeginIndex(),userPageBean.getPageSize());
userPageBean.setData(users);
return userPageBean;
}
}
- controller层(将dao层从数据库获取的数据带到页面去渲染,页面跳转)
@WebServlet("/userlist")
public class UserServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String inputCurrentPage = request.getParameter("currentPage");
Long currentPage = 1l;
if (inputCurrentPage != null && !inputCurrentPage.equals("")){
currentPage = Long.parseLong(inputCurrentPage);
}
UserService userService = new UserServiceImpl();
PageBean<User> pageBean = null;
try {
pageBean = userService.selectAll(currentPage);
} catch (SQLException e) {
e.printStackTrace();
}
request.setAttribute("pageBean",pageBean);
request.getRequestDispatcher("/index.jsp").forward(request,response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
}
- index.jsp (显示页面)
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<title>所有用户</title>
</head>
<body>
<table border="1px" cellpadding="30px" cellspacing="0px" width="800" height="100">
<thead>
<th>ID</th>
<th>用户名</th>
<th>密码</th>
</thead>
<tbody>
<c:forEach items="${pageBean.data}" var="user">
<tr>
<td>${user.id}</td>
<td>${user.name}</td>
<td>${user.password}</td>
</tr>
</c:forEach>
<tr>
<td colspan="3" align="center">
第${pageBean.currentPage}/${pageBean.totalPage}页 总记录数:${pageBean.totalCount} 每页显示${pageBean.pageSize}条
<c:choose>
<c:when test="${pageBean.currentPage == 1}">
<span>[首页]</span>
</c:when>
<c:otherwise>
<a href="${pageContext.request.contextPath}/userlist?currentPage=1">[首页]</a>
</c:otherwise>
</c:choose>
<c:choose>
<c:when test="${pageBean.hasPre}">
<a href="${pageContext.request.contextPath}/userlist?currentPage=${pageBean.currentPage-1}">[上一页]</a>
</c:when>
<c:otherwise>
<span>[上一页]</span>
</c:otherwise>
</c:choose>
<c:choose>
<c:when test="${pageBean.hasNext}">
<a href="${pageContext.request.contextPath}/userlist?currentPage=${pageBean.currentPage+1}">[下一页]</a>
</c:when>
<c:otherwise>
<span>[下一页]</span>
</c:otherwise>
</c:choose>
<c:choose>
<c:when test="${pageBean.currentPage == pageBean.totalPage}">
<span>[尾页]</span>
</c:when>
<c:otherwise>
<a href="${pageContext.request.contextPath}/userlist?currentPage=${pageBean.totalPage}">[尾页]</a>
</c:otherwise>
</c:choose>
跳转至第<select onchange="go()" id="page">
<option value="${pageBean.currentPage}">${pageBean.currentPage}</option>
<c:forEach begin="1" end="${pageBean.totalPage}" var="i">
<option value="${i}">${i}</option>
</c:forEach>
</select>页
<script type="text/javascript">
function go() {
var cp = document.getElementById("page").value;
window.location.href = "${pageContext.request.contextPath}/userlist?currentPage="+cp;
}
</script>
</td>
</tr>
</tbody>
</table>
</body>
</html>
- 最终效果
Servlet文件上传与下载
概述
-
文件上传:是指把本地文件以流的形式提交到服务器上,服务器读取该文件流,并写入到服务器的磁盘上。
-
文件在线查看:是指把服务上的文件以流的形式读取,并写入到本地内存。
-
文件下载:是指把服务器的文件以流的形式读取,并写入到本地磁盘。
-
字节流、文件流、method只能是post
上传的基本原理:
表单默认以application/x-www-form-urlencoded格式进行提交,提交后后台接收的数据为:username=zhangsan&age=20&photo=1.txt,此时servlet会将数据进行字符串切割,使用request.getParameter就可以得到不同的name对应的值。
此时文件流根本没有上传,无法实现文件上传的效果。
要实现文件上传应该把表单的提交格式改成:multipart/form-data,此时会将所有的数据提交到inputStream中,数据内容格式为:
------WebKitFormBoundary7fU67Im8KiBN8Zfm
Content-Disposition: form-data; name="username"
zhangsan
------WebKitFormBoundary7fU67Im8KiBN8Zfm
Content-Disposition: form-data; name="age"
20
------WebKitFormBoundary7fU67Im8KiBN8Zfm
Content-Disposition: form-data; name="photo"; filename="1.txt"
Content-Type: text/plain
AAAAA
BBBBB
CCCCC
DDDDD
------WebKitFormBoundary7fU67Im8KiBN8Zfm--
此时,需要切割字符串,并分析字符串,才能得到文件内容和文本内容。
在Servlet3.0之前的版本,Servlet本身没有提供此功能,需要借助第三方库才可以使用文件上传解析,手动解析代码比较复杂。
在Servlet3.0之后的版本,可以使用@MultipartConfig注解来实现文件上传的解析。代码中需要使用request.getParameter()来接收文本内容,用request.getPart()来处理文件内容。
- 为了让上传的文件名称不重复,可以使用系统时间来生成唯一的字符串。
文件查看和下载
-
文件查看即将服务器上的文件以InputStream的方式打开,并写入到response的outputStream中。
-
文件下载只需要将response的header设置为下载即可。
案列流程图
建库建表
- 创建tb_files表,用于存储文件信息
create table tb_files(
id int primary key auto_increment,
name varchar(100) not null unique,
path varchar(200) not null,
info varchar(400)
);
- 创建实体类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class FileDTO implements Serializable {
private Integer fid;
private String fname;
private String fpath;
private String info;
}
这里就不复制dao接口的方法了直接给实现类
- FileDAOImpl
public class FileDAOImpl implements FileDAO {
private Connection conn = null;
private PreparedStatement pstm = null;
private ResultSet rs = null;
@Override
public int insertFile(FileDTO fileDTO) {
try {
conn = JDBCUtils2.getConnection();
pstm = conn.prepareStatement("insert into tb_files(name,path,info) values(?,?,?)");
pstm.setString(1,fileDTO.getFname());
pstm.setString(2,fileDTO.getFpath());
pstm.setString(3,fileDTO.getInfo());
int i = pstm.executeUpdate();
return i;
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils2.closeAll(conn,pstm,null);
}
return 0;
}
@Override
public List<FileDTO> selectAll() {
List<FileDTO> fileDTOS = new ArrayList<>();
try {
conn = JDBCUtils2.getConnection();
pstm = conn.prepareStatement("select id,name,path,info from tb_files");
rs = pstm.executeQuery();
while (rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String path = rs.getString("path");
String info = rs.getString("info");
FileDTO fileDTO = new FileDTO(id,name,path,info);
fileDTOS.add(fileDTO);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils2.closeAll(conn,pstm,rs);
}
return fileDTOS;
}
@Override
public List<FileDTO> queryFileByPageBean(long pageNum, long pageSize) {
List<FileDTO> fileDTOS = new ArrayList<>();
try {
conn = JDBCUtils2.getConnection();
pstm = conn.prepareStatement("select id,name,path,info from tb_files limit ?,?");
pstm.setLong(1,pageNum);
pstm.setLong(2,pageSize);
rs = pstm.executeQuery();
while (rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String path = rs.getString("path");
String info = rs.getString("info");
FileDTO fileDTO = new FileDTO(id,name,path,info);
fileDTOS.add(fileDTO);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils2.closeAll(conn,pstm,rs);
}
return fileDTOS;
}
@Override
public int queryTotalCount() {
try {
conn = JDBCUtils2.getConnection();
pstm = conn.prepareStatement("select count(*) from tb_files");
rs = pstm.executeQuery();
rs.next();
int row = rs.getInt(1);
return row;
}catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils2.closeAll(conn,pstm,rs);
}
return 0;
}
}
- FileServiceImpl.java
public class FileServiceImpl implements FileService {
private FileDAO fileDAO = new FileDAOImpl();
@Override
public boolean uplodeFile(FileDTO fileDTO) {
int i = fileDAO.insertFile(fileDTO);
return i != 0;
}
@Override
public List<FileDTO> selectAll() {
return fileDAO.selectAll();
}
@Override
public PageBean<FileDTO> queryFileByPageBean(long currentPage) {
PageBean<FileDTO> pageBean = new PageBean<>(currentPage,(long)fileDAO.queryTotalCount());
pageBean.setData(fileDAO.queryFileByPageBean(pageBean.getBeginIndex(),pageBean.getPageSize()));
return pageBean;
}
}
- FileUploadServlet .java(上传文件代码实现)
// 专门用于文件上传的注解
@MultipartConfig
@WebServlet("/upload")
public class FileUploadServlet extends HttpServlet {
private FileService fileService = new FileServiceImpl();
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 接收表单提交的多个文件
//Collection<Part> parts = request.getParts();
// 接收普通字段
String info = request.getParameter("info");
// 接收表单提交的文件
Part part = request.getPart("myFile");
String header = part.getHeader("Content-Disposition");
// 获取文件后缀
String ext = header.substring(header.lastIndexOf("."), header.lastIndexOf("\""));
// 生成新的文件名
String fileName = System.currentTimeMillis() + ext;
// 获取文件在服务器中的存储路径
String realPath = getServletContext().getRealPath("/files");
String savePath = realPath +"/" + fileName;
// 保存文件
part.write(savePath);
// 文件的访问路径(存储到数据库)
String path = "files/" + fileName;
boolean flag = fileService.uplodeFile(new FileDTO(null, fileName, path, info));
//跳转到提示页面
String tips = flag?"<label style='color:green'>文件上传成功!</label>":"<label style='color:red'>文件上传失败!</label>";
request.setAttribute("tips",tips);
request.getRequestDispatcher("prompt.jsp").forward(request,response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
}
- DownloadServlet.java(下载文件代码实现)
@WebServlet("/download")
public class DownloadServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//1.接收文件名
String filename = request.getParameter("filename");
//2.文件下载:读取存储在files目录中的文件,写给浏览器
String savaPath = getServletContext().getRealPath("/files") + "/" + filename;
FileInputStream fis = new FileInputStream(savaPath);
// 设置响应头
response.setContentType("application/pdf");
response.addHeader( "Content-Disposition","attachment; filename="+filename );
response.setContentLength(fis.available());
ServletOutputStream os = response.getOutputStream();
int len = -1;
byte[] bs = new byte[1024*8];
while ((len = fis.read(bs)) != -1){
os.write(bs,0,len);
}
fis.close();
os.close();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
}
- ShowAllServlet .java
@WebServlet("/show")
public class ShowAllServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String str_currentPage = request.getParameter("currentPage");
Long currentPage = 1L;
if (str_currentPage != null && !str_currentPage.equals("")){
currentPage = Long.valueOf(str_currentPage);
}
FileService fileService = new FileServiceImpl();
PageBean<FileDTO> pageBean = fileService.queryFileByPageBean(currentPage);
request.setAttribute("pageBean",pageBean);
request.getRequestDispatcher("download.jsp").forward(request,response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
}
显示页面
- index.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>文件上传/下载</title>
</head>
<body>
<h2>文件上传与下载案列</h2>
<a href="upload.jsp">文件上传</a>
<a href="/show">文件下载</a>
</body>
</html>
-
效果图
-
upload.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>文件上传</title>
</head>
<body>
<form action="/upload" method="post" enctype="multipart/form-data">
<p>文件上传:<input type="file" name="myFile" /></p>
<p>文件描述:<input type="text" name="info" /></p>
<input type="submit" value="提交">
</form>
</body>
</html>
- 效果图
- download.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>文件下载</title>
</head>
<body>
<a href="index.jsp">返回首页</a><br>
<table border="1px" cellspacing="0px" cellpadding="30px">
<thead>
<th>ID</th>
<th>图片展示</th>
<th>图片描述</th>
<th>点我下载</th>
</thead>
<tbody>
<c:forEach items="${pageBean.data}" var="file">
<tr>
<td>${file.fid}</td>
<td><img src="${file.fpath}" alt="文件失效了" height="150px" ></td>
<td>${file.info}</td>
<td><a href="/download?filename=${file.fname}">${file.fname}</a></td>
</tr>
</c:forEach>
<tr>
<td colspan="4" align="center">
第${pageBean.currentPage}/${pageBean.totalPage}页 总记录数:${pageBean.totalCount} 每页显示${pageBean.pageSize}条
<c:if test="${pageBean.currentPage != 1}">
<a href="${pageContext.request.contextPath}/show?currentPage=1">[首页]</a>
<a href="${pageContext.request.contextPath}/show?currentPage=${pageBean.currentPage-1}">[上一页]</a>
</c:if>
<c:if test="${pageBean.currentPage != pageBean.totalPage}">
<a href="${pageContext.request.contextPath}/show?currentPage=${pageBean.currentPage+1}">[下一页]</a>
<a href="${pageContext.request.contextPath}/show?currentPage=${pageBean.totalPage}">[尾页]</a>
</c:if>
</td>
</tr>
</tbody>
</table>
</body>
</html>
- 效果图
- prompt.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<h4>提示页面</h4>
<hr>
<label>${tips}</label>
<br/>
<a href="upload.jsp">继续上传</a>
<a href="/show">下载</a>
</body>
</html>
- 效果图:
注意:上传文件需要在服务器中创建一个文件夹。我这里所创建的文件夹是名称为files