案例：用户信息列表展示

需求

用户信息的增删改查操作

设计

技术选型：

Servlet+JSP+MySQL+JDBCTemplate+Duird+BeanUtils+tomcat

数据库设计

1
2
3
4
5
6
7
8
9
10
11
create database day17; -- 创建数据库
use day17; 			   -- 使用数据库
create table user(   -- 创建表
	id int primary key auto_increment,
	name varchar(20) not null,
	gender varchar(5),
	age int,
	address varchar(32),
	qq	varchar(20),
	email varchar(50)
);

开发

环境搭建

  • 创建数据库环境

  • 创建项目，导入需要的jar包

  • 将前端静态页面复制到web目录下

综合练习

简单功能

  1. 列表查询

  2. 登录

  3. 添加

  4. 删除

  5. 修改

复杂功能

  1. 删除选中

  2. 分页查询

    好处：

    • 减轻服务器内存的开销
    • 提升用户体验

  3. 复杂条件查询

列表查询功能

项目目录：

  1. 新建list.jsp查询页面

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
    <!DOCTYPE html>
    <!-- 网页使用的语言 -->
    <html lang="zh-CN">
    <head>
        <!-- 指定字符集 -->
        <meta charset="utf-8">
        <!-- 使用Edge最新的浏览器的渲染方式 -->
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <!-- viewport视口：网页可以根据设置的宽度自动进行适配，在浏览器的内部虚拟一个容器，容器的宽度与设备的宽度相同。
        width: 默认宽度与设备的宽度相同
        initial-scale: 初始的缩放比，为1:1 -->
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <!-- 上述3个meta标签*必须*放在最前面，任何其他内容都*必须*跟随其后！ -->
        <title>用户信息管理系统</title>
        <!-- 1. 导入CSS的全局样式 -->
        <link href="css/bootstrap.min.css" rel="stylesheet">
        <!-- 2. jQuery导入，建议使用1.9以上的版本 -->
        <script src="js/jquery-2.1.0.min.js"></script>
        <!-- 3. 导入bootstrap的js文件 -->
        <script src="js/bootstrap.min.js"></script>
        <style type="text/css">
            td, th {
                text-align: center;
            }
        </style>
    </head>
    <body>
    <div class="container">
        <h3 style="text-align: center">用户信息列表</h3>
        <div style="float: left;">

            <form class="form-inline">
                <div class="form-group">
                    <label for="exampleInputName2">姓名</label>
                    <input type="text" class="form-control" id="exampleInputName2" >
                </div>
                <div class="form-group">
                    <label for="exampleInputName3">籍贯</label>
                    <input type="text" class="form-control" id="exampleInputName3" >
                </div>

                <div class="form-group">
                    <label for="exampleInputEmail2">邮箱</label>
                    <input type="email" class="form-control" id="exampleInputEmail2"  >
                </div>
                <button type="submit" class="btn btn-default">查询</button>
            </form>

        </div>

        <div style="float: right;margin: 5px;">

            <a class="btn btn-primary" href="add.html">添加联系人</a>
            <a class="btn btn-primary" href="add.html">删除选中</a>

        </div>
        <table border="1" class="table table-bordered table-hover">
            <tr class="success">
                <th><input type="checkbox"></th>
                <th>编号</th>
                <th>姓名</th>
                <th>性别</th>
                <th>年龄</th>
                <th>籍贯</th>
                <th>QQ</th>
                <th>邮箱</th>
                <th>操作</th>
            </tr>

            <c:forEach items="${users}" var="user" varStatus="s">
                <tr>
                    <td><input type="checkbox"></td>
                    <td>${s.count}</td>
                    <td>${user.name}</td>
                    <td>${user.gender}</td>
                    <td>${user.age}</td>
                    <td>${user.address}</td>
                    <td>${user.qq}</td>
                    <td>${user.email}</td>
                    <td><a class="btn btn-default btn-sm" href="update.html">修改</a>&nbsp;<a class="btn btn-default btn-sm" href="">删除</a></td>
                </tr>
            </c:forEach>
        </table>
        <div>
            <nav aria-label="Page navigation">
                <ul class="pagination">
                    <li>
                        <a href="#" aria-label="Previous">
                            <span aria-hidden="true">&laquo;</span>
                        </a>
                    </li>
                    <li><a href="#">1</a></li>
                    <li><a href="#">2</a></li>
                    <li><a href="#">3</a></li>
                    <li><a href="#">4</a></li>
                    <li><a href="#">5</a></li>
                    <li>
                        <a href="#" aria-label="Next">
                            <span aria-hidden="true">&raquo;</span>
                        </a>
                    </li>
                    <span style="font-size: 25px;margin-left: 5px;">
                        共16条记录，共4页
                    </span>
                </ul>
            </nav>
        </div>
    </div>
    </body>
    </html>

  2. 新建User类

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    package com.uestc.dao;
    public class User {
        private int id;
        private String name;
        private String gender;
        private int age;
        private String address;
        private String qq;
        private String emil;
        public int getId() {
            return id;
        }
        public void setId(int 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 int getAge() {
            return age;
        }
        public void setAge(int age) {
            this.age = age;
        }
        public String getAddress() {
            return address;
        }
        public void setAddress(String address) {
            this.address = address;
        }
        public String getQq() {
            return qq;
        }
        public void setQq(String qq) {
            this.qq = qq;
        }
        public String getEmil() {
            return emil;
        }
        public void setEmil(String emil) {
            this.emil = emil;
        }
        @Override
        public String toString() {
            return "User{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", gender='" + gender + '\'' +
                    ", age=" + age +
                    ", address='" + address + '\'' +
                    ", qq='" + qq + '\'' +
                    ", emil='" + emil + '\'' +
                    '}';
        }
    }

    1. 新建JDBCUtils类

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      package com.uestc.utils;
       import com.alibaba.druid.pool.DruidDataSourceFactory;
       import javax.sql.DataSource;
       import javax.xml.crypto.Data;
       import java.io.IOException;
       import java.io.InputStream;
       import java.sql.Connection;
       import java.sql.SQLException;
       import java.util.Properties;
       /**
        * JDBC工具类 使用Durid连接池
        */
       public class JDBCUtils {
           private static DataSource ds ;
           static {
               try {
                   //1.加载配置文件
                   Properties pro = new Properties();
                   //使用ClassLoader加载配置文件，获取字节输入流
                   InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties");
                   pro.load(is);
                   //2.初始化连接池对象
                   ds = DruidDataSourceFactory.createDataSource(pro);
               } catch (IOException e) {
                   e.printStackTrace();
               } catch (Exception e) {
                   e.printStackTrace();
               }
           }
           /**
            * 获取连接池对象
            */
           public static DataSource getDataSource(){
               return ds;
           }
           /**
            * 获取连接Connection对象
            */
           public static Connection getConnection() throws SQLException {
               return  ds.getConnection();
           }
       }

  1. 新建UserDao接口以及对应的实现类UserDaoImpl

    1
    2
    3
    4
    5
    6
    7
    8
    9
    //UserDao
    package com.uestc.dao;
    import java.util.List;
    /**
     * 用户操作的DAO
     */
    public interface UserDao {
        public List<User> findAll();
    }
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    //UserDaoImpl
    package com.uestc.dao.impl;
    import com.uestc.dao.User;
    import com.uestc.dao.UserDao;
    import com.uestc.utils.JDBCUtils;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.JdbcTemplate;
    import java.util.List;
    public class UserDaoImpl implements UserDao {
        private JdbcTemplate template=new JdbcTemplate(JDBCUtils.getDataSource());

        @Override
        public List<User> findAll() {
            //使用JDBC完成查询
            //1.定义sql
            String sql="select * from user";
            List<User> list= template.query(sql,new BeanPropertyRowMapper<User>(User.class));
            return list;
        }
    }

  2. 新建UserService接口以及对应的实现类UserServiceImpl

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    //UserService
    package com.uestc.service;
    import com.uestc.dao.User;
    import java.util.List;

    /**
     * 用户管理的业务接口
     */
    public interface UserService {
        /**
         * 查询所有用户信息
         * @return
         */
        public List<User> findAll();
    }
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    //UserServiceImpl
    package com.uestc.service.impl;
    import com.uestc.dao.User;
    import com.uestc.dao.UserDao;
    import com.uestc.dao.impl.UserDaoImpl;
    import com.uestc.service.UserService;
    import java.util.List;
    public class UserServiceImpl implements UserService{
        private UserDao dao=new UserDaoImpl();
        @Override
        public List<User> findAll() {
            //调用dao完成查询
            return dao.findAll();
        }
    }

  3. 新建UserListServlet类

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    package com.uestc.web.servlet;

    import com.uestc.dao.User;
    import com.uestc.service.UserService;
    import com.uestc.service.impl.UserServiceImpl;

    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("/userListServlet")
    public class UserListServlet extends HttpServlet {
        @Override
        protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
            //调用UserService完成查询
            UserService service=new UserServiceImpl();
            List<User> users=service.findAll();

            //将list存入request域
            req.setAttribute("users",users);
            //转发到jsp页面
            req.getRequestDispatcher("/list.jsp").forward(req,resp);

        }

        @Override
        protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
            this.doPost(req,resp);
        }
    }

    效果展示：

​

登录功能

  1. 建立登录界面login.jsp 和 登录成功跳转界面index.jsp

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    //login.jsp
    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <!DOCTYPE html>
    <html lang="zh-CN">
      <head>
        <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>

        <!-- 1. 导入CSS的全局样式 -->
        <link href="css/bootstrap.min.css" rel="stylesheet">
        <!-- 2. jQuery导入，建议使用1.9以上的版本 -->
        <script src="js/jquery-2.1.0.min.js"></script>
        <!-- 3. 导入bootstrap的js文件 -->
        <script src="js/bootstrap.min.js"></script>
        <script type="text/javascript">
    		function refreshCode() {
    			//1.获取验证码的图片对象
    			var vcode=document.getElementById("vcode");
    			//2.设置src属性，加时间戳
    			vcode.src="${pageContext.request.contextPath}/checkCodeServlet?time="+new Date().getTime();
            }

        </script>
      </head>

      <body>
      	<div class="container" style="width: 400px;">
      		<h3 style="text-align: center;">管理员登录</h3>
            <form action="${pageContext.request.contextPath}/loginServlet" method="post">
    	      <div class="form-group">
    	        <label for="user">用户名：</label>
    	        <input type="text" name="username" class="form-control" id="user" placeholder="请输入用户名"/>
    	      </div>
    	      
    	      <div class="form-group">
    	        <label for="password">密码：</label>
    	        <input type="password" name="password" class="form-control" id="password" placeholder="请输入密码"/>
    	      </div>
    	      
    	      <div class="form-inline">
    	        <label for="vcode">验证码：</label>
    	        <input type="text" name="verifycode" class="form-control" id="verifycode" placeholder="请输入验证码" style="width: 120px;"/>
    	        <a href="javascript:refreshCode()">
    				<img src="${pageContext.request.contextPath}/checkCodeServlet" title="看不清点击刷新" id="vcode"/>
    			</a>
    	      </div>
    	      <hr/>
    	      <div class="form-group" style="text-align: center;">
    	        <input class="btn btn btn-primary" type="submit" value="登录">
    	       </div>
    	  	</form>
    		
    		<!-- 出错显示的信息框 -->
    	  	<div class="alert alert-warning alert-dismissible" role="alert">
    		  <button type="button" class="close" data-dismiss="alert" >
    		  	<span>&times;</span></button>
    		   <strong>${requestScope.login_msg}</strong>
    		</div>
      	</div>
      </body>
    </html>
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    //index.jsp
    <%--
      Created by IntelliJ IDEA.
      User: Liu Fei
      Date: 2020/11/5
      Time: 10:01
      To change this template use File | Settings | File Templates.
    --%>
    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
    <!DOCTYPE html>
    <html lang="zh-CN">
    <head>
      <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>

      <!-- 1. 导入CSS的全局样式 -->
      <link href="css/bootstrap.min.css" rel="stylesheet">
      <!-- 2. jQuery导入，建议使用1.9以上的版本 -->
      <script src="js/jquery-2.1.0.min.js"></script>
      <!-- 3. 导入bootstrap的js文件 -->
      <script src="js/bootstrap.min.js"></script>
      <script type="text/javascript">
      </script>
    </head>
    <body>

    <div>${login_user.name}，欢迎您 </div>
    <div align="center">
      <a>${requestScope.user}</a>
      <a href="${pageContext.request.contextPath}/userListServlet" style="text-decoration:none;font-size:33px">查询所有用户信息</a>
    </div>
    </body>
    </html>

  1. 新建LoginServlet和CheckCodeServlet

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    @WebServlet("/loginServlet")
    public class LoginServlet extends HttpServlet {
        @Override
        protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
            //1.设置编码
            req.setCharacterEncoding("utf-8");
            //2.获取数据
            //2.1 获取用户填写的验证码
            String verifycode=req.getParameter("verifycode");
            Map<String,String[]> map=req.getParameterMap();
            //3.验证码校验
            HttpSession session=req.getSession();
            String checkCode_session=(String)session.getAttribute("CHECKCODE_SERVER");
            session.removeAttribute("CHECKCODE_SERVER");//确保验证码的一次性
            if(verifycode.equalsIgnoreCase(checkCode_session)){
                //验证码正确
                //4.封装User对象
                User user=new User();
                try {
                    BeanUtils.populate(user,map);
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                }
                //5.调用service查询
                UserService service=new UserServiceImpl();
                User loginUser=service.login(user);
                if(loginUser==null){
                    //查找失败
                    //提示信息
                    req.setAttribute("login_msg","用户名或者密码错误");
                    req.getRequestDispatcher("/login.jsp").forward(req,resp);
                }else{
                    //登录成功
                    //将用户存入session
                    session.setAttribute("login_user",loginUser);
                    //跳转页面
                    resp.sendRedirect(req.getContextPath()+"/index.jsp");
                }
                //6.判断是否登录成功
            }else{
                //验证码不一致
                //提示信息
                req.setAttribute("login_msg","验证码错误");
                //跳转登录页面
                req.getRequestDispatcher("/login.jsp").forward(req,resp);
            }

        }

        @Override
        protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
            this.doPost(req,resp);
        }
    }
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    package com.uestc.web.servlet;

    import java.awt.Color;
    import java.awt.Font;
    import java.awt.Graphics;
    import java.awt.image.BufferedImage;
    import java.io.IOException;
    import java.util.Random;
    import javax.imageio.ImageIO;
    import javax.servlet.ServletException;
    import javax.servlet.annotation.WebServlet;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;

    /**
     * 验证码
     */
    @WebServlet("/checkCodeServlet")
    public class CheckCodeServlet extends HttpServlet {
    	public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
    		
    		//服务器通知浏览器不要缓存
    		response.setHeader("pragma","no-cache");
    		response.setHeader("cache-control","no-cache");
    		response.setHeader("expires","0");
    		
    		//在内存中创建一个长80，宽30的图片，默认黑色背景
    		//参数一：长
    		//参数二：宽
    		//参数三：颜色
    		int width = 80;
    		int height = 30;
    		BufferedImage image = new BufferedImage(width,height,BufferedImage.TYPE_INT_RGB);
    		
    		//获取画笔
    		Graphics g = image.getGraphics();
    		//设置画笔颜色为灰色
    		g.setColor(Color.GRAY);
    		//填充图片
    		g.fillRect(0,0, width,height);
    		
    		//产生4个随机验证码，12Ey
    		String checkCode = getCheckCode();
    		//将验证码放入HttpSession中
    		request.getSession().setAttribute("CHECKCODE_SERVER",checkCode);
    		
    		//设置画笔颜色为黄色
    		g.setColor(Color.YELLOW);
    		//设置字体的小大
    		g.setFont(new Font("黑体",Font.BOLD,24));
    		//向图片上写入验证码
    		g.drawString(checkCode,15,25);
    		
    		//将内存中的图片输出到浏览器
    		//参数一：图片对象
    		//参数二：图片的格式，如PNG,JPG,GIF
    		//参数三：图片输出到哪里去
    		ImageIO.write(image,"PNG",response.getOutputStream());
    	}
    	/**
    	 * 产生4位随机字符串 
    	 */
    	private String getCheckCode() {
    		String base = "0123456789ABCDEFGabcdefg";
    		int size = base.length();
    		Random r = new Random();
    		StringBuffer sb = new StringBuffer();
    		for(int i=1;i<=4;i++){
    			//产生0到size-1的随机值
    			int index = r.nextInt(size);
    			//在base字符串中获取下标为index的字符
    			char c = base.charAt(index);
    			//将c放入到StringBuffer中去
    			sb.append(c);
    		}
    		return sb.toString();
    	}
    	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    		this.doGet(request,response);
    	}
    }

    效果展示：

添加功能

  1. 创建add.jsp创建添加联系人页面

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <!-- HTML5文档-->
    <!DOCTYPE html>
    <!-- 网页使用的语言 -->
    <html lang="zh-CN">
    <head>
        <!-- 指定字符集 -->
        <meta charset="utf-8">
        <!-- 使用Edge最新的浏览器的渲染方式 -->
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <!-- viewport视口：网页可以根据设置的宽度自动进行适配，在浏览器的内部虚拟一个容器，容器的宽度与设备的宽度相同。
        width: 默认宽度与设备的宽度相同
        initial-scale: 初始的缩放比，为1:1 -->
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <!-- 上述3个meta标签*必须*放在最前面，任何其他内容都*必须*跟随其后！ -->
        <title>添加用户</title>

        <!-- 1. 导入CSS的全局样式 -->
        <link href="css/bootstrap.min.css" rel="stylesheet">
        <!-- 2. jQuery导入，建议使用1.9以上的版本 -->
        <script src="js/jquery-2.1.0.min.js"></script>
        <!-- 3. 导入bootstrap的js文件 -->
        <script src="js/bootstrap.min.js"></script>
    </head>
    <body>
    <div class="container">
        <center><h3>添加联系人页面</h3></center>
        <form action="${pageContext.request.contextPath}/addUserServlet" method="post">
            <div class="form-group">
                <label for="name">姓名：</label>
                <input type="text" class="form-control" id="name" name="name" placeholder="请输入姓名">
            </div>

            <div class="form-group">
                <label>性别：</label>
                <input type="radio" name="gender" value="男" checked="checked"/>男
                <input type="radio" name="gender" value="女"/>女
            </div>

            <div class="form-group">
                <label for="age">年龄：</label>
                <input type="text" class="form-control" id="age" name="age" placeholder="请输入年龄">
            </div>

            <div class="form-group">
                <label for="address">籍贯：</label>
                <select name="address" class="form-control" id="jiguan">
                    <option value="广东">广东</option>
                    <option value="广西">广西</option>
                    <option value="湖南">湖南</option>
                </select>
            </div>

            <div class="form-group">
                <label for="qq">QQ：</label>
                <input type="text" class="form-control" name="qq" placeholder="请输入QQ号码"/>
            </div>

            <div class="form-group">
                <label for="email">Email：</label>
                <input type="text" class="form-control" name="email" placeholder="请输入邮箱地址"/>
            </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>

    并修改list.jsp中添加联系人按钮的超链接使其指向add.jsp页面

    ![image-20201109172004690](C:\Users\Liu Fei\AppData\Roaming\Typora\typora-user-images\image-20201109172004690.png)

  2. 新建AddUserServlet

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    @WebServlet("/addUserServlet")
    public class AddUserServlet extends HttpServlet {

        @Override
        protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
            //1.设置编码
            req.setCharacterEncoding("utf-8");

            //2.获取参数
            Map<String,String[]> map=req.getParameterMap();
            //3.封装对象

            User user=new User();
            try {
                BeanUtils.populate(user, map);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }

            //4.调用service保存
            UserService service=new UserServiceImpl();
            service.addUser(user);

            //5.跳转到UserListServlet
            resp.sendRedirect(req.getContextPath()+"/userListServlet");
        }

        @Override
        protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
            this.doPost(req,resp);
        }
    }

  3. 在Service和UserDao中添加add()方法

    Service:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    //UserService
    /**
     * 用户管理的业务接口
     */

    public interface UserService {

        /**
         * 查询所有用户信息
         * @return
         */
        public List<User> findAll();

        /**
         * 登录方法
         * @param loginUser
         * @return
         */
        public User login(User loginUser);

        /**
         * 保存User
         * @param user
         */
        public void addUser(User user);
    }
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    //UserServiceImpl
    public class UserServiceImpl implements UserService{
        private UserDao dao=new UserDaoImpl();
        @Override
        public List<User> findAll() {
            //调用dao完成查询
            return dao.findAll();
        }

        @Override
        public void addUser(User user) {
            dao.add(user);
        }

        @Override
        public User login(User loginUser) {
            return dao.findUserByUsernameAndPassWord(loginUser.getUsername(),loginUser.getPassword());
        }
    }

    Dao:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    package com.uestc.dao;
    import java.util.List;

    /**
     * 用户操作的DAO
     */
    public interface UserDao {

        public List<User> findAll();

        public User findUserByUsernameAndPassWord(String userName, String password);

        public void add(User user);
    }
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    package com.uestc.dao.impl;
    public class UserDaoImpl implements UserDao {
        private JdbcTemplate template=new JdbcTemplate(JDBCUtils.getDataSource());
        @Override
        public List<User> findAll() {
            //使用JDBC完成查询
            //1.定义sql
            String sql="select * from user";
            List<User> list= template.query(sql,new BeanPropertyRowMapper<User>(User.class));
            return list;
        }
        @Override
        public User findUserByUsernameAndPassWord(String username, String password) {
            try{
                String sql="select * from user where username=? and password = ?";
                User user=template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class),username,password);
                return user;
            }catch (Exception e){
                e.printStackTrace();
                return null;
            }
        }
        @Override
        public void add(User user) {
            //定义sql
            String sql="insert into user value(null,?,?,?,?,?,?,null,null)";
            //执行sql
            template.update(sql,user.getName(),user.getGender(),user.getAge(),user.getAddress(),user.getQq(),user.getEmail());
        }
    }

    效果展示

删除功能

  1. 修改list.jsp中删除选项使其指向deleteUserServlet，并将需要删除的id传入参数中

  2. 创建DeleteUserServlet

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    @WebServlet("/deleteUserServlet")
    public class DeleteUserServlet extends HttpServlet {
        @Override
        protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
            //1.设置编码
            req.setCharacterEncoding("utf-8");

            //2.获取参数id
            String id=req.getParameter("id");

            //3.调用service删除
            UserService serive=new UserServiceImpl();
            serive.deleteUser(id);

            //4.跳转查询所有的servlet
            resp.sendRedirect(req.getContextPath()+"/userListServlet");

        }
        @Override
        protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
            this.doPost(req,resp);
        }
    }

  3. 在Service和UserDao中添加delete()方法

    Service:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    package com.uestc.service;
    /**
     * 用户管理的业务接口
     */
    public interface UserService {
        /**
         * 查询所有用户信息
         * @return
         */
        public List<User> findAll();

        /**
         * 登录方法
         * @param loginUser
         * @return
         */
        public User login(User loginUser);
        /**
         * 保存User
         * @param user
         */
        public void addUser(User user);

        /**
         * 删除user
         * @param id
         */
        public void deleteUser(String id);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
package com.uestc.service.impl;
public class UserServiceImpl implements UserService{
    private UserDao dao=new UserDaoImpl();
    @Override
    public List<User> findAll() {
        //调用dao完成查询
        return dao.findAll();
    }
    @Override
    public void addUser(User user) {
        dao.add(user);
    }
    @Override
    public void deleteUser(String id) {
        dao.delete(Integer.valueOf(id));
    }
    @Override
    public User login(User loginUser) {
        return dao.findUserByUsernameAndPassWord(loginUser.getUsername(),loginUser.getPassword());
    }
}

DAO:

1
2
3
4
5
6
7
8
9
10
package com.uestc.dao;
/**
 * 用户操作的DAO
 */
public interface UserDao {
    public List<User> findAll();
    public User findUserByUsernameAndPassWord(String userName, String password);
    public void add(User user);
    public void delete(int id);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
package com.uestc.dao.impl;
public class UserDaoImpl implements UserDao {
    private JdbcTemplate template=new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public List<User> findAll() {
        //使用JDBC完成查询
        //1.定义sql
        String sql="select * from user";
        List<User> list= template.query(sql,new BeanPropertyRowMapper<User>(User.class));
        return list;
    }
    @Override
    public User findUserByUsernameAndPassWord(String username, String password) {
        try{
            String sql="select * from user where username=? and password = ?";
            User user=template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class),username,password);
            return user;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
    @Override
    public void add(User user) {
        //定义sql
        String sql="insert into user value(null,?,?,?,?,?,?,null,null)";
        //执行sql
        template.update(sql,user.getName(),user.getGender(),user.getAge(),user.getAddress(),user.getQq(),user.getEmail());
    }
    @Override
    public void delete(int id) {
        //定义sql
        String sql="delete from user where id=?";
        //执行sql
        template.update(sql,id);
    }
}

效果展示：

问题：为了防止误删，需要添加提醒

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
<!--list.jsp-->
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<!-- 网页使用的语言 -->
<html lang="zh-CN">
<head>
    <!-- 指定字符集 -->
    <meta charset="utf-8">
    <!-- 使用Edge最新的浏览器的渲染方式 -->
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <!-- viewport视口：网页可以根据设置的宽度自动进行适配，在浏览器的内部虚拟一个容器，容器的宽度与设备的宽度相同。
    width: 默认宽度与设备的宽度相同
    initial-scale: 初始的缩放比，为1:1 -->
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <!-- 上述3个meta标签*必须*放在最前面，任何其他内容都*必须*跟随其后！ -->
    <title>用户信息管理系统</title>
    <!-- 1. 导入CSS的全局样式 -->
    <link href="css/bootstrap.min.css" rel="stylesheet">
    <!-- 2. jQuery导入，建议使用1.9以上的版本 -->
    <script src="js/jquery-2.1.0.min.js"></script>
    <!-- 3. 导入bootstrap的js文件 -->
    <script src="js/bootstrap.min.js"></script>
    <style type="text/css">
        td, th {
            text-align: center;
        }
    </style>
    <script>
       function deleteUser(id) {
           //用户安全提示
           if(confirm("您确定删除么？")){
               //访问路径
               location.href="${pageContext.request.contextPath}/deleteUserServlet?id="+id;
           }
       }
    </script>
</head>
<body>
<div class="container">
    <h3 style="text-align: center">用户信息列表</h3>

    <div style="float: left;">

        <form class="form-inline">
            <div class="form-group">
                <label for="exampleInputName2">姓名</label>
                <input type="text" class="form-control" id="exampleInputName2" >
            </div>
            <div class="form-group">
                <label for="exampleInputName3">籍贯</label>
                <input type="text" class="form-control" id="exampleInputName3" >
            </div>
            <div class="form-group">
                <label for="exampleInputEmail2">邮箱</label>
                <input type="email" class="form-control" id="exampleInputEmail2"  >
            </div>
            <button type="submit" class="btn btn-default">查询</button>
        </form>
    </div>
    <div style="float: right;margin: 5px;">
        <a class="btn btn-primary" href="${pageContext.request.contextPath}/add.jsp">添加联系人</a>
        <a class="btn btn-primary" href="add.html">删除选中</a>
    </div>
    <table border="1" class="table table-bordered table-hover">
        <tr class="success">
            <th><input type="checkbox"></th>
            <th>编号</th>
            <th>姓名</th>
            <th>性别</th>
            <th>年龄</th>
            <th>籍贯</th>
            <th>QQ</th>
            <th>邮箱</th>
            <th>操作</th>
        </tr>
        <c:forEach items="${users}" var="user" varStatus="s">
            <tr>
                <td><input type="checkbox"></td>
                <td>${s.count}</td>
                <td>${user.name}</td>
                <td>${user.gender}</td>
                <td>${user.age}</td>
                <td>${user.address}</td>
                <td>${user.qq}</td>
                <td>${user.email}</td>
                <td><a class="btn btn-default btn-sm" href="update.html">修改</a>&nbsp;
                    <a class="btn btn-default btn-sm" href="javascript:deleteUser(${user.id})">删除</a></td>
            </tr>
        </c:forEach>
    </table>
    <div>
        <nav aria-label="Page navigation">
            <ul class="pagination">
                <li>
                    <a href="#" aria-label="Previous">
                        <span aria-hidden="true">&laquo;</span>
                    </a>
                </li>
                <li><a href="#">1</a></li>
                <li><a href="#">2</a></li>
                <li><a href="#">3</a></li>
                <li><a href="#">4</a></li>
                <li><a href="#">5</a></li>
                <li>
                    <a href="#" aria-label="Next">
                        <span aria-hidden="true">&raquo;</span>
                    </a>
                </li>
                <span style="font-size: 25px;margin-left: 5px;">
                    共16条记录，共4页
                </span>
            </ul>
        </nav>
    </div>
</div>
</body>
</html>

修改功能

  1. 修改list.jsp中修改使其指向findUserServlet

  1. 新建FindUserServlet

    目的：是先根据id获取user,然后再update.jsp中回显原有的user信息

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    package com.uestc.web.servlet;
    @WebServlet("/findUserServlet")
    public class FindUserServlet extends HttpServlet {
        @Override
        protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
           UserService service=new UserServiceImpl();
            //1.获取id
            String id=req.getParameter("id");
           //2.根据id查找user
            User user=service.findUserById(id);
           //3.将user信息存入request域中
            req.setAttribute("user",user);
            //3.跳转update.jsp
            req.getRequestDispatcher("/update.jsp").forward(req,resp);
        }
        @Override
        protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
            this.doPost(req,resp);
        }
    }

  2. 新建update.jsp

    注意：

    1. 在FindUserServlet中已经将查到的User中的信息保存在request域中, 我们只需要通过el和jstl表达式获取

    2. 将action指向UpdateServlet

    3. 创建隐藏域，传递id

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
    <!DOCTYPE html>
    <!-- 网页使用的语言 -->
    <html lang="zh-CN">
        <head>
            <!-- 指定字符集 -->
            <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 href="css/bootstrap.min.css" rel="stylesheet">
            <script src="js/jquery-2.1.0.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="${pageContext.request.contextPath}/updateServlet" method="post">
              <!--隐藏域提交id-->
              <input type="hidden" name="id" value="${user.id}">
              <div class="form-group">
                <label for="name">姓名：</label>
                <input type="text" class="form-control" id="name" name="name" value="${user.name}" readonly="readonly"  />
              </div>          
              <div class="form-group">
                <label>性别：</label>
                  <c:if test="${user.gender=='男'}">
                      <input type="radio" name="gender" value="男" checked />男
                      <input type="radio" name="gender" value="女"  />女
                  </c:if>
                  <c:if test="${user.gender=='女'}">
                      <input type="radio" name="gender" value="男"  />男
                      <input type="radio" name="gender" value="女" checked />女
                  </c:if>
              </div>
              <div class="form-group">
                <label for="age">年龄：</label>
                <input type="text" class="form-control" id="age"  name="age"  value="${user.age}"  />
              </div>

              <div class="form-group">
                <label for="address">籍贯：</label>
                 <select name="address" class="form-control" >
                     <c:if test="${user.address=='安徽'}">
                         <option value="安徽" selected>安徽</option>
                         <option value="广西">广西</option>
                         <option value="湖南">湖南</option>

                     </c:if>
                     <c:if test="${user.address=='广东'}">
                         <option value="安徽" >安徽</option>
                         <option value="广东" selected>广东</option>
                         <option value="湖南">湖南</option>

                     </c:if>
                     <c:if test="${user.address=='湖南'}">
                         <option value="安徽" >安徽</option>
                         <option value="广东">广东</option>
                         <option value="湖南" selected>湖南</option>
                     </c:if>
                </select>
              </div>
              <div class="form-group">
                <label for="qq">QQ：</label>
                <input type="text" class="form-control" name="qq"  value="${user.qq}"/>
              </div>

              <div class="form-group">
                <label for="email">Email：</label>
                <input type="text" class="form-control" name="email"  value="${user.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>

  3. 新建UpdateServlet

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    package com.uestc.web.servlet;
    import com.uestc.dao.User;
    import com.uestc.service.UserService;
    import com.uestc.service.impl.UserServiceImpl;
    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("/updateServlet")
    public class UpdateServlet extends HttpServlet{
        @Override
        protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
            //1.设置编码
            req.setCharacterEncoding("utf-8");
            //2.获取表单参数
            Map<String,String[]> map=req.getParameterMap();
            System.out.println(map.get("id"));
            //3.封装user对象
            User user=new User();
            try {
                BeanUtils.populate(user,map);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
            //4.调用service的update方法;
            UserService service=new UserServiceImpl();
            service.updateUser(user);
            //5.跳转到查询所有
            resp.sendRedirect(req.getContextPath()+"/userListServlet");
        }
        @Override
        protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
            this.doPost(req,resp);
        }
    }

  4. 在Userservice及其实现类 UserserviceImpl中添加update方法

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    package com.uestc.service;
    import com.uestc.dao.User;
    import java.util.List;
    /**
     * 用户管理的业务接口
     */

    public interface UserService {

        /**
         * 查询所有用户信息
         * @return
         */
        public List<User> findAll();

        /**
         * 登录方法
         * @param loginUser
         * @return
         */
        public User login(User loginUser);

        /**
         * 保存User
         * @param user
         */
        public void addUser(User user);
        /**
         * 删除User
         * @param id
         */
        public void deleteUser(String id);

        /**
         * 根据id查找用户
         * @param id
         * @return
         */
        public User findUserById(String id);
        /**
         * 更新User
         * @param user
         */
        public void updateUser(User user);
    }
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    package com.uestc.service.impl;
    import com.uestc.dao.User;
    import com.uestc.dao.UserDao;
    import com.uestc.dao.impl.UserDaoImpl;
    import com.uestc.service.UserService;
    import java.util.List;
    public class UserServiceImpl implements UserService{
        private UserDao dao=new UserDaoImpl();
        @Override
        public List<User> findAll() {
            //调用dao完成查询
            return dao.findAll();
        }

        @Override
        public void addUser(User user) {
            dao.add(user);
        }

        @Override
        public void deleteUser(String id) {
            dao.delete(Integer.valueOf(id));
        }

        @Override
        public User findUserById(String id) {
            return dao.findUserById(Integer.valueOf(id));
        }

        @Override
        public void updateUser(User user) {
            dao.update(user);
        }

        @Override
        public User login(User loginUser) {
            return dao.findUserByUsernameAndPassWord(loginUser.getUsername(),loginUser.getPassword());
        }
    }

  1. 在UserDao及其实现类 UserDaoImpl中添加update方法

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    package com.uestc.dao;
    import java.util.List;
    /**
     * 用户操作的DAO
     */
    public interface UserDao {
        public List<User> findAll();
        public User findUserByUsernameAndPassWord(String userName, String password);
        public void add(User user);
        public void delete(int id);
        public User findUserById(int id);
        public void update(User user);
    }
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    package com.uestc.dao.impl;
    public class UserDaoImpl implements UserDao {
        private JdbcTemplate template=new JdbcTemplate(JDBCUtils.getDataSource());
        @Override
        public List<User> findAll() {
            //使用JDBC完成查询
            //1.定义sql
            String sql="select * from user";
            List<User> list= template.query(sql,new BeanPropertyRowMapper<User>(User.class));
            return list;
        }
        @Override
        public User findUserByUsernameAndPassWord(String username, String password) {
            try{
                String sql="select * from user where username=? and password = ?";
                User user=template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class),username,password);
                return user;
            }catch (Exception e){
                e.printStackTrace();
                return null;
            }
        }

        @Override
        public void add(User user) {
            //定义sql
            String sql="insert into user value(null,?,?,?,?,?,?,null,null)";
            //执行sql
            template.update(sql,user.getName(),user.getGender(),user.getAge(),user.getAddress(),user.getQq(),user.getEmail());
        }

        @Override
        public void delete(int id) {
            //定义sql
            String sql="delete from user where id=?";
            //执行sql
            template.update(sql,id);
        }
        @Override
        public void update(User user) {
            String sql = "update user set name = ?,gender = ? ,age = ? , address = ? , qq = ?, email = ? where id = ?";
            template.update(sql, user.getName(), user.getGender(), user.getAge(), user.getAddress(), user.getQq(), user.getEmail(), user.getId());
        }
        @Override
        public User findUserById(int id) {
            //定义sql
            String sql="select * from user where id=?";
            //执行sql
            try {
                User user=template.queryForObject(sql,new BeanPropertyRowMapper<User>(User.class),id);
                return user;
            } catch (DataAccessException e) {
                e.printStackTrace();
                return null;
            }
        }
    }

效果展示*：

删除选中功能

  1. 完成全选功能

    首先需要用form包裹table，并为每个选中按钮添加id

    然后通过javascript来给删除选中按钮绑定单击事件

    ![image-20201112100143949](C:\Users\Liu Fei\AppData\Roaming\Typora\typora-user-images\image-20201112100143949.png)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
    <!DOCTYPE html>
    <!-- 网页使用的语言 -->
    <html lang="zh-CN">
    <head>
        <!-- 指定字符集 -->
        <meta charset="utf-8">
        <!-- 使用Edge最新的浏览器的渲染方式 -->
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <!-- viewport视口：网页可以根据设置的宽度自动进行适配，在浏览器的内部虚拟一个容器，容器的宽度与设备的宽度相同。
        width: 默认宽度与设备的宽度相同
        initial-scale: 初始的缩放比，为1:1 -->
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <!-- 上述3个meta标签*必须*放在最前面，任何其他内容都*必须*跟随其后！ -->
        <title>用户信息管理系统</title>
        <!-- 1. 导入CSS的全局样式 -->
        <link href="css/bootstrap.min.css" rel="stylesheet">
        <!-- 2. jQuery导入，建议使用1.9以上的版本 -->
        <script src="js/jquery-2.1.0.min.js"></script>
        <!-- 3. 导入bootstrap的js文件 -->
        <script src="js/bootstrap.min.js"></script>
        <style type="text/css">
            td, th {
                text-align: center;
            }
        </style>
        <script>
           function deleteUser(id) {
               //用户安全提示
               if(confirm("您确定删除么？")){
                   //访问路径
                   location.href="${pageContext.request.contextPath}/deleteUserServlet?id="+id;
               }

           }
           window.onload=function() {
               //给删除选中按钮添加单击事件
               document.getElementById("delSelected").onclick=function(){
                   //表单提交
                   document.getElementById("form").submit();
               }
               //全选功能
               //获取第一个cb
               document.getElementById("firstCb").onclick= function(){
                   //获取下面列表中所有的cb
                   var cbs= document.getElementsByName("uid");
                   //遍历
                   for(var i=0;i< cbs.length;i++){
                       //4.设置这些cbs[i]的状态=firstCb.checked
                       //cbs[i].checked=this.checked;
                       cbs[i].checked = this.checked;
                   }
               }
           }
        </script>
    </head>
    <body>
    <div class="container">
        <h3 style="text-align: center">用户信息列表</h3>
        <div style="float: left;">
            <form class="form-inline">
                <div class="form-group">
                    <label for="exampleInputName2">姓名</label>
                    <input type="text" class="form-control" id="exampleInputName2" >
                </div>
                <div class="form-group">
                    <label for="exampleInputName3">籍贯</label>
                    <input type="text" class="form-control" id="exampleInputName3" >
                </div>

                <div class="form-group">
                    <label for="exampleInputEmail2">邮箱</label>
                    <input type="email" class="form-control" id="exampleInputEmail2"  >
                </div>
                <button type="submit" class="btn btn-default">查询</button>
            </form>
        </div>
        <div style="float: right;margin: 5px;">

            <a class="btn btn-primary" href="${pageContext.request.contextPath}/add.jsp">添加联系人</a>
            <a class="btn btn-primary" href="javascript:void(0);" id="delSelected">删除选中</a>
        </div>
        <form id="form" action="${pageContext.request.contextPath}/delSelectServlet" method="post">

            <table border="1" class="table table-bordered table-hover">
                <tr class="success">
                    <th><input type="checkbox" id="firstCb"></th>
                    <th>编号</th>
                    <th>姓名</th>
                    <th>性别</th>
                    <th>年龄</th>
                    <th>籍贯</th>
                    <th>QQ</th>
                    <th>邮箱</th>
                    <th>操作</th>
                </tr>
                <c:forEach items="${users}" var="user" varStatus="s">
                    <tr>
                        <td><input type="checkbox" name="uid" value="${user.id}"></td>
                        <td>${s.count}</td>
                        <td>${user.name}</td>
                        <td>${user.gender}</td>
                        <td>${user.age}</td>
                        <td>${user.address}</td>
                        <td>${user.qq}</td>
                        <td>${user.email}</td>
                        <td><a class="btn btn-default btn-sm" href="${pageContext.request.contextPath}/findUserServlet?id=${user.id}">修改</a>&nbsp;
                            <a class="btn btn-default btn-sm" href="javascript:deleteUser(${user.id})">删除</a></td>
                    </tr>
                </c:forEach>
            </table>
        </form>
        <div>
            <nav aria-label="Page navigation">
                <ul class="pagination">
                    <li>
                        <a href="#" aria-label="Previous">
                            <span aria-hidden="true">&laquo;</span>
                        </a>
                    </li>
                    <li><a href="#">1</a></li>
                    <li><a href="#">2</a></li>
                    <li><a href="#">3</a></li>
                    <li><a href="#">4</a></li>
                    <li><a href="#">5</a></li>
                    <li>
                        <a href="#" aria-label="Next">
                            <span aria-hidden="true">&raquo;</span>
                        </a>
                    </li>
                    <span style="font-size: 25px;margin-left: 5px;">
                        共16条记录，共4页
                    </span>
                </ul>
            </nav>
        </div>
    </div>
    </body>
    </html>

  1. 完成删除选中功能，新建delSelectServlet

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    package com.uestc.web.servlet;
    @WebServlet("/delSelectServlet")
    public class DelSelectServlet extends HttpServlet {
        protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            //1.获取所有id
            String [] ids=request.getParameterValues("uid");
            //2.调用service删除
            UserService service=new UserServiceImpl();
            service.delSelectedUser(ids);

            //3.跳转到查询所有的servlet
            response.sendRedirect(request.getContextPath()+"/userListServlet");
        }
        protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            this.doPost(request,response);
        }
    }

  2. 在Userservice及其实现类 UserserviceImpl中添加delSelectedUser方法

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    package com.uestc.service;
    import com.uestc.dao.User;
    import java.util.List;
    /**
     * 用户管理的业务接口
     */

    public interface UserService {

        /**
         * 查询所有用户信息
         * @return
         */
        public List<User> findAll();

        /**
         * 登录方法
         * @param loginUser
         * @return
         */
        public User login(User loginUser);

        /**
         * 保存User
         * @param user
         */
        public void addUser(User user);

        /**
         * 删除User
         * @param id
         */
        public void deleteUser(String id);

        /**
         * 根据id查找用户
         * @param id
         * @return
         */
        public User findUserById(String id);

        /**
         * 更新User
         * @param user
         */
        public void updateUser(User user);

        /**
         * 批量删除
         * @param ids
         */
        public void delSelectedUser(String []ids);
    }
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    package com.uestc.service.impl;
    import com.uestc.dao.User;
    import com.uestc.dao.UserDao;
    import com.uestc.dao.impl.UserDaoImpl;
    import com.uestc.service.UserService;

    import java.util.List;

    public class UserServiceImpl implements UserService{
        private UserDao dao=new UserDaoImpl();
        @Override
        public List<User> findAll() {
            //调用dao完成查询
            return dao.findAll();
        }

        @Override
        public void addUser(User user) {
            dao.add(user);
        }

        @Override
        public void deleteUser(String id) {
            dao.delete(Integer.valueOf(id));
        }

        @Override
        public User findUserById(String id) {
            return dao.findUserById(Integer.valueOf(id));
        }

        @Override
        public void updateUser(User user) {
            dao.update(user);
        }

        @Override
        public void delSelectedUser(String [] ids) {
            //1.遍历
            for(String id :ids){
                dao.delete(Integer.valueOf(id));
            }
        }

        @Override
        public User login(User loginUser) {
            return dao.findUserByUsernameAndPassWord(loginUser.getUsername(),loginUser.getPassword());
        }
    }

效果展示：

  1. 删除部分选中：

![](https://cdn.jsdelivr.net/gh/feiLiuTech/imgbed@main/GitLearning/20201112102111.png)

  1. 删除全选 

![](https://cdn.jsdelivr.net/gh/feiLiuTech/imgbed@main/GitLearning/20201112102353.png)

​

优化：

问题：没有选中任何复选框，点击删除选中会出现空指针异常,并且需要删除提示

解决：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
<script>
       function deleteUser(id) {
           //用户安全提示
           if(confirm("您确定删除么？")){
               //访问路径
               location.href="${pageContext.request.contextPath}/deleteUserServlet?id="+id;
           }

       }
       window.onload=function() {
           //给删除选中按钮添加单击事件
           document.getElementById("delSelected").onclick=function(){
               var flag=false;
               if(confirm("您确定要删除选中条目么？")){
                   var cbs= document.getElementsByName("uid");
                   for(var i=0;i<cbs.length;i++){
                       if(cbs[i].checked){
                           flag=true;
                           break;
                       }
                   }
                   if(flag){
                       //表单提交
                       document.getElementById("form").submit();
                   }
               }
           }
           //全选功能
           //获取第一个cb
           document.getElementById("firstCb").onclick= function(){
               //获取下面列表中所有的cb
               var cbs= document.getElementsByName("uid");
               //遍历
               for(var i=0;i< cbs.length;i++){
                   //4.设置这些cbs[i]的状态=firstCb.checked
                   //cbs[i].checked=this.checked;
                   cbs[i].checked = this.checked;
               }
           }
       }
    </script>

效果展示： 此时不会出现空指针异常

分页查询