数据库设计小型教务系统（成绩管理系统）
数据库老师留了一项作业，从二十个实验题目中挑一个进行实验，写出实验报告并提交，我兴冲冲花了两周时间写了个以JFrame作为界面的小型教务系统，，结果说只需要画E-R图，写关系模式画出表格介绍表的组成再写10条SQL语句运行截图就成了Σ(⊙▽⊙"a 。。。。白肝了两周(ノ｀Д)ノ
不过还是打算写在博客上和大家分享一下我的作品，毕竟是Java新手，有好多瑕疵和不足的地方，只供有需要的同学参考，大神轻喷。。
高校综合教务管理系统
实现一个计算机综合教务管理系统，需要存储信息：教师信息，学生信息，班级信息，学校开设课程信息，学生选课信息等，并完成相应管理等功能。教师录完成绩后，就可以对学生成绩就行各种统计分析，请设计一个成绩管理系统，能够完成对学生课程以及成绩的管理，并能进行班级和专业的成绩统计分析。
一、需求分析
1.1需求分析
数据库是一种存储数据并对数据进行操作的工具。数据库的作用在于组织和表达信息，简而言之，数据库就是信息的集合。计算机的数据库可以分为两类：非关系数据库和关系数据库。关系数据库中包含了多个数据表的信息，数据库含有各个不同部分的术语，如记录、域等。
SQLserver 2008R2就是关系数据库开发工具，数据库能汇集各种信息以供查询、存储和检索。SQL 的优点在于它集数据查询、数据操纵、数据定义和数据控制功能于一体。
通过调查本地的学校，根据学校的具体情况设计学生选课信息管理系统。主要功能有：
1.教师管理：教师信息查询、插入、删除、修改等
2.学生管理：学生信息查询、插入、删除、修改等
3.开设课程管理：学校开设课程信息查询、插入、删除、修改等
4.选课管理：选课信息查询、插入、删除、修改等
5. 成绩管理：成绩信息查询、插入、删除、修改等
6. 系统维护：如数据安全管理（含备份与恢复）、操作员管理、权限设置等
1.2 关系模式
关系模式：
Teacher(Tno,Tname,Tsex,Tage,Tdept,Tsalary)
其中各项含义分别为：教师（工号，姓名，性别，年龄，专业，工薪）
Student(Sno,Sname,Ssex,Sage,Sdept,Class)
其中各项含义分别为：学生（学号，姓名，性别，年龄，专业，班级）
Course(Cno,Cname,Cpno,Credit,Remarks)
其中各项含义分别为：课程（课程号，课程名，先修课，学分，类型）
SelectCourse(Sno,Sname,Cno,Cname)
其中各项含义分别为：选课（学号，姓名，性别，课程号，课程名）
SC(Sno,Sname,Cno,Cname,Score)
其中各项含义分别为：成绩（学号，姓名，课程号，课程名，分数）
suser(username,password)
其中各项含义分别为：管理员（用户名，密码）
规范化处理
经过对初始关系模式的规范化处理以下关系模式中不存在部分函数依赖和传递函数依赖（加粗代表主码，斜体外码）
Teacher(Tno,Tname,Tsex,Tage,Tdept,Tsalary)
Student(Sno,Sname,Ssex,Sage,Sdept,Class)
Course(Cno,Cname,Cpno,Credit,Remarks)
SelectCourse(Sno,Sname,Cno,Cname)
SC(Sno,Sname,Cno,Cname,Score)
suser(username,password)
1.3 E-R图
二、数据库表
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
|---|---|---|---|---|
| Tno | 工号 | Char（9） | 主键 | |
| Tname | 姓名 | Char（20） | Not null | |
| Tsex | 性别 | Char（2） | Null | ‘男’或’女’ |
| Tage | 年龄 | Smallint | Null | |
| Tdept | 专业 | Char（20） | Null | |
| Tsalary | 工薪 | Int | Null |
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| Sno | 学号 | Char（9） | 主键 | |
| Sname | 姓名 | Char（20） | ||
| Ssex | 性别 | Char（2） | Null | '男'或'女' |
| Sage | 年龄 | Smallint | Null | |
| Sdept | 专业 | Char（20） | Null | |
| Class | 班级 | Char（10） | Null |
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| Cno | 课程号 | Char（4） | 主键 | |
| Cname | 课程名 | Char（20） | ||
| Cpno | 先修课 | Char（4） | Null | |
| Credit | 学分 | Smallint | Null | |
| Remarks | 类型 | Char（4） | Null | '必修'或'选修' |
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| Sno | 学号 | Char（9） | 主键 | 外键，来自Student表 |
| Sname | 姓名 | Char（20） | ||
| Cno | 课程号 | Char（4） | 外键，来自Course表 | |
| Cname | 课程名 | Char（20） |
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| Sname | 姓名 | Char（20） | Null | 外键，来自SelectCourse表 |
| Sno | 学号 | Char（9） | 主键 | 外键，来自SelectCourse表 |
| Cno | 课程号 | Char（4） | 外键，来自SelectCourse表 | |
| Cname | 课程名 | Char（20） | Null | 外键，来自SelectCourse表 |
| Score | 分数 | Smallint | Null |
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| username | 用户名 | Varchar（20） | 主键 | |
| password | 密码 | Varchar（20） | Null |
三、SQL语句
建表
create table suser (username varchar(20) primary key not null,password varchar(20) null);
insert into suser(username,password) values(‘Admin’,123456);
go
create table Student(
Sno char(9),
Sname char (20) not null ,
Ssex char(2)check (Ssex in(‘男’,‘女’)),
Sage smallint,
Sdept char(20),
Class char(10),
constraint pks primary key(Sno,Sname))
go
Create table Course(
Cno char(4) ,
Cname char(20)not null ,
Cpno char(4),
Credit smallint,
Remarks char(4)check(remarks in(‘选修’,‘必修’))
constraint pkc primary key(Cno,Cname))
go
create table SelectCourse(
Sno char(9),
Sname char(20),
Cno char(4),
Cname char(20),
FOREIGN KEY (sno,sname) REFERENCES Student(sno,sname)on delete cascade,
FOREIGN KEY (cno,cname) REFERENCES Course(Cno,Cname)on delete cascade,
constraint pk primary key(Sno,Sname,Cno,Cname)
)
go
Create table Teacher(
Tno char(9) primary key,
Tname char (20) not null,
Tsex char(2)check (Tsex in(‘男’,‘女’)),
Tage smallint,
Tdept char(20),
Tsalary int
)
go
create table SC(
Sno char(9),
Sname char(20),
Cno char(4),
Cname char(20),
Score smallint,
FOREIGN KEY (sno,sname,cno,cname) REFERENCES SelectCourse(sno,sname,cno,cname)on delete cascade,
constraint pksc primary key(Sno,Cno)
)
建索引
CREATE UNIQUE INDEX Course_Cname
ON Course (Cname);
CREATE UNIQUE INDEX Course_Cno
ON Course (Cno);
CREATE INDEX SC_Sno
ON SC (Sno);
CREATE INDEX SC_Cno
ON SC (Cno);
CREATE INDEX Student_Sdept
ON Student (Sdept);
CREATE INDEX Student_Class
ON Student (Class);
CREATE INDEX SC_Score
ON SC (Score);
CREATE INDEX SelectCourse_Sno
ON SelectCourse (Sno);
CREATE INDEX SelectCourse_Cno
ON SelectCourse (Cno);
CREATE INDEX SelectCourse_Sname
ON SelectCourse (Sname);
CREATE INDEX SelectCourse_Cname
ON SelectCourse (Cname);
CREATE UNIQUE INDEX Student_Sno
ON Student (Sno);
CREATE UNIQUE INDEX Student_Sname
ON Student (Sname);
CREATE UNIQUE INDEX Teacher_Tno
ON Teacher (Tno);
建视图
CREATE VIEW View_1 AS
SELECT TOP (100) PERCENT dbo.SC.Sno, dbo.SC.Sname, dbo.SC.Cno, dbo.SC.Cname, dbo.SC.Score, dbo.Student.Sdept, dbo.Student.Class
FROM dbo.SC INNER JOIN dbo.Student ON dbo.SC.Sno = dbo.Student.Sno
ORDER BY dbo.SC.Score DESC, dbo.SC.Sno
建触发器
create trigger student_update on student
for update as
if(UPDATE(sdept)or UPDATE(Class))
begin
update View_1 set Sdept=a.Sdept,Class=a.Class
from inserted a,View_1 v
where a.Sno=v.Sno
end;
查询
select SNO,sum(Score) sum_grade
from SC
group by SC.SNO
having count(CNO)>=1
order by sum_grade desc
Select * from SC order by Score desc;
Select * from SC where Sno=‘160001’ and Cno=‘1003’
插入
INSERT INTO [SC]
([Sno],[Sname],[Cno],[Cname])
SELECT
[Sno], [Sname], [Cno],[Cname]
FROM
[SelectCourse]
删除
delete from SC where Sno='160001’and Sname='小白’and Cno='1001’and Cname=‘Java’;
修改
update SC set Sno=‘170002’,Cno=‘1003’,Sname=(select Sname from SelectCourse where Sno='170002’and Cno=‘1003’),Cname=(select Cname from SelectCourse where Sno='170002’and Cno=‘1003’),Score=99 where Sno=‘170001’ and Cno=‘1002’;
权限
grant select on suser to test;
grant update on suser to test;
grant insert on suser to test;
grant delete on suser to test;
四、Java前端
先上图
代码：
Login.java
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.KeyAdapter;
import java.awt.event.KeyEvent;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JPasswordField;
import javax.swing.JTextField;
public class Login extends JFrame implements ActionListener {
private static final long serialVersionUID = 5252772035964963789L;
private JPanel pan = new JPanel();
private JLabel namelab = new JLabel("用户名");
private JLabel passlab = new JLabel("密 码");
private JTextField nametext = new JTextField();
private JPasswordField passtext = new JPasswordField();
public JButton denglu = new JButton("登录");
public JButton chongzhi = new JButton("重置");
public Login(){
this.setLocation(550, 250);
Font font = new Font("宋体",Font.BOLD,12);
super.setTitle("欢迎登录小白教务系统");
pan.setLayout(null);
namelab.setBounds(20,20,60,30);
nametext.setBounds(90,20,140,30);
passlab.setBounds(20,60,60,30);
passtext.setBounds(90,60,140,30);
denglu.setBounds(30,120,90,20);
chongzhi.setBounds(140,120,90,20);
pan.add(namelab);
pan.add(nametext);
pan.add(passlab);
pan.add(passtext);
pan.add(denglu);
pan.add(chongzhi);
passtext.setFont(font);
chongzhi.setFont(font);
denglu.addActionListener(this);
chongzhi.addActionListener(this);
super.add(pan);
super.setSize(300,200);
super.setVisible(true);
passtext.addKeyListener(new KeyAdapter() {
public void keyPressed(KeyEvent e2) {
if(e2.getKeyChar()==KeyEvent.VK_ENTER) {//如果密码是enter键
denglu.doClick();//点击登录按钮
} }
});
}
public static void main(String []args){
new Login();
}
public void actionPerformed(ActionEvent arg0) {
if(arg0.getSource()==denglu){
denglu();
}else if (arg0.getSource()==chongzhi){
chongzhi();
}
}
//登录按钮的事件处理函数
public void denglu(){
Jdbc d = new Jdbc();
String username = nametext.getText();
char[] password = passtext.getPassword();
if(d.compare(username, password)){
JOptionPane.showMessageDialog(null, "登陆成功！");
dispose();
new SystemUI();
}
}
//重置按钮触发后的事件处理函数
public void chongzhi() {
nametext.setText("");
passtext.setText("");
}
}
class Jdbc {
Connection con = null;
Statement statement = null;
ResultSet res = null;
String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem";
String name = "test";
String passwd = "123456";
public Jdbc(){
try{
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url,name,passwd);
statement = con.createStatement();
}catch(ClassNotFoundException e){
System.out.println("对不起，找不到这个Driver");
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}
}
//对比用户名和密码是不匹配
public boolean compare(String username,char[] password){
String pwd = String.valueOf(password);
boolean m = false;
String sql = "select password from suser where username='"+username+"'";
try{
res = statement.executeQuery(sql);
if(res.next()){
String pa = res.getString(1);
System.out.println(pa+" " +pwd);
if(pa.equals(pwd)){
m = true;
}else {
JOptionPane.showMessageDialog(null, "密码错误！");
}
}else {
JOptionPane.showMessageDialog(null, "用户名不存在！");
}
}catch(SQLException e){
e.printStackTrace();
}
return m;
}
}
SystemUI.java
import java.awt.BorderLayout;
import java.awt.EventQueue;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JToolBar;
import javax.swing.JTabbedPane;
import javax.swing.JScrollPane;
public class SystemUI extends JFrame{
private static final long serialVersionUID = 4197017698513729527L;
Teacher t = new Teacher();
Course c = new Course();
Student st = new Student();
Score sc = new Score();
SelectCourse se = new SelectCourse();
static JTabbedPane tabbedPane;
static JToolBar toolBar_1;
static JToolBar toolBar_2;
static JToolBar toolBar_3;
static JToolBar toolBar_4;
static JToolBar toolBar_5;
static JButton AddTeacher;
static JButton UpdateTeacher;
static JButton DeleteTeacher;
static JButton SearchTeacher;
static JButton RefreshTeacher;
static JButton AddStudent;
static JButton UpdateStudent;
static JButton DeleteStudent;
static JButton SearchStudent;
static JButton RefreshStudent;
static JButton AddCourse;
static JButton UpdateCourse;
static JButton DeleteCourse;
static JButton SearchCourse;
static JButton RefreshCourse;
static JButton AddSelCou;
static JButton DeleteSelCou;
static JButton SearchSelCou;
static JButton RefreshSelCou;
static JButton AddScore;
static JButton UpdateScore;
static JButton DeleteScore;
static JButton SearchScore;
static JButton RefreshScore;
static JButton Statistical;
static JButton TotalRank;
static JScrollPane scrollPane1;
static JScrollPane scrollPane2;
static JScrollPane scrollPane3;
static JScrollPane scrollPane4;
static JScrollPane scrollPane5;
public SystemUI()
{
this.setSize(565, 500);
this.setLocation(550, 0);
this.setVisible(true);
this.setTitle("欢迎来到小白教务系统");
getContentPane().setLayout(new BorderLayout(0, 0));
tabbedPane = new JTabbedPane(JTabbedPane.TOP);
getContentPane().add(tabbedPane, BorderLayout.CENTER);
JPanel TeacherInfo = new JPanel();
JPanel StudentInfo = new JPanel();
JPanel CourseInfo = new JPanel();
JPanel ScoreInfo = new JPanel();
JPanel SelectCourseInfo = new JPanel();
tabbedPane.add(TeacherInfo,"教师信息");
TeacherInfo.setLayout(new BorderLayout(0, 0));
toolBar_1 = new JToolBar();
TeacherInfo.add(toolBar_1, BorderLayout.NORTH);
AddTeacher = new JButton("添加教师信息");
AddTeacher.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
t.addTeacher();
}
});
toolBar_1.add(AddTeacher);
UpdateTeacher = new JButton("修改教师信息");
UpdateTeacher.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
t.updateTeacher();
}
});
toolBar_1.add(UpdateTeacher);
DeleteTeacher = new JButton("删除教师信息");
DeleteTeacher.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
t.deleteTeacher();
}
});
toolBar_1.add(DeleteTeacher);
SearchTeacher = new JButton("搜索教师信息");
SearchTeacher.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
t.searchTeacher();
}
});
toolBar_1.add(SearchTeacher);
RefreshTeacher = new JButton("刷新");
RefreshTeacher.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
t.refreshTeacher();
}
});
toolBar_1.add(RefreshTeacher);
scrollPane1 = new JScrollPane();
TeacherInfo.add(scrollPane1, BorderLayout.CENTER);
scrollPane1.setViewportView(Teacher.table1);
tabbedPane.add(StudentInfo,"学生信息");
StudentInfo.setLayout(new BorderLayout(0, 0));
toolBar_2 = new JToolBar();
StudentInfo.add(toolBar_2, BorderLayout.NORTH);
AddStudent = new JButton("添加学生信息");
AddStudent.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
st.addStudent();
}
});
toolBar_2.add(AddStudent);
UpdateStudent = new JButton("修改学生信息");
UpdateStudent.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
st.updateStudent();
}
});
toolBar_2.add(UpdateStudent);
DeleteStudent = new JButton("删除学生信息");
DeleteStudent.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
st.deleteStudent();
}
});
toolBar_2.add(DeleteStudent);
SearchStudent = new JButton("搜索学生信息");
SearchStudent.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
st.searchStudent();
}
});
toolBar_2.add(SearchStudent);
RefreshStudent = new JButton("刷新");
RefreshStudent.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
st.refreshStudent();
}
});
toolBar_2.add(RefreshStudent);
scrollPane2 = new JScrollPane();
StudentInfo.add(scrollPane2, BorderLayout.CENTER);
scrollPane2.setViewportView(Student.table2);
tabbedPane.add(CourseInfo,"课程信息");
CourseInfo.setLayout(new BorderLayout(0, 0));
toolBar_3 = new JToolBar();
CourseInfo.add(toolBar_3, BorderLayout.NORTH);
AddCourse = new JButton("添加课程信息");
AddCourse.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
c.addCourse();
}
});
toolBar_3.add(AddCourse);
UpdateCourse = new JButton("修改课程信息");
UpdateCourse.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
c.updateCourse();
}
});
toolBar_3.add(UpdateCourse);
DeleteCourse = new JButton("删除课程信息");
DeleteCourse.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
c.deleteCourse();
}
});
toolBar_3.add(DeleteCourse);
SearchCourse = new JButton("搜索课程信息");
SearchCourse.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
c.searchCourse();
}
});
toolBar_3.add(SearchCourse);
RefreshCourse = new JButton("刷新");
RefreshCourse.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
c.refreshCourse();
}
});
toolBar_3.add(RefreshCourse);
scrollPane3 = new JScrollPane();
CourseInfo.add(scrollPane3, BorderLayout.CENTER);
scrollPane3.setViewportView(Course.table3);
tabbedPane.add(SelectCourseInfo,"选课管理");
SelectCourseInfo.setLayout(new BorderLayout(0, 0));
toolBar_5 = new JToolBar();
SelectCourseInfo.add(toolBar_5, BorderLayout.NORTH);
AddSelCou = new JButton("添加选课信息");
AddSelCou.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
se.addSelCou();
}
});
toolBar_5.add(AddSelCou);
DeleteSelCou = new JButton("删除选课信息");
DeleteSelCou.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
se.deleteSelCou();
}
});
toolBar_5.add(DeleteSelCou);
SearchSelCou = new JButton("搜索选课信息");
SearchSelCou.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
se.searchSelCou();
}
});
toolBar_5.add(SearchSelCou);
RefreshSelCou = new JButton("刷新");
RefreshSelCou.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
se.refreshSelCou();
}
});
toolBar_5.add(RefreshSelCou);
scrollPane5 = new JScrollPane();
SelectCourseInfo.add(scrollPane5, BorderLayout.CENTER);
scrollPane5.setViewportView(SelectCourse.table5);
tabbedPane.add(ScoreInfo,"成绩管理");
ScoreInfo.setLayout(new BorderLayout(0, 0));
toolBar_4 = new JToolBar();
ScoreInfo.add(toolBar_4, BorderLayout.NORTH);
AddScore = new JButton("添加成绩信息");
AddScore.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
sc.addScore();
}
});
toolBar_4.add(AddScore);
UpdateScore = new JButton("修改成绩信息");
UpdateScore.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
sc.updateScore();
}
});
toolBar_4.add(UpdateScore);
DeleteScore = new JButton("删除成绩信息");
DeleteScore.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
sc.deleteScore();
}
});
toolBar_4.add(DeleteScore);
SearchScore = new JButton("搜索成绩信息");
SearchScore.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
sc.searchScore();
}
});
toolBar_4.add(SearchScore);
Statistical = new JButton("统计分析");
Statistical.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
sc.statistical();
}
});
toolBar_4.add(Statistical);
TotalRank = new JButton("总分排名");
TotalRank.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
sc.totalrank();
}
});
toolBar_4.add(TotalRank);
RefreshScore = new JButton("刷新");
RefreshScore.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
sc.refreshScore();
}
});
toolBar_4.add(RefreshScore);
scrollPane4 = new JScrollPane();
ScoreInfo.add(scrollPane4, BorderLayout.CENTER);
scrollPane4.setViewportView(Score.table4);
}
public static void main(String[] args) {
EventQueue.invokeLater(new Runnable() {
public void run() {
try {
SystemUI frame = new SystemUI();
frame.setVisible(true);
} catch (Exception e) {
e.printStackTrace();
}
}
});
}
public static void clickable() {
tabbedPane.setEnabled(true);
AddTeacher.setEnabled(true);
DeleteTeacher.setEnabled(true);
RefreshTeacher.setEnabled(true);
SearchTeacher.setEnabled(true);
UpdateTeacher.setEnabled(true);
AddStudent.setEnabled(true);
DeleteStudent.setEnabled(true);
RefreshStudent.setEnabled(true);
SearchStudent.setEnabled(true);
UpdateStudent.setEnabled(true);
AddCourse.setEnabled(true);
DeleteCourse.setEnabled(true);
RefreshCourse.setEnabled(true);
SearchCourse.setEnabled(true);
UpdateCourse.setEnabled(true);
AddScore.setEnabled(true);
DeleteScore.setEnabled(true);
RefreshScore.setEnabled(true);
SearchScore.setEnabled(true);
UpdateScore.setEnabled(true);
Statistical.setEnabled(true);
AddSelCou.setEnabled(true);
DeleteSelCou.setEnabled(true);
SearchSelCou.setEnabled(true);
RefreshSelCou.setEnabled(true);
TotalRank.setEnabled(true);
}
public static void unclickable() {
tabbedPane.setEnabled(false);
AddTeacher.setEnabled(false);
DeleteTeacher.setEnabled(false);
RefreshTeacher.setEnabled(false);
SearchTeacher.setEnabled(false);
UpdateTeacher.setEnabled(false);
AddStudent.setEnabled(false);
DeleteStudent.setEnabled(false);
RefreshStudent.setEnabled(false);
SearchStudent.setEnabled(false);
UpdateStudent.setEnabled(false);
AddCourse.setEnabled(false);
DeleteCourse.setEnabled(false);
RefreshCourse.setEnabled(false);
SearchCourse.setEnabled(false);
UpdateCourse.setEnabled(false);
AddScore.setEnabled(false);
DeleteScore.setEnabled(false);
RefreshScore.setEnabled(false);
SearchScore.setEnabled(false);
UpdateScore.setEnabled(false);
Statistical.setEnabled(false);
AddSelCou.setEnabled(false);
DeleteSelCou.setEnabled(false);
SearchSelCou.setEnabled(false);
RefreshSelCou.setEnabled(false);
TotalRank.setEnabled(false);
}
}
Teacher.java
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.ListSelectionModel;
import javax.swing.table.DefaultTableCellRenderer;
public class Teacher {
Vector<Object> rowData,columnNames;
static JTable table1=null;
//定义数据库需要的全局变量
PreparedStatement ps=null;
Connection ct=null;
ResultSet rs=null;
public Teacher(){
columnNames=new Vector<Object>();
//设置列名
columnNames.add("工号");
columnNames.add("姓名");
columnNames.add("性别");
columnNames.add("年龄");
columnNames.add("专业");
columnNames.add("工薪");
rowData = new Vector<Object>();
//rowData可以存放多行,开始从数据库里取
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
ps=ct.prepareStatement("select * from Teacher");
rs=ps.executeQuery();
while(rs.next()){
//rowData可以存放多行
Vector<Object> hang=new Vector<Object>();
hang.add(rs.getString(1));
hang.add(rs.getString(2));
hang.add(rs.getString(3));
hang.add(rs.getInt(4));
hang.add(rs.getString(5));
hang.add(rs.getInt(6));
//加入到rowData
rowData.add(hang);
}
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
table1 = new JTable(rowData,columnNames){
private static final long serialVersionUID = -3229560868878458304L;
public boolean isCellEditable(int row, int column)
{return false;}//表格不允许被编辑
};
table1.getSelectionModel().setSelectionMode(ListSelectionModel.SINGLE_SELECTION);//只允许选中一行
DefaultTableCellRenderer tcr = new DefaultTableCellRenderer();// 设置table内容居中
tcr.setHorizontalAlignment(JLabel.CENTER);// 这句和上句作用一样
table1.setDefaultRenderer(Object.class, tcr);
}
public void addTeacher() {
// TODO 自动生成的方法存根
SystemUI.unclickable();
JFrame AddTea = new JFrame("添加教师信息");
AddTea.setSize(250, 270);
AddTea.setLocation(600, 300);
JPanel addtea = new JPanel();
JLabel tno = new JLabel("工号");
JLabel tname = new JLabel("姓名");
JLabel tsex = new JLabel("性别");
JLabel tage = new JLabel("年龄");
JLabel tdept = new JLabel("专业");
JLabel tsalary = new JLabel("工薪");
JTextField tnotext = new JTextField();
JTextField tnametext = new JTextField();
JTextField tsextext = new JTextField();
JTextField tagetext = new JTextField();
JTextField tdepttext = new JTextField();
JTextField tsalarytext = new JTextField();
JButton ok = new JButton("确定");
JButton reset = new JButton("重置");
addtea.setLayout(null);
tno.setBounds(5,5,70,20);
tnotext.setBounds(80,5,120,20);
tname.setBounds(5,30,70,20);
tnametext.setBounds(80,30,120,20);
tsex.setBounds(5,60,70,20);
tsextext.setBounds(80,60,120,20);
tage.setBounds(5,90,70,20);
tagetext.setBounds(80,90,120,20);
tdept.setBounds(5,120,70,20);
tdepttext.setBounds(80,120,120,20);
tsalary.setBounds(5,150,70,20);
tsalarytext.setBounds(80,150,120,20);
ok.setBounds(50,190,60,20);
reset.setBounds(130,190,60,20);
addtea.add(tno);
addtea.add(tnotext);
addtea.add(tname);
addtea.add(tnametext);
addtea.add(tsex);
addtea.add(tsextext);
addtea.add(tage);
addtea.add(tagetext);
addtea.add(tdept);
addtea.add(tdepttext);
addtea.add(tsalary);
addtea.add(tsalarytext);
addtea.add(ok);
addtea.add(reset);
AddTea.add(addtea);
AddTea.setVisible(true);
AddTea.addWindowListener(new WindowAdapter(){
@Override
public void windowClosing(WindowEvent e) {
SystemUI.clickable();
}
});
ok.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
try {
String no = tnotext.getText();
String name = tnametext.getText();
String sex = tsextext.getText();
String dept = tdepttext.getText();
int age = Integer.valueOf(tagetext.getText()).intValue();
int salary = Integer.valueOf(tsalarytext.getText()).intValue();
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
ps=ct.prepareStatement("insert into [Teacher]([Tno],[Tname],[Tsex],[Tage],[Tdept],[Tsalary]) values('"+no+"','"+name+"','"+sex+"',"+age+",'"+dept+"',"+salary+")");
ps.executeUpdate();
JOptionPane.showMessageDialog(null, "添加成功！");
AddTea.dispose();
SystemUI.clickable();
refreshTeacher();
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "添加失败！");
SystemUI.unclickable();
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
});
reset.addActionListener(new ActionListener(){
@Override
public void actionPerformed(ActionEvent arg0) {
tnotext.setText("");
tnametext.setText("");
tsextext.setText("");
tagetext.setText("");
tdepttext.setText("");
tsalarytext.setText("");
}
});
}
public void updateTeacher() {
// TODO 自动生成的方法存根
SystemUI.unclickable();
JFrame UpdateTea = new JFrame("修改教师信息");
UpdateTea.setSize(250, 290);
UpdateTea.setLocation(600, 300);
JPanel updatetea = new JPanel();
JLabel ytno = new JLabel("要修改的工号");
JLabel tname = new JLabel("要改成的姓名");
JLabel tsex = new JLabel("要改成的性别");
JLabel tage = new JLabel("要改成的年龄");
JLabel tdept = new JLabel("要改成的专业");
JLabel tsalary = new JLabel("要改成的工薪");
JTextField ytnotext = new JTextField();
JTextField tnametext = new JTextField();
JTextField tsextext = new JTextField();
JTextField tagetext = new JTextField();
JTextField tdepttext = new JTextField();
JTextField tsalarytext = new JTextField();
JButton ok = new JButton("确定");
JButton reset = new JButton("重置");
updatetea.setLayout(null);
ytno.setBounds(5,30,100,20);
ytnotext.setBounds(110,30,120,20);
tname.setBounds(5,60,100,20);
tnametext.setBounds(110,60,120,20);
tsex.setBounds(5,90,100,20);
tsextext.setBounds(110,90,120,20);
tage.setBounds(5,120,100,20);
tagetext.setBounds(110,120,120,20);
tdept.setBounds(5,150,100,20);
tdepttext.setBounds(110,150,120,20);
tsalary.setBounds(5,180,100,20);
tsalarytext.setBounds(110,180,120,20);
ok.setBounds(50,210,60,20);
reset.setBounds(120,210,60,20);
updatetea.add(ytno);
updatetea.add(ytnotext);
updatetea.add(tname);
updatetea.add(tnametext);
updatetea.add(tsex);
updatetea.add(tsextext);
updatetea.add(tage);
updatetea.add(tagetext);
updatetea.add(tdept);
updatetea.add(tdepttext);
updatetea.add(tsalary);
updatetea.add(tsalarytext);
updatetea.add(ok);
updatetea.add(reset);
UpdateTea.add(updatetea);
UpdateTea.setVisible(true);
UpdateTea.addWindowListener(new WindowAdapter(){
@Override
public void windowClosing(WindowEvent e) {
SystemUI.clickable();
}
});
ok.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
try {
String yno = ytnotext.getText();
String name = tnametext.getText();
String sex = tsextext.getText();
String dept = tdepttext.getText();
int age = Integer.valueOf(tagetext.getText()).intValue();
int salary = Integer.valueOf(tsalarytext.getText()).intValue();
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
ps=ct.prepareStatement("update Teacher set Tname ='"+name+"',Tsex ='"+sex+"', Tdept ='"+dept+"', Tage ='"+age+"', Tsalary ='"+salary+"' where Tno='"+yno+"'");
ps.executeUpdate();
JOptionPane.showMessageDialog(null, "修改成功！");
UpdateTea.dispose();
SystemUI.clickable();
refreshTeacher();
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "修改失败！");
SystemUI.unclickable();
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
});
reset.addActionListener(new ActionListener(){
@Override
public void actionPerformed(ActionEvent arg0) {
ytnotext.setText("");
tnametext.setText("");
tsextext.setText("");
tagetext.setText("");
tdepttext.setText("");
tsalarytext.setText("");
}
});
}
public void deleteTeacher() {
// TODO 自动生成的方法存根
int row = table1.getSelectedRow();
if ( row >= 0 ) {
String string[] = new String[3];
string[0] = (String) table1.getValueAt(row, 0);
string[1] = (String) table1.getValueAt(row, 1);
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
ps=ct.prepareStatement("delete from Teacher where Tno='"+string[0]+"' and Tname='"+string[1]+"'");
ps.executeUpdate();
JOptionPane.showMessageDialog(null, "删除成功！");
refreshTeacher();
row=-1;
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "删除失败！");
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
else {
JOptionPane.showMessageDialog(null, "请选中要删除的行！");
}
}
public void searchTeacher() {
// TODO 自动生成的方法存根
SystemUI.unclickable();
JFrame SeaTea = new JFrame("查找教师信息");
SeaTea.setSize(250, 100);
SeaTea.setLocation(600, 300);
JPanel seatea = new JPanel();
JLabel tno = new JLabel("请输入工号");
JTextField tnotext = new JTextField();
JButton ok = new JButton("确定");
JButton reset = new JButton("重置");
seatea.setLayout(null);
tno.setBounds(5,5,70,20);
tnotext.setBounds(80,5,120,20);
ok.setBounds(50,30,60,20);
reset.setBounds(130,30,60,20);
seatea.add(tno);
seatea.add(tnotext);
seatea.add(ok);
seatea.add(reset);
SeaTea.add(seatea);
SeaTea.setVisible(true);
SeaTea.addWindowListener(new WindowAdapter(){
@Override
public void windowClosing(WindowEvent e) {
SystemUI.clickable();
}
});
ok.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
columnNames=new Vector<Object>();
//设置列名
columnNames.add("工号");
columnNames.add("姓名");
columnNames.add("性别");
columnNames.add("年龄");
columnNames.add("专业");
columnNames.add("工薪");
String str = tnotext.getText();
rowData = new Vector<Object>();
int count=0;
//rowData可以存放多行,开始从数据库里取
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
if(!tnotext.getText().trim().equals("")) {
ps=ct.prepareStatement("select * from Teacher where tno='"+str+"'");
rs=ps.executeQuery();
}
if(tnotext.getText().trim().equals("")) {
ps=ct.prepareStatement("select * from Teacher ");
rs=ps.executeQuery();
JOptionPane.showMessageDialog(null, "请输入查询信息！");
}
while(rs.next()){
//rowData可以存放多行
Vector<Object> hang=new Vector<Object>();
hang.add(rs.getString(1));
hang.add(rs.getString(2));
hang.add(rs.getString(3));
hang.add(rs.getInt(4));
hang.add(rs.getString(5));
hang.add(rs.getInt(6));
//加入到rowData
rowData.add(hang);
count++;
}
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "查询失败！");
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
if(count!=0) {
table1 = new JTable(rowData,columnNames){
private static final long serialVersionUID = -1545099432791772807L;
public boolean isCellEditable(int row, int column)
{return false;}//表格不允许被编辑
};
table1.getSelectionModel().setSelectionMode(ListSelectionModel.SINGLE_SELECTION);//只允许选中一行
DefaultTableCellRenderer tcr = new DefaultTableCellRenderer();// 设置table内容居中
tcr.setHorizontalAlignment(JLabel.CENTER);// 这句和上句作用一样
table1.setDefaultRenderer(Object.class, tcr);
SystemUI.scrollPane1.setViewportView(table1);
SeaTea.dispose();
SystemUI.clickable();}
if(count==0) {
JOptionPane.showMessageDialog(null, "查无此人！");
}
}
});
reset.addActionListener(new ActionListener(){
@Override
public void actionPerformed(ActionEvent arg0) {
tnotext.setText("");
}
});
}
public void refreshTeacher() {
// TODO 自动生成的方法存根
new Teacher();
SystemUI.scrollPane1.setViewportView(Teacher.table1);
}
}
Student.java
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.ListSelectionModel;
import javax.swing.table.DefaultTableCellRenderer;
public class Student {
Vector<Object> rowData,columnNames;
static JTable table2=null;
//定义数据库需要的全局变量
PreparedStatement ps=null;
Connection ct=null;
ResultSet rs=null;
public Student(){
columnNames=new Vector<Object>();
//设置列名
columnNames.add("学号");
columnNames.add("姓名");
columnNames.add("性别");
columnNames.add("年龄");
columnNames.add("专业");
columnNames.add("班级");
rowData = new Vector<Object>();
//rowData可以存放多行,开始从数据库里取
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
ps=ct.prepareStatement("select * from Student");
rs=ps.executeQuery();
while(rs.next()){
//rowData可以存放多行
Vector<Object> hang=new Vector<Object>();
hang.add(rs.getString(1));
hang.add(rs.getString(2));
hang.add(rs.getString(3));
hang.add(rs.getInt(4));
hang.add(rs.getString(5));
hang.add(rs.getString(6));
//加入到rowData
rowData.add(hang);
}
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
table2 = new JTable(rowData,columnNames){
private static final long serialVersionUID = -4333506764666173598L;
public boolean isCellEditable(int row, int column)
{return false;}//表格不允许被编辑
};
table2.getSelectionModel().setSelectionMode(ListSelectionModel.SINGLE_SELECTION);//只允许选中一行
DefaultTableCellRenderer tcr = new DefaultTableCellRenderer();// 设置table内容居中
tcr.setHorizontalAlignment(JLabel.CENTER);// 这句和上句作用一样
table2.setDefaultRenderer(Object.class, tcr);
}
public void refreshStudent() {
// TODO 自动生成的方法存根
new Student();
SystemUI.scrollPane2.setViewportView(Student.table2);
}
public void searchStudent() {
// TODO 自动生成的方法存根
SystemUI.unclickable();
JFrame SeaStu = new JFrame("查找学生信息");
SeaStu.setSize(250, 100);
SeaStu.setLocation(600, 300);
JPanel seastu = new JPanel();
JLabel sno = new JLabel("请输入学号");
JTextField snotext = new JTextField();
JButton ok = new JButton("确定");
JButton reset = new JButton("重置");
seastu.setLayout(null);
sno.setBounds(5,5,70,20);
snotext.setBounds(80,5,120,20);
ok.setBounds(50,30,60,20);
reset.setBounds(130,30,60,20);
seastu.add(sno);
seastu.add(snotext);
seastu.add(ok);
seastu.add(reset);
SeaStu.add(seastu);
SeaStu.setVisible(true);
SeaStu.addWindowListener(new WindowAdapter(){
@Override
public void windowClosing(WindowEvent e) {
SystemUI.clickable();
}
});
ok.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
columnNames=new Vector<Object>();
//设置列名
columnNames.add("学号");
columnNames.add("姓名");
columnNames.add("性别");
columnNames.add("年龄");
columnNames.add("专业");
columnNames.add("班级");
String str = snotext.getText();
rowData = new Vector<Object>();
int count=0;
//rowData可以存放多行,开始从数据库里取
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
if(!snotext.getText().trim().equals("")) {
ps=ct.prepareStatement("select * from Student where sno='"+str+"'");
rs=ps.executeQuery();
}
if(snotext.getText().trim().equals("")) {
ps=ct.prepareStatement("select * from Student ");
JOptionPane.showMessageDialog(null, "请输入查询信息！");
rs=ps.executeQuery();
}
while(rs.next()){
//rowData可以存放多行
Vector<Object> hang=new Vector<Object>();
hang.add(rs.getString(1));
hang.add(rs.getString(2));
hang.add(rs.getString(3));
hang.add(rs.getInt(4));
hang.add(rs.getString(5));
hang.add(rs.getString(6));
//加入到rowData
rowData.add(hang);
count++;
}
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "查询失败！");
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
if(count!=0) {
table2 = new JTable(rowData,columnNames){
private static final long serialVersionUID = -8131400305374145108L;
public boolean isCellEditable(int row, int column)
{return false;}//表格不允许被编辑
};
table2.getSelectionModel().setSelectionMode(ListSelectionModel.SINGLE_SELECTION);//只允许选中一行
DefaultTableCellRenderer tcr = new DefaultTableCellRenderer();// 设置table内容居中
tcr.setHorizontalAlignment(JLabel.CENTER);// 这句和上句作用一样
table2.setDefaultRenderer(Object.class, tcr);
SystemUI.scrollPane2.setViewportView(table2);
SeaStu.dispose();
SystemUI.clickable();}
if(count==0) {
JOptionPane.showMessageDialog(null, "查无此人！");
}
}
});
reset.addActionListener(new ActionListener(){
@Override
public void actionPerformed(ActionEvent arg0) {
snotext.setText("");
}
});
}
public void deleteStudent() {
// TODO 自动生成的方法存根
int row = table2.getSelectedRow();
if ( row >= 0 ) {
String string[] = new String[3];
string[0] = (String) table2.getValueAt(row, 0);
string[1] = (String) table2.getValueAt(row, 1);
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
ps=ct.prepareStatement("delete from Student where Sno='"+string[0]+"' and Sname='"+string[1]+"'");
ps.executeUpdate();
JOptionPane.showMessageDialog(null, "删除成功！");
refreshStudent();
row=-1;
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "删除失败！");
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
else {
JOptionPane.showMessageDialog(null, "请选中要删除的行！");
}
}
public void addStudent() {
// TODO 自动生成的方法存根
SystemUI.unclickable();
JFrame AddStu = new JFrame("添加学生信息");
AddStu.setSize(250, 270);
AddStu.setLocation(600, 300);
JPanel addStu = new JPanel();
JLabel sno = new JLabel("学号");
JLabel sname = new JLabel("姓名");
JLabel ssex = new JLabel("性别");
JLabel sage = new JLabel("年龄");
JLabel sdept = new JLabel("专业");
JLabel sclass = new JLabel("班级");
JTextField snotext = new JTextField();
JTextField snametext = new JTextField();
JTextField ssextext = new JTextField();
JTextField sagetext = new JTextField();
JTextField sdepttext = new JTextField();
JTextField sclasstext = new JTextField();
JButton ok = new JButton("确定");
JButton reset = new JButton("重置");
addStu.setLayout(null);
sno.setBounds(5,5,70,20);
snotext.setBounds(80,5,120,20);
sname.setBounds(5,30,70,20);
snametext.setBounds(80,30,120,20);
ssex.setBounds(5,60,70,20);
ssextext.setBounds(80,60,120,20);
sage.setBounds(5,90,70,20);
sagetext.setBounds(80,90,120,20);
sdept.setBounds(5,120,70,20);
sdepttext.setBounds(80,120,120,20);
sclass.setBounds(5,150,70,20);
sclasstext.setBounds(80,150,120,20);
ok.setBounds(50,190,60,20);
reset.setBounds(130,190,60,20);
addStu.add(sno);
addStu.add(snotext);
addStu.add(sname);
addStu.add(snametext);
addStu.add(ssex);
addStu.add(ssextext);
addStu.add(sage);
addStu.add(sagetext);
addStu.add(sdept);
addStu.add(sdepttext);
addStu.add(sclass);
addStu.add(sclasstext);
addStu.add(ok);
addStu.add(reset);
AddStu.add(addStu);
AddStu.setVisible(true);
AddStu.addWindowListener(new WindowAdapter(){
@Override
public void windowClosing(WindowEvent e) {
SystemUI.clickable();
}
});
ok.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
try {
String no = snotext.getText();
String name = snametext.getText();
String sex = ssextext.getText();
String dept = sdepttext.getText();
String sclass = sclasstext.getText();
int age = Integer.valueOf(sagetext.getText()).intValue();
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
ps=ct.prepareStatement("insert into [Student]([Sno],[Sname],[Ssex],[Sage],[Sdept],[Class]) values('"+no+"','"+name+"','"+sex+"',"+age+",'"+dept+"','"+sclass+"')");
ps.executeUpdate();
JOptionPane.showMessageDialog(null, "添加成功！");
AddStu.dispose();
SystemUI.clickable();
refreshStudent();
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "添加失败！");
SystemUI.unclickable();
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
});
reset.addActionListener(new ActionListener(){
@Override
public void actionPerformed(ActionEvent arg0) {
snotext.setText("");
snametext.setText("");
ssextext.setText("");
sagetext.setText("");
sdepttext.setText("");
sclasstext.setText("");
}
});
}
public void updateStudent() {
// TODO 自动生成的方法存根
SystemUI.unclickable();
JFrame UpdateStu = new JFrame("修改学生信息");
UpdateStu.setSize(250, 250);
UpdateStu.setLocation(600, 300);
JPanel updatestu = new JPanel();
JLabel syno = new JLabel("要修改的学号");
JLabel ssex = new JLabel("要改成的性别");
JLabel sage = new JLabel("要改成的年龄");
JLabel sdept = new JLabel("要改成的专业");
JLabel sclas = new JLabel("要改成的班级");
JTextField synotext = new JTextField();
JTextField ssextext = new JTextField();
JTextField sagetext = new JTextField();
JTextField sdepttext = new JTextField();
JTextField sclastext = new JTextField();
JButton ok = new JButton("确定");
JButton reset = new JButton("重置");
updatestu.setLayout(null);
syno.setBounds(5,5,100,20);
synotext.setBounds(110,5,120,20);
ssex.setBounds(5,30,100,20);
ssextext.setBounds(110,30,120,20);
sage.setBounds(5,60,100,20);
sagetext.setBounds(110,60,120,20);
sdept.setBounds(5,90,100,20);
sdepttext.setBounds(110,90,120,20);
sclas.setBounds(5,120,100,20);
sclastext.setBounds(110,120,120,20);
ok.setBounds(50,170,60,20);
reset.setBounds(120,170,60,20);
updatestu.add(syno);
updatestu.add(synotext);
updatestu.add(ssex);
updatestu.add(ssextext);
updatestu.add(sage);
updatestu.add(sagetext);
updatestu.add(sdept);
updatestu.add(sdepttext);
updatestu.add(sclas);
updatestu.add(sclastext);
updatestu.add(ok);
updatestu.add(reset);
UpdateStu.add(updatestu);
UpdateStu.setVisible(true);
UpdateStu.addWindowListener(new WindowAdapter(){
@Override
public void windowClosing(WindowEvent e) {
SystemUI.clickable();
}
});
ok.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
try {
String yno = synotext.getText();
String sex = ssextext.getText();
String dept = sdepttext.getText();
String ssclas = sclastext.getText();
int age = Integer.valueOf(sagetext.getText()).intValue();
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
ps=ct.prepareStatement("update Student set Ssex ='"+sex+"', Sdept ='"+dept+"', Sage ='"+age+"', Class ='"+ssclas+"' where Sno='"+yno+"'");
ps.executeUpdate();
JOptionPane.showMessageDialog(null, "修改成功！");
UpdateStu.dispose();
SystemUI.clickable();
refreshStudent();
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "修改失败！");
SystemUI.unclickable();
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
});
reset.addActionListener(new ActionListener(){
@Override
public void actionPerformed(ActionEvent arg0) {
ssextext.setText("");
sagetext.setText("");
sdepttext.setText("");
sclastext.setText("");
}
});
}
}
Course.java
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.ListSelectionModel;
import javax.swing.table.DefaultTableCellRenderer;
public class Course {
Vector<Object> rowData,columnNames;
//定义数据库需要的全局变量
PreparedStatement ps=null;
Connection ct=null;
ResultSet rs=null;
static JTable table3=null;
public Course(){
columnNames=new Vector<Object>();
//设置列名
columnNames.add("课程号");
columnNames.add("课程名");
columnNames.add("先修课课程号");
columnNames.add("学分");
columnNames.add("类型");
rowData = new Vector<Object>();
//rowData可以存放多行,开始从数据库里取
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
ps=ct.prepareStatement("select * from Course");
rs=ps.executeQuery();
while(rs.next()){
//rowData可以存放多行
Vector<Object> hang=new Vector<Object>();
hang.add(rs.getString(1));
hang.add(rs.getString(2));
hang.add(rs.getString(3));
hang.add(rs.getInt(4));
hang.add(rs.getString(5));
//加入到rowData
rowData.add(hang);
}
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
table3 = new JTable(rowData,columnNames){
private static final long serialVersionUID = 6843971351064905400L;
public boolean isCellEditable(int row, int column)
{return false;}//表格不允许被编辑
};
table3.getSelectionModel().setSelectionMode(ListSelectionModel.SINGLE_SELECTION);//只允许选中一行
DefaultTableCellRenderer tcr = new DefaultTableCellRenderer();// 设置table内容居中
tcr.setHorizontalAlignment(JLabel.CENTER);// 这句和上句作用一样
table3.setDefaultRenderer(Object.class, tcr);
}
public void addCourse() {
// TODO 自动生成的方法存根
SystemUI.unclickable();
JFrame AddCou = new JFrame("添加课程信息");
AddCou.setSize(250, 250);
AddCou.setLocation(600, 300);
JPanel addCou = new JPanel();
JLabel cno = new JLabel("课程号");
JLabel cname = new JLabel("课程名");
JLabel cpno = new JLabel("先修课");
JLabel credit = new JLabel("学分");
JLabel cremark = new JLabel("类型");
JTextField cnotext = new JTextField();
JTextField cnametext = new JTextField();
JTextField cpnotext = new JTextField();
JTextField credittext = new JTextField();
JTextField cremarktext = new JTextField();
JButton ok = new JButton("确定");
JButton reset = new JButton("重置");
addCou.setLayout(null);
cno.setBounds(5,5,70,20);
cnotext.setBounds(80,5,120,20);
cname.setBounds(5,30,70,20);
cnametext.setBounds(80,30,120,20);
cpno.setBounds(5,60,70,20);
cpnotext.setBounds(80,60,120,20);
credit.setBounds(5,90,70,20);
credittext.setBounds(80,90,120,20);
cremark.setBounds(5,120,70,20);
cremarktext.setBounds(80,120,120,20);
ok.setBounds(50,150,60,20);
reset.setBounds(130,150,60,20);
addCou.add(cno);
addCou.add(cnotext);
addCou.add(cname);
addCou.add(cnametext);
addCou.add(cpno);
addCou.add(cpnotext);
addCou.add(credit);
addCou.add(credittext);
addCou.add(cremark);
addCou.add(cremarktext);
addCou.add(ok);
addCou.add(reset);
AddCou.add(addCou);
AddCou.setVisible(true);
AddCou.addWindowListener(new WindowAdapter(){
@Override
public void windowClosing(WindowEvent e) {
SystemUI.clickable();
}
});
ok.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
try {
String no = cnotext.getText();
String name = cnametext.getText();
String pno = cpnotext.getText();
String remark = cremarktext.getText();
int credit = Integer.valueOf(credittext.getText()).intValue();
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
String sql;
sql = "insert into [Course]([Cno],[Cname],[Cpno],[Credit],[Remarks]) values('"+no+"','"+name+"','"+pno+"',"+credit+",'"+remark+"')";
ps=ct.prepareStatement(sql);
ps.executeUpdate();
JOptionPane.showMessageDialog(null, "添加成功！");
AddCou.dispose();
SystemUI.clickable();
refreshCourse();
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "添加失败！");
SystemUI.unclickable();
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
});
reset.addActionListener(new ActionListener(){
@Override
public void actionPerformed(ActionEvent arg0) {
cnotext.setText("");
cnametext.setText("");
cpnotext.setText("");
credittext.setText("");
cremarktext.setText("");
}
});
}
public void updateCourse() {
// TODO 自动生成的方法存根
SystemUI.unclickable();
JFrame UpdateCou = new JFrame("修改课程信息");
UpdateCou.setSize(250, 200);
UpdateCou.setLocation(600, 300);
JPanel updatecou = new JPanel();
JLabel ycno = new JLabel("要修改的课程号");
JLabel cpno = new JLabel("要改成的先修课");
JLabel credit = new JLabel("要改成的学分");
JLabel cremark = new JLabel("要改成的类型");
JTextField ycnotext = new JTextField();
JTextField cpnotext = new JTextField();
JTextField credittext = new JTextField();
JTextField cremarktext = new JTextField();
JButton ok = new JButton("确定");
JButton reset = new JButton("重置");
updatecou.setLayout(null);
ycno.setBounds(5,5,100,20);
ycnotext.setBounds(110,5,120,20);
cpno.setBounds(5,30,100,20);
cpnotext.setBounds(110,30,120,20);
credit.setBounds(5,60,100,20);
credittext.setBounds(110,60,120,20);
cremark.setBounds(5,90,100,20);
cremarktext.setBounds(110,90,120,20);
ok.setBounds(50,130,60,20);
reset.setBounds(120,130,60,20);
updatecou.add(ycno);
updatecou.add(ycnotext);
updatecou.add(cpno);
updatecou.add(cpnotext);
updatecou.add(credit);
updatecou.add(credittext);
updatecou.add(cremark);
updatecou.add(cremarktext);
updatecou.add(ok);
updatecou.add(reset);
UpdateCou.add(updatecou);
UpdateCou.setVisible(true);
UpdateCou.addWindowListener(new WindowAdapter(){
@Override
public void windowClosing(WindowEvent e) {
SystemUI.clickable();
}
});
ok.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
try {
String yno = ycnotext.getText();
String pno = cpnotext.getText();
String remark = cremarktext.getText();
int credit = Integer.valueOf(credittext.getText()).intValue();
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
String sql;
sql = "update Course set Cpno ='"+pno+"', Credit ='"+credit+"', Remarks ='"+remark+"' where Cno='"+yno+"'";
ps=ct.prepareStatement(sql);
ps.executeUpdate();
JOptionPane.showMessageDialog(null, "修改成功！");
UpdateCou.dispose();
SystemUI.clickable();
refreshCourse();
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "修改失败！");
SystemUI.unclickable();
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
});
reset.addActionListener(new ActionListener(){
@Override
public void actionPerformed(ActionEvent arg0) {
ycnotext.setText("");
cpnotext.setText("");
credittext.setText("");
cremarktext.setText("");
}
});
}
public void deleteCourse() {
// TODO 自动生成的方法存根
int row = table3.getSelectedRow();
if ( row >= 0 ) {
String string[] = new String[3];
string[0] = (String) table3.getValueAt(row, 0);
string[1] = (String) table3.getValueAt(row, 1);
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
ps=ct.prepareStatement("delete from Course where Cno='"+string[0]+"' and Cname='"+string[1]+"'");
ps.executeUpdate();
JOptionPane.showMessageDialog(null, "删除成功！");
refreshCourse();
row=-1;
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "删除失败！");
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
else {
JOptionPane.showMessageDialog(null, "请选中要删除的行！");
}
}
public void searchCourse() {
// TODO 自动生成的方法存根
SystemUI.unclickable();
JFrame SeaCou = new JFrame("查找课程信息");
SeaCou.setSize(250, 100);
SeaCou.setLocation(600, 300);
JPanel seacou = new JPanel();
JLabel cno = new JLabel("请输入课程号");
JTextField cnotext = new JTextField();
JButton ok = new JButton("确定");
JButton reset = new JButton("重置");
seacou.setLayout(null);
cno.setBounds(5,5,80,20);
cnotext.setBounds(90,5,120,20);
ok.setBounds(50,30,60,20);
reset.setBounds(130,30,60,20);
seacou.add(cno);
seacou.add(cnotext);
seacou.add(ok);
seacou.add(reset);
SeaCou.add(seacou);
SeaCou.setVisible(true);
SeaCou.addWindowListener(new WindowAdapter(){
@Override
public void windowClosing(WindowEvent e) {
SystemUI.clickable();
}
});
ok.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
columnNames=new Vector<Object>();
//设置列名
columnNames.add("课程号");
columnNames.add("课程名");
columnNames.add("先修课课程号");
columnNames.add("学分");
columnNames.add("类型");
String str = cnotext.getText();
rowData = new Vector<Object>();
int count=0;
//rowData可以存放多行,开始从数据库里取
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
if(!cnotext.getText().trim().equals("")) {
ps=ct.prepareStatement("select * from Course where cno='"+str+"'");
rs=ps.executeQuery();
}
else if(cnotext.getText().trim().equals("")) {
ps=ct.prepareStatement("select * from Course ");
rs=ps.executeQuery();
JOptionPane.showMessageDialog(null, "请输入查询信息！");
}
while(rs.next()){
//rowData可以存放多行
Vector<Object> hang=new Vector<Object>();
hang.add(rs.getString(1));
hang.add(rs.getString(2));
hang.add(rs.getString(3));
hang.add(rs.getInt(4));
hang.add(rs.getString(5));
//加入到rowData
rowData.add(hang);
count++;
}
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "查询失败！");
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
if(count!=0) {
table3 = new JTable(rowData,columnNames){
private static final long serialVersionUID = -8656035973692724899L;
public boolean isCellEditable(int row, int column)
{return false;}//表格不允许被编辑
};
table3.getSelectionModel().setSelectionMode(ListSelectionModel.SINGLE_SELECTION);//只允许选中一行
DefaultTableCellRenderer tcr = new DefaultTableCellRenderer();// 设置table内容居中
tcr.setHorizontalAlignment(JLabel.CENTER);// 这句和上句作用一样
table3.setDefaultRenderer(Object.class, tcr);
SystemUI.scrollPane3.setViewportView(table3);
SeaCou.dispose();
SystemUI.clickable();}
if(count==0) {
JOptionPane.showMessageDialog(null, "没有查询到相关消息！");
}
}
});
reset.addActionListener(new ActionListener(){
@Override
public void actionPerformed(ActionEvent arg0) {
cnotext.setText("");
}
});
}
public void refreshCourse() {
// TODO 自动生成的方法存根
new Course();
SystemUI.scrollPane3.setViewportView(Course.table3);
}
}
SelectCourse.java
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.ListSelectionModel;
import javax.swing.table.DefaultTableCellRenderer;
public class SelectCourse {
Vector<Object> rowData,columnNames;
//定义数据库需要的全局变量
PreparedStatement ps=null;
Connection ct=null;
ResultSet rs=null;
static JTable table5=null;
public SelectCourse(){
columnNames=new Vector<Object>();
//设置列名
columnNames.add("学号");
columnNames.add("姓名");
columnNames.add("课程号");
columnNames.add("课程名");
rowData = new Vector<Object>();
//rowData可以存放多行,开始从数据库里取
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
ps=ct.prepareStatement("select * from SelectCourse");
rs=ps.executeQuery();
while(rs.next()){
//rowData可以存放多行
Vector<Object> hang=new Vector<Object>();
hang.add(rs.getString(1));
hang.add(rs.getString(2));
hang.add(rs.getString(3));
hang.add(rs.getString(4));
//加入到rowData
rowData.add(hang);
}
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
table5 = new JTable(rowData,columnNames){
private static final long serialVersionUID = -8736911822989769111L;
public boolean isCellEditable(int row, int column)
{return false;}//表格不允许被编辑
};
table5.getSelectionModel().setSelectionMode(ListSelectionModel.SINGLE_SELECTION);//只允许选中一行
DefaultTableCellRenderer tcr = new DefaultTableCellRenderer();// 设置table内容居中
tcr.setHorizontalAlignment(JLabel.CENTER);// 这句和上句作用一样
table5.setDefaultRenderer(Object.class, tcr);
}
public void addSelCou() {
// TODO 自动生成的方法存根
SystemUI.unclickable();
JFrame Addse = new JFrame("添加选课信息");
Addse.setSize(250, 150);
Addse.setLocation(600, 300);
JPanel addse = new JPanel();
JLabel sesno = new JLabel("学号");
JLabel secno = new JLabel("课程号");
JTextField sesnotext = new JTextField();
JTextField secnotext = new JTextField();
JButton ok = new JButton("确定");
JButton reset = new JButton("重置");
addse.setLayout(null);
sesno.setBounds(5,5,70,20);
sesnotext.setBounds(80,5,120,20);
secno.setBounds(5,30,70,20);
secnotext.setBounds(80,30,120,20);
ok.setBounds(50,70,60,20);
reset.setBounds(130,70,60,20);
addse.add(sesno);
addse.add(sesnotext);
addse.add(secno);
addse.add(secnotext);
addse.add(ok);
addse.add(reset);
Addse.add(addse);
Addse.setVisible(true);
Addse.addWindowListener(new WindowAdapter(){
@Override
public void windowClosing(WindowEvent e) {
SystemUI.clickable();
}
});
ok.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
try {
String sno = sesnotext.getText();
String cno = secnotext.getText();
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
ps=ct.prepareStatement("insert into [SelectCourse]([Sno],[Sname],[Cno],[Cname]) values('"+sno+"',(select Sname from Student where Sno='"+sno+"'),'"+cno+"',(select Cname from Course where Cno='"+cno+"'))");
ps.executeUpdate();
JOptionPane.showMessageDialog(null, "添加成功！");
Addse.dispose();
SystemUI.clickable();
refreshSelCou();
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "添加失败！");
SystemUI.unclickable();
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
});
reset.addActionListener(new ActionListener(){
@Override
public void actionPerformed(ActionEvent arg0) {
sesnotext.setText("");
secnotext.setText("");
}
});
}
public void deleteSelCou() {
// TODO 自动生成的方法存根
int row = table5.getSelectedRow();
if ( row >= 0 ) {
String string[] = new String[4];
string[0] = (String) table5.getValueAt(row, 0);
string[1] = (String) table5.getValueAt(row, 1);
string[2] = (String) table5.getValueAt(row, 2);
string[3] = (String) table5.getValueAt(row, 3);
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
ps=ct.prepareStatement("delete from SelectCourse where Sno='"+string[0]+"' and Sname='"+string[1]+"' and Cno='"+string[2]+"' and Cname='"+string[3]+"'");
ps.executeUpdate();
JOptionPane.showMessageDialog(null, "删除成功！");
refreshSelCou();
row=-1;
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "删除失败！");
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
else {
JOptionPane.showMessageDialog(null, "请选中要删除的行！");
}
}
public void searchSelCou() {
// TODO 自动生成的方法存根
SystemUI.unclickable();
JFrame SeaSelCou = new JFrame("查找课程信息");
SeaSelCou.setSize(250, 150);
SeaSelCou.setLocation(600, 300);
JPanel seaselcou = new JPanel();
JLabel cno = new JLabel("请输入课程号");
JTextField cnotext = new JTextField();
JLabel sno = new JLabel("请输入学号");
JTextField snotext = new JTextField();
JButton ok = new JButton("确定");
JButton reset = new JButton("重置");
seaselcou.setLayout(null);
cno.setBounds(5,5,80,20);
cnotext.setBounds(90,5,120,20);
sno.setBounds(5,30,80,20);
snotext.setBounds(90,30,120,20);
ok.setBounds(50,70,60,20);
reset.setBounds(130,70,60,20);
seaselcou.add(cno);
seaselcou.add(cnotext);
seaselcou.add(sno);
seaselcou.add(snotext);
seaselcou.add(ok);
seaselcou.add(reset);
SeaSelCou.add(seaselcou);
SeaSelCou.setVisible(true);
SeaSelCou.addWindowListener(new WindowAdapter(){
@Override
public void windowClosing(WindowEvent e) {
SystemUI.clickable();
}
});
ok.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
columnNames=new Vector<Object>();
//设置列名
columnNames.add("学号");
columnNames.add("姓名");
columnNames.add("课程号");
columnNames.add("课程名");
String str = cnotext.getText();
String str1 = snotext.getText();
rowData = new Vector<Object>();
int count=0;
//rowData可以存放多行,开始从数据库里取
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
if(snotext.getText().trim().equals("")&&!cnotext.getText().trim().equals("")){
ps=ct.prepareStatement("select * from SelectCourse where cno='"+str+"'");
rs=ps.executeQuery();
}
if(cnotext.getText().trim().equals("")&&!snotext.getText().trim().equals("")){
ps=ct.prepareStatement("select * from SelectCourse where sno='"+str1+"'");
rs=ps.executeQuery();
}
if(!cnotext.getText().trim().equals("")&&!snotext.getText().trim().equals("")){
ps=ct.prepareStatement("select * from SelectCourse where sno='"+str1+"' and cno='"+str+"'");
rs=ps.executeQuery();
}
if(cnotext.getText().trim().equals("")&&snotext.getText().trim().equals("")){
ps=ct.prepareStatement("select * from SelectCourse");
rs=ps.executeQuery();
JOptionPane.showMessageDialog(null, "请输入查询信息！");
}
while(rs.next()){
//rowData可以存放多行
Vector<Object> hang=new Vector<Object>();
hang.add(rs.getString(1));
hang.add(rs.getString(2));
hang.add(rs.getString(3));
hang.add(rs.getString(4));
//加入到rowData
rowData.add(hang);
count++;
}
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "查询失败！");
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
if(count!=0) {
table5 = new JTable(rowData,columnNames){
private static final long serialVersionUID = -3692229347549819226L;
public boolean isCellEditable(int row, int column)
{return false;}//表格不允许被编辑
};
table5.getSelectionModel().setSelectionMode(ListSelectionModel.SINGLE_SELECTION);//只允许选中一行
DefaultTableCellRenderer tcr = new DefaultTableCellRenderer();// 设置table内容居中
tcr.setHorizontalAlignment(JLabel.CENTER);// 这句和上句作用一样
table5.setDefaultRenderer(Object.class, tcr);
SystemUI.scrollPane5.setViewportView(table5);
SeaSelCou.dispose();
SystemUI.clickable();}
if(count==0) {
JOptionPane.showMessageDialog(null, "没有查询到相关信息！");
}
}
});
reset.addActionListener(new ActionListener(){
@Override
public void actionPerformed(ActionEvent arg0) {
cnotext.setText("");
snotext.setText("");
}
});
}
public void refreshSelCou() {
// TODO 自动生成的方法存根
new SelectCourse();
SystemUI.scrollPane5.setViewportView(SelectCourse.table5);
}
}
Score.java
import java.awt.BorderLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.Vector;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JInternalFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.JToolBar;
import javax.swing.ListSelectionModel;
import javax.swing.event.InternalFrameAdapter;
import javax.swing.event.InternalFrameEvent;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.DefaultTableModel;
public class Score {
Vector<Object> rowData;
Vector<Object> columnNames;
//定义数据库需要的全局变量
PreparedStatement ps=null;
Connection ct=null;
ResultSet rs=null;
static JTable table4=null;
static JTable table6=null;
static JTable table7=null;
public Score(){
columnNames=new Vector<Object>();
//设置列名
columnNames.add("学号");
columnNames.add("姓名");
columnNames.add("课程号");
columnNames.add("课程名");
columnNames.add("成绩");
rowData = new Vector<Object>();
//rowData可以存放多行,开始从数据库里取
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
ps=ct.prepareStatement("select * from SC");
rs=ps.executeQuery();
while(rs.next()){
//rowData可以存放多行
Vector<Object> hang=new Vector<Object>();
hang.add(rs.getString(1));
hang.add(rs.getString(2));
hang.add(rs.getString(3));
hang.add(rs.getString(4));
hang.add(rs.getInt(5));
//加入到rowData
rowData.add(hang);
}
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
table4 = new JTable(rowData,columnNames){
private static final long serialVersionUID = 1525421110274312344L;
public boolean isCellEditable(int row, int column)
{return false;}//表格不允许被编辑
};
table4.getSelectionModel().setSelectionMode(ListSelectionModel.SINGLE_SELECTION);//只允许选中一行
DefaultTableCellRenderer tcr = new DefaultTableCellRenderer();// 设置table内容居中
tcr.setHorizontalAlignment(JLabel.CENTER);// 这句和上句作用一样
table4.setDefaultRenderer(Object.class, tcr);
}
public void addScore() {
// TODO 自动生成的方法存根
SystemUI.unclickable();
JFrame Addsc = new JFrame("添加成绩信息");
Addsc.setSize(250, 180);
Addsc.setLocation(600, 300);
JPanel addsc = new JPanel();
JLabel scsno = new JLabel("学号");
JLabel sccno = new JLabel("课程号");
JLabel score = new JLabel("成绩");
JTextField scsnotext = new JTextField();
JTextField sccnotext = new JTextField();
JTextField scoretext = new JTextField();
JButton ok = new JButton("确定");
JButton reset = new JButton("重置");
addsc.setLayout(null);
scsno.setBounds(5,5,70,20);
scsnotext.setBounds(80,5,120,20);
sccno.setBounds(5,30,70,20);
sccnotext.setBounds(80,30,120,20);
score.setBounds(5,60,70,20);
scoretext.setBounds(80,60,120,20);
ok.setBounds(50,100,60,20);
reset.setBounds(130,100,60,20);
addsc.add(scsno);
addsc.add(scsnotext);
addsc.add(sccno);
addsc.add(sccnotext);
addsc.add(score);
addsc.add(scoretext);
addsc.add(ok);
addsc.add(reset);
Addsc.add(addsc);
Addsc.setVisible(true);
Addsc.addWindowListener(new WindowAdapter(){
@Override
public void windowClosing(WindowEvent e) {
SystemUI.clickable();
}
});
ok.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
try {
String sno = scsnotext.getText();
String cno = sccnotext.getText();
int score = Integer.valueOf(scoretext.getText()).intValue();
int count=0;
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
ps=ct.prepareStatement("select Sname from SelectCourse where Sno='"+sno+"' and Cno='"+cno+"'");
rs=ps.executeQuery();
while(rs.next()){
count++;
}
if(count==0) {
JOptionPane.showMessageDialog(null, "此人没有选此课程！");
}
if(count!=0) {
ps=ct.prepareStatement("insert into [SC]([Sno],[Sname],[Cno],[Cname],[Score]) values('"+sno+"',(select Sname from SelectCourse where Sno='"+sno+"' and Cno='"+cno+"'),'"+cno+"',(select Cname from SelectCourse where Cno='"+cno+"' and Sno='"+sno+"'),"+score+")");
ps.executeUpdate();
JOptionPane.showMessageDialog(null, "添加成功！");
Addsc.dispose();
SystemUI.clickable();
refreshScore();
}
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "添加失败！");
SystemUI.unclickable();
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
});
reset.addActionListener(new ActionListener(){
@Override
public void actionPerformed(ActionEvent arg0) {
scsnotext.setText("");
sccnotext.setText("");
scoretext.setText("");
}
});
}
public void updateScore() {
// TODO 自动生成的方法存根
SystemUI.unclickable();
JFrame UpdateSC = new JFrame("修改成绩信息");
UpdateSC.setSize(250, 240);
UpdateSC.setLocation(600, 300);
JPanel updatesc = new JPanel();
JLabel yscsno = new JLabel("要修改的学号");
JLabel scsno = new JLabel("要改成的学号");
JLabel ysccno = new JLabel("要修改的课程号");
JLabel sccno = new JLabel("要改成的课程号");
JLabel score = new JLabel("成绩");
JTextField yscsnotext = new JTextField();
JTextField scsnotext = new JTextField();
JTextField ysccnotext = new JTextField();
JTextField sccnotext = new JTextField();
JTextField scoretext = new JTextField();
JButton ok = new JButton("确定");
JButton reset = new JButton("重置");
updatesc.setLayout(null);
yscsno.setBounds(5,5,100,20);
yscsnotext.setBounds(110,5,120,20);
scsno.setBounds(5,30,100,20);
scsnotext.setBounds(110,30,120,20);
ysccno.setBounds(5,60,100,20);
ysccnotext.setBounds(110,60,120,20);
sccno.setBounds(5,90,100,20);
sccnotext.setBounds(110,90,120,20);
score.setBounds(5,120,100,20);
scoretext.setBounds(110,120,120,20);
ok.setBounds(50,160,60,20);
reset.setBounds(120,160,60,20);
updatesc.add(yscsno);
updatesc.add(yscsnotext);
updatesc.add(scsno);
updatesc.add(scsnotext);
updatesc.add(ysccno);
updatesc.add(ysccnotext);
updatesc.add(sccno);
updatesc.add(sccnotext);
updatesc.add(score);
updatesc.add(scoretext);
updatesc.add(ok);
updatesc.add(reset);
UpdateSC.add(updatesc);
UpdateSC.setVisible(true);
UpdateSC.addWindowListener(new WindowAdapter(){
@Override
public void windowClosing(WindowEvent e) {
SystemUI.clickable();
}
});
ok.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
try {
String ysno=yscsnotext.getText();
String ycno=ysccnotext.getText();
String sno = scsnotext.getText();
String cno = sccnotext.getText();
int score = Integer.valueOf(scoretext.getText()).intValue();
int count=0;
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
ps=ct.prepareStatement("select Sname from SelectCourse where Sno='"+sno+"' and Cno='"+cno+"'");
rs=ps.executeQuery();
while(rs.next()){
count++;
}
if(count==0) {
JOptionPane.showMessageDialog(null, "此人没有选此课程！");
}
if(count!=0) {
ps=ct.prepareStatement("update SC set Sno ='"+sno+"', Cno ='"+cno+"',Sname=(select Sname from SelectCourse where Sno='"+sno+"'and Cno ='"+cno+"'), Cname =(select Cname from SelectCourse where Cno='"+cno+"'and Sno='"+sno+"'), Score ="+score+" where Sno='"+ysno+"'and Cno='"+ycno+"'");
ps.executeUpdate();
JOptionPane.showMessageDialog(null, "修改成功！");
UpdateSC.dispose();
SystemUI.clickable();
refreshScore();
}
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "修改失败！");
SystemUI.unclickable();
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
});
reset.addActionListener(new ActionListener(){
@Override
public void actionPerformed(ActionEvent arg0) {
scsnotext.setText("");
sccnotext.setText("");
scoretext.setText("");
}
});
}
public void deleteScore() {
// TODO 自动生成的方法存根
int row = table4.getSelectedRow();
if ( row >= 0 ) {
String string[] = new String[4];
string[0] = (String) table4.getValueAt(row, 0);
string[1] = (String) table4.getValueAt(row, 1);
string[2] = (String) table4.getValueAt(row, 2);
string[3] = (String) table4.getValueAt(row, 3);
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
ps=ct.prepareStatement("delete from SC where Sno='"+string[0]+"' and Sname='"+string[1]+"' and Cno='"+string[2]+"' and Cname='"+string[3]+"'");
ps.executeUpdate();
JOptionPane.showMessageDialog(null, "删除成功！");
refreshScore();
row=-1;
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "删除失败！");
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
else {
JOptionPane.showMessageDialog(null, "请选中要删除的行！");
}
}
public void searchScore() {
// TODO 自动生成的方法存根
SystemUI.unclickable();
JFrame SeaSC = new JFrame("查找选课信息");
SeaSC.setSize(250, 150);
SeaSC.setLocation(600, 300);
JPanel seasc = new JPanel();
JLabel cno = new JLabel("请输入课程号");
JTextField cnotext = new JTextField();
JLabel sno = new JLabel("请输入学号");
JTextField snotext = new JTextField();
JButton ok = new JButton("确定");
JButton reset = new JButton("重置");
seasc.setLayout(null);
cno.setBounds(5,5,80,20);
cnotext.setBounds(90,5,120,20);
sno.setBounds(5,30,80,20);
snotext.setBounds(90,30,120,20);
ok.setBounds(50,70,60,20);
reset.setBounds(130,70,60,20);
seasc.add(cno);
seasc.add(cnotext);
seasc.add(sno);
seasc.add(snotext);
seasc.add(ok);
seasc.add(reset);
SeaSC.add(seasc);
SeaSC.setVisible(true);
SeaSC.addWindowListener(new WindowAdapter(){
@Override
public void windowClosing(WindowEvent e) {
SystemUI.clickable();
}
});
ok.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
columnNames=new Vector<Object>();
//设置列名
columnNames.add("学号");
columnNames.add("姓名");
columnNames.add("课程号");
columnNames.add("课程名");
columnNames.add("成绩");
String str = cnotext.getText();
String str1 = snotext.getText();
rowData = new Vector<Object>();
int count=0;
//rowData可以存放多行,开始从数据库里取
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
if(snotext.getText().trim().equals("")&&!cnotext.getText().trim().equals("")){
ps=ct.prepareStatement("select * from SC where cno='"+str+"'");
rs=ps.executeQuery();
}
if(cnotext.getText().trim().equals("")&&!snotext.getText().trim().equals("")){
ps=ct.prepareStatement("select * from SC where sno='"+str1+"'");
rs=ps.executeQuery();
}
if(!cnotext.getText().trim().equals("")&&!snotext.getText().trim().equals("")){
ps=ct.prepareStatement("select * from SC where sno='"+str1+"' and cno='"+str+"'");
rs=ps.executeQuery();
}
if(cnotext.getText().trim().equals("")&&snotext.getText().trim().equals("")){
ps=ct.prepareStatement("select * from SC order by Score desc ");
rs=ps.executeQuery();
JOptionPane.showMessageDialog(null, "请输入查询信息！");
}
while(rs.next()){
//rowData可以存放多行
Vector<Object> hang=new Vector<Object>();
hang.add(rs.getString(1));
hang.add(rs.getString(2));
hang.add(rs.getString(3));
hang.add(rs.getString(4));
hang.add(rs.getInt(5));
//加入到rowData
rowData.add(hang);
count++;
}
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "查询失败！");
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
if(count!=0) {
table4 = new JTable(rowData,columnNames){
private static final long serialVersionUID = -518524586936803727L;
public boolean isCellEditable(int row, int column)
{return false;}//表格不允许被编辑
};
table4.getSelectionModel().setSelectionMode(ListSelectionModel.SINGLE_SELECTION);//只允许选中一行
DefaultTableCellRenderer tcr = new DefaultTableCellRenderer();// 设置table内容居中
tcr.setHorizontalAlignment(JLabel.CENTER);// 这句和上句作用一样
table4.setDefaultRenderer(Object.class, tcr);
SystemUI.scrollPane4.setViewportView(table4);
SeaSC.dispose();
SystemUI.clickable();}
if(count==0) {
JOptionPane.showMessageDialog(null, "没有查询到相关消息！");
}
}
});
reset.addActionListener(new ActionListener(){
@Override
public void actionPerformed(ActionEvent arg0) {
cnotext.setText("");
snotext.setText("");
}
});
}
public void statistical() {
// TODO 自动生成的方法存根
DecimalFormat df = new DecimalFormat("0.00%");//及格率变百分数形式
columnNames=new Vector<Object>();
//设置列名
columnNames.add("学号");
columnNames.add("姓名");
columnNames.add("课程号");
columnNames.add("课程名");
columnNames.add("成绩");
columnNames.add("专业");
columnNames.add("班级");
rowData = new Vector<Object>();
//rowData可以存放多行,开始从数据库里取
Object[] options = {"班级统计","专业统计"};
int choice =JOptionPane.showOptionDialog(null,"请选择统计的对象","成绩统计",0,JOptionPane.QUESTION_MESSAGE,null,options,options[1]);
if(choice==0) {
SystemUI.unclickable();
JInternalFrame ClassAnalyse=new JInternalFrame("班级成绩分析",false,true,false,false);
ClassAnalyse.setVisible(true);
SystemUI.scrollPane4.setViewportView(ClassAnalyse);
ClassAnalyse.addInternalFrameListener(new InternalFrameAdapter() {
public void internalFrameClosed(InternalFrameEvent e) {
SystemUI.clickable();// after you close it.
refreshScore();
}
});
JScrollPane jspc = new JScrollPane();
ClassAnalyse.add(jspc, BorderLayout.CENTER);
JToolBar jtbc=new JToolBar();
ClassAnalyse.add(jtbc,BorderLayout.NORTH);
JPanel jpc = new JPanel();
ClassAnalyse.add(jpc,BorderLayout.SOUTH);
JLabel jlc1=new JLabel("班级名");
JLabel jlc2=new JLabel("课程号");
JButton jbc = new JButton("确定");
JTextField jtfc1= new JTextField();
JTextField jtfc2= new JTextField();
jtbc.add(jlc1);
jtbc.add(jtfc1);
jtbc.add(jlc2);
jtbc.add(jtfc2);
jtbc.add(jbc);
jbc.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
clearTable();
String str = jtfc1.getText();
String str1 = jtfc2.getText();
int count = 0;
int num = 0;
double passrate=0;
double average=0;
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
if(jtfc1.getText().trim().equals("")||jtfc2.getText().trim().equals("")) {
JOptionPane.showMessageDialog(null, "请填入完整信息！");
}
else {
ps=ct.prepareStatement("select * from View_1 where Class='"+str+"'and Cno='"+str1+"' order by Score desc");
rs=ps.executeQuery();
while(rs.next()){
//rowData可以存放多行
count++;
Vector<Object> hang=new Vector<Object>();
hang.add(rs.getString(1));
hang.add(rs.getString(2));
hang.add(rs.getString(3));
hang.add(rs.getString(4));
hang.add(rs.getInt(5));
hang.add(rs.getString(6));
hang.add(rs.getString(7));
//加入到rowData
rowData.add(hang);
}
ps=ct.prepareStatement("select * from View_1 where Class='"+str+"'and Cno='"+str1+"' and Score>=60");
rs=ps.executeQuery();
while(rs.next()){
num++;
}
ps=ct.prepareStatement("select AVG(Score) from View_1 where Class='"+str+"'and Cno='"+str1+"'");
rs=ps.executeQuery();
while(rs.next()){
average=rs.getDouble(1);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
table6 = new JTable(rowData,columnNames){
private static final long serialVersionUID = 596141572765495714L;
public boolean isCellEditable(int row, int column)
{return false;}//表格不允许被编辑
};
table6.getSelectionModel().setSelectionMode(ListSelectionModel.SINGLE_SELECTION);//只允许选中一行
DefaultTableCellRenderer tcr = new DefaultTableCellRenderer();// 设置table内容居中
tcr.setHorizontalAlignment(JLabel.CENTER);// 这句和上句作用一样
table6.setDefaultRenderer(Object.class, tcr);
jspc.setViewportView(table6);
if(count==0) {
JOptionPane.showMessageDialog(null, "没有查询到相关消息！");
}
if (count!=0)
passrate=num*1.0/count;
String psrt=df.format(passrate);
JLabel jlc=new JLabel("本班有'"+count+"'个学生参加考试,平均分为'"+average+"',及格率为'"+psrt+"'");
jpc.add(jlc);
}
});
}
if(choice==1) {
SystemUI.unclickable();
JInternalFrame DeptAnalyse = new JInternalFrame("专业成绩分析",false,true,false,false);
DeptAnalyse.setVisible(true);
SystemUI.scrollPane4.setViewportView(DeptAnalyse);
DeptAnalyse.addInternalFrameListener(new InternalFrameAdapter() {
public void internalFrameClosed(InternalFrameEvent e) {
SystemUI.clickable();// after you close it.
refreshScore();
}
});
JScrollPane jspd = new JScrollPane();
DeptAnalyse.add(jspd, BorderLayout.CENTER);
JToolBar jtbd=new JToolBar();
DeptAnalyse.add(jtbd,BorderLayout.NORTH);
JPanel jpd = new JPanel();
DeptAnalyse.add(jpd,BorderLayout.SOUTH);
JLabel jld1=new JLabel("专业名");
JLabel jld2=new JLabel("课程号");
JButton jbd = new JButton("确定");
JTextField jtfd1= new JTextField();
JTextField jtfd2= new JTextField();
jtbd.add(jld1);
jtbd.add(jtfd1);
jtbd.add(jld2);
jtbd.add(jtfd2);
jtbd.add(jbd);
jbd.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
clearTable();
String str=jtfd1.getText();
String str1=jtfd2.getText();
int count = 0;
int num = 0;
double passrate=0;
double average=0;
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
if(jtfd1.getText().trim().equals("")||jtfd2.getText().trim().equals("")) {
JOptionPane.showMessageDialog(null, "请填入完整信息！");
}
else {
ps=ct.prepareStatement("select * from View_1 where Sdept='"+str+"'and Cno='"+str1+"' order by Score desc");
rs=ps.executeQuery();
while(rs.next()){
//rowData可以存放多行
count++;
Vector<Object> hang=new Vector<Object>();
hang.add(rs.getString(1));
hang.add(rs.getString(2));
hang.add(rs.getString(3));
hang.add(rs.getString(4));
hang.add(rs.getInt(5));
hang.add(rs.getString(6));
hang.add(rs.getString(7));
//加入到rowData
rowData.add(hang);
}
ps=ct.prepareStatement("select * from View_1 where Sdept='"+str+"'and Cno='"+str1+"'and Score>=60");
rs=ps.executeQuery();
while(rs.next()){
num++;
}
ps=ct.prepareStatement("select AVG(Score) from View_1 where Sdept='"+str+"'and Cno='"+str1+"'");
rs=ps.executeQuery();
while(rs.next()){
average=rs.getDouble(1);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
table6 = new JTable(rowData,columnNames){
private static final long serialVersionUID = -6357053302044595304L;
public boolean isCellEditable(int row, int column)
{return false;}//表格不允许被编辑
};
table6.getSelectionModel().setSelectionMode(ListSelectionModel.SINGLE_SELECTION);//只允许选中一行
DefaultTableCellRenderer tcr = new DefaultTableCellRenderer();// 设置table内容居中
tcr.setHorizontalAlignment(JLabel.CENTER);// 这句和上句作用一样
table6.setDefaultRenderer(Object.class, tcr);
jspd.setViewportView(table6);
if(count==0) {
JOptionPane.showMessageDialog(null, "没有查询到相关消息！");
}
if (count!=0)
passrate=num*1.0/count;
String psrt=df.format(passrate);
JLabel jld=new JLabel("本专业有'"+count+"'个学生参加考试,平均分为'"+average+"',及格率为'"+psrt+"'");
jpd.add(jld);
}
});
}
}
public void refreshScore() {
// TODO 自动生成的方法存根
new Score();
SystemUI.scrollPane4.setViewportView(Score.table4);
}
public static synchronized void clearTable() {
try {
((DefaultTableModel)table6.getModel()).getDataVector().clear();
((DefaultTableModel)table6.getModel()).fireTableDataChanged();
table6.updateUI();
}catch(Exception e){
}
}
public void totalrank() {
// TODO 自动生成的方法存根
JFrame jf = new JFrame("总分排名");
jf.setVisible(true);
jf.setSize(250, 300);
JScrollPane js=new JScrollPane();
jf.add(js,BorderLayout.CENTER);
columnNames=new Vector<Object>();
//设置列名
columnNames.add("名次");
columnNames.add("学号");
columnNames.add("总分");
rowData = new Vector<Object>();
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=EducationalManagementSystem","test","123456");
ps=ct.prepareStatement("select SNO,sum(Score) sum_grade from SC group by SC.SNO having count(CNO)>=1 order by sum_grade desc");
rs=ps.executeQuery();
int rank=0;
while(rs.next()){
//rowData可以存放多行
rank++;
Vector<Object> hang=new Vector<Object>();
hang.add(rank);
hang.add(rs.getString(1));
hang.add(rs.getString(2));
//加入到rowData
rowData.add(hang);
}
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(ct!=null){
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
table7 = new JTable(rowData,columnNames){
private static final long serialVersionUID = 3701216243676371199L;
public boolean isCellEditable(int row, int column)
{return false;}//表格不允许被编辑
};
table7.getSelectionModel().setSelectionMode(ListSelectionModel.SINGLE_SELECTION);//只允许选中一行
DefaultTableCellRenderer tcr = new DefaultTableCellRenderer();// 设置table内容居中
tcr.setHorizontalAlignment(JLabel.CENTER);// 这句和上句作用一样
table7.setDefaultRenderer(Object.class, tcr);
js.setViewportView(table7);
}
}
到此，系统就做完了，登录的用户名和密码在数据库的suser表里自己设定
仅供初学者参考哦
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
