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(

CIdvarchar(20) PRIMARY KEY,

Cstate varchar(20) ,

Message varchar(20) ,

ReturnTime datetime(0) ,

SId int ,

key1 int ,

key2 int

);

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

create table student(

SIdint PRIMARY KEY,

SName varchar(20) ,

Smoney int ,

key1int ,

key2 int

);

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

create table teacher(

TIdint PRIMARY KEY,

TName varchar(20) ,

TPwd int ,

key1varchar(20) ,

key2 varchar(20)

);

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

create table eexit(

idvarchar(20) PRIMARY KEY,

time int,

money int ,

returntimedatetime

);

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

create table rome(

RIdvarchar(20) PRIMARY KEY,

RStateint,

Leadervarchar(20),

key1int ,

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 5;

end;

$$

delimiter;

delimiter $$

drop PROCEDURE if exists sumMoney202;

create PROCEDURE sumMoney202 ()

begin

select sum(money) from eexit where 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() {@Overridepublic 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 blocke1.printStackTrace();}//listFrame.setLocation(10,20);设置里面位置}});JMenuItem item12 = new JMenuItem("老师信息");item12.addActionListener(new ActionListener() {@Overridepublic 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 blocke1.printStackTrace();}}});menu1.add(item11);menu1.add(item12);JMenu menu2 = new JMenu("统计数据");menuBar.add(menu2);JMenuItem item21 = new JMenuItem("统计时间段");item21.addActionListener(new ActionListener() {@Overridepublic 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 blocke1.printStackTrace();}}});JMenuItem item22 = new JMenuItem("统计机房");item22.addActionListener(new ActionListener() {@Overridepublic 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 blocke1.printStackTrace();}}});JMenuItem item23 = new JMenuItem("统计所有");item23.addActionListener(new ActionListener() {@Overridepublic 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 blocke1.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 checkString 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)  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/Sint 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");}}

5 总结

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

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

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

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