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界面下方进行提示,体现用户友好型。
此次数据库课设还有很多不足,只能统计当天,不能将几天的数据进行累加,表的设计还需非空,默认值等约束。