第四篇——JDBC操作数据库之修改数据

最新推荐文章于 2024-04-08 21:24:28 发布

Remember_Ray

最新推荐文章于 2024-04-08 21:24:28 发布

阅读量1w

点赞数 4

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

版权

Java Web 同时被 2 个专栏收录

28 篇文章 6 订阅

订阅专栏

Java Web

18 篇文章 1 订阅

订阅专栏

使用JDBC修改数据库中的数据，其操作方法是和添加数据差不多的，只不过在修改数据的时候还要用到UPDATE语句来实现的，例如：把图书信息id为1的图书数量改为100，其sql语句是：update book set bookCount=100 where id=1。在实际开发过程中，通常会由程序传递SQL语句中的参数，所以修改数据也通常使用PreparedStatement对象进行操作。

步骤说明：
1、代码请参考——第三篇JDBC数据库之添加数据。

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

一、bookList.jsp

1.添加<td><a href="<%=request.getContextPath()%>/bookUpdate?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>xx</td>
                </tr>
            </c:forEach>
        </table>
    </form>
</body>
</html>

二、BookUpdate.java

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

2.调用模型----调用查询方法-->根据Id

/**
 * Created by Ray on 2018/3/11 0011.
 **/
public class BookUpdate 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("/bookUpdate.jsp").forward(request,response);
    }
}

三、bookUpdate.jsp

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

<%@ 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()%>/BookDoUpdate" 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}"></td>
                </tr>
                <tr>
                    <td>图书名称:</td>
                    <td><input type="text" name="name" value="${bookitem.name}"></td>
                </tr>
                <tr>
                    <td>图书价格:</td>
                    <td><input type="text" name="price" value="${bookitem.price}"></td>
                </tr>
                <tr>
                    <td>图书数量:</td>
                    <td><input type="text" name="bookCount" value="${bookitem.bookCount}"></td>
                </tr>
                <tr>
                    <td>图书作者:</td>
                    <td><input type="text" name="author" value="${bookitem.author}"></td>
                </tr>
            </c:forEach>
            <tr>
                <td><input type="submit" value="修改"></td>
                <td><input type="reset" value="重置"></td>
            </tr>
        </table>
    </form>
</body>
</html>

四、BookDoUpdate.java

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

2.调用模型----调用更新方法

package com.control;

import com.model.BookModel;

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

/**
 * Created by Ray on 2018/3/11 0011.
 **/
public class BookDoUpdate 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"));
        String name = request.getParameter("name");
        double price = Double.parseDouble(request.getParameter("price"));
        int bookCount = Integer.parseInt(request.getParameter("bookCount"));
        String author = request.getParameter("author");

        //调用模型
        BookModel BookModel = new BookModel();
        //调用更新方法
        BookModel.updateBook(id,name,price,bookCount,author);
        //response重定向改变路径
        response.sendRedirect(request.getContextPath() + "/BookList");
    }
}

五、BookModel.java

1.根据id获取信息listId(int id)

2.修改图书信息updateBook(int id,String name,double price,int bookCount,String author)

package com.model;

import com.dbutil.Dbconn;
import com.entity.Book;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 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();
        }
    }
}

六、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>

</web-app>

七、页面效果

ok！

Remember_Ray

关注

4
点赞
踩
23

收藏

觉得还不错? 一键收藏
0
评论
第四篇——JDBC操作数据库之修改数据

使用JDBC修改数据库中的数据，其操作方法是和添加数据差不多的，只不过在修改数据的时候还要用到UPDATE语句来实现的，例如：把图书信息id为1的图书数量改为100，其sql语句是：update book set bookCount=100 where id=1。在实际开发过程中，通常会由程序传递SQL语句中的参数，所以修改数据也通常使用PreparedStatement对象进行操作。步骤说明：1...
复制链接

扫一扫