简易动态数据源（依赖commons-dbutils）

maven依赖

        <commons-dbutils.version>1.7</commons-dbutils.version>

 <!--数据库工具
            commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，
            并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。因此dbutils成为很多不喜欢hibernate的公司的首选。
        -->
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>${commons-dbutils.version}</version>
        </dependency>

动态数据源配置信息

import java.io.Serializable;

/**
 * className DynamicDataSourceProperty
 * description 动态数据源配置信息
 * 
 * @author 余业宏
 * @date 2022年04月04日 01:59:45
 * @version 1.0
 * @since 1.8
 */
public class DynamicDataSourceProperty implements Serializable {

    private static final long serialVersionUID = 8569960621876164733L;

    /**数据库唯一标识*/
    private String dataSourceIdent;
    /**数据库驱动类*/
    private String driverClassName;
    /**url*/
    private String url;
    /**用户名*/
    private String username;
    /**密码*/
    private String password;

    public String getDataSourceIdent() {
        return dataSourceIdent;
    }

    public void setDataSourceIdent(String dataSourceIdent) {
        this.dataSourceIdent = dataSourceIdent;
    }

    public String getDriverClassName() {
        return driverClassName;
    }

    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

多数据源配置

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * className DynamicDbConfig
 * description 多数据源配置
 *
 * @author 余业宏
 * @date 2022年03月30日 23:30:00
 * @version 1.0
 * @since 1.8
 */
@Data
@Component
@ConfigurationProperties(prefix = "dynamic-db-config")
public class DynamicDbConfig {

    /**
     * 数据库账号信息dynamicDataSources
     */
    private List<DynamicDataSourceProperty> dynamicDataSources;
}

JDBCConstants常量

/**
 * className JDBCConstants
 * description JDBCConstants
 *
 * @author 余业宏
 * @date 2022年04月13日 08:29:54
 * @version 1.0
 * @since 1.8
 */
public interface JDBCConstants{

    /**
     * 基本的删除SQL
     */
    String BASE_DELETE = "delete from {0} where {1} = ?";

    /**
     * 基本的假删除SQL
     */
    String BASE_DELETE_LOGICALLY = "update {0} set is_del = 1 where {1} = ?";

    /**
     * 基本的单条件查询SQL
     */
    String BASE_SELECT = "select * from {0} where {1} = ?";

    /**
     * 基本的通过多个条件查询SQL
     */
    String BASE_PARAMS_SELECT = "select * from {0} where 1=1";

    /**
     * 基本的统计SQL
     */
    String BASE_COUNT = "select count(*) from {0} where 1=1";

    /**
     * 基本的插入SQL
     */
    String BASE_INSERT = "insert into {0} ({1}) values ({2})";

    /**
     * 基本的更新SQL
     */
    String BASE_UPDATE = "update {0} set {1} where 1=1";
}

动态数据源工具类-DynamicDbUtil

import com.google.common.collect.Maps;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.ibatis.jdbc.SQL;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.io.IOException;
import java.lang.reflect.Field;
import java.nio.charset.StandardCharsets;
import java.security.NoSuchAlgorithmException;
import java.security.spec.InvalidKeySpecException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * className DynamicDbUtil
 * description 动态数据源工具类
 *
 * @author 余业宏
 * @date 2022年04月14日 17:09:03
 * @version 1.0
 * @since 1.8
 */
@Component
public class DynamicDbUtil {

    private static final Logger logger = LoggerFactory.getLogger(DynamicDbUtil.class);

    /**
     * 连接集合
     */
    private static Map<String, DynamicDataSourceProperty> dynamicDataSourceConcurrentMap = Maps.newConcurrentMap();

    /**
     * 连接集合
     */
    private static Map<String, Connection> connectionMap = Maps.newConcurrentMap();

    /**
     * 数据库连接信息
     */
    @Resource
    private DynamicDbConfig dynamicDbConfig;


    private static final Object LOCK = new Object();


    /**
     * 功能描述: 数据源初始化
     * @author 余业宏
     * @date 2022年04月19日 20:21:16
     **/
    @PostConstruct
    public void init() {
        List<DynamicDataSourceProperty> dynamicDataSources = dynamicDbConfig.getDynamicDataSources();
        for (DynamicDataSourceProperty dynamicDataSource : dynamicDataSources) {
            dynamicDataSourceConcurrentMap.put(dynamicDataSource.getDataSourceIdent(), dynamicDataSource);
            logger.info("【动态数据源加载】 - [数据源标识]:{},[数据库驱动类]:{},[URL]:{},[用户名]:{},[密码]:{}",
                    dynamicDataSource.getDataSourceIdent(),
                    dynamicDataSource.getDriverClassName(),
                    dynamicDataSource.getUrl(),
                    dynamicDataSource.getUsername(),
                    dynamicDataSource.getPassword());
        }
        logger.info("【动态数据源加载】 - {}个动态数据源加载完成", dynamicDataSourceConcurrentMap.size());
    }

    /**
     * 功能描述: 获取数据库连接
     * @author 余业宏
     * @date 2022年03月31日 00:09:34
     *
     * @param url 数据库连接url
     * @param user 用户名
     * @param password 密码
     * @return java.sql.Connection
     **/
    private synchronized Connection createConnection(String driverClassName, String url, String user, String password) {
        Connection connection = null;
        try {
            DbUtils.loadDriver(driverClassName);
            connection = DriverManager.getConnection(url, user, password);
            logger.info("" + url);
        } catch (Exception e) {
            logger.error("connectionDataBase error: ", e);
        }
        return connection;
    }


    /**
     * 功能描述:  获取连接信息
     * @author 余业宏
     * @date 2022年04月13日 08:44:28
     *
     * @param dataSourceIdent 数据库连接唯一标识
     * @return java.sql.Connection
     **/
    private Connection getConnection(String dataSourceIdent) throws SQLException {
        Connection connection = connectionMap.get(dataSourceIdent);
        if (connection == null || connection.isClosed()) {
            synchronized (LOCK) {
                DynamicDataSourceProperty dataSourceProperty = dynamicDataSourceConcurrentMap.get(dataSourceIdent);
                connection = createConnection(dataSourceProperty.getDriverClassName(), dataSourceProperty.getUrl(), dataSourceProperty.getUsername(), dataSourceProperty.getPassword());
                connectionMap.put(dataSourceIdent, connection);
            }
        }
        return connection;
    }


    /**
     * 将Java Bean中的所有参数（包括NULL参数）插入到数据库
     * <p>Note: 这个方法需要Bean中的参数（驼峰法）与数据库的参数（下划线）一一对应</p>
     * @author 余业宏
     * @date 2022年04月13日 08:40:44
     * @since 1.0.0
     * @param dataSourceIdent 数据库标识
     * @param tableName 表名
     * @param k 待插入的Java Bean
     * @return java.lang.Integer
     */
    public <K> Integer insert(String dataSourceIdent, String tableName, K k) {
        QueryRunner queryRunner = new QueryRunner();
        try {
            //获取连接
            Connection connection = getConnection(dataSourceIdent);

            Map<String, Object> params = objectToMap(k);
            //参数
            List<String> keysList = new ArrayList<>();
            //问号
            List<String> markList = new ArrayList<>(params != null ? params.size() : 1);
            //参数值
            List<Object> valuesList = new ArrayList<>();
            if (params != null && params.size() > 0) {
                //拼接插入参数
                for (Map.Entry<String, Object> entry : params.entrySet()) {
                    keysList.add(humpToUnderline(entry.getKey()));
                    valuesList.add(entry.getValue());
                    markList.add("?");
                }
            }
            //组装SQL
            String sql = MessageFormat.format(JDBCConstants.BASE_INSERT, tableName
                    , StringUtils.join(keysList, ","), StringUtils.join(markList, ","));
            return queryRunner.update(connection, sql, valuesList.toArray());
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return 0;
        }
    }

    /**
     * 将Java Bean中有值的参数插入到数据库（批量插入）
     * @author 余业宏
     * @date 2022年04月13日 15:22:23
     * @since 1.0.0
     * @param tableName 表名
     * @param list 待插入的Java Bean的集合
     * @return int[]
     */
    public <K> int[] insertBatchSelective(String dataSourceIdent, String tableName, List<K> list) {
        QueryRunner queryRunner = new QueryRunner();
        try {
            //获取连接
            Connection connection = getConnection(dataSourceIdent);
            if (list != null && list.size() > 0) {
                //所有参数值
                Object[][] params = new Object[list.size()][];
                //参数
                List<String> keysList = new ArrayList<>();
                //问号
                List<String> markList = new ArrayList<>();
                for (int i = 0; i < list.size(); i++) {
                    K k = list.get(i);
                    Map<String, Object> elementParams = objectToMap(k);
                    //参数值
                    List<Object> valuesList = new ArrayList<>();

                    if (elementParams != null && elementParams.size() > 0) {
                        //拼接插入参数
                        for (Map.Entry<String, Object> entry : elementParams.entrySet()) {
                            if (entry.getValue() != null) {
                                //只有第一次才设置参数
                                if (i == 0) {
                                    keysList.add(humpToUnderline(entry.getKey()));
                                    markList.add("?");
                                }
                                valuesList.add(entry.getValue());
                            }
                        }
                        if (valuesList.size() > 0) {
                            params[i] = valuesList.toArray();
                        }
                    }
                }
                //组装SQL
                String sql = MessageFormat.format(JDBCConstants.BASE_INSERT, tableName
                        , StringUtils.join(keysList, ","), StringUtils.join(markList, ","));
                return queryRunner.batch(connection, sql, params);
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return new int[]{0};
    }

    /**
     * 更新数据（有值的参数才更新）
     * <p>Note: 这个方法需要Bean中的参数（驼峰法）与数据库的参数（下划线）一一对应</p>
     * @author zifangsky
     * @date 2018/11/14 15:36
     * @since 1.0.0
     * @param dataSourceIdent 数据库标识
     * @param tableName 表名
     * @param k 待更新的Java Bean
     * @param fieldName 判断字段名
     * @param value 判断条件
     * @return java.lang.Integer
     */
    public <K> Integer updateSelective(String dataSourceIdent, String tableName, K k, String fieldName, Object value) {
        QueryRunner queryRunner = new QueryRunner();
        try {
            //获取连接
            Connection connection = getConnection(dataSourceIdent);
            Map<String, Object> params = objectToMap(k);
            //参数
            List<String> keysList = new ArrayList<>();
            //参数值
            List<Object> valuesList = new ArrayList<>();
            if (params != null && params.size() > 0) {
                //拼接更新参数
                for (Map.Entry<String, Object> entry : params.entrySet()) {
                    if (entry.getValue() != null) {
                        keysList.add(humpToUnderline(entry.getKey()) + " = ?");
                        valuesList.add(entry.getValue());
                    }
                }
            }
            //组装SQL
            String sql = MessageFormat.format(JDBCConstants.BASE_UPDATE, tableName, StringUtils.join(keysList, ","))
                    + " and " + fieldName + " = ?";
            valuesList.add(value);
            return queryRunner.update(connection, sql, valuesList.toArray());
        } catch (Exception e) {
            logger.error(e.getMessage());
            return 0;
        }
    }

    /**
     * 功能描述: 查询语句
     * @author 余业宏
     * @date 2022年03月31日 00:07:30
     *
     * @param dataSourceIdent 数据源标识
     * @param sql 执行sql语句
     * @param clazz  clazz
     * @return java.util.List<T>
     **/
    public <T> List<T> query(String dataSourceIdent, String sql, Class<T> clazz) {
        try {
            logger.info("数据源标志：{},执行SQL语句：{}", dataSourceIdent, sql);
            //获取连接
            Connection connection = getConnection(dataSourceIdent);
            QueryRunner queryRunner = new QueryRunner();
            //开启下划线->驼峰转换所用
            BeanProcessor bean = new GenerousBeanProcessor();
            RowProcessor processor = new BasicRowProcessor(bean);
            BeanListHandler<T> beanListHandler = new BeanListHandler<>(clazz, processor);
            return queryRunner.query(connection, sql, beanListHandler);
        } catch (SQLException e) {
            logger.error("sql 查询错误", e);
            throw new RuntimeException("sql 查询错误");
        }
    }


    /**
     * 单表单条件查询
     * @author 余业宏
     * @date 2022年04月13日 10:44:39
     * @since 1.0.0
     * @param dataSourceIdent 数据源标识
     * @param tableName 表名
     * @param fieldName 判断字段名
     * @param value 判断条件
     * @param clazz 返回的Bean的类型
     * @return K
     */
    public <K> K select(String dataSourceIdent, String tableName, String fieldName, Object value, Class<K> clazz) {
        QueryRunner queryRunner = new QueryRunner();
        try {
            //获取连接信息
            Connection connection = getConnection(dataSourceIdent);
            String sql = MessageFormat.format(JDBCConstants.BASE_SELECT, tableName, fieldName);
            //开启下划线->驼峰转换所用
            BeanProcessor bean = new GenerousBeanProcessor();
            RowProcessor processor = new BasicRowProcessor(bean);
            return queryRunner.query(connection, sql, new BeanHandler<>(clazz, processor), value);
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
            return null;
        }
    }

    /**
     * 单表单条件查询列表
     * @author 余业宏
     * @date 2022年04月13日 10:44:39
     * @since 1.0.0
     * @param dataSourceIdent 数据源标识
     * @param tableName 表名
     * @param fieldName 判断字段名
     * @param value 判断条件
     * @param clazz 返回的Bean的类型
     * @return K
     */
    public <K> List<K> selectForList(String dataSourceIdent, String tableName, String fieldName, Object value, Class<K> clazz) {
        QueryRunner queryRunner = new QueryRunner();
        try {
            //获取连接信息
            Connection connection = getConnection(dataSourceIdent);
            String sql = MessageFormat.format(JDBCConstants.BASE_SELECT, tableName, fieldName);
            //开启下划线->驼峰转换所用
            BeanProcessor bean = new GenerousBeanProcessor();
            RowProcessor processor = new BasicRowProcessor(bean);
            BeanListHandler<K> beanListHandler = new BeanListHandler<>(clazz, processor);
            return queryRunner.query(connection, sql, beanListHandler, value);
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
            return null;
        }
    }

    /**
     * 多条件查询列表
     * @author zifangsky
     * @date 2018/11/14 11:28
     * @since 1.0.0
     * @param tableName 表名
     * @param params 参数Map
     * @param clazz 返回的Bean的类型
     * @return java.lang.Integer
     */
    public <K> List<K> selectForList(String dataSourceIdent, String tableName, Map<String, Object> params, Class<K> clazz) {
        try {
            Connection connection = getConnection(dataSourceIdent);
            QueryRunner queryRunner = new QueryRunner();
            StringBuilder builder = new StringBuilder(MessageFormat.format(JDBCConstants.BASE_PARAMS_SELECT, tableName));
            List<Object> valuesList = new ArrayList<>(params != null ? params.size() : 1);
            if (params != null && params.size() > 0) {
                //拼接查询条件
                for (Map.Entry<String, Object> entry : params.entrySet()) {
                    builder.append(" and ");
                    builder.append(entry.getKey());
                    builder.append(" = ?");
                    valuesList.add(entry.getValue());
                }
            }
            //开启下划线->驼峰转换所用
            BeanProcessor bean = new GenerousBeanProcessor();
            RowProcessor processor = new BasicRowProcessor(bean);
            BeanListHandler<K> beanListHandler = new BeanListHandler<>(clazz, processor);
            return queryRunner.query(connection, builder.toString(), beanListHandler, valuesList.toArray());
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
            return null;
        }
    }

    /**
     * 功能描述: sql语句构造参考
     * @author 余业宏
     * @date 2022年04月01日 08:24:22
     **/
    private void getSql() {
        String x = new SQL() {{
            SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
            SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
            FROM("PERSON P");
            FROM("ACCOUNT A");
            INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
            INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
            WHERE("P.ID = A.ID");
            WHERE("P.FIRST_NAME like ?");
            OR();
            WHERE("P.LAST_NAME like ?");
            GROUP_BY("P.ID");
            HAVING("P.LAST_NAME like ?");
            OR();
            HAVING("P.FIRST_NAME like ?");
            ORDER_BY("P.ID");
            ORDER_BY("P.FULL_NAME");
        }}.toString();
    }


    /**
     * 功能描述: 将Bean的所有参数转换为Map
     * @author 余业宏
     * @date 2022年04月13日 08:40:44
     *
     * @param bean  bean
     * @return java.util.Map<java.lang.String, java.lang.Object>
     **/
    private static Map<String, Object> objectToMap(Object bean) throws Exception {
        if (bean == null) {
            return null;
        } else {
            Field[] allFields = FieldUtils.getAllFields(bean.getClass());
            Map<String, Object> map = new HashMap<>(allFields.length);

            for (Field field : allFields) {
                field.setAccessible(true);
                map.put(field.getName(), field.get(bean));
            }
            return map;
        }
    }

    /**
     * 功能描述: 将驼峰法变量转下划线命名
     * @author 余业宏
     * @date 2022年04月13日 08:43:13
     *
     * @param str 原变量
     * @return java.lang.String
     **/
    private static String humpToUnderline(final String str) {
        StringBuilder builder = new StringBuilder(str);
        int point = 0;
        for (int i = 0; i < str.length(); i++) {
            if (Character.isUpperCase(str.charAt(i))) {
                builder.insert(i + point, "_");
                point += 1;
            }
        }
        return builder.toString().toLowerCase();
    }

    /**
     * 过滤排序变量，防止危险字符注入
     * @author 余业宏
     * @date 2018/11/15 18:36
     * @since 1.0.0
     * @param sortName 排序变量
     * @return java.lang.String
     */
    private String filterCharacter(String sortName) {
        if (StringUtils.isNoneBlank(sortName)) {
            String regexRule = "[\\u4e00-\\u9fa5\\w-]+";
            Pattern pattern = Pattern.compile(regexRule);
            Matcher matcher = pattern.matcher(sortName);
            if (matcher.find()) {
                return matcher.group();
            }
        }

        return null;
    }

}

使用示例

 @Resource
 private DynamicDbUtil dynamicDbUtil;
            
EmergencyEventJjxxDto jjxxDto = dynamicDbUtil.select("dataSourceIdent_db_01", "jcj_jjxx", "jjbh", eventNaturalKey, EmergencyEventJjxxDto.class);