基于mysql的简单图书管理系统_mysql图书馆管理系统

作者：IT小白 | 2024-06-09 03:31:40

踩

mysql图书馆管理系统

因为这两天要做数据库课设但是本人又很菜就做了一个简单的系统，简单的实现了增删改查，数据库用的是mysql，但是数据库的权限功能都没有实现，就是程序比较简陋，新手程序员可以参考一下~

题目内容

图书管理系统
设计说明
（1）开发内容
做出图书管理系统的需求分析，概念结构分析，逻辑结构分析，数据库的实施及维护。
开发要求
○1 进行新书入库、现有图书信息修改以及删除；
② 能够实现对读者基本信息的查询和编辑管理；
③ 能够实现预约功能；
④ 能够实现借阅信息的查询功能；
（2）开发环境及工具
系统前台开发软件：My Eclipse或其他
系统后台管理软件:SQL server management studio 2008或其他
系统开发语言：JAVA或其他
（3）系统功能简介
能够存储一定数量的图书信息,并方便有效的进行相应的书籍数据操作和管理，这主要包括：
　　　1) 图书信息的录入、删除及修改。
　　　2) 图书信息的多关键字检索查询。
　　　3) 图书的出借、返还、预约。

功能展示

主界面
在这里插入图片描述

管理员界面
在这里插入图片描述

在这里插入图片描述

学生界面
在这里插入图片描述

主要代码：

package test1;//进行数据库的连接

import java.sql.Connection;
import java.sql.DriverManager;

public class lianjie {
    public  static Connection connection() {


        String driver = "com.mysql.jdbc.Driver";
        String URL = "jdbc:mysql://localhost:3306/ts?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&useSSL=false";
        Connection con = null;
        try {
            Class.forName(driver);
        } catch (java.lang.ClassNotFoundException el) {
            System.out.println("无法加载驱动.");
        }
        try {
            con = DriverManager.getConnection(URL, "root", "123456");//这里输入你自己安装MySQL时候设置的用户名和密码，用户名默认为root
            System.out.println("连接成功.");
        } catch (Exception el) {
            System.out.println("连接失败:" + el.getMessage());
        }

          return  con;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

package test1;//管理员添加学生信息

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

public class Add extends JFrame  {//添加学生信息类，输入学生的信息进行添加




    private static final long serialVersionUID = -1928970409928880648L;

    JLabel jlnumber = new JLabel("学号：");
    JLabel jlpass = new JLabel("密码：");
    JLabel jlname = new JLabel("姓名：");
    JLabel jlsex = new JLabel("性别：");
    JLabel jlbirthday = new JLabel("出生日期：");
    JLabel jldepartment = new JLabel("学院：");

    JTextField jtnumber = new JTextField("",20);
    JTextField jtpass = new JTextField("",20);
    JTextField jtname = new JTextField("",20);
    JTextField jtsex = new JTextField("",20);
    JTextField jtbirthday = new JTextField("",20);
    JTextField jtdepartment = new JTextField("",20);

    JButton buttonadd = new JButton("添加");
    JButton buttonreturn = new JButton("返回");


    public Add() {
        //添加
        JPanel jpnumber = new JPanel();
        JPanel jppass = new JPanel();
        JPanel jpname = new JPanel();
        JPanel jpsex = new JPanel();
        JPanel jpbirthday = new JPanel();
        JPanel jpdepartment = new JPanel();
        JPanel jpforbutton = new JPanel(new GridLayout(1,1));

        jpnumber.add(jlnumber);
        jpnumber.add(jtnumber);

        jppass.add(jlpass);
        jppass.add(jtpass);

        jpname.add(jlname);
        jpname.add(jtname);

        jpsex.add(jlsex);
        jpsex.add(jtsex);

        jpbirthday.add(jlbirthday);
        jpbirthday.add(jtbirthday);

        jpdepartment.add(jldepartment);
        jpdepartment.add(jtdepartment);

        jpforbutton.add(buttonadd);
        jpforbutton.add(buttonreturn);

        //数据库的连接,为添加按钮增加监听事件
        buttonadd.addActionListener(new ActionListener(){
            public void actionPerformed(ActionEvent e) {


                setVisible(false);

                Connection con = lianjie.connection();


                Statement stat = null;
                PreparedStatement ps=null;
                String sql = "INSERT INTO student(number,password,name,sex,birthday,department) "
                        + "values(?,?,?,?,?,?)";


                try {
                    ps=con.prepareStatement(sql);//执行sql语句


                ps.setString(1,jtnumber.getText());
                ps.setString(2,jtpass.getText());
                ps.setString(3,jtname.getText());
                ps.setString(4,jtsex.getText());
                ps.setString(5,jtbirthday.getText());
                ps.setString(6,jtdepartment.getText());


                ps.executeUpdate();

                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
                finally{
                    try{
                        con.close();
                        System.out.println("MySQL 关闭成功");
                        JOptionPane.showMessageDialog(null, "添加成功啦！！");//提示窗口
                    }catch (SQLException c){
                        System.out.println("MySQL 关闭失败 ");
                        c.printStackTrace();
                        JOptionPane.showMessageDialog(null, "添加失败啦！！");//提示窗口
                    }

                }

            }
        });



//为返回按钮添加监听事件
        buttonreturn.addActionListener(new ActionListener(){
            public void actionPerformed(ActionEvent e){
                setVisible(false);

            }
        });


        this.setTitle("添加学生信息");
        this.setLayout(new GridLayout(9,1));
        this.add(jpnumber);
        this.add(jppass);
        this.add(jpname);
        this.add(jpsex);
        this.add(jpbirthday);
        this.add(jpdepartment);
        this.add(jpforbutton);
        this.setLocation(400,300);
        this.setSize(350,300);
        this.setVisible(true);

    }


}


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143

package test1;//管理员修改学生信息

import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;
public class Change extends JFrame {//修改学生信息的类
    private static final long serialVersionUID = -1928970409928880648L;

    JLabel jlnumber = new JLabel("学号：");
    JLabel jlpass = new JLabel("密码:");
    JLabel jlname = new JLabel("姓名：");
    JLabel jlsex = new JLabel("性别：");
    JLabel jlbirthday = new JLabel("出生日期：");
    JLabel jldepartment = new JLabel("学院：");

    JTextField jtnumber = new JTextField("",20);
    JTextField jtpass = new JTextField("",20);
    JTextField jtname = new JTextField("",20);
    JTextField jtsex = new JTextField("",20);
    JTextField jtbirthday = new JTextField("",20);
    JTextField jtdepartment = new JTextField("",20);

    JButton buttonchange = new JButton("修改");
    JButton buttonreturn = new JButton("返回");


    public Change() {
        JPanel jpnumber = new JPanel();
        JPanel jppass = new JPanel();
        JPanel jpname = new JPanel();
        JPanel jpsex = new JPanel();
        JPanel jpbirthday = new JPanel();
        JPanel jpdepartment = new JPanel();
        JPanel jpforbutton = new JPanel(new GridLayout(1,1));

        jpnumber.add(jlnumber);
        jpnumber.add(jtnumber);

        jppass.add(jlpass);
        jppass.add(jtpass);

        jpname.add(jlname);
        jpname.add(jtname);

        jpname.add(jlname);
        jpname.add(jtname);

        jpsex.add(jlsex);
        jpsex.add(jtsex);

        jpbirthday.add(jlbirthday);
        jpbirthday.add(jtbirthday);

        jpdepartment.add(jldepartment);
        jpdepartment.add(jtdepartment);

        jpforbutton.add(buttonchange);
        jpforbutton.add(buttonreturn);

        buttonchange.addActionListener(new ActionListener(){
            public void actionPerformed(ActionEvent e){



                String number = jtnumber.getText();
                String name = jtname.getText();
                String sex = jtsex.getText();
                String birthday = jtbirthday.getText();
                String department = jtdepartment.getText();
                String password = jtpass.getText();

                Connection con = lianjie.connection();
                ResultSet res = null;
                Statement stat = null;

                String sql = "SELECT NUMBER FROM student;";

                try {
                    stat=con.createStatement();
                    res=stat.executeQuery(sql);
                    boolean flag=false;

                    while (res.next())
                    {

                        //change
                        if (res.getString(1).equals(number)) {

                            String sql2 = "UPDATE student SET name='" + name + "'  WHERE number='" + jtnumber.getText() + "'";
                            String sql6 = "UPDATE student SET password='" + password+ "'  WHERE number='" + jtnumber.getText() + "'";
                            String sql3 = "UPDATE student SET sex='" + sex + "'  WHERE number='" + jtnumber.getText() + "'";
                            String sql4 = "UPDATE student SET birthday='" + birthday + "'  WHERE number='" + jtnumber.getText() + "'";
                            String sql5 = "UPDATE student SET department='" + department + "'  WHERE number='" + jtnumber.getText() + "'";

                            JOptionPane.showMessageDialog(null, "修改学生信息成功！！");//提示窗口
                            flag=true;

                            try {
                                stat = con.createStatement();
                                stat.executeUpdate(sql2);
                               stat.executeUpdate(sql6);
                                stat.executeUpdate(sql3);
                                stat.executeUpdate(sql4);
                                stat.executeUpdate(sql5);

                                jtname.setText("");
                                jtsex.setText("");
                                jtbirthday.setText("");
                                jtdepartment.setText("");
                                jtpass.setText("");


                                break;
                            } catch (SQLException g) {

                                g.printStackTrace();
                            }
                        }


                        }
                        if (flag==false){
                            JOptionPane.showMessageDialog(null, "修改失败啦，请输入正确的学号！！");//提示窗口
                        }
                } catch (SQLException e1) {
                    e1.printStackTrace();
                } finally {
                    try {
                        con.close();
                    } catch (SQLException e1) {
                        e1.printStackTrace();
                    }
                }


            }



        });


        buttonreturn.addActionListener(new ActionListener(){
            public void actionPerformed(ActionEvent e){
                setVisible(false);
            }
        });

        this.setTitle("修改学生信息");
        this.setLayout(new GridLayout(9,1));
        this.add(jpnumber);
        this.add(jppass);
        this.add(jpname);
        this.add(jpsex);
        this.add(jpbirthday);
        this.add(jpdepartment);
        this.add(jpforbutton);
        this.setLocation(400,300);
        this.setSize(350,300);
        this.setVisible(true);


    }



}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170

package 数据库课设;//学生预约图书
import javax.swing.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class 预约 extends JFrame {
      JButton buttonbook=new JButton("预约");
      JButton buttonreturn=new JButton("返回");
      JTextField book = new JTextField("",20);
      JLabel label = new JLabel("（输入完整图书名称）");
      public 预约(String sid) {
    	  super("预约");
    	  this.setSize(800,600);
          this.setLayout(null);
          this.setLocation(100,50);
          add(buttonreturn);
          add(buttonbook);
          add(book);
          add(label);
          buttonbook.setBounds(10, 10, 100, 80);
          buttonreturn.setBounds(600, 10, 100, 80);
          book.setBounds(400, 10, 100, 80);
          label.setBounds(200,10,200,80);
          this.setVisible(true);
          Connection con=连接.MyConnection();
          // 建立查询条件
          Statement sql;
          ResultSet rs;
          try {
        	  sql=con.createStatement();
          }
          catch(SQLException e) {
  	        System.out.println(e);
          }
          buttonreturn.addActionListener(new ActionListener(){
              public void actionPerformed(ActionEvent e){
                  setVisible(false);

              }
          });
          buttonbook.addActionListener(new ActionListener(){
              public void actionPerformed(ActionEvent e){
            	  
            	  
              	 String str=book.getText();
              	 String s;
              	 try {
              		 Connection con=连接.MyConnection();
              		 Statement sql=con.createStatement();
              		 ResultSet rs;
              	     rs=sql.executeQuery("SELECT * FROM tushu WHERE BookName LIKE '"+str+"'");
              	     rs.next();
              	     s=rs.getString(1);
              	    	
              	     int a=rs.getInt(6);
              	     if(a==0) {
               	    	JOptionPane.showMessageDialog(null, "预约失败，此书已全部借出", "错误",JOptionPane.WARNING_MESSAGE);
              	     }
              	     else {
              	     int count=sql.executeUpdate("UPDATE tushu set Count=Count-1 WHERE BookID='"+s+"'");
              	     }
              	     if(a==0) {
              	    	int count1=sql.executeUpdate("UPDATE tushu set COM='已借完' WHERE BookID='"+s+"'");
              	     }
              	     else {
              	    	
              	    	sql.executeUpdate("INSERT INTO Search VALUES('" + sid + "','" + s + "')");
              	    	JOptionPane.showMessageDialog(null, "预约成功！");
             
              	     }
              }
              	catch(SQLException e1) {
              		System.out.println(e1);
              		JOptionPane.showMessageDialog(null, "无此书", "错误",JOptionPane.WARNING_MESSAGE);
                }
              }
              });
      }

    }


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

package 数据库课设;//学生归还图书
import javax.swing.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class 归还 extends JFrame{
	String str;
	JButton buttonback=new JButton("归还");
	JButton buttonreturn=new JButton("返回");
	JTextField back = new JTextField("",20);
	JLabel label = new JLabel("（请输入完整图书名称）");
	String s=back.getText();
    public 归还(String sid) {
  	    super("归还");
  	    this.setSize(800,600);
        this.setLayout(null);
        this.setLocation(100,50);
        add(buttonreturn);
        add(buttonback);
        add(back);
        add(label);
        buttonback.setBounds(10, 10, 100, 80);
        buttonreturn.setBounds(600, 10, 100, 80);
        back.setBounds(400, 10, 100, 80);
        label.setBounds(200,10,200,80);
        this.setVisible(true);
        Connection con=连接.MyConnection();
        // 建立查询条件
        Statement sql;
        ResultSet rs;
        try {
        	sql=con.createStatement();        	
        }
        catch(SQLException e) {
  	        System.out.println(e);
          }
        buttonreturn.addActionListener(new ActionListener(){
            public void actionPerformed(ActionEvent e){
                setVisible(false);

            }
        });
        buttonback.addActionListener(new ActionListener(){
            public void actionPerformed(ActionEvent e){
          	    String s=back.getText();
          	    boolean flag=false;
          	  Connection con=连接.MyConnection();
 		        Statement sql;
				try {
					sql = con.createStatement();
				} catch (SQLException e2) {
					// TODO 自动生成的 catch 块
					e2.printStackTrace();
				}
 		        ResultSet rs;
				ResultSet rst;
				String sss;

          	    try {
          	    	sql = con.createStatement();
       		        rs=sql.executeQuery("select bookname,tushu.bookid FROM tushu,Search  WHERE tushu.bookid=search.bookid  and Studentid='"+sid+"'");
       		        while(rs.next()) {
       		        	String ss=rs.getString(1);
       		        	sss=rs.getString(2);
       		        	if(ss.equals(s)) {
       		        		flag=true;
       		        		str=sss;
       		        		JOptionPane.showMessageDialog(null, "归还成功！");
       		        		
       		        		
       		        	}
       		           }
       		        if(flag==false) { 
		        		JOptionPane.showMessageDialog(null, "未借此书", "错误",JOptionPane.WARNING_MESSAGE);
		        	}
       		        else{
								sql.executeUpdate("UPDATE tushu set Count=Count+1 WHERE BookID='" + str+ "'");
							}


       		      
       		         sql.execute("DELETE FROM Search  WHERE Search.BookID In(select t.bookid from (select tushu.bookid from search,tushu where  BookName = '"+s+"'and search.BookID=tushu.BookID) as t) and Studentid='"+sid+"'");
			}
          	    
          	  catch(SQLException e1) {
          		    System.out.println(e1);
            		JOptionPane.showMessageDialog(null, "未借此书", "错误",JOptionPane.WARNING_MESSAGE);
              }
            }
            });
    }


}


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99

package menu;//菜单页面选择学生or管理员

import test1.login;
import 数据库课设.学生登录;

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

  public class menu extends JFrame{


      JPanel jp3;
     JButton jb1,jb2;
     public menu(){

         
         jb1 = new JButton("学生");
         jb2 = new JButton("管理员");
         jb1.setBounds(100, 80, 80, 50);
         jb2.setBounds(100, 180, 80, 50);
         jp3 = new JPanel();
         //String path="a.png";
         this.setLayout(null);
         jp3.setLayout(null);
         ImageIcon background = new ImageIcon("src/a.png");
         JLabel back=new JLabel(background);
         jp3.setBounds(0, 0, 800, 600);
         back.setBounds(0, 0, background.getIconWidth(),background.getIconHeight());	

         
        
         
         jp3.add(jb1,new Integer("100"));
         jp3.add(jb2,new Integer("100"));
         jp3.add(back,new Integer("-100"));
         
         this.add(jp3);
         Point p = new Point(450, 300  );
         this.setSize(739, 413);
         this.setLocation(p);
         this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
         this.setVisible(true);
         this.setTitle("图书馆管理系统");
         this.setResizable(false);
             
       
         jb1.addActionListener(new ActionListener(){
         
         public void actionPerformed(ActionEvent e) {

             new 学生登录();
         }

         });
         
         jb2.addActionListener(new ActionListener(){
             
             public void actionPerformed(ActionEvent e) {

       new login();
             }

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

完整源码有空上传~

2023/6/19
我终于想起来上传了，但是大一的东西数据库文件实在找不到了，其他的文件还在，给大一的小萌新学习用~
链接：https://pan.baidu.com/s/1pRORommPehTGJ8CwK7piTg
提取码：xlep

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/IT小白/article/detail/692434