JDBC--自己编写工具类从数据库中添删改查成绩

编写的工具类

public class DatabaseConnectivity {
    //返回数据库连接的方法,传入src目录下的配置文件名
  public   static Connection getConnection(String jdbcPropertiesName) throws IOException, ClassNotFoundException, SQLException {
        //获取数据库连接
        ClassLoader classLoader = prepare_statement.class.getClassLoader();
        //返回一个得到配置文件的流
        InputStream asStream = classLoader.getResourceAsStream(jdbcPropertiesName);
        Properties properties=new Properties();
        Connection connection = null;
        properties.load(asStream);
            //获取资源
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driverClass = properties.getProperty("DriverClass");
            //注册驱动
        Class.forName(driverClass);
            //获取连接
        connection = DriverManager.getConnection(url, user, password);

        asStream.close();
        return connection;
    }
    public static void releaseResources(Connection connection, Statement statement) throws SQLException {
        if (connection!=null){
            connection.close();
        }
        if(statement!=null){
            statement.close();
        }
    }
    //重载的关闭资源方法
    public static void releaseResources(Connection connection, Statement statement,ResultSet resultSet) throws SQLException {
      if (connection!=null){
          connection.close();
      }
      if(statement!=null){
          statement.close();
      }
      if(resultSet!=null){
          resultSet.close();
      }

    }
    //更新数据库的方法，增，删改
    public static int updateData(String jdbcPropertiesName,String sql,Object ...args) throws SQLException, IOException, ClassNotFoundException {
        int count=0;
        //获取连接
        Connection connection = getConnection(jdbcPropertiesName);
        //获取预编译的statement对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //填充占位符
        if(args.length!=0){
            for(int i=0;i<args.length;i++){
                preparedStatement.setObject(i+1,args[i]);
            }
        }
        //执行
        count = preparedStatement.executeUpdate();//返回影响的行数
        //关闭资源
        releaseResources(connection,preparedStatement);
        return count;
    }

//    通用的查询方法--针对于Customer表
    public static User selectOneUser(String sql,Object ...args)  {
        Connection connection=null;
        ResultSet resultSet=null;
        PreparedStatement preparedStatement=null;
        try {
            //获取数据库连接
            connection = getConnection("jdbc.properties");
            //获取预编译的statement接口对象
            preparedStatement = connection.prepareStatement(sql);
            //填充占位符
            for(int i=0;i<args.length;i++){
                preparedStatement.setObject(i+1,args[i]);
            }
            //获取执行的结果集
            resultSet = preparedStatement.executeQuery();
            //获取这个结果集的元数据：就是修饰这个结果集的数据（如列，列名等）
//        getMetaData()
//        检索此 ResultSet对象的列的数量，类型和属性。
            ResultSetMetaData metaData = resultSet.getMetaData();
            //获取列数
            int columnCount = metaData.getColumnCount();
//        当这个结果集有数据时
            if(resultSet.next()){
                //创建一个空参的user对象
                User user = new User();
                for(int i=1;i<=columnCount;i++){
                    //获取一个字段
                    Object object = resultSet.getObject(i);
                    //获取当前的列名
                    String columnName = metaData.getColumnName(i);
                    //通过反射，给对应属性赋值
                    Class<User> userClass = User.class;
                    Field declaredField = userClass.getDeclaredField(columnName);
                    //给获取到的属性赋值，将user对象套入当前模板，并赋值
                    declaredField.setAccessible(true);
                    declaredField.set(user,object);
                }

                return user;
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        finally {
            //关闭资源
            try {
                releaseResources(connection,preparedStatement,resultSet);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }
    //通用的针对Order表的查询，返回一个Order对象
    public static Order selectOneOrder(String sql,Object ...args)  {
        //针对于表的字段名与类的属性名不同的情况，
        //1、必须声明sql语句时，使用类的属性名来充当字段的别名
        //2、使用使用元数据时，需要使用getColumnLabel方法来获取别名，如果没有别名，则返回的是字段名
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //获取数据库连接
            connection = getConnection("jdbc.properties");
            //获取预编译的statement对象
            preparedStatement = connection.prepareStatement(sql);
            //填充占位符
            for(int i=0;i<args.length;i++){
                preparedStatement.setObject(i+1,args[i]);
            }
            //执行并返回结果集
            resultSet = preparedStatement.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            //获取列数
            int columnCount = metaData.getColumnCount();
            //将查询结果取出
            if(resultSet.next()){
                //创建一个空参的Order对象
                Order order = new Order();
                for(int i=1;i<=columnCount;i++){
                    //返回当前列属性值
                    Object object = resultSet.getObject(i);
                    //获取当前列的别名
                    String columnLabel = metaData.getColumnLabel(i);
                    //通过反射，给对应的属性赋值
                    Class<? extends Order> aClass = order.getClass();//返回一个类模板
                    Field declaredField = aClass.getDeclaredField(columnLabel);
                    declaredField.setAccessible(true);//除去私有权限
                    declaredField.set(order,object);//赋值

                }
                return order;
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        finally {
            //关闭资源
            try {
                releaseResources(connection,preparedStatement,resultSet);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }
    //通用的针对不同表的查询操作,通过传入的泛型参数来动态确定方法的返回值类型
    public static <T> T selectTables(Class<T> tClass,String sql,Object ...args){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            //获取数据库连接
            connection = getConnection("jdbc.properties");
            //获取预编译的statement对象
            preparedStatement = connection.prepareStatement(sql);
            //填充占位符
            for(int i=0;i<args.length;i++){
                preparedStatement.setObject(i+1,args[i]);
            }
            //执行并返回结果集
            resultSet = preparedStatement.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            //获取列数
            int columnCount = metaData.getColumnCount();
            //将查询结果取出
            if(resultSet.next()){
                //创建一个空参的T对象
                Constructor<T> constructor = tClass.getConstructor();
                T t = constructor.newInstance();//动态创建T类型的对象

                for(int i=1;i<=columnCount;i++){
                    //返回当前列属性值
                    Object object = resultSet.getObject(i);
                    //获取当前列的别名
                    String columnLabel = metaData.getColumnLabel(i);
                    //通过反射，给对应的属性赋值
                    Class<?> aClass = t.getClass();//返回一个类模板
                    Field declaredField = aClass.getDeclaredField(columnLabel);
                    declaredField.setAccessible(true);//除去私有权限
                    declaredField.set(t,object);//赋值

                }
                return t;
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            try {
                releaseResources(connection,preparedStatement,resultSet);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;

    }
//    查询任意表，返回多个对象的集合
public static <T> List<T> selectListTables(Class<T> tClass, String sql, Object ...args){
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        //获取数据库连接
        connection = getConnection("jdbc.properties");
        //获取预编译的statement对象
        preparedStatement = connection.prepareStatement(sql);
        //填充占位符
        for(int i=0;i<args.length;i++){
            preparedStatement.setObject(i+1,args[i]);
        }
        //执行并返回结果集
        resultSet = preparedStatement.executeQuery();
        //获取结果集的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        //获取列数
        int columnCount = metaData.getColumnCount();
        //存储对象的集合
        ArrayList<T> tList = new ArrayList<>();
        //将查询结果取出
        while (resultSet.next()){
            //创建一个空参的T对象
            Constructor<T> constructor = tClass.getConstructor();
            T t = constructor.newInstance();//动态创建T类型的对象

            for(int i=1;i<=columnCount;i++){
                //返回当前列属性值
                Object object = resultSet.getObject(i);
                //获取当前列的别名
                String columnLabel = metaData.getColumnLabel(i);
                //通过反射，给对应的属性赋值
                Class<?> aClass = t.getClass();//返回一个类模板
                Field declaredField = aClass.getDeclaredField(columnLabel);
                declaredField.setAccessible(true);//除去私有权限
                declaredField.set(t,object);//赋值

            }
            //将当前取出的对象存入集合
            tList.add(t);
        }
        return tList;
    } catch (IOException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } catch (NoSuchFieldException e) {
        e.printStackTrace();
    } catch (IllegalAccessException e) {
        e.printStackTrace();
    } catch (NoSuchMethodException e) {
        e.printStackTrace();
    } catch (InvocationTargetException e) {
        e.printStackTrace();
    } catch (InstantiationException e) {
        e.printStackTrace();
    } finally {
        //关闭资源
        try {
            releaseResources(connection,preparedStatement,resultSet);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
      return null;
}
}

具体的操作

/**
 * @Classname JdbcPractice2
 * @author: 我心
 * @Description: 创建四六级成绩的添，查，删的方法,针对于examstudent表
 * @Date 2021/9/19 16:08
 * @Created by Lenovo
 */
public class JdbcPractice2 {
    //输入身份证号查询
    public ExamStudent selectIDCardGrade(String iDCard){
        String sql="SELECT FlowID AS 'flowID',Type AS 'type',IDCard iDCard,ExamCard examCard,StudentName studentName ,Location location,Grade grade FROM examstudent WHERE IDCard =?";
        ExamStudent examStudent = DatabaseConnectivity.selectTables(ExamStudent.class, sql, iDCard);
        return examStudent;
    }
    //输入准考证号查询
    public ExamStudent selectFlowGrade(String examCard){
        String sql="SELECT FlowID AS 'flowID',Type AS 'type',IDCard iDCard,ExamCard examCard,StudentName studentName ,Location location,Grade grade FROM examstudent WHERE ExamCard=?";
        ExamStudent examStudent = DatabaseConnectivity.selectTables(ExamStudent.class, sql, examCard);
        return examStudent;
    }
    //删除信息
    public int deleteMessage(ExamStudent examStudent) throws SQLException, IOException, ClassNotFoundException {
        int flowID = examStudent.getFlowID();
        String sql="DELETE FROM examstudent WHERE FlowID=?;";
        int i = DatabaseConnectivity.updateData("jdbc.properties", sql, examStudent.getFlowID());
        return i;

    }
    //添加成绩
    public int addMessage(ExamStudent examStudent) throws SQLException, IOException, ClassNotFoundException {
        String sql="INSERT INTO examstudent(FlowID,Type,IDCard,ExamCard,StudentName,Location,Grade) VALUES(?,?,?,?,?,?,?)";
        int i = DatabaseConnectivity.updateData("jdbc.properties", sql, examStudent.getFlowID(),
                examStudent.getType(), examStudent.getiDCard(), examStudent.getExamCard(),
                examStudent.getStudentName(), examStudent.getLocation(), examStudent.getGrade());
        return i;
    }
    public static void main(String[] args) throws Exception {
        JdbcPractice2 jdbcPractice2 = new JdbcPractice2();
        Scanner scanner = new Scanner(System.in);
        System.out.println("----------------------------查询四六级成绩----------------------------");
        System.out.println("请输入选择：");
        System.out.println("1.查询操作\n2.删除操作\n3.添加成绩");
        String option=scanner.nextLine();
        if (option.equals("1")) {
            System.out.println("a.准考证号\nb.身份证号");
            String s = scanner.nextLine();
            //a.准考证号
            if (s.equals("a")){
                System.out.print("输入您的准考证号：");
                String s1 = scanner.nextLine();
                ExamStudent examStudent = jdbcPractice2.selectFlowGrade(s1);
                if(examStudent==null){
                    throw new Exception("查无此人！");
                }
                System.out.println("------------------------查询结果------------------------");
                System.out.println("流水号:"+examStudent.getFlowID()+"\n四级/六级："+examStudent.getType()+"\n身份证号："+examStudent.getiDCard()+
                        "\n准考证号："+examStudent.getExamCard()+"\n学生姓名："+examStudent.getStudentName()+"\n区域："+examStudent.getLocation()+
                        "\n成绩："+examStudent.getGrade());
            }
            else if(s.equals("b")){
                System.out.print("输入您的身份证号：");
                String s2=scanner.nextLine();
                ExamStudent examStudent = jdbcPractice2.selectIDCardGrade(s2);
                if(examStudent==null){
                    throw new Exception("查无此人！");
                }
                System.out.println("------------------------查询结果------------------------");
                System.out.println("流水号:"+examStudent.getFlowID()+"\n四级/六级："+examStudent.getType()+"\n身份证号："+examStudent.getiDCard()+
                        "\n准考证号："+examStudent.getExamCard()+"\n学生姓名："+examStudent.getStudentName()+"\n区域："+examStudent.getLocation()+
                        "\n成绩："+examStudent.getGrade());
            }
            else throw new Exception("输入的选择不正确！");
        }
        //删除操作
        else if(option.equals("2")){
            System.out.println("-----------------------删除操作-----------------------");
            System.out.println("a.通过准考证号删除\nb.通过身份证号删除");
            String s3=scanner.nextLine();
            //a.通过准考证号删除
            if (s3.equals("a")){
                System.out.print("输入准考证号：");
                String num=scanner.nextLine();
                ExamStudent examStudent = jdbcPractice2.selectFlowGrade(num);
                System.out.println("要删除的学生信息");
                System.out.println(examStudent);
                System.out.println("确认删除？Y/N");
                String sure=scanner.nextLine();
                while (true) {
                    if (sure.equals("Y")||sure.equals("y")){
                        int i = jdbcPractice2.deleteMessage(examStudent);
                        if(i>0){
                            System.out.println("删除成功！");
                            break;
                        }
                    }
                    else if(sure.equals("n")||sure.equals("N")){
                        break;
                    }
                    else
                        continue;
                }

            }
            //b.通过身份证号删除
            else if(s3.equals("b")){
                System.out.print("输入身份证号：");
                String num=scanner.nextLine();
                ExamStudent examStudent = jdbcPractice2.selectIDCardGrade(num);
                System.out.println("要删除的学生信息");
                System.out.println(examStudent);
                System.out.println("确认删除？Y/N");
                String sure=scanner.nextLine();
                while (true) {
                    if (sure.equals("Y")||sure.equals("y")){
                        int i = jdbcPractice2.deleteMessage(examStudent);
                        if(i>0){
                            System.out.println("删除成功！");
                            break;
                        }
                    }
                    else if(sure.equals("n")||sure.equals("N")){
                        break;
                    }
                    else
                        continue;
                }
            }
            else {
                throw new Exception("请输入正确的选择！");
            }

        }
        //添加操作
        else if(option.equals("3")){
            System.out.println("-------------------添加成绩信息-------------------");
            System.out.print("Type(4/6):");
            String Type=scanner.nextLine();
            System.out.print("IDCard（身份证号）:");
            String IDCard=scanner.nextLine();
            System.out.print("准考证号:");
            String ExamCard=scanner.nextLine();
            System.out.print("学生姓名:");
            String StudentName=scanner.nextLine();
            System.out.print("地址:");
            String Location=scanner.nextLine();
            System.out.print("成绩:");
            String Grade=scanner.nextLine();
            ExamStudent examStudent=new ExamStudent(Integer.parseInt(Type),IDCard,ExamCard,StudentName,Location,Integer.parseInt(Grade));
            int i = jdbcPractice2.addMessage(examStudent);
            if(i>0){
                System.out.println("添加成功！您已影响了"+i+"行");
            }
        }


    }
}