jdbc mysql增删改查方法封装

 详细解释都在代码注释里面

    private static String URL;
    private static String USER;
    private static String PWD;

    //    加载属性文件：只需要执行1次
    static {
//        Class.forName("com.mysql.cj.jdbc.Driver")
//        Map接口的实现类，存储键值对
        Properties properties = new Properties();
        try {
//            文件读取器
//            FileReader reader = new FileReader("E:\\JavaProjects\\java1\\java1\\day20\\db.properties");
//            类名.class.getClassLoader():获取到字节码 对应的的类加载器
//            getResourceAsStream():将读取到的资源文件转换为字节流
//            默认读取源码根目录中的位置
            InputStream inputStream = DbHelper.class.getClassLoader().getResourceAsStream("DbHelpe.properties");
            properties.load(inputStream);//加载文件
            URL = properties.getProperty("URL"); //根据属性名获取属性值
            USER = properties.getProperty("USER"); //根据属性名获取属性值
            PWD = properties.getProperty("PWD"); //根据属性名获取属性值
        } catch (FileNotFoundException e) {
            System.out.println(e.getMessage());
        } catch (IOException e) {
            System.out.println(e.getMessage());
        }


    }

    //    获取连接（内部使用)
    public static Connection getConn() throws SQLException {
        return DriverManager.getConnection(URL, USER, PWD);
    }


    //    List:集合 ArryList：动态数组，多个对象
//    每一个对象用什么？Student, Class,Teacher List<Student> List<Teacher>
//    对象使用Map：键值对，列名=列值,列名=列值,列名=列值
//    params:可变参数，在方法内部代表一个数组
    public static List<Map<String, Object>> query(String sql, Object... params) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            connection = getConn();
            statement = connection.prepareStatement(sql);//对sql命令进行预编译
            setParams(statement, params);//调用方法绑定参数
            resultSet = statement.executeQuery();//执行查询命令得到结果集
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();//通过结果集获取元数据对象：结果集的表结构：列名、列数量、
            while (resultSet.next()) { //逐行读取，next()：移动指针到 下一行，判断是否读到数据，一次读取一行
                Map<String, Object> rowMap = new HashMap<>(); //代表一行
                for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
//                    getColumnLabel():获取列的别名，如果没有就获取列名
                    rowMap.put(resultSetMetaData.getColumnLabel(i), resultSet.getObject(i) == null ? " " : resultSet.getObject(i));
                }
                list.add(rowMap);//填充list
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(connection, statement, resultSet);//调用关闭方法
        }
        return list;
    }

    /**
     * 关闭方法
     *
     * @param connection
     * @param statement
     * @param resultSet
     */
    public static void closeAll(Connection connection, PreparedStatement statement, ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }

    //    连接
//    预编译的语句对象
//    结果
    public static boolean update(String sql, Object... params) {
        Connection connection = null;
        PreparedStatement statement = null;
        boolean result = false;
        try {
            connection = getConn();
            statement = connection.prepareStatement(sql);//预编译
            setParams(statement, params);//调用方法绑定参数
            int n = statement.executeUpdate();
            if (n > 0) {
                result = true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(connection, statement, null);
        }
        return result;
    }

    /**
     * 设置参数
     *
     * @param statement
     * @param params
     * @throws SQLException
     */
    private static void setParams(PreparedStatement statement, Object[] params) throws SQLException {
        //  绑定参数
        if (params != null) {
            for (int i = 0; i < params.length; i++) {
                statement.setObject(i + 1, params[i]);
            }
        }
    }

    public static List<Emp> queryEmps(String sql, Object... params) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Emp> list = new ArrayList<>();
        try {
            connection = getConn();
            statement = connection.prepareStatement(sql);//对sql命令进行预编译
            setParams(statement, params);//调用方法绑定参数
            resultSet = statement.executeQuery();//执行查询命令得到结果集
            while (resultSet.next()) { //逐行读取，next()：移动指针到 下一行，判断是否读到数据，一次读取一行
                Emp emp = new Emp();//创建员工对象
                emp.setEmpno(resultSet.getInt(1));
                emp.setEname(resultSet.getString(2));
                emp.setHiredate(resultSet.getDate("hiredate").toLocalDate());
                list.add(emp);//填充list

                Date date = resultSet.getDate(1);
                Time time = resultSet.getTime(2);

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(connection, statement, resultSet);//调用关闭方法
        }
        return list;
    }
    /**
     * 录入数据后获取最新的自增列的值
     *
     * @return
     */
    public static int insertAndGet(String sql, Object... params) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int id = 0;//要返回的id
        try {
            connection=getConn();
            statement=connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
            setParams(statement,params);//调用方法绑定参数
            int n=statement.executeUpdate();//执行命令
            if(n>0){
                resultSet=statement.getGeneratedKeys();//获取自增的key
                if (resultSet.next()){
                    id=resultSet.getInt(1);//获取结果集中的id
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbHelper.closeAll(connection,statement,null);
        }
        return id;
    }