简易控制台版学生管理系统2.0

学生管理系统2.0(JDBC控制台版）

基于学生管理系统1.0版本的ArrayList版本的重磅升级！增添了JDBC对MySQL的增删改查！

（这应该可以当作一次Java+MySQL期末作业了）

先来个总结

JDBC和MySQL的本省比较简单，但是对于表的设计，高效的查询，以及程序的设计模式决定了程序运行的效率

做的这个小玩意感觉写的有点屎山了，写完后反思了一下不足的地方

• 写的比较随意，几乎什么章法
• 命名过于接近，如果不是自己写的估计别人都能看晕
• 程序设计的不够优雅，还是需要学习一下优秀的代码
• 没有分层次，耦合过高

踩的一些小坑

Connection conn = ....;
String sql = "select count(*) from stu_grade where ? >= ?";
PreparedStatement ps = conn.PreparedStatement(sql);
ps.setObject(1, "Object");
ps.setObject(2, 110);

ResultSet rs = ps.executeQuery();
rs.getObject(1);

如上代码中有两处坑

踩坑一：

在SQL语句中where ? >= ?在条件中表字段名不能作为占位符被setObject()，如果使用占位符会发现返回的值与预期不符。

如果不需要从输入中获取条件可以考虑使用

踩坑二：

在rs.getObject()中，ResultSet和Iterator类很类似，Iterator类中的hasNext()不仅作为一个返回布尔类型在if或者while进行判断的作用，并且会将指针从空指向第一个数值。

也就是如果没有hasNext()无法迭代到下一个对象，同理在ResultSet获取结果时也需要用next()方法来移动指针获取数据库返回的每一行的数据，所以在如果只需要一行数据的时候在getObject()前调用一次next()。

踩坑三：

在读取Properties文件的时候，@Test单元测试类的路径为模块中的根目录，可以直接写在模块的根目录下FileInputStream(DBConnectInfo.properties)

但是在调用main函数的时候，默认读取的路径为工程下的路径，在读取的时候应该使用

FileInputStream(System.getProperty("user.dir")+"/StudentManagementSystem/DBConnectInfo.properties")

之前一直使用单元测试运行程序，一下子忘了这回事，直到发现在IDEA中不能使用Scanner输入就直接用main方法调了，然后一直抛数据库连接的错，但是自己跑回去测试的时候用的又是单元测试，导致当时一直没想通到底哪里出了问题……

以及解决IDEA单元测试不能输入的问题：在IDEA导航栏help中的编辑自定义VM选项（找不到就看带VM的）

添加以下代码

-Deditable.java.test.console=true

目录结构

|src
	|___ DBUtils.java
	|___ GradeInfo.java
	|___ Manager.java
	|___ Student.java
	|___ StuGradeMethod.java
	|___ StuInfoMethod.java
|lib
	|___ mysql-connector-java-8.0.30.jar
	|___ testing-7.1.0.jar
|DBConnectInfo.properties

我知道应该分开建包但是个人比较懒，反正是写着练习一下CRUD的，就不要在意细节了）

数据库表结构

create
database student_db;

use
student_db;

create table student
(
    stu_id   int primary key auto_increment,
    stu_name varchar(15),
    stu_age  int unsigned check ( stu_age < 150 )
);

create table stu_grade
(
    stu_id      int,
    chinese     decimal(5, 2) unsigned check ( chinese <= 150 ),
    math        decimal(5, 2) unsigned check ( math <= 150 ),
    english     decimal(5, 2) unsigned check ( english <= 150 ),
    science     decimal(5, 2) check ( science <= 100 ),
    total_grade decimal(5, 2) generated always as (chinese + math + english + science) virtual
    constraint student_id foreign key (stu_id) references student (stu_id)
);

total_grade此列使用了MySQL 8.0的新特性——计算列，根据自定义的公式生成的虚拟列，在表数据更改时此列也会同时进行更改

stu_id本来不应该用int类型，做着玩的随意一点

工具类

DBUtils.java

import org.testng.annotations.Test;

import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/***
 * @title DBUtils
 * @author WuCheng
 * @create 2022/9/21 16:26
 **/
public class DBUtils {
    /***
     * @description 获取数据库连接
     * @author WuCheng
     */
    public static Connection getConnection() throws Exception {
        FileInputStream fis = new FileInputStream(System.getProperty("user.dir")+"/StudentManagementSystem/DBConnectInfo.properties");
        Properties properties = new Properties();

        properties.load(fis);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        Class.forName(driver);

        Connection conn = DriverManager.getConnection(url, user, password);

        return conn;
    }

    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    
    /*** 
     * @description 关闭连接（增删改操作） 
     * @author WuCheng 
     */
    public static void closeConnection(Connection conn, PreparedStatement ps) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    /*** 
     * @description  关闭连接（查询操作）
     * @author WuCheng 
     */
    public static void closeConnection(Connection conn, PreparedStatement ps, ResultSet rs) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    /*** 
     * @description 通用插入和修改操作 
     * @author WuCheng 
     */
    public static int insertOrUpdate(String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }

            return ps.executeUpdate();
        } catch (Exception e) {
            return 0;
        } finally {
            closeConnection(conn, ps);
        }
    }

    /*** 
     * @description 通用查询操作 
     * @author WuCheng 
     */
    public static <T> List<T> selectInfo(Class<T> calzz, String sql, Object... args)  {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }

            rs = ps.executeQuery();
            List<T> list = new ArrayList<>();

            while (rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();
                T t = calzz.newInstance();
                int count = rsmd.getColumnCount();

                for (int i = 0; i < count; i++) {
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    Object value = rs.getObject(i + 1);

                    Field field = calzz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,value);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn, ps, rs);
        }
        return null;
    }

    /*** 
     * @description 针对于学生是否达到标准的查询 
     * @author WuCheng 
     */
    public static int selectInfo(String sql,String subject,int grade){
        sql = sql+subject;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs= null;
        try {
            conn = DBUtils.getConnection();

            ps = conn.prepareStatement(sql);
            ps.setInt(1,grade);

            rs = ps.executeQuery();
            rs.next();

            return rs.getInt(1);
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            closeConnection(conn,ps,rs);
        }
        return 0;
    }

    /*** 
     * @description 获取平均成绩 
     * @author WuCheng 
     */
    public static void getAvgGrade(String sql,float [] gradeAvg){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs= null;
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            rs.next();
            for (int i = 0; i < gradeAvg.length; i++) {
                gradeAvg[i] = rs.getFloat(i+1);
            }
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            closeConnection(conn,ps,rs);
        }
    }

    /*** 
     * @description 查询学生是否存在 
     * @author WuCheng 
     */
    public static boolean haveRecord(String id){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        boolean isTrue = false;

        try {
            String sql = "select * from student where stu_id = ?";
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,id);

            rs = ps.executeQuery();

            isTrue = rs.next();
        } catch (Exception e) {
            return false;
        } finally {
            closeConnection(conn, ps, rs);
        }
        return isTrue;
    }

}

方法类

StuInfoMethod.java

import org.testng.annotations.Test;

import java.util.Iterator;
import java.util.List;
import java.util.Scanner;

/*** 
 * @description 学生信息方法
 * @date 2022/9/21 20:32
 * @author WuCheng
 */
public class StuInfoMethod {

    Scanner scan = new Scanner(System.in);
    StuGradeMethod grade = new StuGradeMethod();

    public void userUI() {
        System.out.println("--------欢迎使用学生管理系统--------");
        System.out.println("|         1.添加学生             |");
        System.out.println("|         2.修改学生             |");
        System.out.println("|         3.删除学生             |");
        System.out.println("|         4.展示所有学生          |");
        System.out.println("|         5.查询学生成绩信息       |");
        System.out.println("|         6.退出系统             |");
        System.out.println("---------------------------------");
        System.out.print("请输入你的选择：");
    }

    public boolean choose(boolean OK) {
        String choose = scan.nextLine();
        switch (choose) {
            case "1": addStudent();
                break;
            case "2": setStudent();
                break;
            case "3": delStudent();
                break;
            case "4": showAllStuInfo();
                break;
            case "5": {
                do {
                    grade.GradeManageUI();
                }while (grade.choose(true));
            }
                break;
            case "6": 
                return false;
            default: System.out.println("没有该选项！");
        }
        return OK;
    }

	/*** 
     * @description 添加学生
     * @author WuCheng 
     */
    @Test
    public void addStudent() {
        String sql = "insert into student(stu_name,stu_age) values(?,?)";
        do {
            System.out.print("请输入姓名：");
            String name = scan.nextLine();
            System.out.print("请输入年龄：");
            String age = scan.nextLine();

            if (name.length() == 0 || age.length() == 0f) {
                System.out.println("有数据未输入！");
                return;
            } else {
                DBUtils.insertOrUpdate(sql, name, age);
                System.out.println("是否继续录入（y/n）");
            }
        } while (scan.nextLine().equals("y"));
    }

    /*** 
     * @description 修改学生
     * @author WuCheng 
     */
    @Test
    public void setStudent() {
        System.out.print("请输入要修改的学生学号：");
        String id = scan.nextLine();

        if (DBUtils.haveRecord(id)) {
            System.out.print("请输入新姓名：");
            String name = scan.nextLine();
            System.out.print("请输入新年龄：");
            String age = scan.nextLine();

            String sql = "update student set stu_name = ?,stu_age = ? where stu_id = ?";
            if(DBUtils.insertOrUpdate(sql, name, age, id) >= 1){
                System.out.println("修改成功");
            }else{
                System.out.println("修改失败");
            }
        } else {
            System.out.println("查无此学生！");
        }

        return;
    }

    /*** 
     * @description 删除学生
     * @author WuCheng 
     */
    @Test
    public void delStudent() {
        System.out.println("请输入要删除的学生学号：");
        String id = scan.nextLine();

        List<Student> list = DBUtils.selectInfo(Student.class, "select stu_name name from student where stu_id = ?", id);

        if (list.size() >= 1) {
            Student student = list.get(0);
            System.out.print("查询到学生：" + student.getName() + "\n是否删除（y/n）");
            if (scan.nextLine().equals("y")) {
                String sql1 = "delete from student where stu_id = ?";
                String sql2 = "delete from stu_grade where stu_id = ?";
                DBUtils.insertOrUpdate(sql2, id);
                if (DBUtils.insertOrUpdate(sql1, id) >= 1) {
                    System.out.println("删除成功");
                    return;
                } else {
                    System.out.println("删除失败，");
                }

            } else {
                System.out.println("返回主页面");
                return;
            }
        } else {
            System.out.println("未找到该学生学号，请检查是否正确！");
            return;
        }
    }

    /*** 
     * @description 显示所有学生信息
     * @author WuCheng 
     */
    @Test
    public static void showAllStuInfo() {
        String sql = "select stu_id id, stu_name name,stu_age age from student";
        List<Student> list = DBUtils.selectInfo(Student.class, sql);

        Iterator<Student> iterator = list.iterator();
        System.out.println("|   学号     |     姓名    |    年龄    |");
        while (iterator.hasNext()){
            iterator.next().outputStuInfo();
        }

    }

}

StuGradeMethod.java

import org.testng.annotations.Test;

import java.util.*;

/***
 * @title StuGradeInfo
 * @description 学生成绩方法
 * @author WuCheng
 * @version 1.0.0
 * @create 2022/9/26 13:16
 **/
public class StuGradeMethod {

    Scanner scan = new Scanner(System.in);

    public void GradeManageUI() {
        System.out.println("-----------学生成绩管理-----------");
        System.out.println("|         1.列出所有学生成绩      |");
        System.out.println("|         2.添加成绩             |");
        System.out.println("|         3.修改成绩             |");
        System.out.println("|         4.整体成绩信息          |");
        System.out.println("|         5.返回上级             |");
        System.out.println("---------------------------------");
        System.out.print("请输入你的选择：");
    }

    public boolean choose(boolean OK) {
        String choose = scan.nextLine();
        switch (choose) {
            case "1": showAllGrade();
                break;
            case "2": addGrade();
                break;
            case "3": updateGrade();
                break;
            case "4": gradeInfo();
                break;
            case "5":
                return false;
            default:
                System.out.println("没有该选项！");
        }
        return OK;
    }
    
    /*** 
     * @description 展示所有学生信息
     * @author WuCheng 
     */
    @Test
    public void showAllGrade() {
        String sql = "select stu.stu_id id,stu_name name, chinese,math,english,science,total_grade totalGrade from student stu join stu_grade sg on stu.stu_id = sg.stu_id";
        List<Student> list = DBUtils.selectInfo(Student.class, sql);
        if (list == null) {
            System.out.println("无成绩信息");
            return;
        }
        System.out.println("|学号\t|名字\t\t|语文\t|数学\t|英语\t|科学\t|总分\t|");
        Iterator<Student> iterator = list.iterator();
        while (iterator.hasNext()) {
            iterator.next().outputGraInfo();
        }
    }

    /*** 
     * @description 添加学生成绩
     * @author WuCheng 
     */
    @Test
    public void addGrade() {
        System.out.print("请输入需要添加成绩的学生学号：");
        String id = scan.nextLine();
        if (DBUtils.haveRecord(id)) {
            System.out.print("请输入语文成绩：");
            String chinese = scan.nextLine();
            System.out.print("请输入数学成绩：");
            String math = scan.nextLine();
            System.out.print("请输入英语成绩：");
            String english = scan.nextLine();
            System.out.print("请输入科学成绩：");
            String science = scan.nextLine();

            String sql = "insert into stu_grade(stu_id, chinese, math, english, science) values(?,?,?,?,?)";

            if (DBUtils.insertOrUpdate(sql, id, chinese, math, english, science) > 0) {
                System.out.println("录入成功");
            } else {
                System.out.println("录入失败，请检查格式或者成绩已存在");
            }
        } else {
            System.out.println("该学生不存在");
        }
    }

    /*** 
     * @description 修改学生成绩
     * @author WuCheng 
     */
    @Test
    public void updateGrade() {
        System.out.print("请输入需要修改成绩的学生学号：");
        String id = scan.nextLine();
        if (DBUtils.haveRecord(id)) {
            System.out.print("请输入语文成绩：");
            String chinese = scan.nextLine();
            System.out.print("请输入数学成绩：");
            String math = scan.nextLine();
            System.out.print("请输入英语成绩：");
            String english = scan.nextLine();
            System.out.print("请输入科学成绩：");
            String science = scan.nextLine();

            String sql = "update stu_grade set chinese = ?,math = ?,english = ?,science = ? where stu_id = ?";

            if (DBUtils.insertOrUpdate(sql, chinese, math, english, science, id) > 0) {
                System.out.println("修改成功");
            } else {
                System.out.println("修改失败");
            }
        }
    }

	/*** 
     * @description 查看各科成绩相关信息
     * @author WuCheng 
     */
    @Test
    public void gradeInfo() {
        String sql = "select avg(chinese) chinese,avg(math) math,avg(english) english,avg(science) science,avg(total_grade) totalGrade from stu_grade";
        float[] gradeAvg = new float[5];

        DBUtils.getAvgGrade(sql, gradeAvg);

        String gradeInfoSelect = "select count(*) from stu_grade where ? <= ";

        String[] subjects = {"chinese", "math", "english", "science"};
        List<GradeInfo> infos = new ArrayList();

        for (String subject : subjects) {
            GradeInfo gradeInfo = new GradeInfo(subject);
            if (subject.equals("science")) {
                gradeInfo.setExcellent(DBUtils.selectInfo(gradeInfoSelect, subject, 80));
                gradeInfo.setPass(DBUtils.selectInfo(gradeInfoSelect, subject, 60));
                gradeInfo.setNoPass(DBUtils.selectInfo(gradeInfoSelect, subject, 0) - gradeInfo.getPass());
            } else {
                gradeInfo.setExcellent(DBUtils.selectInfo(gradeInfoSelect, subject, 120));
                gradeInfo.setPass(DBUtils.selectInfo(gradeInfoSelect, subject, 90));
                gradeInfo.setNoPass(DBUtils.selectInfo(gradeInfoSelect, subject, 0) - gradeInfo.getPass());
            }
            infos.add(gradeInfo);
        }

        int person = infos.get(0).getPass() + infos.get(0).getNoPass();

        System.out.println("各科人数：");
        System.out.println("总人数:" + person);
        System.out.println("|科目\t|优秀\t|及格\t|不及格\t|");
        for (GradeInfo gradeinfo : infos) {
            gradeinfo.outputGradeInfo();
        }
        System.out.println("\n");
        System.out.println("各科平均分：");
        System.out.print("|语文\t|数学\t|英语\t|科学\t|总分\t|\n|");
        for (float i : gradeAvg) {
            System.out.print(i+"\t|");
        }
        System.out.println();

    }
}

对象

Student.java

import java.math.BigDecimal;

/***
 * @title Student
 * @description 学生对象
 * @author WuCheng
 * @version 1.0.0
 * @create 2022/9/21 19:54
 **/
public class Student {
    private int id;
    private String name;
    private int age;
    private BigDecimal chinese;
    private BigDecimal math;
    private BigDecimal english;
    private BigDecimal science;
    private BigDecimal totalGrade;

    /*** 
     * 中间的构造方法以及get、set方法省略
     */

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
	/*** 
     * @description 学生信息表格化输出方法
     * @author WuCheng 
     */
    public void outputStuInfo(){
        System.out.printf("|    %-5d\t|   %-3s\t  |\t%5d\t  |\n",getId(),getName(),getAge());
    }

    public String getGrade() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", chinese=" + chinese +
                ", math=" + math +
                ", english=" + english +
                ", science=" + science +
                ", totalGrade=" + totalGrade
                '}';
    }

	/*** 
     * @description 学生成绩表格化输出方法
     * @author WuCheng 
     */
    public void outputGraInfo(){ System.out.printf("|%d\t\t|%-8s\t|%.1f\t|%.1f\t|%.1f\t|%.1f\t|%.1f\t|\n",id,name,chinese.floatValue(),math.floatValue(),english.floatValue(),science.floatValue(),totalGrade.floatValue());
    }
}

GradeInfo.java

/***
 * @title GradeInfo
 * @description 成绩信息对象
 * @author WuCheng
 * @version 1.0.0
 * @create 2022/9/26 16:29
 **/
public class GradeInfo {
    private String subject;
    private int excellent;
    private int pass;
    private int noPass;

    /*** 
     * 中间的构造方法以及get、set方法省略
     */
    
    @Override
    public String toString() {
        return "GradeInfo{" +
                "subject='" + subject + '\'' +
                ", excellent=" + excellent +
                ", pass=" + pass +
                ", noPass=" + noPass +
                '}';
    }

    /*** 
     * @description 把科目转换为中文 
     * @date 2022/9/27 23:35
     * @author WuCheng 
     */
    public String toChinese(String subject){
        switch (subject) {
            case "chinese":
                return "语文";
            case "math":
                return "数学";
            case "english":
                return "英语";
            case "science":
                return "科学";
            default: return null;
        }
    }
    
    /*** 
     * @description 整体成绩信息格式化输出 
     * @date 2022/9/27 23:36
     * @author WuCheng 
     */
    public void outputGradeInfo(){
        System.out.println("|"+toChinese(subject)+"\t|\t"+excellent+"\t|\t"+pass+"\t|\t"+noPass+"\t|");
    }
}

调用入口

/***
 * @title Manager
 * @description 程序入口
 * @author WuCheng
 * @version 1.0.0
 **/
public class Manager {
    public static void main(String[] args) {
        StuInfoMethod mainMethod = new StuInfoMethod();
        do {
            mainMethod.userUI();
        } while (mainMethod.choose(true));
    }
}