【温故知新】Java web 开发（四）JSTL 与 JDBC 的增删改查
2024-09-03 07:36:50
本篇开始使用 jstl 这个 jsp 的标签库，在同一个 Servlet 中实现处理 CRUD 请求，以及使用 jdbc 数据库基本操作。然后你会发现 Servlet 和 jdbc 还是有很多不方便之处，然后在篇章五将开始正式使用框架。
使用 jstl 是为了前后端分离，试想如果 jsp 中嵌入一堆的 java 代码片段，那样的话前端就很难开发除非它懂 java 技术，或者得由后端开发这个。
这次我们就做一个书籍列表的功能，记录购买过的书籍的名称、价格和页数信息，可以添加、删除、修改单本书籍信息（同时删除多本书籍这个需要 js 的帮助，这里暂时不用，留在之后回顾）。
1. 简单的逻辑梳理
要实现增删改查需要设计一下流程，这里只给出一个简单的设计：
首页 有展示书籍列表的入口，
书籍列表页提供一个表格展示数据库中存入的书籍，每个书籍（每行记录后）都附带编辑和删除操作，
编辑需要跳转到编辑页面实现，过程中需要查询一次数据库得到数据，而不是从列表页直接获取传递，编辑书籍页面提交给真正的编辑方法来执行入库
删除操作不需要专门页面来处理，直接数据库操作，返回结果展示到一个消息页面，该页面通用，
表格下方有添加数据的链接，添加需要跳转到添加页面实现，添加书籍页面数据提交给真正的添加动作方法来执行入库，
如删除操作所述，基本页面的操作结果由通用消息页面展示，该页面根据传递过来的字符串和URL决定跳转到地址。
2. jstl el 表达式
使用 el 表达式必须关掉 忽略 el 表达式的设定，如下<%@ page isELIgnored ="false" %>，如果不关闭，则页面上会出现很多${"sth"}，不会被解析成 jstl 标签。
常用的 jstl 库及其用法示例
- 核心库 <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
- 格式化文本库 <%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
- 函数库 <%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
核心库，提供了类似语言的基础服务，比如流程控制，输出语句，赋值语句等。
格式化文本，顾名思义，可以格式化时间、浮点数等。
函数库，提供了很多函数，比如字符串的操作如子串，长度等功能，还有map、list这种集合类判空等方法，自行研究。
这里贴几段示例
第一个：用到的有核心库里的流程控制语句，还有循环语句，函数库的长度方法以及处理 map 的方法。
<c:choose>
<c:when test="${empty fileMap or fn:length(fileMap) == 0}">
<p>您还没有上传文件，请点击这里上传：<a href=/file/add>上传文件</a></p>
</c:when>
<c:otherwise>
<c:forEach items="${fileMap}" var="entry">
<p>文件名: ${entry.key} <a href="/file/download?fileName=${entry.value}" >下载</a></p>
</c:forEach>
</c:otherwise>
</c:choose>
第二个：用到的有格式化浮点数的语句
<c:forEach items="${bookList}" var="book">
<tr>
<th>ids</th>
<th><input type="checkbox" value="${book.id}" /></th>
<td>${book.name}</td>
<td><fmt:formatNumber value="${book.price}" pattern="0.00"/></td>
<td>${book.pageCount}</td>
<td><a href="/book?method=update&id=${book.id}">编辑</a> <a href="/book?method=delete&id=${book.id}">删除</a></td>
</tr>
</c:forEach>
差点忘记说了，上边有取数的操作，比如最开始的${bookList}、${fileMap}，它是怎样从 Servlet 传递到 jsp 中的呢？
答案是 servlet 中用 request.setAttribute("bookList", bookList);这种方式。
3. 数据库操作 jdbc
创建数据库连接的基本动作步骤
- 加载 jdbc 驱动类 DriverManager
- 创建连接（连接URL的格式）
- 创建 statement， 防止 Sql 注入的 PreparedStatement
- 执行语句 查询 executeQuery(); 更改、删除 executeUpdate()
- 处理返回结果 ResultSet
- 关闭连接
public class JDBCTest {
public static void main(String[] args) {
String driver = "com.mysql.jdbc.Driver";
String dbName = "spring";
String passwrod = "root";
String userName = "root";
String url = "jdbc:mysql://localhost:3308/" + dbName;
String sql = "select * from users";
try {
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, userName,
passwrod);
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println("id : " + rs.getInt(1) + " name : "
+ rs.getString(2) + " password : " + rs.getString(3));
}
// 关闭记录集
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭声明
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭链接对象
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
4. 具体实现
因为一个 Servlet 只有一个 service 方法，所以它能处理的请求URL也只有一类，为了实现 CRUD 操作，采用 /book?method=list这种方式以method参数区分。这里的实现不是为了展示技术的，所以没有用RESTFul 的方式，也不要嫌 low，正是因为 servlet 的不好用之处，才逼着我们去用更好用的框架比如 Spring MVC，对吧。
BookServlet.java
@WebServlet(name = "bookServlet", urlPatterns = {"/book"})
public class BookServlet extends HttpServlet {
@Override
public void init() {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
@Override
public void service(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException{
request.setCharacterEncoding("UTF-8");
String method = request.getParameter("method");
Connection conn;
try {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/webpractice", "root", "Circle233???");
switch (method) {
case "list":
list(request, response, conn);
break;
case "add":
add(request, response);
break;
case "create":
create(request, response, conn);
break;
case "delete":
delete(request, response, conn);
break;
case "update":
update(request, response, conn);
break;
case "updateOp":
updateOp(request, response, conn);
break;
default:
break;
}
} catch (Exception e) {
e.printStackTrace();
}
}
private void add(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
request.getRequestDispatcher("/WEB-INF/page/book_add.jsp").forward(request, response);
}
private void create(HttpServletRequest request, HttpServletResponse response, Connection conn) throws IOException, ServletException {
String message;
PreparedStatement ps = null;
String insertSql = "insert into books(name, price, page_count) values(?,?,?)";
try {
String name = request.getParameter("name");
String pricePara = request.getParameter("price");
double price = Double.valueOf(pricePara);
String pageCountPara = request.getParameter("pageCount");
int pageCount = Integer.parseInt(pageCountPara);
ps = conn.prepareStatement(insertSql);
ps.setString(1, name);
ps.setDouble(2, price);
ps.setInt(3, pageCount);
ps.executeUpdate();
message = "添加书籍成功！";
request.setAttribute("message",message);
request.setAttribute("suggestURL", "/book?method=list");
request.setAttribute("declaration", "查看书籍列表");
request.getRequestDispatcher("/WEB-INF/page/message.jsp").forward(request, response);
} catch (Exception e) {
e.printStackTrace();
message = "添加书籍出错！";
request.setAttribute("message",message);
request.setAttribute("suggestURL", "/book?method=add");
request.setAttribute("declaration", "重新添加书籍");
request.getRequestDispatcher("/WEB-INF/page/message.jsp").forward(request, response);
} finally {
returnResource(null, ps, conn);
}
}
private void delete(HttpServletRequest request, HttpServletResponse response, Connection conn) throws IOException, ServletException {
String message;
PreparedStatement ps = null;
String deleteSql = "delete from books where id = (?)";
try {
String id = request.getParameter("id");
ps = conn.prepareStatement(deleteSql);
ps.setString(1, id);
ps.executeUpdate();
message = "删除书籍成功！";
request.setAttribute("message",message);
request.setAttribute("suggestURL", "/book?method=list");
request.setAttribute("declaration", "查看书籍列表");
request.getRequestDispatcher("/WEB-INF/page/message.jsp").forward(request, response);
} catch (Exception e) {
e.printStackTrace();
message = "删除书籍出错！";
request.setAttribute("message",message);
request.setAttribute("suggestURL", "/book?method=list");
request.setAttribute("declaration", "回到书籍列表");
request.getRequestDispatcher("/WEB-INF/page/message.jsp").forward(request, response);
} finally {
returnResource(null, ps, conn);
}
}
private void update(HttpServletRequest request, HttpServletResponse response, Connection conn) throws IOException, ServletException {
PreparedStatement ps = null;
ResultSet rs = null;
String message;
try {
String queryOneSql = "select * from books where id = ?";
ps = conn.prepareStatement(queryOneSql);
ps.setString(1, request.getParameter("id"));
rs = ps.executeQuery();
if (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
double price = rs.getDouble("price");
int pageCount = rs.getInt("page_count");
Book book = new Book(name, price, pageCount);
book.setId(id);
request.setAttribute("book", book);
request.getRequestDispatcher("/WEB-INF/page/book_update.jsp").forward(request, response);
} else {
message = "没有查到对应书籍，请刷新列表页面！";
request.setAttribute("message",message);
request.setAttribute("suggestURL", "/book?method=list");
request.setAttribute("declaration", "回到书籍列表");
request.getRequestDispatcher("/WEB-INF/page/message.jsp").forward(request, response);
}
} catch (Exception e) {
e.printStackTrace();
message = "编辑书籍出错！";
request.setAttribute("message",message);
request.setAttribute("suggestURL", "/book?method=list");
request.setAttribute("declaration", "回到书籍列表");
request.getRequestDispatcher("/WEB-INF/page/message.jsp").forward(request, response);
} finally {
returnResource(rs, ps, conn);
}
}
private void updateOp(HttpServletRequest request, HttpServletResponse response, Connection conn) throws IOException, ServletException {
PreparedStatement ps = null;
ResultSet rs = null;
String message;
try {
String updateSql = "update books set name = ?, price = ?, page_count = ? where id = ?";
ps = conn.prepareStatement(updateSql);
ps.setString(1, request.getParameter("name"));
ps.setDouble(2, Double.valueOf(request.getParameter("price")));
ps.setInt(3, Integer.parseInt(request.getParameter("pageCount")));
ps.setInt(4, Integer.parseInt(request.getParameter("id")));
int result = ps.executeUpdate();
System.out.println("result: " + result);
if (result == 1) {
message = "修改书籍成功！";
request.setAttribute("message", message);
request.setAttribute("suggestURL", "/book?method=list");
request.setAttribute("declaration", "查看书籍列表");
request.getRequestDispatcher("/WEB-INF/page/message.jsp").forward(request, response);
} else {
message = "修改书籍失败！";
request.setAttribute("message", message);
request.setAttribute("suggestURL", "/book?method=list");
request.setAttribute("declaration", "回到书籍列表");
request.getRequestDispatcher("/WEB-INF/page/message.jsp").forward(request, response);
}
} catch (Exception e) {
e.printStackTrace();
message = "修改书籍失败！";
request.setAttribute("message", message);
request.setAttribute("suggestURL", "/book?method=list");
request.setAttribute("declaration", "回到书籍列表");
request.getRequestDispatcher("/WEB-INF/page/message.jsp").forward(request, response);
} finally {
returnResource(rs, ps, conn);
}
}
private void list(HttpServletRequest request, HttpServletResponse response, Connection conn) throws IOException, ServletException{
PreparedStatement ps = null;
ResultSet rs = null;
try {
List<Book> list = new ArrayList<>();
ps = conn.prepareStatement("select * from books");
rs = ps.executeQuery();
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
double price = rs.getDouble("price");
int pageCount = rs.getInt("page_count");
Book book = new Book(name, price, pageCount);
book.setId(id);
list.add(book);
}
request.setAttribute("bookList", list);
request.getRequestDispatcher("/WEB-INF/page/book_list.jsp").forward(request, response);
} catch (SQLException e) {
e.printStackTrace();
} finally {
returnResource(rs, ps, conn);
}
}
private void returnResource(ResultSet rs, PreparedStatement ps, Connection conn) {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
book_add.jsp
<%--
Created by IntelliJ IDEA.
User: yixin
Date: 2018/7/12
Time: 14:49
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html lang="zh-cn">
<head>
<meta charset="UTF-8">
<title>添加新书籍</title>
</head>
<body>
<form action="/book?method=create" method="post" >
书名：<input name="name" type="text"><br />
价格：<input name="price" type="text"><br />
页数：<input name="pageCount" type="number"> <br />
<input type="submit" value="提交"> <input type="reset" value="重置">
</form>
<a href="/index.html">回到首页</a>
</body>
</html>
book_list.jsp
<%@ page contentType="text/html;charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%@ page isELIgnored ="false" %>
<!DOCTYPE html>
<html lang="zh-cn">
<head>
<meta charset="UTF-8">
<title>书籍展示页</title>
</head>
<body>
<div>
<h2><span>购买过的书籍列表</span></h2> <c:choose>
<c:when test="${empty bookList}">
<tr>没有内容</tr>
</c:when>
<c:otherwise>
<table border="1">
<tr>
<th><input type="checkbox" id="chbAll"></th>
<th>编号</th>
<th>书名</th>
<th>价格</th>
<th>页数</th>
<th>操作</th>
</tr>
<tbody>
<c:forEach items="${bookList}" var="book">
<tr>
<th>ids</th>
<th><input type="checkbox" value="${book.id}" /></th>
<td>${book.name}</td>
<td><fmt:formatNumber value="${book.price}" pattern="0.00"/></td>
<td>${book.pageCount}</td>
<td><a href="/book?method=update&id=${book.id}">编辑</a> <a href="/book?method=delete&id=${book.id}">删除</a></td>
</tr>
</c:forEach>
</tbody>
</table>
</c:otherwise>
</c:choose>
<div>
<p>
<a href="/book?method=add">添加书籍</a> <a href="/index.html">返回首页</a>
</p>
</div>
</div>
</body>
</html>
book_update.jsp
<%--
Created by IntelliJ IDEA.
User: yinjd
Date: 2018/7/15
Time: 17:33
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page isELIgnored ="false" %>
<!DOCTYPE HTML>
<html lang="zh-cn">
<head>
<meta charset="UTF-8">
<title>修改书籍</title>
</head>
<body>
<form action="/book?method=updateOp" method="post" >
书名：<input name="name" type="text" value="${book.name}"><br />
价格：<input name="price" type="text" value="${book.price}"><br />
页数：<input name="pageCount" type="number" value="${book.pageCount}"> <br />
<input type="hidden" name="id" value="${book.id}"><br />
<input type="submit" value="提交">
</form>
</body>
</html>
message.jsp
<%@ page contentType="text/html;charset=UTF-8" pageEncoding="UTF-8" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn"%>
<%@ page isELIgnored ="false" %>
<!DOCTYPE html>
<html lang="zh-cn">
<head>
<meta charset="UTF-8">
<title>消息页</title>
</head>
<body> <p>${message}</p>
<p>点击这里><a href="${suggestURL}">${declaration}</a></p> </body>
</html>
index.html
<!DOCTYPE html>
<html lang="zh-cn">
<head>
<meta charset="UTF-8">
<title>欢迎页</title>
</head>
<body>
<a href="/file/list">上传文件列表</a><br />
<a href="/book?method=list">购买书籍列表</a>
</body>
</html>
5. 效果展示页
最新文章
- socket 网络编程
- [BZOJ1067][SCOI2007]降雨量
- sqlserver监控阻塞（死锁）具体情况
- 【Cocos2d入门教程五】Cocos2d-x动作篇
- c语言学习之基础知识点介绍（八）：函数的基本用法
- java实现单链表的增删功能
- Mysql参数汇总
- 关于HashSet集合add元素
- Linux库多重依赖
- Vivado Design Suite用户指南之约束的使用第一部分（介绍部分）
- the first blog in 2017——《论作为程序员的我考研》
- 《剑指offer》二叉树镜像
- Qt5.3.1 静态编译的configure
- 【MyBean调试笔记】接口的使用和清理
- 线程同步 –AutoResetEvent和ManualResetEvent
- Django 2.0 学习(20)：Django 中间件详解
- 关于用Spine制作骨骼动画的步骤
- git add用法
- 公司内网静态IP，外网无线动态IP 同时上网，不必再切换网卡啦 route 命令给你搞定。
- React中静态类型校验 - PropTypes