软件学院“云服务”志愿服务队在宣传推广志愿服务精神和志愿文件、践行雷锋精神方面一直发挥着重要作用。为了方便管理,现为“云服务”志愿服务队开发一个简单的志愿者管理工具。要求:
1. 将志愿者信息保存数据库中,数据表结构如下:
字段名 | Java数据类型 | 宽度 | SQL数据类型 |
id | int | 10 | int |
Name | String | 20 | Char(20) |
Sex | String | 2 | Char(2) |
Age | Int | 3 | Integer |
2. 设计图形用户界面,通过事件处理实现志愿者数据管理功能。
3. 用恰当的方法处理可能出现的异常。
4. 将数据表及其数据操作封装成DAO类,将数据操作功能封装成类的方法,通过该类,借助图形用户界面实现下面功能:
(1)向表中增加记录并显示增加后的所有记录(具体数据自定);
(2)从表中删除id=1的记录,并显示删除后的所有记录;
(3)修改表中记录:查询条件id=2,修改其name字段的值(自定),修改完毕显示所有记录;
(4)查询表中id=3的记录并显示。
要在Java中实现对数据库的操作,首先需要导入mysql-connector-java.jar驱动
驱动下载:官方地址:MySQL :: MySQL Downloads
然后向下滑动
选择Platform Independent (对于windows 系统)
下边两个下载哪个都行,看具体能解压什么类型的文件
点击下载之后,点击no,可以直接下载
导入驱动:
创建一个新的文件夹,并且把刚刚下载的驱动复制到该文件夹下
然后
就导入成功了
然后就可以开始写连接到数据库的代码和具体的实现实验要求的代码了
首先我们需要一个Conn类用于连接数据库
注意:密码为创建mysql时所设置的密码,数据库名也是
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Conn { // 创建类Conn
Connection con=null; // 声明Connection对象
public static String user;
public static String password;
public Connection getConnection() { // 建立返回值为Connection的方法
try { // 加载数据库驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("数据库驱动加载成功");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
user = "root";//数据库登录名
password = "xxx";//密码
try { // 通过访问数据库的URL获取数据库连接对象
con = DriverManager.getConnection
("jdbc:mysql://localhost:3306/test",user,password);
System.out.println("数据库连接成功");
} catch (SQLException e) {
e.printStackTrace();
}
return con; // 按方法要求返回一个Connection对象
}
}
创建类GetStudentInfo 用来创建图形化界面:
import javax.swing.*;
import java.awt.*;
public class GetStudentInfo extends JPanel {
JTextField id; //定义四个文本域存放学生信息
JTextField name;
JTextField sex;
JTextField age;
public GetStudentInfo(){
JLabel stuid=new JLabel("id");
id=new JTextField(4);
JLabel stuname=new JLabel("姓名");
name=new JTextField(4);
JLabel stusex=new JLabel("性别");
sex=new JTextField(4);
JLabel stuage=new JLabel("年龄");
age=new JTextField(4);
this.setLayout(new FlowLayout()); //本次运用的布局为FlowLayout布局
this.add(stuid);
this.add(id); //向窗口里添加信息
this.add(stuname);
this.add(name);
this.add(stusex);
this.add(sex);
this.add(stuage);
this.add(age);
}
public String getId(){ //getter和setter的运用
String id1=id.getText();
return id1;
}
public String getName(){
String name1=name.getText();
return name1;
}
public String getSex(){
String sex1=sex.getText();
return sex1;
}
public String getAge(){
String age1= age.getText();
return age1;
}
}
然后写第三个类StuManage用来实现具体要求:
import javax.swing.*;
import javax.swing.table.DefaultTableCellRenderer;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;
public class StuManage extends JFrame implements ActionListener {
int row; //用于记录更改行数
JTabbedPane tab=new JTabbedPane(); //new一个可切换的选项卡
JPanel mainpanel=new JPanel(); //new一个新的窗口
JScrollPane viewlistscorll; //滚动条1
JScrollPane viewscroll; //滚动条2
JPanel updatepanel=new JPanel(); //new更改数据库的窗口
GetStudentInfo stuinfo1=new GetStudentInfo(); //调用获取学生类1
GetStudentInfo stuinfo2=new GetStudentInfo(); //调用获取学生类2
JPanel querypanel=new JPanel(); //new查看数据库窗口
JButton dataButton=new JButton("删除"); //定义删除按钮
JTextField queryTestFile=new JTextField(10); //查看文本域,宽度为10
Object data[][],data1[][],data2[][]; //为遍历数据而准备的Object二维数组
Object colname[]={"id","姓名","性别","年龄"}; //定义索引
JTable stutable,querytable,querylist; //定义表格
JButton add=new JButton("添加");
JButton modifybutton=new JButton("修改");
JButton updatebutton=new JButton("更新");
JButton querybutton=new JButton("查询");
JButton update=new JButton("更新数据");
String sno;
public StuManage(){ //重写构造方法
super("学生管理系统");
setDefaultCloseOperation(3);
viewDataList();
addData();
deleteData();
modifyData();
queryData();
add(tab);
}
//添加数据
public void addData(){
JButton adddata_clear=new JButton("清除");
mainpanel.add(stuinfo1); //主界面添加学生信息获取1
add.addActionListener(this);//添加事件监听器
stuinfo1.add(add); //添加按钮
stuinfo1.add(adddata_clear); //添加清理按钮
adddata_clear.addActionListener(new ActionListener() { //new监视器,并重新抽象类
@Override
public void actionPerformed(ActionEvent d) {
stuinfo1.id.setText("");
stuinfo1.sex.setText("");
stuinfo1.name.setText("");
stuinfo1.age.setText("");
}
});
tab.add("添加数据",stuinfo1); //添加到选项卡上
}
//修改数据原理同上
public void modifyData(){
JButton update_clear=new JButton("清除");
mainpanel.add(stuinfo2);
stuinfo2.add(modifybutton);
stuinfo2.add(querybutton);
querybutton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
stuinfo2.id.setEditable(false);
stuinfo2.name.setEditable(true);
stuinfo2.sex.setEditable(true);
stuinfo2.age.setEditable(true);
sno=stuinfo2.id.getText();
if(stuinfo2.id.getText().isEmpty())
JOptionPane.showMessageDialog(null, "学号不能为空");
else
try{
ResultSet rs;
Connection conn=new Conn().getConnection();
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
String sql="select * from student where id="+sno;
rs=stmt.executeQuery(sql);
while(rs.next()){ //遍历
stuinfo2.name.setText(rs.getString("name"));
stuinfo2.sex.setText(rs.getString("sex"));
stuinfo2.age.setText(rs.getString("age"));
}
querytable.setVisible(false);
querytable.setVisible(true);
rs.close();
conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
});
stuinfo2.add(modifybutton); //修改监视器
modifybutton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
if(stuinfo2.id.getText().isEmpty())
JOptionPane.showMessageDialog(null,"学号不能为空");
else
try{
stuinfo2.name.setEditable(false); //设置为不可更改
stuinfo2.sex.setEditable(false);
stuinfo2.age.setEditable(false);
String sno= stuinfo2.id.getText();
String name=stuinfo2.name.getText();
String sex=stuinfo2.sex.getText();
String sage=stuinfo2.age.getText();
Connection conn=new Conn().getConnection();
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
String sql="update student set name='"+name+"' where id='"+sno+"'";
stmt.executeUpdate(sql);
JOptionPane.showMessageDialog(null,"修改成功");
stmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
}finally {
StuManage add1=new StuManage(); //最好将修改的结果添加到StuManage
add1.setLocationRelativeTo(null);
add1.setVisible(true);
add1.setSize(500,170);
setVisible(false);
stuinfo2.id.setEditable(true);
}
}
});
stuinfo2.add(update_clear); //给更新按钮添加监视器
update_clear.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
stuinfo2.id.setEditable(true);
stuinfo2.name.setEditable(false);
stuinfo2.sex.setEditable(false);
stuinfo2.age.setEditable(false);
stuinfo2.id.setText("");
stuinfo2.name.setText("");
stuinfo2.sex.setText("");
stuinfo2.age.setText("");
}
});
stuinfo2.name.setEditable(false);
stuinfo2.sex.setEditable(false);
stuinfo2.age.setEditable(false);
tab.add("修改数据",stuinfo2); //为选项卡命名修改数据
}
//删除数据原理同上上
public void deleteData(){
JLabel snolabel=new JLabel("学号");
JTextField snotext=new JTextField(" ");
JButton delete_query=new JButton("查询");
JButton delete_clear=new JButton("清除");
mainpanel.add(snolabel); //各种添加按钮
mainpanel.add(snotext);//注意
mainpanel.add(delete_query);//各种添加按钮
mainpanel.add(dataButton);//各种添加按钮
mainpanel.add(delete_clear);//各种添加按钮
data2=new Object[1][6]; //用于遍历的数组
querylist=new JTable(data2,colname);
JScrollPane jsp=new JScrollPane(querylist);
mainpanel.add(jsp);
querylist.setVisible(false);
querylist.setFillsViewportHeight(true);
delete_query.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
sno= snotext.getText();
if(snotext.getText().isEmpty())
JOptionPane.showMessageDialog(null,"学号不能为空");
else
try{
ResultSet rs1;
Connection conn=new Conn().getConnection();
Statement stmt1=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
String sql1="select * from student where id="+sno;
rs1=stmt1.executeQuery(sql1);
int i=0;
if(rs1.next()){ //遍历查找
data2[i][0]=rs1.getString(1);
data2[i][1]=rs1.getString(2);
data2[i][2]=rs1.getString(3);
data2[i][3]=rs1.getString(4);
querylist.setVisible(true);
}else JOptionPane.showMessageDialog(null,"无记录!"); //提示
querylist.setVisible(false);
querylist.setVisible(true);
rs1.close();
conn.close();
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
}
});
dataButton.addActionListener(new ActionListener() { //删除按钮添加事件监视器
@Override
public void actionPerformed(ActionEvent e) {
sno=snotext.getText();
if(snotext.getText().isEmpty())
JOptionPane.showMessageDialog(null,"学号不能为空");
else
try{
Connection conn=new Conn().getConnection();
Statement stmt1=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
String sql1="delete from student where id='"+sno+"'";
stmt1.executeUpdate(sql1);
querytable.setVisible(false);
querytable.setVisible(true);
conn.close();
JOptionPane.showMessageDialog(null,"删除成功");
}catch (Exception ex){
ex.printStackTrace();
}
}
});
delete_clear.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
snotext.setText("");
}
});
tab.add("删除数据",mainpanel);
}
//查询数据原理同上上上
public void queryData(){
JLabel cxjl=new JLabel("学号");
JButton cxbutton=new JButton("查询");
JButton cxqcbutton=new JButton("清除");
querypanel.add(cxjl);//各种添加按钮
querypanel.add(queryTestFile);//各种添加按钮
querypanel.add(cxbutton);//各种添加按钮
querypanel.add(cxqcbutton);//各种添加按钮
cxqcbutton.addActionListener(new ActionListener() { //添加按钮事件监视器
@Override
public void actionPerformed(ActionEvent e) {
queryTestFile.setText("");
}
});
data1=new Object[1][6];
querytable=new JTable(data1,colname);
viewlistscorll=new JScrollPane(querytable);
querypanel.add(viewlistscorll);
querytable.setVisible(false);
querytable.setFillsViewportHeight(true);
tab.add("查询数据",querypanel);
cxbutton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
sno= queryTestFile.getText();
if(queryTestFile.getText().isEmpty())
JOptionPane.showMessageDialog(null,"学号不能为空");
else
try{
ResultSet rs1;
Connection conn=new Conn().getConnection();
Statement stmt1=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
String sql1="select *from student where id="+sno;
rs1=stmt1.executeQuery(sql1);
int i=0;
if(rs1.next()){ //遍历
data1[i][0]=rs1.getString(1);
data1[i][1]=rs1.getString(2);
data1[i][2]=rs1.getString(3);
data1[i][3]=rs1.getString(4);
querytable.setVisible(true);
}
else JOptionPane.showMessageDialog(null,"无记录!");
querytable.setVisible(false);
querytable.setVisible(true);
rs1.close();
conn.close();
}catch (Exception ex){
System.out.println(ex.getMessage());
}
}
});
}
//用表格显示数据
public void viewData(){
try{
JPanel content=new JPanel();
JButton clear=new JButton("清除");
ResultSet rs;
Connection conn=new Conn().getConnection();
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
String sql="select * from student";
rs=stmt.executeQuery(sql);
rs.last();
row=rs.getRow();
data=new Object[row][4];
stutable=new JTable(data,colname);
DefaultTableCellRenderer r=new DefaultTableCellRenderer();
r.setHorizontalAlignment(JLabel.CENTER);
stutable.setDefaultRenderer(Object.class,r);
content.setPreferredSize(new Dimension(50,400));
content.add(updatebutton); //你忘添加了
content.add(clear);
updatebutton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
StuManage add1=new StuManage();
add1.setLocationRelativeTo(null);
add1.setVisible(true);
add1.setSize(476,170);
setVisible(false);
}
});
content.add(new JScrollPane(stutable));
viewlistscorll=new JScrollPane(content);
rs.beforeFirst();
clear.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
int i;
for (i=0;i<stutable.getRowCount();i++){ //遍历
data[i][0]="";
data[i][1]="";
data[i][2]="";
data[i][3]="";
}
}
});
int i=0;
while(rs.next()){
data[i][0]=rs.getInt(1);
data[i][1]=rs.getString(2);
data[i][2]=rs.getString(3);
data[i][3]=rs.getInt(4);
i++;
}
rs.close();
conn.close();
}catch (Exception e){
e.printStackTrace();
}
tab.add("浏览数据",viewlistscorll);
}
public void viewDataList(){
viewData();
}
public boolean queryExist(String id){
boolean b= false;
try{
ResultSet rs;
Connection conn=new Conn().getConnection();
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
String sql="select * from student where id="+id;
rs=stmt.executeQuery(sql);
if(rs.next()){
b=true;
}
rs.close();
conn.close();
}catch (Exception ex){
System.out.println(ex.getMessage());
}
return b;
}
public void actionPerformed(ActionEvent e){
if(e.getSource()==add){
Statement stmt=null;
try{
String id=stuinfo1.getId();
String name1=stuinfo1.getName();
String sex=stuinfo1.getSex();
String age=stuinfo1.getAge();
if(queryExist(id)){
JOptionPane.showMessageDialog(null,"学号已存在");
}else{
Connection conn=new Conn().getConnection();
String sql="insert into student values("+"'"+id+"','"+name1+"','"+sex+"','"+age+"')"; //插入数据
stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
stmt.executeUpdate(sql);
JOptionPane.showMessageDialog(null,"添加成功");
}
}catch (Exception f){
f.printStackTrace();
JOptionPane.showMessageDialog(null,"添加失败");
}finally {
try{
stmt.close();
}catch (SQLException e1){
e1.printStackTrace();
}
}
}
}
}
最后一个类Main用来运行:
public class Main {
public static void main(String[] args) {
StuManage stumanage=new StuManage();
stumanage.setLocationRelativeTo(null);
stumanage.setVisible(true);
stumanage.setSize(500,170);
}
}
在数据库填入数据后运行就可显示数据