Spring动态加载、编辑数据源用于多数据源

需求是根据不同的用户分配不同的数据源，而且数据源最好可编辑，实现动态化。那最好的方案肯定是把数据源信息存数据库里啊。于是搜了好多文章，找到了这篇文章但文章中有点问题，一直不走写的代码，发现有一点写错了，或者是配置没写全的缘故，而且缺少一个文件，就在原来的基础上稍微修改了一下。

主要配置文件applicationContext.xml，不关键的已省略。

<!--多数据源切换管理-->
    <bean id="dynamicDataSource" class="com.rongtai.acs.core.utils.DynamicDataSource">
        <property name="targetDataSources">
            <map>
            </map>
        </property>
        <!--默认数据源-->
        <property name="defaultTargetDataSource" ref="dataSource" />
    </bean>

    <!-- local development环境 -->
    <beans profile="development">
        <context:property-placeholder ignore-resource-not-found="true"
                                      location="classpath*:/application.properties" />

        <!-- Tomcat JDBC连接池 -->
        <bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
            <property name="driverClassName" value="${jdbc.driver}" />
            <property name="url" value="${jdbc.url}" />
            <property name="username" value="${jdbc.username}" />
            <property name="password" value="${jdbc.password}" />
            <property name="defaultAutoCommit" value="false" />
            <!-- 默认值是true，当从连接池取连接时，验证这个连接是否有效-->
            <property name="testOnBorrow" value="true"/>
            <!--一条sql语句，用来验证数据库连接是否正常。这条语句必须是一个查询模式，并至少返回一条数据。可以为任何可以验证数据库连接是否正常的sql-->
            <property name="validationQuery" value="select 1"/>
            <!-- 是否自动回收超时连接-->
            <property name="removeAbandoned" value="true"/>
            <!-- 空闲时测试连接，必须配置validationQuery才有效-->
            <property name="testWhileIdle" value="true"/>
            <!-- 连接池启动时的初始值 -->
            <property name="initialSize" value="8"/>
            <!-- 连接Idle一个小时后超时 -->
            <property name="timeBetweenEvictionRunsMillis" value="3600000" />
            <property name="minEvictableIdleTimeMillis" value="3600000" />
        </bean>

        <!-- Jpa Entity Manager 配置 -->
        <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
            <property name="dataSource" ref="dynamicDataSource"/>
            <property name="jpaVendorAdapter" ref="hibernateJpaVendorAdapter"/>
            <property name="packagesToScan" value="com.rongtai.acs"/>
            <property name="jpaProperties">
                <props>
                    <!-- 命名规则 My_NAME->MyName -->
                    <prop key="hibernate.ejb.naming_strategy">org.hibernate.cfg.ImprovedNamingStrategy</prop>
                    <prop key="hibernate.hbm2ddl.auto">update</prop>
                    <!--<prop key="hibernate.hbm2ddl.auto">create-drop</prop>-->
                    <prop key="hibernate.hbm2ddl.import_files">sql/mysql/init.sql</prop>
                    <prop key="hibernate.hbm2ddl.import_files_sql_extractor">
                        org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor
                    </prop>
                    <prop key="hibernate.connection.useUnicode">true</prop>
                    <prop key="hibernate.connection.characterEncoding">UTF-8</prop>
                    <prop key="hibernate.connection.charSet">UTF-8</prop>
                </props>
            </property>
        </bean>
    </beans>

类一 DynamicDataSource.java

package com.rongtai.acs.core.utils;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.jdbc.datasource.lookup.JndiDataSourceLookup;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;

public class DynamicDataSource extends AbstractRoutingDataSource {
    private Logger log = LoggerFactory.getLogger(this.getClass());

    private Map<Object, Object> _targetDataSources;

    /**
     * @see org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource#determineCurrentLookupKey()
     * @describe 数据源为空或者为0时，自动切换至默认数据源，即在配置文件中定义的dataSource数据源
     */
    @Override
    protected Object determineCurrentLookupKey() {
        String dataSourceName = DbContextHolder.getDBType();
        if (dataSourceName == null) {
            dataSourceName = Constants.DEFAULT_DATA_SOURCE_NAME;
        } else {
            this.selectDataSource(dataSourceName);
        }
        log.debug("--------> use datasource " + dataSourceName);
        return dataSourceName;
    }

    /**
     * 到数据库中查找名称为dataSourceName的数据源
     *
     * @author Geloin
     * @date Jan 20, 2014 12:15:41 PM
     * @param dataSourceName
     */
    private void selectDataSource(String dataSourceName) {
        Object sid = DbContextHolder.getDBType();
        if (StringUtils.isEmpty(dataSourceName)
                || dataSourceName.trim().equals("dataSource")) {
            DbContextHolder.setDBType("dataSource");
            return;
        }
        Object obj = this._targetDataSources.get(dataSourceName);
        if (obj != null && sid.equals(dataSourceName)) {
            return;
        } else {
            DataSource dataSource = this.getDataSource(dataSourceName);
            if (null != dataSource) {
                this.setDataSource(dataSourceName, dataSource);
            }
        }
    }

    @Override
    public void setTargetDataSources(Map<Object, Object> targetDataSources) {
        this._targetDataSources = targetDataSources;
        super.setTargetDataSources(this._targetDataSources);
        afterPropertiesSet();
    }

    private void addTargetDataSource(String key, DataSource dataSource) {
        this._targetDataSources.put(key, dataSource);
        this.setTargetDataSources(this._targetDataSources);
    }

    private DataSource createDataSource(String driverClassName, String url,
                                        String username, String password) {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(driverClassName);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        return dataSource;
    }

    /**
     * 到数据库中查询名称为dataSourceName的数据源
     *
     * @author Geloin
     * @date Jan 20, 2014 12:18:12 PM
     * @param dataSourceName
     * @return
     */
    private DataSource getDataSource(String dataSourceName) {
        this.selectDataSource(Constants.DEFAULT_DATA_SOURCE_NAME);
        this.determineCurrentLookupKey();
        Connection conn = null;
        try {
            conn = this.getConnection();
            StringBuilder builder = new StringBuilder();
            builder.append("SELECT C_NAME,C_TYPE,C_URL,C_USER_NAME,");
            builder.append("C_PASSWORD,C_JNDI_NAME,C_DRIVER_CLASS_NAME ");
            builder.append("FROM IA_DATA_SOURCE WHERE c_name = ?");

            PreparedStatement ps = conn.prepareStatement(builder.toString());
            ps.setString(1, dataSourceName);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {

                Integer type = rs.getInt("C_TYPE");
                if (StringUtils.isNotEmpty(String.valueOf(type))) {
                    // DB
                    String url = rs.getString("C_URL");
                    String userName = rs.getString("C_USER_NAME");
                    String password = rs.getString("C_PASSWORD");
                    String driverClassName = rs
                            .getString("C_DRIVER_CLASS_NAME");
                    DataSource dataSource = this.createDataSource(
                            driverClassName, url, userName, password);
                    return dataSource;
                } else {
                    // JNDI
                    String jndiName = rs.getString("C_JNDI_NAME");

                    JndiDataSourceLookup jndiLookUp = new JndiDataSourceLookup();
                    DataSource dataSource = jndiLookUp.getDataSource(jndiName);
                    return dataSource;
                }

            }
            rs.close();
            ps.close();
        } catch (SQLException e) {
            log.error(String.valueOf(e));
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                log.error(String.valueOf(e));
            }
        }
        return null;
    }

    /**
     * 将已存在的数据源存储到内存中
     *
     * @author Geloin
     * @date Jan 20, 2014 12:24:13 PM
     * @param dataSourceName
     * @param dataSource
     */
    private void setDataSource(String dataSourceName, DataSource dataSource) {
        this.addTargetDataSource(dataSourceName, dataSource);
        DbContextHolder.setDBType(dataSourceName);
    }

}

类二 DbContextHolder.java

package com.rongtai.acs.core.utils;

public class DbContextHolder {

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();

    public static void setDBType(String dbType) {
        contextHolder.set(dbType);
    }

    public static String getDBType() {
        return (String) contextHolder.get();
    }

    public static void clearDBType() {
        contextHolder.remove();
    }

    /**
     *  切换数据源语句     字符串为实体类GsoftDataSource中的name属性也就是数据库表IA_DATA_SOURCE中的c_name字段
     *  DbContextHolder.setDBType("dataSourceName");
     */
}

类三 Constants.java

package com.rongtai.acs.core.utils;

public class Constants {
    public static String DEFAULT_DATA_SOURCE_NAME="dataSource";
    public static String DataSourceType="";
}

类四实体类 GsoftDataSource.java

package com.rongtai.acs.core.utils;


import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;


@Entity
@Table(name = "IA_DATA_SOURCE")
public class GsoftDataSource {
    @Id
//    @SequenceGenerator(name = "IA_DATA_SOURCE_SEQ", sequenceName = "IA_DATA_SOURCE_SEQ", allocationSize = 1)
//    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "IA_DATA_SOURCE_SEQ")
    private Long id;

    /**
     * data source name
     */
    @Column(name = "C_NAME", unique = true)
    private String name;

    /**
     * data source type, default is database<br />
     */
    @Column(name = "C_TYPE")
    private Integer type = DataSourceType.DB.intValue();

    /**
     * 数据库类型，目前只支持MySql和Oracle<br />
     */
    @Column(name = "C_DATA_TYPE")
    private Integer dataType = DataType.ORACLE.intValue();

    @Column(name = "C_URL")
    private String url;

    @Column(name = "C_USER_NAME")
    private String userName;

    @Column(name = "C_PASSWORD")
    private String password;

    @Column(name = "C_JNDI_NAME")
    private String jndiName;

    @Column(name = "C_DRIVER_CLASS_NAME")
    private String driverClassName;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Integer getType() {
        return type;
    }

    public void setType(Integer type) {
        this.type = type;
    }

    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;
    }

    public String getJndiName() {
        return jndiName;
    }

    public void setJndiName(String jndiName) {
        this.jndiName = jndiName;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDriverClassName() {
        return driverClassName;
    }

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

    public Integer getDataType() {
        return dataType;
    }

    public void setDataType(Integer dataType) {
        this.dataType = dataType;
    }
    public enum DataType {
        ORACLE(0),
        MYSQL(1);
        private Integer value;
        public Integer intValue() {
            return this.value;
        }
        DataType(Integer value) {
            this.value = value;
        }
    }
    public enum DataSourceType {
        DB(0),
        ss(1);
        private Integer value;
        DataSourceType(Integer value) {
            this.value = value;
        }

        public Integer intValue() {
        return this.value;
        }
    }
}

实体类需要建对应的数据库表,由于我只用到了mysql，只能只说它了，sql语句如下:

CREATE TABLE `ia_data_source` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `c_data_type` INT(11) NOT NULL,
  `c_driver_class_name` VARCHAR(255) DEFAULT NULL,
  `c_jndi_name` VARCHAR(255) DEFAULT NULL,
  `c_name` VARCHAR(255) DEFAULT NULL,
  `c_password` VARCHAR(255) DEFAULT NULL,
  `c_type` INT(11) NOT NULL,
  `c_url` VARCHAR(255) DEFAULT NULL,
  `c_user_name` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_bo3uh3stkpnq52ffugvt3934r` (`c_name`)
) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

表数据截下图

切换数据源语句，只需在service对数据进行增删改查时加上以下语句

DbContextHolder.setDBType("IA_DATA_SOURCE中某个数据源的name属性的值");

我的是

分析：原来文章的bug是不能加载加入的这些文件，原因是在Jpa Entity Manager 配置的地方，DataSource没有用配置好的数据源，这是必要的；另外是缺少Constants.java类，害的我揣摩了很久。

需要注意的是包名如果更改的话，记得配置文件中也要把对应的Class类路径做下更改。

本来要实现的是在用户登录时根据不同的用户类型，选择不同的数据源，还未实现。先实现了这个demo，等完成时，再贴代码。

欢迎前来交流 ^_^ !

Spring动态加载、编辑数据源 用于多数据源

推荐阅读

Spring动态加载、编辑数据源用于多数据源