程序无需重启
可以加个过滤器支持未设置数据库无法调用
将配置存到property文件,也可存到缓存
设置时需要一个工具类判断传入的数据库是否可连接等
import org.apache.commons.configuration.PropertiesConfiguration;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLNonTransientConnectionException;
/**
* 功能:定义动态数据源派生类。从基础的DataSource派生,动态性自己实现。
* 作者:
* 日期:2018-10-13
*/
public class DynamicDataSource extends DataSource {
protected LoggerUtil logger = LogFactory.getLogger(getClass());
/**
* 改写本方法是为了在请求不同工程的数据时去连接不同的数据库。
*/
@Override
public Connection getConnection() {
logger.error("DynamicDataSource getConnection");
String ip = DBIdentifier.getIp();
if (StringUtils.isBlank(ip)) {
PropertiesConfiguration conf = null;
try {
conf = new PropertiesConfiguration("application-test.properties");
ip = String.valueOf(conf.getString("spring.datasource.write.ip"));
} catch (org.apache.commons.configuration.ConfigurationException e) {
e.printStackTrace();
}
}
if (org.apache.commons.lang.StringUtils.length(ip) < 8) {
return null;
}
//1、获取数据源
DataSource dds = DDSHolder.instance().getDDS(ip);
//2、如果数据源不存在则创建
if (dds == null) {
try {
DataSource newDDS = initDDS(ip);
DDSHolder.instance().addDDS(ip, newDDS);
} catch (IllegalArgumentException | IllegalAccessException e) {
logger.error("Init data source fail. projectCode:" + ip);
return null;
}
}
dds = DDSHolder.instance().getDDS(ip);
Connection connection = null;
try {
connection = dds.getConnection();
return connection;
} catch (SQLNonTransientConnectionException Exception) {
return null;
} catch (SQLException e) {
return null;
} catch (Exception e) {
return null;
}
}
/**
* 以当前数据对象作为模板复制一份。
*
* @return dds
* @throws IllegalAccessException
* @throws IllegalArgumentException
*/
private DataSource initDDS(String ip) throws IllegalArgumentException, IllegalAccessException {
logger.error("DynamicDataSource initDDS ip:" + ip);
DataSource dds = new DataSource();
// 2、复制PoolConfiguration的属性
PoolProperties property = new PoolProperties();
Field[] pfields = PoolProperties.class.getDeclaredFields();
for (Field f : pfields) {
f.setAccessible(true);
Object value = f.get(this.getPoolProperties());
try {
f.set(property, value);
} catch (Exception e) {
//有一些static final的属性不能修改。忽略。
logger.info("Set value fail. attr name:" + f.getName());
continue;
}
}
dds.setPoolProperties(property);
// 3、设置数据库名称和IP(一般来说,端口和用户名、密码都是统一固定的)
String url = "jdbc:mysql://" + ip + ":3306/test?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&useSSL=true&serverTimezone=Asia/Shanghai";
dds.setUrl(url);
return dds;
}
}
import com.github.pagehelper.PageHelper;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.TransactionManagementConfigurer;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
/**
* 作用:数据源配置类
*/
@Configuration
@EnableTransactionManagement
public class DataSourceConfig implements TransactionManagementConfigurer {
@Value("${spring.datasource.type}")
private Class<? extends DataSource> dataSourceType;
@Value("${datasouce.aliases}")
private String typeAliasesPackage;
/**
* 主数据库
* @return
*/
@Primary
@Bean(name = "writeDataSource")
@ConfigurationProperties(prefix = "spring.datasource.write")
public DataSource writeDataSource() {
return DataSourceBuilder.create().type(dataSourceType).build();
}
@Bean(name="dataSource")
@ConfigurationProperties(prefix="spring.datasource.write")
public DataSource getDataSource() {
DataSourceBuilder builder = DataSourceBuilder.create();
builder.type(DynamicDataSource.class);
return builder.build();
}
/**
* 有多少个从库就要配置多少个,当前只有一个
* @return
*/
// @Bean(name = "readDataSource")
// @ConfigurationProperties(prefix = "spring.datasource.read")
// public DataSource readDataSourceOne() {
// return DataSourceBuilder.create().type(dataSourceType).build();
// }
/**
* 将多个数据源放入数据源路由集合中
* @return
*/
@Bean("routingDataSource")
public RoutingDataSource dataSourceProxy(){
RoutingDataSource proxy = new RoutingDataSource();
Map<Object,Object> dataSourceMap = new HashMap<>();
dataSourceMap.put(TargetDataSource.DYNIC.getCode(),getDataSource());
dataSourceMap.put(TargetDataSource.WRITE.getCode(),writeDataSource());
// dataSourceMap.put(TargetDataSource.READ.getCode(),readDataSourceOne());
proxy.setDefaultTargetDataSource(dataSourceMap.get(TargetDataSource.WRITE.getCode()));
//proxy.setDefaultTargetDataSource(dataSourceMap.get(TargetDataSource.DYNIC.getCode()));
proxy.setTargetDataSources(dataSourceMap);
return proxy;
}
/**
* 初始化SqlSessionFactory,设置扫描的mapper路径和entity包路径
* @return
*/
@Bean
public SqlSessionFactory sqlSessionFactory() {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSourceProxy());
bean.setTypeAliasesPackage(typeAliasesPackage);
bean.setVfs(SpringBootVFS.class);
/**
* 设置分页插件
*/
PageHelper pageHelper = new PageHelper();
Properties properties = new Properties();
properties.setProperty("offsetAsPageNum", "true");
properties.setProperty("rowBoundsWithCount", "true");
properties.setProperty("reasonable", "true");
pageHelper.setProperties(properties);
bean.setPlugins(new Interceptor[] {pageHelper});
//添加XML目录
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
try {
bean.setMapperLocations(resolver.getResources("classpath:/mapper/**/*.xml"));
return bean.getObject();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* 事务配置,考虑多数据源情况下
* @return
*/
@Bean
public PlatformTransactionManager txManager() {
return new DataSourceTransactionManager(dataSourceProxy());
}
@Override
public PlatformTransactionManager annotationDrivenTransactionManager() {
return txManager();
}
}
package com.mwkj.platform.cloud.application;
import com.mwkj.common.log.LogFactory;
import com.mwkj.common.log.LoggerUtil;
import com.mwkj.common.resp.ResponseMessage;
import com.mwkj.common.resp.ResponseMessageCodeEnum;
import com.mwkj.common.service.dao.dds.DBIdentifier;
import com.mwkj.common.service.dao.ds.DataSourceContextHolder;
import com.mwkj.common.util.JsonUtils;
import org.apache.catalina.connector.Request;
import org.apache.catalina.connector.RequestFacade;
import org.apache.commons.configuration.PropertiesConfiguration;
import org.apache.commons.lang3.StringUtils;
import org.apache.tomcat.util.buf.MessageBytes;
import org.springframework.http.HttpStatus;
import javax.servlet.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
/**
* 功能:在SpringBoot中通过注解注册的方式简单的使用Filter
* 日期:2018-10-13
*/
public class FileterController implements Filter {
private static final LoggerUtil logger = LogFactory.getLogger(FileterController.class);
@Override
public void init(FilterConfig filterConfig) throws ServletException {
logger.info("Filter初始化中");
}
@Override
public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
logger.info("Filter开始进行过滤处理");
DataSourceContextHolder.dynic();
HttpServletRequest httpServletRequest = (HttpServletRequest) servletRequest;
if (StringUtils.equals("/api/http/v1/users", httpServletRequest.getRequestURI()) == false) {
String ip = "";
PropertiesConfiguration conf = null;
try {
conf = new PropertiesConfiguration("application-test.properties");
ip = String.valueOf(conf.getString("spring.datasource.write.ip"));
} catch (org.apache.commons.configuration.ConfigurationException e) {
e.printStackTrace();
}
if (org.apache.commons.lang.StringUtils.length(ip) < 8) {
HttpServletResponse response = (HttpServletResponse) servletResponse;
((HttpServletResponse) response).setStatus(HttpStatus.UNAUTHORIZED.value());
response.setContentType("application/json; charset=utf-8");
String json= null;
try {
json = JsonUtils.writeValueAsString(ResponseMessage.failResponse("数据库未配置"));
} catch (Exception e) {
}
response.getWriter().print(json);
return;
}
}
//调用该方法后,表示过滤器经过原来的url请求处理方法
filterChain.doFilter(servletRequest, servletResponse);
}
@Override
public void destroy() {
logger.info("Filter销毁中");
}
}
@RequestMapping(value = "/v1/users", method = RequestMethod.GET)
public ResponseMessage queryUser(@RequestParam(value = "ip", required = true) String ip) {
DataSourceConfig dataSourceConfig =new DataSourceConfig();
dataSourceConfig.setDriverClass("com.mysql.jdbc.Driver");
dataSourceConfig.setDbName("test");
dataSourceConfig.setUsername("");
dataSourceConfig.setPassword("");
dataSourceConfig.setPort(3306);
dataSourceConfig.setIp(ip);
String connectInfo = DBConnect.testConnection(dataSourceConfig);
if (org.springframework.util.StringUtils.hasText(connectInfo)) {
return ResponseMessage.failResponse("无法连接到" + ip + "上面的数据库");
}
DBIdentifier.setIp(ip);
PropertiesConfiguration conf = null;
try {
conf = new PropertiesConfiguration("application-test.properties");
conf.setProperty("spring.datasource.write.ip", ip);
conf.save();
} catch (ConfigurationException e) {
e.printStackTrace();
}
ResponseMessage resp = parkFreeService.findAllList();
return ResponseMessage.successResponse(resp);
}
package com.mwkj.common.service.util.db;
import java.util.HashMap;
import java.util.Map;
/**
* 日期:2018-10-13
*/
public class DataBaseConfig {
private static Map<String, String> jdbcUrlMap = new HashMap<String,String>();
static {
// com.mysql.jdbc.Driver
// net.sourceforge.jtds.jdbc.Driver
// com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbcUrlMap.put("com.mysql.jdbc.Driver", "jdbc:mysql://%s:%s/%s?useUnicode=true&characterEncoding=UTF-8");
jdbcUrlMap.put("net.sourceforge.jtds.jdbc.Driver", "jdbc:jtds:sqlserver://%s:%s;databaseName=%s");
}
private String dbName;
private String driverClass;
private String ip;
private int port;
private String username;
private String password;
public String getJdbcUrl() {
String url = jdbcUrlMap.get(driverClass);
return String.format(url, ip,port,dbName);
}
public String getDbName() {
return dbName;
}
public void setDbName(String dbName) {
this.dbName = dbName;
}
public String getDriverClass() {
return driverClass;
}
public void setDriverClass(String driverClass) {
this.driverClass = driverClass;
}
public String getIp() {
return ip;
}
public void setIp(String ip) {
this.ip = ip;
}
public int getPort() {
return port;
}
public void setPort(int port) {
this.port = port;
}
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;
}
}
package com.mwkj.common.service.util.db;
/**
* 功能:
* 日期:2018-10-13
*/
public class DataSourceConfig extends DataBaseConfig {
private int dcId;
private String backUser;
public int getDcId() {
return dcId;
}
public void setDcId(int dcId) {
this.dcId = dcId;
}
public String getBackUser() {
return backUser;
}
public void setBackUser(String backUser) {
this.backUser = backUser;
}
}
package com.mwkj.common.service.util.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* 功能:
* 日期:2018-10-13
*/
public class DBConnect {
public static Connection getConnection(DataSourceConfig config) throws ClassNotFoundException, SQLException
{
Class.forName(config.getDriverClass());
return DriverManager.getConnection(config.getJdbcUrl(),
config.getUsername(), config.getPassword());
}
/**
* 测试连接,返回错误信息,无返回内容说明连接成功
*
* @param dataSourceConfig
* @return 返回错误信息,无返回内容说明连接成功
*/
public static String testConnection(DataSourceConfig dataSourceConfig) {
Connection con = null;
String ret = null;
try {
con = DBConnect.getConnection(dataSourceConfig);
// 不为空说明连接成功
if (con == null) {
ret = dataSourceConfig.getDbName() + "连接失败";
}
} catch (ClassNotFoundException e) {
ret = dataSourceConfig.getDbName() + "连接失败" + "<br>错误信息:"
+ "找不到驱动" + dataSourceConfig.getDriverClass();
} catch (SQLException e) {
ret = dataSourceConfig.getDbName() + "连接失败" + "<br>错误信息:"
+ e.getMessage();
} finally {
if (con != null) {
try {
con.close(); // 关闭连接,该连接无实际用处
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return ret;
}
}
public enum TargetDataSource {
WRITE("write", "主库"), READ("read", "从库"), DYNIC("dynic", "动态");
final private String code;
final private String name;
TargetDataSource(String _code, String _name) {
this.code = _code;
this.name = _name;
}
public String getCode() {
return code;
}
public String getName() {
return name;
}
public static String getNameByCode(String _code) {
for (TargetDataSource item : TargetDataSource.values()) {
if (item.getCode().equals(_code)) {
return item.getName();
}
}
return "";
}
}
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* 时间:2017-11-30
* 作用:获取数据源名称,最终根据这个名称选择数据源
*/
public class RoutingDataSource extends AbstractRoutingDataSource {
/**
* 获取数据源
* @return
*/
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getTargetDataSource();
}
}
@Configuration
public class MyBatisMapperScannerConfig {
@Bean
public MapperScannerConfigurer mapperScannerConfigurer() {
MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
mapperScannerConfigurer.setBasePackage("com.mwkj.**.dao");
mapperScannerConfigurer.setAnnotationClass(MyBatisRepository.class);
return mapperScannerConfigurer;
}
}
public class DataSourceContextHolder {
private static final ThreadLocal<String> dataSourceLocal = new ThreadLocal<String>();
public static ThreadLocal<String> getDataSourceLocal() {
return dataSourceLocal;
}
/**
* 从库当前只有一个后面可以增加
*/
public static void read() {
dataSourceLocal.set(TargetDataSource.READ.getCode());
}
/**
* 主库只有一个
*/
public static void write() {
dataSourceLocal.set(TargetDataSource.WRITE.getCode());
}
/**
* 主库只有一个
*/
public static void dynic() {
dataSourceLocal.set(TargetDataSource.DYNIC.getCode());
}
public static void setTarget(String key) {
dataSourceLocal.set(key);
}
/**
* 获取当前的数据源名称
* @return
*/
public static String getTargetDataSource() {
return dataSourceLocal.get();
}
public static void clearDataSourceKey(){
dataSourceLocal.remove();
}
}
public class DBIdentifier {
/**
* 用不同的工程编码来区分数据库
*/
private static ThreadLocal<String> ips = new ThreadLocal<String>();
public static String getIp() {
return ips.get();
}
public static void setIp(String ip) {
ips.set(ip);
}
}
/**
* 功能:动态数据源管理器。
* 日期:2018-10-13
*/
public class DDSHolder {
/**
* 管理动态数据源列表。<工程编码,数据源>
*/
private Map<String, DDSTimer> ddsMap = new HashMap<String, DDSTimer>();
/**
* 通过定时任务周期性清除不使用的数据源
*/
private static Timer clearIdleTask = new Timer();
static {
clearIdleTask.schedule(new ClearIdleTimerTask(), 5000, 60 * 1000);
};
private DDSHolder() {
}
/*
* 获取单例对象
*/
public static DDSHolder instance() {
return DDSHolderBuilder.instance;
}
/**
* 添加动态数据源。
*
* @param projectCode 项目编码
* @param dds dds
*/
public synchronized void addDDS(String projectCode, DataSource dds) {
DDSTimer ddst = new DDSTimer(dds);
ddsMap.put(projectCode, ddst);
}
/**
* 查询动态数据源
*
* @param projectCode 项目编码
* @return dds
*/
public synchronized DataSource getDDS(String projectCode) {
if (ddsMap.containsKey(projectCode)) {
DDSTimer ddst = ddsMap.get(projectCode);
ddst.refreshTime();
return ddst.getDds();
}
return null;
}
/**
* 清除超时无人使用的数据源。
*/
public synchronized void clearIdleDDS() {
Iterator<Entry<String, DDSTimer>> iter = ddsMap.entrySet().iterator();
for (; iter.hasNext(); ) {
Entry<String, DDSTimer> entry = iter.next();
if (entry.getValue().checkAndClose())
{
iter.remove();
}
}
}
/**
* 单例构件类
* @author elon
* @version 2018年2月26日
*/
private static class DDSHolderBuilder {
private static DDSHolder instance = new DDSHolder();
}
}
package com.mwkj.common.service.dao.dds;
import org.apache.tomcat.jdbc.pool.DataSource;
/**
* 功能:动态数据源定时器管理。长时间无访问的数据库连接关闭。
* 日期:2018-10-13
*/
public class DDSTimer {
/**
* 空闲时间周期。超过这个时长没有访问的数据库连接将被释放。默认为10分钟。
*/
private static long idlePeriodTime = 10 * 60 * 1000;
/**
* 动态数据源
*/
private DataSource dds;
/**
* 上一次访问的时间
*/
private long lastUseTime;
public DDSTimer(DataSource dds) {
this.dds = dds;
this.lastUseTime = System.currentTimeMillis();
}
/**
* 更新最近访问时间
*/
public void refreshTime() {
lastUseTime = System.currentTimeMillis();
}
/**
* 检测数据连接是否超时关闭。
*
* @return true-已超时关闭; false-未超时
*/
public boolean checkAndClose() {
if (System.currentTimeMillis() - lastUseTime > idlePeriodTime)
{
dds.close();
return true;
}
return false;
}
public DataSource getDds() {
return dds;
}
}
package com.mwkj.common.service.dao.dds;
import java.util.TimerTask;
/**
* 功能:清除空闲连接任务。
* 日期:2018-10-13
*/
public class ClearIdleTimerTask extends TimerTask {
@Override
public void run() {
DDSHolder.instance().clearIdleDDS();
}
}
/**
* Copyright © 2012-2013 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
*
* Licensed under the Apache License, Version 2.0 (the "License");
*/
package com.mwkj.common.service.util.context;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.DisposableBean;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.context.annotation.Lazy;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.stereotype.Service;
import java.io.IOException;
/**
* 以静态变量保存Spring ApplicationContext, 可在任何代码任何地方任何时候取出ApplicaitonContext.
*
* @date 2013-5-29 下午1:25:40
*/
@Service
@Lazy(false)
public class ContextUtils implements ApplicationContextAware, DisposableBean {
private static ApplicationContext applicationContext = null;
private static Logger logger = LoggerFactory.getLogger(ContextUtils.class);
/**
* 取得存储在静态变量中的ApplicationContext.
*/
public static ApplicationContext getApplicationContext() {
return applicationContext;
}
public static String getRootRealPath() {
String rootRealPath = "";
try {
rootRealPath = getApplicationContext().getResource("").getFile().getAbsolutePath();
} catch (IOException e) {
logger.warn("获取系统根目录失败");
}
return rootRealPath;
}
public static String getResourceRootRealPath() {
String rootRealPath = "";
try {
rootRealPath = new DefaultResourceLoader().getResource("").getFile().getAbsolutePath();
} catch (IOException e) {
logger.warn("获取资源根目录失败");
}
return rootRealPath;
}
/**
* 从静态变量applicationContext中取得Bean, 自动转型为所赋值对象的类型.
*/
@SuppressWarnings("unchecked")
public static <T> T getBean(String name) {
return (T) applicationContext.getBean(name);
}
/**
* 从静态变量applicationContext中取得Bean, 自动转型为所赋值对象的类型.
*/
public static <T> T getBean(Class<T> requiredType) {
return applicationContext.getBean(requiredType);
}
/**
* 清除SpringContextHolder中的ApplicationContext为Null.
*/
public static void clearHolder() {
if (logger.isDebugEnabled()) {
logger.debug("清除SpringContextHolder中的ApplicationContext:" + applicationContext);
}
applicationContext = null;
}
/**
* 实现ApplicationContextAware接口, 注入Context到静态变量中.
*/
@Override
public void setApplicationContext(ApplicationContext applicationContext) {
if (ContextUtils.applicationContext != null) {
logger.info("SpringContextHolder中的ApplicationContext被覆盖, 原有ApplicationContext为:"
+ ContextUtils.applicationContext);
}
ContextUtils.applicationContext = applicationContext;
}
@Override
public void destroy() throws Exception {
ContextUtils.clearHolder();
}
}