数据库课程设计
开发环境：eclipse、SQLServer
E-R图：
功能分析：
开始界面：选择身份
开始界面源码：
package StudentSQL;
public class Start {
// 启动登录界面
public static void main(String[] args) {
Login login = new Login();
}
}package StudentSQL;
import java.awt.*;
import javax.swing.*;
import java.awt.event.*;
public class Login extends JFrame implements ActionListener{
//定义组件
JFrame frame=new JFrame();
JPanel jp1,jp2,jp3;//面板
JLabel jlb1,jlb2;//标签
JButton jb1,jb2;//按钮
//构造函数
public Login(){
//创建面板
jp1=new JPanel();
jp2=new JPanel();
jp3=new JPanel();
//创建标签
jlb1=new JLabel("请按照你的身份选择登陆");
//创建按钮
jb1=new JButton("管理员");
jb2=new JButton("学生");
//设置布局管理
frame.setLayout(new GridLayout(3, 1));//网格式布局
//加入各个组件
jp1.add(jlb1);
jp3.add(jb1);
jp3.add(jb2);
//加入到JFrame
frame.add(jp2);
frame.add(jp1);
//this.add(jp2);
frame.add(jp3);
//设置窗体
frame.setTitle("用户登录界面");//窗体标签
frame.setSize(400, 300);//窗体大小
frame.setLocationRelativeTo(null);//在屏幕中间显示(居中显示)
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);//退出关闭JFrame
frame.setVisible(true);//显示窗体
//锁定窗体
//this.setResizable(false);
jb1.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
// frame.setVisible(false);
// frame.setDefaultCloseOperation(operation);
AdministratorLogin adlogin = new AdministratorLogin();
}
});
jb2.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
// frame.setVisible(false);
StudentGUI stuGUI = new StudentGUI();
}
});
}
@Override
public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub
}
}身份选择学生，则直接显示查询界面（学生没有对数据库进行增删改查的权限）
package StudentSQL;
import javax.swing.*;
import java.awt.*;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.awt.event.MouseEvent;
import java.awt.event.MouseListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class StudentGUI extends JFrame implements MouseListener, ItemListener {
protected JFrame jf;
// 定义选项卡
private JTabbedPane Base;
// 定义选项卡上的嵌板
private JPanel jp1;
// 定义各按钮
private JButton InsertRecord1, InsertReset1;
// 定义各标签
private JLabel InsertID1;
// 定义各文本框
private JTextField InsertID2;
private JTextArea QueryRecordResult;
// 定义滚动条
private JScrollPane scroll = null;
// 定义一个数据库操作的实例
private OperationMySql db = null;
StudentGUI() {
// 设置各按钮信息
setButton();
// 设置各标签信息
setLabel();
// 设置各文本框信息
setTextField();
// 设置各面板信息
setPanel();
// 设置布局信息
setLayout();
// 设置选项卡信息
setBase();
// 设置主窗口信息
setThis();
// 设置数据库信息
setDB();
}
// 设置各按钮信息的方法
private void setButton() {
// jp1 上的按钮
InsertRecord1 = new JButton("查询");
InsertRecord1.setBounds(150, 400, 100, 45);
InsertRecord1.setMargin(new Insets(0, 0, 0, 0)); // 设置按钮的边缘空白为四个方向全为0，也即让按钮中的文本与按钮边缘贴齐
InsertReset1 = new JButton("重置");
InsertReset1.setBounds(300, 400, 100, 45);
InsertReset1.setMargin(new Insets(0, 0, 0, 0));
// 按键监听初始化
initial();
}
// 设置各标签信息的方法
private void setLabel() {
// jp1 上的标签
InsertID1 = new JLabel("学 号：");
InsertID1.setBounds(100, 40, 120, 50);
}
// 设置各文本框信息的方法
private void setTextField() {
// jp1 上的文本框
InsertID2 = new JTextField();
InsertID2.setBounds(210, 40, 200, 35);
QueryRecordResult = new JTextArea("查询结果：");
QueryRecordResult.setBounds(30,100,660,200);
QueryRecordResult.setEditable(false);
QueryRecordResult.setLineWrap(true);
scroll = new JScrollPane(QueryRecordResult); // 添加滚动条
scroll.setBounds(30, 30, 560, 260);
scroll.setVerticalScrollBarPolicy(ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED); // 当需要垂直滚动条时显示
scroll.setHorizontalScrollBarPolicy(ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED);// 当需要水平滚动条时显示
}
// 设置各面板信息的方法
private void setPanel() {
jp1 = new JPanel();
}
// 设置布局信息的方法
private void setLayout() {
// 添加 jp1 的组件
jp1.setLayout(null);
jp1.add(InsertRecord1);
jp1.add(InsertReset1);
jp1.add(InsertID1);
jp1.add(InsertID2);
jp1.add(QueryRecordResult);
}
// 设置选项卡信息的方法
private void setBase() {
Base = new JTabbedPane(JTabbedPane.TOP);
Base.addTab("查询学生成绩信息", jp1);
}
// 设置主窗口信息的方法
private void setThis() {
this.add(Base);
this.setTitle("学生信息管理系统");
this.setLocation(300, 200);
this.setSize(800, 550);
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
this.setResizable(false);
this.setVisible(true);
}
// 设置数据库信息的方法
private void setDB() {
db = new OperationMySql();
// 连接数据库
db.setDburl("jdbc:sqlserver://127.0.0.1:1433;DatabaseName=text");
// 加载驱动
db.setDbdriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");
db.setUsername("sa");
db.setPassword("123456");
}
// 初始化
void initial() {
// 给各按钮添加监听器
// InsertRecord, InsertReset, DeleteRecord, DeleteReset, QueryRecord, UpdateRecord, CourseQuery, GradeQuery;
InsertRecord1.addMouseListener(this);
InsertReset1.addMouseListener(this);
}
@Override
public void mouseClicked(MouseEvent e) {
// 添加按钮功能
// 点击重置键则清空文本框
// 查询学生信息功能
if (e.getSource().equals(InsertReset1)) {
InsertID2.setText("");
InsertID2.setFont(new Font("宋体", 1, 23));
} else if (e.getSource().equals(InsertRecord1)) {
String InsertStuID = InsertID2.getText();
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String id = InsertStuID;
//生成一条sql语句
String sql="SELECT * FROM student left outer join grade on(student.学号=grade.学号) left outer join course on(grade.课程号=course.课程号) WHERE grade.学号=" + id;
Statement statement=conn.createStatement();//创建一个Statement对象
ResultSet resultSet=statement.executeQuery(sql);//执行sql查询语句
QueryRecordResult.setText("查询结果：" + "\n" +
"学号" + "\t" + " 姓名" + "\t" + " 课程名称" + "\t" + " 成绩" + "\t" + " 补考成绩" + "\t\n");
// 输出结果集记录
while (resultSet.next()) {
QueryRecordResult.append(
resultSet.getString("学号") + "\t"
+ resultSet.getString("姓名") + "\t"
+ resultSet.getString("课程名") + "\t"
+ resultSet.getString("成绩") + "\t"
+ resultSet.getString("补考成绩") + "\t\n");
}
conn.close();
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
JOptionPane.showOptionDialog(jf, "查询信息失败！", "查询失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
}
}
}
@Override
public void mousePressed(MouseEvent e) {
}
@Override
public void mouseReleased(MouseEvent e) {
}
@Override
public void mouseEntered(MouseEvent e) {
}
@Override
public void mouseExited(MouseEvent e) {
}
@Override
public void itemStateChanged(ItemEvent e) {
}
}身份选择管理员，系统需要根据数据库中存储的管理员ID及密码，对管理员进行身份认证
如果管理员身份验证失败，及密码输入错误，系统提示错误信息
身份验证成功，可以对数据库进行增删改查操作
package StudentSQL;
import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class AdministratorLogin implements ActionListener {
// 定义主窗口
private final JFrame jf;
// 定义输入用户名和密码的标签提示
private final JLabel InputUserName;
private final JLabel InputPassWord;
// 定义输入用户名文本框
private final JTextField UserName;
// 定义输入密码框
private final JPasswordField PassWord;
// 定义登录和取消按钮
private final JButton Login;
private final JButton Cancel;
AdministratorLogin() {
// 各组件实例化过程
jf = new JFrame("管理员登录");
InputUserName = new JLabel("管理员ID：");
InputPassWord = new JLabel("密 码 ：");
UserName = new JTextField();
PassWord = new JPasswordField();
Login = new JButton("登录");
Cancel = new JButton("退出");
// 设置主窗口大小、位置和布局
jf.setSize(400, 300);
jf.setLocationRelativeTo(null);//在屏幕中间显示(居中显示)
// 设置窗口流式布局
jf.setLayout(new FlowLayout());
// 设置用户名和密码框大小
UserName.setPreferredSize(new Dimension(300, 30));
PassWord.setPreferredSize(new Dimension(300, 30));
// 依次向主窗口添加各组件
jf.getContentPane().add(InputUserName);
jf.getContentPane().add(UserName);
jf.getContentPane().add(InputPassWord);
jf.getContentPane().add(PassWord);
jf.getContentPane().add(Login);
jf.getContentPane().add(Cancel);
// 设置主窗口不可调节大小
jf.setResizable(false);
// 设置主窗口默认关闭操作
jf.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
// 给登录和取消按钮添加 Action 监听器
Login.addActionListener(this);
Cancel.addActionListener(this);
// 设置主窗口可见
jf.setVisible(true);
}
@Override
public void actionPerformed(ActionEvent e) {
// 如果单击【退出】按钮则程序退出
if (e.getSource().equals(Cancel)) {
System.exit(0);
}
// 如果单击【登录】按钮则检查用户名和密码是否匹配
else if (e.getSource().equals(Login))
{
try
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");//加载数据库驱动
String url="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=text";//声明数据库test的url
String user="sa";//数据库的用户名
String password="123456";//数据库的密码
Connection conn=DriverManager.getConnection(url, user, password);
System.out.println("正在进行身份验证...");
String sql="SELECT * FROM administrator WHERE 管理员ID=" + UserName.getText();
//生成一条sql的查询语句
Statement statement=conn.createStatement();//创建一个Statement对象
ResultSet resultSet=statement.executeQuery(sql);//执行sql查询语句
while(resultSet.next())
{
// 如果用户名和密码匹配，则打开具体操作面板
if(UserName.getText().equals(resultSet.getString("管理员ID")) && String.valueOf(PassWord.getPassword()).equals(resultSet.getString("密码")))
{
Administrator admini = new Administrator();
jf.dispose();
}
// 如果用户名和密码不匹配，则给出提示对话框
else
{
JOptionPane.showOptionDialog(jf, "用户名或密码错误", "登陆失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
}
}
}catch (ClassNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}//
catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
}管理员选择要进行的操作
package StudentSQL;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
import javax.swing.JButton;
import javax.swing.JDesktopPane;
import javax.swing.JFrame;
import javax.swing.JInternalFrame.JDesktopIcon;
import javax.swing.JLabel;
import javax.swing.JPanel;
public class Administrator{
private JDesktopPane Jd;
//定义组件
JFrame frame=new JFrame();
JPanel jp1,jp2,jp3;//面板
JLabel jlb1,jlb2,jlb3,jlb4;//标签
JButton jb1,jb2,jb3,jb4;//按钮
public Administrator()
{
//创建面板
jp1=new JPanel();
jp2=new JPanel();
jp3=new JPanel();
//创建标签
jlb1=new JLabel("请选择你要进行的操作：");
//创建按钮
jb1=new JButton("添加信息");
jb2=new JButton("删除信息");
jb3=new JButton("修改信息");
jb4=new JButton("查询信息");
//设置布局管理
frame.setLayout(new GridLayout(3, 1));//网格式布局
//加入各个组件
jp1.add(jlb1);
jp3.add(jb1);
jp3.add(jb2);
jp3.add(jb3);
jp3.add(jb4);
//加入到JFrame
frame.add(jp2);
frame.add(jp1);
frame.add(jp3);
//设置窗体
frame.setTitle("管理员界面");//窗体标签
frame.setSize(400, 300);//窗体大小
frame.setLocationRelativeTo(null);//在屏幕中间显示(居中显示)
frame.setVisible(true);//显示窗体
//锁定窗体
//this.setResizable(false);
jb1.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
new AdditionGUI();
}
});
jb2.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
// frame.setVisible(false);
new DeleteGUI();
}
});
jb3.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
// frame.setVisible(false);
new UpdateGUI();
}
});
jb4.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
// frame.setVisible(false);
new SelectGUI();
}
});
}
}对数据库进行添加信息操作：
代码：
package StudentSQL;
import javax.swing.*;
import java.awt.*;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.awt.event.MouseEvent;
import java.awt.event.MouseListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class AdditionGUI extends JFrame implements MouseListener, ItemListener {
protected JFrame jf;
// 定义选项卡
private JTabbedPane Base;
// 定义选项卡上的嵌板
/*
* jp1, 添加学生信息
* jp2, 添加课程信息
* jp3, 添加成绩信息
* jp4, 添加管理员信息
* */
private JPanel jp1, jp2, jp3, jp4;
// 定义各按钮
/*
* InsertRecord, 添加记录按钮
* InsertReset, 添加取消按钮
* */
private JButton InsertRecord1, InsertReset1,InsertRecord2,InsertReset2,
InsertRecord3, InsertReset3,InsertRecord4, InsertReset4;
// 定义各标签
/*
* InsertID1, 插入学号提示标签
* InsertName1, 插入姓名提示标签
* InsertSex1, 插入性别提示标签
* InsertBirth1, 插入出生日期提示标签
* InsertClass1, 插入班级提示标签
* */
private JLabel InsertID1, InsertName1, InsertSex1, InsertBirth1,InsertClass1,
InsertCno1,InsertCname1,InsertTime1,InsertCredit1,
InsertGraID1,InsertGraCno1,InsertGrade1,InsertAgainGrade1,
InsertAdminID1,InsertAdiminName1,InsertAdminPassword1;
// 定义各文本框
/*
* InsertID2, 插入学号文本框
* InsertName2, 插入姓名文本框
* InsertSex2, 插入性别文本框
* InsertBirth2, 插入出生日期文本框
* InsertClass2, 插入班级文本框
* */
private JTextField InsertID2, InsertName2, InsertSex2, InsertBirth2, InsertClass2,
InsertCno2,InsertCname2,InsertTime2,InsertCredit2,
InsertGraID2,InsertGraCno2,InsertGrade2,InsertAgainGrade2,
InsertAdminID2,InsertAdiminName2,InsertAdminPassword2;
// 定义一个数据库操作的实例
private OperationMySql db = null;
AdditionGUI() {
// 设置各按钮信息
setButton();
// 设置各标签信息
setLabel();
// 设置各文本框信息
setTextField();
// 设置各面板信息
setPanel();
// 设置布局信息
setLayout();
// 设置选项卡信息
setBase();
// 设置主窗口信息
setThis();
// 设置数据库信息
setDB();
}
// 设置各按钮信息的方法
private void setButton() {
// jp1 上的按钮
InsertRecord1 = new JButton("添加");
InsertRecord1.setBounds(150, 400, 100, 45);
InsertRecord1.setMargin(new Insets(0, 0, 0, 0)); // 设置按钮的边缘空白为四个方向全为0，也即让按钮中的文本与按钮边缘贴齐
InsertReset1 = new JButton("重置");
InsertReset1.setBounds(300, 400, 100, 45);
InsertReset1.setMargin(new Insets(0, 0, 0, 0));
// jp2 上的按钮
InsertRecord2 = new JButton("添加");
InsertRecord2.setBounds(150, 400, 100, 45);
InsertRecord2.setMargin(new Insets(0, 0, 0, 0)); // 设置按钮的边缘空白为四个方向全为0，也即让按钮中的文本与按钮边缘贴齐
InsertReset2 = new JButton("重置");
InsertReset2.setBounds(300, 400, 100, 45);
InsertReset2.setMargin(new Insets(0, 0, 0, 0));
// jp3 上的按钮
InsertRecord3 = new JButton("添加");
InsertRecord3.setBounds(150, 400, 100, 45);
InsertRecord3.setMargin(new Insets(0, 0, 0, 0)); // 设置按钮的边缘空白为四个方向全为0，也即让按钮中的文本与按钮边缘贴齐
InsertReset3 = new JButton("重置");
InsertReset3.setBounds(300, 400, 100, 45);
InsertReset3.setMargin(new Insets(0, 0, 0, 0));
// jp4 上的按钮
InsertRecord4 = new JButton("添加");
InsertRecord4.setBounds(150, 400, 100, 45);
InsertRecord4.setMargin(new Insets(0, 0, 0, 0)); // 设置按钮的边缘空白为四个方向全为0，也即让按钮中的文本与按钮边缘贴齐
InsertReset4 = new JButton("重置");
InsertReset4.setBounds(300, 400, 100, 45);
InsertReset4.setMargin(new Insets(0, 0, 0, 0));
// 按键监听初始化
initial();
}
// 设置各标签信息的方法
private void setLabel() {
// jp1 上的标签
InsertID1 = new JLabel("学 号：");
InsertID1.setBounds(100, 40, 120, 50);
InsertName1 = new JLabel("姓 名：");
InsertName1.setBounds(100, 100, 120, 50);
InsertSex1 = new JLabel("性 别：");
InsertSex1.setBounds(100, 160, 120, 50);
InsertBirth1 = new JLabel("出生日期：");
InsertBirth1.setBounds(100, 220, 120, 50);
InsertClass1 = new JLabel("班 级：");
InsertClass1.setBounds(100, 280, 120, 50);
InsertCname1 = new JLabel("课程名");
InsertCname1.setBounds(100, 40, 120, 50);
InsertCno1 = new JLabel("课程号");
InsertCno1.setBounds(100, 100, 120, 50);
InsertTime1 = new JLabel("学 时");
InsertTime1.setBounds(100, 160, 120, 50);
InsertCredit1 = new JLabel("学 分");
InsertCredit1.setBounds(100, 220, 120, 50);
InsertGraID1 = new JLabel("学 号");
InsertGraID1.setBounds(100, 40, 120, 50);
InsertGraCno1 = new JLabel("课程号");
InsertGraCno1.setBounds(100, 100, 120, 50);
InsertGrade1 = new JLabel("成 绩");
InsertGrade1.setBounds(100, 160, 120, 50);
InsertAgainGrade1 = new JLabel("补考成绩");
InsertAgainGrade1.setBounds(100, 220, 120, 50);
InsertAdminID1 = new JLabel("管理员ID");
InsertAdminID1.setBounds(100, 40, 120, 50);
InsertAdiminName1 = new JLabel("姓 名");
InsertAdiminName1.setBounds(100, 100, 120, 50);
InsertAdminPassword1 = new JLabel("密 码");
InsertAdminPassword1.setBounds(100, 160, 120, 50);
}
// 设置各文本框信息的方法
private void setTextField() {
// jp1 上的文本框
InsertID2 = new JTextField();
InsertID2.setBounds(210, 40, 200, 35);
InsertName2 = new JTextField();
InsertName2.setBounds(210, 100, 200, 35);
InsertSex2 = new JTextField();
InsertSex2.setBounds(210, 160, 200, 35);
InsertBirth2 = new JTextField();
InsertBirth2.setBounds(210, 220, 200, 35);
InsertClass2 = new JTextField();
InsertClass2.setBounds(210, 280, 200, 35);
InsertCname2 = new JTextField();
InsertCname2.setBounds(210, 40, 200, 35);
InsertCno2 = new JTextField();
InsertCno2.setBounds(210, 100, 200, 35);
InsertTime2 = new JTextField();
InsertTime2.setBounds(210, 160, 200, 35);
InsertCredit2 = new JTextField();
InsertCredit2.setBounds(210, 220, 200, 35);
InsertGraID2 = new JTextField();
InsertGraID2.setBounds(210, 40, 200, 35);
InsertGraCno2 = new JTextField();
InsertGraCno2.setBounds(210, 100, 200, 35);
InsertGrade2 = new JTextField();
InsertGrade2.setBounds(210, 160, 200, 35);
InsertAgainGrade2 = new JTextField();
InsertAgainGrade2.setBounds(210, 220, 200, 35);
InsertAdminID2 = new JTextField();
InsertAdminID2.setBounds(210, 40, 200, 35);
InsertAdiminName2 = new JTextField();
InsertAdiminName2.setBounds(210, 100, 200, 35);
InsertAdminPassword2 = new JTextField();
InsertAdminPassword2.setBounds(210, 160, 200, 35);
}
// 设置各面板信息的方法
private void setPanel() {
jp1 = new JPanel();
jp2 = new JPanel();
jp3 = new JPanel();
jp4 = new JPanel();
}
// 设置布局信息的方法
private void setLayout() {
// 添加 jp1 的组件
jp1.setLayout(null);
jp1.add(InsertRecord1);
jp1.add(InsertReset1);
jp1.add(InsertID1);
jp1.add(InsertName1);
jp1.add(InsertSex1);
jp1.add(InsertBirth1);
jp1.add(InsertClass1);
jp1.add(InsertID2);
jp1.add(InsertName2);
jp1.add(InsertSex2);
jp1.add(InsertBirth2);
jp1.add(InsertClass2);
// 添加 jp2 上的组件
jp2.setLayout(null);
jp2.add(InsertRecord2);
jp2.add(InsertReset2);
jp2.add(InsertCname1);
jp2.add(InsertCno1);
jp2.add(InsertTime1);
jp2.add(InsertCredit1);
jp2.add(InsertCname2);
jp2.add(InsertCno2);
jp2.add(InsertTime2);
jp2.add(InsertCredit2);
// 添加 jp3 上的组件
jp3.setLayout(null);
jp3.add(InsertRecord3);
jp3.add(InsertReset3);
jp3.add(InsertGraID1);
jp3.add(InsertGraCno1);
jp3.add(InsertGrade1);
jp3.add(InsertAgainGrade1);
jp3.add(InsertGraID2);
jp3.add(InsertGraCno2);
jp3.add(InsertGrade2);
jp3.add(InsertAgainGrade2);
// 添加 jp4 上的组件
jp4.setLayout(null);
jp4.add(InsertRecord4);
jp4.add(InsertReset4);
jp4.add(InsertAdminID1);
jp4.add(InsertAdiminName1);
jp4.add(InsertAdminPassword1);
jp4.add(InsertAdminID2);
jp4.add(InsertAdiminName2);
jp4.add(InsertAdminPassword2);
}
// 设置选项卡信息的方法
private void setBase() {
Base = new JTabbedPane(JTabbedPane.TOP);
Base.addTab("添加学生信息", jp1);
Base.addTab("添加课程信息", jp2);
Base.addTab("添加成绩信息", jp3);
Base.addTab("添加管理员信息", jp4);
}
// 设置主窗口信息的方法
private void setThis() {
this.add(Base);
this.setTitle("学生信息管理系统");
this.setLocation(300, 200);
this.setSize(800, 550);
this.setResizable(false);
this.setVisible(true);
// this.setClosable(true);
// this.setIconifiable(true);
}
// 设置数据库信息的方法
private void setDB() {
db = new OperationMySql();
// 连接 mysql
db.setDburl("jdbc:sqlserver://127.0.0.1:1433;DatabaseName=text");
// 加载驱动
db.setDbdriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");
db.setUsername("sa");
db.setPassword("123456");
}
// 初始化
void initial() {
// 给各按钮添加监听器
// InsertRecord, InsertReset, DeleteRecord, DeleteReset, QueryRecord, UpdateRecord, CourseQuery, GradeQuery;
InsertRecord1.addMouseListener(this);
InsertReset1.addMouseListener(this);
InsertRecord2.addMouseListener(this);
InsertReset2.addMouseListener(this);
InsertRecord3.addMouseListener(this);
InsertReset3.addMouseListener(this);
InsertRecord4.addMouseListener(this);
InsertReset4.addMouseListener(this);
}
@Override
public void mouseClicked(MouseEvent e) {
// 添加按钮功能
// 点击重置键则清空文本框
// 添加学生信息功能
if (e.getSource().equals(InsertReset1)) {
InsertID2.setText("");
InsertID2.setFont(new Font("宋体", 1, 23));
InsertName2.setText("");
InsertName2.setFont(new Font("宋体", 1, 23));
InsertSex2.setText("");
InsertSex2.setFont(new Font("宋体", 1, 23));
InsertBirth2.setText("");
InsertBirth2.setFont(new Font("宋体", 1, 23));
InsertClass2.setText("");
InsertClass2.setFont(new Font("宋体", 1, 23));
} else if (e.getSource().equals(InsertRecord1)) {
String InsertStuID = InsertID2.getText();
String InsertStuName = InsertName2.getText();
String InsertStuSex = InsertSex2.getText();
String InsertStuBirth = InsertBirth2.getText();
String InsertStuClass = InsertClass2.getText();
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
//生成一条mysql语句
String sql="insert into student(学号,姓名,性别,出生日期,班级) values('" + InsertStuID + "','" + InsertStuName + "','" + InsertStuSex + "','" + InsertStuBirth + "','" + InsertStuClass + "')";
Statement stmt=conn.createStatement();//创建一个Statement对象
stmt.executeUpdate(sql);//执行sql语句
System.out.println("添加信息成功");
JOptionPane.showOptionDialog(jf, "添加信息成功！", "添加成功",
JOptionPane.CLOSED_OPTION,
JOptionPane.INFORMATION_MESSAGE, null, null, null);
conn.close();
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
JOptionPane.showOptionDialog(jf, "添加信息失败！", "添加失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
}
}
// 添加课程信息功能
else if (e.getSource().equals(InsertReset2)) {
InsertCno2.setText("");
InsertCno2.setFont(new Font("宋体", 1, 23));
InsertCname2.setText("");
InsertCname2.setFont(new Font("宋体", 1, 23));
InsertTime2.setText("");
InsertTime2.setFont(new Font("宋体", 1, 23));
InsertCredit2.setText("");
InsertCredit2.setFont(new Font("宋体", 1, 23));
} else if (e.getSource().equals(InsertRecord2)) {
String InsertCouID = InsertCno2.getText();
String InsertCouName = InsertCname2.getText();
String InsertCouTime = InsertTime2.getText();
String InsertCouCredit = InsertCredit2.getText();
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String id = InsertCouID;
String name = InsertCouName;
String time = InsertCouTime;
String credit = InsertCouCredit;
//生成一条mysql语句
String sql="insert into course(课程号,课程名,学时,学分) values('" + id + "','" + name + "','" + time + "','" + credit + "')";
Statement stmt=conn.createStatement();//创建一个Statement对象
stmt.executeUpdate(sql);//执行sql语句
System.out.println("添加信息成功！");
JOptionPane.showOptionDialog(jf, "添加信息成功！", "添加成功",
JOptionPane.CLOSED_OPTION,
JOptionPane.INFORMATION_MESSAGE, null, null, null);
conn.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
JOptionPane.showOptionDialog(jf, "添加信息失败！", "添加失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
e1.printStackTrace();
}
}
//添加成绩信息
else if (e.getSource().equals(InsertReset3)) {
InsertGraID2.setText("");
InsertGraID2.setFont(new Font("宋体", 1, 23));
InsertGraCno2.setText("");
InsertGraCno2.setFont(new Font("宋体", 1, 23));
InsertGrade2.setText("");
InsertGrade2.setFont(new Font("宋体", 1, 23));
InsertAgainGrade2.setText("");
InsertAgainGrade2.setFont(new Font("宋体", 1, 23));
} else if (e.getSource().equals(InsertRecord3)) {
String InsertGraID = InsertGraID2.getText();
String InsertGraCno = InsertGraCno2.getText();
String InsertGrade = InsertGrade2.getText();
String InsertAgainGrade = InsertAgainGrade2.getText();
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String studentID = InsertGraID;
String classID = InsertGraCno;
String grade = InsertGrade;
String newGrade = InsertAgainGrade;
//生成一条mysql语句
String sql="insert into grade(学号,课程号,成绩,补考成绩) values('" + studentID + "','" + classID + "','" + grade + "','" + newGrade + "')";
Statement stmt=conn.createStatement();//创建一个Statement对象
stmt.executeUpdate(sql);//执行sql语句
System.out.println("添加信息成功！");
JOptionPane.showOptionDialog(jf, "添加信息成功！", "添加成功",
JOptionPane.CLOSED_OPTION,
JOptionPane.INFORMATION_MESSAGE, null, null, null);
conn.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
JOptionPane.showOptionDialog(jf, "添加信息失败！", "添加失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
e1.printStackTrace();
}
}
//添加管理员信息
else if (e.getSource().equals(InsertReset4)) {
InsertAdminID2.setText("");
InsertAdminID2.setFont(new Font("宋体", 1, 23));
InsertAdiminName2.setText("");
InsertAdiminName2.setFont(new Font("宋体", 1, 23));
InsertAdminPassword2.setText("");
InsertAdminPassword2.setFont(new Font("宋体", 1, 23));
} else if (e.getSource().equals(InsertRecord4)) {
String InsertID = InsertAdminID2.getText();
String InsertName = InsertAdiminName2.getText();
String InsertPassword = InsertAdminPassword2.getText();
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String id = InsertID;
String name = InsertName;
String password = InsertPassword;
//生成一条mysql语句
String sql="insert into administrator(管理员ID,姓名,密码) values('" + id + "','" + name + "','" + password + "')";
Statement stmt=conn.createStatement();//创建一个Statement对象
stmt.executeUpdate(sql);//执行sql语句
System.out.println("添加信息成功！");
JOptionPane.showOptionDialog(jf, "添加信息成功！", "添加成功",
JOptionPane.CLOSED_OPTION,
JOptionPane.INFORMATION_MESSAGE, null, null, null);
conn.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
JOptionPane.showOptionDialog(jf, "添加信息失败！", "添加失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
e1.printStackTrace();
}
}
}
@Override
public void mousePressed(MouseEvent e) {
}
@Override
public void mouseReleased(MouseEvent e) {
}
@Override
public void mouseEntered(MouseEvent e) {
}
@Override
public void mouseExited(MouseEvent e) {
}
@Override
public void itemStateChanged(ItemEvent e) {
}
}删除操作：
代码：
package StudentSQL;
import javax.swing.*;
import java.awt.*;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.awt.event.MouseEvent;
import java.awt.event.MouseListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class DeleteGUI extends JFrame implements MouseListener, ItemListener {
protected JFrame jf;
// 定义选项卡
private JTabbedPane Base;
// 定义选项卡上的嵌板
private JPanel jp1, jp2, jp3, jp4,jp5;
// 定义各按钮
private JButton InsertRecord1, InsertReset1,InsertRecord2,InsertReset2,
InsertRecord3, InsertReset3,InsertRecord4, InsertReset4,
Delete1,Delete2,Delete3,Delete4;
// 定义各标签
private JLabel InsertID1,InsertCno1,InsertGraID1,InsertGraCno1,InsertAdminID1;
// 定义各文本框
private JTextField InsertID2,InsertCno2,InsertGraID2,InsertGraCno2,InsertAdminID2;
private JTextArea QueryRecordResult, CourseQueryResult, GradeQueryResult,AdminiQueryResult;
// 定义滚动条
private JScrollPane scroll = null;
private JScrollPane CourseScroll = null;
private JScrollPane GradeScroll = null;
// 定义一个数据库操作的实例
private OperationMySql db = null;
DeleteGUI() {
// 设置各按钮信息
setButton();
// 设置各标签信息
setLabel();
// 设置各文本框信息
setTextField();
// 设置各面板信息
setPanel();
// 设置布局信息
setLayout();
// 设置选项卡信息
setBase();
// 设置主窗口信息
setThis();
// 设置数据库信息
setDB();
}
// 设置各按钮信息的方法
private void setButton() {
// jp1 上的按钮
InsertRecord1 = new JButton("查询");
InsertRecord1.setBounds(150, 400, 100, 45);
InsertRecord1.setMargin(new Insets(0, 0, 0, 0)); // 设置按钮的边缘空白为四个方向全为0，也即让按钮中的文本与按钮边缘贴齐
InsertReset1 = new JButton("重置");
InsertReset1.setBounds(300, 400, 100, 45);
Delete1 = new JButton("删除");
Delete1.setBounds(450, 400, 100, 45);
Delete1.setMargin(new Insets(0, 0, 0, 0));
// jp2 上的按钮
InsertRecord2 = new JButton("查询");
InsertRecord2.setBounds(150, 400, 100, 45);
InsertRecord2.setMargin(new Insets(0, 0, 0, 0)); // 设置按钮的边缘空白为四个方向全为0，也即让按钮中的文本与按钮边缘贴齐
InsertReset2 = new JButton("重置");
InsertReset2.setBounds(300, 400, 100, 45);
InsertReset2.setMargin(new Insets(0, 0, 0, 0));
Delete2 = new JButton("删除");
Delete2.setBounds(450, 400, 100, 45);
Delete2.setMargin(new Insets(0, 0, 0, 0));
// jp3 上的按钮
InsertRecord3 = new JButton("查询");
InsertRecord3.setBounds(150, 400, 100, 45);
InsertRecord3.setMargin(new Insets(0, 0, 0, 0)); // 设置按钮的边缘空白为四个方向全为0，也即让按钮中的文本与按钮边缘贴齐
InsertReset3 = new JButton("重置");
InsertReset3.setBounds(300, 400, 100, 45);
InsertReset3.setMargin(new Insets(0, 0, 0, 0));
Delete3 = new JButton("删除");
Delete3.setBounds(450, 400, 100, 45);
Delete3.setMargin(new Insets(0, 0, 0, 0));
// jp4 上的按钮
InsertRecord4 = new JButton("查询");
InsertRecord4.setBounds(150, 400, 100, 45);
InsertRecord4.setMargin(new Insets(0, 0, 0, 0)); // 设置按钮的边缘空白为四个方向全为0，也即让按钮中的文本与按钮边缘贴齐
InsertReset4 = new JButton("重置");
InsertReset4.setBounds(300, 400, 100, 45);
InsertReset4.setMargin(new Insets(0, 0, 0, 0));
Delete4 = new JButton("删除");
Delete4.setBounds(450, 400, 100, 45);
Delete4.setMargin(new Insets(0, 0, 0, 0));
// 按键监听初始化
initial();
}
// 设置各标签信息的方法
private void setLabel() {
// jp1 上的标签
InsertID1 = new JLabel("学 号：");
InsertID1.setBounds(100, 40, 120, 50);
InsertCno1 = new JLabel("课程号");
InsertCno1.setBounds(100, 40, 120, 50);
InsertGraID1 = new JLabel("学 号");
InsertGraID1.setBounds(100, 40, 120, 50);
InsertGraCno1 = new JLabel("课程号");
InsertGraCno1.setBounds(100, 80, 120, 50);
InsertAdminID1 = new JLabel("管理员ID");
InsertAdminID1.setBounds(100, 40, 120, 50);
}
// 设置各文本框信息的方法
private void setTextField() {
// jp1 上的文本框
InsertID2 = new JTextField();
InsertID2.setBounds(210, 40, 200, 35);
QueryRecordResult = new JTextArea("查询结果：");
QueryRecordResult.setBounds(30,100,660,200);
QueryRecordResult.setEditable(false);
QueryRecordResult.setLineWrap(true);
scroll = new JScrollPane(QueryRecordResult); // 添加滚动条
scroll.setBounds(30, 30, 560, 260);
scroll.setVerticalScrollBarPolicy(ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED); // 当需要垂直滚动条时显示
scroll.setHorizontalScrollBarPolicy(ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED);// 当需要水平滚动条时显示
InsertCno2 = new JTextField();
InsertCno2.setBounds(210, 40, 200, 35);
CourseQueryResult = new JTextArea("查询结果：");
CourseQueryResult.setBounds(30,100,660,200);
CourseQueryResult.setEditable(false);
CourseQueryResult.setLineWrap(true);
scroll = new JScrollPane(CourseQueryResult); // 添加滚动条
scroll.setBounds(30, 30, 560, 260);
scroll.setVerticalScrollBarPolicy(ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED); // 当需要垂直滚动条时显示
scroll.setHorizontalScrollBarPolicy(ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED);// 当需要水平滚动条时显示
InsertGraID2 = new JTextField();
InsertGraID2.setBounds(210, 40, 200, 35);
InsertGraCno2 = new JTextField();
InsertGraCno2.setBounds(210, 80, 200, 35);
GradeQueryResult = new JTextArea("查询结果：");
GradeQueryResult.setBounds(30,140,660,200);
GradeQueryResult.setEditable(false);
GradeQueryResult.setLineWrap(true);
scroll = new JScrollPane(GradeQueryResult); // 添加滚动条
scroll.setBounds(30, 30, 560, 260);
scroll.setVerticalScrollBarPolicy(ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED); // 当需要垂直滚动条时显示
scroll.setHorizontalScrollBarPolicy(ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED);// 当需要水平滚动条时显示
InsertAdminID2 = new JTextField();
InsertAdminID2.setBounds(210, 40, 200, 35);
AdminiQueryResult = new JTextArea("查询结果：");
AdminiQueryResult.setBounds(30,100,660,200);
AdminiQueryResult.setEditable(false);
AdminiQueryResult.setLineWrap(true);
scroll = new JScrollPane(AdminiQueryResult); // 添加滚动条
scroll.setBounds(30, 30, 560, 260);
scroll.setVerticalScrollBarPolicy(ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED); // 当需要垂直滚动条时显示
scroll.setHorizontalScrollBarPolicy(ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED);// 当需要水平滚动条时显示
}
// 设置各面板信息的方法
private void setPanel() {
jp1 = new JPanel();
jp2 = new JPanel();
jp3 = new JPanel();
jp4 = new JPanel();
}
// 设置布局信息的方法
private void setLayout() {
// 添加 jp1 的组件
jp1.setLayout(null);
jp1.add(InsertRecord1);
jp1.add(InsertReset1);
jp1.add(InsertID1);
jp1.add(InsertID2);
jp1.add(QueryRecordResult);
jp1.add(Delete1);
// 添加 jp2 上的组件
jp2.setLayout(null);
jp2.add(InsertRecord2);
jp2.add(InsertReset2);
jp2.add(InsertCno1);
jp2.add(InsertCno2);
jp2.add(CourseQueryResult);
jp2.add(Delete2);
// 添加 jp3 上的组件
jp3.setLayout(null);
jp3.add(InsertRecord3);
jp3.add(InsertReset3);
jp3.add(InsertGraID1);
jp3.add(InsertGraID2);
jp3.add(InsertGraCno1);
jp3.add(InsertGraCno2);
jp3.add(GradeQueryResult);
jp3.add(Delete3);
// 添加 jp4 上的组件
jp4.setLayout(null);
jp4.add(InsertRecord4);
jp4.add(InsertReset4);
jp4.add(InsertAdminID1);
jp4.add(InsertAdminID2);
jp4.add(AdminiQueryResult);
jp4.add(Delete4);
}
// 设置选项卡信息的方法
private void setBase() {
Base = new JTabbedPane(JTabbedPane.TOP);
Base.addTab("删除学生信息", jp1);
Base.addTab("删除课程信息", jp2);
Base.addTab("删除成绩信息", jp3);
Base.addTab("删除管理员信息", jp4);
}
// 设置主窗口信息的方法
private void setThis() {
this.add(Base);
this.setTitle("学生信息管理系统");
this.setLocation(300, 200);
this.setSize(800, 550);
// this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
this.setResizable(false);
this.setVisible(true);
}
// 设置数据库信息的方法
private void setDB() {
db = new OperationMySql();
// 连接 mysql
db.setDburl("jdbc:sqlserver://127.0.0.1:1433;DatabaseName=text");
// 加载驱动
db.setDbdriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");
db.setUsername("sa");
db.setPassword("123456");
}
// 初始化
void initial() {
// 给各按钮添加监听器
// InsertRecord, InsertReset, DeleteRecord, DeleteReset, QueryRecord, UpdateRecord, CourseQuery, GradeQuery;
InsertRecord1.addMouseListener(this);
InsertReset1.addMouseListener(this);
InsertRecord2.addMouseListener(this);
InsertReset2.addMouseListener(this);
InsertRecord3.addMouseListener(this);
InsertReset3.addMouseListener(this);
InsertRecord4.addMouseListener(this);
InsertReset4.addMouseListener(this);
Delete1.addMouseListener(this);
Delete2.addMouseListener(this);
Delete3.addMouseListener(this);
Delete4.addMouseListener(this);
}
@Override
public void mouseClicked(MouseEvent e) {
// 添加按钮功能
// 点击重置键则清空文本框
// 查询学生信息功能
if (e.getSource().equals(InsertReset1)) {
InsertID2.setText("");
InsertID2.setFont(new Font("宋体", 1, 23));
} else if (e.getSource().equals(InsertRecord1)) {
String InsertStuID = InsertID2.getText();
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String id = InsertStuID;
//生成一条sql语句
String sql="SELECT * FROM student WHERE 学号=" + id;
Statement statement=conn.createStatement();//创建一个Statement对象
ResultSet resultSet=statement.executeQuery(sql);//执行sql查询语句
QueryRecordResult.setText("查询结果：" + "\n" +
"学号" + "\t" + " 姓名" + "\t" + " 性别" + "\t" + " 出生日期" + "\t" + " 班级" + "\t\n");
// 输出结果集记录
while (resultSet.next()) {
QueryRecordResult.append(
resultSet.getString("学号") + "\t"
+ resultSet.getString("姓名") + "\t"
+ resultSet.getString("性别") + "\t"
+ resultSet.getString("出生日期") + "\t"
+ resultSet.getString("班级") + "\t");
}
conn.close();
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
JOptionPane.showOptionDialog(jf, "查询信息失败！", "查询失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
}
}else if (e.getSource().equals(Delete1)) {
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String id = InsertID2.getText();
System.out.println("删除失败！");
String sqlDelete="delete from student where 学号=" + id;
Statement stmt=conn.createStatement();//创建一个Statement对象
stmt.executeUpdate(sqlDelete);//执行sql语句
JOptionPane.showOptionDialog(jf, "删除信息成功！", "删除成功",
JOptionPane.CLOSED_OPTION,
JOptionPane.INFORMATION_MESSAGE, null, null, null);
conn.close();
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
JOptionPane.showOptionDialog(jf, "删除信息失败！", "删除失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
}
}
//删除课程信息
else if (e.getSource().equals(InsertReset2)) {
InsertCno2.setText("");
InsertCno2.setFont(new Font("宋体", 1, 23));
} else if (e.getSource().equals(InsertRecord2)) {
String InsertCno= InsertCno2.getText();
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String id = InsertCno;
//生成一条sql语句
String sql="SELECT * FROM course WHERE 课程号=" + id;
Statement statement=conn.createStatement();//创建一个Statement对象
ResultSet resultSet=statement.executeQuery(sql);//执行sql查询语句
CourseQueryResult.setText("查询结果：" + "\n" +
"课程号" + "\t" + " 课程名" + "\t" + " 学时" + "\t" + "学分" + "\t" + "授课教师" + "\t\n");
// 输出结果集记录
while (resultSet.next()) {
CourseQueryResult.append(
resultSet.getString("课程号") + "\t"
+ resultSet.getString("课程名") + "\t"
+ resultSet.getString("学时") + "\t"
+ resultSet.getString("学分") + "\t"
+ resultSet.getString("授课教师") + "\t");
}
conn.close();
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
JOptionPane.showOptionDialog(jf, "查询信息失败！", "查询失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
}
}else if (e.getSource().equals(Delete2)) {
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String id = InsertCno2.getText();
String sqlDelete="delete from course where 课程号=" + id;
Statement stmt=conn.createStatement();//创建一个Statement对象
stmt.executeUpdate(sqlDelete);//执行sql语句
JOptionPane.showOptionDialog(jf, "删除信息成功！", "删除成功",
JOptionPane.CLOSED_OPTION,
JOptionPane.INFORMATION_MESSAGE, null, null, null);
conn.close();
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
JOptionPane.showOptionDialog(jf, "删除信息失败！", "删除失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
}
}
//删除成绩信息
else if (e.getSource().equals(InsertReset3)) {
InsertGraID2.setText("");
InsertGraID2.setFont(new Font("宋体", 1, 23));
InsertGraCno2.setText("");
InsertGraCno2.setFont(new Font("宋体", 1, 23));
} else if (e.getSource().equals(InsertRecord3)) {
String InsertGraID = InsertGraID2.getText();
try {
String id = InsertGraID;
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String sqlSelect="SELECT * FROM student left outer join grade on(student.学号=grade.学号) "
+ " left outer join course on(grade.课程号=course.课程号) WHERE grade.学号=" + id;
Statement stmtSelect = conn.createStatement();//创建一个Statement对象
ResultSet resultSet=stmtSelect.executeQuery(sqlSelect);//执行sql语句
GradeQueryResult.setText("查询结果：" + "\n" +
"学号" + "\t" + " 姓名" + "\t" + " 课程名" + "\t" + " 课程号" + "\t"
+ " 成绩" + "\t" + "补考成绩"+ "\t\n");
// 输出结果集记录
while (resultSet.next()) {
GradeQueryResult.append(
resultSet.getString("学号") + "\t"
+ resultSet.getString("姓名") + "\t"
+ resultSet.getString("课程名") + "\t"
+ resultSet.getString("课程号") + "\t"
+ resultSet.getString("成绩") + "\t"
+ resultSet.getString("补考成绩") + "\t\n");
}
conn.close();
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}else if (e.getSource().equals(Delete3)) {
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String id = InsertGraID2.getText();
String classID = InsertGraCno2.getText();
String sql="delete from grade where 学号=" + id + "AND 课程号=" + classID;
Statement stmt=conn.createStatement();//创建一个Statement对象
stmt.executeUpdate(sql);//执行sql语句
JOptionPane.showOptionDialog(jf, "删除信息成功！", "删除成功",
JOptionPane.CLOSED_OPTION,
JOptionPane.INFORMATION_MESSAGE, null, null, null);
conn.close();
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
JOptionPane.showOptionDialog(jf, "删除信息失败！", "删除失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
}
}
//删除管理员信息
else if (e.getSource().equals(InsertReset4)) {
InsertAdminID2.setText("");
InsertAdminID2.setFont(new Font("宋体", 1, 23));
} else if (e.getSource().equals(InsertRecord4)) {
String InsertID = InsertAdminID2.getText();
try {
String id = InsertID;
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String sql="SELECT * FROM administrator WHERE 管理员ID=" + id;
//生成一条sql的查询语句
Statement statement=conn.createStatement();//创建一个Statement对象
ResultSet resultSet=statement.executeQuery(sql);//执行sql查询语句
AdminiQueryResult.setText("查询结果：" + "\n" +
"管理员ID" + "\t" + " 姓名" + "\t" + " 密码" + "\t\n");
// 输出结果集记录
while (resultSet.next()) {
AdminiQueryResult.append(
resultSet.getString("管理员ID") + "\t\t"
+ resultSet.getString("姓名") + "\t\t"
+ resultSet.getString("密码") + "\t\n");
}
conn.close();
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}else if (e.getSource().equals(Delete4)) {
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String id = InsertAdminID2.getText();
String sqlDelete="delete from administrator where 管理员ID=" + id;
Statement stmt=conn.createStatement();//创建一个Statement对象
stmt.executeUpdate(sqlDelete);//执行sql语句
JOptionPane.showOptionDialog(jf, "删除信息成功！", "删除成功",
JOptionPane.CLOSED_OPTION,
JOptionPane.INFORMATION_MESSAGE, null, null, null);
conn.close();
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
JOptionPane.showOptionDialog(jf, "删除信息失败！", "删除失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
}
}
}
@Override
public void mousePressed(MouseEvent e) {
}
@Override
public void mouseReleased(MouseEvent e) {
}
@Override
public void mouseEntered(MouseEvent e) {
}
@Override
public void mouseExited(MouseEvent e) {
}
@Override
public void itemStateChanged(ItemEvent e) {
}
}修改：
package StudentSQL;
import javax.swing.*;
import java.awt.*;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.awt.event.MouseEvent;
import java.awt.event.MouseListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class UpdateGUI extends JFrame implements MouseListener, ItemListener {
protected JFrame jf;
// 定义选项卡
private JTabbedPane Base;
// 定义选项卡上的嵌板
private JPanel jp1, jp2, jp3, jp4;
// 定义各按钮
private JButton InsertRecord1, InsertReset1,InsertRecord2,InsertReset2,
InsertRecord3, InsertReset3,InsertRecord4, InsertReset4,
Select1,Select2,Select3,Select4;
// 定义各标签
private JLabel InsertID1, InsertName1, InsertSex1, InsertBirth1,InsertClass1,
InsertCno1,InsertCname1,InsertTime1,InsertCredit1,
InsertGraID1,InsertGraCno1,InsertGrade1,InsertAgainGrade1,
InsertAdminID1,InsertAdiminName1,InsertAdminPassword1;
// 定义各文本框
private JTextField InsertID2, InsertName2, InsertSex2, InsertBirth2, InsertClass2,
InsertCno2,InsertCname2,InsertTime2,InsertCredit2,
InsertGraID2,InsertGraCno2,InsertGrade2,InsertAgainGrade2,
InsertAdminID2,InsertAdiminName2,InsertAdminPassword2;
private JTextArea QueryRecordResult, CourseQueryResult, GradeQueryResult,AdminiQueryResult;
// 定义一个数据库操作的实例
private OperationMySql db = null;
UpdateGUI() {
// 设置各按钮信息
setButton();
// 设置各标签信息
setLabel();
// 设置各文本框信息
setTextField();
// 设置各面板信息
setPanel();
// 设置布局信息
setLayout();
// 设置选项卡信息
setBase();
// 设置主窗口信息
setThis();
// 设置数据库信息
setDB();
}
// 设置各按钮信息的方法
private void setButton() {
// jp1 上的按钮
InsertRecord1 = new JButton("确认修改");
InsertRecord1.setBounds(150, 400, 100, 45);
InsertRecord1.setMargin(new Insets(0, 0, 0, 0)); // 设置按钮的边缘空白为四个方向全为0，也即让按钮中的文本与按钮边缘贴齐
InsertReset1 = new JButton("重置");
InsertReset1.setBounds(300, 400, 100, 45);
InsertReset1.setMargin(new Insets(0, 0, 0, 0));
Select1 = new JButton("查询");
Select1.setBounds(450, 400, 100, 45);
Select1.setMargin(new Insets(0, 0, 0, 0));
// jp2 上的按钮
InsertRecord2 = new JButton("确认修改");
InsertRecord2.setBounds(150, 400, 100, 45);
InsertRecord2.setMargin(new Insets(0, 0, 0, 0)); // 设置按钮的边缘空白为四个方向全为0，也即让按钮中的文本与按钮边缘贴齐
InsertReset2 = new JButton("重置");
InsertReset2.setBounds(300, 400, 100, 45);
InsertReset2.setMargin(new Insets(0, 0, 0, 0));
Select2 = new JButton("查询");
Select2.setBounds(450, 400, 100, 45);
Select2.setMargin(new Insets(0, 0, 0, 0));
// jp3 上的按钮
InsertRecord3 = new JButton("确认修改");
InsertRecord3.setBounds(150, 400, 100, 45);
InsertRecord3.setMargin(new Insets(0, 0, 0, 0)); // 设置按钮的边缘空白为四个方向全为0，也即让按钮中的文本与按钮边缘贴齐
InsertReset3 = new JButton("重置");
InsertReset3.setBounds(300, 400, 100, 45);
InsertReset3.setMargin(new Insets(0, 0, 0, 0));
Select3 = new JButton("查询");
Select3.setBounds(450, 400, 100, 45);
Select3.setMargin(new Insets(0, 0, 0, 0));
// jp4 上的按钮
InsertRecord4 = new JButton("确认修改");
InsertRecord4.setBounds(150, 400, 100, 45);
InsertRecord4.setMargin(new Insets(0, 0, 0, 0)); // 设置按钮的边缘空白为四个方向全为0，也即让按钮中的文本与按钮边缘贴齐
InsertReset4 = new JButton("重置");
InsertReset4.setBounds(300, 400, 100, 45);
InsertReset4.setMargin(new Insets(0, 0, 0, 0));
Select4 = new JButton("查询");
Select4.setBounds(450, 400, 100, 45);
Select4.setMargin(new Insets(0, 0, 0, 0));
// 按键监听初始化
initial();
}
// 设置各标签信息的方法
private void setLabel() {
// jp1 上的标签
InsertID1 = new JLabel("学 号：");
InsertID1.setBounds(100, 40, 120, 50);
InsertName1 = new JLabel("姓 名：");
InsertName1.setBounds(100, 200, 120, 50);
InsertSex1 = new JLabel("性 别：");
InsertSex1.setBounds(100, 240, 120, 50);
InsertBirth1 = new JLabel("出生日期：");
InsertBirth1.setBounds(100, 280, 120, 50);
InsertClass1 = new JLabel("班 级：");
InsertClass1.setBounds(100, 320, 120, 50);
InsertCno1 = new JLabel("课程号");
InsertCno1.setBounds(100, 40, 120, 50);
InsertCname1 = new JLabel("课程名");
InsertCname1.setBounds(100, 200, 120, 50);
InsertTime1 = new JLabel("学 时");
InsertTime1.setBounds(100, 240, 120, 50);
InsertCredit1 = new JLabel("学 分");
InsertCredit1.setBounds(100, 280, 120, 50);
InsertGraID1 = new JLabel("学 号");
InsertGraID1.setBounds(100, 40, 120, 50);
InsertGraCno1 = new JLabel("课程号");
InsertGraCno1.setBounds(100, 200, 120, 50);
InsertGrade1 = new JLabel("成 绩");
InsertGrade1.setBounds(100, 240, 120, 50);
InsertAgainGrade1 = new JLabel("补考成绩");
InsertAgainGrade1.setBounds(100, 280, 120, 50);
InsertAdminID1 = new JLabel("管理员ID");
InsertAdminID1.setBounds(100, 40, 120, 50);
InsertAdiminName1 = new JLabel("姓 名");
InsertAdiminName1.setBounds(100, 200, 120, 50);
InsertAdminPassword1 = new JLabel("密 码");
InsertAdminPassword1.setBounds(100, 240, 120, 50);
}
// 设置各文本框信息的方法
private void setTextField() {
// jp1 上的文本框
InsertID2 = new JTextField();
InsertID2.setBounds(210, 40, 200, 35);
InsertName2 = new JTextField();
InsertName2.setBounds(210, 200, 200, 35);
InsertSex2 = new JTextField();
InsertSex2.setBounds(210, 240, 200, 35);
InsertBirth2 = new JTextField();
InsertBirth2.setBounds(210, 280, 200, 35);
InsertClass2 = new JTextField();
InsertClass2.setBounds(210, 320, 200, 35);
QueryRecordResult = new JTextArea("查询结果：");
QueryRecordResult.setBounds(30,80,660,100);
QueryRecordResult.setEditable(false);
QueryRecordResult.setLineWrap(true);
InsertCno2 = new JTextField();
InsertCno2.setBounds(210, 40, 200, 35);
InsertCname2 = new JTextField();
InsertCname2.setBounds(210, 200, 200, 35);
InsertTime2 = new JTextField();
InsertTime2.setBounds(210, 240, 200, 35);
InsertCredit2 = new JTextField();
InsertCredit2.setBounds(210, 280, 200, 35);
CourseQueryResult = new JTextArea("查询结果：");
CourseQueryResult.setBounds(30,80,660,100);
CourseQueryResult.setEditable(false);
CourseQueryResult.setLineWrap(true);
InsertGraID2 = new JTextField();
InsertGraID2.setBounds(210, 40, 200, 35);
InsertGraCno2 = new JTextField();
InsertGraCno2.setBounds(210, 200, 200, 35);
InsertGrade2 = new JTextField();
InsertGrade2.setBounds(210, 240, 200, 35);
InsertAgainGrade2 = new JTextField();
InsertAgainGrade2.setBounds(210, 280, 200, 35);
GradeQueryResult = new JTextArea("查询结果：");
GradeQueryResult.setBounds(30,80,660,100);
GradeQueryResult.setEditable(false);
GradeQueryResult.setLineWrap(true);
InsertAdminID2 = new JTextField();
InsertAdminID2.setBounds(210, 40, 200, 35);
InsertAdiminName2 = new JTextField();
InsertAdiminName2.setBounds(210, 200, 200, 35);
InsertAdminPassword2 = new JTextField();
InsertAdminPassword2.setBounds(210,240, 200, 35);
AdminiQueryResult = new JTextArea("查询结果：");
AdminiQueryResult.setBounds(30,80,660,100);
AdminiQueryResult.setEditable(false);
AdminiQueryResult.setLineWrap(true);
}
// 设置各面板信息的方法
private void setPanel() {
jp1 = new JPanel();
jp2 = new JPanel();
jp3 = new JPanel();
jp4 = new JPanel();
}
// 设置布局信息的方法
private void setLayout() {
// 添加 jp1 的组件
jp1.setLayout(null);
jp1.add(InsertRecord1);
jp1.add(InsertReset1);
jp1.add(InsertID1);
jp1.add(InsertName1);
jp1.add(InsertSex1);
jp1.add(InsertBirth1);
jp1.add(InsertClass1);
jp1.add(InsertID2);
jp1.add(InsertName2);
jp1.add(InsertSex2);
jp1.add(InsertBirth2);
jp1.add(InsertClass2);
jp1.add(QueryRecordResult);
jp1.add(Select1);
// 添加 jp2 上的组件
jp2.setLayout(null);
jp2.add(InsertRecord2);
jp2.add(InsertReset2);
jp2.add(InsertCno1);
jp2.add(InsertCname1);
jp2.add(InsertTime1);
jp2.add(InsertCredit1);
jp2.add(InsertCno2);
jp2.add(InsertCname2);
jp2.add(InsertTime2);
jp2.add(InsertCredit2);
jp2.add(CourseQueryResult);
jp2.add(Select2);
// 添加 jp3 上的组件
jp3.setLayout(null);
jp3.add(InsertRecord3);
jp3.add(InsertReset3);
jp3.add(InsertGraID1);
jp3.add(InsertGraCno1);
jp3.add(InsertGrade1);
jp3.add(InsertAgainGrade1);
jp3.add(InsertGraID2);
jp3.add(InsertGraCno2);
jp3.add(InsertGrade2);
jp3.add(InsertAgainGrade2);
jp3.add(GradeQueryResult);
jp3.add(Select3);
// 添加 jp4 上的组件
jp4.setLayout(null);
jp4.add(InsertRecord4);
jp4.add(InsertReset4);
jp4.add(InsertAdminID1);
jp4.add(InsertAdiminName1);
jp4.add(InsertAdminPassword1);
jp4.add(InsertAdminID2);
jp4.add(InsertAdiminName2);
jp4.add(InsertAdminPassword2);
jp4.add(AdminiQueryResult);
jp4.add(Select4);
}
// 设置选项卡信息的方法
private void setBase() {
Base = new JTabbedPane(JTabbedPane.TOP);
Base.addTab("修改学生信息", jp1);
Base.addTab("修改课程信息", jp2);
Base.addTab("修改成绩信息", jp3);
Base.addTab("修改管理员信息", jp4);
}
// 设置主窗口信息的方法
private void setThis() {
this.add(Base);
this.setTitle("学生信息管理系统");
this.setLocation(300, 200);
this.setSize(800, 550);
// this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); //强制关闭所有
this.setResizable(false);
this.setVisible(true);
}
// 设置数据库信息的方法
private void setDB() {
db = new OperationMySql();
// 连接 mysql
db.setDburl("jdbc:sqlserver://127.0.0.1:1433;DatabaseName=text");
// 加载驱动
db.setDbdriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");
db.setUsername("sa");
db.setPassword("123456");
}
// 初始化
void initial() {
// 给各按钮添加监听器
InsertRecord1.addMouseListener(this);
InsertReset1.addMouseListener(this);
InsertRecord2.addMouseListener(this);
InsertReset2.addMouseListener(this);
InsertRecord3.addMouseListener(this);
InsertReset3.addMouseListener(this);
InsertRecord4.addMouseListener(this);
InsertReset4.addMouseListener(this);
Select1.addMouseListener(this);
Select2.addMouseListener(this);
Select3.addMouseListener(this);
Select4.addMouseListener(this);
}
@Override
public void mouseClicked(MouseEvent e) {
// 添加按钮功能
// 点击重置键则清空文本框
// 修改学生信息功能
if (e.getSource().equals(InsertReset1)) {
InsertID2.setText("");
InsertID2.setFont(new Font("宋体", 1, 23));
InsertName2.setText("");
InsertName2.setFont(new Font("宋体", 1, 23));
InsertSex2.setText("");
InsertSex2.setFont(new Font("宋体", 1, 23));
InsertBirth2.setText("");
InsertBirth2.setFont(new Font("宋体", 1, 23));
InsertClass2.setText("");
InsertClass2.setFont(new Font("宋体", 1, 23));
} else if (e.getSource().equals(InsertRecord1)) {
String InsertStuID = InsertID2.getText();
String InsertStuName = InsertName2.getText();
String InsertStuSex = InsertSex2.getText();
String InsertStuBirth = InsertBirth2.getText();
String InsertStuClass = InsertClass2.getText();
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
//生成一条mysql语句
String sql="update student set 姓名=' " + InsertStuName + "',性别='" + InsertStuSex + "',出生日期='"
+ InsertStuBirth + "',班级='" + InsertStuClass + "' where 学号= " + InsertStuID;
Statement stmt=conn.createStatement();//创建一个Statement对象
stmt.executeUpdate(sql);//执行sql语句
JOptionPane.showOptionDialog(jf, "修改信息成功！", "修改成功",
JOptionPane.CLOSED_OPTION,
JOptionPane.INFORMATION_MESSAGE, null, null, null);
conn.close();
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
JOptionPane.showOptionDialog(jf, "修改信息失败！", "修改失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
}
}else if (e.getSource().equals(Select1)) {
String InsertStuID = InsertID2.getText();
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String id = InsertStuID;
//生成一条sql语句
String sql="SELECT * FROM student WHERE 学号=" + id;
Statement statement=conn.createStatement();//创建一个Statement对象
ResultSet resultSet=statement.executeQuery(sql);//执行sql查询语句
QueryRecordResult.setText("查询结果：" + "\n" +
"学号" + "\t" + " 姓名" + "\t" + " 性别" + "\t" + " 出生日期" + "\t" + " 班级" + "\t\n");
// 输出结果集记录
while (resultSet.next()) {
QueryRecordResult.append(
resultSet.getString("学号") + "\t"
+ resultSet.getString("姓名") + "\t"
+ resultSet.getString("性别") + "\t"
+ resultSet.getString("出生日期") + "\t"
+ resultSet.getString("班级") + "\t");
}
conn.close();
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
JOptionPane.showOptionDialog(jf, "查询信息失败！", "查询失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
}
}
// 修改课程信息功能
else if (e.getSource().equals(InsertReset2)) {
InsertCno2.setText("");
InsertCno2.setFont(new Font("宋体", 1, 23));
InsertCname2.setText("");
InsertCname2.setFont(new Font("宋体", 1, 23));
InsertTime2.setText("");
InsertTime2.setFont(new Font("宋体", 1, 23));
InsertCredit2.setText("");
InsertCredit2.setFont(new Font("宋体", 1, 23));
} else if (e.getSource().equals(InsertRecord2)) {
String InsertCouID = InsertCno2.getText();
String InsertCouName = InsertCname2.getText();
String InsertCouTime = InsertTime2.getText();
String InsertCouCredit = InsertCredit2.getText();
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String id = InsertCouID;
String name = InsertCouName;
String time = InsertCouTime;
String credit = InsertCouCredit;
//生成一条mysql语句
String sql="update course set 课程名=' " + InsertCouName + "',学时='" + InsertCouTime + "',学分='"
+ InsertCouCredit + "' where 课程号= " + InsertCouID;
Statement stmt=conn.createStatement();//创建一个Statement对象
stmt.executeUpdate(sql);//执行sql语句
System.out.println("修改信息成功！");
JOptionPane.showOptionDialog(jf, "修改信息成功！", "修改成功",
JOptionPane.CLOSED_OPTION,
JOptionPane.INFORMATION_MESSAGE, null, null, null);
conn.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
JOptionPane.showOptionDialog(jf, "修改信息失败！", "修改失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
e1.printStackTrace();
}
}else if (e.getSource().equals(Select2)) {
String InsertStuID = InsertID2.getText();
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String id = InsertStuID;
//生成一条sql语句
String sql="SELECT * FROM student WHERE 学号=" + id;
Statement statement=conn.createStatement();//创建一个Statement对象
ResultSet resultSet=statement.executeQuery(sql);//执行sql查询语句
CourseQueryResult.setText("查询结果：" + "\n" +
"课程号" + "\t" + " 课程名" + "\t" + " 学时" + "\t" + "学分" + "\t" + "授课教师" + "\t\n");
// 输出结果集记录
while (resultSet.next()) {
CourseQueryResult.append(
resultSet.getString("课程号") + "\t"
+ resultSet.getString("课程名") + "\t"
+ resultSet.getString("学时") + "\t"
+ resultSet.getString("学分") + "\t"
+ resultSet.getString("授课教师") + "\t");
}
conn.close();
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
JOptionPane.showOptionDialog(jf, "查询信息失败！", "查询失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
}
}
//修改成绩信息
else if (e.getSource().equals(InsertReset3)) {
InsertGraID2.setText("");
InsertGraID2.setFont(new Font("宋体", 1, 23));
InsertGraCno2.setText("");
InsertGraCno2.setFont(new Font("宋体", 1, 23));
InsertGrade2.setText("");
InsertGrade2.setFont(new Font("宋体", 1, 23));
InsertAgainGrade2.setText("");
InsertAgainGrade2.setFont(new Font("宋体", 1, 23));
} else if (e.getSource().equals(InsertRecord3)) {
String InsertGraID = InsertGraID2.getText();
String InsertGraCno = InsertGraCno2.getText();
String InsertGrade = InsertGrade2.getText();
String InsertAgainGrade = InsertAgainGrade2.getText();
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
//生成一条mysql语句
String sql="update grade set 成绩=' " + InsertGrade + "',补考成绩='" + InsertAgainGrade
+ "' where 课程号= '" + InsertGraCno + "' AND 学号='" + InsertGraID + "'";
Statement stmt=conn.createStatement();//创建一个Statement对象
stmt.executeUpdate(sql);//执行sql语句
System.out.println("修改信息成功！");
JOptionPane.showOptionDialog(jf, "修改信息成功！", "修改成功",
JOptionPane.CLOSED_OPTION,
JOptionPane.INFORMATION_MESSAGE, null, null, null);
conn.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
JOptionPane.showOptionDialog(jf, "修改信息失败！", "修改失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
e1.printStackTrace();
}
}else if (e.getSource().equals(Select3)) {
String InsertGraID = InsertGraID2.getText();
try {
String id = InsertGraID;
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String sqlSelect="SELECT * FROM student left outer join grade on(student.学号=grade.学号) "
+ " left outer join course on(grade.课程号=course.课程号) WHERE grade.学号=" + id;
Statement stmtSelect = conn.createStatement();//创建一个Statement对象
ResultSet resultSet=stmtSelect.executeQuery(sqlSelect);//执行sql语句
GradeQueryResult.setText("查询结果：" + "\n" +
"学号" + "\t" + " 姓名" + "\t" + " 课程名" + "\t" + " 课程号" + "\t"
+ " 成绩" + "\t" + "补考成绩"+ "\t\n");
// 输出结果集记录
while (resultSet.next()) {
GradeQueryResult.append(
resultSet.getString("学号") + "\t"
+ resultSet.getString("姓名") + "\t"
+ resultSet.getString("课程名") + "\t"
+ resultSet.getString("课程号") + "\t"
+ resultSet.getString("成绩") + "\t"
+ resultSet.getString("补考成绩") + "\t\n");
}
conn.close();
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
//修改管理员信息
else if (e.getSource().equals(InsertReset4)) {
InsertAdminID2.setText("");
InsertAdminID2.setFont(new Font("宋体", 1, 23));
InsertAdiminName2.setText("");
InsertAdiminName2.setFont(new Font("宋体", 1, 23));
InsertAdminPassword2.setText("");
InsertAdminPassword2.setFont(new Font("宋体", 1, 23));
} else if (e.getSource().equals(InsertRecord4)) {
String InsertID = InsertAdminID2.getText();
String InsertName = InsertAdiminName2.getText();
String InsertPassword = InsertAdminPassword2.getText();
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
//生成一条mysql语句
String sql="update administrator set 姓名=' " + InsertName + "',密码='" + InsertPassword
+ "' where 管理员ID= " + InsertID;
Statement stmt=conn.createStatement();//创建一个Statement对象
stmt.executeUpdate(sql);//执行sql语句
System.out.println("修改信息成功！");
JOptionPane.showOptionDialog(jf, "修改信息成功！", "修改成功",
JOptionPane.CLOSED_OPTION,
JOptionPane.INFORMATION_MESSAGE, null, null, null);
conn.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
JOptionPane.showOptionDialog(jf, "修改信息失败！", "修改添加失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
e1.printStackTrace();
}
}else if (e.getSource().equals(Select4)) {
String InsertID = InsertAdminID2.getText();
try {
String id = InsertID;
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String sql="SELECT * FROM administrator WHERE 管理员ID=" + id;
//生成一条sql的查询语句
Statement statement=conn.createStatement();//创建一个Statement对象
ResultSet resultSet=statement.executeQuery(sql);//执行sql查询语句
AdminiQueryResult.setText("查询结果：" + "\n" +
"管理员ID" + "\t" + " 姓名" + "\t" + " 密码" + "\t\n");
// 输出结果集记录
while (resultSet.next()) {
AdminiQueryResult.append(
resultSet.getString("管理员ID") + "\t\t"
+ resultSet.getString("姓名") + "\t\t"
+ resultSet.getString("密码") + "\t\n");
}
conn.close();
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
@Override
public void mousePressed(MouseEvent e) {
}
@Override
public void mouseReleased(MouseEvent e) {
}
@Override
public void mouseEntered(MouseEvent e) {
}
@Override
public void mouseExited(MouseEvent e) {
}
@Override
public void itemStateChanged(ItemEvent e) {
}
}查询：
代码：
package StudentSQL;
import javax.swing.*;
import java.awt.*;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.awt.event.MouseEvent;
import java.awt.event.MouseListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class SelectGUI extends JFrame implements MouseListener, ItemListener {
protected JFrame jf;
// 定义选项卡
private JTabbedPane Base;
// 定义选项卡上的嵌板
/*
* jp1, 查询学生信息
* jp2, 查询课程信息
* jp3, 查询成绩信息
* jp4, 查询管理员信息
* */
private JPanel jp1, jp2, jp3, jp4,jp5;
// 定义各按钮
private JButton InsertRecord1, InsertReset1,InsertRecord2,InsertReset2,
InsertRecord3, InsertReset3,InsertRecord4, InsertReset4;
// 定义各标签
private JLabel InsertID1,InsertCno1,InsertGraID1,InsertAdminID1;
// 定义各文本框
private JTextField InsertID2,InsertCno2,InsertGraID2,InsertAdminID2;
private JTextArea QueryRecordResult, CourseQueryResult, GradeQueryResult,AdminiQueryResult;
// 定义滚动条
private JScrollPane scroll = null;
private JScrollPane CourseScroll = null;
private JScrollPane GradeScroll = null;
// 定义一个数据库操作的实例
private OperationMySql db = null;
SelectGUI() {
// 设置各按钮信息
setButton();
// 设置各标签信息
setLabel();
// 设置各文本框信息
setTextField();
// 设置各面板信息
setPanel();
// 设置布局信息
setLayout();
// 设置选项卡信息
setBase();
// 设置主窗口信息
setThis();
// 设置数据库信息
setDB();
}
// 设置各按钮信息的方法
private void setButton() {
// jp1 上的按钮
InsertRecord1 = new JButton("查询");
InsertRecord1.setBounds(150, 400, 100, 45);
InsertRecord1.setMargin(new Insets(0, 0, 0, 0)); // 设置按钮的边缘空白为四个方向全为0，也即让按钮中的文本与按钮边缘贴齐
InsertReset1 = new JButton("重置");
InsertReset1.setBounds(300, 400, 100, 45);
InsertReset1.setMargin(new Insets(0, 0, 0, 0));
// jp2 上的按钮
InsertRecord2 = new JButton("查询");
InsertRecord2.setBounds(150, 400, 100, 45);
InsertRecord2.setMargin(new Insets(0, 0, 0, 0)); // 设置按钮的边缘空白为四个方向全为0，也即让按钮中的文本与按钮边缘贴齐
InsertReset2 = new JButton("查询");
InsertReset2.setBounds(300, 400, 100, 45);
InsertReset2.setMargin(new Insets(0, 0, 0, 0));
// jp3 上的按钮
InsertRecord3 = new JButton("查询");
InsertRecord3.setBounds(150, 400, 100, 45);
InsertRecord3.setMargin(new Insets(0, 0, 0, 0)); // 设置按钮的边缘空白为四个方向全为0，也即让按钮中的文本与按钮边缘贴齐
InsertReset3 = new JButton("重置");
InsertReset3.setBounds(300, 400, 100, 45);
InsertReset3.setMargin(new Insets(0, 0, 0, 0));
// jp4 上的按钮
InsertRecord4 = new JButton("查询");
InsertRecord4.setBounds(150, 400, 100, 45);
InsertRecord4.setMargin(new Insets(0, 0, 0, 0)); // 设置按钮的边缘空白为四个方向全为0，也即让按钮中的文本与按钮边缘贴齐
InsertReset4 = new JButton("重置");
InsertReset4.setBounds(300, 400, 100, 45);
InsertReset4.setMargin(new Insets(0, 0, 0, 0));
// 按键监听初始化
initial();
}
// 设置各标签信息的方法
private void setLabel() {
// jp1 上的标签
InsertID1 = new JLabel("学 号：");
InsertID1.setBounds(100, 40, 120, 50);
InsertCno1 = new JLabel("课程号");
InsertCno1.setBounds(100, 40, 120, 50);
InsertGraID1 = new JLabel("学 号");
InsertGraID1.setBounds(100, 40, 120, 50);
InsertAdminID1 = new JLabel("管理员ID");
InsertAdminID1.setBounds(100, 40, 120, 50);
}
// 设置各文本框信息的方法
private void setTextField() {
// jp1 上的文本框
InsertID2 = new JTextField();
InsertID2.setBounds(210, 40, 200, 35);
QueryRecordResult = new JTextArea("查询结果：");
QueryRecordResult.setBounds(30,100,660,200);
QueryRecordResult.setEditable(false);
QueryRecordResult.setLineWrap(true);
scroll = new JScrollPane(QueryRecordResult); // 添加滚动条
scroll.setBounds(30, 30, 560, 260);
scroll.setVerticalScrollBarPolicy(ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED); // 当需要垂直滚动条时显示
scroll.setHorizontalScrollBarPolicy(ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED);// 当需要水平滚动条时显示
InsertCno2 = new JTextField();
InsertCno2.setBounds(210, 40, 200, 35);
CourseQueryResult = new JTextArea("查询结果：");
CourseQueryResult.setBounds(30,100,660,200);
CourseQueryResult.setEditable(false);
CourseQueryResult.setLineWrap(true);
scroll = new JScrollPane(CourseQueryResult); // 添加滚动条
scroll.setBounds(30, 30, 560, 260);
scroll.setVerticalScrollBarPolicy(ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED); // 当需要垂直滚动条时显示
scroll.setHorizontalScrollBarPolicy(ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED);// 当需要水平滚动条时显示
InsertGraID2 = new JTextField();
InsertGraID2.setBounds(210, 40, 200, 35);
GradeQueryResult = new JTextArea("查询结果：");
GradeQueryResult.setBounds(30,100,660,200);
GradeQueryResult.setEditable(false);
GradeQueryResult.setLineWrap(true);
scroll = new JScrollPane(GradeQueryResult); // 添加滚动条
scroll.setBounds(30, 30, 560, 260);
scroll.setVerticalScrollBarPolicy(ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED); // 当需要垂直滚动条时显示
scroll.setHorizontalScrollBarPolicy(ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED);// 当需要水平滚动条时显示
InsertAdminID2 = new JTextField();
InsertAdminID2.setBounds(210, 40, 200, 35);
AdminiQueryResult = new JTextArea("查询结果：");
AdminiQueryResult.setBounds(30,100,660,200);
AdminiQueryResult.setEditable(false);
AdminiQueryResult.setLineWrap(true);
scroll = new JScrollPane(AdminiQueryResult); // 添加滚动条
scroll.setBounds(30, 30, 560, 260);
scroll.setVerticalScrollBarPolicy(ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED); // 当需要垂直滚动条时显示
scroll.setHorizontalScrollBarPolicy(ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED);// 当需要水平滚动条时显示
}
// 设置各面板信息的方法
private void setPanel() {
jp1 = new JPanel();
jp2 = new JPanel();
jp3 = new JPanel();
jp4 = new JPanel();
}
// 设置布局信息的方法
private void setLayout() {
// 添加 jp1 的组件
jp1.setLayout(null);
jp1.add(InsertRecord1);
jp1.add(InsertReset1);
jp1.add(InsertID1);
jp1.add(InsertID2);
jp1.add(QueryRecordResult);
// 添加 jp2 上的组件
jp2.setLayout(null);
jp2.add(InsertRecord2);
jp2.add(InsertReset2);
jp2.add(InsertCno1);
jp2.add(InsertCno2);
jp2.add(CourseQueryResult);
// 添加 jp3 上的组件
jp3.setLayout(null);
jp3.add(InsertRecord3);
jp3.add(InsertReset3);
jp3.add(InsertGraID1);
jp3.add(InsertGraID2);
jp3.add(GradeQueryResult);
// 添加 jp4 上的组件
jp4.setLayout(null);
jp4.add(InsertRecord4);
jp4.add(InsertReset4);
jp4.add(InsertAdminID1);
jp4.add(InsertAdminID2);
jp4.add(AdminiQueryResult);
}
// 设置选项卡信息的方法
private void setBase() {
Base = new JTabbedPane(JTabbedPane.TOP);
Base.addTab("查询学生信息", jp1);
Base.addTab("查询课程信息", jp2);
Base.addTab("查询成绩信息", jp3);
Base.addTab("查询管理员信息", jp4);
}
// 设置主窗口信息的方法
private void setThis() {
this.add(Base);
this.setTitle("学生信息管理系统");
this.setLocation(300, 200);
this.setSize(800, 550);
// this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
this.setResizable(false);
this.setVisible(true);
}
// 设置数据库信息的方法
private void setDB() {
db = new OperationMySql();
// 连接 mysql
db.setDburl("jdbc:sqlserver://127.0.0.1:1433;DatabaseName=text");
// 加载驱动
db.setDbdriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");
db.setUsername("sa");
db.setPassword("123456");
}
// 初始化
void initial() {
// 给各按钮添加监听器
// InsertRecord, InsertReset, DeleteRecord, DeleteReset, QueryRecord, UpdateRecord, CourseQuery, GradeQuery;
InsertRecord1.addMouseListener(this);
InsertReset1.addMouseListener(this);
InsertRecord2.addMouseListener(this);
InsertReset2.addMouseListener(this);
InsertRecord3.addMouseListener(this);
InsertReset3.addMouseListener(this);
InsertRecord4.addMouseListener(this);
InsertReset4.addMouseListener(this);
}
@Override
public void mouseClicked(MouseEvent e) {
// 添加按钮功能
// 点击重置键则清空文本框
// 查询学生信息功能
if (e.getSource().equals(InsertReset1)) {
InsertID2.setText("");
InsertID2.setFont(new Font("宋体", 1, 23));
} else if (e.getSource().equals(InsertRecord1)) {
String InsertStuID = InsertID2.getText();
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String id = InsertStuID;
//生成一条sql语句
String sql="SELECT * FROM student WHERE 学号=" + id;
Statement statement=conn.createStatement();//创建一个Statement对象
ResultSet resultSet=statement.executeQuery(sql);//执行sql查询语句
QueryRecordResult.setText("查询结果：" + "\n" +
"学号" + "\t" + " 姓名" + "\t" + " 性别" + "\t" + " 出生日期" + "\t" + " 班级" + "\t\n");
// 输出结果集记录
while (resultSet.next()) {
QueryRecordResult.append(
resultSet.getString("学号") + "\t"
+ resultSet.getString("姓名") + "\t"
+ resultSet.getString("性别") + "\t"
+ resultSet.getString("出生日期") + "\t"
+ resultSet.getString("班级") + "\t");
}
conn.close();
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
JOptionPane.showOptionDialog(jf, "查询信息失败！", "查询失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
}
}
//查询课程信息
else if (e.getSource().equals(InsertReset2)) {
InsertCno2.setText("");
InsertCno2.setFont(new Font("宋体", 1, 23));
} else if (e.getSource().equals(InsertRecord2)) {
String InsertCno= InsertCno2.getText();
try
{
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String id = InsertCno;
//生成一条sql语句
String sql="SELECT * FROM course WHERE 课程号=" + id;
Statement statement=conn.createStatement();//创建一个Statement对象
ResultSet resultSet=statement.executeQuery(sql);//执行sql查询语句
CourseQueryResult.setText("查询结果：" + "\n" +
"课程号" + "\t" + " 课程名" + "\t" + " 学时" + "\t" + "学分" + "\t" + "授课教师" + "\t\n");
// 输出结果集记录
while (resultSet.next()) {
CourseQueryResult.append(
resultSet.getString("课程号") + "\t"
+ resultSet.getString("课程名") + "\t"
+ resultSet.getString("学时") + "\t"
+ resultSet.getString("学分") + "\t"
+ resultSet.getString("授课教师") + "\t");
}
conn.close();
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
JOptionPane.showOptionDialog(jf, "查询信息失败！", "查询失败",
JOptionPane.CLOSED_OPTION,
JOptionPane.ERROR_MESSAGE, null, null, null);
}
}
//查询成绩信息
else if (e.getSource().equals(InsertReset3)) {
InsertGraID2.setText("");
InsertGraID2.setFont(new Font("宋体", 1, 23));
} else if (e.getSource().equals(InsertRecord3)) {
String InsertGraID = InsertGraID2.getText();
try {
String id = InsertGraID;
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String sqlSelect="SELECT * FROM student left outer join grade on(student.学号=grade.学号) "
+ " left outer join course on(grade.课程号=course.课程号) WHERE grade.学号=" + id;
Statement stmtSelect = conn.createStatement();//创建一个Statement对象
ResultSet resultSet=stmtSelect.executeQuery(sqlSelect);//执行sql语句
GradeQueryResult.setText("查询结果：" + "\n" +
"学号" + "\t" + " 姓名" + "\t" + " 课程名" + "\t" + " 课程号" + "\t"
+ " 成绩" + "\t" + "补考成绩"+ "\t\n");
// 输出结果集记录
while (resultSet.next()) {
GradeQueryResult.append(
resultSet.getString("学号") + "\t"
+ resultSet.getString("姓名") + "\t"
+ resultSet.getString("课程名") + "\t"
+ resultSet.getString("课程号") + "\t"
+ resultSet.getString("成绩") + "\t"
+ resultSet.getString("补考成绩") );
}
conn.close();
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
//查询管理员信息
else if (e.getSource().equals(InsertReset4)) {
InsertAdminID2.setText("");
InsertAdminID2.setFont(new Font("宋体", 1, 23));
} else if (e.getSource().equals(InsertRecord4)) {
String InsertID = InsertAdminID2.getText();
try {
String id = InsertID;
//建立数据库连接，获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(db.getDburl(), db.getUsername(), db.getPassword());
String sql="SELECT * FROM administrator WHERE 管理员ID=" + id;
//生成一条sql的查询语句
Statement statement=conn.createStatement();//创建一个Statement对象
ResultSet resultSet=statement.executeQuery(sql);//执行sql查询语句
AdminiQueryResult.setText("查询结果：" + "\n" +
"管理员ID" + "\t" + " 姓名" + "\t" + " 密码" + "\t\n");
// 输出结果集记录
while (resultSet.next()) {
AdminiQueryResult.append(
resultSet.getString("管理员ID") + "\t\t"
+ resultSet.getString("姓名") + "\t\t"
+ resultSet.getString("密码") + "\t\n");
}
conn.close();
System.out.println("查询成功！");
}catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
@Override
public void mousePressed(MouseEvent e) {
}
@Override
public void mouseReleased(MouseEvent e) {
}
@Override
public void mouseEntered(MouseEvent e) {
}
@Override
public void mouseExited(MouseEvent e) {
}
@Override
public void itemStateChanged(ItemEvent e) {
}
}纪念一下自己这次的课设作业，我太菜了
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
