java牛马之路17-2-封装DButil的过程

1.数据库工具类

public class DBUtil {

    private static String url;
    private static String username;
    private static String password;

    private static ThreadLocal<Connection> local;

    static{
        Properties properties = new Properties();
        try {
            properties.load(DBUtil.class.getClassLoader().getResourceAsStream("DBConfig.properties"));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        String driverName = properties.getProperty("driverName");
        url = properties.getProperty("url");
        username = properties.getProperty("username");
        password = properties.getProperty("password");

        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }

        local = new ThreadLocal<>();
    }

2.获取连接对象

  public static Connection getConnection() throws SQLException {
        Connection connection = local.get();//获取当前线程的Connection对象
        if(connection == null){
            connection = DriverManager.getConnection(url,username,password);
            local.set(connection);//将Connection对象添加到local中
        }
        return connection;
    }

3.关闭资源

 public static void close(Connection connection, Statement statement, ResultSet resultSet){
        if(resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(connection != null){
            try {
                if(connection.getAutoCommit()){
                    connection.close();
                    local.set(null);
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

4.事务的关闭与开启

    /**
     * 开启事务
     */
    public static void startTransaction() throws SQLException {
        Connection connection = getConnection();
        connection.setAutoCommit(false);
    }

    /**
     * 提交事务
     */
    public static void commit() throws SQLException {
        Connection connection = local.get();
        if(connection != null){
            connection.commit();
            local.set(null);
        }
    }

    public static void rollback() throws SQLException {
        Connection connection = local.get();
        if(connection != null){
            connection.rollback();
            local.set(null);
        }
    }

5.更新数据（添加、删除、修改）

public static int commonUpdate(String sql,Object... params) throws SQLException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql);
            paramHandler(statement,params);
            int num = statement.executeUpdate();
            return num;
        }finally {
            close(connection,statement,null);
        }
    }

6.添加数据 - 主键回填

public static int commonInsert(String sql,Object... params) throws SQLException {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
            paramHandler(statement,params);
            statement.executeUpdate();

            resultSet = statement.getGeneratedKeys();
            int primaryKey = 0;
            if(resultSet.next()){
                primaryKey = resultSet.getInt(1);
            }
            return primaryKey;
        }finally {
            close(connection,statement,resultSet);
        }
    }

7.查询数据

/**
     * 查询多个数据
     */
    public static <T> List<T> commonQueryList(Class<T> clazz,String sql, Object... params) throws SQLException, InstantiationException, IllegalAccessException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql);
            paramHandler(statement,params);
            resultSet = statement.executeQuery();

            //获取表数据对象
            ResultSetMetaData metaData = resultSet.getMetaData();
            //获取字段个数
            int count = metaData.getColumnCount();

            List<T> list = new ArrayList<>();

            while(resultSet.next()){

                T t = clazz.newInstance();

                //获取字段名及数据
                for (int i = 1; i <= count; i++) {
                    String fieldName = metaData.getColumnName(i);
                    Object fieldVal = resultSet.getObject(fieldName);
                    setField(t,fieldName,fieldVal);
                }
                list.add(t);
            }
            return list;
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
    }

    /**
     * 查询单个数据
     */
    public static <T> T commonQueryObj(Class<T> clazz,String sql, Object... params) throws SQLException, InstantiationException, IllegalAccessException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql);
            paramHandler(statement,params);
            resultSet = statement.executeQuery();

            //获取表数据对象
            ResultSetMetaData metaData = resultSet.getMetaData();
            //获取字段个数
            int count = metaData.getColumnCount();

            if(resultSet.next()){

                T t = clazz.newInstance();

                //获取字段名及数据
                for (int i = 1; i <= count; i++) {
                    String fieldName = metaData.getColumnName(i);
                    Object fieldVal = resultSet.getObject(fieldName);
                    setField(t,fieldName,fieldVal);
                }
                return t;
            }
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return null;
    }

8.处理statement对象参数数据的处理器

 private static void paramHandler(PreparedStatement statement,Object... params) throws SQLException {
        for (int i = 0; i < params.length; i++) {
            statement.setObject(i+1,params[i]);
        }
    }

9.获取当前类及其父类的属性对象

private static Field getField(Class<?> clazz,String name){

        for(Class<?> c = clazz;c != null;c = c.getSuperclass()){
            try {
                Field field = c.getDeclaredField(name);
                return field;
            } catch (NoSuchFieldException e) {
            } catch (SecurityException e) {
            }
        }
        return null;
    }

10.设置对象中的属性

 private static void setField(Object obj,String name,Object value){

        Field field = getField(obj.getClass(), name);
        if(field != null){
            field.setAccessible(true);
            try {
                field.set(obj, value);
            } catch (IllegalArgumentException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
    }