1、（windows综合程序）设计一个学生平时成绩统计软件。要求：
(1) 录入课程名称(进入系统时录入)、学生姓名、学号、成绩、日期(自动生成日期并在界面显示)，除第一次外其他次数输入只需要录入学号、成绩，其他自动产生，录入要求所有项目不能为空，界面设计自己定义，每录入一笔记录暂时放在内存，当点击按钮保存时再写入数据库。每次只能录入一次的学生平时成绩（同一日期，所有学生的成绩）
(2) 数据库端建立数据库表(第一次录入课程名称时创建以课程名为名字的表)，含字段：学号、姓名、第一次平时成绩、第二次次平时成绩(共10次成绩)、平时平均成绩(自动计算)
(3) 查询和浏览学生平时成绩，要求：
① 查询平时成绩时只要录入学号则显示窗体显示：姓名、学号、目前为止平均平时成绩。
② 浏览学生成绩时呀求逐笔学生记录显示学生成绩，每次按按纽‘下一笔’时显示下一位学生的成绩
(4) 生成学生平时成绩EXECL表，将数据库中的学生成绩生成一个表格放在本地(这项不要求每位同学做，愿意做的可以查找相关资料后做)
其他在上面没有提出的自己确定，包括界面形式等，只要能够让系统达到上述要求即可。
以下是我的源代码：
package endtest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.Calendar;
import java.util.Date;
import java.util.Scanner;
public class myjdbc {
private static final String url = "jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=UTC";
private static final String name = "com.mysql.cj.jdbc.Driver";
private static final String user = "root";
private static final String password = "06101010lu";
private Connection conn = null;
private Statement pst = null;
public void getDataBaseConnection(){
try{
Class.forName(name);
conn = DriverManager.getConnection(url, user, password);
System.out.println("已成功的与数据库MySQL建立连接！！");
}catch(Exception e){
System.out.println("连接失败！！");
e.printStackTrace();
}
}
public void init(){
try {
pst = conn.createStatement();
String sql = "CREATE TABLE IF NOT EXISTS register(USER VARCHAR(200),PWD VARCHAR(200))";
pst.executeUpdate(sql);
}catch (SQLException e) {
e.printStackTrace();
}
}
public void addUser(String user,String pwd){
System.out.println(user+" yy "+pwd);
try {
pst = conn.createStatement();
String sql = "INSERT INTO register(USER,PWD) values('"+user+"','"+pwd+"')";
pst.executeUpdate(sql);
System.out.println("yes");
}catch (SQLException e) {
System.out.println("fails!");
e.printStackTrace();
}
}
public boolean checkUser(String user,String pwd){
try {
pst = conn.createStatement();
String sql = "SELECT * FROM register WHERE USER = "+user+" AND PWD = "+pwd;
ResultSet ans = pst.executeQuery(sql);
if(ans.next()) return true;
return false;
}catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public void close() {
try {
this.conn.close();
this.pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void creatMyTable(String tableName) {
try{
System.out.println("请输入课程名称："+tableName+" ");
pst = conn.createStatement();
String sql = "CREATE TABLE IF NOT EXISTS "
+ tableName
+"(ID VARCHAR(200),CNAME VARCHAR(200),SCORE FLOAT,CSUM FLOAT ,NUM FLOAT ,CTIME char(200))";
pst.executeUpdate(sql);
System.out.println("成功创建学生表！！！");
}catch(SQLException e){
e.printStackTrace();
}
}
public boolean checkTable(String tableName){
try {
System.out.println("fd:"+tableName);
ResultSet ans = conn.getMetaData().getTables(null, null, tableName, null );
if(ans.next()) return true;
return false;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public void saveMyTable(String tableName,String name,String id,float score) {
try {
LocalDate date = LocalDate.now(); // get the current date
String time = date.toString();
System.out.println(date+" "+time);
pst = conn.createStatement();
String sql1 = "SELECT * FROM " + tableName + " WHERE ID="+id;
ResultSet ans = pst.executeQuery(sql1);
float sum = score,num = 1.0f;
if(ans.next()){
sum +=ans.getFloat("CSUM");
num +=ans.getFloat("NUM");
score = sum / num;
}
sql1 = "DELETE FROM "+ tableName + " WHERE ID="+id;
pst.executeUpdate(sql1);
System.out.println(score + " "+ sum+" "+" "+num);
String sql ="INSERT INTO "
+tableName
+"(ID,CNAME,SCORE,CSUM,NUM,CTIME) VALUES('"+id+"','"+name+"',"+score+","+sum+","+num+",'"+time+"')";
pst.executeUpdate(sql);// 返回影响的记录行数
} catch (SQLException e) {
e.printStackTrace();
}
}
public void QueryStudent(String sc,String idn,String[] ans) {
try{
System.out.println(sc+" --- "+idn+" --- ");
pst = conn.createStatement();
String sql = "SELECT * FROM "+sc+" WHERE ID= "+idn;
ResultSet rc = pst.executeQuery(sql);
System.out.println("true!!!");
// 展开结果集数据库
if(rc.next()){
// 通过字段检索
ans[0] = rc.getString("ID");
ans[1] = rc.getString("CNAME");
float score = rc.getInt("SCORE");
ans[2] = Float.toString(score);
}
}catch(SQLException e) {
e.printStackTrace();
}
}
public String[][] DisplayStudent(String sc) {
try{
int now=0;
String [][] res = new String[10000][3];
pst = conn.createStatement();
String sql = "SELECT * FROM "+ sc;
ResultSet rc = pst.executeQuery(sql);
// 展开结果集数据库
System.out.println("所有学生集合：");
while(rc.next()){
// 通过字段检索
res[now][0] = rc.getString("ID");
res[now][1] = rc.getString("CNAME");
float score = rc.getInt("SCORE");
res[now][2] = Float.toString(score);
now++;
}
String [][] ans;
if(now==0){
ans = new String[1][3];
ans[0][0]=ans[0][1]=ans[0][2]="此为空表！";
}
else{
ans = new String[now][3];
for(int i=0;i<now;i++){
ans[i]=res[i];
}
}
return ans;
}catch(SQLException e) {
e.printStackTrace();
}
return new String[0][0];
}
}
package endtest;
import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.geom.AffineTransform;
public class test {
public static void main(String[] args){
myjdbc jdbc = new myjdbc();
jdbc.getDataBaseConnection();
jdbc.init();
JFrame frame = new JFrame("学生平时成绩统计系统");
frame.setVisible(true);
frame.setBounds(300,200,600,400);
JPanel panel = new JPanel();
frame.add(panel);
//采用自定义设置，只有这样，JLabel才可以自定义在JPanel中的位置，不然JLevel是居中显示
panel.setLayout(null);
Font font = new Font("微软雅黑", Font.BOLD,18);
JLabel user_lab = new JLabel("用户名：");
user_lab.setFont(font);
user_lab.setBounds(110,20,100,100);
panel.add(user_lab);
//创建输入的文本框
JTextField user_Field = new JTextField();
user_Field.setBounds(200,60,160,30);
panel.add(user_Field);
user_lab.setBackground(Color.white);
//创建输入的密码框
JLabel pwd = new JLabel("密码：");
pwd.setFont(font);
pwd.setBounds(110,100,160,30);
panel.add(pwd);
JPasswordField pwd_Field = new JPasswordField();
pwd_Field.setBounds(200,100,160,30);
pwd.setBackground(Color.white);
panel.add(pwd_Field);
JButton login = new JButton("登录");
login.setBounds(180,150,80,30);
panel.add(login);
JButton register = new JButton("注册");
register.setBounds(278,150,80,30);
panel.add(register);
register.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
//获取文本值的api
JPanel RegisterPanel = new JPanel();
RegisterPanel.setLayout(null);
JLabel user = new JLabel("用户名：");
user.setFont(font);
user.setBounds(110,20,100,100);
RegisterPanel.add(user);
//创建输入的文本框
JTextField user_F = new JTextField();
user_F.setBounds(200,60,160,30);
RegisterPanel.add(user_F);
user.setBackground(Color.white);
//创建第一个输入的密码框
JLabel FirPwd = new JLabel("密码：");
FirPwd.setFont(font);
FirPwd.setBounds(110,100,160,30);
RegisterPanel.add(FirPwd);
JPasswordField FirPwdField = new JPasswordField();
FirPwdField.setBounds(200,100,160,30);
FirPwd.setBackground(Color.white);
RegisterPanel.add(FirPwdField);
//创建第二个输入的密码框
JLabel SecPwd = new JLabel("确认密码：");
SecPwd.setFont(font);
SecPwd.setBounds(110,140,160,30);
RegisterPanel.add(SecPwd);
JPasswordField SecPwdField = new JPasswordField();
SecPwdField.setBounds(200,140,160,30);
SecPwd.setBackground(Color.white);
RegisterPanel.add(SecPwdField);
JButton reg = new JButton("注册");
reg.setBounds(278,190,80,30);
RegisterPanel.add(reg);
JButton back = new JButton("返回");
back.setBounds(180,190,80,30);
RegisterPanel.add(back);
panel.setVisible(false);
frame.add(RegisterPanel);
back.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
panel.setVisible(true);
RegisterPanel.setVisible(false);
}
});
reg.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
String user = user_F.getText().trim();
String pwd1 = FirPwdField.getText().trim();
String pwd2 = SecPwdField.getText().trim();
if(pwd1.equals(pwd2)&&!user.equals("")&&!pwd1.equals("")){
jdbc.addUser(user,pwd1);
frame.setVisible(false);
choose(jdbc);
}
else{
System.out.println("ssss help");
JFrame error = new JFrame("输入错误！！！");
error.setBounds(200,200,400,100);
JPanel er = new JPanel();
er.setLayout(null);
JLabel tip = new JLabel("两次密码输入不同！或者用户名为null！");
tip.setBounds(10,0,350,30);
JButton reback = new JButton("返回");
reback.setBounds(170,30,60,20);
er.add(tip);
er.add(reback);
error.add(er);
error.setVisible(true);
reback.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
error.setVisible(false);
panel.setVisible(true);
}
});
}
}
});
}
});
login.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
//获取文本值的api
String username = user_Field.getText().trim();
String password = pwd_Field.getText().trim();
if(!password.equals("")&&!username.equals("")&&jdbc.checkUser(username,password)){
frame.setVisible(false);
choose(jdbc);
}
else {
JFrame error = new JFrame("密码错误");
error.setBounds(200,200,400,100);
JPanel er = new JPanel();
er.setLayout(null);
JLabel tip = new JLabel("账号不存在或密码错误！");
tip.setBounds(10,0,350,30);
JButton reback = new JButton("返回");
reback.setBounds(170,30,60,20);
er.add(tip);
er.add(reback);
error.add(er);
error.setVisible(true);
reback.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
error.setVisible(false);
}
});
}
}
});
}
public static void choose(myjdbc jdbc){
JFrame frame = new JFrame("学生平时成绩统计系统");
frame.setVisible(true);
frame.setBounds(300,200,600,400);
JPanel panel = new JPanel();
frame.add(panel);
//采用自定义设置，只有这样，JLabel才可以自定义在JPanel中的位置，不然JLevel是居中显示
panel.setLayout(null);
Font font = new Font("微软雅黑", Font.BOLD,14);
JButton first = new JButton("建立表格");
first.setBounds(200,20,150,50);
first.setFont(font);
panel.add(first);
JButton other = new JButton("录入成绩");
other.setBounds(200,100,150,50);
other.setFont(font);
panel.add(other);
JButton query = new JButton("查询成绩");
query.setBounds(200,180,150,50);
query.setFont(font);
panel.add(query);
JButton watch = new JButton("浏览成绩");
watch.setBounds(200,260,150,50);
watch.setFont(font);
panel.add(watch);
first.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
panel.setVisible(false);
JPanel create = new JPanel();
create.setLayout(null);
frame.add(create);
JLabel Input = new JLabel("表格的名字：");
Input.setBounds(10,50,100,30);
Input.setFont(font);
create.add(Input);
JTextField input = new JTextField();
input.setBounds(120,50,200,30);
input.setFont(font);
create.add(input);
JButton back = new JButton("返回");
back.setFont(font);
back.setBounds(100,100,100,30);
create.add(back);
JButton submit = new JButton("提交");
submit.setFont(font);
submit.setBounds(220,100,100,30);
create.add(submit);
back.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
create.setVisible(false);
panel.setVisible(true);
}
});
submit.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
String c = input.getText().trim();
boolean f = false;
if(c.length() == 0) ;
else if(c.charAt(0)<='z'&&c.charAt(0)>='a') f = true;
else if(c.charAt(0)<='Z'&&c.charAt(0)>='A') f = true;
else if(c.charAt(0)=='_') f = true;
if(f&&!jdbc.checkTable(c)) {
jdbc.creatMyTable(c);
create.setVisible(false);
panel.setVisible(true);
}
else{
JFrame error = new JFrame("表格错误");
error.setBounds(200,200,400,100);
JPanel er = new JPanel();
er.setLayout(null);
JLabel tip = new JLabel("表格名字不符合规范或表格已经存在！！");
tip.setBounds(10,0,350,30);
JButton reback = new JButton("返回");
reback.setBounds(170,30,60,20);
er.add(tip);
er.add(reback);
error.add(er);
error.setVisible(true);
reback.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
error.setVisible(false);
}
});
}
}
});
}
});
other.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
panel.setVisible(false);
JPanel FPanel = new JPanel();
FPanel.setLayout(null);
frame.add(FPanel);
JLabel Course = new JLabel("课程：");
Course.setBounds(100,50,100,30);
Course.setFont(font);
FPanel.add(Course);
JTextField course = new JTextField();
course.setBounds(160,50,200,30);
FPanel.add(course);
JLabel Name = new JLabel("姓名：");
Name.setBounds(100,100,100,30);
Name.setFont(font);
FPanel.add(Name);
JTextField name = new JTextField();
name.setBounds(160,100,200,30);
FPanel.add(name);
JLabel ID = new JLabel("学号：");
ID.setBounds(100,150,100,30);
ID.setFont(font);
FPanel.add(ID);
JTextField id = new JTextField();
id.setBounds(160,150,200,30);
id.setFont(font);
FPanel.add(id);
JLabel Score = new JLabel("成绩：");
Score.setBounds(100,200,100,30);
Score.setFont(font);
FPanel.add(Score);
JTextField score = new JTextField();
score.setBounds(160,200,200,30);
score.setFont(font);
FPanel.add(score);
JButton last = new JButton("取消");
last.setBounds(150,250,100,30);
last.setFont(font);
FPanel.add(last);
JButton com = new JButton("提交");
com.setBounds(260,250,100,30);
com.setFont(font);
FPanel.add(com);
last.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
FPanel.setVisible(false);
panel.setVisible(true);
}
});
com.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
String c = course.getText().trim();
String n = name.getText().trim();
String d = id.getText().trim();
String ss = score.getText().trim();
int s = 0;
for(int i=0;i<ss.length();i++){
s=s*10+ss.charAt(i)-'0';
}
boolean idNum = jdbc.checkTable(c);
System.out.println(idNum+" really");
if(idNum&&s<=100&&s>=0&&n.length()!=0&&d.length()==9){
jdbc.saveMyTable(c,n,d,s);
panel.setVisible(true);
FPanel.setVisible(false);
}
else{
JFrame error = new JFrame("输入错误");
error.setBounds(200,200,400,230);
JPanel er = new JPanel();
er.setLayout(null);
error.add(er);
JLabel tip1 = new JLabel("tip1:课程号不能为空且首字母只能是字母或者下划线！");
tip1.setBounds(10,0,350,30);
er.add(tip1);
JLabel tip2 = new JLabel("tip2:名字不能为空！");
tip2.setBounds(10,50,350,30);
er.add(tip2);
JLabel tip3 = new JLabel("tip3:学号必须是9位并且表格必须存在数据库之中！");
tip3.setBounds(10,100,350,30);
er.add(tip3);
JButton reback = new JButton("返回");
reback.setBounds(170,150,60,20);
er.add(reback);
error.setVisible(true);
reback.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
error.setVisible(false);
}
});
}
}
});
}
});
query.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
JPanel which = new JPanel();
frame.add(which);
which.setLayout(null);
which.setVisible(true);
panel.setVisible(false);
JLabel tip1 = new JLabel("请输入课程：");
tip1.setFont(font);
tip1.setBounds(100,100,100,30);
which.add(tip1);
JTextField answer1 = new JTextField();
answer1.setFont(font);
answer1.setBounds(200,100,300,30);
which.add(answer1);
JLabel tip2 = new JLabel("请输入学号：");
tip2.setFont(font);
tip2.setBounds(100,150,100,30);
which.add(tip2);
JTextField answer2 = new JTextField();
answer2.setFont(font);
answer2.setBounds(200,150,300,30);
which.add(answer2);
JButton reback = new JButton("返回");
reback.setFont(font);
reback.setBounds(270,200,100,20);
which.add(reback);
JButton submit = new JButton("提交");
submit.setFont(font);
submit.setBounds(400,200,100,20);
which.add(submit);
reback.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
which.setVisible(false);
panel.setVisible(true);
}
});
submit.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
String sc = answer1.getText().trim();
String idN = answer2.getText().trim();
String[] ans = new String[3];
ans[1]=ans[2]=ans[0]="查无此人！";
if(jdbc.checkTable(sc))jdbc.QueryStudent(sc,idN,ans);
else{
ans[0]=ans[1]=ans[2]="查无此表！";
}
JPanel show = new JPanel();
frame.add(show);
show.setLayout(null);
show.setVisible(true);
which.setVisible(false);
frame.add(show);
JLabel top = new JLabel("查询成绩");
top.setFont(font);
top.setBounds(250,20,100,30);
show.add(top);
JLabel name = new JLabel("姓名：");
name.setFont(font);
name.setBounds(100,60,100,30);
show.add(name);
JTextField Name = new JTextField(ans[0]);
Name.setFont(font);
Name.setBounds( 200,60,200,30);
show.add(Name);
JLabel id = new JLabel("学号：");
id.setFont(font);
id.setBounds(100,120,100,30);
show.add(id);
JTextField ID = new JTextField(ans[1]);
ID.setFont(font);
ID.setBounds(200,120,200,30);
show.add(ID);
JLabel score = new JLabel("成绩：");
score.setFont(font);
score.setBounds(100,180,100,30);
show.add(score);
JTextField Score = new JTextField(ans[2]);
Score.setFont(font);
Score.setBounds(200,180,200,30);
show.add(Score);
JButton back = new JButton("返回");
back.setBounds(300,240,100,30);
back.setFont(font);
show.add(back);
back.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
which.setVisible(true);
show.setVisible(false);
}
});
}
});
}
});
watch.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
JPanel which = new JPanel();
frame.add(which);
which.setLayout(null);
which.setVisible(true);
panel.setVisible(false);
JLabel tip1 = new JLabel("请输入课程：");
tip1.setFont(font);
tip1.setBounds(100,100,100,30);
which.add(tip1);
JTextField answer1 = new JTextField();
answer1.setFont(font);
answer1.setBounds(200,100,300,30);
which.add(answer1);
JButton reback = new JButton("返回");
reback.setFont(font);
reback.setBounds(270,150,100,20);
which.add(reback);
JButton submit = new JButton("提交");
submit.setFont(font);
submit.setBounds(400,150,100,20);
which.add(submit);
reback.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
which.setVisible(false);
panel.setVisible(true);
}
});
submit.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
String sc = answer1.getText().trim();
String[][] ans ;
if(!jdbc.checkTable(sc)){
ans = new String[1][3];
ans[0][0]=ans[0][1]=ans[0][2]="数据库中无此表！";
}
else{
ans = jdbc.DisplayStudent(sc);
}
int[] now = {0};
JPanel show = new JPanel();
frame.add(show);
show.setLayout(null);
show.setVisible(true);
which.setVisible(false);
frame.add(show);
JLabel top = new JLabel("查询成绩");
top.setFont(font);
top.setBounds(250,20,100,30);
show.add(top);
JLabel name = new JLabel("姓名：");
name.setFont(font);
name.setBounds(100,60,100,30);
show.add(name);
JTextField Name = new JTextField();
Name.setFont(font);
Name.setBounds( 200,60,200,30);
show.add(Name);
Name.setText(ans[now[0]][0]);
JLabel id = new JLabel("学号：");
id.setFont(font);
id.setBounds(100,120,100,30);
show.add(id);
JTextField ID = new JTextField();
ID.setFont(font);
ID.setBounds(200,120,200,30);
show.add(ID);
ID.setText(ans[now[0]][1]);
JLabel score = new JLabel("成绩：");
score.setFont(font);
score.setBounds(100,180,100,30);
show.add(score);
JTextField Score = new JTextField();
Score.setFont(font);
Score.setBounds(200,180,200,30);
show.add(Score);
Score.setText(ans[now[0]][2]);
JButton back = new JButton("下一页");
back.setBounds(300,240,100,30);
back.setFont(font);
show.add(back);
back.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent actionEvent) {
now[0]++;
if(now[0] < ans.length) {
Name.setText(ans[now[0]][0]);
ID.setText(ans[now[0]][1]);
Score.setText(ans[now[0]][2]);
}
else{
show.setVisible(false);
which.setVisible(true);
}
}
});
}
});
}
});
}
}
其实我觉得做了很多重复的东西，还有很多值得改善的地方。
注意这个运行要导入mysql和Java的连接包。
