第五篇——JDBC操作数据库之删除数据

最新推荐文章于 2024-09-13 13:44:42 发布

Remember_Ray

最新推荐文章于 2024-09-13 13:44:42 发布

阅读量3.6k

点赞数 2

本文链接：https://blog.csdn.net/q343509740/article/details/79525640

版权

Java Web 同时被 2 个专栏收录

28 篇文章 6 订阅

订阅专栏

Java Web

18 篇文章 1 订阅

订阅专栏

删除数据使用的SQL语句为delete语句，如果删除图书id为1的图书信息，其SQL语句为：

delete from book where id=1

在实际开发中删除数据通常使用PreparedStatement对象进行操作。

步骤说明：
1、部分代码请参考——第四篇JDBC操作数据库之修改数据。

2、在第四篇基础上继续实现功能——第五篇JDBC操作数据库之删除数据。

一、bookList.jsp

1.添加

<td><a href="<%=request.getContextPath()%>/BookDelete?id=${bookitem.id}">删除</a></td>

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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">

    <title>图书列表</title>

    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">

	<link rel="stylesheet" type="text/css" href="css/bookList.css">


</head>

<body>
    <form action="" method="post">
        <table width="80%" align="center">
            <div class="list">图书列表</div>
            <ul>
                <li><a href="bookAdd.jsp">新增图书</a> </li>
                <li>xxx</li>
                <li>xxx</li>
                <li>xxx</li>
            </ul>
            <tr>
                <td>图书编号</td>
                <td>图书名称</td>
                <td>图书价格</td>
                <td>图书数量</td>
                <td>图书作者</td>
                <td>图书修改</td>
                <td>图书删除</td>
            </tr>
            <c:forEach var="bookitem" items="${booklist}">
                <tr>
                    <td>${bookitem.id}</td>
                    <td>${bookitem.name}</td>
                    <td>${bookitem.price}</td>
                    <td>${bookitem.bookCount}</td>
                    <td>${bookitem.author}</td>
                    <td><a href="<%=request.getContextPath()%>/BookUpdate?id=${bookitem.id}">修改</a></td>
                    <td><a href="<%=request.getContextPath()%>/BookDelete?id=${bookitem.id}">删除</a></td>
                </tr>
            </c:forEach>
        </table>
    </form>
</body>
</html>

二、BookDelete.java

1.根据input标签name属性获取值

2.调用模型---调用查询方法(根据id)

3.request传递值

/**
 * Created by Ray on 2018/3/12 0012.
 **/
public class BookDelete extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //根据input name获取值
        int id = Integer.parseInt(request.getParameter("id"));
        //调用模型
        BookModel bookModel = new BookModel();
        //调用查询方法(根据id),返回booklist集合
        List booklist = bookModel.listId(id);
        //不同页面间传递,传递一次后,request失去作用
        request.setAttribute("booklist",booklist);
        //request请求,不会改变路径
        request.getRequestDispatcher("/bookDelete.jsp").forward(request,response);
    }
}

三、bookDelete.jsp

1.添加

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

2.跳转servlet---BookDoDelete.java

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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">

    <title>删除图书信息</title>

    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">
    <!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->

</head>

<body>
    <form action="<%=request.getContextPath()%>/BookDoDelete" method="post">
        <table align="center">
            <div align="center" style="font-size: 36px;font-weight: bold;">删除列表</div>
            <c:forEach var="bookitem" items="${booklist}">
                <tr>
                    <td>图书编号:</td>
                    <td><input type="text" name="id" value="${bookitem.id}" readonly="readonly"></td>
                </tr>
                <tr>
                    <td>图书名称:</td>
                    <td><input type="text" name="name" value="${bookitem.name}" readonly="readonly"></td>
                </tr>
                <tr>
                    <td>图书价格:</td>
                    <td><input type="text" name="price" value="${bookitem.price}" readonly="readonly"></td>
                </tr>
                <tr>
                    <td>图书数量:</td>
                    <td><input type="text" name="bookCount" value="${bookitem.bookCount}" readonly="readonly"></td>
                </tr>
                <tr>
                    <td>图书作者:</td>
                    <td><input type="text" name="author" value="${bookitem.author}" readonly="readonly"></td>
                </tr>
            </c:forEach>
            <tr>
                <td><input type="submit" value="删除"></td>
                <td>
                    <button>
                        <a href="<%=request.getContextPath()%>/BookList" style="text-decoration: none">返回</a>
                    </button>
                </td>
            </tr>
        </table>
    </form>
</body>
</html>

四、BookDoDelete.java

1.根据input标签name属性获取值

2.调用模型---调用删除方法(根据id)

3.response重定向

/**
 * Created by Ray on 2018/3/12 0012.
 **/
public class BookDoDelete extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //根据input name获取值
        int id = Integer.parseInt(request.getParameter("id"));
        //调用模型
        BookModel bookModel = new BookModel();
        //调用删除方法(根据id)
        bookModel.deleteBook(id);
        //response重定向改变路径
        response.sendRedirect(request.getContextPath() + "/BookList");
    }
}

五、BookModel.java

1.根据Id删除图书信息

/**
 * Created by Ray on 2018/3/11 0011.
 **/
public class BookModel {

    Dbconn Dbconn = new Dbconn();
    Connection conn = null;
    PreparedStatement ps;
    ResultSet rs;

    /**
    * @Author: Ray
    * @Date: 2018/3/11 0011
    * @Description: 查询所有图书信息
    * @Return: booklist
    */
    public List<Book> bookList(){
        //创建booklist集合
        List booklist = new ArrayList();
        Book Book = null;
        try{
            //创建Connection连接
            conn = Dbconn.getConnection();
            //查询所有图书信息的sql语句
            String sql = "select * from booktable";
            //获取PreparedStatement
            ps = conn.prepareStatement(sql);
            //执行查询
            rs = ps.executeQuery();
            //判断光标向后移动,并判断是否有效
            while (rs.next()){
                //实例化Book对象
                Book = new Book();
                //对id属性赋值
                Book.setId(rs.getInt("id"));
                //对name属性赋值
                Book.setName(rs.getString("name"));
                //对price属性赋值
                Book.setPrice(rs.getDouble("price"));
                //对bookCount属性赋值
                Book.setBookCount(rs.getInt("bookCount"));
                //对author属性赋值
                Book.setAuthor(rs.getString("author"));
                //将book对象添加到集合中
                booklist.add(Book);
            }
            //关闭连接
            Dbconn.closeConnection(conn,ps,rs);
        }catch (Exception e){
            e.printStackTrace();
        }
        //返回booklist集合
        return booklist;
    }

    /**
    * @Author: Ray
    * @Date: 2018/3/11 0011
    * @Description: 新增图书信息
    * @Return:
    */
    public void addBook(int id,String name,double price,int bookCount,String author){
        try{
            //创建Connection连接
            conn = Dbconn.getConnection();
            //添加图书信息的sql语句
            String sql = "insert into booktable(id,name,price,bookCount,author) values(?,?,?,?,?)";
            //获取PrepareStatement
            ps = conn.prepareStatement(sql);
            //对占位符进行赋值
            ps.setInt(1,id);
            ps.setString(2,name);
            ps.setDouble(3,price);
            ps.setInt(4,bookCount);
            ps.setString(5,author);
            //执行更新,返回更新锁影响的行数
            int row = ps.executeUpdate();
            if(row > 0){
                System.out.println("添加数据成功");
            }
            ps.close();
            conn.close();
        }catch (Exception e){
            System.out.println("添加数据失败");
            e.printStackTrace();
        }
    }

    /**
    * @Author: Ray
    * @Date: 2018/3/11 0011
    * @Description: 根据Id查询图书信息
    * @Return: booklist集合
    */
    public List<Book> listId(int id){
        //创建booklist集合
        List booklist = new ArrayList();
        Book Book = null;
        try{
            //创建Connection 连接
            conn = Dbconn.getConnection();
            //根据id查询图书信息的sql语句
            String sql = "select * from booktable where id = ?";
            //创建PreparedStatement
            ps = conn.prepareStatement(sql);
            //对占位符进行赋值
            ps.setInt(1,id);
            //执行查询
            rs = ps.executeQuery();
            //判断光标向后移动,并判断是否有效
            while(rs.next()){
                Book = new Book();
                Book.setId(rs.getInt("id"));
                Book.setName(rs.getString("name"));
                Book.setPrice(rs.getDouble("price"));
                Book.setBookCount(rs.getInt("bookCount"));
                Book.setAuthor(rs.getString("author"));
                booklist.add(Book);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return booklist;
    }

    /**
    * @Author: Ray
    * @Date: 2018/3/11 0011
    * @Description: 修改图书信息
    * @Return:
    */
    public void updateBook(int id,String name,double price,int bookCount,String author){
        try{
            //创建Connection连接
            conn = Dbconn.getConnection();
            //更新图书信息的sql语句
            String sql = "update booktable set name=?,price=?,bookCount=?,author=? where id=?";
            //获取PreparedStatement
            ps = conn.prepareStatement(sql);
            //对占位符进行赋值
            ps.setString(1,name);
            ps.setDouble(2,price);
            ps.setInt(3,bookCount);
            ps.setString(4,author);
            ps.setInt(5,id);
            //执行更新
            int row = ps.executeUpdate();
            //判断是否有效
            if(row > 0){
                System.out.println("修改数据成功");
            }
            //关闭连接
            ps.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    
    /**
    * @Author: Ray
    * @Date: 2018/3/12 0012
    * @Description: 根据Id删除图书信息
    * @Return: 
    */
    public void deleteBook(int id){
        try{
            //创建Connection连接
            conn = Dbconn.getConnection();
            //删除图书信息的SQL语句
            String sql = "delete from booktable where id = ?";
            //获取PreparedStatement
            ps = conn.prepareStatement(sql);
            //对占位符进行赋值
            ps.setInt(1,id);
            //执行删除
            int row = ps.executeUpdate();
            //判断是否有效
            if(row > 0){
                System.out.println("删除数据成功");
            }
            ps.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

六、web.xml

这个很关键,不能出错,否则访问会出现404错误

<?xml version="1.0" encoding="UTF-8"?>
<web-app>
    <servlet>
        <servlet-name>BookList</servlet-name>
        <servlet-class>com.control.BookList</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>BookList</servlet-name>
        <url-pattern>/BookList</url-pattern>
    </servlet-mapping>

    <servlet>
        <servlet-name>BookAdd</servlet-name>
        <servlet-class>com.control.BookAdd</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>BookAdd</servlet-name>
        <url-pattern>/BookAdd</url-pattern>
    </servlet-mapping>

    <servlet>
        <servlet-name>BookUpdate</servlet-name>
        <servlet-class>com.control.BookUpdate</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>BookUpdate</servlet-name>
        <url-pattern>/BookUpdate</url-pattern>
    </servlet-mapping>

    <servlet>
        <servlet-name>BookDoUpdate</servlet-name>
        <servlet-class>com.control.BookDoUpdate</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>BookDoUpdate</servlet-name>
        <url-pattern>/BookDoUpdate</url-pattern>
    </servlet-mapping>

    <servlet>
        <servlet-name>BookDelete</servlet-name>
        <servlet-class>com.control.BookDelete</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>BookDelete</servlet-name>
        <url-pattern>/BookDelete</url-pattern>
    </servlet-mapping>

    <servlet>
        <servlet-name>BookDoDelete</servlet-name>
        <servlet-class>com.control.BookDoDelete</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>BookDoDelete</servlet-name>
        <url-pattern>/BookDoDelete</url-pattern>
    </servlet-mapping>

</web-app>

七、页面效果