连接数据库实现图书管理系统-CSDN博客

本文链接：https://blog.csdn.net/qq_50352386/article/details/120571036

使用JDBC连接MYSQL数据库，实现对图书的浏览，添加，修改，删除功能

index.jsp 浏览图书所有信息，并提供添加，修改和删除图书的超级链接

add.html 添加图书信息的表单页面，表单提交到add.jsp页面

add.jsp 添加图书信息的处理页面，将添加的图书信息插入到数据库，处理完成后提示信息并跳转到index.jsp页面

edit.jsp 修改图书信息的表单页面，该页面显示预修改的图书信息，表单提交到edit_do.jsp页面

edit_do.jsp 修改图书信息的处理页面，将修改的图书信息更新到数据库，处理完成后提示信息并跳转到index.jsp页面

del.jsp 删除图书信息的处理页面，从数据库中删除图书信息，处理完成后提示信息并跳转到index.jsp页面

index.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" import="java.sql.*" %>
<html>
  <head>
    <title>图书管理系统</title>
  </head>
  <link rel="stylesheet" type="text/css" href="book.css">
  <h2 align="center" style="color: darkseagreen">图书管理系统</h2>
  <p align="center"><a href="add.html">添加图书信息</a></p>
  <table align="center" width="50%" id="table-3">
  <tr>
    <th>书名</th>
    <th>作者</th>
    <th>出版社</th>
    <th>价格</th>
    <th>管理</th>
  </tr>
    <%
      Connection connection = null;
      PreparedStatement preparedStatement =null;
      ResultSet resultSet =null;
      try {
        connection = Jdbcutils.getConnection();
        String sql="select * from bookinfo";
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
          int id=resultSet.getInt(1);


    %>
    <tr>
      <td><%=resultSet.getString("bookname")%></td>
      <td><%=resultSet.getString("author")%></td>
      <td><%=resultSet.getString("press")%></td>
      <td><%=resultSet.getString("price")%></td>
      <td>
        <a href="edit.jsp?id=<%=id%>">修改</a>&nbsp;
        <a href="del.jsp?id=<%=id%>">删除</a>
      </td>
    </tr>
    <%
      }
      } catch (SQLException throwables) {
        throwables.printStackTrace();
      }finally {
        Jdbcutils.close(resultSet,preparedStatement,connection);
      }
    %>
  </table>
  </body>
</html>

add.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>添加图书信息</title>
</head>
<link rel="stylesheet" type="text/css" href="book.css">

<body>
<h2 align="center" style="color: darkseagreen">添加图书信息</h2>
<form name="form1" action="add.jsp" method="post">
<table align="center" width="30%" id="table-3" >
    <tr>
        <th width="30%">书名：</th>
        <td><input type="text" name="bookname"></td>
    </tr>
    <tr>
        <th width="30%">作者：</th>
        <td><input type="text" name="author"></td>
    </tr>
    <tr>
        <th width="30%">出版社：</th>
        <td><input type="text" name="press"></td>
    </tr>
    <tr>
        <th width="30%">价格：</th>
        <td><input type="text" name="price"></td>
    </tr>
    <tr>
        <th colspan="2">
            <input type="submit" value="添加">
            <input type="reset" value="重置" οnclick="formReset()">
        </th>
    </tr>
</table>
    <script>
    function formReset()
    {
    document.getElementById("from1").reset()
    }
    </script>
</form>
</body>
</html>

add.jsp

<%@ page contentType="text/html;charset=utf-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<%
    request.setCharacterEncoding("utf-8");
    String bookname=request.getParameter("bookname");
    String author=request.getParameter("author");
    String press=request.getParameter("press");
    String price=request.getParameter("price");
    Connection connection = Jdbcutils.getConnection();
    String sql="insert into bookinfo values(null,?,?,?,?)";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setString(1,bookname);
    preparedStatement.setString(2,author);
    preparedStatement.setString(3,press);
    preparedStatement.setFloat(4, Float.parseFloat(price));
    int i = preparedStatement.executeUpdate();
    String msg="添加失败，单击确定跳转到图书列表页";
    if(i==1)
        msg="添加成功，单击确定跳转到图书列表页";
    Jdbcutils.close(null,preparedStatement,connection);
%>
<script>window.alert('<%=msg%>')</script>
<%
    response.setHeader("Refresh","1;url=index.jsp");
%>
</body>
</html>

edit.jsp

<%@ page contentType="text/html;charset=utf-8" language="java" %>
<html>
<head>
    <title>修改图书信息</title>
</head>
<link rel="stylesheet" type="text/css" href="book.css">
<body>
<%
    request.setCharacterEncoding("utf-8");
    String id=request.getParameter("id");
    Connection connection = null;
    PreparedStatement preparedStatement =null;
    ResultSet resultSet =null;
    try {
        connection = Jdbcutils.getConnection();
        String sql="select * from bookinfo where id=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,Integer.parseInt(id));
        resultSet = preparedStatement.executeQuery();
        if(resultSet.next())
        {
            String bookname=resultSet.getString("bookname");
            String author=resultSet.getString("author");
            String press=resultSet.getString("press");
            float price=resultSet.getFloat("price");

%>
    <h2 align="center"style="color: darkseagreen">修改图书信息</h2>
    <form name="form1" action="edit_do.jsp"method="get">
        <input type="hidden" name="id" value="<%=id%>">
        <table align="center" width="30%" id="table-3">
            <tr>
                <th width="30%">
                    书名：
                </th>
                <td>
                    <input type="text" name="bookname" value="<%=bookname%>">
                </td>
            </tr>
            <tr>
                <th width="30%">
                    作者：
                </th>
                <td>
                    <input type="text" name="author" value="<%=author%>">
                </td>
            </tr>
            <tr>
                <th width="30%">
                    出版社：
                </th>
                <td>
                    <input type="text" name="press" value="<%=press%>">
                </td>
            </tr>
            <tr>
                <th width="30%">
                    价格：
                </th>
                <td>
                    <input type="text" name="price" value="<%=price%>">
                </td>
            </tr>
            <tr>
                <th colspan="2">
                    <input type="submit" value="修改">
                    <input type="reset" value="重置">
                </th>
            </tr>

        </table>
    </form>

<%
        }
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }finally {
        Jdbcutils.close(resultSet,preparedStatement,connection);
    }
%>
</body>
</html>

edit_do.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title></title>
</head>
<body>
<%
    request.setCharacterEncoding("utf-8");
    String id=request.getParameter("id");
    String bookname=request.getParameter("bookname");
    String author=request.getParameter("author");
    String press=request.getParameter("press");
    String price=request.getParameter("price");
    
    Connection   connection = Jdbcutils.getConnection();
    String sql="update bookinfo set bookname=?,author=?,press=?,price=? where id=?";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setString(1,bookname);
    preparedStatement.setString(2,author);
    preparedStatement.setString(3,press);
    preparedStatement.setFloat(4, Float.parseFloat(price));
    preparedStatement.setInt(5,Integer.parseInt(id));
    int i = preparedStatement.executeUpdate();
    String msg="修改失败，单击确定跳转到图书列表页";
    if(i==1)
        msg="修改成功，单击确定跳转到图书列表页";
    Jdbcutils.close(null, preparedStatement, connection);
%>
<script>window.alert('<%=msg%>')</script>
<%
    response.setHeader("Refresh","1;url=index.jsp");
%>
</body>
</html>

del.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<%
    String id=request.getParameter("id");
    Connection connection = Jdbcutils.getConnection();
    String sql="delete from bookinfo where id=?";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setInt(1,Integer.parseInt(id));
    int i = preparedStatement.executeUpdate();
    String msg="删除失败，单击确定跳转到图书列表页";
    if(i==1)
        msg="删除成功，单击确定跳转到图书列表页";
%>
<%
    Jdbcutils.close(null,preparedStatement,connection);
%>
<script>window.alert('<%=msg%>')</script>
<%
    response.setHeader("Refresh","1;url=index.jsp");
%>
</body>
</html>