mybatis mysql merge_Spring Boot + Mybatis 整合Mysql ，SQLServer数据源以及整合druid，动态调整数据源切换。...

pom.xml依赖

org.springframework.boot

spring-boot-starter-web

org.mybatis.spring.boot

mybatis-spring-boot-starter

1.3.2

mysql

mysql-connector-java

runtime

org.springframework.boot

spring-boot-starter-test

test

com.microsoft.sqlserver

sqljdbc4

4.0

org.springframework.boot

spring-boot-starter-aop

配置文件application.yml

datasource:

mysql:

type: com.alibaba.druid.pool.DruidDataSource

driverClassName: com.mysql.jdbc.Driver

url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&useSSL=false&characterEncoding=utf8

username: root

password: root

initialSize: 1

minIdle: 3

maxActive: 20

maxWait: 60000

timeBetweenEvictionRunsMillis: 60000

minEvictableIdleTimeMillis: 30000

validationQuery: select 'x'

testWhileIdle: true

testOnBorrow: false

testOnReturn: false

poolPreparedStatements: true

maxPoolPreparedStatementPerConnectionSize: 20

# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙

filters: stat,wall,slf4j

# 通过connectProperties属性来打开mergeSql功能；慢SQL记录

connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

# 合并多个DruidDataSource的监控数据

#useGlobalDataSourceStat: true

sqlserver:

url: jdbc:sqlserver://127.0.0.1:1433;databasename=test

driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver

username: lc0019999

password: Xtgly2018

创建一个数据源类型枚举类

public enum DataSourceType {

Mysql("mysql"),

SQLServer("sqlserver");

private String name;

DataSourceType(String name) {

this.name = name;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

}

数据上下文

public class JdbcContextHolder {

private final static ThreadLocal local = new ThreadLocal<>();

public static void putDataSource(String name){

local.set(name);

}

public static String getDataSource(){

return local.get();

}

}

AOP数据源切换

@Aspect

@Order(2)

@Component

public class DataSourceAspect {

private Logger logger = LoggerFactory.getLogger(this.getClass());

//切点

@Pointcut("execution(* com.test.*.service..*(..)))")

public void aspect(){

System.out.println("aspect");

}

@Before("aspect()")

private void before(JoinPoint joinPoint){

Object target = joinPoint.getTarget();

String method = joinPoint.getSignature().getName();

Class> classz = target.getClass();

Class>[] parameterTypes = ((MethodSignature) joinPoint.getSignature()).getMethod().getParameterTypes();

try {

Method m = classz.getMethod(method,parameterTypes);

if (m != null && m.isAnnotationPresent(MyDataSource.class)){

MyDataSource data = m.getAnnotation(MyDataSource.class);

JdbcContextHolder.putDataSource(data.value().getName());

logger.info("===============上下文赋值完成:{}"+data.value().getName());

}

}catch (Exception e){

e.printStackTrace();

}

}

}

自定义注解mydatasource

@Retention(RetentionPolicy.RUNTIME)

@Target({ElementType.METHOD})

public @interface MyDataSource {

DataSourceType value() default DataSourceType.Mysql;

}

数据库配置

@SuppressWarnings("AlibabaRemoveCommentedCode")

@Configuration

public class DataSourceConfig {

private Logger logger = LoggerFactory.getLogger(this.getClass());

@Value("${datasource.mysql.url}")

private String dbUrl;

@Value("${datasource.mysql.username}")

private String username;

@Value("${datasource.mysql.password}")

private String password;

@Value("${datasource.mysql.driverClassName}")

private String driverClassName;

@Value("${datasource.mysql.initialSize}")

private int initialSize;

@Value("${datasource.mysql.minIdle}")

private int minIdle;

@Value("${datasource.mysql.maxActive}")

private int maxActive;

@Value("${datasource.mysql.maxWait}")

private int maxWait;

@Value("${datasource.mysql.timeBetweenEvictionRunsMillis}")

private int timeBetweenEvictionRunsMillis;

@Value("${datasource.mysql.minEvictableIdleTimeMillis}")

private int minEvictableIdleTimeMillis;

@Value("${datasource.mysql.validationQuery}")

private String validationQuery;

@Value("${datasource.mysql.testWhileIdle}")

private boolean testWhileIdle;

@Value("${datasource.mysql.testOnBorrow}")

private boolean testOnBorrow;

@Value("${datasource.mysql.testOnReturn}")

private boolean testOnReturn;

@Value("${datasource.mysql.poolPreparedStatements}")

private boolean poolPreparedStatements;

@Value("${datasource.mysql.maxPoolPreparedStatementPerConnectionSize}")

private int maxPoolPreparedStatementPerConnectionSize;

@Value("${datasource.mysql.filters}")

private String filters;

@Value("{datasource.mysql.connectionProperties}")

private String connectionProperties;

@Bean(name="mysql")

@ConfigurationProperties(prefix = "datasource.mysql")

public DataSource dataSourceMysql(){

System.out.println("主配"+dbUrl);

DruidDataSource datasource = new DruidDataSource();

datasource.setUrl(dbUrl);

datasource.setUsername(username);

datasource.setPassword(password);

datasource.setDriverClassName(driverClassName);

datasource.setInitialSize(initialSize);

datasource.setMinIdle(minIdle);

datasource.setMaxActive(maxActive);

datasource.setMaxWait(maxWait);

datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);

datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);

datasource.setValidationQuery(validationQuery);

datasource.setTestWhileIdle(testWhileIdle);

datasource.setTestOnBorrow(testOnBorrow);

datasource.setTestOnReturn(testOnReturn);

datasource.setPoolPreparedStatements(poolPreparedStatements);

datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);

try {

datasource.setFilters(filters);

} catch (SQLException e) {

logger.error("druid configuration initialization filter", e);

}

datasource.setConnectionProperties(connectionProperties);

return datasource;

}

@Value("${datasource.sqlserver.url}")

private String sqlServerUrl;

@Value("${datasource.sqlserver.username}")

private String sqlServerUsername;

@Value("${datasource.sqlserver.password}")

private String sqlServerPassword;

@Value("${datasource.sqlserver.driverClassName}")

private String sqlServerDriverClassName;

@Bean(name="sqlserver")

@ConfigurationProperties(prefix = "datasource.sqlserver")

public DataSource dataSourceSQLServer(){

System.out.println("次配"+sqlServerUrl);

DruidDataSource datasource = new DruidDataSource();

datasource.setUrl(sqlServerUrl);

datasource.setUsername(sqlServerUsername);

datasource.setPassword(sqlServerPassword);

datasource.setDriverClassName(sqlServerDriverClassName);

datasource.setInitialSize(initialSize);

return datasource;

}

@Bean(name = "dynamicDataSource")

@Primary //优先使用，多数据源

public DataSource dataSource(){

DynamicDataSource dynamicDataSource = new DynamicDataSource();

DataSource mysql = dataSourceMysql();

DataSource sqlServer = dataSourceSQLServer();

//设置默认数据源

dynamicDataSource.setDefaultTargetDataSource(mysql);

//配置多个数据源

Map map = new HashMap<>();

map.put(DataSourceType.Mysql.getName(),mysql);

map.put(DataSourceType.SQLServer.getName(),sqlServer);

dynamicDataSource.setTargetDataSources(map);

return dynamicDataSource;

}

@Bean(name="druidServlet")

public ServletRegistrationBean druidServlet() {

ServletRegistrationBean reg = new ServletRegistrationBean();

reg.setServlet(new StatViewServlet());

reg.addUrlMappings("/druid/*");

reg.addInitParameter("allow", ""); //白名单

return reg;

}

@Bean(name = "filterRegistrationBean")

public FilterRegistrationBean filterRegistrationBean() {

FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();

filterRegistrationBean.setFilter(new WebStatFilter());

filterRegistrationBean.addUrlPatterns("/*");

filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");

filterRegistrationBean.addInitParameter("profileEnable", "true");

filterRegistrationBean.addInitParameter("principalCookieName","USER_COOKIE");

filterRegistrationBean.addInitParameter("principalSessionName","USER_SESSION");

filterRegistrationBean.addInitParameter("DruidWebStatFilter","/*");

return filterRegistrationBean;

}

}