【温故知新】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

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

加载 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> &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. 效果展示页