反射封装JDBC

---------封装代码---------------

package jdbc.fanshe;

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 My_JDBC_Crud {
    private static String driverClass;
    private static String url;
    private static String username;
    private static String password;

    static {
        //运行阶段动态的加载类配置
        InputStream resourceAsStream = My_JDBC_Crud.class.getClassLoader().getResourceAsStream("jdbc.properties");
        //创建一个能够获取键值对的内部属性对象
        Properties properties = new Properties();
        //从数据流对象中获取配置信息
        try {
            properties.load(resourceAsStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        //解构值
        driverClass = properties.getProperty("driverClassName");
        url = properties.getProperty("url");
        username = properties.getProperty("username");
        password = properties.getProperty("password");

        //加载驱动
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 1.静态方法 获取数据库的链接
     *
     * @return 链接对象
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    /**
     * 封装预编译对象
     *
     * @param sql        sql语句
     * @param connection 链接
     * @param parameters 参数
     * @return
     * @throws SQLException
     */
    public static PreparedStatement getPreparedStatement(String sql, Connection connection, Object... parameters) throws SQLException {
        PreparedStatement prestatement = null;
        ParameterMetaData parameterMetaData = null;
        int count = 0;

        //获取预编译对象
        prestatement = connection.prepareStatement(sql);
        //获取预编译对象的元数据
        parameterMetaData = prestatement.getParameterMetaData();
        count = parameterMetaData.getParameterCount();
        if (count != 0 && parameters != null && parameters.length == count) {
            for (int i = 0; i < count; i++) {
                prestatement.setObject(i + 1, parameters[i]);
            }
        }
        return prestatement;
    }

    /**
     * 2.关闭数据库操作对象
     * @param conn
     * @param statement
     * @param resultSet
     * @throws SQLException
     */
    public static void close(Connection conn, PreparedStatement statement, ResultSet resultSet) throws SQLException {
        if (conn != null) {
            conn.close();
        }
        if (statement != null) {
            statement.close();
        }
        if (resultSet != null) {
            resultSet.close();
        }
    }

    /**
     *  3.测试更新
     *
     * @param sql
     * @param parameters 参数
     * @return 结果集
     * @throws SQLException
     */
    public static int updata1(String sql, Object... parameters) throws SQLException {
        //链接数据库
        Connection connection = My_JDBC_Crud.getConnection();
        PreparedStatement preStatement = My_JDBC_Crud.getPreparedStatement(sql, connection, parameters);
//        //获取预编译对象
//        PreparedStatement preStatement = connection.prepareStatement(sql);
//        int num = preStatement.getParameterMetaData().getParameterCount();
//
//        if (num != 0 && parameters != null && parameters.length == num) {
//            for (int i = 0; i < num; i++) {
//                preStatement.setObject(i + 1, parameters[i]);
//            }
//        }
        int result = preStatement.executeUpdate();
        My_JDBC_Crud.close(connection, preStatement, null);

        return result;
    }

    /*** 4.以指定的类型返回一条数据
     * @param sql
     * @param cls
     * @param parameters
     * @param <T>
     * @return
     * @throws SQLException
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static <T> T queryBean(String sql, Class<T> cls, Object... parameters) throws SQLException, NoSuchMethodException, InvocationTargetException, InstantiationException, IllegalAccessException {
        //1.定义查询对象
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        //定义泛型变量，类型取决传值
        T t = null;
        connection = My_JDBC_Crud.getConnection();
        statement = My_JDBC_Crud.getPreparedStatement(sql, connection, parameters);
        resultSet = statement.executeQuery();

        ResultSetMetaData metaData = resultSet.getMetaData();
        while (resultSet.next()) {
            t = cls.getConstructor().newInstance();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                BeanUtils.setProperty(t, metaData.getColumnName(i), resultSet.getObject(i));
            }
        }
        My_JDBC_Crud.close(connection, statement, resultSet);
        return t;
    }

    /**
     * * 5.以指定的类型返回list集合数据 返回结果
     *
     * @param sql
     * @param cls
     * @param parameters
     * @param <T>
     * @return
     * @throws SQLException
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static <T> ArrayList<T> queryBeanList(String sql, Class<T> cls, Object... parameters) throws SQLException, NoSuchMethodException, InvocationTargetException, InstantiationException, IllegalAccessException {
        //1.定义查询对象
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        //2.创建list集合
        ArrayList<T> arrayList = new ArrayList<>();
        connection = My_JDBC_Crud.getConnection();
        statement = My_JDBC_Crud.getPreparedStatement(sql, connection, parameters);
        resultSet = statement.executeQuery();

        ResultSetMetaData metaData = resultSet.getMetaData();
        while (resultSet.next()) {
            T t = cls.getConstructor().newInstance();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                BeanUtils.setProperty(t, metaData.getColumnName(i), resultSet.getObject(i));
            }
            arrayList.add(t);
        }
        My_JDBC_Crud.close(connection, statement, resultSet);
        return arrayList;
    }

    /**
     * * 6.以HashMap形式返回一条键值对数据
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static Map<String, Object> queryMap(String sql, Object... parameters) throws SQLException {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters);
        ResultSet resultSet = preparedStatement.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int count = metaData.getColumnCount();
        HashMap<String, Object> map = new HashMap<>();
        while (resultSet.next()) {

            for (int i = 0; i < count; i++) {
                map.put(metaData.getColumnName(i + 1), resultSet.getObject(i + 1));
            }
        }
        close(connection, preparedStatement, resultSet);
        return map;
    }

    /**
     * * 7.返回集合中数据是HashMap的list集合
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */

    public static ArrayList<Map> queryMapList(String sql, Object... parameters) throws SQLException {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters);
        ResultSet resultSet = preparedStatement.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int count = metaData.getColumnCount();
        ArrayList<Map> maps = new ArrayList<>();
        while (resultSet.next()) {
            HashMap<String, Object> map = new HashMap<>();
            for (int i = 0; i < count; i++) {
                map.put(metaData.getColumnName(i + 1), resultSet.getObject(i + 1));
            }
            maps.add(map);
        }
        close(connection, preparedStatement, resultSet);
        return maps;
    }

    /**
     * * 8.获取一条记录以对象的形式返回
     *
     * @param sql
     * @param parameters
     * @return arrays
     * @throws SQLException
     */
    public static Object[] queryArray(String sql, Object... parameters) throws SQLException {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters);
        ResultSet resultSet = preparedStatement.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int count = metaData.getColumnCount();
        Object[] objects = new Object[count];
        while (resultSet.next()) {
            for (int i = 0; i < count; i++) {
                objects[i] = resultSet.getObject(i + 1);
            }
        }
        close(connection, preparedStatement, resultSet);
        return objects;
    }

    /**
     ** 9.返回元素是数组的list集合框架
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static List<Object[]> queryArrayList(String sql, Object... parameters) throws SQLException {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters);
        ResultSet resultSet = preparedStatement.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int count = metaData.getColumnCount();
        ArrayList<Object[]> objectslist = new ArrayList<>();
        while (resultSet.next()) {
            Object[] objects = new Object[count];
            for (int i = 0; i < count; i++) {
                objects[i] = resultSet.getObject(i + 1);
            }
            objectslist.add(objects);
        }
        close(connection, preparedStatement, resultSet);
        return objectslist;
    }
}

----------测试代码-----------------

package jdbc;

import jdbc.fanshe.My_JDBC_Crud;

import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.util.List;

public class TestJdbc {
    public static void main(String[] args) throws SQLException, InvocationTargetException, NoSuchMethodException, InstantiationException, IllegalAccessException {
//        System.out.println("My_JDBC_Crud.getConnection() = " + My_JDBC_Crud.getConnection());

//        int i = My_JDBC_Crud.updata1("update user set nickname=? where id=?", "远野贵树", 5);
//        System.out.println("i = " + i);

//        ArrayList<user> user = My_JDBC_Crud.queryBeanList("select * from user where id=?", user.class, 3);
//        System.out.println("user = " + user);

//        Map<String, Object> aaa = My_JDBC_Crud.queryMap("select * from user", null);
//        System.out.println("aaa = " + aaa);

//        ArrayList<Map> maps = My_JDBC_Crud.queryMapList("select * from user", null);
//        System.out.println("aaa = " + maps);

//        Object[] aas = My_JDBC_Crud.queryArray("select * FROM user", null);
//        System.out.println("aas = " + aas);
//        for (int i = 0; i < aas.length; i++) {
//            for (Object o : aas) {
//                System.out.println("o = " + o);
//            }
//        }
        List<Object[]> objects = My_JDBC_Crud.queryArrayList("select * from user", null);
        for (int i = 0; i < objects.size(); i++) {
            for (int j = 0; j < objects.get(i).length; j++) {
                System.out.println("objects.get(i)[j] = " + objects.get(i)[j]);
            }
            System.out.println("------------");
        }
    }
}