【JavaWeb·3】Servlet+JDBC实现对数据库内数据增删改查

最新推荐文章于 2024-07-26 12:28:02 发布

禾苗码上

最新推荐文章于 2024-07-26 12:28:02 发布

阅读量2.2k

点赞数 4

分类专栏： JavaWeb 文章标签： java mysql

本文链接：https://blog.csdn.net/qq_42195589/article/details/125563127

版权

JavaWeb 专栏收录该内容

10 篇文章 1 订阅

订阅专栏

Servlet+JDBC增删改查

整体框架
在这在这里插入图片描述
里插入图片描述

准备工作：

1、书写db.properties配置文件

mydriver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/bz?characterEncoding=utf-8
username=root
password=root

2、准备工具类JDBCUtil.java

public class JDBCUtil {
    private static final Properties pro = new Properties();
    private static final ThreadLocal<Connection> tl = new ThreadLocal<>();

    private JDBCUtil() {
    }

    static {
        InputStream is = JDBCUtil.class.getResourceAsStream("/com/baizhi/hjq/conf/db.properties");
        try {
            pro.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
//获取连接
    public static Connection getConnection() {
        Connection conn = tl.get();
        if (conn == null) {
            String driver = pro.getProperty("mydriver");
            try {
                Class.forName(driver);
                String url = pro.getProperty("url");
                String username = pro.getProperty("username");
                String password = pro.getProperty("password");
                conn = DriverManager.getConnection(url, username, password);
                tl.set(conn);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return conn;
    }
//关闭资源
    public static void close(ResultSet rs, Statement pstm, Connection conn) {
        try {
            if (rs != null) rs.close();
            if (pstm != null) pstm.close();
            if (conn != null) {
                conn.close();
                tl.remove();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static void close(Statement pstm, Connection conn) {
        close(null, pstm, conn);
    }

    public static void close(Statement pstm) {
        close(null, pstm, null);
    }

    public static void close(ResultSet rs, Statement pstm) {
        close(rs, pstm, null);
    }

    public static void close(Connection conn) {
        close(null, null, conn);
    }
}

查询全部数据

1、写Dao层接口+实现类

List<Emp> queryAll();

public List<Emp> queryAll() {
        //加载驱动获取链接
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        //返回值为List集合
        List<Emp> list = new ArrayList<>();
        try {
        	//书写sql
            String sql = "select * from emp";
            //准备发送sql的工具
            pstm = conn.prepareStatement(sql);
            //发送sql
            rs = pstm.executeQuery();
            //处理结果集合
            while(rs.next()){
                int empno = rs.getInt(1);
                String ename = rs.getString(2);
                String job = rs.getString(3);
                int mgr = rs.getInt(4);
                Date hireDate = rs.getDate(5);
                double sal = rs.getDouble(6);
                double comm = rs.getDouble(7);
                int deptno = rs.getInt(8);
                Emp emp = new Emp(empno,ename,job,mgr,hireDate,sal,comm,deptno);
                list.add(emp);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException(throwables);
        } finally {
            //释放资源
            JDBCUtil.close(rs,pstm);
        }
    }

2、书写Service层接口+实现类

List<Emp> queryAll();

 public List<Emp> queryAll() {
        Connection conn = JDBCUtil.getConnection();
        try {
            conn.setAutoCommit(false);
            EmpDao empDao = new EmpDaoImpl();
            List<Emp> empList = empDao.queryAll();
            conn.commit();
            return empList;
        } catch (SQLException throwables) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
            throw new RuntimeException(throwables);
        } finally {
            JDBCUtil.close(conn);
        }
    }

3、书写Servlet

public class EmpListServlet implements Servlet {
    @Override
    public void service(ServletRequest req, ServletResponse resp) throws Exception {
    	//设置编码格式
        resp.setContentType("text/html");
        resp.setCharacterEncoding("utf-8");
        PrintWriter out = resp.getWriter();
        //调用service层的查询方法
        EmpService empService = new EmpServiceImpl();
        List<Emp> empList = empService.queryAll();
        //通过表格展示数据
        out.println("<html><head><title>表格</title></head><body>");
        out.println("<table border='1' cellspacing='0'>");
        out.println("<tr>");
        out.println("<th>员工编号</th><th>员工姓名</th><th>工作岗位</th><th>上级领导</th><th>入职日期</th><th>员工工资</th><th>员工奖金</th><th>部门编号</th><th>操作</th>");
        out.println("</tr>");
        for (Emp emp : empList) {
            out.println("<tr>");
            out.println("<td>"+emp.getEmpno()+"</td><td>"+ emp.getEname()+"</td><td>"+emp.getJob()+"</td><td>"+emp.getMgr()+"</td><td>"+emp.getHiredate()+"</td><td>"+emp.getSal()+"</td><td>"+emp.getComm()+"</td><td>"+emp.getDeptno()+"</td>");
            out.println("<td><a href='http://localhost:8080/empmgr/delete.do?empno="+emp.getEmpno()+"'>删除</a></td>");
            out.println("<td><a href='http://localhost:8080/empmgr/update1.do?empno="+emp.getEmpno()+"'>修改</a></td>");
            out.println("</tr>");
        }
        out.println("</table>");
        out.println("<br><br>");
        out.println("<input type='button' onclick='http://localhost:8080/empmgr/html/add.html' value='添加员工信息'>");
        out.println("</body></html>");
    }

    @Override
    public void init(ServletConfig servletConfig) throws ServletException {
    }
    @Override
    public ServletConfig getServletConfig() {
        return null;
    }
    @Override
    public String getServletInfo() {
        return null;
    }
    @Override
    public void destroy() {
    }
}

4、书写web.xml配置文件

 	<servlet>
        <servlet-name>EmpListServlet</servlet-name>
        <servlet-class>com.baizhi.hjq.controller.EmpListServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>EmpListServlet</servlet-name>
        <url-pattern>/list.do</url-pattern>
    </servlet-mapping>

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HROi8xRk-1656668283442)(C:\Users\72442\AppData\Roaming\Typora\typora-user-images\image-20220701171742151.png)]$

删除指定条数据

此处的指定是查询全部时，每条数据后跟删除选项

1、写Dao层接口+实现类

boolean deleteById(int id);

public boolean deleteById(int id) {
        //加载驱动创建连接
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement pstm = null;
        try {
            String sql = "delete from emp where EMPNO=?";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,id);
            //发送sql
            int i = pstm.executeUpdate();
            if(i != 0){
                return true;
            }
            return false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException(throwables);
        } finally {
            //释放资源
            JDBCUtil.close(pstm);
        }
    }

2、书写Service层接口+实现类

boolean deleteById(int id);

@Override
    public boolean deleteById(int id) {
        Connection conn = JDBCUtil.getConnection();
        try {
            conn.setAutoCommit(false);
            EmpDao empDao = new EmpDaoImpl();
            boolean flag = empDao.deleteById(id);
            conn.commit();
            return flag;
        } catch (SQLException throwables) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
            throw new RuntimeException(throwables);
        } finally {
            JDBCUtil.close(conn);
        }
    }

3、书写Servlet

public class EmpDelete implements Servlet {
    @Override
    public void service(ServletRequest req, ServletResponse resp) throws Exception {
        resp.setCharacterEncoding("utf-8");
        resp.setContentType("text/html");
        req.setCharacterEncoding("utf-8");
        PrintWriter out = resp.getWriter();
        EmpService empService = new EmpServiceImpl();
        //获取查询页面传来的id值
        int id = Integer.parseInt(req.getParameter("empno"));
        boolean flag = empService.deleteById(id);
        if(flag){
            out.println("删除成功！");
        }else {
            out.println("删除失败");
        }
    }
    @Override
    public void init(ServletConfig servletConfig) throws ServletException {
    }
    @Override
    public ServletConfig getServletConfig() {
        return null;
    }
    @Override
    public String getServletInfo() {
        return null;
    }
    @Override
    public void destroy() {
    }
}

4、书写web.xml配置文件

	<servlet>
        <servlet-name>EmDelete</servlet-name>
        <servlet-class>com.baizhi.hjq.controller.EmpDelete</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>EmDelete</servlet-name>
        <url-pattern>/delete.do</url-pattern>
    </servlet-mapping>

通过表单增加信息

1、写html页面

注意：表单的action提交跳转

<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>添加员工</title>
</head>
<body>
<form action="http://localhost:8080/empmgr/insert.do" method="post">
  员工编号：<input type="text" name="empno"><br>
  员工姓名：<input type="text" name="ename"><br>
  工作岗位：<input type="text" name="job"><br>
  领导编号：<input type="text" name="mgr"><br>
  入职日期：<input type="text" name="hiredate"><br>
  员工工资：<input type="text" name="sal"><br>
  员工津贴：<input type="text" name="comm"><br>
  部门编号：<input type="text" name="deptno"><br>
  <input type="submit" value="添加">
</form>
</body>
</html>

2、写Dao层接口+实现类

boolean insert(Emp emp);

 @Override
    public boolean insert(Emp emp) {
        //加载驱动创建连接
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement pstm = null;
        try {
            String sql = "insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(?,?,?,?,?,?,?,?)";
            pstm = conn.prepareStatement(sql);
            //绑定参数
            pstm.setInt(1,emp.getDeptno());
            pstm.setString(2,emp.getEname());
            pstm.setString(3,emp.getJob());
            pstm.setInt(4,emp.getMgr());
            pstm.setDate(5,(Date)emp.getHiredate());
            pstm.setDouble(6,emp.getSal());
            pstm.setDouble(7,emp.getComm());
            pstm.setInt(8,emp.getDeptno());
            int i = pstm.executeUpdate();
            if(i!=0)return true;
            return false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException(throwables);
        } finally {
            //释放资源
            JDBCUtil.close(pstm);
        }
    }

3、书写Service层接口+实现类

boolean insert(Emp emp) throws SQLException;

@Override
    public boolean insert(Emp emp){
        Connection conn = JDBCUtil.getConnection();
        try {
            conn.setAutoCommit(false);
            EmpDao empDao = new EmpDaoImpl();
            boolean flag = empDao.insert(emp);
            conn.commit();
            return flag;
        } catch (SQLException throwables) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
            throw new RuntimeException(throwables);
        } finally {
            //释放资源
            JDBCUtil.close(conn);
        }
    }

4、书写Servlet

public class EmpInsert implements Servlet {
    @Override
    public void service(ServletRequest req, ServletResponse resp) throws Exception {
        resp.setContentType("text/html");
        resp.setCharacterEncoding("utf-8");
        req.setCharacterEncoding("utf-8");
        //获取表单传来的数据们
        int empno = Integer.parseInt(req.getParameter("empno"));
        String ename = req.getParameter("ename");
        String job = req.getParameter("job");
        int mgr = Integer.parseInt(req.getParameter("mgr"));
        //日期类型需要转换
        Date date = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            date = sdf.parse(req.getParameter("hiredate"));
        } catch (ParseException e) {
            e.printStackTrace();
        }
        double sal = Double.parseDouble(req.getParameter("sal"));
        double comm = Double.parseDouble(req.getParameter("comm"));
        int deptno = Integer.parseInt(req.getParameter("deptno"));
        Emp emp = new Emp(empno,ename,job,mgr,new java.sql.Date(date.getTime()),sal,comm,deptno);
        EmpService empService = new EmpServiceImpl();
        PrintWriter out = resp.getWriter();
        try {
            boolean flag = empService.insert(emp);
            if(flag){
                out.println("添加成功啦");
            }else{
                out.println("添加失败");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    @Override
    public void init(ServletConfig servletConfig) throws ServletException {
    }
    @Override
    public ServletConfig getServletConfig() {
        return null;
    }
    @Override
    public String getServletInfo() {
        return null;
    }
    @Override
    public void destroy() {
    }
}

5、书写配置文件

	<servlet>
        <servlet-name>EmpInsert</servlet-name>
        <servlet-class>com.baizhi.hjq.controller.EmpInsert</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>EmpInsert</servlet-name>
        <url-pattern>/insert.do</url-pattern>
    </servlet-mapping>

更新数据【难点】

更新数据分为两部分

1、先对指定id的数据以表单的形式进行查询显示

2、在显示的数据上进行修改操作，表单提交后，将表单内数据带到跳转页面中，实现更新操作。

指定id数据以表单查询显示

1、写Dao层接口+实现类

Emp queryById(int id);

@Override
    public Emp queryById(int id) {
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            String sql = "select * from emp where empno=?";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,id);
            rs = pstm.executeQuery();
            Emp emp = null;
            //处理结果集合
            while (rs.next()){
                int empno = rs.getInt(1);
                String ename = rs.getString(2);
                String job = rs.getString(3);
                int mgr = rs.getInt(4);
                Date date = rs.getDate(5);
                double sal = rs.getDouble(6);
                double comm = rs.getDouble(7);
                int deptno = rs.getInt(8);
                emp = new Emp(empno,ename,job,mgr,date,sal,comm,deptno);
            }
            return emp;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException(throwables);
        } finally {
            JDBCUtil.close(rs,pstm);
        }
    }

2、书写Service层接口+实现类

Emp queryById(int id);

@Override
    public Emp queryById(int id) {
        Connection conn = JDBCUtil.getConnection();
        try {
            conn.setAutoCommit(false);
            EmpDao empDao = new EmpDaoImpl();
            Emp emp = empDao.queryById(id);
            conn.commit();
            return emp;
        } catch (SQLException throwables) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
            throw new RuntimeException(throwables);
        } finally {
            JDBCUtil.close(conn);
        }
    }

3、书写Servlet

public class EmpUpdate1 implements Servlet {
    @Override
    public void service(ServletRequest req, ServletResponse resp) throws Exception {
        req.setCharacterEncoding("utf-8");
        resp.setCharacterEncoding("utf-8");
        resp.setContentType("text/html");
        PrintWriter out = resp.getWriter();
        EmpService empService = new EmpServiceImpl();
        int id = Integer.parseInt(req.getParameter("empno"));
        Emp emp = empService.queryById(id);
        //将查询结果以表单的形式显示
        out.println("<html><head><title>更新操作</title></head><body>");
        out.println("<form action='update2.do' method='post'");
        out.println("员工信息<br>");
        out.println("编号：<input type='text' value="+emp.getEmpno()+" name='empno'><br>");
        out.println("姓名：<input type='text' value="+emp.getEname()+" name='ename'><br>");
        out.println("岗位：<input type='text' value="+emp.getJob()+" name='job'><br>");
        out.println("上司：<input type='text' value="+emp.getMgr()+" name='mgr'><br>");
        out.println("日期：<input type='text' value="+emp.getHiredate()+" name='hiredate'><br>");
        out.println("工资：<input type='text' value="+emp.getSal()+" name='sal'><br>");
        out.println("补贴：<input type='text' value="+emp.getComm()+" name='comm'><br>");
        out.println("部门：<input type='text' value="+emp.getDeptno()+" name='deptno'><br>");
        out.println("<input type='submit' value='修改'>");
        out.println("</form></body></html>");
    }
    @Override
    public void init(ServletConfig servletConfig) throws ServletException { }

    @Override
    public ServletConfig getServletConfig() { return null; }

    @Override
    public String getServletInfo() { return null; }

    @Override
    public void destroy() { }
}

4、书写配置文件

	<servlet>
        <servlet-name>EmpUpdate1</servlet-name>
        <servlet-class>com.baizhi.hjq.controller.EmpUpdate1</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>EmpUpdate1</servlet-name>
        <url-pattern>/update1.do</url-pattern>
    </servlet-mapping>

修改操作

1、写Dao层接口+实现类

boolean update(Emp emp);

@Override
    public boolean update(Emp emp) {
        //加载驱动，获取链接
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement pstm = null;
        try {
            String sql = "update emp set ename=?,job=?,mgr=?,hiredate=?,sal=?,comm=?,deptno=? where empno=?";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1,emp.getEname());
            pstm.setString(2,emp.getJob());
            pstm.setInt(3,emp.getMgr());
            pstm.setDate(4,new Date(emp.getHiredate().getTime()));
            pstm.setDouble(5,emp.getSal());
            pstm.setDouble(6,emp.getComm());
            pstm.setInt(7,emp.getDeptno());
            pstm.setInt(8,emp.getEmpno());
            int count = pstm.executeUpdate();
            if(count != 0){
                return true;
            }
            return false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException(throwables);
        } finally {
            JDBCUtil.close(pstm);
        }
    }

2、书写Service层接口+实现类

boolean update(Emp emp) throws SQLException;

 @Override
    public boolean update(Emp emp) {
        Connection conn = JDBCUtil.getConnection();
        try {
            conn.setAutoCommit(false);
            EmpDao empDao = new EmpDaoImpl();
            boolean flag = empDao.update(emp);
            conn.commit();
            return flag;
        } catch (SQLException throwables) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
            throw new RuntimeException(throwables);
        } finally {
            JDBCUtil.close(conn);
        }
    }

3、书写Servlet

public class EmpUpdate2 implements Servlet {
    @Override
    public void service(ServletRequest req, ServletResponse resp) throws Exception {
        req.setCharacterEncoding("utf-8");
        resp.setContentType("text/html");
        resp.setCharacterEncoding("utf-8");
        PrintWriter out = resp.getWriter();
        int empno = Integer.parseInt(req.getParameter("empno"));
        String ename = req.getParameter("ename");
        String job = req.getParameter("job");
        int mgr = Integer.parseInt(req.getParameter("mgr"));
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date date = null;
        try {
            date = sdf.parse(req.getParameter("hiredate"));
        } catch (ParseException e) {
            e.printStackTrace();
        }
        double sal = Double.parseDouble(req.getParameter("sal"));
        double comm = Double.parseDouble(req.getParameter("comm"));
        int deptno = Integer.parseInt(req.getParameter("deptno"));
        Emp emp = new Emp(empno,ename,job,mgr,date,sal,comm,deptno);
        EmpService empService = new EmpServiceImpl();
        boolean flag = false;
        try {
            flag = empService.update(emp);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        if(flag){
            out.println("更新成功");
        }else{
            out.println("更新失败");
        }
    }
    @Override
    public void init(ServletConfig servletConfig) throws ServletException {
    }
    @Override
    public ServletConfig getServletConfig() {
        return null;
    }
    @Override
    public String getServletInfo() {
        return null;
    }
    @Override
    public void destroy() {
    }
}

4、书写配置文件

	<servlet>
        <servlet-name>EmpUpdate2</servlet-name>
        <servlet-class>com.baizhi.hjq.controller.EmpUpdate2</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>EmpUpdate2</servlet-name>
        <url-pattern>/update2.do</url-pattern>
    </servlet-mapping>