My_DBMS_BMS

[TOC]

一, 前言

  我不会前后端分离的springboot, 也不会什么jsp, 我考虑之后, 选择了java原生窗口, awt/swing + JDBC + mysql.

“识迷途之未远, 觉今是而昨非”

改别人的终究觉得心里不踏实, 自己写一个就完事了, 就算他比较low.

Table

最初版在老师的建议下, 再次进行了较大调整, 新增加了2个表格, 现共有5个表.

冗余度较低, 查书查询用到了双表连接.

1. 正在借阅借书表(borrowing) : (BID, ISBN, ID, BTime)
2. 历史借阅结束表(borrowed) : (Bdate, Bid, ID, ISBN)
3. 按种类分类图书表(book) : (ISBN, Bname, Bpublish, Bauthor, Bpress, Bnumber, Kind)
4. 按单本分类图书表(everybook) : (ISBN, Bname, Bnumber, BID, ZT)
5. 用户表(users) : (ID, Upasswords1, Uname, Uage, Usex, Umarjor, Uphone, Upassword2)

E-R图

二, 独立完成(打分项1)

100%独立完成, 学习技术过程中, 就算不会要学, 也一定手动敲一遍, 决不复制黏贴, 熟悉技术.

三, 可运行(打分项2)

没啥好说的, 主要界面与功能见图片.

登录页:

管理员:

用户:

建议满分

四, 插入, 删除, 修改(打分项3)

因为代码数量过多, 仅展示图书插入, 其他略写. 全部代码可去github获取

1. 正在借阅借书表(borrowing): 插入, 删除
2. 历史借阅结束表(borrowed): 插入
3. 按种类分类图书表(book): 插入, 删除, 修改
4. 按单本分类图书表(everybook): 插入, 删除, 修改
5. 用户表(users): 插入, 删除, 修改.

Book_Add.class:

package P;

import java.awt.print.Book;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

/*
insert 语句直接 添加
 */

public class Book_Add {
    private static Connection conn = null;
    private static PreparedStatement ps = null;
    private static ResultSet rs = null;
    private static final CallableStatement cs = null;
    
    public static void Insert(Boook stu) throws SQLException, ClassNotFoundException {

        conn = Conn.conn();
        String sql = "INSERT INTO Book (ISBN, Bname, Bpublish, Bauthor, Bpress, Bnumber, Kind) VALUES(?,?,?,?,?,?,?)";		//插入sql语句
        try {
            ps = conn.prepareStatement(sql);
            /*
            ISBN string;
            Bname string;
            Bpublish int;
            Bauthor string;
            Bpress string;
            Bnumber int;
            Kind string;
             */
            ps.setString(1, stu.getISBN());
            ps.setString(2, stu.getBname());
            ps.setInt(3, stu.getBpublish());
            ps.setString(4, stu.getBauthor());
            ps.setString(5, stu.getBpress());
            ps.setInt(6, stu.getBnumber());
            ps.setString(7, stu.getKind());

            ps.executeUpdate();			//执行sql语句

            System.out.println("插入成功");

            String sql2 = "INSERT INTO everybook (ISBN, Bname, Bnumber) VALUES(?,?,?)";
            ps = conn.prepareStatement(sql2);
            ps.setString(1, stu.getISBN());
            ps.setString(2, stu.getBname());
            ps.setInt(3,stu.getBnumber());

            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Conn.close();
        }
    }

}

Add_Books:

package P;

import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;

public class Add_Books extends JFrame {
    private JLabel ab1, ab2, ab3, ab4, ab0;
    private JLabel ab5, ab6, ab7, ab8;
    private JTextField test1, test2, test3, test4;
    private JTextField test5, test6, test7, test8;
    private JButton bu1, bu2, bu3, bu4, bu5, bu6, bu7;
    private int ID, Uage;
    private String Upassword1, Uname, Usex, Umarjor, Uphone, Upassword2;
    int cout;
    public Add_Books(){
        setLocationRelativeTo(Windows_Login.frame);
        setTitle("添加图书");
        setSize(500, 580);
        setLocation(200, 200);
        setVisible(true);
        //setDefaultCloseOperation(this.EXIT_ON_CLOSE);
        setLayout(null);

        ab0 = new JLabel("添加图书");
        ab0.setFont(new Font("微软雅黑", Font.BOLD, 30));
        ab0.setBounds(200, 10, 300, 40);

        //ISBN string
        ab1 = new JLabel("ISBN: ");
        ab1.setBounds(100, 60, 200, 30);

        test1 = new JTextField();
        test1.setBounds(160, 60, 200, 30);

        //Bname string
        ab2 = new JLabel("书名: ");
        ab2.setBounds(100, 110, 200, 30);

        test2 = new JTextField();
        test2.setBounds(160, 110, 200, 30);

        //Bpublish int
        ab3 = new JLabel("出版日期: ");
        ab3.setBounds(100, 160, 200, 30);

        test3 = new JTextField();
        test3.setBounds(160, 160, 200, 30);

        //Bauthor string
        ab4 = new JLabel("作者: ");
        ab4.setBounds(100, 210, 200, 30);

        test4 = new JTextField();
        test4.setBounds(160, 210, 200, 30);

        //Bpress string
        ab5 = new JLabel("出版社: ");
        ab5.setBounds(100, 260, 200,30);

        test5 = new JTextField();
        test5.setBounds(160, 260, 200, 30);

        //Bnumber int
        ab6 = new JLabel("库存量: ");
        ab6.setBounds(100, 310, 200,30);

        test6 = new JTextField();
        test6.setBounds(160, 310, 200, 30);

        //kind string
        ab7 = new JLabel("书类: ");
        ab7.setBounds(100, 360, 200,30);

        test7 = new JTextField();
        test7.setBounds(160, 360, 200, 30);

        bu1 = new JButton("添加");
        bu1.setBounds(200, 450, 100, 40);

        add(ab0);
        add(ab1);
        add(ab2);
        add(ab3);
        add(ab4);
        add(ab5);
        add(ab6);
        add(ab7);


        add(test1);
        add(test2);
        add(test3);
        add(test4);
        add(test5);
        add(test6);
        add(test7);

        add(bu1);
        bu1.setVisible(true);

        bu1.addActionListener(new ActionListener ()  {
            @Override
            public void actionPerformed(ActionEvent e) {
                System.out.println("触发了10号事件: 添加图书");

                String isbn = test1.getText();
                String bname = test2.getText();
                int bpublish = Integer.parseInt(test3.getText());
                String bauthor = test4.getText();
                String bpress = test5.getText();
                int bnumber = Integer.parseInt(test6.getText());
                String kind = test7.getText();

                try{
                    Boook b = new Boook();
                    b.setBauthor(bauthor);
                    b.setBname(bname);
                    b.setBnumber(bnumber);
                    b.setBpress(bpress);
                    b.setBpublish(bpublish);
                    b.setISBN(isbn);
                    b.setKind(kind);
                    //Book_Add c = new Book_Add();
                    Book_Add.Insert(b);
                    Little_Notice.puts("插入成功!");
                }
                catch(SQLException x){

                }
                catch(ClassNotFoundException x){

                }
            }
        });
    }
    public static void main(String[] args) {
        //new Windows_HomePage_User();
    }
}

五, 视图(打分项4)

共有5个视图, 因篇幅有限, 仅举例一处使用, 管理员查看所有用户的时候, 使用了视图, 从而达到管理员也不知道用户密码的目的.

1. 图书分类视图.
2. 管理员查看所有用户的信息(密码除外)
3. 用户查看当前借阅信息
4. 用户查看历史借阅信息
5. 管理员查看所有历史信息
   🍺🍺🍺🍺🍺

Look_ALL_Book():

package P;

import javax.swing.*;
import java.awt.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Look_ALL_Book extends JFrame {
    public Look_ALL_Book() {
        setTitle("查看所有图书");
        setSize(750, 600);
        setLocationRelativeTo(Windows_Login.frame);
        setVisible(true);
        setResizable(false);
        setLayout(null);
        //setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);


        try {
            Connection conn = Conn.conn();
            Statement stmt = conn.createStatement();
            String sql = "select * from user_l5";
            ResultSet rs = stmt.executeQuery(sql);

            Object[][] rowData = new Object[2200][6];

            int cnt = 0;
            int flag = 0;
            //System.out.println(c + c);
            while (rs.next()) {
                int id = rs.getInt("ID");
                String Uname = rs.getString("Uname");
                int Uage = rs.getInt("Uage");
                String Usex = rs.getString("Usex");
                String Umarjor = rs.getString("Umarjor");
                String Uphone = rs.getString("Uphone");
                rowData[cnt][0] = id;
                rowData[cnt][1] = Uname;
                rowData[cnt][2] = Uage;
                rowData[cnt][3] = Usex;
                rowData[cnt][4] = Umarjor;
                rowData[cnt][5] = Uphone;
                cnt++;//vector.add(b);
                //表头
                //System.out.println(ISBN);
            }
            JPanel panel = new JPanel();

            String[] columnNames = {"ID", "名字", "年龄", "性别", "专业", "手机号"};

            //所有行数据

            JTable table = new JTable(rowData, columnNames);

            table.setRowHeight(40);
            table.getColumnModel().getColumn(0).setPreferredWidth(60);
            table.setPreferredScrollableViewportSize(new Dimension(700, 520));

            JScrollPane scrollPane = new JScrollPane(table);

            panel.add(scrollPane);
            add(panel);
            setContentPane(panel);
        }
        catch (SQLException x){

        }
        catch(ClassNotFoundException x){

        }
    }
    public static void main(String[] args){

    }
}

🚝🚃🚃🚃🚃🚃🚃🚃🚃🚃🚃🚃🚃🚃🚃🚃(火车尾表情没有???微软垃圾)

六, 索引(打分项5)

建议满分 😘😘😘

手动为最常进行查询的属性, 添加索引.
共有5个索引.
字符串类型设置Hash, 整数类型设置Btree

七, 存储过程(打分项6)

存储过程因为系统比较简单, 因此只写了两个all_book, all_users
(仅举例一处, 请看sql = “{call all_book()}”处😛)

CREATE DEFINER = CURRENT_USER PROCEDURE `all_users`()
BEGIN
	select * from users;
END;;

Look_All_User():

package P;

import javax.swing.*;
import java.awt.*;
import java.sql.*;

public class Look_ALL_User extends JFrame {
    public Look_ALL_User() {
        setTitle("所有种类图书总览");
        setSize(750, 600);
        setLocationRelativeTo(Windows_Login.frame);
        setVisible(true);
        setResizable(false);
        setLayout(null);
        //setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);


        try {
            /*
            Connection conn = Conn.conn();
            Statement stmt = conn.createStatement();
            String sql = "select * from book";
            ResultSet rs = stmt.executeQuery(sql);

             */
            Connection conn = Conn.conn();
            CallableStatement cs = conn.prepareCall("{call all_book()}");
            ResultSet rs = cs.executeQuery();
            System.out.println("all_books");

            Object[][] rowData = new Object[2200][7];

            int cnt = 0;
            int flag = 0;
            //System.out.println(c + c);
            while (rs.next()) {
                String ISBN = rs.getString("ISBN");
                String Bname = rs.getString("Bname");
                int Bpublish = rs.getInt("Bpublish");
                String Bauthor = rs.getString("Bauthor");
                String Bpress = rs.getString("Bpress");
                int Bnumber = rs.getInt("Bnumber");
                String Kind = rs.getString("Kind");
                rowData[cnt][0] = ISBN;
                rowData[cnt][1] = Bname;
                rowData[cnt][2] = Bpublish;
                rowData[cnt][3] = Bauthor;
                rowData[cnt][4] = Bpress;
                rowData[cnt][5] = Bnumber;
                rowData[cnt][6] = Kind;
                cnt++;//vector.add(b);
                //表头
                //System.out.println(ISBN);
            }
            JPanel panel = new JPanel();

            String[] columnNames = {"ISBN", "书名", "出版日期", "作者", "出版社", "数量", "种类"};

            //所有行数据

            JTable table = new JTable(rowData, columnNames);

            table.setRowHeight(40);
            table.getColumnModel().getColumn(0).setPreferredWidth(60);
            table.setPreferredScrollableViewportSize(new Dimension(700, 520));

            JScrollPane scrollPane = new JScrollPane(table);

            panel.add(scrollPane);
            add(panel);
            setContentPane(panel);
        }
        catch (SQLException x){

        }
        catch(ClassNotFoundException x){

        }
    }
    public static void main(String[] args){

    }
}

八, 触发器(打分项7)

在book表中插入每类图书时, 相同类图书要在everybook插入相应数量(Bnumber)的相同图书, 使用触发器, 检测book表的插入(After状态)

代码一直没写出来, 触发器这部分感觉没法触类旁通.

BEGIN
 DECLARE  no_more_record INT DEFAULT 0;
 DECLARE  isbn varchar(255);
 DECLARE  bname varchar(255);
 DECLARE  bnumber int;
 DECLARE  cur_record CURSOR FOR  SELECT ISBN, Bname, Bnumber from book;
 DECLARE  SET  i = Bnumber;

 OPEN  cur_record;
 FETCH  cur_record INTO isbn, bname, bnumber;
 
 WHILE i  DO
 set i = i - 1;
 INSERT  INTO test(ISBN, Bname, Bnumber)
 VALUES  (isbn, bname, bnumber);
 FETCH  cur_record INTO isbn, bame, bnumber;
 END WHILE;
 CLOSE  cur_record;
END

九, 函数(打分项8)

函数写了2个, find_paaword, find_ps_Admin
一个获取普通用户密码, 第二个获取管理员密码

CREATE DEFINER = CURRENT_USER FUNCTION `find_ps_Admin`()
 RETURNS integer
BEGIN
	RETURN (SELECT Upassword1 from users where users.ID=11111);
END;;

其实我写了没用, 在高级语言层级(java)使用了构造函数多态, 从而完成不同的功能.

package P;

import javax.swing.*;
import java.awt.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.text.*;

public class Borrow_my extends JFrame {
    public Borrow_my(int ID) {
        setTitle("借阅记录");
        setSize(500, 400);
        setLocationRelativeTo(Windows_Login.frame);
        setVisible(true);
        setResizable(false);
        setLayout(null);
        //setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

        try {
            Connection conn4 = Conn.conn();
            Statement stmt = conn4.createStatement();
            String sqlx = "select * from borrowing";
            ResultSet rs = stmt.executeQuery(sqlx);

            Object[][] rowData = new Object[8200][4];

            int cnt = 0;
            int flag = 0;
            SimpleDateFormat sdf = new SimpleDateFormat("", Locale.SIMPLIFIED_CHINESE);
            sdf.applyPattern("yyyy年MM月dd日");

            //System.out.println(c + c);
            while (rs.next()) {
                int id = rs.getInt("ID");
                System.out.println(id);
                if(ID != id)
                    continue;
                String isbn = rs.getString("ISBN");
                long btime = rs.getLong("BTime");
                int bid = rs.getInt("BID");
                rowData[cnt][0] = id;
                rowData[cnt][1] = isbn;
                rowData[cnt][2] = bid;
                rowData[cnt][3] = sdf.format(btime);
                cnt++;

                //System.out.println(ISBN);
            }
            JPanel panel = new JPanel();

            String[] columnNames = {"ID", "ISBN", "图书编号", "归还日期"};

            //所有行数据

            JTable table = new JTable(rowData, columnNames);

            table.setRowHeight(40);
            table.getColumnModel().getColumn(0).setPreferredWidth(60);
            table.setPreferredScrollableViewportSize(new Dimension(400, 300));

            JScrollPane scrollPane = new JScrollPane(table);

            panel.add(scrollPane);
            add(panel);
            setContentPane(panel);
        }
        catch (SQLException x){

        }
        catch(ClassNotFoundException x){

        }
    }
    public Borrow_my() {
        setTitle("所有借阅");
        setSize(700, 500);
        setLocationRelativeTo(Windows_Login.frame);
        setVisible(true);
        setResizable(false);
        setLayout(null);
        //setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

        try {
            Connection conn5 = Conn.conn();
            Statement stmt = conn5.createStatement();
            String sql = "select * from borrowing";
            ResultSet rs = stmt.executeQuery(sql);

            Object[][] rowData = new Object[8200][4];

            int cnt = 0;
            int flag = 0;

            SimpleDateFormat sdf = new SimpleDateFormat("", Locale.SIMPLIFIED_CHINESE);
            sdf.applyPattern("yyyy年MM月dd日");

            //System.out.println(c + c);
            while (rs.next()) {
                int id = rs.getInt("ID");
                System.out.println(id);
                String isbn = rs.getString("ISBN");
                long btime = rs.getLong("BTime");
                int bid = rs.getInt("BID");

                rowData[cnt][0] = id;
                rowData[cnt][1] = isbn;
                rowData[cnt][2] = bid;
                rowData[cnt][3] = sdf.format(btime);
                cnt++;

                //System.out.println(ISBN);
            }
            JPanel panel = new JPanel();

            String[] columnNames = {"ID", "ISBN", "图书编号", "归还日期"};

            JTable table = new JTable(rowData, columnNames);

            table.setRowHeight(40);
            table.getColumnModel().getColumn(0).setPreferredWidth(60);
            table.setPreferredScrollableViewportSize(new Dimension(400, 300));

            JScrollPane scrollPane = new JScrollPane(table);

            panel.add(scrollPane);
            add(panel);
            setContentPane(panel);
        }
        catch (SQLException x){

        }
        catch(ClassNotFoundException x){

        }
    }
    public static void main(String[] args){

    }
}

十, 备份, 还原(打分项9)

备份还原的话, 在mysql操纵软件(我使用navicat)中可以轻松实现.

所有源码以及数据库均已上线GitHub🍕🍔🍟🌭, 点击连接跳转🤟🤟🤟

https://github.com/qpwlkq/Books_Management_System

遵循开源协议: CC0-1.0 License

十一, 权限控制(打分项10)

我的权限控制更多的限制在在高级语言层面, 不同的用户主页对应不同的功能,
经过老师的建议后, 我进行了修改, 每次执行管理员的操作时, 会进行匹配, 查询当前用户是否是管理员账户, 然后才会致行操作, 或报告错误.

学了一个新的名词, sql注入, 其实就是凑一个一定为true的语句, 只要对输入进行提前判断是否合法就可以了.

写在后面

经过认真写一个大作业, 对于数据库的应用熟悉多了, 感觉很妙.
写博客写了半个下午+晚上, 不打个满分吗, 么么么
🚗🚓🚕🛺🚙🚌🚐🚎🚑🚒🚚🚛🚜🚘🚔🚖🚍