第14个java作业，要求用数据库做数据库管理系统
最早的前身是：学生信息排序管理（JAVA文件操作） 用图形界面进行了重构。
这是数据库版，文件流版参见 【GUI程序设计】学生信息管理系统(文件流版)
界面没有改动，文件流部分全部用数据库重构，文件类变为数据库加载类，五小时完成。
注意事项：
数据库连接信息在connection包的DAO类修改
运行截图：
源代码：
进行了重大更新的源文件：
一、数据库连接包(connection包)
1.DAO类（数据库连接核心类）
package connection;
import java.sql.Connection;
import java.sql.DriverManager;
public class DAO {
private static String driverName = "com.mysql.cj.jdbc.Driver";
private static String userName = "root";
private static String password = "123456";
private static DAO dao=new DAO();
public DAO(){
try{
Class.forName(driverName);//加载驱动
}catch(ClassNotFoundException e){
e.printStackTrace();
}
}
public static Connection getConn() {
String url = "jdbc:mysql://localhost:3306/yuheng"
+ "?useUnicode=true&useSSL=false&characterEncoding=utf-8"
+ "&serverTimezone=Asia/Shanghai";
Connection conn = null;
try {
conn = null;
conn = DriverManager.getConnection(url,userName,password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
}
2.SQL_OPEN类（数据库加载类-由文件版的User重构而成，只有加载功能）
package connection;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import StudentInfomation.Student;
import StudentInfomation.StudentGroup;
public class SQL_OPEN {
public StudentGroup studentGroup=new StudentGroup();
public void file_load() throws SQLException {//记录加载
Connection conn = null;
Statement ps = null;
ResultSet rs = null;
conn = DAO.getConn();
new DAO();
conn = DAO.getConn();
ps = conn.createStatement();
String sql = "SELECT * FROM students";
rs = ps.executeQuery(sql);
studentGroup.dataBase.clear();
while(rs.next())
studentGroup.add(new Student(rs.getString(1),rs.getString(2),rs.getDouble(3)));
}
}
二、用户信息包(StudentInfomation包)
1.student类（基础类，无变动）
package StudentInfomation;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
public class Student{
String stuNo,name;
double grade;
public Student() {
stuNo=name="";
grade=0.0;
}
public Student(String stuNo,String name,String grade) {
this.stuNo=stuNo;
this.name=name;
this.grade=Double.valueOf(grade);
}
public Student(String stuNo,String name,double grade) {
this.stuNo=stuNo;
this.name=name;
this.grade=grade;
}
public String stuInfo(){
if(this.name.length()=="占位符".length())
return String.format("%-12s %-30s %s",this.stuNo,this.name,Double.toString(this.grade));
else return String.format("%-12s %-33s %s",this.stuNo,this.name,Double.toString(this.grade));
}
}
2.StudentGroup类（学生信息核心类，重大重构）
package StudentInfomation;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import connection.DAO;
public class StudentGroup{
public List<Student> dataBase =new ArrayList<Student>();
public void add(Student stu) throws SQLException {//-------------数据库加载操作
dataBase.add(stu);
}
public void insert(Student stu) throws SQLException {//-------------数据库插入操作
dataBase.add(stu);
Connection conn=null;
PreparedStatement ps=null;
conn=DAO.getConn();
String sql_insert="insert into students values(?,?,?)";//sql语句准备
PreparedStatement pst1=conn.prepareStatement(sql_insert);//创建封装语句的对象
pst1.setString(1,stu.stuNo);
pst1.setString(2,stu.name);
pst1.setString(3,String.valueOf(stu.grade));
pst1.executeUpdate();
pst1.close();
conn.close();
}
public String modify(String no,String name,String grade) {
Connection conn=null;
PreparedStatement pst1=null,pst2=null;
ResultSet rs=null;
conn=DAO.getConn();
String sql1="update students set sname=?,score=? where sno=?";
String sql2="select * from students where sno=?";
try {
pst1=conn.prepareStatement(sql1);//创建封装语句的对象
pst2=conn.prepareStatement(sql2);
pst1.setString(1,name);//给第2个占位符赋值
pst1.setString(2,grade);//给第1个占位符赋值
pst2.setString(1, no);
pst1.setString(3,no);//给第2个占位符赋值
pst1.executeUpdate();
rs=pst2.executeQuery();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
String re="";
try {
if(rs.next()) {
re= new Student(rs.getString(1),rs.getString(2),String.valueOf(rs.getDouble(3))).stuInfo();
}
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
if(re=="") return "找不到这个学号的学生";
return "修改成功\r\n新记录是：\r\n"+re;
}
//查询------------------------------------------------
public String lookup(String no,String name) {//精准查询
int index_ans[]=new int [dataBase.size()];
int index_num=0;
for(int i=0;i<dataBase.size();i++)
if(dataBase.get(i).stuNo.equals(no)&&dataBase.get(i).name.equals(name))
index_ans[index_num++]=i;
if(index_num==0) return "找不到";
else {
String re="";
for(int i=0;i<index_num;i++)
re+=(dataBase.get(index_ans[i]).stuInfo()+"\r\n");
return re;
}
}
public String lookup(String infomation,int num) {//模糊查询 num=1学号查询 2:名字查询 3:成绩查询
int index_ans[]=new int [dataBase.size()];
int index_num=0;
if(num==1) {//学号查询
for(int i=0;i<dataBase.size();i++)
if(dataBase.get(i).stuNo.equals(infomation))
index_ans[index_num++]=i;
}else if(num==2) {
for(int i=0;i<dataBase.size();i++)
if(dataBase.get(i).name.equals(infomation))
index_ans[index_num++]=i;
}else if(num==3) {
for(int i=0;i<dataBase.size();i++)
if(dataBase.get(i).grade==Double.valueOf(infomation))
index_ans[index_num++]=i;
}
if(index_num==0) return "找不到";
else {
String re="";
for(int i=0;i<index_num;i++)
re+=(dataBase.get(index_ans[i]).stuInfo()+"\r\n");
return re;
}
}
//删除-------------------------------------------------------
public String delete(String no,String name) {//精准删除
Connection conn=null;
conn=DAO.getConn();
ResultSet rs=null;
String sql2_del="delete from students where sno=? AND sname=?";//准备sql语句，？是占位符，内容待定
PreparedStatement pst2 = null;
try {
pst2 = conn.prepareStatement(sql2_del);
} catch (SQLException e1) {
// TODO 自动生成的 catch 块
e1.printStackTrace();
}//创建封装语句的对象
try {
pst2.setString(1,no);
pst2.setString(2,name);
pst2.executeUpdate();//执行
pst2.close();
conn.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
return "被删除的数据\r\n"+lookup(no, name);
}
public String delete(String infomation,int num){//模糊删除 num=1学号删除 2:名字删除 3:成绩删除
Connection conn=null;
conn=DAO.getConn();
ResultSet rs=null;
PreparedStatement pst2 = null;
String sql2_del;
if(num==1) {//学号删除
sql2_del="delete from students where sno=?";//准备sql语句，？是占位符，内容待定
try {
pst2=conn.prepareStatement(sql2_del);
pst2.setString(1, infomation);
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}else if(num==2) {
sql2_del="delete from students where sname=?";
try {
pst2=conn.prepareStatement(sql2_del);
pst2.setString(1, infomation);
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}else if(num==3) {
sql2_del="delete from students where score=?";
try {
pst2=conn.prepareStatement(sql2_del);
pst2.setString(1, infomation);
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
try {
pst2.execute();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
return "删除掉的记录\r\n"+lookup(infomation, num);
}
public void sortSub() {
Collections.sort(dataBase, new Comparator<Student>() {
public int compare(Student stu1, Student stu2) {
if(stu1.grade<stu2.grade) return 1;
else if(stu1.grade>stu2.grade) return -1;
else return 0;
}
});
}
public void sortAdd() {
Collections.sort(dataBase, new Comparator<Student>() {
public int compare(Student stu1, Student stu2) {
if(stu1.grade>stu2.grade) return 1;
else if(stu1.grade<stu2.grade) return -1;
else return 0;
}
});
}
}