JdbcTemplateUtil 工具类分享

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * JdbcTemplateUtil 工具类（默认使用druid连接池）
 */
public class JdbcTemplateUtil<JdbcAbstractMapper> {

    public static void main(String[] args) throws IOException {
        System.out.println(JdbcTemplateUtil.getConnection());
        /**
         * 查询
         */
        String sql = "select account_name ,password_new from test_user where id>?";
        Object[] objArr = {1};
        AbstractJdbcMapper abstractJdbcMapper = new AbstractJdbcMapper() {
            @Override
            public Object mappinng(ResultSet rs) throws Exception {
//                return defalutMapping(rs, null);
//                return defalutMapping(rs, UserBean.class);
                UserBean userBean = new UserBean();
                userBean.setAccountName(rs.getString(1));
                userBean.setPasswordNew(rs.getString(2));
                return userBean;
            }
        };
//        List<Map<String, Object>> list = JdbcTemplateUtil.getInstance().findAllInfo(sql, objArr, abstractJdbcMapper);
//        for (Map<String, Object> map : list) {
//            System.out.println(map);
//        }

        List<UserBean> list = JdbcTemplateUtil.getInstance().findAllInfo(sql, objArr, abstractJdbcMapper);
        for (UserBean userBean : list) {
            System.out.println(userBean);
        }
//        UserBean userBean = (UserBean) JdbcTemplateUtil.getInstance().findOneInfo(sql, objArr, abstractJdbcMapper);
//        System.out.println(userBean);
        /**
         * 更新
         */
        String sql0 = "insert into  test_user ( account_name ,password_new) values(?,?)";
        Object[] objArr0 = {"dafa", "fdasd"};
        int id = JdbcTemplateUtil.getInstance().updateOne(sql0, objArr0, Boolean.TRUE);
        int record = JdbcTemplateUtil.getInstance().updateOne(sql0, objArr0, Boolean.FALSE);
        System.out.println("返回的主键ID=" + id + ",受影响条数为" + record);
    }

    /**
     * 单例模式
     */
    private static volatile JdbcTemplateUtil instance;

    private JdbcTemplateUtil() {
    }

    public static JdbcTemplateUtil getInstance() {
        if (instance == null) {
            synchronized (JdbcTemplateUtil.class) {
                if (instance == null) {
                    instance = new JdbcTemplateUtil();
                }
            }
        }
        return instance;
    }

    /**
     * 错误描述常量
     */
    public static final String ERROR_DB_DATASOURCE = "初始化数据库连接池异常";
    public static final String ERROR_DB_CONNECTION = "数据库获取连接异常";
    public static final String ERROR_DB_SQL = "数据库SQL执行异常";
    public static final String ERROR_DB_CLOSE = "数据库关闭连接异常";
    /**
     * druid连接池
     */
    private static DataSource druid;

    /**
     * 静态代码块
     */
    static {
        initDataSource();
    }

    /**
     * 初始化连接池
     */
    public static void initDataSource() {
        try {
            Properties properties = getPropertiesByName("application.properties", "spring.datasource.*", "spring.datasource.type");
            druid = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(ERROR_DB_DATASOURCE, e);
        }
    }

    /**
     * 读取配置文件指定开头的配置项
     *
     * @param filePath
     * @param prefixName       前缀【例如选择所有配置*   选择前缀开头的配置spring.datasource.*】
     * @param filterProperties 过滤掉的属性,逗号分隔 (为空或者空字符串时不过滤) key1,key2,key3
     * @return
     * @throws IOException
     */
    public static Properties getPropertiesByName(String filePath, String prefixName, String filterProperties) throws IOException {
        Properties properties = new Properties();
        InputStream inputStream = JdbcTemplateUtil.class.getClassLoader().getResourceAsStream(filePath);
        properties.load(inputStream);
        if (filterProperties != null && filterProperties.trim().length() > 0) {
            String[] keys = filterProperties.split("\\,");
            for (String key : keys) {
                properties.remove(key);
            }
        }
        prefixName = prefixName.substring(0, prefixName.length() - 1);
        //注意List遍历时不能直接remove删除，其中set在remove操作时set集合不能再有任何修改，否则hashcode发生改变，则不能remove成功
        Iterator<String> iterator = properties.stringPropertyNames().iterator();
        while (iterator.hasNext()) {
            String key = iterator.next();
            String value = properties.getProperty(key);
            if (key.startsWith(prefixName)) {
                properties.remove(key);
                key = key.replace(prefixName, "");
                properties.setProperty(key, value);
            } else {
                properties.remove(key);
            }
        }
        properties.list(System.out);
        return properties;
    }

    /**
     * 获取Connection
     *
     * @return
     */
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = druid.getConnection();
            connection.setAutoCommit(false);
            connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
        } catch (SQLException e) {
            throw new RuntimeException(ERROR_DB_CONNECTION, e);
        }
        return connection;
    }

    /**
     * 关闭资源
     *
     * @param conn
     * @param stmt
     * @param rs
     */
    public static void closeConnection(Connection conn, Statement stmt, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(ERROR_DB_CLOSE, e);
        }
    }


    /**
     * 封装更新（增、删、改）某条记录操作
     *
     * @param sql
     * @param objArr
     * @return
     */
    public int updateOne(String sql, Object[] objArr, boolean isReturnGeneratedKey) {
        // 初始化成员变量
        int record = 0;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // 注册并链接
        con = getConnection();
        try {
            con.setAutoCommit(false);
            // pstmt
            pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            if (objArr != null) {
                for (int i = 1; i <= objArr.length; i++) {
                    pstmt.setObject(i, objArr[i - 1]);
                }
            }
            // 执行
            record = pstmt.executeUpdate();
            con.commit();
            //是否返回主键
            if (isReturnGeneratedKey) {
                rs = pstmt.getGeneratedKeys();
                if (rs.next()) {
                    record = rs.getInt(1);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(ERROR_DB_SQL, e);
        } finally {
            closeConnection(con, pstmt, rs);
        }
        return record;
    }


    /**
     * 封装单条查询操作
     *
     * @param sql
     * @param objArr
     * @param mapper
     * @return
     */
    public Object findOneInfo(String sql, Object[] objArr, Object mapper) {
        // 初始化成员变量
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Object rsObject = null;
        // pstmt
        try {
            con = getConnection();
            pstmt = con.prepareStatement(sql);
            if (objArr != null) {
                for (int i = 1; i <= objArr.length; i++) {
                    pstmt.setObject(i, objArr[i - 1]);
                }
            }
            // 执行
            rs = pstmt.executeQuery();
            System.out.println(pstmt.toString());
            if (rs.next()) {
                if (mapper instanceof Class) {
                    Class clazz = (Class) mapper;
                    rsObject = new AbstractJdbcMapper() {
                        @Override
                        public Object mappinng(ResultSet rs) throws Exception {
                            return defalutMapping(rs, clazz);
                        }
                    };
                } else if (mapper instanceof AbstractJdbcMapper) {
                    AbstractJdbcMapper abstractJdbcMapper = (AbstractJdbcMapper) mapper;
                    rsObject = abstractJdbcMapper.mappinng(rs);
                } else {
                    throw new RuntimeException("Object mapper 参数必须为Class类或者实现AbstractJdbcMapper抽象类");
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(ERROR_DB_SQL, e);
        } finally {
            // 查询
            closeConnection(con, pstmt, rs);
        }
        return rsObject;
    }


    /**
     * 封装查询多条操作
     *
     * @param sql
     * @param objArr
     * @param mapper
     * @return
     */
    public List<? extends Object> findAllInfo(String sql, Object[] objArr, Object mapper) {
        List<Object> list = new ArrayList<Object>();
        // 初始化变量
        Object rsObject = null;
        // Connection.createStatement() 创建一个 Statement 对象来将 SQL 语句发送到数据库。
        Connection con = null;
        // PreparedStatement表示预编译的 SQL 语句的对象，Statement 对象的子接口
        PreparedStatement pstmt = null;
        // ResultSet查询获得的数据表,next方法将光标移动到下一行对象(对应数据库表中的行)，没有下一行时返回 false;
        ResultSet rs = null;
        try {
            // 注册并链接
            con = getConnection();
            // 创建PreparedStatement对象
            pstmt = con.prepareStatement(sql);
            if (objArr != null) {
                for (int i = 1; i <= objArr.length; i++) {
                    pstmt.setObject(i, objArr[i - 1]);
                }
            }
            // 执行
            rs = pstmt.executeQuery();
            System.out.println(pstmt.toString());
            while (rs.next()) {
                if (mapper instanceof Class) {
                    Class clazz = (Class) mapper;
                    rsObject = new AbstractJdbcMapper() {
                        @Override
                        public Object mappinng(ResultSet rs) throws Exception {
                            return defalutMapping(rs, clazz);
                        }
                    };
                } else if (mapper instanceof AbstractJdbcMapper) {
                    AbstractJdbcMapper abstractJdbcMapper = (AbstractJdbcMapper) mapper;
                    rsObject = abstractJdbcMapper.mappinng(rs);
                } else {
                    throw new RuntimeException("Object mapper 参数必须为Class类或者实现AbstractJdbcMapper抽象类");
                }
                list.add(rsObject);
            }
        } catch (Exception e) {
            throw new RuntimeException(ERROR_DB_SQL, e);
        } finally {
            closeConnection(con, pstmt, rs);
        }
        return list;

    }


}

abstract class AbstractJdbcMapper {
    /**
     * 下划线和驼峰互转正则常量
     */
    public static final Pattern LINE_PATTERN = Pattern.compile("_(\\w)");
    public static final Pattern HUMP_PATTERN = Pattern.compile("[A-Z]");

    /**
     * 抽象方法———自定义ResultSet转Object
     *
     * @param rs
     * @return
     * @throws Exception
     */
    public abstract Object mappinng(ResultSet rs) throws Exception;

    /**
     * 成员方法———默认ResultSet转Object
     *
     * @param rs
     * @param clazz
     * @return
     * @throws Exception
     */
    public Object defalutMapping(ResultSet rs, Class clazz) throws Exception {
        return resultSet2Object(rs, clazz);
    }


    /**
     * ResultSet转Object
     *
     * @param rs
     * @param clazz
     * @return
     * @throws Exception
     */
    private Object resultSet2Object(ResultSet rs, Class clazz) throws Exception {
        Map<String, Object> resultMap = new HashMap<>(16);
        //获取该行元数据
        ResultSetMetaData metaData = rs.getMetaData();
        //获取该行总列数（根据元数据和列数可以获得该列对应的字段名称）
        int columnCount = metaData.getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            String lineKey = metaData.getColumnLabel(i);
            //下划线转驼峰
            String humpKey = lineToHump(lineKey);
            resultMap.put(humpKey, rs.getString(i));
        }
        if (clazz != null) {
            return map2Obj(resultMap, clazz);
        } else {
            return resultMap;
        }
    }

    /**
     * Map 转 Object
     *
     * @param map
     * @param clazz
     * @return
     * @throws Exception
     */
    private Object map2Obj(Map<String, Object> map, Class<?> clazz) throws Exception {
        Object obj = clazz.newInstance();
        Field[] declaredFields = obj.getClass().getDeclaredFields();
        for (Field field : declaredFields) {
            int mod = field.getModifiers();
            if (Modifier.isStatic(mod) || Modifier.isFinal(mod)) {
                continue;
            }
            field.setAccessible(true);
            field.set(obj, map.get(field.getName()));
        }
        return obj;
    }

    /**
     * 下划线转驼峰
     *
     * @param str
     * @return
     */
    private String lineToHump(String str) {
        str = str.toLowerCase();
        Matcher matcher = LINE_PATTERN.matcher(str);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, matcher.group(1).toUpperCase());
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

    /**
     * 驼峰转下划线
     *
     * @param str
     * @return
     */
    private String humpToLine(String str) {
        Matcher matcher = HUMP_PATTERN.matcher(str);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, "_" + matcher.group(0).toLowerCase());
        }
        matcher.appendTail(sb);
        return sb.toString();
    }
}

/*
 * 对象映射接口 Jdk1.8
 * (1) 增加default方法。default方法作用范围也是public，只是有了具体实现的方法体。对已有的接口，如果想对接口增加一个新方法，那么需要对所有实现该接口的类进行修改。而有了default方法，可以解决该问题。
 * (2) 新增static方法。static修饰的方法也是非抽象方法，使用同类的静态方法一样，给方法的调用带来了方便。程序入口main方法也是static，现在接口也可以运行了。
 */
public interface JdbcMapper {
    /**
     * 将结果映射为一个Object对象，亦可创建一个空的实现调用默认方法
     *
     * @param rs
     * @return
     */
    Object mappinng(ResultSet rs);


    /**
     * 使用jdk1.8接口中的static方法或者default方法
     */
    static Map<String, Object> defaultMappinng(ResultSet rs) {
        try {
            Map<String, Object> resultMap = new HashMap<>(16);
            //获取该行元数据
            ResultSetMetaData metaData = rs.getMetaData();
            //获取该行总列数（根据元数据和列数可以获得该列对应的字段名称）
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String lineKey = metaData.getColumnLabel(i);
                //下划线转驼峰
                String humpKey = LineToHumpTool.lineToHump(lineKey);
                resultMap.put(humpKey, rs.getString(i));
            }
        } catch (Exception e) {
            throw new RuntimeException("ResultSet结果映射为Map<String,Object>对象失败：", e);
        }
        return null;
    }

}