数据库课设：机房管理系统

1.功能要求：

􀀂实现机房、上机类型等基本信息的管理；􀍅

􀀂实现机器信息、管理人员信息的管理；􀍅

􀀂实现上机管理，包括机房名称、在用电脑信息、已上机时长和使用人员信息、 和各个机房空闲电脑数量等；􀍅

􀀂实现收费管理，包括机房电脑信息、计费信息、上机人员信息、上机时间、已上机时长以及服务人数和计费统计等；􀍅

􀀂创建触发器，实现下机时自动计算此次上机的时间和费用；􀍅

􀀂创建存储过程，统计各机房的上机时间和上机费用；􀍅

􀀂创建存储过程，统计指定时间段内各收费合计和上机人员统计；

􀀂 创建存储过程，统计收费管理和上机管理中的已上机时长；􀍅

􀀂建立数据库相关表之间的参照完整性约束。

2.1系统设计

2.1.1****功能结构

2.2****概念设计

2.3****逻辑设计

学生信息（学生学号， 学生姓名， 余额， 密码）

老师信息（老师工号， 姓名， 密码， 负责机房号）

电脑信息（电脑编号， 机房号， 电脑详细信息， 开始上机时间，学号）

上机信息（电脑编号， 上机时长， 上机费用， 开始上机时间）

机房信息（机房号， 状态， 学号， 老师工号）

2.4****物理设计

（1****）学生表

列表

数据类型

主键

含义

Sid

int

Y

学生学号

SName

Varchar(20)

学生姓名

Smoney

int

余额

Key1

int

密码

（2****）教师表

列表

数据类型

主键

含义

TId

int

Y

教师工号

TName

Varcher(20)

教师姓名

TPwd

int

密码

Key1

int

负责机房号

（3****）电脑信息表

列表

数据类型

主键

含义

CId

int

Y

电脑编号

CState

Varcher(20)

机房号

Message

Varcher(20)

电脑详细信息

ReturnTime

dateTime

开始上机时间

SId

int

学生学号

（4****）上机信息表

列表

数据类型

主键

含义

Id

Int

Y

电脑编号

Time

Int

上机时长

Money

int

上机费用

ReturnTime

dateTime

开始上机时间

3.数据库SQL语句的实现

3.1****建库建表

drop database if exists schoolp;

create database schoolp;

use schoolp;

/**************** 电脑基本信息表*******************/

create table computer(

CId varchar(20) PRIMARY KEY,

Cstate varchar(20) ,

Message varchar(20) ,

ReturnTime datetime(0) ,

SId int ,

key1 int ,

 key2                                  int

);

/**************** 学生基本信息表*******************/

create table student(

SId int PRIMARY KEY,

SName varchar(20) ,

Smoney int ,

key1 int ,

key2 int

);

/**************** 老师基本信息表*******************/

create table teacher(

TId int PRIMARY KEY,

TName varchar(20) ,

TPwd int ,

key1 varchar(20) ,

key2 varchar(20)

);

/**************** 上机信息表*******************/

create table eexit(

id varchar(20) PRIMARY KEY,

time int ,

money int ,

returntime datetime

);

/**************** 机房信息表*******************/

create table rome(

RId varchar(20) PRIMARY KEY,

RState int ,

Leader varchar(20) ,

 key1                                  int                                                    ,

key2 int

);

-- 外键

alter table computer add constraint FK_SId_student_SId

foreign key(SId) references student (SId);

alter table computer add constraint FK_Cstate_rome_RId

foreign key(Cstate) references rome (RId);

alter table teacher add constraint FK_key1_rome_RId

foreign key(key1) references rome (RId);

-- alter table eexit add constraint FK_id_computer_SId

-- foreign key(id) references computer (CId);

-- -----------------------触发器------------------------------

-- 新增上机时，计算上机金额并存入eexit表中

DROP TRIGGER IF EXISTS tg11;

delimiter ;;

CREATE TRIGGER tg11 BEFORE UPDATE ON computer FOR EACH ROW UPDATE eexit SET money = TIMESTAMPDIFF(MINUTE , OLD.ReturnTime, now())* (8 / 60) where id = OLD.CId

;;

delimiter ;

-- 当新增上机时，计算上机时长并存入eexit表中

DROP TRIGGER IF EXISTS tg22;

delimiter ;;

CREATE TRIGGER tg22 BEFORE UPDATE ON computer FOR EACH ROW UPDATE eexit SET time = TIMESTAMPDIFF(MINUTE , OLD.ReturnTime, now()) where id = OLD.CId

;;

delimiter ;

-- 当新增上机时，把当前时间存入eexit表时间栏中

DROP TRIGGER IF EXISTS tg33;

delimiter ;;

CREATE TRIGGER tg33 BEFORE UPDATE ON computer FOR EACH ROW UPDATE eexit SET ReturnTime = OLd.ReturnTime where id = OLD.CId

;;

delimiter ;

-- ------------------- 存储过程 ----------------------

-- 统计所有机房时间

delimiter $$

drop PROCEDURE if exists sumTime;

create PROCEDURE sumTime ()

begin

select sum(time) from eexit;

end;

$$

delimiter;

-- 统计所有机房余额

delimiter $$

drop PROCEDURE if exists sumMoney;

create PROCEDURE sumMoney ()

begin

select sum(money) from eexit;

end;

$$

delimiter;

-- 统计201机房时间，余额

delimiter $$

drop PROCEDURE if exists sumMoney201;

create PROCEDURE sumMoney201 ()

begin

select sum(money) from eexit where id <= 5;

end;

$$

delimiter;

delimiter $$

drop PROCEDURE if exists sumTime201;

create PROCEDURE sumTime201 ()

begin

select sum(time) from eexit where id <= 5;

end;

$$

delimiter;

-- 统计202机房时间，余额

delimiter $$

drop PROCEDURE if exists sumTime202;

create PROCEDURE sumTime202 ()

begin

select sum(time) from eexit where id <= 10 and id > 5;

end;

$$

delimiter;

delimiter $$

drop PROCEDURE if exists sumMoney202;

create PROCEDURE sumMoney202 ()

begin

select sum(money) from eexit where id <= 10 and id > 5;

end;

$$

delimiter;

4.java运行代码和Swing界面

4.1登录界面和主界面

4.2机房信息

4.3新增上机学生

4.4 学生下机

4.5 查询

4.6 统计

4.7 java代码

4.7.1 底层界面代码

package chppain.view;
import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.FlowLayout;

import javax.swing.JDialog;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.border.TitledBorder;

public class BaseDialog extends JDialog {
    private JLabel lblMsg = null;
    protected JPanel msgPanel = null;
    //受保护的子类才能直接访问
    protected JPanel mainPanel = null; 
    public BaseDialog() {
        msgPanel = new JPanel();
        msgPanel.setBorder(new TitledBorder(null, "", TitledBorder.LEADING, TitledBorder.TOP, null, null));
        FlowLayout flowLayout = (FlowLayout) msgPanel.getLayout();
        flowLayout.setAlignment(FlowLayout.LEFT);
        getContentPane().add(msgPanel, BorderLayout.SOUTH);
        
        JLabel lblNewLabel = new JLabel("提示:");
        msgPanel.add(lblNewLabel);
        
        lblMsg = new JLabel("");
        msgPanel.add(lblMsg);
        
        mainPanel = new JPanel();
        getContentPane().add(mainPanel, BorderLayout.CENTER);
        mainPanel.setLayout(null);
    }
    protected void setErrorMsg(String msg) {
        lblMsg.setForeground(Color.RED);
        lblMsg.setText(msg);
    }
    protected void setSuccessMsg(String msg) {
        lblMsg.setForeground(Color.GREEN);
        lblMsg.setText(msg);
    }
    protected void setInfoMsg(String msg) {
        lblMsg.setForeground(Color.BLACK);
        lblMsg.setText(msg);
    }

}

4.7.2 登录界面代码

package chppain.view;

import javax.swing.ImageIcon;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;

import java.awt.BorderLayout;
import java.awt.Color;

import javax.swing.JPanel;
import javax.swing.JPasswordField;
import javax.swing.JComboBox;
import javax.swing.JTextField;
import javax.swing.SwingConstants;

import chppain.dao.StudentDAO;
import chppain.dao.TeacherDAO;
import chppain.entity.Student;
import chppain.entity.Teacher;

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

public class LoginFrame extends JFrame{
    private JLabel lblMsg = null;
    private JTextField textField;
    private JTextField textField_1;
    public LoginFrame() {
        setTitle("系统登录--机房管理系统");
        setResizable(false);
        setSize(538, 350);
        setLocationRelativeTo(null);
        ImageIcon img = new ImageIcon("imgs/jifang2.jpg");
        JLabel lblNewLabel = new JLabel(img);
        getContentPane().add(lblNewLabel, BorderLayout.NORTH);
        
        JPanel panel = new JPanel();
        getContentPane().add(panel, BorderLayout.CENTER);
        panel.setLayout(null);
        
        JLabel lbl1 = new JLabel("类型:", SwingConstants.RIGHT);
        lbl1.setBounds(39, 10, 100, 23);
        panel.add(lbl1);
        JLabel lbl2 = new JLabel("用户:", new ImageIcon("imgs/base/user.png"), SwingConstants.RIGHT);
        lbl2.setBounds(39, 40, 100, 23);
        panel.add(lbl2);
        JLabel lbl3 = new JLabel("密码:", new ImageIcon("imgs/base/pass.png"), SwingConstants.RIGHT);
        lbl3.setBounds(39, 70, 100, 23);
        panel.add(lbl3);
        
        JComboBox comboBox = new JComboBox();
        panel.add(comboBox);
        comboBox.setBounds(174, 10, 140, 23);
        comboBox.addItem("教师登录");
        comboBox.addItem("学生登录");
        comboBox.setSelectedIndex(1);
        
        JTextField txtUserName = new JTextField();
        txtUserName.setBounds(174, 40, 140, 23);
        panel.add(txtUserName);
        
        JPasswordField txtPwd = new JPasswordField();
        txtPwd.setBounds(174, 70, 140, 23);
        panel.add(txtPwd);
        
        JButton btnOK = new JButton("登    录");
        btnOK.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                String userName = txtUserName.getText();
                
                if (userName.trim().length() == 0) {
                    lblMsg.setText("用户名不能为空！");
                    return;
                }
                char[] pwds = txtPwd.getPassword();
                String Pwd = new String(pwds);
                if (Pwd.trim().length() == 0) {
                    lblMsg.setText("密码不能为空！");
                    return;
                }
                //
                //访问数据库
                Teacher teacher = null;
                Student student = null;
                String tag = "学生";
                int index = comboBox.getSelectedIndex();
                if (index == 0) {
                    tag = "老师";
                    TeacherDAO TeacherDAO = new TeacherDAO();
                    teacher= TeacherDAO.findById(userName, Pwd);
                    if (teacher.getTName() == null) {
                        lblMsg.setText("用户名密码错误！");
                        JOptionPane.showMessageDialog(null, tag + "用户名密码错误！", "登录失败", JOptionPane.ERROR_MESSAGE);
                    }else {
                        MainFrame mainFrame = new MainFrame();
                        mainFrame.setVisible(true);
                        mainFrame.setExtendedState(JFrame.MAXIMIZED_BOTH);
                        dispose();
                    }
                }
                if (index == 1) {
                    StudentDAO studentDAO = new StudentDAO();
                    student= studentDAO.findById(userName, Pwd);
                    if (student.getSName() == null) {
                        lblMsg.setText("用户名密码错误！");
                        JOptionPane.showMessageDialog(null, tag + "用户名密码错误！", "登录失败", JOptionPane.ERROR_MESSAGE);
                    }else {
                        MainFrame mainFrame = new MainFrame();
                        mainFrame.setVisible(true);
//                        mainFrame.setExtendedState(JFrame.MAXIMIZED_BOTH);
                        dispose();
                    }
                }
            }
        });
        btnOK.setBounds(349, 40, 126, 23);
        panel.add(btnOK);
        
        JButton btcCS = new JButton("退    出");
        btcCS.setBounds(349, 70, 126, 23);
        panel.add(btcCS);
        
        txtUserName.setText("");
        txtPwd.setText("");
        
        lblMsg = new JLabel("");
        lblMsg.setForeground(Color.RED);
        getContentPane().add(lblMsg, BorderLayout.SOUTH);
        
    }
}

4.7.3 主界面代码

package chppain.view;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.beans.PropertyVetoException;

import javax.swing.JFrame;
import javax.swing.JMenu;
import javax.swing.JMenuBar;
import javax.swing.JMenuItem;

import chppain.dao.ComputerDAO;
import chppain.dao.StudentDAO;

import javax.swing.ImageIcon;
import javax.swing.JDesktopPane;
import java.awt.BorderLayout;
import java.awt.Color;

import javax.swing.JPanel;
import javax.swing.JLabel;
import java.awt.Font;
import java.awt.FlowLayout;

public class MainFrame extends JFrame{
    private JDesktopPane desktopPane = null;
    public MainFrame() {
        setTitle("机房管理系统");
        setSize(1200,700);
        setLocationRelativeTo(null);
        
        JMenuBar menuBar = new JMenuBar();
        setJMenuBar(menuBar);
        JMenu menu1 = new JMenu("基础数据");
        menuBar.add(menu1);
        JMenuItem item11 = new JMenuItem("机房信息");
        item11.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                ComputerListFrame listFrame = new ComputerListFrame();
                desktopPane.add(listFrame);
                listFrame.setVisible(true);
                try {
                    listFrame.setSelected(true);
                } catch (PropertyVetoException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
                //listFrame.setLocation(10,20);设置里面位置
            }
        });
        JMenuItem item12 = new JMenuItem("老师信息");
        item12.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                TeacherListFrame teaListFrame = new TeacherListFrame();
                desktopPane.add(teaListFrame);
                teaListFrame.setVisible(true);
                try {
                    teaListFrame.setSelected(true);
                } catch (PropertyVetoException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
            }
        });
        menu1.add(item11);
        menu1.add(item12);
        
        JMenu menu2 = new JMenu("统计数据");
        menuBar.add(menu2);
        
        JMenuItem item21 = new JMenuItem("统计时间段");
        item21.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                StatisticsTimeFrame statisticsTimeFrame = new StatisticsTimeFrame();
                desktopPane.add(statisticsTimeFrame);
                statisticsTimeFrame.setVisible(true);
                try {
                    statisticsTimeFrame.setSelected(true);
                } catch (PropertyVetoException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
            }
        });
        
        JMenuItem item22 = new JMenuItem("统计机房");
        item22.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                StatisticsRoomFrame statisticsRoomFrame = new StatisticsRoomFrame();
                desktopPane.add(statisticsRoomFrame);
                statisticsRoomFrame.setVisible(true);
                try {
                    statisticsRoomFrame.setSelected(true);
                } catch (PropertyVetoException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
            }
        });
        
        JMenuItem item23 = new JMenuItem("统计所有");
        item23.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                StatisticsFrame statisticsFrame = new StatisticsFrame();
                desktopPane.add(statisticsFrame);
                statisticsFrame.setVisible(true);
                try {
                    statisticsFrame.setSelected(true);
                } catch (PropertyVetoException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
            }
        });
        
        menu2.add(item21);
        menu2.add(item22);
        menu2.add(item23);
        getContentPane().setLayout(new BorderLayout(0, 0));
        
        desktopPane = new JDesktopPane();
        getContentPane().add(desktopPane);
        
        ImageIcon bg = new ImageIcon("imgs/beijing1.jpg");
        JLabel lblNewLabel = new JLabel(bg);
        lblNewLabel.setSize(bg.getIconWidth(), bg.getIconHeight());
        desktopPane.add(lblNewLabel);
        
        JLabel label = new JLabel("欢迎登陆机房管理系统");
        label.setFont(new Font("宋体", Font.PLAIN, 41));
        label.setForeground(Color.WHITE);
        label.setBounds(358, 54, 420, 73);
        lblNewLabel.add(label);
        
        JLabel txtshoufei = new JLabel("收费信息:");
        txtshoufei.setFont(new Font("宋体", Font.PLAIN, 30));
        txtshoufei.setForeground(Color.WHITE);
        txtshoufei.setBounds(100, 144, 420, 73);
        lblNewLabel.add(txtshoufei);
        
        JLabel label1 = new JLabel("1 号机房          30元/小时");
        label1.setFont(new Font("宋体", Font.PLAIN, 21));
        label1.setForeground(Color.WHITE);
        label1.setBounds(450, 144, 420, 73);
        lblNewLabel.add(label1);
        
        JLabel label2 = new JLabel("2 号机房          25元/小时");
        label2.setFont(new Font("宋体", Font.PLAIN, 21));
        label2.setForeground(Color.WHITE);
        label2.setBounds(450, 224, 420, 73);
        lblNewLabel.add(label2);
        
        JLabel label3 = new JLabel("3 号机房          10元/小时");
        label3.setFont(new Font("宋体", Font.PLAIN, 21));
        label3.setForeground(Color.WHITE);
        label3.setBounds(450, 304, 420, 73);
        lblNewLabel.add(label3);

        JLabel label4 = new JLabel("4 号机房          8元/小时");
        label4.setFont(new Font("宋体", Font.PLAIN, 21));
        label4.setForeground(Color.WHITE);
        label4.setBounds(450, 384, 420, 73);
        lblNewLabel.add(label4);
        }
}

4.7.4 新增界面代码

package chppain.view;

import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Font;
import java.awt.Toolkit;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTextField;
import javax.swing.border.TitledBorder;

import chppain.dao.ComputerDAO;
import chppain.dao.StudentDAO;
import chppain.dao.TeacherDAO;
import chppain.entity.Computer;
import chppain.entity.Student;
import chppain.entity.Teacher;

import javax.swing.JPasswordField;

public class SaveStudentFrame extends BaseDialog{
    private JTextField txtSId;
    private JTextField txtpwd;
    private JTextField txtCId;
    private JTextField textField;
    private JTextField textField_1;
    private JTextField textField_2;
    private JPasswordField passwordField;
    public SaveStudentFrame() {
        setTitle("学生登录");
        setSize(523, 550);
        //居中
        int windowWidth = this.getWidth(); //获得窗口宽
        int windowHeight = this.getHeight(); //获得窗口高
        Toolkit kit = Toolkit.getDefaultToolkit(); //定义工具包
        int screenWidth = kit.getScreenSize().width; //获取屏幕的宽
        int screenHeight = kit.getScreenSize().height; //获取屏幕的高
        this.setLocation(screenWidth/2 - windowWidth/2, screenHeight/2 - windowHeight/2);//设置窗口居中显示
        
        JButton btnOK = new JButton("确定");
        btnOK.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                setInfoMsg("");
                //1 check
                String StSId = txtSId.getText();
                String Cid = txtCId.getText();
                String StPwd = txtpwd.getText();
                if (StSId == null || "".equals(StSId.trim())) {
                    setErrorMsg("学号不能为空！");
                    return;
                }
                if (StSId.length() > 6) {
                    setErrorMsg("学号不能大于6位！");
                    return;
                }
                if (Cid == null || "".equals(Cid.trim())) {
                    setErrorMsg("编号不能为空！");
                    return;
                }
                if (Integer.parseInt(Cid) <= 0 || Integer.parseInt(Cid) > 20) {
                    setErrorMsg("请输入正确的电脑编号！");
                    return;
                }
                if (StPwd == null || "".equals(StPwd.trim())) {
                    setErrorMsg("密码不能为空！");
                    return;
                }
                
                //访问数据库
                Student student = null;
                StudentDAO studentDAO = new StudentDAO();
                student= studentDAO.findById(StSId, StPwd);
                
                ComputerDAO computerDAO1 = new ComputerDAO();
                Computer computer1 = computerDAO1.findById(Cid);
                
                ComputerDAO computerDAO2 = new ComputerDAO();
                Computer computer2 = computerDAO1.findByIdComputer(StSId);
                
                if (student.getSName() == null) {
                    setErrorMsg("用户名密码错误！");
                    JOptionPane.showMessageDialog(null, "用户名密码错误！", "登录失败", JOptionPane.ERROR_MESSAGE);
                    return;
                }
                if (computer2 != null) {
                    setErrorMsg("您以上机，不能重复登录！");
                    return;
                }
                
                else {
                    ComputerListFrame ListFrame = new ComputerListFrame();
                    ListFrame.setVisible(true);
                    dispose();
                }
                
                
                //2 C/S
                int SId = Integer.parseInt(txtSId.getText());
                int CId = Integer.parseInt(txtCId.getText());
                Computer c = new Computer(SId, CId);
                ComputerDAO computerDAO = new ComputerDAO();
                boolean ok= computerDAO.update(c);
                if (ok) {
                    setSuccessMsg("新增上机成功！");
                    JOptionPane.showMessageDialog(null, "新增上机成功！", "成功", JOptionPane.INFORMATION_MESSAGE);
                    dispose();
                }else {
                    JOptionPane.showMessageDialog(null, "新增上机失败！", "失败", JOptionPane.ERROR_MESSAGE);
                }
            }
        });
        btnOK.setBounds(309, 392, 97, 23);
        mainPanel.add(btnOK);
        
        JLabel q = new JLabel("新增上机学生");
        q.setBounds(156, 25, 145, 55);
        mainPanel.add(q);
        q.setFont(new Font("宋体", Font.PLAIN, 20));
        q.setBounds(178, 32, 145, 55);
        mainPanel.add(btnOK);
        
        JButton btnCS = new JButton("取消");
        btnCS.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                dispose();
            }
        });
        btnCS.setBounds(87, 392, 97, 23);
        mainPanel.add(btnCS);
        
        JPanel panel = new JPanel();
        panel.setBorder(new TitledBorder(null, "", TitledBorder.LEADING, TitledBorder.TOP, null, null));
        panel.setBounds(25, 110, 435, 207);
        mainPanel.add(panel);
        panel.setLayout(null);
        
        JLabel label = new JLabel("\u5B66\u53F7");
        label.setBounds(68, 32, 91, 23);
        panel.add(label);
        
        txtSId = new JTextField();
        txtSId.setColumns(10);
        txtSId.setBounds(149, 32, 214, 23);
        panel.add(txtSId);
        
        JLabel label_1 = new JLabel("\u5BC6\u7801");
        label_1.setBounds(68, 134, 97, 23);
        panel.add(label_1);
        
        txtpwd = new JPasswordField();
        txtpwd.setColumns(10);
        txtpwd.setBounds(149, 134, 214, 23);
        panel.add(txtpwd);
        
        
        txtCId = new JTextField();
        txtCId.setColumns(10);
        txtCId.setBounds(149, 86, 214, 23);
        panel.add(txtCId);
        
        JLabel label_2 = new JLabel("\u7535\u8111\u7F16\u53F7");
        label_2.setBounds(68, 86, 91, 23);
        panel.add(label_2);
        
    }
}

4.7.5 统计部分代码

package chppain.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import chppain.db.DBManager;
import chppain.entity.Statistics;
import chppain.entity.Student;
import chppain.entity.Statistics;
import chppain.entity.Statistics;

//存储过程
public class StatisticsDAO {
    /**
     * 统计所有    收费，时间
     * @param c
     * @return
     */
    
    public Statistics statistic(String sta) {
        Statistics t = new Statistics();
        String sql = null;
        if (sta == "timeForenoor") {
            sql = "call sumtimeforenoon ()";
        }
        if (sta == "timeAfternoon") {
            sql = "call sumtimeafternoon ()";
        }
        if (sta == "moneyForenoor") {
            sql = "call summoneyforenoon ()";
        }
        if (sta == "moneyAfternoon") {
            sql = "call summoneyafternoon ()";
        }
        if (sta == "time201") {
            sql = "call sumTime201 ()";
        }
        if (sta == "money201") {
            sql = "call sumMoney201 ()";
        }
        if (sta == "time202") {
            sql = "call sumTime202 ()";
        }
        if (sta == "money202") {
            sql = "call sumMoney202 ()";
        }
        if (sta == "time203") {
            sql = "call sumTime203 ()";
        }
        if (sta == "money203") {
            sql = "call sumMoney203 ()";
        }
        if (sta == "time204") {
            sql = "call sumTime204 ()";
        }
        if (sta == "money204") {
            sql = "call sumMoney204 ()";
        }
        
        if (sta == "time") {
            sql = "call sumTime ()";
        }
        if (sta == "money") {
            sql = "call sumMoney ()";
        }
        if (sta == "time201") {
            sql = "call sumTime201 ()";
        }
        if (sta == "money201") {
            sql = "call sumMoney201 ()";
        }
        if (sta == "time202") {
            sql = "call sumTime202 ()";
        }
        if (sta == "money202") {
            sql = "call sumMoney202 ()";
        }
        if (sta == "time203") {
            sql = "call sumTime203 ()";
        }
        if (sta == "money203") {
            sql = "call sumMoney203 ()";
        }
        if (sta == "time204") {
            sql = "call sumTime204 ()";
        }
        if (sta == "money204") {
            sql = "call sumMoney204 ()";
        }
        System.out.println(sql);
        DBManager dbManager = new DBManager();
        ResultSet rs = dbManager.query(sql);
        try {
            if(rs.next()) {
                int index = rs.getInt(1);
                //构造方法传参赋值
                t = new Statistics(index);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            dbManager.close();
        }
        return t;
    }
    /**
     * 按时间段查询
     * @param sta
     * @return
     */
    
}

package chppain.view;
import java.awt.event.ActionListener;

import javax.swing.JButton;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JTextField;
import javax.swing.border.TitledBorder;

import chppain.dao.StatisticsDAO;
import chppain.entity.Statistics;

import java.awt.Font;
import java.awt.event.ActionEvent;
import javax.swing.JRadioButton;

public class StatisticsTimeFrame extends BaseFrame{
    private JTextField txtTime;
    private JTextField txtmoney;
    private JRadioButton ckbAll;
    private JRadioButton ckbAfternoon;
    private JRadioButton ckbforenoon;
    public StatisticsTimeFrame() {
//        msgPanel.setVisible(false);
        setTitle("统计数据");
        setSize(523, 458);
        //居中
        this.setLocation(337, 97);//设置窗口居中显示
        
        JButton btnOK = new JButton("确定");
//        msgPanel.setVisible(false);
        btnOK.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                quary();
            }
        });
        btnOK.setBounds(364, 349, 97, 23);
        mainPanel.add(btnOK);
        mainPanel.add(btnOK);
        
        JLabel q = new JLabel("统计数据");
        q.setBounds(201, 10, 145, 55);
        mainPanel.add(q);
        q.setFont(new Font("宋体", Font.PLAIN, 20));
        
        JPanel panel = new JPanel();
        panel.setBorder(new TitledBorder(null, "", TitledBorder.LEADING, TitledBorder.TOP, null, null));
        panel.setBounds(26, 64, 435, 252);
        mainPanel.add(panel);
        panel.setLayout(null);
        
        JLabel label_1 = new JLabel("时间");
        label_1.setBounds(66, 133, 54, 23);
        panel.add(label_1);
        
        JLabel label_2 = new JLabel("收费");
        label_2.setBounds(66, 176, 54, 23);
        panel.add(label_2);
        
        JLabel lblNewLabel = new JLabel("请选择:");
        lblNewLabel.setBounds(30, 10, 90, 23);
        panel.add(lblNewLabel);
        
        txtTime = new JTextField();
        txtTime.setText("0");
        txtTime.setEditable(false);
        txtTime.setColumns(10);
        txtTime.setBounds(147, 133, 214, 23);
        panel.add(txtTime);
        
        txtmoney = new JTextField();
        txtmoney.setText("0");
        txtmoney.setEditable(false);
        txtmoney.setColumns(10);
        txtmoney.setBounds(147, 176, 214, 23);
        panel.add(txtmoney);
        
        ckbforenoon = new JRadioButton("上午 8:00-12:00");
        ckbforenoon.setBounds(30, 55, 119, 23);
        panel.add(ckbforenoon);
        
        ckbAfternoon = new JRadioButton("下午 12:00-5:40");
        ckbAfternoon.setBounds(171, 55, 119, 23);
        panel.add(ckbAfternoon);
        
        ckbAll = new JRadioButton("    全选");
        ckbAll.setBounds(310, 55, 97, 23);
        panel.add(ckbAll);
        
        
        JButton button = new JButton("取消");
        button.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                dispose();
            }
        });
        button.setBounds(219, 349, 97, 23);
        mainPanel.add(button);
        loadData();
        
    }
    private void quary() {
         
//        if (ckbAll.i) {
//            
//        }
            if(ckbforenoon.isSelected()) {
                StatisticsDAO statisticsDAO = new StatisticsDAO();
                Statistics statistics = statisticsDAO.statistic("timeforenoon");
                txtTime.setText(statistics.getIndex() + "");
                Statistics statistics2 = statisticsDAO.statistic("moneyforenoon");
                txtmoney.setText(statistics2.getIndex() + "");
            }
            
            if(ckbAll.isSelected()) {
                StatisticsDAO statisticsDAO = new StatisticsDAO();
                Statistics statistics = statisticsDAO.statistic("time");
                txtTime.setText(statistics.getIndex() + "");
                Statistics statistics2 = statisticsDAO.statistic("money");
                txtmoney.setText(statistics2.getIndex() + "");
            }

            if (ckbAfternoon.isSelected()) {
            StatisticsDAO statisticsDAO = new StatisticsDAO();
            Statistics statistics = statisticsDAO.statistic("timeAfternoon");
            txtTime.setText(statistics.getIndex() + "");
            Statistics statistics2 = statisticsDAO.statistic("moneyAfternoon");
            txtmoney.setText(statistics2.getIndex() + "");
        }
    }
    private void loadData() {
        StatisticsDAO statisticsDAO = new StatisticsDAO();
        Statistics T201 = statisticsDAO.statistic("time201");
        Statistics M201 = statisticsDAO.statistic("money201");

        Statistics T202 = statisticsDAO.statistic("time202");
        Statistics M202 = statisticsDAO.statistic("money202");
        
        Statistics T203 = statisticsDAO.statistic("time203");
        txtTime.setText(T203.getIndex() + "");
        Statistics M203 = statisticsDAO.statistic("money203");
        System.out.println(M203.getIndex());
        
        Statistics T204 = statisticsDAO.statistic("time204");
        txtmoney.setText(T204.getIndex() + "");
        Statistics M204 = statisticsDAO.statistic("money204");
        
    }
}

    在数据库设计中，由于机房得电脑总数是固定的，所以在创建表时已经在电脑信息表中添加一定的数据，每次进行上机操作，实际上是进行修改操作，将电脑信息中的学生学号等属性进行修改，实现上机操作，同样，在进行下机时，也是将修改过的数据改为null,进行下机，当然，点击下机按钮，先进行查询，点击确定后，才进行下机。

    由于MySQL中创建了大量的存储过程，在java中调用时，每次都需要连接数据库，非常麻烦，每个存储过程中有很多类似，存储过程调用的结果都是一条数据，存储过程的SQL语句都是一条call开头的非常简短的SQL语句，所以这里创建了一个Statistics类，其中只有一个属性，用来传递结果，对应的创建的StatisticsDAO类中statistic()方法进行判断，通过输入的字符串判断调用哪一个存储过程，再将结果返回给Statistics进行输出，这样每次调用只需在同一个StatisticsDAO类中给statistic()传入不同的字符串，就会有不同的值返回。

    界面设计中所有的界面继承BaseDialog类，BaseDialog类最下面实现提示功能，在界面需要时，将其显示出来，不需要时将其隐藏，在界面需要提示时，比如输出密码，上机下机操作，有严重错误“密码错误”会出现弹窗，否则在“用户名不能为空”“密码不能为空”只会在BaseDialog界面下方进行提示，体现用户友好型。

    此次数据库课设还有很多不足，只能统计当天，不能将几天的数据进行累加，表的设计还需非空，默认值等约束。