JDBC 技术应用实践：网上书城后台管理系统

电玩女神 2023-02-15 12:58 13阅读 0赞

### 实验二 JDBC 技术应用实践 ###

> 本项目使用 Servlet + JSP + JDBC 实现，没有使用任何框架，数据库为 MySQL 5.7

## 一、实验目的 ##

1、掌握数据库事务处理，掌握 `DAO` 设计模式;  
2、掌握使用传统的方法访问数据库；  
2、掌握使用数据源的方法访问数据库。  
4、掌握 `JSTL` 核心标签库。

## 二、实验内容 ##

*  采用 `MVC` 设计模式，设计并实现网上书城后台管理程序，访问数据库采用 `DAO` 设计模式。实现的功能包括：商品管理，公告管理，订单查询。
 *  商品管理模块包括添加商品，修改商品，删除商品，查询商品；
 *  公告管理模块包括添加公告，修改公告，删除公告；
 *  订单查询模块包括查看所有订单，查询订单详情，删除订单。

## 三、实验步骤 ##

### 1、公告管理模块 ###

#### 查询所有公告 ####

首先是实现查询所有的操作，写一个实现类去继承 `NoticeDAO` 接口，获取返回值直接返回。

/** * 查询所有公告 * * @return 公告集合 */
    @Override
    public List<Notice> getAllNotices() { 
        List<Notice> notices = new ArrayList<>();
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet resultSet = null;
        try { 
            connection = BaseDao.getConnection();
            pst = connection.prepareStatement("select * from notice;");
            resultSet = pst.executeQuery();
            notices = iteratorRes(resultSet, pst);
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            BaseDao.close(connection, pst, resultSet);
        }
        return notices;
    }

但是我这里并没有直接迭代结果集，因为后面有分页查询还需要迭代结果集，所以我这里直接抽取出来一个方法来迭代结果集。

/** * 抽取出来一个方法: 遍历查询出来的结果集封装到一个集合中并返回。 * * @param resultSet * @param pst * @return */
    private List<Notice> iteratorRes(ResultSet resultSet, PreparedStatement pst) { 
        List<Notice> notices = new ArrayList<>();
        try { 
            resultSet = pst.executeQuery();
            while (resultSet.next()) { 
                int n_id = resultSet.getInt("n_id");
                String title = resultSet.getString("title");
                String details = resultSet.getString("details");
                String n_time = resultSet.getString("n_time");
                Notice notice = new Notice(n_id, title, details, n_time);
                notices.add(notice);
            }
        } catch (SQLException e) { 
            e.printStackTrace();
        }
        return notices;
    }

另外使用了一个获取记录总条数的辅助方法：

/** * 得到记录总数 * * @return 返回值 */
    @Override
    public int getTotal() { 
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet res = null;
        int total = 0;
        try { 
            connection = BaseDao.getConnection();
            pst = connection.prepareStatement("select count(*) from notice;");
            res = pst.executeQuery();
            while (res.next()) { 
                total = res.getInt(1);
            }
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            BaseDao.close(connection, pst, res);
        }
        return total;
    }

对应的 `Servlet` 的方法是：

protected void doGet(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException { 
        String uri = request.getRequestURI();
        if (uri.endsWith("/ListNoticeServlet")) { 
            queryAll(request, response);
        }
    }
    
    private void queryAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 
        List<Notice> notices = dao.getAllNotices();
        request.setAttribute("notices", notices);
        request.getRequestDispatcher("admin/notices/list.jsp").forward(request, response);
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70][]

#### 分页查询 ####

/** * 分页查询 * * @param page 页码 * @param pageSize 每一页的大小 * @return 返回每一页的公告数 */
    @Override
    public List<Notice> getNoticesByPage(int page, int pageSize) { 
        List<Notice> notices = new ArrayList<>();
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet resultSet = null;
        try { 
            connection = BaseDao.getConnection();
            pst = connection.prepareStatement("select * from notice limit ?, ?;");
            pst.setInt(1, (page - 1) * pageSize);
            pst.setInt(2, pageSize);
            resultSet = pst.executeQuery();
            notices = iteratorRes(resultSet, pst);
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            BaseDao.close(connection, pst, resultSet);
        }
        return notices;
    }

分页查询使用到了 `limit` 关键字。

使用分页查询之后的 Servlet 应该是这样的：

private void queryAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 
        // 每页显示几条记录
        int pageSize = 10;
        // 总页数
        int pageNum = (dao.getTotal() - 1) / pageSize + 1;
        // 当前页码
        String currentPage = Optional.ofNullable(request.getParameter("page")).orElse("1");
        List<Notice> notices = dao.getNoticesByPage(Integer.parseInt(currentPage), pageSize);
        request.setAttribute("page", currentPage);
        request.setAttribute("pageNum", pageNum);
        request.setAttribute("notices", notices);
        request.getRequestDispatcher("admin/notices/list.jsp").forward(request, response);
    }

*  其中我们要将分页参数存到 `request` 域中，然后 `jsp` 页面来接收。
 *  `pageSize` 指定的每页的条数，这里写死了为 `10` ，所以每一页展示的条数就是 `10` 。

<span>总共有${pageNum}页，当前是第${page}页</span>  
    <a href="ListNoticeServlet?page=1">首页</a>
    <a href="ListNoticeServlet?page=${page-1}">上一页 </a>
    <a href="ListNoticeServlet?page=${page+1}">下一页</a>
    <a href="ListNoticeServlet?page=${pageNum}">尾页</a>

重新启动后就能看到分页效果。  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 1][]  
点击尾页试一下：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 2][]  
但是会有一个问题就是，就算后面没有数据了，点击下一页页数也会加 1，所以应该加一个判断条件类避免这种情况发生。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 3][]  
可以使用 `JavaScript` 实现，也可以在后端使用 `Java` 代码实现：

// 当前页码
    String page = request.getParameter("page");
    String currentPage = Optional.ofNullable(page).orElse("1");
    int p = Integer.parseInt(currentPage);
    if (p >= pageNum) { 
        p = pageNum;
    } else if (p <= 1) { 
        p = 1;
    }
    List<Notice> notices = dao.getNoticesByPage(p, pageSize);

修改之后就不会有问题了。

#### 增加公告 ####

在 `Servlet` 中书写以下代码：

/** * 添加公告 * * @param request 请求 * @param response 响应 * @throws ServletException 异常 * @throws IOException IO 异常 */
    private void addNotice(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 
        request.setCharacterEncoding("UTF-8");
        String title = request.getParameter("title");
        String details = request.getParameter("details");
        String date = MyTool.changeTime(new Date());
        Notice notice = new Notice(title, details, date);
        dao.addNotice(notice);
        request.getRequestDispatcher("admin/notices/add.jsp").forward(request, response);
    }

添加之后虽然成功了，但是出现了乱码：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 4][]  
出现这种问题要么是前端提交表单时出现了问题，要么是数据库编码的问题，但是这里数据库编码设置是正确的，那么就是前端表单的问题了。

request.setCharacterEncoding("UTF-8");

加上这一条信息就可以了。  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 5][]

#### 删除公告 ####

/** * 删除公告 * * @param request 请求对象 * @param response 响应对象 * @throws ServletException Servlet 异常 * @throws IOException IO异常 */
    private void deleteNotice(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 
        String id = request.getParameter("id");
        int i = dao.deleteNotice(id);
        String msg;
        if (i != 0) { 
            msg = "操作失败，请稍后再试！";
        } else { 
            msg = "删除成功！";
        }
        request.setAttribute("msg", msg);
        request.getRequestDispatcher("ListNoticeServlet").forward(request, response);
    }

Dao 的方法：

/** * 删除公告 * * @param n_id 公告的 ID 值 * @return */
    @Override
    public int deleteNotice(String n_id) { 
        Connection connection = null;
        PreparedStatement pst = null;
        int res = 0;
        try { 
            connection = BaseDao.getConnection();
            String sql = "delete from notice where n_id = ?";
            pst = connection.prepareStatement(sql);
            pst.setString(1, n_id);
            res = pst.executeUpdate();
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            BaseDao.close(connection, pst, null);
        }
        return res;
    }

删除成功：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 6][]

#### 修改公告 ####

首先要根据 ID 查询：

/** * 根据 ID 查询 * * @param n_id ID * @return 返回公告对象 */
    @Override
    public Notice findNoticeById(String n_id) { 
        Notice notice = null;
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet resultSet = null;
        try { 
            connection = BaseDao.getConnection();
            pst = connection.prepareStatement("select * from notice;");
            resultSet = pst.executeQuery();
            while (resultSet.next()) { 
                int id = resultSet.getInt("n_id");
                String title = resultSet.getString("title");
                String details = resultSet.getString("details");
                String n_time = resultSet.getString("n_time");
                notice = new Notice(id, title, details, n_time);
            }
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            BaseDao.close(connection, pst, resultSet);
        }
        return notice;
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 7][]  
然后传入要修改的公告 ID ，这里有一个隐藏域 ID，我们取出来就可以判断是哪一个，然后传入新的公告对象即可修改。

修改方法如下：

@Override
    public int updateNotice(Notice n) { 
        Connection connection = null;
        PreparedStatement pst = null;
        int i = 0;
        try { 
            connection = BaseDao.getConnection();
            pst = connection.prepareStatement("update notice set title = ?, details = ?, n_time = ?" +
                    "where n_id = ?");
            pst.setString(1, n.getTitle());
            pst.setString(2, n.getDetails());
            pst.setString(3, n.getN_time());
            pst.setInt(4, n.getN_id());
            i = pst.executeUpdate();
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            BaseDao.close(connection, pst, null);
        }
        return i;
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 8][]

### 2、商品管理模块 ###

#### 查询所有商品 ####

首先编写 `Servlet` 代码：

/** * 查询所有 * * @param request 请求对象 * @param response 响应对象 * @throws ServletException Servlet 异常 * @throws IOException IO 异常 */
    private void findAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 
        List<Products> products = dao.queryAll();
        request.setAttribute("products", products);
        request.getRequestDispatcher("admin/products/list.jsp").forward(request, response);
    }

然后是 `Dao` 层：

/** * 查询所有商品 * * @return 返回值 */
    @Override
    public List<Products> queryAll() { 
        List<Products> products = new ArrayList<>();
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet res = null;
        try { 
            connection = BaseDao.getConnection();
            pst = connection.prepareStatement("select * from products;");
            res = pst.executeQuery();
            while (res.next()) { 
                Products product = new Products(res.getString("id"),
                        res.getString("name"),
                        res.getDouble("price"),
                        res.getString("category"),
                        res.getInt("pnum"),
                        res.getString("imgurl"),
                        res.getString("description"));
                products.add(product);
            }
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            BaseDao.close(connection, pst, res);
        }
        return products;
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 9][]

#### 添加商品 ####

这里涉及到图片上传，为了能实现噶爱功能，有些部分必须设置为指定的属性：

*  Servlet 中必须加上 `@MultipartConfig` 注解；
 *  表单中的类型值必须设为 `Mulitipart` 类型；

/** * 添加商品 * * @param request 请求 * @param response 响应 * @throws IOException IO异常 */
    private void addProduct(HttpServletRequest request, HttpServletResponse response) throws IOException { 
        String category = request.getParameter("category");
        String description = request.getParameter("description");
        String id = UUID.randomUUID().toString();
        String imgUrl = "";
        String name = request.getParameter("name");
        int pNum = TypeChange.stringToInt(request.getParameter("pnum"));
        double price = Double.parseDouble(request.getParameter("price"));
        Products products = new Products(id, name, price, category, pNum, imgUrl, description);
        dao.addProducts(products);
        response.sendRedirect("showProducts");
    }

Dao 层：

/** * 添加商品 * * @param products 商品对象 * @return 返回值 */
    @Override
    public int addProducts(Products products) { 
        Connection connection = null;
        PreparedStatement pst = null;
        int res = 0;
        try { 
            connection = BaseDao.getConnection();
            pst = connection.prepareStatement("insert into products values (?, ?, ?, ?, ?, ?, ?)");
            pst.setString(1, products.getId());
            pst.setString(2, products.getName());
            pst.setDouble(3, products.getPrice());
            pst.setString(4, products.getCategory());
            pst.setInt(5, products.getPnum());
            pst.setString(6, products.getImgurl());
            pst.setString(7, products.getDescription());
            res = pst.executeUpdate();
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            BaseDao.close(connection, pst, null);
        }
        return res;
    }

但是目前还么有加上图片上传的功能，我们下面加一下：  
![20200606084819576.png][]  
表单要设置为 `multipart/form-data`；

// 获取上传的图片信息
    Part part = request.getPart("upload");
    String header = part.getHeader("content-disposition");
    String ext = header.substring(header.lastIndexOf("."), header.length() - 1);
    String imgName = System.currentTimeMillis() + ext;
    String path = request.getServletContext().getRealPath("/productImg");
    System.out.println(path);
    part.write(path + "\\" + imgName);
    String imgUrl = "productImg\\" + imgName;
    Products products = new Products(id, name, price, category, pNum, imgUrl, description);

添加如下代码即可长传成功。  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 10][]

#### 修改商品信息 ####

根据 ID 查询：

/** * 根据 ID 查询 * * @param id * @return */
    @Override
    public Products queryById(String id) { 
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet res = null;
        Products product = null;
        try { 
            connection = BaseDao.getConnection();
            pst = connection.prepareStatement("select * from products where id = ?;");
            pst.setString(1, id);
            res = pst.executeQuery();
            while (res.next()) { 
                product = new Products(res.getString("id"),
                        res.getString("name"),
                        res.getDouble("price"),
                        res.getString("category"),
                        res.getInt("pnum"),
                        res.getString("imgurl"),
                        res.getString("description"));
            }
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            BaseDao.close(connection, pst, res);
        }
        return product;
    }

然后是修改商品：

/** * 更新商品 * * @param request 请求对象 * @param response 响应对象 * @throws IOException IO异常 * @throws ServletException Servlet 异常 */
    private void editProduct(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { 
        String category = request.getParameter("category");
        String description = request.getParameter("description");
        String id = request.getParameter("id");
        String name = request.getParameter("name");
        int pNum = TypeChange.stringToInt(request.getParameter("pnum"));
        double price = Double.parseDouble(request.getParameter("price"));
        // 获取上传的图片信息
        Part part = request.getPart("upload");
        String header = part.getHeader("content-disposition");
        String ext = header.substring(header.lastIndexOf("."), header.length() - 1);
        String imgName = System.currentTimeMillis() + ext;
        String path = request.getServletContext().getRealPath("/productImg");
        System.out.println(path);
        part.write(path + "\\" + imgName);
        String imgUrl = "productImg\\" + imgName;
        Products products = new Products(id, name, price, category, pNum, imgUrl, description);
        dao.editProduct(products);
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 11][]

#### 根据条件查询 ####

首先 Servlet 应该这么写：

/** * 多条件查询 * * @param request 请求对象 * @param response 响应对象 */
    private void findProductByManyCondition(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 
        List<Products> products = dao.findProductByManyCondition(
                request.getParameter("id"),
                request.getParameter("name"),
                request.getParameter("category"),
                request.getParameter("minprice"),
                request.getParameter("maxprice")
        );
        request.setAttribute("products", products);
        request.getRequestDispatcher("showProducts").forward(request, response);
    }

然后是 Dao：

这里主要就是使用了动态的查询，类似于 `Mybatis` 的动态 `SQL` 语句：

/** * 根据条件查询 * * @param id 商品的 ID * @param category 商品的 category * @param name 商品的 name * @param minPrice 商品的 minPrice * @param maxPrice 商品的 maxPrice * @return 返回集合 */
    @Override
    public List<Products> findProductByManyCondition(String id, String category, String name, String minPrice, String maxPrice) { 
        List<Products> products = new ArrayList<>();
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet res = null;
        try { 
            connection = BaseDao.getConnection();
            String sql = "select * from products where 1=1 ";
            if (id != null && id.length() != 0) { 
                sql += "and id = '" + id + "'";
            } else if (category != null && category.length() != 0) { 
                sql += "and category = '" + category + "'";
            } else if (name != null) { 
                sql += "and name = '" + name + "'";
            } else if (minPrice != null && maxPrice != null) { 
                sql += "and price between " + minPrice + " and " + maxPrice + "";
            }
            pst = connection.prepareStatement(sql);
            res = pst.executeQuery();
            while (res.next()) { 
                Products product = new Products(res.getString("id"),
                        res.getString("name"),
                        res.getDouble("price"),
                        res.getString("category"),
                        res.getInt("pnum"),
                        res.getString("imgurl"),
                        res.getString("description"));
                products.add(product);
            }
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            BaseDao.close(connection, pst, res);
        }
        return products;
    }

测试多条件查询：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 12][]

### 3、订单管理 ###

#### 订单表和用户表的关联 ####

订单管理涉及到多表的查询，在设计实体类的时候已经考虑到了，在 Order 类中有 User 对象，这是一对一的关系。

/** * 查询所有的订单 * * @return 返回订单结果集 */
    @Override
    public List<Order> findAllOrder() { 
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet res = null;
        List<Order> orders = new ArrayList<>();
        try { 
            connection = BaseDao.getConnection();
            pst = connection.prepareStatement("select u.*, o.id as oid, money, receiverAddress, " +
                    "receiverName, receiverPhone, paystate, ordertime, user_id " +
                    "from orders o " +
                    "left join user u " +
                    "on o.user_id = u.id;");
            res = pst.executeQuery();
            while (res.next()) { 
                Order order = new Order();
                User user = new User();
                order.setId(res.getString("oid"));
                order.setMoney(Double.parseDouble(res.getString("money")));
                order.setReceiverAddress(res.getString("receiverAddress"));
                order.setReceiverName(res.getString("receiverName"));
                order.setReceiverPhone(res.getString("receiverPhone"));
                order.setPaystate(res.getInt("paystate"));
                order.setOrdertime(res.getDate("ordertime"));
                user.setId(res.getInt("id"));
                user.setUsername(res.getString("username"));
                user.setPassword(res.getString("password"));
                user.setGender(res.getString("gender"));
                user.setEmail(res.getString("email"));
                user.setTelephone(res.getString("telephone"));
                user.setIntroduce(res.getString("introduce"));
                user.setActiveCode(res.getString("activeCode"));
                user.setState(res.getInt("state"));
                user.setRole(res.getString("role"));
                user.setRegistTime(res.getDate("registerTime"));
                order.setUser(user);
                orders.add(order);
            }
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            BaseDao.close(connection, pst, res);
        }
        return orders;
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 13][]

#### 订单表和商品表的关联 ####

首先要在订单表中根据 ID 查询出订单信息。

/** * 订单表中根据 ID 查询订单 * * @param id 订单的ID * @return 返回的是订单对象 */
    @Override
    public Order findOrderById(String id) { 
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet res = null;
        Order order = new Order();
        try { 
            connection = BaseDao.getConnection();
            pst = connection.prepareStatement("select u.*, o.id as oid, money, receiverAddress, " +
                    "receiverName, receiverPhone, paystate, ordertime, user_id " +
                    "from orders o " +
                    "left join user u " +
                    "on o.user_id = u.id" +
                    " where o.id = ?;");
            pst.setString(1, id);
            res = pst.executeQuery();
            while (res.next()) { 
                User user = new User();
                order.setId(res.getString("oid"));
                order.setMoney(Double.parseDouble(res.getString("money")));
                order.setReceiverAddress(res.getString("receiverAddress"));
                order.setReceiverName(res.getString("receiverName"));
                order.setReceiverPhone(res.getString("receiverPhone"));
                order.setPaystate(res.getInt("paystate"));
                order.setOrdertime(res.getDate("ordertime"));
                user.setId(res.getInt("id"));
                user.setUsername(res.getString("username"));
                user.setPassword(res.getString("password"));
                user.setGender(res.getString("gender"));
                user.setEmail(res.getString("email"));
                user.setTelephone(res.getString("telephone"));
                user.setIntroduce(res.getString("introduce"));
                user.setActiveCode(res.getString("activeCode"));
                user.setState(res.getInt("state"));
                user.setRole(res.getString("role1"));
                user.setRegistTime(res.getDate("registTime"));
                order.setUser(user);
            }
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            BaseDao.close(connection, pst, res);
        }
        return order;
    }

然后用刚才查出来的订单信息再去 `OrdeItem` 表中查询对应的 `OrderItem` 表的信息，查出来订单对应的商品，然后再装到 `Order` 实体类中。

/** * 这里需要从 OrderItem 表中查询订单对应的商品信息 * * @param order 订单对象 * @return 返回的是订单商品的集合 */
    @Override
    public List<OrderItem> findOrderItemByOrder(Order order) { 
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet res = null;
        List<OrderItem> orderItems = new ArrayList<>();
        try { 
            connection = BaseDao.getConnection();
            pst = connection.prepareStatement("select * from orderitem oi left join products p on oi.product_id = p.id" +
                    " where order_id = ?;");
            pst.setString(1, order.getId());
            res = pst.executeQuery();
            while (res.next()) { 
                OrderItem orderItem = new OrderItem();
                Products product = new Products(res.getString("id"),
                        res.getString("name"),
                        res.getDouble("price"),
                        res.getString("category"),
                        res.getInt("pnum"),
                        res.getString("imgurl"),
                        res.getString("description"));
                orderItem.setP(product);
                int buynum = res.getInt("buynum");
                orderItem.setBuynum(buynum);
                orderItem.setOrder(order);
                orderItems.add(orderItem);
            }
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            BaseDao.close(connection, pst, res);
        }
        return orderItems;
    }

查询的结果如图：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 14][]

## 四、实验心得 ##

通过这次实验，理解了 MVC 的架构设计，以及一些经典的 JDBC 的 CRUD 操作，更加能理解为什么会有 ORM 等框架了，因为每当从数据库查出来数据之后都要自己进行封装，对于多数据的内容会非常麻烦，而且目前程序中类与类之间的耦合严重，造成牵一发而动全身的后果。可以使用反射和代理的设计模式进行优化，这就相当于 Spring 。还有一个问题就有大量的代码重复问题，可以抽取出来作为一个基类，但是还是有问题，所以 mybatis 横空出世，采用对象关系映射的思想，即 Object Relation Mapping，简化开发。但是框架基本采用配置的方式来简化开发，随着业务需求的增加，配置会显得很臃肿，于是 SpringBoot 来了，约定大于配置是其基本原则，再次简化了开发，回顾 JavaWeb 开发的发展历程，使自己的学习之路更加清晰。

综上，自己做了一遍 JDBC 实现 CRUD 之后再去学习框架就会更加理解为什么会有这些框架出现，他们解决了什么问题？怎么解决的？用的什么设计模式？如果换做自己能不能实现？JavaWeb 的路还有很远，Spring 家族的框架已经成为规范，但是无论如何也不离不开基础的 JavaEE 规范，希望以后自己学习的时候能更加注重 **深度** 而不是广度，就像一句话一样：“你知道的越多，不知道的就越多。”

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200605183615566.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2020060518591131.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200605190114402.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200605190227667.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200605210520571.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200605210913708.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200605212236535.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200605214106796.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200605221651201.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/2020060608130036.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA==,size_16,color_FFFFFF,t_70
[20200606084819576.png]: https://img-blog.csdnimg.cn/20200606084819576.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/2020060610245789.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/202006061025254.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 12]: https://img-blog.csdnimg.cn/20200606102747742.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 13]: https://img-blog.csdnimg.cn/20200606130136237.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA_size_16_color_FFFFFF_t_70 14]: https://img-blog.csdnimg.cn/20200606130157180.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk0MTM2NA==,size_16,color_FFFFFF,t_70