综合练习

项目简介

项目功能简介

1. 查询联系人
2. 添加联系人
3. 修改联系人
4. 删除联系人

三层架构

【三层架构】

【面向接口编程】

Web项目环境搭建

统一开发环境

统一开发环境任何项目开发前必须要做的事情。因为企业中的开发都是团队开发，如果一个团队的成员开发环境不一致，很容易产生一些不必要的问题。所以，开发项目之前必须要统一开发环境。本次综合练习需要统一的开发环境有以下几项：

1. 开发工具

   IntelliJ IDEA

2. JDK

   17

3. Tomcat

   10

4. 数据库

   MySQL 5.7

5. Servlet

   4.0

使用IDEA创建web项目

导入jar包

将以下 jar 包，导入到项目目录/web/WEB-INF/lib当中，Add As Library

• druid 连接池

  druid-1.1.5.jar

• JSTL

  jakarta.servlet.jsp.jstl-3.0.1.jar

  jakarta.servlet.jsp.jstl-api-3.0.0.jar

• MySQL Connector

  mysql-connector-j-8.2.0.jar

  注意:如果是 MySQL5.6 的话，使用另一个 jar 包

  mysql-connector-java-5.1.37-bin.jar

• JdbcTemplate

  spring-beans-5.0.0.RELEASE.jar

  spring-core-5.0.0.RELEASE.jar

  spring-jdbc-5.0.0.RELEASE.jar

  spring-tx-5.0.0.RELEASE.jar

• BeanUtils

  commons-beanutils-1.9.4.jar

  commons-collections-3.2.2.jar

  commons-logging-1.1.1.jar

• IOUtils

  commons-io-2.1.jar

导入Web资源文件

将以下文件，导入到项目目录/web当中

【注意】WEB-INF 为受保护目录，浏览器无法访问

• css
  • bootstrap.min.js
• fonts
  • glyphicons-halflings-regular.eot
  • glyphicons-halflings-regular.svg
  • glyphicons-halflings-regular.ttf
  • glyphicons-halflings-regular.woff
  • glyphicons-halflings-regular.woff2
• js
  • bootstrap.min.js
  • jquery-3.3.1.min.js
• add.jsp
• index.jsp
• list.jsp
• login.jsp
• update.jsp

数据库环境准备

创建数据库

使用数据库客户端创建数据库

• 数据库名

  contact

• 字符集

  utf8mb4

• 默认排序规则

  utf8mb4_bin

导入数据库脚本

DROP TABLE IF EXISTS `contact_info`;
CREATE TABLE `contact_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_bin NOT NULL,
  `gender` varchar(50) COLLATE utf8mb4_bin NOT NULL,
  `birthday` varchar(50) COLLATE utf8mb4_bin NOT NULL,
  `birthplace` varchar(50) COLLATE utf8mb4_bin NOT NULL,
  `mobile` varchar(50) COLLATE utf8mb4_bin NOT NULL,
  `email` varchar(50) COLLATE utf8mb4_bin NOT NULL,
  `del` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`)
);

INSERT INTO `contact_info` VALUES ('1', '张三', 'm', '633801600000', '广州', '134-0000-0000', 'zhangsan@future-weaver.com', '0');
INSERT INTO `contact_info` VALUES ('2', '李四', 'm', '667843200000', '上海', '134-0000-0001', 'lisi@future-weaver.com', '0');
INSERT INTO `contact_info` VALUES ('3', '王五', 'f', '702230400000', '广州', '134-0000-0002', 'wangwu@future-weaver.com', '0');
INSERT INTO `contact_info` VALUES ('4', '赵六', 'm', '736444800000', '北京', '134-0000-0003', 'zhaoliu@future-weaver.com', '0');
INSERT INTO `contact_info` VALUES ('5', '田七', 'f', '770745600000', '广州', '134-0000-0004', 'tianqi@future-weaver.com', '0');

SELECT * FROM contact_info;

配置C3P0配置文件

将c3p0-config.xml，导入到项目目录/src当中

• 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/contact?useUnicode=true&amp;characterEncoding=utf-8&amp;serverTimezone=GMT%2B8</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="acquireIncrement">2</property>
        <property name="initialPoolSize">5</property>
        <property name="minPoolSize">1</property>
        <property name="maxPoolSize">5</property>
    </default-config>
</c3p0-config>

导入工具类

• DBUtil.java

package com.futureweaver.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

public class DBUtil {
    private static DataSource ds = new ComboPooledDataSource();

    public static DataSource getDataSource() {
        return ds;
    }

    public static JdbcTemplate getJdbcTemplate() {
        return new JdbcTemplate(ds);
    }
}

• DateUtil.java

package com.futureweaver.utils;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class DateUtil {
    // 传入一个格式化的日期字符串(yyyy-MM-dd)，返回一个Date
    public static Date transStringToDate(String stringDate) {
        Date parse = null;
        try {
            parse = new SimpleDateFormat("yyyy-MM-dd").parse(stringDate);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return parse;
    }

    // 传入一个Date对象，返回一个格式化后的字符串
    public static String transDateToString(Date date) {
        return new SimpleDateFormat("yyyy-MM-dd").format(date);
    }

    // 传入一个1970-01-01毫秒数，返回一个格式化后的字符串
    public static String transDateToString(long date) {
        return new SimpleDateFormat("yyyy-MM-dd").format(date);
    }
}

创建域domain模型Model

• ContactInfo.java

package com.futureweaver.domain;

import com.futureweaver.utils.DateUtil;

import java.util.Date;

public class ContactInfo {
    // 编号
    private String id;

    // 姓名
    private String name;

    // 性别
    private String gender;

    // 生日
    private long birthday;

    // 籍贯
    private String birthplace;

    // 手机
    private String mobile;

    // 邮箱
    private String email;

    public ContactInfo() {
    }

    public ContactInfo(String id, String name, String gender, long birthday, String birthplace, String mobile, String email) {
        this.id = id;
        this.name = name;
        this.gender = gender;
        this.birthday = birthday;
        this.birthplace = birthplace;
        this.mobile = mobile;
        this.email = email;
    }

    public ContactInfo(String name, String gender, long birthday, String birthplace, String mobile, String email) {
        this.name = name;
        this.gender = gender;
        this.birthday = birthday;
        this.birthplace = birthplace;
        this.mobile = mobile;
        this.email = email;
    }

    @Override
    public String toString() {
        return "ContactInfo{" +
                "id='" + id + '\'' +
                ", name='" + name + '\'' +
                ", gender='" + gender + '\'' +
                ", birthday=" + birthday +
                ", birthplace='" + birthplace + '\'' +
                ", mobile='" + mobile + '\'' +
                ", email='" + email + '\'' +
                '}';
    }

    public long getAge() {
        long now = System.currentTimeMillis();
        return (now - birthday) / 1000 / 60 / 60 / 24 / 365;
    }

    public void setFormatBirthday(String formatBirthday) {
        birthday = DateUtil.transStringToDate(formatBirthday).getTime();
    }

    public String getFormatBirthday() {
        return DateUtil.transDateToString(birthday);
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public long getBirthday() {
        return birthday;
    }

    public void setBirthday(long birthday) {
        this.birthday = birthday;
    }

    public String getBirthplace() {
        return birthplace;
    }

    public void setBirthplace(String birthplace) {
        this.birthplace = birthplace;
    }

    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

启动Tomcat

用户列表查询

查询所有的用户信息，并展示在页面上。

修改index.jsp的查询链接

将查看所有用户信息的链接地址改成 Servlet 映射路径进行查询；

<a href="/query_contacts" style="text-decoration:none;font-size:33px">查询所有用户信息</a>

创建包，编写代码

创建包

• com.futureweaver.dao
• com.futureweaver.domain
  • ContactInfo.java
• com.futureweaver.service
• com.futureweaver.utils
  • DBUtil.java
• com.futureweaver.web

编写Java代码

• ContactInfoDAO.java

package com.futureweaver.dao;

import com.futureweaver.domain.ContactInfo;

import java.util.List;

public interface ContactInfoDAO {
    // 查询所有通讯信息
    public List<ContactInfo> queryAll();
}

• ContactInfoDAOImpl.java

package com.futureweaver.dao.impl;

import com.futureweaver.dao.ContactInfoDAO;
import com.futureweaver.domain.ContactInfo;
import com.futureweaver.utils.DBUtil;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class ContactInfoDAOImpl implements ContactInfoDAO {
    private JdbcTemplate jdbcTemplate = DBUtil.getJdbcTemplate();

    public List<ContactInfo> queryAll() {
        String sql = "SELECT * FROM contact_info WHERE del=0";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ContactInfo.class));
    }
}

• ContactInfoService.java

package com.futureweaver.service;

import com.futureweaver.domain.ContactInfo;

import java.util.List;

public interface ContactInfoService {
    // 查询所有通讯信息
    public List<ContactInfo> queryAll();
}

• ContactInfoServiceImpl.java

package com.futureweaver.service.impl;

import com.futureweaver.dao.ContactInfoDAO;
import com.futureweaver.dao.impl.ContactInfoDAOImpl;
import com.futureweaver.domain.ContactInfo;
import com.futureweaver.service.ContactInfoService;

import java.util.List;

public class ContactInfoServiceImpl implements ContactInfoService {
    ContactInfoDAO dao = new ContactInfoDAOImpl();

    @Override
    public List<ContactInfo> queryAll() {
        return dao.queryAll();
    }
}

• QueryContactsServlet.java

package com.futureweaver.web;

import com.futureweaver.domain.ContactInfo;
import com.futureweaver.service.ContactInfoService;
import com.futureweaver.service.impl.ContactInfoServiceImpl;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

@WebServlet("/query_contacts")
public class QueryContactsServlet extends HttpServlet {
    private ContactInfoService service = new ContactInfoServiceImpl();

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // 先获取到所有的通讯录信息
        List<ContactInfo> contactInfoList = service.queryAll();
      
        // 因为List在jsp当中会使用，所以要放到作用域当中
        request.setAttribute("contacts", contactInfoList);

        // 转发至jsp
        request.getRequestDispatcher("/list.jsp").forward(request, response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }
}

编写JSP代码

• list.jsp

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" %>
<% String path = request.getContextPath(); %>
<% String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/";%>

<!DOCTYPE html>
<html lang="en">
<head>
    <base href="<%=basePath%>"/>
    <meta charset="utf-8"/>
    <meta http-equiv="X-UA-Compatible" content="IE=edge"/>
    <meta name="viewport" content="width=device-width, initial-scale=1"/>
    <title>用户信息管理系统</title>

    <link rel='stylesheet' href='css/bootstrap.min.css'/>
    <script src='js/jquery-3.3.1.min.js'></script>
    <script src='js/bootstrap.min.js'></script>

    <script>
        function delContact(contactId) {
            var flag = window.confirm("确认删除吗?");
            if (flag) {
                window.location = "del_by_id?id=" + contactId;
            }
        }
    </script>

    <style type="text/css">
        td, th {
            text-align: center;
        }
    </style>
</head>

<body>
<div class="container">
    <h3 style="text-align: center">用户信息列表</h3>
    <table border="1" class="table table-bordered table-hover">
        <tr class="success">
            <th>编号</th>
            <th>姓名</th>
            <th>性别</th>
            <th>年龄</th>
            <th>籍贯</th>
            <th>手机</th>
            <th>邮箱</th>
            <th>操作</th>
        </tr>
<c:forEach items="${contacts}" var="contact" varStatus="status">
        <tr>
            <td>${status.count}</td>
            <td>${contact.name}</td>
            <td>${contact.gender == 'm' ? "男" : "女"}</td>
            <td>${contact.age}</td>
            <td>${contact.birthplace}</td>
            <td>${contact.mobile}</td>
            <td>${contact.email}</td>
            <td>
                <a class="btn btn-default btn-sm" href="update.jsp">修改</a>
                <a class="btn btn-default btn-sm" href="">删除</a>
            </td>
        </tr>
</c:forEach>
        <tr>
            <td colspan="8" align="center"><a class="btn btn-primary" href="add.jsp">添加联系人</a></td>
        </tr>
    </table>
</div>
</body>
</html>

删除指定用户

编写JSP代码

<a class="btn btn-default btn-sm" href="javascript:void(0);" onclick="delContact(${contact.id})">删除</a>

<script>
    function delContact(contactId) {
        var flag = window.confirm("确认删除吗?");
        if (flag) {
            window.location = "del_by_id?id=" + contactId;
        }
    }
</script>

编写Java代码

• ContactInfoDAO

package com.futureweaver.dao;

import com.futureweaver.domain.ContactInfo;

import java.util.List;

public interface ContactInfoDAO {
    // 查询所有通讯信息
    public List<ContactInfo> queryAll();

    // 删除通讯录信息
    public int delById(String id);
}

• ContactInfoDAOImpl

package com.futureweaver.dao.impl;

import com.futureweaver.dao.ContactInfoDAO;
import com.futureweaver.domain.ContactInfo;
import com.futureweaver.utils.DBUtil;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class ContactInfoDAOImpl implements ContactInfoDAO {
    private JdbcTemplate jdbcTemplate = DBUtil.getJdbcTemplate();

    @Override
    public List<ContactInfo> queryAll() {
        String sql = "SELECT * FROM contact_info WHERE del=0";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ContactInfo.class));
    }

    @Override
    public int delById(String id) {
        String sql = "UPDATE contact_info SET del=1 WHERE id=?";
        return jdbcTemplate.update(sql, id);
    }
}

• ContactInfoService

package com.futureweaver.service;

import com.futureweaver.domain.ContactInfo;

import java.util.List;

public interface ContactInfoService {
    // 查询所有通讯信息
    public List<ContactInfo> queryAll();

    // 删除指定用户
    public void delById(String id);
}

• ContactInfoServiceImpl

package com.futureweaver.service.impl;

import com.futureweaver.dao.ContactInfoDAO;
import com.futureweaver.dao.impl.ContactInfoDAOImpl;
import com.futureweaver.domain.ContactInfo;
import com.futureweaver.service.ContactInfoService;

import java.util.List;

public class ContactInfoServiceImpl implements ContactInfoService {
    ContactInfoDAO dao = new ContactInfoDAOImpl();

    @Override
    public List<ContactInfo> queryAll() {
        return dao.queryAll();
    }

    @Override
    public void delById(String id) {
        dao.delById(id);
    }
}

• DelByIdServlet.java

package com.futureweaver.web;

import com.futureweaver.service.ContactInfoService;
import com.futureweaver.service.impl.ContactInfoServiceImpl;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

@WebServlet("/del_by_id")
public class DelByIdServlet extends HttpServlet {
    private ContactInfoService service = new ContactInfoServiceImpl();

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // 获取删除的通讯录的id
        String contactId = request.getParameter("id");

        service.delById(contactId);

        // Servlet擅长的是Java代码、业务处理
        // 所以需要跳转到其他的资源
        // 删除完成之后，对于用户来说，要看到最新的、已经被删除掉之后的通讯录列表
        // 所以需要跳转到list.jsp
        // 转发
        // 重定向
        // 因为重定向之后，浏览器的url地址会变，所以使用重定向
        response.sendRedirect(request.getContextPath() + "/query_contacts");
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }
}

修改数据

回显数据

点击修改按钮后，将当前数据的信息展示在页面上

需求分析

1. JSP 向 Servlet 提交通讯录 id
2. Servlet 根据 id 查询通讯录信息，将查询结果存放至域对象，并转发到 update.jsp
3. update.jsp 从域对象中取出数据，展示到页面

代码实现

• ContactInfoDAO.java

package com.futureweaver.dao;

import com.futureweaver.domain.ContactInfo;

import java.util.List;

public interface ContactInfoDAO {
    // 查询所有通讯信息
    public List<ContactInfo> queryAll();

    // 删除通讯录信息
    public int delById(String id);

    // 查询通讯录信息
    public List<ContactInfo> queryById(String id);
}

• ContactDAOImpl.java

package com.futureweaver.dao.impl;

import com.futureweaver.dao.ContactInfoDAO;
import com.futureweaver.domain.ContactInfo;
import com.futureweaver.utils.DBUtil;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class ContactInfoDAOImpl implements ContactInfoDAO {
    private JdbcTemplate jdbcTemplate = DBUtil.getJdbcTemplate();

    @Override
    public List<ContactInfo> queryAll() {
        String sql = "SELECT * FROM contact_info WHERE del=0";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ContactInfo.class));
    }

    @Override
    public int delById(String id) {
        String sql = "UPDATE contact_info SET del=1 WHERE id=?";
        return jdbcTemplate.update(sql, id);
    }

    @Override
    public List<ContactInfo> queryById(String id) {
        String sql = "SELECT * FROM contact_info WHERE id=? AND del=0";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ContactInfo.class), id);
    }
}

• ContactInfoService.java

package com.futureweaver.service;

import com.futureweaver.domain.ContactInfo;

import java.util.List;

public interface ContactInfoService {
    // 查询所有通讯信息
    public List<ContactInfo> queryAll();

    // 删除指定用户
    public void delById(String id);

    // 查询指定用户
    public ContactInfo queryById(String id);
}

• ContactInfoServiceImpl.java

package com.futureweaver.service.impl;

import com.futureweaver.dao.ContactInfoDAO;
import com.futureweaver.dao.impl.ContactInfoDAOImpl;
import com.futureweaver.domain.ContactInfo;
import com.futureweaver.service.ContactInfoService;

import java.util.List;

public class ContactInfoServiceImpl implements ContactInfoService {
    ContactInfoDAO dao = new ContactInfoDAOImpl();

    @Override
    public List<ContactInfo> queryAll() {
        return dao.queryAll();
    }

    @Override
    public void delById(String id) {
        dao.delById(id);
    }

    @Override
    public ContactInfo queryById(String id) {
        List<ContactInfo> contactResults = dao.queryById(id);

        if (contactResults.isEmpty()) {
            return null;
        } else {
            return contactResults.get(0);
        }
    }
}

• UpdateByIdPreServlet.java

package com.futureweaver.web;

import com.futureweaver.domain.ContactInfo;
import com.futureweaver.service.ContactInfoService;
import com.futureweaver.service.impl.ContactInfoServiceImpl;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

@WebServlet("/update_by_id_pre")
public class UpdateByIdPreServlet extends HttpServlet {
    ContactInfoService service = new ContactInfoServiceImpl();

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String contactId = request.getParameter("id");

        ContactInfo contactInfo = service.queryById(contactId);

        request.setAttribute("contact", contactInfo);

        request.getRequestDispatcher("update.jsp").forward(request, response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }
}

• update.jsp

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" %>
<% String path = request.getContextPath(); %>
<% String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/";%>

<!DOCTYPE html>
<html lang="en">
<head>
    <base href="<%=basePath%>"/>
    <meta charset="utf-8"/>
    <meta http-equiv="X-UA-Compatible" content="IE=edge"/>
    <meta name="viewport" content="width=device-width, initial-scale=1"/>
    <title>修改用户</title>

    <link rel='stylesheet' href='css/bootstrap.min.css'/>
    <script src='js/jquery-3.3.1.min.js'></script>
    <script src='js/bootstrap.min.js'></script>
</head>

<body>
<div class="container" style="width: 400px;">
    <h3 style="text-align: center;">修改联系人</h3>
    <form action="" method="post">
        <div class="form-group">
            <label for="name">姓名：</label>
            <input type="text" class="form-control" id="name" name="name" readonly="readonly" placeholder="请输入姓名" value="${contact.name}"/>
        </div>

        <div class="form-group">
            <label>性别：</label>
            <input type="radio" name="gender" value="m" <c:if test="${contact.gender == 'm'}">checked</c:if>/>男
            <input type="radio" name="gender" value="f" <c:if test="${contact.gender == 'f'}">checked</c:if>/>女
        </div>

        <div class="form-group">
            <label for="age">生日：</label>
            <input type="text" class="form-control" id="formatBirthday" name="formatBirthday" placeholder="请输入生日(yyyy-MM-dd)" value="${contact.formatBirthday}"/>
        </div>

        <div class="form-group">
            <label for="birthplace">籍贯：</label>
            <select name="birthplace" class="form-control">
                <option value="广州" <c:if test="${contact.birthplace == '广州'}">selected</c:if>>广州</option>
                <option value="上海" <c:if test="${contact.birthplace == '上海'}">selected</c:if>>上海</option>
                <option value="北京" <c:if test="${contact.birthplace == '北京'}">selected</c:if>>北京</option>
            </select>
        </div>

        <div class="form-group">
            <label for="mobile">手机：</label>
            <input type="text" class="form-control" name="mobile" placeholder="请输入手机号码" value="${contact.mobile}"/>
        </div>

        <div class="form-group">
            <label for="email">Email：</label>
            <input type="text" class="form-control" name="email" placeholder="请输入邮箱地址" value="${contact.email}"/>
        </div>

        <div class="form-group" style="text-align: center">
            <input class="btn btn-primary" type="submit" value="提交"/>
            <input class="btn btn-default" type="reset" value="重置"/>
            <input class="btn btn-default" type="button" value="返回"/>
        </div>
    </form>
</div>
</body>
</html>

保存修改后的数据

将修改后的数据保存到数据库

需求分析

1. 使用隐藏域提交 Contact id

   <input type="hidden" name="id" value="${contact.id}">

2. SQL 语句应为更新操作

代码实现

• ContactInfoDAO.java

package com.futureweaver.dao;

import com.futureweaver.domain.ContactInfo;

import java.util.List;

public interface ContactInfoDAO {
    // 查询所有通讯信息
    public List<ContactInfo> queryAll();

    // 删除通讯录信息
    public int delById(String id);

    // 查询通讯录信息
    public List<ContactInfo> queryById(String id);

    // 更新通讯录信息
    public int updateContactInfo(ContactInfo contactInfo);
}

• ContactInfoDAOImpl.java

package com.futureweaver.dao.impl;

import com.futureweaver.dao.ContactInfoDAO;
import com.futureweaver.domain.ContactInfo;
import com.futureweaver.utils.DBUtil;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class ContactInfoDAOImpl implements ContactInfoDAO {
    private JdbcTemplate jdbcTemplate = DBUtil.getJdbcTemplate();

    @Override
    public List<ContactInfo> queryAll() {
        String sql = "SELECT * FROM contact_info WHERE del=0";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ContactInfo.class));
    }

    @Override
    public int delById(String id) {
        String sql = "UPDATE contact_info SET del=1 WHERE id=?";
        return jdbcTemplate.update(sql, id);
    }

    @Override
    public List<ContactInfo> queryById(String id) {
        String sql = "SELECT * FROM contact_info WHERE id=? AND del=0";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ContactInfo.class), id);
    }

    @Override
    public int updateContactInfo(ContactInfo contactInfo) {
        String sql = "UPDATE contact_info SET name=?, gender=?, birthday=?, birthplace=?, mobile=?, email=? WHERE id=? AND del=0";
        return jdbcTemplate.update(sql,
                contactInfo.getName(),
                contactInfo.getGender(),
                contactInfo.getBirthday(),
                contactInfo.getBirthplace(),
                contactInfo.getMobile(),
                contactInfo.getEmail(),
                contactInfo.getId());
    }
}

• ContactInfoService.java

package com.futureweaver.service;

import com.futureweaver.domain.ContactInfo;

import java.util.List;

public interface ContactInfoService {
    // 查询所有通讯信息
    public List<ContactInfo> queryAll();

    // 删除指定用户
    public void delById(String id);

    // 查询指定用户
    public ContactInfo queryById(String id);

    // 更新指定用户
    public boolean updateContact(ContactInfo contactInfo);
}

• ContactInfoServiceImpl.java

package com.futureweaver.service.impl;

import com.futureweaver.dao.ContactInfoDAO;
import com.futureweaver.dao.impl.ContactInfoDAOImpl;
import com.futureweaver.domain.ContactInfo;
import com.futureweaver.service.ContactInfoService;

import java.util.List;

public class ContactInfoServiceImpl implements ContactInfoService {
    ContactInfoDAO dao = new ContactInfoDAOImpl();

    @Override
    public List<ContactInfo> queryAll() {
        return dao.queryAll();
    }

    @Override
    public void delById(String id) {
        dao.delById(id);
    }

    @Override
    public ContactInfo queryById(String id) {
        List<ContactInfo> contactResults = dao.queryById(id);

        if (contactResults.isEmpty()) {
            return null;
        } else {
            return contactResults.get(0);
        }
    }

    @Override
    public boolean updateContact(ContactInfo contactInfo) {
        int daoResult = dao.updateContactInfo(contactInfo);
        return daoResult == 1;
    }
}

• UpdateByIdServlet.java

package com.futureweaver.web;

import com.futureweaver.domain.ContactInfo;
import com.futureweaver.service.ContactInfoService;
import com.futureweaver.service.impl.ContactInfoServiceImpl;
import org.apache.commons.beanutils.BeanUtils;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.Map;

@WebServlet("/update_by_id")
public class UpdateByIdServlet extends HttpServlet {
    private ContactInfoService service = new ContactInfoServiceImpl();

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html; charset=utf-8");

        Map<String, String[]> parameterMap = request.getParameterMap();
        ContactInfo contactInfo = new ContactInfo();

        try {
            BeanUtils.populate(contactInfo, parameterMap);
        } catch (Exception e) {
            e.printStackTrace();
        }

        service.updateContact(contactInfo);
        request.getRequestDispatcher("/query_contacts").forward(request, response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }
}

页面查看最新数据

重定向到/query_contacts

新增用户

需求分析

1. 点击添加联系人按钮后，跳转至add.jsp页面
2. 点击提交按钮后，发送请求至 AddContactServlet
3. AddContactServlet 处理结束之后，重定向至query_contacts

代码实现

• ContactInfoDAO.java

package com.futureweaver.dao;

import com.futureweaver.domain.ContactInfo;

import java.util.List;

public interface ContactInfoDAO {
    // 查询所有通讯信息
    public List<ContactInfo> queryAll();

    // 删除通讯录信息
    public int delById(String id);

    // 查询通讯录信息
    public List<ContactInfo> queryById(String id);

    // 更新通讯录信息
    public int updateContactInfo(ContactInfo contactInfo);

    // 添加通讯录信息
    public int insertContactInfo(ContactInfo contactInfo);
}

• ContactInfoDAOImpl.java

package com.futureweaver.dao.impl;

import com.futureweaver.dao.ContactInfoDAO;
import com.futureweaver.domain.ContactInfo;
import com.futureweaver.utils.DBUtil;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class ContactInfoDAOImpl implements ContactInfoDAO {
    private JdbcTemplate jdbcTemplate = DBUtil.getJdbcTemplate();

    @Override
    public List<ContactInfo> queryAll() {
        String sql = "SELECT * FROM contact_info WHERE del=0";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ContactInfo.class));
    }

    @Override
    public int delById(String id) {
        String sql = "UPDATE contact_info SET del=1 WHERE id=?";
        return jdbcTemplate.update(sql, id);
    }

    @Override
    public List<ContactInfo> queryById(String id) {
        String sql = "SELECT * FROM contact_info WHERE id=? AND del=0";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ContactInfo.class), id);
    }

    @Override
    public int updateContactInfo(ContactInfo contactInfo) {
        String sql = "UPDATE contact_info SET name=?, gender=?, birthday=?, birthplace=?, mobile=?, email=? WHERE id=? AND del=0";
        return jdbcTemplate.update(sql,
                contactInfo.getName(),
                contactInfo.getGender(),
                contactInfo.getBirthday(),
                contactInfo.getBirthplace(),
                contactInfo.getMobile(),
                contactInfo.getEmail(),
                contactInfo.getId());
    }

    @Override
    public int insertContactInfo(ContactInfo contactInfo) {
        String sql = "INSERT INTO contact_info(name, gender, birthday, birthplace, mobile, email) VALUES(?, ?, ?, ?, ?, ?)";
        return jdbcTemplate.update(sql,
                contactInfo.getName(),
                contactInfo.getGender(),
                contactInfo.getBirthday(),
                contactInfo.getBirthplace(),
                contactInfo.getMobile(),
                contactInfo.getEmail());
    }
}

• ContactInfoService.java

package com.futureweaver.service;

import com.futureweaver.domain.ContactInfo;

import java.util.List;

public interface ContactInfoService {
    // 查询所有通讯信息
    public List<ContactInfo> queryAll();

    // 删除指定用户
    public void delById(String id);

    // 查询指定用户
    public ContactInfo queryById(String id);

    // 更新指定用户
    public boolean updateContact(ContactInfo contactInfo);

    // 添加用户
    public boolean insertContact(ContactInfo contactInfo);
}

• ContactInfoServiceImpl.java

package com.futureweaver.service.impl;

import com.futureweaver.dao.ContactInfoDAO;
import com.futureweaver.dao.impl.ContactInfoDAOImpl;
import com.futureweaver.domain.ContactInfo;
import com.futureweaver.service.ContactInfoService;

import java.util.List;

public class ContactInfoServiceImpl implements ContactInfoService {
    ContactInfoDAO dao = new ContactInfoDAOImpl();

    @Override
    public List<ContactInfo> queryAll() {
        return dao.queryAll();
    }

    @Override
    public void delById(String id) {
        dao.delById(id);
    }

    @Override
    public ContactInfo queryById(String id) {
        List<ContactInfo> contactResults = dao.queryById(id);

        if (contactResults.isEmpty()) {
            return null;
        } else {
            return contactResults.get(0);
        }
    }

    @Override
    public boolean updateContact(ContactInfo contactInfo) {
        int daoResult = dao.updateContactInfo(contactInfo);
        return daoResult == 1;
    }

    @Override
    public boolean insertContact(ContactInfo contactInfo) {
        int daoResult = dao.insertContactInfo(contactInfo);
        return daoResult == 1;
    }
}

• AddContactServlet.java

package com.futureweaver.web;

import com.futureweaver.domain.ContactInfo;
import com.futureweaver.service.ContactInfoService;
import com.futureweaver.service.impl.ContactInfoServiceImpl;
import org.apache.commons.beanutils.BeanUtils;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.Map;

@WebServlet("/add_contact")
public class AddContactServlet extends HttpServlet {
    private ContactInfoService service = new ContactInfoServiceImpl();

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // 提交的参数里面有中文，所以需要解决请求的乱码问题
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html; charset=utf-8");

        Map<String, String[]> parameterMap = request.getParameterMap();
        ContactInfo contactInfo = new ContactInfo();

        // 借助BeanUtils，把参数当中的所有与用户相关的参数，设置到JavaBean当中
        try {
            BeanUtils.populate(contactInfo, parameterMap);
        } catch (Exception e) {
            e.printStackTrace();
        }

        // 参数需要的是一个ContactInfo JavaBean
        // 所以需要提前设置好这个JavaBean
        service.insertContact(contactInfo);

        // 当添加成功之后，需要回到query_contacts页面
        // 重定向
        response.sendRedirect(request.getContextPath() + "/query_contacts");
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }
}

分页查询

页面数据太多，一页展示不完。

带有分页的页面：

分页需求分析

页面上的分页数据展示可以分为两部分：

1. 分页数据：页面当中的通讯录记录，随页码的变化而变化
2. 分页条：控制分页的按钮

分页需求实现

MySQL LIMIT关键字

SELECT * FROM contact_info WHERE del=0 LIMIT n1, n2

练习

1. 每页查询 3 条数据；
2. 分别查询第 1 页，第 2 页，第 3 页的数据；
3. 总结出规律

结论

SELECT * FROM contact_info WHERE del=0 LIMIT n1, n2

n1 起始条数；n2 偏移量（从起始条数起，总共查询多少条数据）

技术细节

1. 需要用到 5 个变量
   1. [JSP 传输给 Servlet]每页显示的数据条数: pageSize(默认为 10)
   2. [JSP 传输给 Servlet]当前页码: currentPage(默认为 1)
   3. [Servlet 用于运算]查询时的起始条数: pageOffset
   4. [Servlet 用于运算]总记录条数: recordCount
   5. [Servlet 传输给 JSP]页数: pageCount
2. 页码数和每页显示多少数据都是页面上用户选择的。需要从前台传递过来
3. [计算规则]页面数：Math.ceil(recordCount / pageSize)
4. [计算规则]起始条数：pageOffset = (pageCount-1)*pageSize;

代码实现

• ContactInfoDAO.java

package com.futureweaver.dao;

import com.futureweaver.domain.ContactInfo;

import java.util.List;

public interface ContactInfoDAO {
    // 查询所有通讯信息
    public List<ContactInfo> queryAll();

    // 删除通讯录信息
    public int delById(String id);

    // 查询通讯录信息
    public List<ContactInfo> queryById(String id);

    // 更新通讯录信息
    public int updateContactInfo(ContactInfo contactInfo);

    // 添加通讯录信息
    public int insertContactInfo(ContactInfo contactInfo);

    // 查询通讯录总条数
    public int queryContactInfoSize();

    // 查询所有通讯信息(分页版本)
    public List<ContactInfo> queryAll(int offset, int size);
}

• ContactInfoDAOImpl.java

package com.futureweaver.dao.impl;

import com.futureweaver.dao.ContactInfoDAO;
import com.futureweaver.domain.ContactInfo;
import com.futureweaver.utils.DBUtil;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class ContactInfoDAOImpl implements ContactInfoDAO {
    private JdbcTemplate jdbcTemplate = DBUtil.getJdbcTemplate();

    @Override
    public List<ContactInfo> queryAll() {
        String sql = "SELECT * FROM contact_info WHERE del=0";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ContactInfo.class));
    }

    @Override
    public int delById(String id) {
        String sql = "UPDATE contact_info SET del=1 WHERE id=?";
        return jdbcTemplate.update(sql, id);
    }

    @Override
    public List<ContactInfo> queryById(String id) {
        String sql = "SELECT * FROM contact_info WHERE id=? AND del=0";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ContactInfo.class), id);
    }

    @Override
    public int updateContactInfo(ContactInfo contactInfo) {
        String sql = "UPDATE contact_info SET name=?, gender=?, birthday=?, birthplace=?, mobile=?, email=? WHERE id=? AND del=0";
        return jdbcTemplate.update(sql,
                contactInfo.getName(),
                contactInfo.getGender(),
                contactInfo.getBirthday(),
                contactInfo.getBirthplace(),
                contactInfo.getMobile(),
                contactInfo.getEmail(),
                contactInfo.getId());
    }

    @Override
    public int insertContactInfo(ContactInfo contactInfo) {
        String sql = "INSERT INTO contact_info(name, gender, birthday, birthplace, mobile, email) VALUES(?, ?, ?, ?, ?, ?)";
        return jdbcTemplate.update(sql,
                contactInfo.getName(),
                contactInfo.getGender(),
                contactInfo.getBirthday(),
                contactInfo.getBirthplace(),
                contactInfo.getMobile(),
                contactInfo.getEmail());
    }

    @Override
    public int queryContactInfoSize() {
        String sql = "SELECT COUNT(*) FROM contact_info WHERE del=0";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    @Override
    public List<ContactInfo> queryAll(int offset, int size) {
        String sql = "SELECT * FROM contact_info WHERE del=0 LIMIT ?, ?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ContactInfo.class), offset, size);
    }
}

• ContactInfoService.java

package com.futureweaver.service;

import com.futureweaver.domain.ContactInfo;

import java.util.List;

public interface ContactInfoService {
    // 查询所有通讯信息
    public List<ContactInfo> queryAll();

    // 删除指定用户
    public void delById(String id);

    // 查询指定用户
    public ContactInfo queryById(String id);

    // 更新指定用户
    public boolean updateContact(ContactInfo contactInfo);

    // 添加用户
    public boolean insertContact(ContactInfo contactInfo);

    // 查询通讯录总条数
    public int queryContactInfoSize();

    // 查询所有通讯信息(分页版本)
    public List<ContactInfo> queryAll(int offset, int size);
}

• ContactServiceImpl.java

package com.futureweaver.service.impl;

import com.futureweaver.dao.ContactInfoDAO;
import com.futureweaver.dao.impl.ContactInfoDAOImpl;
import com.futureweaver.domain.ContactInfo;
import com.futureweaver.service.ContactInfoService;

import java.util.List;

public class ContactInfoServiceImpl implements ContactInfoService {
    ContactInfoDAO dao = new ContactInfoDAOImpl();

    @Override
    public List<ContactInfo> queryAll() {
        return dao.queryAll();
    }

    @Override
    public void delById(String id) {
        dao.delById(id);
    }

    @Override
    public ContactInfo queryById(String id) {
        List<ContactInfo> contactResults = dao.queryById(id);

        if (contactResults.isEmpty()) {
            return null;
        } else {
            return contactResults.get(0);
        }
    }

    @Override
    public boolean updateContact(ContactInfo contactInfo) {
        int daoResult = dao.updateContactInfo(contactInfo);
        return daoResult == 1;
    }

    @Override
    public boolean insertContact(ContactInfo contactInfo) {
        int daoResult = dao.insertContactInfo(contactInfo);
        return daoResult == 1;
    }

    @Override
    public int queryContactInfoSize() {
        return dao.queryContactInfoSize();
    }

    @Override
    public List<ContactInfo> queryAll(int offset, int size) {
        return dao.queryAll(offset, size);
    }
}

• QueryContacts.java

package com.futureweaver.web;

import com.futureweaver.domain.ContactInfo;
import com.futureweaver.service.ContactInfoService;
import com.futureweaver.service.impl.ContactInfoServiceImpl;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

@WebServlet("/query_contacts")
public class QueryContactsServlet extends HttpServlet {
    private ContactInfoService service = new ContactInfoServiceImpl();

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        /**
         * 1. 需要用到5个变量
         *    1. [JSP传输给Servlet]每页显示的数据条数: pageSize(默认为10)
         *    2. [JSP传输给Servlet]当前页码: currentPage(默认为1)
         *    3. [Servlet用于运算]查询时的起始条数: pageOffset
         *    4. [Servlet用于运算]总记录条数: recordCount
         *    5. [Servlet传输给JSP]页数: pageCount
         * 2. 页码数和每页显示多少数据都是页面上用户选择的。需要从前台传递过来
         * 3. [计算规则]页面数：Math.ceil(recordCount / pageSize)
         * 4. [计算规则]起始条数：pageOffset = (pageCount-1)*pageSize;
         */

        // 定义当前页码
        // 先获取浏览器发送的页码的参数
        int currentPage = 1;
        String currentPageParam = request.getParameter("currentPage");
        if (currentPageParam != null) {
            currentPage = Integer.parseInt(currentPageParam);
        }

        // 需要先知道数据库当中，有多少条数据
        int recordCount = service.queryCount();

        // 每页显示的数据条数，先假定每页10条
        int pageSize = 10;

        // 这个是Limit第一关键字 = (当前页 - 1) 乘以 一页多少条，因为用户的数字是从1开始
        int pageOffset = (currentPage - 1) * pageSize;

        // 总记录条数
        int pageCount = (int)Math.ceil((double)recordCount / pageSize);

        // 将总记录条数发送给jsp
        request.setAttribute("pageCount", pageCount);

        // 将当前页面的页码数传给jsp
        request.setAttribute("currentPage", currentPage);

        // 先获取到所有的通讯录信息
        List<ContactInfo> contactInfoList = service.queryAll(pageOffset, pageSize);

        // 因为List在jsp当中会使用，所以要放到作用域当中
        request.setAttribute("contacts", contactInfoList);

        // 转发至jsp
        request.getRequestDispatcher("/list.jsp").forward(request, response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }
}

• list.jsp

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" %>
<% String path = request.getContextPath(); %>
<% String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/";%>

<!DOCTYPE html>
<html lang="en">
<head>
    <base href="<%=basePath%>"/>
    <meta charset="utf-8"/>
    <meta http-equiv="X-UA-Compatible" content="IE=edge"/>
    <meta name="viewport" content="width=device-width, initial-scale=1"/>
    <title>用户信息管理系统</title>

    <link rel='stylesheet' href='css/bootstrap.min.css'/>
    <script src='js/jquery-3.3.1.min.js'></script>
    <script src='js/bootstrap.min.js'></script>

    <script>
        function delContact(contactId) {
            var flag = window.confirm("确认删除吗?");
            if (flag) {
                window.location = "del_by_id?id=" + contactId;
            }
        }

        function changePageSize(pageSize) {
            window.location = "query_contacts?currentPage=1&pageSize="+pageSize;
        }
    </script>

    <style type="text/css">
        td, th {
            text-align: center;
        }
    </style>
</head>

<body>
<div class="container">
    <h3 style="text-align: center">用户信息列表</h3>
    <table border="1" class="table table-bordered table-hover">
        <tr>
            <td colspan="8" align="center"><a class="btn btn-primary" href="add.jsp">添加联系人</a></td>
        </tr>

        <tr class="success">
            <th>编号</th>
            <th>姓名</th>
            <th>性别</th>
            <th>年龄</th>
            <th>籍贯</th>
            <th>手机</th>
            <th>邮箱</th>
            <th>操作</th>
        </tr>
<c:forEach items="${contacts}" var="contact" varStatus="status">
        <tr>
            <td>${status.count}</td>
            <td>${contact.name}</td>
            <td>${contact.gender == 'm' ? "男" : "女"}</td>
            <td>${contact.age}</td>
            <td>${contact.birthplace}</td>
            <td>${contact.mobile}</td>
            <td>${contact.email}</td>
            <td>
                <a class="btn btn-default btn-sm" href="update_by_id_pre?id=${contact.id}">修改</a>
                <a class="btn btn-default btn-sm" href="javascript:void(0);" onclick="delContact(${contact.id})">删除</a>
            </td>
        </tr>
</c:forEach>

        <tr>
            <td colspan="8" align="center" class="form-inline">

                <%-- 因为需要在页面当中，构造几个页码按钮，所以需要遍历 --%>
                <c:forEach begin="1" end="${pageCount}" var="pageItem">
                <a class="btn btn-default <c:if test="${currentPage == pageItem}">btn-success</c:if>" href="query_contacts?currentPage=${pageItem}">${pageItem}</a>
                </c:forEach>

                <select class="form-control" onchange="changePageSize(this.value)">
<c:forEach begin="5" end="15" step="5" var="pageItem">
                    <option value="${pageItem}" <c:if test="${pageItem == pageSize}">selected</c:if>>${pageItem}条/页</option>
</c:forEach>
                </select>

            </td>
        </tr>
    </table>
</div>
</body>
</html>

学习目标总结

• 能够使用 MySQL 的 limit 关键字，查询表中数据
• 能够编写分页页面功能
• 能够完成对商品的查询
• 能够完成添加商品
• 能够完成修改商品
• 能够完成删除选定商品信息