4. 常用类

1.连接池工具类

/**
 * 连接池工具类
 */
public class DataSourceUtil {

    //每个模块只需要一个连接池
    private static DataSource dataSource;

    //在静态代码块中创建连接池
    static {
        //1.读取属性文件
        Properties properties = new Properties();
        try (InputStream in = DataSourceUtil.class.getResourceAsStream("/jdbc.properties")) {
            properties.load(in);
            //2.通过属性文件创建连接池
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取数据源
     */
    public static DataSource getDataSource() {
        return dataSource;
    }

    /**
     * 从连接池中获取连接对象
     */
    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 关闭所有的资源
     */
    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        //关闭结果集
        try {
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //关闭语句对象
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //关闭连接对象
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 关闭所有的资源
     */
    public static void close(Connection connection, Statement statement) {
        close(connection, statement, null);
    }
}

2.通用增删改查类

/**
 * 通用的JDBC工具类
 */
public class JdbcTemplate<T> {

    //数据源
    private DataSource dataSource;

    /**
     * 传入一个数据源创建对象
     */
    public JdbcTemplate(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     * 获取连接对象
     */
    public Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 关闭所有的资源
     */
    public void close(Connection connection, Statement statement, ResultSet resultSet) {
        //先关闭结果集
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //关闭语句对象
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //关闭连接对象
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭所有的资源
     */
    public void close(Connection connection, Statement statement) {
        close(connection, statement, null);
    }

    /**
     * 实现增删改
     *
     * @param sql    要执行的SQL语句
     * @param params 占位符的值
     */
    public int update(String sql, Object... params) {
        Connection connection = null;
        PreparedStatement statement = null;
        int row = 0;
        try {
            //获取连接对象
            connection = getConnection();
            //创建预编译的语句对象
            statement = connection.prepareStatement(sql);
            //替换占位符：占位符位置，要替换的值
            setParameter(statement, params);
            //执行SQL语句
            row = statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //释放资源
            close(connection, statement);
        }
        return row;
    }

    private void setParameter(PreparedStatement statement, Object... params) throws SQLException {
        //1.获取参数元数据
        ParameterMetaData parameterMetaData = statement.getParameterMetaData();
        //2.获取参数的个数
        int parameterCount = parameterMetaData.getParameterCount();
        //3.给每个占位符赋值
        for (int i = 0; i < parameterCount; i++) {
            statement.setObject(i + 1, params[i]);
        }
    }

    /**
     * 添加记录
     *
     * @return 返回新增的主键值
     */
    public int save(String sql, Object... params) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int primaryKey = 0;
        try {
            //1.获取连接对象
            connection = getConnection();
            //2.创建预编译的语句对象，第2个参数：要设置获取主键
            statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            //3.设置占位符
            setParameter(statement, params);
            //4.执行添加操作，返回影响的行数
            int row = statement.executeUpdate();
            //添加成功再获取主键
            if (row > 0) {
                resultSet = statement.getGeneratedKeys();
                //如果有结果集，则获取数据
                if (resultSet.next()) {
                    //只有一列数据，直接封装给Student对象的id属性
                    primaryKey = resultSet.getInt(1);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //5.释放资源
            close(connection, statement, resultSet);
        }
        return primaryKey;
    }

    /**
     * 查询多条记录
     *
     * @param sql    要查询的SQL语句
     * @param type   要封装的实体类对象
     * @param params 占位符的值
     */
    public List<T> query(String sql, Class<T> type, Object... params) {
        List<T> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //1.获取连接对象
            connection = getConnection();
            //2.创建语句对象
            statement = connection.prepareStatement(sql);
            setParameter(statement, params);
            //3.执行SQL语句
            resultSet = statement.executeQuery();
            //4.获取结果集，并且封装
            while (resultSet.next()) {
                //调用方法，封装一个对象
                T entity = encapsulate(type, resultSet);
                list.add(entity);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //5.释放资源
            close(connection, statement, resultSet);
        }
        return list;
    }

    /**
     * 查询一条记录
     */
    public T queryForObject(String sql, Class<T> type, Object... params) {
        //直接调用上面的方法
        List<T> list = query(sql, type, params);
        if (!list.isEmpty()) {
            return list.get(0);
        } else {
            return null;
        }
    }

    /**
     * 将结果集封装成一个实体对象
     *
     * @param resultSet 结果集
     * @return 封装好的实体对象
     */
    private T encapsulate(Class<T> type, ResultSet resultSet) {
        T obj = null;
        try {
            //创建实体类对象
            obj = type.getConstructor().newInstance();
            //获取结果集元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            //获取总列数
            int columnCount = metaData.getColumnCount();
            //给每个成员变量赋值
            for (int i = 1; i <= columnCount; i++) {
                //获取列名或别名
                String columnLabel = metaData.getColumnLabel(i);
                //获取结果集中数据
                Object value = resultSet.getObject(i);
                //通过反射给属性赋值
                Field field = type.getDeclaredField(columnLabel);
                //设置暴力反射
                field.setAccessible(true);
                //给属性赋值
                field.set(obj, value);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return obj;
    }


    /**
     * 查询多条封装成List，其中每个元素是Map
     */
    public List<Map<String, Object>> queryForList(String sql, Object... params) {
        List<Map<String, Object>> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //1.获取连接对象
            connection = getConnection();
            //2.创建语句对象
            statement = connection.prepareStatement(sql);
            setParameter(statement, params);
            //3.执行SQL语句
            resultSet = statement.executeQuery();
            //4.获取结果集，并且封装
            while (resultSet.next()) {
                LinkedHashMap<String, Object> map = new LinkedHashMap<>();
                //获取结果集元数据
                ResultSetMetaData metaData = resultSet.getMetaData();
                //获取总列数
                int columnCount = metaData.getColumnCount();
                //给每个成员变量赋值
                for (int i = 1; i <= columnCount; i++) {
                    //获取列名或别名
                    String columnLabel = metaData.getColumnLabel(i);
                    //获取结果集中数据
                    Object value = resultSet.getObject(i);
                    //给Map赋值
                    map.put(columnLabel, value);
                }
                list.add(map);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //5.释放资源
            close(connection, statement, resultSet);
        }
        return list;
    }

    /**
     * 查询一条记录封装成Map
     */
    public Map<String, Object> queryForMap(String sql, Object... params) {
        List<Map<String, Object>> list = queryForList(sql, params);
        if (!list.isEmpty()) {
            return list.get(0);
        } else {
            return null;
        }
    }
}

3.mybatis获取sqlseesion会话工具类

/**
 * MyBatis会话工具类
 */
public class MyBatisUtil {
    //工厂类
    private final static SqlSessionFactory factory;

    //在类加载的时候执行一次
    static {
        try {
            //1.读取配置文件
            InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
            //2.创建工厂建造类
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            //3.创建会话工厂
            factory = builder.build(inputStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取会话对象
     */
    public static SqlSession getSession() {
        return factory.openSession();
    }
}