JDBC实现四六级考试记录添加、查询、删除功能_考试信息删除接口-优快云博客

JDBC实现四六级考试记录添加、查询、删除功能

1. 代码演示

2. 项目的目录结构

3. 数据库mysql

4. code

4.1 ExamTest.class

package com.shan.examprace;

import com.shan.bean.ExamStudent;
import com.shan.util.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Scanner;

/**
 * @author shan
 * @date 2021/5/13
 */
public class ExamTest {

    public static void main(String[] args) {
        System.out.println("请选择你要输入的类型：");
        System.out.println("a.添加考生信息");
        System.out.println("b.查询考生信息");
        System.out.println("c.删除考生信息");
        Scanner scanner = new Scanner(System.in);
        String sel = scanner.next();
        if("a".equalsIgnoreCase(sel)){
            //问题1：向examstudent表中添加一条数据
            testInsert();
        }else if("b".equalsIgnoreCase(sel)){
            //问题2：根据身份证号或者准考证号查询学生成绩信息
            queryWithIDCardOrExamCard();
        }else if("c".equalsIgnoreCase(sel)){
            //问题2：根据身份证号或者准考证号查询学生成绩信息
            delWithExamCard();
        }
        else {
            System.out.println("您的输入有误，请重新进入程序。");
        }




    }

    private static void delWithExamCard() {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入要删除考生的准考证号：");
        String examCard = scanner.next();
        String sql = "DELETE FROM examstudent WHERE ExamCard = ?";
        int delcount = update(sql, examCard);
        if(delcount!=0){
            System.out.println("删除成功！");
        }else {
            System.out.println("查无此人，删除失败！");
        }
    }

    public static void queryWithIDCardOrExamCard(){
        System.out.println("请选择你要输入的类型：");
        System.out.println("a.准考证号");
        System.out.println("b.身份证号");
        Scanner scanner = new Scanner(System.in);
        String sel = scanner.next();
        if("a".equalsIgnoreCase(sel)){
            System.out.println("请输入准考证号：");
            String examCard = scanner.next();
            //若数据库字段名和ExamStudent.class中不一致时，需要起别名
            // FlowID --> 数据库字段名 、 flowID ExamStudent类 --> 字段名
            // String sql = "select FlowID flowID,Type type,IDCard,ExamCard examCard,StudentName name, Location location, Grade grade from examstudent where examCard=?";
            String sql = "select * from examstudent where ExamCard=?";
            ExamStudent student = getInstance(ExamStudent.class, sql, examCard);
            if(student!=null){
                System.out.println(student);
            }else {
                System.out.println("输入的准考证号有误！");
            }


        }else if("b".equalsIgnoreCase(sel)){
            System.out.println("请输入身份证号：");
            String IDCard = scanner.next();
            String sql = "select * from examstudent where IDCard=?";
            ExamStudent student = getInstance(ExamStudent.class, sql, IDCard);
            if(student!=null){
                System.out.println(student);
            }else {
                System.out.println("输入的准考证号有误！");
            }
        }else {
            System.out.println("您的输入有误，请重新进入程序。");
        }
    }


    public static void testInsert() {
        Scanner scanner = new Scanner(System.in);
        System.out.print("四级/六级（输入格式： 4/6）: ");
        int Type = scanner.nextInt();
        System.out.print("身份证号: ");
        String IDCard = scanner.next();
        System.out.print("准考证号: ");
        String ExamCard = scanner.next();
        System.out.print("学生姓名: ");
        String StudentName = scanner.next();
        System.out.print("所在城市: ");
        String Location = scanner.next();
        System.out.print("学生成绩: ");
        String Grade = scanner.next();

        String sql = "insert into examstudent(Type,IDCard,ExamCard,StudentName,Location,Grade)values(?,?,?,?,?,?)"; //?,?表示占位符
        int insertCount = update(sql, Type,IDCard,ExamCard,StudentName,Location,Grade);
        System.out.println(insertCount>0?"添加成功":"添加失败");

    }

    public static <T> T getInstance(Class<T> clazz,String sql,Object ...args){
        Connection conn = null;
        PreparedStatement ps =null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();

            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++)
                ps.setObject(i + 1, args[i]);

            rs = ps.executeQuery();
            // 获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集的列数
            int columnCount = rsmd.getColumnCount();


            if (rs.next()) {
                T t = clazz.newInstance();
                //处理结果集一行数据中的每一列
                for (int i = 0; i < columnCount; i++) {
                    //获取列值
                    Object columnValue = rs.getObject(i + 1);

                    //获取每个列的列名
                    String columnName = rsmd.getColumnName(i + 1);


                    //给cust对象指定columnName的属性，赋值为columnvalue,通过反射
                    Field field = clazz.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t, columnValue);

                }
                return t;

            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn, ps, rs);
        }

        return null;

    }

    //通用的增删改操作
    public static int update(String sql, Object... args) {//sql中占位符的个数应该与可变形参的长度一致
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1.获取数据库的连接
            conn = JDBCUtils.getConnection();

            //2.预编译sql语句，返回PrepareStatement的实例
            ps = conn.prepareStatement(sql);

            //3.填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }

            //4.执行
            //ps.execute(): 如果执行的是查询操作且有返回结果，返回true; 如果执行的是增、删、改操作，没有返回值 返回false；
            //方式一：
            //return ps.execute();
            //方式二：
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.资源的关闭
            JDBCUtils.closeResource(conn, ps);
        }
        return 0;

    }
}

4.2 ExamStudent.class

package com.shan.bean;

/**
 * @author shan
 * @date 2021/5/13
 */
public class ExamStudent {
    private long FlowID; //流水号
    private long Type; //考试类型
    private String IDCard; //身份证号
    private String ExamCard; //准考证号
    private String StudentName; //学生姓名
    private String Location; // 所在城市
    private long Grade; //分数

    public ExamStudent() {
    }

    public ExamStudent(long flowID, long type, String IDCard, String examCard, String studentName, String location, long grade) {
        FlowID = flowID;
        Type = type;
        this.IDCard = IDCard;
        ExamCard = examCard;
        StudentName = studentName;
        Location = location;
        Grade = grade;
    }

    public long getFlowID() {
        return FlowID;
    }

    public void setFlowID(long flowID) {
        FlowID = flowID;
    }

    public long getType() {
        return Type;
    }

    public void setType(long type) {
        Type = type;
    }

    public String getIDCard() {
        return IDCard;
    }

    public void setIDCard(String IDCard) {
        this.IDCard = IDCard;
    }

    public String getExamCard() {
        return ExamCard;
    }

    public void setExamCard(String examCard) {
        ExamCard = examCard;
    }

    public String getStudentName() {
        return StudentName;
    }

    public void setStudentName(String studentName) {
        StudentName = studentName;
    }

    public String getLocation() {
        return Location;
    }

    public void setLocation(String location) {
        Location = location;
    }

    public long getGrade() {
        return Grade;
    }

    public void setGrade(long grade) {
        Grade = grade;
    }

    @Override
    public String toString() {
        System.out.println("===========查询结果===========");
        return info();
//        return "ExamStudent{" +
//                "FlowID=" + FlowID +
//                ", Type=" + Type +
//                ", IDCard='" + IDCard + '\'' +
//                ", ExamCard='" + ExamCard + '\'' +
//                ", StudentName='" + StudentName + '\'' +
//                ", Location='" + Location + '\'' +
//                ", Grade=" + Grade +
//                '}';
    }

    private String info() {
        return "流水号：" + FlowID
                + "\n四级/六级：" + Type
                + "\n身份证号：" + IDCard
                + "\n准考证号：" + ExamCard
                + "\n考生姓名：" + StudentName
                + "\n所在地区：" + Location
                + "\n成绩：" + Grade
                ;
    }
}

4.3 JDBCUtils.class

package com.shan.util;

import com.shan.connection.ConnectionTest;

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

/**
 * @author shan
 * @date 2021/5/7
 * 操作数据库的工具类
 */
public class JDBCUtils {
    //获取数据库连接
    public static Connection getConnection() throws Exception {
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);

        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String DB_URL = pros.getProperty("DB_URL");
        String JDBC_DRIVER = pros.getProperty("JDBC_DRIVER");

        //2.加载驱动
        Class.forName(JDBC_DRIVER);

        //3.获取连接
        Connection conn = DriverManager.getConnection(DB_URL, user, password);
        return conn;

    }

    //关闭数据库的连接操作
    public static void closeResource(Connection conn, PreparedStatement ps){
        //资源的关闭
        try {
            if(ps != null)
                ps.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
        try {
            if(conn != null)
                conn.close();
        } catch (SQLException e){
            e.printStackTrace();
        }

    }
    // 查找的 资源关闭
    public static void closeResource(Connection conn, PreparedStatement ps, ResultSet result){
        //资源的关闭
        try {
            if(ps != null)
                ps.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
        try {
            if(conn != null)
                conn.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
        try {
            if(result != null)
                result.close();
        } catch (SQLException e){
            e.printStackTrace();
        }

    }
}

4.4 jdbc.properties

user=root
password=shan5211314..
DB_URL=jdbc:mysql://localhost:3306/test
JDBC_DRIVER=com.mysql.jdbc.Driver

代码主要翻译自：尚硅谷JDBC核心技术(新版jdbc)_哔哩哔哩 (゜-゜)つロ干杯~-bilibili