[大数据]原生jdbc+mysql控制台学生信息管理系统

表结构

CREATE TABLE IF NOT EXISTS `student`
(
?? ?stu_id INT PRIMARY KEY AUTO_INCREMENT,
?? ?stu_name CHAR(5) NOT NULL,
?? ?stu_major VARCHAR(25) NULL,
?? ?stu_email VARCHAR(50) UNIQUE NOT NULL,
?? ?stu_id_card VARCHAR(50) UNIQUE NOT NULL,
?? ?stu_location VARCHAR(25) NULL,
?? ?stu_grade INT NOT NULL,
?? ?CONSTRAINT ck_grader CHECK(stu_grade>=0 AND stu_grade<=100)
)

javaBean：

public class Student {
    private Integer id;
    private String name;
    private String major;
    private String email;
    private String idCard;
    private String location;
    private Integer grade;

    public Student() {
    }

    public Student(String name, String major, String email, String idCard, String location, Integer grade) {
        this.name = name;
        this.major = major;
        this.email = email;
        this.idCard = idCard;
        this.location = location;
        this.grade = grade;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", major='" + major + '\'' +
                ", email='" + email + '\'' +
                ", idCard='" + idCard + '\'' +
                ", location='" + location + '\'' +
                ", grade='" + grade + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getMajor() {
        return major;
    }

    public void setMajor(String major) {
        this.major = major;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getIdCard() {
        return idCard;
    }

    public void setIdCard(String idCard) {
        this.idCard = idCard;
    }

    public String getLocation() {
        return location;
    }

    public void setLocation(String location) {
        this.location = location;
    }

    public Integer getGrade() {
        return grade;
    }

    public void setGrade(Integer grade) {
        this.grade = grade;
    }
}

主类：

package com.xdy.exer;

import com.xdy.bean.Student;
import com.xdy.util.CMUtility;
import com.xdy.util.JDBCUtils;

import java.io.IOException;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.function.Consumer;

public class CRUDExer {
    /**
     * 更新操作
     * @param sql
     * @param args
     * @return
     */
    public int update(String sql,Object ...args){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.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) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,ps);
        }
        return 0;
    }

    /**
     * 针对所有表的通用查询
     */
    public <T> T getInstance(Class<T> clazz,String sql,Object ...args){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            resultSet = ps.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            if(resultSet.next()){
                Constructor<T> declaredConstructor = clazz.getDeclaredConstructor();
                T t = declaredConstructor.newInstance();
                for (int i=0;i<columnCount;i++){
                    Object columnValue = resultSet.getObject(i + 1);
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                return t;
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,ps,resultSet);
        }
        return null;
    }

    /**
     * 针对所有表查询多条记录
     */
    public <T> List<T> getForList(Class<T> clazz, String sql, Object ...args){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        List<T> list = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            resultSet = ps.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            list = new ArrayList<>();
            while (resultSet.next()){
                Constructor<T> declaredConstructor = clazz.getDeclaredConstructor();
                T t = declaredConstructor.newInstance();
                for (int i=0;i<columnCount;i++){
                    Object columnValue = resultSet.getObject(i + 1);
                    String columnLabel = metaData.getColumnLabel(i+1);
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,ps,resultSet);
        }
        return list;
    }

    public static void main(String[] args) {
        CRUDExer crudExer = new CRUDExer();
        boolean isExit = true;
        do {
            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.print("请选择(1~5)：");
            char menu = CMUtility.readMenuSelection();
            switch (menu) {
                case '1':
                    crudExer.Insert();
                    break;
                case '2':
                    crudExer.updateStu();
                    break;
                case '3':
                    crudExer.delete();
                    break;
                case '4':
                    System.out.println("学生编号\t学生姓名\t专业名称\t电子邮件\t身份证号\t籍贯\t分数");
                    crudExer.queryAll();
                    break;
                case '5':
                    System.out.println("正在退出...");
                    isExit = false;
                    break;
            }
        }while (isExit);
    }

    /**
     * 添加
     */
    public void Insert(){
        System.out.println("===========添加学生信息===========");
        System.out.print("请输入学生姓名：");
        String name = CMUtility.readString(5);
        System.out.print("请输入专业名称：");
        String major = CMUtility.readString(25);
        System.out.print("请输入电子邮件：");
        String email = CMUtility.readString(50);
        System.out.print("请输入身份证号码：");
        String idCard = CMUtility.readString(50);
        System.out.print("请输入籍贯：");
        String location = CMUtility.readString(25);
        System.out.print("请输入分数(0~100):");
        Integer grade = CMUtility.readInt();
        String sql = "insert into student(stu_name,stu_major,stu_email,stu_id_card,stu_location,stu_grade)values(?,?,?,?,?,?)";
        int update = update(sql,name,major,email,idCard,location,grade);
        if(update > 0){
            System.out.println("添加成功！");
        }else{
            System.out.println("添加失败！");
        }
    }

    /**
     * 修改
     */
    public void updateStu(){
        System.out.println("===========修改学生信息===========");
        System.out.print("请输入要修改的学生姓名：");
        String stuName = CMUtility.readString(5);
        String sql = "select stu_id id,stu_name name,stu_major major,stu_email email,stu_id_card idCard,stu_location location,stu_grade grade from student where stu_name = ?";
        Student student = getInstance(Student.class, sql, stuName);
        if(student == null){
            System.out.println("没有找到该学生的信息！");
        }else{
            System.out.print("请输入修改的专业名称("+student.getMajor()+"):");
            String newMajor = CMUtility.readString(50,student.getMajor());
            System.out.print("请输入修改的电子邮件("+student.getEmail()+"):");
            String newEmail = CMUtility.readString(50,student.getEmail());
            System.out.print("请输入修改的分数("+student.getGrade()+"):");
            Integer newGrade = CMUtility.readInt(student.getGrade());
            sql = "update student set stu_major = ?,stu_email = ?,stu_grade = ? where stu_name = ?";
            int update = update(sql, newMajor, newEmail, newGrade, stuName);
            if (update > 0){
                System.out.println("修改成功！");
            }else{
                System.out.println("修改失败！");
            }
        }
    }

    /**
     * 删除
     */
    public void delete(){
        System.out.println("===========删除学生信息===========");
        System.out.print("请输入要删除的学生姓名：");
        String stuName = CMUtility.readString(5);
        String sql = "select stu_id id,stu_name name,stu_major major,stu_email email,stu_id_card idCard,stu_location location,stu_grade grade from student where stu_name = ?";
        Student student = getInstance(Student.class, sql, stuName);
        if(student ==null){
            System.out.println("没有找到该学生信息！");
        }else{
            sql = "delete from student where stu_name = ?";
            int update = update(sql, stuName);
            if(update > 0){
                System.out.println("删除成功！");
            }else{
                System.out.println("删除失败！");
            }
        }
    }
    /**
     * 查看所有学生信息
     */
    public void queryAll(){
        String sql = "select stu_id id,stu_name name,stu_major major,stu_email email,stu_id_card idCard,stu_location location,stu_grade grade from student";
        List<Student> studentList = getForList(Student.class, sql);
        studentList.forEach(new Consumer<Student>() {
            @Override
            public void accept(Student student) {
                System.out.println(student.getId()+"\t"+student.getName()+"\t"+student.getMajor()+"\t"+student.getEmail()+
                        "\t"+student.getIdCard()+"\t"+student.getLocation()+"\t"+student.getGrade());
            }
        });
    }
}

两个工具类：

package com.xdy.util;


import java.util.*;
/**
CMUtility工具类：
将不同的功能封装为方法，就是可以直接通过调用方法使用它的功能，而无需考虑具体的功能实现细节。
*/
public class CMUtility {
    private static Scanner scanner = new Scanner(System.in);
    /**
   用于界面菜单的选择。该方法读取键盘，如果用户键入’1’-’5’中的任意字符，则方法返回。返回值为用户键入字符。
   */
   public static char readMenuSelection() {
        char c;
        for (; ; ) {
            String str = readKeyBoard(1, false);
            c = str.charAt(0);
            if (c != '1' && c != '2' && 
                c != '3' && c != '4' && c != '5') {
                System.out.print("选择错误，请重新输入：");
            } else break;
        }
        return c;
    }
   /**
   从键盘读取一个字符，并将其作为方法的返回值。
   */
    public static char readChar() {
        String str = readKeyBoard(1, false);
        return str.charAt(0);
    }
   /**
   从键盘读取一个字符，并将其作为方法的返回值。
   如果用户不输入字符而直接回车，方法将以defaultValue 作为返回值。
   */
    public static char readChar(char defaultValue) {
        String str = readKeyBoard(1, true);
        return (str.length() == 0) ? defaultValue : str.charAt(0);
    }
   /**
   从键盘读取一个长度不超过3位的整数，并将其作为方法的返回值。
   */
    public static int readInt() {
        int n;
        for (; ; ) {
            String str = readKeyBoard(3, false);
            try {
                n = Integer.parseInt(str);
                break;
            } catch (NumberFormatException e) {
                System.out.print("数字输入错误，请重新输入：");
            }
        }
        return n;
    }
   /**
   从键盘读取一个长度不超过2位的整数，并将其作为方法的返回值。
   如果用户不输入字符而直接回车，方法将以defaultValue 作为返回值。
   */
    public static int readInt(int defaultValue) {
        int n;
        for (; ; ) {
            String str = readKeyBoard(2, true);
            if (str.equals("")) {
                return defaultValue;
            }

            try {
                n = Integer.parseInt(str);
                break;
            } catch (NumberFormatException e) {
                System.out.print("数字输入错误，请重新输入：");
            }
        }
        return n;
    }
   /**
   从键盘读取一个长度不超过limit的字符串，并将其作为方法的返回值。
   */
    public static String readString(int limit) {
        return readKeyBoard(limit, false);
    }
   /**
   从键盘读取一个长度不超过limit的字符串，并将其作为方法的返回值。
   如果用户不输入字符而直接回车，方法将以defaultValue 作为返回值。
   */
    public static String readString(int limit, String defaultValue) {
        String str = readKeyBoard(limit, true);
        return str.equals("")? defaultValue : str;
    }
   /**
   用于确认选择的输入。该方法从键盘读取‘Y’或’N’，并将其作为方法的返回值。
   */
    public static char readConfirmSelection() {
        char c;
        for (; ; ) {
            String str = readKeyBoard(1, false).toUpperCase();
            c = str.charAt(0);
            if (c == 'Y' || c == 'N') {
                break;
            } else {
                System.out.print("选择错误，请重新输入：");
            }
        }
        return c;
    }

    private static String readKeyBoard(int limit, boolean blankReturn) {
        String line = "";

        while (scanner.hasNextLine()) {
            line = scanner.nextLine();
            if (line.length() == 0) {
                if (blankReturn) return line;
                else continue;
            }

            if (line.length() < 1 || line.length() > limit) {
                System.out.print("输入长度（不大于" + limit + "）错误，请重新输入：");
                continue;
            }
            break;
        }

        return line;
    }
}

package com.xdy.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    /**
     * 获取数据库连接
     * @return
     * @throws IOException
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
        Properties props = new Properties();
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        props.load(is);
        String url = props.getProperty("url");
        String user = props.getProperty("user");
        String password = props.getProperty("password");
        String driverClass = props.getProperty("driverClass");
        Class.forName(driverClass);
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }

    /**
     * 关闭资源
     */
    public static void closeResource(Connection conn, Statement ps){
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (ps != null)
                ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void closeResource(Connection conn, Statement ps, ResultSet resultSet){
        closeResource(conn,ps);
        try {
            if (resultSet != null)
                resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}