springboot+mybatis+mysql+sql server配置双数据源及druid监控_mybatis 同时 mysql sql server

因为项目整合需要同时使用mysql和sql server两个数据库；经过查询各个博客，终于搞定了，现在分享一下，不足之处还请指教。
配置pom依赖
mysql驱动我选择的这个版本，因为使用默认的，经常会报错，查看资料后，改的这个版本。

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid-spring-boot-starter</artifactId>
	<version>1.1.21</version>
</dependency>

<!-- mysql驱动包 -->
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.0.8</version><!--$NO-MVN-MAN-VER$ -->
</dependency>
<dependency>
			<groupId>com.microsoft.sqlserver</groupId>
			<artifactId>sqljdbc4</artifactId>
			<version>4.0</version>
</dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

yml配置：
关于druid配置，不知道为什么，在yml中配置的filters不生效，索性就只配置了数据库的连接数据，其他的均为默认：

spring:
  datasource1:
    mysql:
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://***.***.***.***:3306/gxrl?useSSL=true&useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE
      username: ***
      password: I***
      type: com.alibaba.druid.pool.DruidDataSource

  datasource2:
    sqlserver:
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      url: jdbc:sqlserver://***.***.0.***:1433;DatabaseName=***
      username: sa
      password: ***
      type: com.alibaba.druid.pool.DruidDataSource
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

然后就是配置数据源代码：
这里一定要注意前缀必须与yml一致；
要在数据源setfilters，不然监控不到sql的运行情况。

/**
 * 配置双数据源
 * @author Administrator
 */
@Configuration
public class DataSourceConfig {

    @Resource
    private DataSourceProperties basicProperties;

    /**
     * mysql数据源
     *
     * @return DataSource
     * application.yml 中对应属性的前缀
     */
    @Bean(name = "dbOneDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource1.mysql")
    public DataSource dbOneDataSource() throws SQLException {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setFilters("stat,wall,slf4j");
        return druidDataSource;
    }

    /**
     * sql server 数据源
     *
     * @return DataSource
     * application.yml 中对应属性的前缀
     */
    @Bean(name = "dbTwoDataSource")
    @ConfigurationProperties(prefix = "spring.datasource2.sqlserver")
    public DataSource dbTwoDataSource() throws SQLException {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setFilters("stat,wall,slf4j");
        return druidDataSource;
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

mybatis配置：
对于加了事务的方法，一定要指定事务管理器
添加transactionManager = "sqlSessionTransactionManagerOne"

/**
 * @author Administrator
 */
public class MybatisConfig {
    /**
     *  mysql数据库 SqlSessionFactory && SqlSessionTemplate 创建
     */
    @Configuration
    @MapperScan(basePackages = {"com.itangde.beatcharge.mapper"},
            sqlSessionFactoryRef = "sqlSessionFactoryOne",
            sqlSessionTemplateRef = "sqlSessionTemplateOne")
    public static class dbOne {
        @Resource
        DataSource dbOneDataSource;

        @Bean(name = "sqlSessionFactoryOne")
        public SqlSessionFactory sqlSessionFactory() throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dbOneDataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapperxml/*.xml"));
            System.out.println("sqlSessionFactoryOne 创建成功。");
            return bean.getObject();
        }

        @Bean(name = "sqlSessionTransactionManagerOne")
        public DataSourceTransactionManager transactionManager(){
            return new DataSourceTransactionManager(dbOneDataSource);
        }

        @Bean(name = "sqlSessionTemplateOne")
        public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactoryOne") SqlSessionFactory sqlSessionFactory) {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }


    /**
     * sql server数据库 SqlSessionFactory && SqlSessionTemplate 创建
     */
    @Configuration
    @MapperScan(basePackages = {"com.itangde.beatcharge.dao"},
            sqlSessionFactoryRef = "sqlSessionFactoryTwo",
            sqlSessionTemplateRef = "sqlSessionTemplateTwo")
    public static class dbTwo {
        @Resource
        DataSource dbTwoDataSource;

        @Bean(name = "sqlSessionFactoryTwo")
        public SqlSessionFactory sqlSessionFactory() throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dbTwoDataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
            System.out.println("sqlSessionFactoryTwo 创建成功。");
            return bean.getObject();
        }

        @Bean(name = "qlSessionTransactionManagerTwo")
        public DataSourceTransactionManager transactionManager(){
            return new DataSourceTransactionManager(dbTwoDataSource);
        }

        @Bean(name = "sqlSessionTemplateTwo")
        public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactoryTwo") SqlSessionFactory sqlSessionFactory) {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

配置druid的监控
initParams.put(“loginUsername”,“admin”);
initParams.put(“loginPassword”,“admin”);
这两行可以注掉，注掉后，登录监控页面就不需要密码账号了。
initParams 不能为空，否则会报错。

import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

/**
 * @author Administrator
 * Druid监控配置
 */
@Configuration
public class DruidMoniterConfig {
    /**
     * 1、配置一个管理后台的Servlet
     */
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String,String> initParams = new HashMap<>();
        initParams.put("loginUsername","admin");
        initParams.put("loginPassword","admin");
        //默认就是允许所有访问
        initParams.put("allow","");
        bean.setInitParameters(initParams);
        return bean;
    }

    /**
     * 2、配置一个web监控的filter
     */
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());
        Map<String,String> initParams = new HashMap<>();
        initParams.put("exclusions","*.js,*.css,/druid/*");
        bean.setInitParameters(initParams);
        bean.setUrlPatterns(Arrays.asList("/*"));
        return  bean;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

spring监控
这个是要配置你的mapper接口地址，不然也监控不到。
期初我配置的是xml文件地址，一直没有数据。

/**
 * spring监控配置
 * @author Administrator
 */
@Configuration
public class SpringDaoMethodAspect {
    @Bean
    public DruidStatInterceptor druidStatInterceptor() {
        return new DruidStatInterceptor();
    }

    /**
     * mapper切点
     * @return JdkRegexpMethodPointcut
     */
    @Bean
    @Scope("prototype")
    public JdkRegexpMethodPointcut druidStatPointcut() {
        JdkRegexpMethodPointcut pointcut = new JdkRegexpMethodPointcut();
        pointcut.setPattern("***.***.***.mapper.*");
        return pointcut;
    }

    @Bean
    public DefaultPointcutAdvisor druidStatAdvisor(DruidStatInterceptor druidStatInterceptor, JdkRegexpMethodPointcut druidStatPointcut) {
        DefaultPointcutAdvisor defaultPointAdvisor = new DefaultPointcutAdvisor();
        defaultPointAdvisor.setPointcut(druidStatPointcut);
        defaultPointAdvisor.setAdvice(druidStatInterceptor);
        return defaultPointAdvisor;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

启动效果如下：
网上也有去掉阿里广告的帖子，可以自己搜一下，配置上。我比较懒，就没有做查如何去广告。