【温故知新】Java web 开发(四)JSTL 与 JDBC 的增删改查

  本篇开始使用 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} &nbsp;&nbsp;&nbsp; <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> &nbsp;<a href="/book?method=delete&id=${book.id}">删除</a></td>
</tr>
</c:forEach>

差点忘记说了,上边有取数的操作,比如最开始的${bookList}、${fileMap},它是怎样从 Servlet 传递到 jsp 中的呢?

答案是 servlet 中用 request.setAttribute("bookList", bookList);这种方式。

3. 数据库操作 jdbc

创建数据库连接的基本动作步骤

  1. 加载 jdbc 驱动类 DriverManager
  2. 创建连接(连接URL的格式)
  3. 创建 statement, 防止 Sql 注入的 PreparedStatement
  4. 执行语句 查询 executeQuery(); 更改、删除 executeUpdate()
  5. 处理返回结果 ResultSet
  6. 关闭连接
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> &nbsp;<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> &nbsp; <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. 效果展示页

【温故知新】Java web 开发(四)JSTL 与 JDBC 的增删改查

【温故知新】Java web 开发(四)JSTL 与 JDBC 的增删改查

【温故知新】Java web 开发(四)JSTL 与 JDBC 的增删改查

 
 
 
 
 
 
 
 
 
 

上一篇:dj cookie & session组件


下一篇:Node教程——Node+MongoDB案例实现用户信息的增删改查