数据库课设：机房管理系统
1.功能要求： 实现机房、上机类型等基本信息的管理； 实现机器信息、管理人员信息的管理； 实现上机管理，包括机房名称、在用电脑信息、已上机时长和使用人员信息、 和各个机房空闲电脑数量等； 实现收费管理，包括机房电脑信息、计费信息、上机人员信息、上机时间、已上机时长以及服务人数和计费统计等； 创建触发器，实现下机时自动计算此次上机的时间和费用； 创建存储过
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");
}
}
5 总结
在数据库设计中，由于机房得电脑总数是固定的，所以在创建表时已经在电脑信息表中添加一定的数据，每次进行上机操作，实际上是进行修改操作，将电脑信息中的学生学号等属性进行修改，实现上机操作，同样，在进行下机时，也是将修改过的数据改为null,进行下机，当然，点击下机按钮，先进行查询，点击确定后，才进行下机。
由于MySQL中创建了大量的存储过程，在java中调用时，每次都需要连接数据库，非常麻烦，每个存储过程中有很多类似，存储过程调用的结果都是一条数据，存储过程的SQL语句都是一条call开头的非常简短的SQL语句，所以这里创建了一个Statistics类，其中只有一个属性，用来传递结果，对应的创建的StatisticsDAO类中statistic()方法进行判断，通过输入的字符串判断调用哪一个存储过程，再将结果返回给Statistics进行输出，这样每次调用只需在同一个StatisticsDAO类中给statistic()传入不同的字符串，就会有不同的值返回。
界面设计中所有的界面继承BaseDialog类，BaseDialog类最下面实现提示功能，在界面需要时，将其显示出来，不需要时将其隐藏，在界面需要提示时，比如输出密码，上机下机操作，有严重错误“密码错误”会出现弹窗，否则在“用户名不能为空”“密码不能为空”只会在BaseDialog界面下方进行提示，体现用户友好型。
此次数据库课设还有很多不足，只能统计当天，不能将几天的数据进行累加，表的设计还需非空，默认值等约束。
本社区面向用户介绍CSDN开发云部门内部产品使用和产品迭代功能，产品功能迭代和产品建议更透明和便捷
更多推荐
26
0- 0
已为社区贡献1条内容
所有评论(0)