第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; } }); } }
很好呀