Day5_1--jdbc学习代码简记（重点：多种返回类型的查询的实现）

当方法的返回类型是泛型是一般需要给传入jabean类对象的反射类，在其他类型方法中就不用传入反射类。在Day学习的代码中写的泛型方法中没有传入反射类是因为我通过截取sql语句然后获取反射类了。

import org.apache.commons.beanutils.BeanUtils;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.*;

public class JdbcTools {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;

    static {
        InputStream resourceAsStream = JdbcTools.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        try {
            properties.load(resourceAsStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        driver = properties.getProperty("driver");
        url = properties.getProperty("url");
        username = properties.getProperty("username");
        password = properties.getProperty("password");
//加载驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
//释放资源
        if (resourceAsStream != null) {
            try {
                resourceAsStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 1.完成数据库链接操作
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection(url, username, password);
        return connection;
    }

    /**
     * 2.获取完整的预编译好的sql对象
     *
     * @param sql        预编译的sql语句
     * @param connection db链接对象
     * @param parameters sql替换的参数
     * @return 预编译好的sql对象
     * @throws SQLException
     */
    public static PreparedStatement getPreparedStatement(String sql, Connection connection, Object... parameters) throws SQLException {
        PreparedStatement statement = connection.prepareStatement(sql);
//1.获取元数据
        ParameterMetaData parameterMetaData = statement.getParameterMetaData();
//2.获取字符串需要的参数个数
        int count = parameterMetaData.getParameterCount();
//3.判断是否传参，并且设置参数值
        if (count != 0 && parameters != null && parameters.length == count)
            for (int i = 0; i < count; i++) {
                statement.setObject(i + 1, parameters[i]);
            }
        return statement;
    }

    /**
     * 3.实现更新
     *
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static int update1(String sql, Object... parameters) throws SQLException {
        Connection connection = JdbcTools.getConnection();
        PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters);
        int i = preparedStatement.executeUpdate();
        return i;
    }

    /**
     * 4. 关闭对象，释放资源
     *
     * @param resources
     */
    public static void closeAll(AutoCloseable... resources) {
        if (resources != null && resources.length > 0) {
            Arrays.stream(resources).forEach(source -> {
                if (source != null) {
                    try {
                        source.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            });
        }
    }

    /**
     * 5.根据条件查询一条数据,以传入的javaBean类型返回
     *
     * @param sql
     * @param cls
     * @param parameters
     * @param <T>
     * @return
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     */
    public static <T> T queryBean(String sql, Class<T> cls, Object... parameters) throws SQLException, InstantiationException, IllegalAccessException, InvocationTargetException {
//1.定义需要的变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        T t = null;
//2.获取链接
        connection = JdbcTools.getConnection();
//3.获取sql的预编译对象
        preparedStatement = JdbcTools.getPreparedStatement(sql, connection, parameters);
//4.执行查询，得到结果集
        resultSet = preparedStatement.executeQuery();
//5.反编译对象，从结果集中获取表的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        resultSet.next();
        t = cls.newInstance();//实例化一个bean的空对象，用来封装数据
        int count = metaData.getColumnCount();
        for (int i = 1; i <= count; i++) {
            BeanUtils.setProperty(t, metaData.getColumnName(i), resultSet.getObject(i));
        }
        JdbcTools.closeAll(connection, preparedStatement, resultSet);
        return t;
    }

    /**
     * 6.根据条件查询多条数据
     *
     * @param sql        String
     * @param cls        Class<T>
     * @param parameters 替换函数
     * @param <T>
     * @return
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     */
    public static <T> List<T> queryBeanList(String sql, Class<T> cls, Object... parameters) throws SQLException, InstantiationException, IllegalAccessException, InvocationTargetException {
//1.定义需要的变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<T> arrayList = new ArrayList<>();
        T t = null;
//2.获取链接
        connection = JdbcTools.getConnection();
//3.获取sql的预编译对象
        preparedStatement = JdbcTools.getPreparedStatement(sql, connection, parameters);
//4.执行查询，得到结果集
        resultSet = preparedStatement.executeQuery();
//5.反编译对象，从结果集中获取表的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        int count = metaData.getColumnCount();
        while (resultSet.next()) {
            t = cls.newInstance();//实例化一个bean的空对象，用来封装数据
            for (int i = 1; i <= count; i++) {
                BeanUtils.setProperty(t, metaData.getColumnName(i), resultSet.getObject(i));
            }
            arrayList.add(t);
        }
        JdbcTools.closeAll(connection, preparedStatement, resultSet);
        return arrayList;
    }

    /**
     * 7.查询多条记录，以Map集合的形式返回。
     *
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static ArrayList<Map> queryMapList(String sql, Object... parameters) throws SQLException {
//1.定义需要的变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Map> maps = new ArrayList<>();
//2.获取链接
        connection = JdbcTools.getConnection();
//3.获取sql的预编译对象
        preparedStatement = JdbcTools.getPreparedStatement(sql, connection, parameters);
//4.执行查询，得到结果集
        resultSet = preparedStatement.executeQuery();
//5.反编译对象，从结果集中获取表的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        int count = metaData.getColumnCount();
        while (resultSet.next()) {
            HashMap<String, Object> map = new HashMap<>();
            for (int i = 1; i <= count; i++) {
                map.put(metaData.getColumnName(i), resultSet.getObject(i));
            }
            maps.add(map);
        }
        JdbcTools.closeAll(connection, preparedStatement, resultSet);
        return maps;
    }

    /**
     * 8.查询一条记录，以数组形式返回
     *
     * @param sql
     * @param parameters
     * @return Array
     * @throws SQLException
     */
    public static Object[] queryArray(String sql, Object... parameters) throws SQLException {
//1.定义需要的变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
//2.获取链接
        connection = JdbcTools.getConnection();
//3.获取sql的预编译对象
        preparedStatement = JdbcTools.getPreparedStatement(sql, connection, parameters);
//4.执行查询，得到结果集
        resultSet = preparedStatement.executeQuery();
//5.反编译对象，从结果集中获取表的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        int count = metaData.getColumnCount();
        Object[] o = new Object[count];//定义长度为count的空数组
        resultSet.next();
        for (int i = 0; i < count; i++) {
            o[i] = resultSet.getObject(i + 1);
        }
        JdbcTools.closeAll(connection, preparedStatement, resultSet);
        return o;
    }

    /**
     * 9.查询多条记录，以数组集合（集合中的数据是数组）的数据状态返回
     *
     * @param sql        sql查询语句
     * @param parameters 替换参数
     * @return List
     * @throws SQLException
     */
    public static List<Object[]> queryArrayList(String sql, Object... parameters) throws SQLException {
//1.定义需要的变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Object[]> objects = new ArrayList<>();
//2.获取链接
        connection = JdbcTools.getConnection();
//3.获取sql的预编译对象
        preparedStatement = JdbcTools.getPreparedStatement(sql, connection, parameters);
//4.执行查询，得到结果集
        resultSet = preparedStatement.executeQuery();
//5.反编译对象，从结果集中获取表的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        int count = metaData.getColumnCount();
        while (resultSet.next()) {
            Object[] o = new Object[count];//定义长度为count的空数组
            for (int i = 0; i < count; i++) {
                o[i] = resultSet.getObject(i + 1);
            }
            objects.add(o);
        }
        JdbcTools.closeAll(connection, preparedStatement, resultSet);
        return objects;
    }

    /**
     * 10.查询一条记录，以键值对形式返回
     *
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static Map<String, Object> queryMap(String sql, Object... parameters) throws SQLException {
//1.定义需要的变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
//2.获取链接
        connection = JdbcTools.getConnection();
//3.获取sql的预编译对象
        preparedStatement = JdbcTools.getPreparedStatement(sql, connection, parameters);
//4.执行查询，得到结果集
        resultSet = preparedStatement.executeQuery();
//5.反编译对象，从结果集中获取表的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        int count = metaData.getColumnCount();
        HashMap<String, Object> map = new HashMap<>();
        while (resultSet.next()) {
            for (int i = 1; i <= count; i++) {
                map.put(metaData.getColumnName(i), resultSet.getObject(i));
            }
        }
        JdbcTools.closeAll(connection, preparedStatement, resultSet);
        return map;
    }

    public static void main(String[] args) throws SQLException, InvocationTargetException, InstantiationException, IllegalAccessException {
        System.out.println(queryArray("select * from student").toString());
//        System.out.println(queryBean("select * from student", student.class).toString());
    }
}