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