JSP--------------------图书管理器

题目：图书管理器

1.数据库设计

//创建数据库
CREATE DATABASE bkmanage;

//使用 数据库
use bkmanage;


//创建数据表
CREATE TABLE bookmanage(
b_id BIGINT PRIMARY KEY auto_increment,
b_name VARCHAR(40) NOT NULL,
b_auto VARCHAR(40) NOT NULL,
b_time datetime NOT null,
b_type int NOT null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

//测试数据 

INSERT into bookmanage (b_name,b_auto,b_time,b_type) VALUES('axing','开心',now(),1);
INSERT into bookmanage (b_name,b_auto,b_time,b_type) VALUES('xiaohong','一年',now(),2);
INSERT into bookmanage (b_name,b_auto,b_time,b_type) VALUES('xiaohui','一月',now(),3);

 2.使用 MyEclipse 创建 Web 项目 BookManage，导入数据库驱动包，在源文件夹中创建 4 个包：entity 包用于存放实体类，dao 包用于存放数据访问类，service 包用于存5 放业务逻辑处理类，servlet 包用于存放接收请求的 Servlet 类 

2.1 创建实体类 BookManage.java

package com.example.entity;

public class BookManage {
    private int id;
    private String name;
    private String author;
    private String time;
    private int type;

    public BookManage() {
    }

    public BookManage(int id, String name, String author, String time, int type) {
        this.id = id;
        this.name = name;
        this.author = author;
        this.time = time;
        this.type = type;
    }

    public BookManage(String name, String author, String time, int type) {
        this.name = name;
        this.author = author;
        this.time = time;
        this.type = type;
    }

    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 getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public String getTime() {
        return time;
    }

    public void setTime(String time) {
        this.time = time;
    }

    public int getType() {
        return type;
    }

    public void setType(int type) {
        this.type = type;
    }
}

2.2 创建 DAO基类 :BaseDao.java 包含基本数据库资源的获取、释放操作。注意释放资源时的先后顺序及异常处理

package com.example.dao;

import java.sql.*;

public class BaseDao {
    final static String url="jdbc:mysql://localhost:3306/bkmanage?characterEncoding=utf8";
    final static String user="root";
    final static String password="";

    /**
     * 数据库连接工具类 返回一个连接 该方法是获取连接 将url user password 设置为变量 一旦设置为变量 就不可以在修改
     * @return
     */
    public Connection getConnection(){
        Connection con=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return con;
    }

    /**
     * 该方法是关闭连接 (三个参数)
     * @param con
     * @param pre
     * @param re
     */
    public void closeConnection(Connection con, PreparedStatement pre, ResultSet re){
        try {
            if (con!=null) {
                con.close();
            }
            if(pre!=null){
                pre.close();
            }
            if (re!=null){
                re.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 关闭连接(参数两个)
     * @param con
     * @param pre
     */
    public void closeConnection(Connection con, PreparedStatement pre){
        try {
            if (con!=null) {
                con.close();
            }
            if(pre!=null){
                pre.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

创建 DAO 类：BookManageDao.java（接口加实现类）

package com.example.dao;

import com.example.entity.BookManage;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class BookManageDao {
    BaseDao baseDao=new BaseDao();

    /**
     * 这是一个查询所有图书的方法  不需要任何参数
     * @return 返回是一个list对象
     */
    public List<BookManage> queryBook(){
        Connection con = baseDao.getConnection();
        String sql="SELECT * FROM bookmanage";
        PreparedStatement pre=null;
        ResultSet rs=null;
        List list = new ArrayList();
        try {
             pre = con.prepareStatement(sql);
             rs = pre.executeQuery();
             while(rs.next()){
                 BookManage bm=new BookManage();
                 bm.setId(rs.getInt(1));
                 bm.setName(rs.getString(2));
                 bm.setAuthor(rs.getString(3));
                 bm.setTime(rs.getString(4));
                 bm.setType(rs.getInt(5));
                 list.add(bm);
             }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            baseDao.closeConnection(con,pre,rs);
        }
        return list;
    }

    /**
     * 这是增加书籍方法
     * @param book 传入一个对象
     */
    public void addBook(BookManage book){
        Connection con = baseDao.getConnection();
        String sql="INSERT into bookmanage(b_name,b_auto,b_time,b_type) VALUES(?,?,?,?)";
        PreparedStatement pre=null;
        int b=0;
        try {
             pre = con.prepareStatement(sql);
             pre.setString(1,book.getName());
             pre.setString(2,book.getAuthor());
             pre.setString(3,book.getTime());
             pre.setInt(4,book.getType());
             b = pre.executeUpdate();
            System.out.println(b);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            baseDao.closeConnection(con,pre);
        }
    }

    /**
     * 删除书籍方法 利用b_id 作为标识 删除 因为b_id 是唯一标识
     * @param
     */
    public void delBook(int id){
        Connection con = baseDao.getConnection();
        String sql="DELETE FROM bookmanage WHERE b_id=?";
        PreparedStatement pre=null;
        try {
            pre = con.prepareStatement(sql);
            pre.setInt(1,id);
            int i = pre.executeUpdate();
            System.out.println(i);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            baseDao.closeConnection(con,pre);
        }
    }

}

A、编写查询方法，查询所有图书信息  ----  对应query()方法

B、编写添加方法，新增图书信息    -----     对应add()方法

C、编写删除方法，删除指定的图书信息  ----   对应del()方法

创建 Service 类：BookManageService.java（接口加实现类）

AddBookService 添加书籍 

package com.example.service;

import com.example.dao.BookManageDao;
import com.example.entity.BookManage;

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

public class AddBookService extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setContentType("text/html;charset=UTF-8");
        req.setCharacterEncoding("UTF-8");
        String name = req.getParameter("name").trim();
        String auto = req.getParameter("auto");
        String date = req.getParameter("date");
        int lei = Integer.parseInt(req.getParameter("lei"));
        BookManageDao bookManageDao=new BookManageDao();
        BookManage b=new BookManage(name,auto,date,lei);
        bookManageDao.addBook(b);
        resp.getWriter().print("alert('添加成功')");
        req.getRequestDispatcher("index.jsp").forward(req,resp);
    }

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

删除 书籍 

package com.example.service;

import com.example.dao.BookManageDao;

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

public class BookManageService extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        int id = Integer.parseInt(req.getParameter("id"));
        BookManageDao bookManageDao=new BookManageDao();
        bookManageDao.delBook(id);
        req.getRequestDispatcher("index.jsp").forward(req,resp);
    }

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

创建图书信息列表页面

A、按照图-1 所示，进行页面设计，并使用 CSS 进行美化（标题行背景颜色、超链接样 式和文字、隔行变色

B、正确设置“新增图书信息”超链接，点击该超链接后能够进入到新增图书信息页面6 C、为每一行图书信息，绑定执行“删除”操作的 Ajax 方法，删除成功则删除对应 tr， 删除失败则不作任何操作

 JS代码 

window.onload = function () {
        let aLi = document.getElementsByTagName('tbody');
        let thead = document.querySelector('thead');

        console.log(thead)
        for (let i = 0; i < aLi.length; i++) {
            if (i % 2 == 0) {
                aLi[i].style.background = 'Azure1';
            }
            else {
                aLi[i].style.background = 'Cyan';

            }
        }
        thead.style.background = 'DeepSkyBlue';

        let del =document.querySelectorAll('.del');
        for(let i=0;i<del.length;i++){
            del[i].addEventListener('click',function (){
               // warning()
            })
        }
    }

    function warning(){
        if(confirm('确认删除这本书吗?')==true){
            return true;
        }else {
            return false;
        }
    }

11、创建新增图书信息页面 A、按照图-2 所示，进行页面设计 B、保存数据之前要使用 jQuery 对输入项进行非空验证，对日期进行格式验证，对图书 类别下拉列表框进行选择验证

 

 

 

 

JS代码

 let name=document.getElementById('name');
        let auto=document.getElementById('auto');
        let data=document.getElementById('data');
        let lei = document.getElementById('lei');
        // let sumbit = document.getElementById('sumbit')
        // sumbit.addEventListener('click',function (){
        //     // isEmpty()
        // })
        // console.log(sumbit)

        function isEmpty(){
            let name=document.getElementById('name');
            let auto=document.getElementById('auto');
            let data=document.getElementById('data');
            let lei = document.getElementById('lei');
            if (name.value == '' || name.value == null){
                alert('图书名称不能为空')
                return false;
            }else if(auto.value == '' || auto.value == null){
                alert('图书作者不能为空')
                return false;
            }else if (data.value == '' || data.value == null){
                alert('购买日期不能为空')
                return false;
            }else if (lei.value == '' || lei.value == null){
                alert('请选择图书分类')
                return false;
            }
            document.form1.submit();
            alert('添加图书成功')
        }

 index.jsp文件 

<%@ page import="com.example.dao.BookManageDao" %>
<%@ page import="com.example.entity.BookManage" %>
<%@ page import="java.util.List" %>
<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html>
<head>
    <title>个人图书管理</title>
</head>
<style>
    a {
        text-decoration: none;
    }
</style>

<body>
    <div style="text-align: center">
        <h2 style="font-weight: 400">图书信息</h2>
        <table border="1px" width="500" align="center" style="margin-top: 40px">
            <thead>
                <th>图书名称</th>
                <th>图书作者</th>
                <th>购买时间</th>
                <th>图书分类</th>
                <th>操作</th>
            </thead>
            <%
                BookManageDao bm=new BookManageDao();
                List<BookManage> list = bm.queryBook();
                for (int i=0;i<list.size();i++){
            %>
            <tbody>
                <td><%=list.get(i).getName()%></td>
                <td><%=list.get(i).getAuthor()%></td>
                <td><%=list.get(i).getTime()%></td>
                <td><%int abc=list.get(i).getType();
                    if(abc == 1){%>
                        <%="计算机/软件"%>
                    <%}else if(abc == 2){%>
                        <%="小说/文摘"%>
                    <%}else{%>
                        <%="杂项"%>
                    <%}
                %>
                </td>
                <td><a href="bookManageService?id=<%=list.get(i).getId()%>" onclick="javascript:return warning()" class="del">删除</a></td>
            </tbody>
            <%
                }
            %>
        </table>
        <a href="addbook.jsp" style="color: red;font-size: 20px;font-weight: 700;margin-right: 400px">新增图书信息</a>
    </div>
</body>
<script>
    window.onload = function () {
        let aLi = document.getElementsByTagName('tbody');
        let thead = document.querySelector('thead');

        console.log(thead)
        for (let i = 0; i < aLi.length; i++) {
            if (i % 2 == 0) {
                aLi[i].style.background = 'Azure1';
            }
            else {
                aLi[i].style.background = 'Cyan';

            }
        }
        thead.style.background = 'DeepSkyBlue';

        let del =document.querySelectorAll('.del');
        for(let i=0;i<del.length;i++){
            del[i].addEventListener('click',function (){
               // warning()
            })
        }
    }

    function warning(){
        if(confirm('确认删除这本书吗?')==true){
            return true;
        }else {
            return false;
        }
    }
</script>
</html>

addbook.jsp

<%--
  Created by IntelliJ IDEA.
  User: 86177
  Date: 2021/5/10
  Time: 9:44
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>新增图书信息</title>
    <script>
        let name=document.getElementById('name');
        let auto=document.getElementById('auto');
        let data=document.getElementById('data');
        let lei = document.getElementById('lei');
        // let sumbit = document.getElementById('sumbit')
        // sumbit.addEventListener('click',function (){
        //     // isEmpty()
        // })
        // console.log(sumbit)

        function isEmpty(){
            let name=document.getElementById('name');
            let auto=document.getElementById('auto');
            let data=document.getElementById('data');
            let lei = document.getElementById('lei');
            if (name.value == '' || name.value == null){
                alert('图书名称不能为空')
                return false;
            }else if(auto.value == '' || auto.value == null){
                alert('图书作者不能为空')
                return false;
            }else if (data.value == '' || data.value == null){
                alert('购买日期不能为空')
                return false;
            }else if (lei.value == '' || lei.value == null){
                alert('请选择图书分类')
                return false;
            }
            document.form1.submit();
            alert('添加图书成功')
        }

    </script>
</head>
<body>
    <div style="text-align: center">
        <h2 style="font-weight: 400">新增图书信息</h2>
        <form action="addBookService" method="post" id="form1" name="form1">
            图书名称:<input type="text" name="name" id="name" value=""><br>
            图书作者: <input type="text" name="auto" id="auto" value=""><br>
            购买日期: <input type="text" name="date" id="data" value=""><br>
            图书类别: <select name="lei" id="lei">
            <option value="1">计算机/软件</option>
            <option value="2">小说/文摘</option>
            <option value="3">杂项</option>
            <option value="" selected>选择所属分类</option>
        </select> <br>
           <span style="margin-top: 10px">注:购买日期格式必须为:yyyy-MM-dd</span><br>
            <input type="button" value="增加图书" style="margin-top: 20px" id="sumbit" onclick="isEmpty()">
        </form>
    </div>
</body>

</html>

web.xml配置

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
         version="4.0">


    <servlet>
        <servlet-name>BookManageService</servlet-name>
        <servlet-class>com.example.service.BookManageService</servlet-class>
    </servlet>

    <servlet-mapping>
        <servlet-name>BookManageService</servlet-name>
        <url-pattern>/bookManageService</url-pattern>
    </servlet-mapping>

    <servlet>
        <servlet-name>AddBookService</servlet-name>
        <servlet-class>com.example.service.AddBookService</servlet-class>
    </servlet>

    <servlet-mapping>
        <servlet-name>AddBookService</servlet-name>
        <url-pattern>/addBookService</url-pattern>
    </servlet-mapping>

</web-app>