Java Web下MySQL数据库的增删改查(一)

以图书管理系统举例（jsp+servlet+bean）

1.数据库的连接

package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBUtil {
    private static String url = "jdbc:mysql://localhost:3306/book system?&useSSL=false&serverTimezone=UTC";//要连接的库名
    private static String user = "******";//数据库用户名
    private static String password = "******";//数据库密码
    private static String jdbcName="com.mysql.cj.jdbc.Driver";
    private Connection con=null;
    public static  Connection getConnection() {
        Connection con=null;
        try {
            Class.forName(jdbcName);
            con=DriverManager.getConnection(url, user, password);
            //System.out.println("数据库连接成功");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            //System.out.println("数据库连接失败");
            e.printStackTrace();
        }
        try {
            con = DriverManager.getConnection(url,user,password);
            System.out.println("连接成功");


        } catch (SQLException e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return con;
    }
    public static void main(String[] args)throws SQLException { //测试数据表的连接
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql ="select * from reader_information";
        pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();
        System.out.println(getConnection());
        while(rs.next()){
            System.out.println("成功");
        }

        }

       // return con;
        
    
    public static void close(Connection con) {
        if(con!=null)
            try {
                con.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        
    }
    public static void close(Statement state, Connection conn) {
        if(state!=null) {
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    public static void close(ResultSet rs, Statement state, Connection conn) {
        if(rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(state!=null) {
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

2.实体层

package bean;

public class Bean_book {
    private int id;
    private String name;
    private String writer;
    private String press;
    private int num;

    
    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 getWriter() {
        return writer;
    }
    public void setWriter(String writer) {
        this.writer = writer;
    }

    public String getPress() {
        return press;
    }
    public void setPress(String press) {
        this.press = press;
    }
    
    public int getNum() {
        return num;
    }
    public void setNum(int num) {
        this.num = num;
    }
        
    public Bean_book(int id, String name, String writer,  String press,int num) {
        this.id = id;
        this.name = name;
        this.writer = writer;
        this.press = press;
        this.num = num;
    }
    
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", writer='" + writer + '\'' +
                ", press='" + press + '\'' +
                ", num=" + num +
                '}';
    }
}

package bean;

import java.util.Date;

public class Bean_borrowing {
    private int id;
    private String name;
    private String writer;
    private String press;
    private Date time;



    
    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 getWriter() {
        return writer;
    }
    public void setWriter(String writer) {
        this.writer = writer;
    }

    public String getPress() {
        return press;
    }
    public void setPress(String press) {
        this.press = press;
    }
    
    public Date getTime() {
        return time;
    }
    public void setTime(Date time) {
        this.time = time;
    }
    
    public Bean_borrowing(int id, String name, String writer,  String press,Date time) {
        this.id = id;
        this.name = name;
        this.writer = writer;
        this.press = press;
        this.time = time;
    }
    
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", writer='" + writer + '\'' +
                ", press='" + press + '\'' +
                ", time=" + time +
                '}';
    }
}

package bean;

public class Bean_reader {
    
    private int uid;
    private String name;
    private String sex;
    private String college;
    private String password;

    
    public int getUid() {
        return uid;
    }
    public void setUid(int uid) {
        this.uid = uid;
    }
    
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getCollege() {
        return college;
    }
    public void setCollege(String college) {
        this.college = college;
    }
    
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    
    public Bean_reader(int uid, String name, String sex,  String college,String password) {
        this.uid = uid;
        this.name = name;
        this.sex = sex;
        this.college = college;
        this.password = password;
    }
    
    public Bean_reader(int uid,String password) {
        this.uid = uid;
        this.password = password;
    }
    
    
    
    public String toString() {
        return "Reader{" +
                "uid=" + uid +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", college='" + college + '\'' +
                ", password=" + password +
                '}';
    }
}

3.Dao层数据访问，实现对数据的增、删、改、查

package dao;

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

import bean.Bean_reader;
import bean.Bean_book;
import bean.Bean_borrowing;
import db.DBUtil;

public class Dao {
    //dao层
    private DBUtil dbutil=new DBUtil();

    
public Dao() {
    // TODO Auto-generated constructor stub
}
public boolean insert_reader(Bean_reader bean) {//插入读者数据的方法
    boolean f=false;
    String sql="insert into reader_information(uid,name,sex,college,password) values('"+bean.getUid()+"','"+bean.getName()+"','"+bean.getSex()+"','"+bean.getCollege()+"'+'"+bean.getPassword()+"')";
    Connection conn=DBUtil.getConnection();//数据库连接，加载驱动
    Statement state=null;
    try
    {
        state=conn.createStatement();//实例化Statement对象,方便对sql语句进行操作
        System.out.println(conn);
        state.executeUpdate(sql);
        f=true;
        //执行数据库更新操作用于执行INSERT、UPDATE或DELETE语句以及SQLDDL（数据定义语言）语句，
        //例如CREATETABLE和DROPTABLE,（创建表和删除表）
    }catch(Exception e)//当try语句中s出现异常时，会执行catch中的语句
      {
        e.printStackTrace();//捕获异常的语句
      }
     finally //finally作为异常处理的一部分，它只能用在try/catch语句中，并且附带一个语句块，表示这段语句最终一定会被执行（不管有没有抛出异常），经常被用在需要释放资源的情况下。
     {
         DBUtil.close(conn);
     }
    return f;
}

public boolean insert_book(Bean_book bean) {//插入图书数据的方法
    boolean f=false;
    String sql="insert into book_information(id,name,writer,press,num) values('"+bean.getId()+"','"+bean.getName()+"','"+bean.getWriter()+"','"+bean.getPress()+"','"+bean.getNum()+"')";
    Connection conn=DBUtil.getConnection();//数据库连接，加载驱动
    Statement state=null;
    try
    {
        state=conn.createStatement();//实例化Statement对象,方便对sql语句进行操作
        System.out.println(conn);
        state.executeUpdate(sql);
        f=true;
        //执行数据库更新操作用于执行INSERT、UPDATE或DELETE语句以及SQLDDL（数据定义语言）语句，
        //例如CREATETABLE和DROPTABLE,（创建表和删除表）
    }catch(Exception e)//当try语句中s出现异常时，会执行catch中的语句
      {
        e.printStackTrace();//捕获异常的语句
      }
     finally //finally作为异常处理的一部分，它只能用在try/catch语句中，并且附带一个语句块，表示这段语句最终一定会被执行（不管有没有抛出异常），经常被用在需要释放资源的情况下。
     {
         DBUtil.close(conn);
     }
    return f;
}

public boolean insert_borrowing(Bean_borrowing bean) {//插入借阅图书数据的方法
    boolean f=false;
    String sql="insert into borrowing_information(id,name,writer,press,sysSysDate) values('"+bean.getId()+"','"+bean.getName()+"','"+bean.getWriter()+"','"+bean.getPress()+"','"+bean.getTime()+"')";
    Connection conn=DBUtil.getConnection();//数据库连接，加载驱动
    Statement state=null;
    try
    {
        state=conn.createStatement();//实例化Statement对象,方便对sql语句进行操作
        System.out.println(conn);
        state.executeUpdate(sql);
        f=true;
        //执行数据库更新操作用于执行INSERT、UPDATE或DELETE语句以及SQLDDL（数据定义语言）语句，
        //例如CREATETABLE和DROPTABLE,（创建表和删除表）
    }catch(Exception e)//当try语句中s出现异常时，会执行catch中的语句
      {
        e.printStackTrace();//捕获异常的语句
      }
     finally //finally作为异常处理的一部分，它只能用在try/catch语句中，并且附带一个语句块，表示这段语句最终一定会被执行（不管有没有抛出异常），经常被用在需要释放资源的情况下。
     {
         DBUtil.close(conn);
     }
    return f;
}



public List<Bean_book> list(){//查询所有方法
    String sql="select * from book_information order by id ASC";
    Connection conn=DBUtil.getConnection();
    Statement st=null;
    List<Bean_book> list=new ArrayList<>();
    ResultSet rs=null;
    Bean_book bean=null;
    try {
        st=conn.createStatement();
        st.executeQuery(sql);
        rs=st.executeQuery(sql);
        while(rs.next()) {
            int id=rs.getInt("id");
            String name = rs.getString("name");
            String writer = rs.getString("writer");
            String press = rs.getString("press");
            int num=rs.getInt("num");
            bean=new Bean_book(id,name,writer,press,num);
            list.add(bean);
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    finally {
        DBUtil.close(rs, st, conn);
    }
    return list;
}


public List<Bean_book> searchByName(String str) throws SQLException{//查询条件方法
    String sql="select * from book_information where(name like '%"+str+"%')";
    Connection conn=DBUtil.getConnection();
    Statement st=null;
    PreparedStatement pt = conn.prepareStatement(sql);
    List<Bean_book> search=new ArrayList<>();
    ResultSet rs=null;
    Bean_book bean=null;
    try {
      pt=conn.prepareStatement(sql);
        rs=pt.executeQuery();
        while(rs.next()) {
            int id=rs.getInt("id");
            String name = rs.getString("name");
            String writer = rs.getString("writer");
            String press = rs.getString("press");
            int num=rs.getInt("num");
            bean=new Bean_book(id,name,writer,press,num);
            search.add(bean);
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    finally {
        DBUtil.close(rs, st, conn);
    }
    return search;
}

public List<Bean_book> searchByWriter(String str) throws SQLException{//查询条件方法
    String sql="select * from book_information where(writer like '%"+str+"%')";
    Connection conn=DBUtil.getConnection();
    Statement st=null;
    PreparedStatement pt = conn.prepareStatement(sql);
    List<Bean_book> search=new ArrayList<>();
    ResultSet rs=null;
    Bean_book bean=null;
    try {
      pt=conn.prepareStatement(sql);
        rs=pt.executeQuery();
        while(rs.next()) {
            int id=rs.getInt("id");
            String name = rs.getString("name");
            String writer = rs.getString("writer");
            String press=rs.getString("press");
            int num=rs.getInt("num");
            bean=new Bean_book(id,name,writer,press,num);
            search.add(bean);
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    finally {
        DBUtil.close(rs, st, conn);
    }
    return search;
}

public boolean update(Bean_book bean) {//更新自减方法
    String sql="update book_information set num='"+bean.getNum()+"',name='"+bean.getName()+"',writer='"+bean.getWriter()+"',press='"+bean.getPress()+"'where id='"+bean.getId()+"'";
    Connection conn=DBUtil.getConnection();
    boolean f=false;
    Statement st=null;
    try {
        st=conn.createStatement();
        st.executeUpdate(sql);
        f=true;
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return f;
}

public boolean delete_book(int id ) {//删除方法
    String sql="delete from book_information where id='"+id+"'";
    boolean f=false;
    Connection conn =DBUtil.getConnection();
    Statement st=null;
    try {
        st=conn.createStatement();
        st.executeUpdate(sql);
        f=true;
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    finally{
        DBUtil.close(st, conn);
    }
    return f;
}

public List<Bean_reader> find(){//查询
    String sql="select * from reader_information";
    Connection conn=DBUtil.getConnection();
    Statement st=null;
    List<Bean_reader> find=new ArrayList<>();
    ResultSet rs=null;
    Bean_reader bean=null;
    try {
        st=conn.createStatement();
        st.executeQuery(sql);
        rs=st.executeQuery(sql);
        while(rs.next()) {
            int uid=rs.getInt("uid");
            String password = rs.getString("password");
            bean=new Bean_reader(uid,password);
            find.add(bean);
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    finally {
        DBUtil.close(rs, st, conn);
    }
    return find;
}

}

4.servlet

 

package servlet;

import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


import java.util.Date; 
import java.util.Calendar;
import java.text.ParseException;
import java.text.SimpleDateFormat; 

import bean.Bean_reader;
import bean.Bean_book;
import bean.Bean_borrowing;
import dao.Dao;

/**
 * Servlet implementation class servlet
 */
@WebServlet("/servlet")
public class servlet extends HttpServlet {
    Dao dao = new Dao();
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public servlet() {
        super();
        // TODO Auto-generated constructor stub
    }
    
    private void insert_reader(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {//增
        // TODO Auto-generated method stub
        request.setCharacterEncoding("utf-8");
        int uid = Integer.parseInt(request.getParameter("uid"));
        String name = request.getParameter("name");
        String sex = request.getParameter("sex");
        String college= request.getParameter("college");
        String password= request.getParameter("password");
        Bean_reader bean=new Bean_reader(uid,name,sex,college,password);
 
        if(dao.insert_reader(bean)) {
            request.setAttribute("message", "添加成功");
            request.getRequestDispatcher("add reader.jsp").forward(request, response);
        }
    }
    
    private void insert_book(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {//增
        // TODO Auto-generated method stub
        request.setCharacterEncoding("utf-8");
        int id = Integer.parseInt(request.getParameter("id"));
        String name = request.getParameter("name");
        String writer = request.getParameter("writer");
        String press= request.getParameter("press");
        int num = Integer.parseInt(request.getParameter("num"));
        Bean_book bean=new Bean_book(id,name,writer,press,num);
 
        if(dao.insert_book(bean)) {
            request.setAttribute("message", "添加成功");
            request.getRequestDispatcher("add book.jsp").forward(request, response);
        }
    }
    
    /*private void insert_borrowing(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {//增
        // TODO Auto-generated method stub
        request.setCharacterEncoding("utf-8");
        int id = Integer.parseInt(request.getParameter("id"));
        String name = request.getParameter("name");
        String writer = request.getParameter("writer");
        String press= request.getParameter("press");
        String time= request.getParameter("sysSysDate");
        int num= Integer.parseInt(request.getParameter("num"));
        num=num-1;
        Bean_borrowing bean=new Bean_borrowing(id,name,writer,press,time);
        Bean_book       bean1=new Bean_book(id,name,writer,press,num);
 
        if(dao.insert_borrowing(bean)&&dao.update(bean1)) {
            request.setAttribute("message", "订阅成功");
            request.getRequestDispatcher("list book.jsp").forward(request, response);
        }
    }*/
    
    private void list(HttpServletRequest request, HttpServletResponse response) throws Exception {
        // TODO Auto-generated method stub
        request.setCharacterEncoding("utf-8");
        List<Bean_book> list = dao.list();
        request.setAttribute("list", list);
        request.getRequestDispatcher("list book.jsp").forward(request,response);
    }
    
    private void list_admin(HttpServletRequest request, HttpServletResponse response) throws Exception {
        // TODO Auto-generated method stub
        request.setCharacterEncoding("utf-8");
        List<Bean_book> list_admin = dao.list();
        request.setAttribute("list", list_admin);
        request.getRequestDispatcher("delete book.jsp").forward(request,response);
    }
    
    private void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {//改
        // TODO Auto-generated method stub
        request.setCharacterEncoding("utf-8");
        int id = Integer.parseInt(request.getParameter("id"));
        String name = request.getParameter("name");
        String writer = request.getParameter("writer");
        String press = request.getParameter("press");
        int num= Integer.parseInt(request.getParameter("num"));
        num=num-1;

        Bean_book bean=new Bean_book(id,name,writer,press,num);
        dao.update(bean);      
        request.setAttribute("message", "订阅成功");
        request.getRequestDispatcher("servlet?method=list").forward(request, response);
    }
    
    private void delete_book(HttpServletRequest request, HttpServletResponse response) throws Exception, IOException {//删
        // TODO Auto-generated method stub
        request.setCharacterEncoding("UTF-8");
        int id=Integer.parseInt(request.getParameter("id"));
        dao.delete_book(id); //进行数据库的删除操作
        request.setAttribute("message", "删除成功");
        request.getRequestDispatcher("servlet?method=list_admin").forward(request, response);
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        request.setCharacterEncoding("utf-8");
        String method=request.getParameter("method");
         if("insert_reader".equals(method)) {
             insert_reader(request,response);           
            }
         else if("insert_book".equals(method)) {
             insert_book(request,response);
         }
         /*else if("insert_borrowing".equals(method)) {
             insert_borrowing(request,response);
         }*/
         else if("list".equals(method)) {
                try {
                    list(request,response);
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
         }
         else if("list_admin".equals(method)) {
                try {
                    list_admin(request,response);
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
         }
         else if("update".equals(method)) {
                update(request,response);
            }
         else if("delete_book".equals(method)) {
                try {
                    delete_book(request,response);
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }                
            }
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}

条件查询

package servlet;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import bean.Bean_book;
import dao.Dao;

/**
 * Servlet implementation class searchServlet_admin
 */
@WebServlet("/searchServlet_admin")
public class searchServlet_admin extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public searchServlet_admin() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //response.getWriter().append("Served at: ").append(request.getContextPath());
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        String cxfs=request.getParameter("cxfs");
        System.out.print(cxfs);
        
        String str=request.getParameter("value");
         Dao dao=new Dao();
         List<Bean_book> list = null;
         
         try {
             if("1".equals(cxfs)){
                     list=dao.searchByName(str);
                 } 
             if("2".equals(cxfs)){
                     list=dao.searchByWriter(str);
                 } 
             } catch (SQLException e) {
                 // TODO 自动生成的 catch 块
                 e.printStackTrace();
             }
         request.setAttribute("list", list);
         request.getRequestDispatcher("delete book.jsp").forward(request,response);
         System.out.print(list.size());
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}

package servlet;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import bean.Bean_book;
import dao.Dao;

/**
 * Servlet implementation class searchServlet
 */
@WebServlet("/searchServlet")
public class searchServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public searchServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        //response.getWriter().append("Served at: ").append(request.getContextPath());
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        String cxfs=request.getParameter("cxfs");
        System.out.print(cxfs);
        
        String str=request.getParameter("value");
         Dao dao=new Dao();
         List<Bean_book> list = null;
         
         try {
             if("1".equals(cxfs)){
                     list=dao.searchByName(str);
                 } 
             if("2".equals(cxfs)){
                     list=dao.searchByWriter(str);
                 } 
             } catch (SQLException e) {
                 // TODO 自动生成的 catch 块
                 e.printStackTrace();
             }
         request.setAttribute("list", list);
         request.getRequestDispatcher("list book.jsp").forward(request,response);
         System.out.print(list.size());
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}

5.JSP页面

<%@page import="java.sql.PreparedStatement"%>
    <%@ page import="db.DBUtil"%> 
    <%@ page import="java.sql.SQLException"%>
    <%@ page import="java.sql.Connection"%>
    <%@ page import="java.sql.ResultSet"%>
    <%@ page import="java.sql.PreparedStatement"%>
    <%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>登录</title>
</head>
<body>


      <table id="addTable" class="table table-bordered  ">                 
      
       <tr class="text-center row">
                <td class="col-sm-2">
                    学号/工号
                </td>
                <td class="col-sm-4">
                       <input type="text" class="form-control"  name="id" id="id" >
                </td>
       </tr>           
                  
       <tr class="text-center row">
                <td class="col-sm-2">
                      密码
                </td>
                <td class="col-sm-4">
                  <input type="password" class="form-control"  name="password" id="password">
                </td>
       </tr>


       <tr class="text-center row">
                <td>
                  登陆身份
                </td>
                <td colspan="3">
                  <select class="form-control" id="identity" name="identity">
                    <option value="读者">读者</option>
                    <option value="管理员">管理员</option>
                  </select>
                </td>
       </tr>
       </table>
            <input type="submit" value="登录" onclick="return next()">

     
<script type="text/javascript">
function next(){
    var id=document.getElementById('id').value;
    var password=document.getElementById('password').value;

    
    

    
    if(document.getElementById('identity').value=="读者"){
        if(document.getElementById('id').value==''){
            alert('学号/工号不能为空！');
            document.getElementById('id').focus();
            return false;
        }
        else if(document.getElementById('id').value.length!=8){
            alert('学号/工号位数错误，请输入8位正确数字！');
            document.getElementById('id').focus();
            return false;
        }
        if(document.getElementById('password').value==''){
            alert('密码不能为空！');
            document.getElementById('password').focus();
            return false;
        }
        window.location.href="reader_index.jsp?id="+id;
    }
    if(document.getElementById('identity').value=="管理员"){
        if(document.getElementById('id').value==''){
            alert('学号/工号不能为空！');
            document.getElementById('id').focus();
            return false;
        }
        else if(document.getElementById('id').value.length!=8){
            alert('学号/工号位数错误，请输入8位正确数字！');
            document.getElementById('id').focus();
            return false;
        }
        if(document.getElementById('password').value==''){
            alert('密码不能为空！');
            document.getElementById('password').focus();
            return false;
        }
        window.location.href="admin_index.jsp?id="+id;
    }
}
</script>


</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>管理员主页</title>
</head>
    <script type="text/javascript">
                 let id = location.search.split("=")[1];
              alert("工号"+id+"你好！"); //弹出对话框
    </script>
<body>
   <div align="center" font-size="30px">
        <h1>管理员</h1>
        <div>
            <a  href="add reader.jsp">添加读者信息</a>
        </div>
        <div>
            <a href="add book.jsp">添加新书信息</a>
        </div>
        <div>
            <a href="servlet?method=list_admin">删除书目信息</a>
        </div>
        <div>
            <a href="servlet?method=list">打印催还书目</a>
        </div>
    </div>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>读者功能页</title>
</head>
    <script type="text/javascript">
                 let id = location.search.split("=")[1];
              alert("欢迎您！"+"读者"+id); //弹出对话框
    </script>
<body>
    <div align="center" font-size="30px">
        <h1>读者</h1>
        <div>
            <a  href="servlet?method=list">浏览图书信息</a>
        </div>
        <div>
            <a href="servlet?method=list">查询图书信息</a>
        </div>
        <div>
            <a href="servlet?method=list">借阅图书</a>
        </div>
        <div>
            <a href="servlet?method=list">浏览催还书目</a>
        </div>
        <div>
            <a href="servlet?method=list">归还图书</a>
        </div>
    </div>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加新书信息</title>
</head>
<body>
<%
        Object message = request.getAttribute("message");
        if (message != null && !"".equals(message)) {
    %>
    <script type="text/javascript">
              alert("<%=request.getAttribute("message")%>"); //弹出对话框
    </script>
    <%
        }
    %>
<form action="servlet?method=insert_book" method="post">

      <table id="addTable" class="table table-bordered  ">
      
      <tr class="text-center row">
                <td class="col-sm-2">
                图书编号
                </td>
                <td class="col-sm-4">
                    <input type="text" class="form-control"  name="id" id="id" >
                </td>
       </tr>         
                  
      
       <tr class="text-center row">
                <td class="col-sm-2">
                书名
                </td>
                <td class="col-sm-4">
                       <input type="text" class="form-control"  name="name" id="name" >
                </td>
       </tr>           
                  

       <tr class="text-center row">
                <td class="col-sm-2">
                作者名
                </td>
                <td class="col-sm-4">
                       <input type="text" class="form-control"  name="writer" id="writer" >
                </td>
       </tr>
       
       <tr class="text-center row">
                <td class="col-sm-2">
                出版社名
                </td>
                <td class="col-sm-4">
                       <input type="text" class="form-control"  name="press" id="press" >
                </td>
       </tr>
       
       <tr class="text-center row">
                <td class="col-sm-2">
                可借阅数量
                </td>
                <td class="col-sm-4">
                       <input type="text" class="form-control"  name="num" id="num" >
                </td>
       </tr> 
       
             
       
       </table>
            <input type="submit" value="添加" onclick="check()">         
</form> 
</body>
<script type="text/javascript">
        function check() //封装一个<body>中做成点击事件的函数
        {
            if(document.getElementById('id').value=='') {
                  alert('图书编号不能为空！');
                  document.getElementById('id').focus();
                  return false;
            }
            if(document.getElementById('name').value==''){
                  alert('书名不能为空！');
                    document.getElementById('name').focus();
                    return false;
            }
            if(document.getElementById('writer').value=='') {
                      alert('作者名不能为空！');
                      document.getElementById('writer').focus();
                      return false;
               }
            else if(document.getElementById('press').value==''){
                  alert('出版社名不能为空！');
                  document.getElementById('press').focus();
                  return false;
            }
            if(document.getElementById('num').value=='') {
                      alert('借阅数量不能为空！');
                      document.getElementById('num').focus();
                      return false;
               }
        }
        
    </script>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加读者信息</title>
</head>
<body>
<%
        Object message = request.getAttribute("message");
        if (message != null && !"".equals(message)) {
    %>
    <script type="text/javascript">
              alert("<%=request.getAttribute("message")%>"); //弹出对话框
    </script>
    <%
        }
    %>

<form action="servlet?method=insert_reader" method="post">

      <table id="addTable" class="table table-bordered  ">
      
      <tr class="text-center row">
                <td class="col-sm-2">
                学号
                </td>
                <td class="col-sm-4">
                    <input type="text" class="form-control"  name="uid" id="uid" >
                </td>
       </tr>         
                  
      
       <tr class="text-center row">
                <td class="col-sm-2">
                姓名
                </td>
                <td class="col-sm-4">
                       <input type="text" class="form-control"  name="name" id="name" >
                </td>
       </tr>
       
       <tr class="text-center row">
                <td class="col-sm-2">
                密码
                </td>
                <td class="col-sm-4">
                    <input type="text" class="form-control"  name="password" id="password" >
                </td>
       </tr>           
                  
              <tr>
                <td class="col-sm-2">
                  性别
                </td>
                <td class="col-sm-4">
                          <input type="radio"  name="sex" id="sex" value="男">男
                          <input type="radio"  name="sex" id="sex" value="女">女
                </td>
              </tr>


       <tr class="text-center row">
                <td class="col-sm-2">
                所在学院
                </td>
                <td class="col-sm-4">
                       <input type="text" class="form-control"  name="college" id="college" >
                </td>
       </tr> 
       
             
       
       </table>
            <input type="submit" value="添加" onclick="check()">         
</form> 
</body>
<script type="text/javascript">
        function check() //封装一个<body>中做成点击事件的函数
        {
            if(document.getElementById('id').value=='') {
                  alert('学号不能为空！');
                  document.getElementById('id').focus();
                  return false;
            }
            if(document.getElementById('name').value==''){
                  alert('姓名不能为空！');
                    document.getElementById('name').focus();
                    return false;
            }
            if(document.getElementById('college').value==''){
                  alert('所在学院不能为空！');
                  document.getElementById('college').focus();
                  return false;
            }
        }
        
    </script>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
               <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>删除</title>
</head>
<body>
<%
        Object message = request.getAttribute("message");
        if (message != null && !"".equals(message)) {
    %>
    <script type="text/javascript">
              alert("<%=request.getAttribute("message")%>"); //弹出对话框
    </script>
    <%
        }
    %>
    <div align="center">
        <h1 >信息列表</h1>
          <h1> 
        <form action="searchServlet_admin" method="post">
        <select name="cxfs">
  <option  id="cxfs"value ="1">书名</option>
  <option  id="cxfs" value ="2">作者名</option>
</select>
            <input type="text" id="value" name="value" placeholder="请输入条件">
            <input type="submit" id="select" name="select" value="查询" />
      </form>
        
        </h1>
        <table >
            <tr>
                <td>图书编号</td>
                <td>书名</td>
                <td>作者名</td>
                <td>出版社名称</td>
                <td>可借阅数量</td>
                <td align="center" colspan="2">操作</td>
            </tr>
            <c:forEach items="${list}" var="item">
                <tr>
                    <td>${item.id}</td>
                    <td>${item.name}</td>
                    <td>${item.writer}</td>
                    <td>${item.press}</td>
                    <td>${item.num}</td>
                    <td><a href="servlet?method=delete_book&id=${item.id}">删除</a></td>
                </tr>
            </c:forEach>
        </table>
    </div>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
           <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>查询</title>
</head>
<body>
<%
        Object message = request.getAttribute("message");
        if (message != null && !"".equals(message)) {
    %>
    <script type="text/javascript">
              alert("<%=request.getAttribute("message")%>"); //弹出对话框
    </script>
    <%
        }
    %>
    <div align="center">
        <h1 >信息列表</h1>
        <h3>当前时间：<input id="sysDate" style="font-size:18.72px;color:red;width: 320px;text-align: center;"></h3>
          <h1> 
        <form action="searchServlet" method="post">
        <select name="cxfs">
  <option  id="cxfs"value ="1">书名</option>
  <option  id="cxfs" value ="2">作者名</option>
</select>
            <input type="text" id="value" name="value" placeholder="请输入条件">
            <input type="submit" id="select" name="select" value="查询" />
      </form>
        
        </h1>
        <table >
            <tr>
                <td>图书编号</td>
                <td>书名</td>
                <td>作者名</td>
                <td>出版社名称</td>
                <td>可借阅数量</td>
                <td align="center" colspan="2">操作</td>
            </tr>
            <c:forEach items="${list}" var="item">
                <tr>
                    <td>${item.id}</td>
                    <td>${item.name}</td>
                    <td>${item.writer}</td>
                    <td>${item.press}</td>
                    <td>${item.num}</td>
                    <td><a href="servlet?method=update&id=${item.id}&name=${item.name}&writer=${item.writer}&press=${item.press}&num=${item.num}" 
                    onclick="next()">订阅</a></td>
                </tr>
            </c:forEach>
        </table>
    </div>
</body>

<script type="text/javascript">
        /*---------- 动态获取系统当前日期方法start ------*/
        setInterval(
                "document.getElementById('sysDate').value=new Date().toLocaleString()+'  星期'+'日一二三四五六'.charAt(new Date().getDay());",
                1000);
        setInterval(
                "document.getElementById('sysSysDate').value=new Date().toLocaleString();",
                1000);
 
        /*---------- 动态获取系统当前日期方法end ------*/
        
        var d = new Date();
        d.setMonth(d.getMonth()+3);
        d=d.toLocaleString();


        function next(){
            alert('订阅成功！还书日期为'+d);
        }
</script>
</html>

6.总结

1.与图书借阅相关的功能例如记录借阅时间还没有实现，待完善

2.没有实现登录的验证，只有简单的跳转，仍需要学习

3.关于参数传递不了解

4.实现了简单的MySQL增、删、改、查

5.存在冗余还没有修改

6.仍需要继续改进

7.数据库后台建表

用户信息表的密码暂时无用，还没有实现登陆验证

 

 

后文：https://www.cnblogs.com/Arisf/p/14141243.html