前言
这次我使用java和mysql做了一个学生管理系统,需要了解jdbc和JFrame的相关知识,并且要了解sql语言。
链接:https://pan.baidu.com/s/16BSIqI2qNpi39rRRXwmTtg
提取码:7g6f
一、创建窗口
总共有四个窗口,一个窗口为开始窗口 和中转站,图示如下
二、创建响应事件,连接mysql
下列代码为student的增删改查功能的实现,其他两个页面的功能代码与他类似,就不贴出来了。
//插入数据
btnadd.addActionListener(new ActionListener() { // 为按钮添加监听事件
public void actionPerformed(ActionEvent e) {
//所有的都不能为空
if(snotext.getText().length()==0||snametext.getText().length()==0||agetext.getText().length()==0||sextext.getText().length()==0) {
//显示提示对话框
JOptionPane.showMessageDialog(student.this, "输入不能为空");
}
else {
//连接数据库,并执行sql命令,完成功能
Connection con=null;
PreparedStatement ps=null;
//结果集
ResultSet result=null;
int rs=0;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC", "root", "175932");
String sqls="SELECT * FROM student WHERE sno=?";
ps=con.prepareStatement(sqls);
ps.setString(1, snotext.getText());
result=ps.executeQuery();
//看是否有结果,有就重复
if(result.next()) {
JOptionPane.showMessageDialog(student.this, "学号不能重复");
if(result!=null) {
result.close();
}
if(ps!=null) {
ps.close();
}
}
//不重复
else {
if(result!=null) {
result.close();
}
if(ps!=null) {
ps.close();
}
String sql="INSERT INTO student VALUES(?,?,?,?)";
ps=con.prepareStatement(sql);
ps.setString(1, snotext.getText());
ps.setString(2, snametext.getText());
ps.setString(3, sextext.getText());
ps.setString(4, agetext.getText());
rs=ps.executeUpdate();
if(rs!=0) {
JOptionPane.showMessageDialog(student.this, "添加成功");
}
}
} catch (SQLException e1) {
JOptionPane.showMessageDialog(student.this, "输入错误");
e1.printStackTrace();
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
} catch (Exception e1) {
JOptionPane.showMessageDialog(student.this, "输入错误");
e1.printStackTrace();
} finally {
if(ps!=null) {
try {
ps.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
if(con!=null) {
try {
con.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
}
}
});
//删除数据,以学号sno为准
btndelete.addActionListener(new ActionListener() { // 为按钮添加监听事件
public void actionPerformed(ActionEvent e) {
//学号不能为空
if(snotext.getText().length()==0) {
//显示提示对话框
JOptionPane.showMessageDialog(student.this, "学号不能为空");
}
else {
//连接数据库,并执行sql命令,完成功能
Connection con=null;
PreparedStatement ps=null;
PreparedStatement psc=null;
int rs=0;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC", "root", "175932");
String sql="DELETE FROM student WHERE sno=?";
ps=con.prepareStatement(sql);
ps.setString(1, snotext.getText());
//删除之前,要将选课表的相应数据删除
String sqlsc="DELETE FROM sc WHERE sno=?";
psc=con.prepareStatement(sqlsc);
psc.setString(1, snotext.getText());
psc.execute();
if(psc!=null) {
psc.close();
}
rs=ps.executeUpdate();
if(rs!=0) {
JOptionPane.showMessageDialog(student.this, "删除成功");
}
else {
JOptionPane.showMessageDialog(student.this, "无此学号");
}
} catch (SQLException e1) {
JOptionPane.showMessageDialog(student.this, "输入错误");
e1.printStackTrace();
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
} catch (Exception e1) {
JOptionPane.showMessageDialog(student.this, "输入错误");
e1.printStackTrace();
} finally {
if(ps!=null) {
try {
ps.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
if(con!=null) {
try {
con.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
}
}
});
//修改数据,无法修改学号,以学号为准找到要修改的数据
btnupdate.addActionListener(new ActionListener() { // 为按钮添加监听事件
public void actionPerformed(ActionEvent e) {
//所有的都不能为空
if(snotext.getText().length()==0||snametext.getText().length()==0||agetext.getText().length()==0||sextext.getText().length()==0) {
//显示提示对话框
JOptionPane.showMessageDialog(student.this, "输入不能为空");
}
else {
//连接数据库,并执行sql命令,完成功能
Connection con=null;
PreparedStatement ps=null;
int rs=0;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC", "root", "175932");
String sql="UPDATE student SET sname=?,sex=?,age=? WHERE sno=?";
ps=con.prepareStatement(sql);
ps.setString(4, snotext.getText());
ps.setString(1, snametext.getText());
ps.setString(2, sextext.getText());
ps.setString(3, agetext.getText());
rs=ps.executeUpdate();
if(rs!=0) {
JOptionPane.showMessageDialog(student.this, "修改成功");
}
else {
JOptionPane.showMessageDialog(student.this, "无此学号");
}
//System.out.println(con);//测试是否连通
} catch (SQLException e1) {
JOptionPane.showMessageDialog(student.this, "输入错误");
e1.printStackTrace();
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
} catch (Exception e1) {
JOptionPane.showMessageDialog(student.this, "输入错误");
e1.printStackTrace();
} finally {
if(ps!=null) {
try {
ps.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
if(con!=null) {
try {
con.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
}
}
});
//查询数据,以学号sno为准
btnselect.addActionListener(new ActionListener() { // 为按钮添加监听事件
public void actionPerformed(ActionEvent e) {
//学号不能为空
if(snotext.getText().length()==0) {
//显示提示对话框
JOptionPane.showMessageDialog(student.this, "学号不能为空");
}
else {
//连接数据库,并执行sql命令,完成功能
Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC", "root", "175932");
String sql="SELECT * FROM student WHERE sno=?";
ps=con.prepareStatement(sql);
ps.setString(1, snotext.getText());
rs=ps.executeQuery();
if(rs.next()) {
snametext.setText(rs.getString("sname"));
sextext.setText(rs.getString("sex"));
agetext.setText(rs.getString("age"));
JOptionPane.showMessageDialog(student.this, "查询成功");
}
else {
JOptionPane.showMessageDialog(student.this, "查无此学号");
}
} catch (SQLException e1) {
JOptionPane.showMessageDialog(student.this, "输入错误");
e1.printStackTrace();
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
} catch (Exception e1) {
JOptionPane.showMessageDialog(student.this, "输入错误");
e1.printStackTrace();
} finally {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
if(ps!=null) {
try {
ps.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
if(con!=null) {
try {
con.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
}
}
});
三、数据库test中表的结构
四、结果
我只贴了选课表的查询结果图