Spring Boot 中使用 MyBatis 整合 Druid 多資料來源

搜雲庫技術團隊發表於2017-10-20
Spring BootMyBatisUI

本文將講述 spring boot + mybatis + druid 多資料來源配置方案。

環境

CentOs7.3 安裝 MySQL 5.7.19 二進位制版本

Github 程式碼

程式碼我已放到 Github ，匯入spring-boot-mybatis 專案

github github.com/souyunku/sp…

專案結構

新增依賴

在專案中新增 mybatis，druid 依賴

點選預覽 pom.xml

<dependency>
	<groupId>org.mybatis.spring.boot</groupId>
	<artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
</dependency>
省略 更多
複製程式碼

基礎資料來源

@Configuration
@EnableConfigurationProperties(DruidDbProperties.class)
@Import({DruidMonitConfig.class})
public abstract class AbstractDruidDBConfig {

    private Logger logger = LoggerFactory.getLogger(AbstractDruidDBConfig.class);

    @Resource
    private DruidDbProperties druidDbProperties;

    public DruidDataSource createDataSource(String url, String username, String password) {
        if (StringUtils.isEmpty(url)) {
            System.out.println(
                    "Your database connection pool configuration is incorrect!" + " Please check your Spring profile");
            throw new ApplicationContextException("Database connection pool is not configured correctly");
        }

        DruidDataSource datasource = new DruidDataSource();

        datasource.setUrl(url);
        datasource.setUsername(username);
        datasource.setPassword(password);
        // datasource.setDriverClassName(
        // StringUtils.isEmpty(driverClassName) ?
        // druidDbProperties.getDriverClassName() : driverClassName);
        datasource.setInitialSize(druidDbProperties.getInitialSize());
        datasource.setMinIdle(druidDbProperties.getMinIdle());
        datasource.setMaxActive(druidDbProperties.getMaxActive());
        datasource.setMaxWait(druidDbProperties.getMaxWait());
        datasource.setTimeBetweenEvictionRunsMillis(druidDbProperties.getTimeBetweenEvictionRunsMillis());
        datasource.setMinEvictableIdleTimeMillis(druidDbProperties.getMinEvictableIdleTimeMillis());
        datasource.setValidationQuery(druidDbProperties.getValidationQuery());
        datasource.setTestWhileIdle(druidDbProperties.isTestWhileIdle());
        datasource.setTestOnBorrow(druidDbProperties.isTestOnBorrow());
        datasource.setTestOnReturn(druidDbProperties.isTestOnReturn());
        try {
            datasource.setFilters(druidDbProperties.getFilters());
        } catch (SQLException e) {
            logger.error("druid configuration initialization filter", e);
        }
        datasource.setConnectionProperties(druidDbProperties.getConnectionProperties());
        return datasource;

    }

    /**
     * 載入預設mybatis xml配置檔案，並初始化分頁外掛
     *
     * @param dataSource
     * @return
     * @throws Exception
     */
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
        return createSqlSessionFactory(dataSource, "classpath:mybatis/**/*.xml");
    }

    /**
     * 載入mybatis xml配置檔案，並初始化分頁外掛
     *
     * @param dataSource      資料來源
     * @param mapperLocations 自定義xml配置路徑
     * @return
     * @throws Exception
     */
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource, String mapperLocations) throws Exception {
        return createSqlSessionFactory(dataSource, mapperLocations);
    }

    private SqlSessionFactory createSqlSessionFactory(DataSource dataSource, String mapperLocations) throws Exception {

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        // mybatis分頁
        PageHelper pageHelper = new PageHelper();
        Properties props = new Properties();
        props.setProperty("dialect", "mysql");
        props.setProperty("reasonable", "true");
        props.setProperty("supportMethodsArguments", "true");
        props.setProperty("returnPageInfo", "check");
        props.setProperty("params", "count=countSql");
        pageHelper.setProperties(props); // 新增外掛
        sqlSessionFactoryBean.setPlugins(new Interceptor[]{pageHelper});
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sqlSessionFactoryBean.setMapperLocations(resolver.getResources(mapperLocations));
        return sqlSessionFactoryBean.getObject();

    }
}

複製程式碼

Druid 監控配置

@EnableConfigurationProperties(DruidDbProperties.class)
@EnableAspectJAutoProxy(proxyTargetClass = true)
public class DruidMonitConfig {

    @Resource
    private DruidDbProperties druidDbProperties;

    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean reg = new ServletRegistrationBean();
        reg.setServlet(new StatViewServlet());
        reg.addUrlMappings("/druid/*");

        if (!StringUtils.isEmpty(druidDbProperties.getAllow())) {
            reg.addInitParameter("allow", druidDbProperties.getAllow()); // 白名單
        }
        if (!StringUtils.isEmpty(druidDbProperties.getDeny())) {
            reg.addInitParameter("deny", druidDbProperties.getDeny()); // 黑名單
        }
        reg.addInitParameter("loginUsername", druidDbProperties.getUsername());
        reg.addInitParameter("loginPassword", druidDbProperties.getPassword());
        return reg;
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }

    /**
     * 監聽Spring 1.定義攔截器 2.定義切入點 3.定義通知類
     *
     * @return
     */
    @Bean
    public DruidStatInterceptor druidStatInterceptor() {
        return new DruidStatInterceptor();
    }

    @Bean
    public JdkRegexpMethodPointcut druidStatPointcut() {
        JdkRegexpMethodPointcut druidStatPointcut = new JdkRegexpMethodPointcut();
        String patterns = "io.ymq.mybatis*";
        druidStatPointcut.setPatterns(patterns);
        return druidStatPointcut;
    }

    @Bean
    public Advisor druidStatAdvisor() {
        return new DefaultPointcutAdvisor(druidStatPointcut(), druidStatInterceptor());
    }
}
複製程式碼

Druid 監控引數


@ConfigurationProperties(prefix = "druid")
public class DruidDbProperties {

    private String driverClassName = "com.mysql.jdbc.Driver";

    /**
     * 初始化時建立物理連線的個數。初始化發生在顯示呼叫init方法，或者第一次getConnection時
     */
    private int initialSize = 10;

    /**
     * 最小連線池數量
     */
    private int minIdle = 50;

    /**
     * 最大連線池數量
     */
    private int maxActive = 300;

    /**
     * 獲取連線時最大等待時間，單位毫秒。配置了maxWait之後，預設啟用公平鎖，併發效率會有所下降，如果需要可以通過配置useUnfairLock屬性為true使用非公平鎖。
     */
    private int maxWait = 60000;

    /**
     * 有兩個含義： 1)
     * Destroy執行緒會檢測連線的間隔時間，如果連線空閒時間大於等於minEvictableIdleTimeMillis則關閉物理連線。 2)
     * testWhileIdle的判斷依據，詳細看testWhileIdle屬性的說明
     */
    private int timeBetweenEvictionRunsMillis = 60000;

    /**
     * 連線保持空閒而不被驅逐的最長時間
     */
    private int minEvictableIdleTimeMillis = 3600000;

    /**
     * 用來檢測連線是否有效的sql，要求是一個查詢語句，常用select
     * 'x'。如果validationQuery為null，testOnBorrow、testOnReturn、testWhileIdle都不會其作用。
     */
    private String validationQuery = "SELECT USER()";

    /**
     * 建議配置為true，不影響效能，並且保證安全性。申請連線的時候檢測，如果空閒時間大於timeBetweenEvictionRunsMillis，執行validationQuery檢測連線是否有效。
     */
    private boolean testWhileIdle = true;

    /**
     * 申請連線時執行validationQuery檢測連線是否有效，做了這個配置會降低效能。
     */
    private boolean testOnBorrow = false;

    /**
     * 歸還連線時執行validationQuery檢測連線是否有效，做了這個配置會降低效能。
     */
    private boolean testOnReturn = false;

    /**
     * 屬性型別是字串，通過別名的方式配置擴充套件外掛，常用的外掛有： 監控統計用的filter:stat 日誌用的filter:log4j
     * 防禦sql注入的filter:wall
     */
    private String filters = "mergeStat,config,wall";

    private String connectionProperties;

    /**
     * 白名單
     */
    private String allow;

    /**
     * 黑名單
     */
    private String deny;

    private String username = "admin";

    private String password = "admin";
	
	省略 get set
}
複製程式碼

配置資料來源 one

@Configuration
@EnableTransactionManagement
public class DBOneConfiguration extends AbstractDruidDBConfig {

    @Value("${ymq.one.datasource.url}")
    private String url;

    @Value("${ymq.one.datasource.username}")
    private String username;

    @Value("${ymq.one.datasource.password}")
    private String password;

    // 註冊 datasourceOne
    @Bean(name = "datasourceOne", initMethod = "init", destroyMethod = "close")
    public DruidDataSource dataSource() {
        return super.createDataSource(url, username, password);
    }

    @Bean(name = "sqlSessionFactorYmqOne")
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        return super.sqlSessionFactory(dataSource());
    }

    @Bean
    public PlatformTransactionManager transactionManager() throws SQLException {
        return new DataSourceTransactionManager(dataSource());
    }
}
複製程式碼

配置資料來源 two

@Configuration
@EnableTransactionManagement
public class DBOneConfiguration extends AbstractDruidDBConfig {

    @Value("${ymq.one.datasource.url}")
    private String url;

    @Value("${ymq.one.datasource.username}")
    private String username;

    @Value("${ymq.one.datasource.password}")
    private String password;

    // 註冊 datasourceOne
    @Bean(name = "datasourceOne", initMethod = "init", destroyMethod = "close")
    public DruidDataSource dataSource() {
        return super.createDataSource(url, username, password);
    }

    @Bean(name = "sqlSessionFactorYmqOne")
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        return super.sqlSessionFactory(dataSource());
    }

    @Bean
    public PlatformTransactionManager transactionManager() throws SQLException {
        return new DataSourceTransactionManager(dataSource());
    }
}
複製程式碼

BaseDao one

@Repository
public class YmqOneBaseDao extends BaseDao {

    @Resource
    public void setSqlSessionFactorYmqOne(SqlSessionFactory sqlSessionFactory) {
        super.setSqlSessionFactory(sqlSessionFactory);
    }
}
複製程式碼

BaseDao two

@Repository
public class YmqTwoBaseDao extends BaseDao {

    @Resource
    public void setSqlSessionFactorYmqTwo(SqlSessionFactory sqlSessionFactory) {
        super.setSqlSessionFactory(sqlSessionFactory);
    }
}
複製程式碼

測試 Controller

@RestController
public class IndexController {

    private static final Logger LOG = LoggerFactory.getLogger(IndexController.class);

    @Autowired
    private YmqOneBaseDao ymqOneBaseDao;

    @Autowired
    private YmqTwoBaseDao ymqTwoBaseDao;

    @RequestMapping("/")
    public String index() throws Exception {

        List<TestOnePo> testOnePoList = null;

        testOnePoList = ymqOneBaseDao.selectList(new TestOnePo());
        for (TestOnePo item : testOnePoList) {
            LOG.info("資料來源 ymqOneBaseDao ：查詢結果:{}", JSONObject.toJSONString(item));
        }

        List<TestTwoPo> testTwoPoList = null;

        testTwoPoList = ymqTwoBaseDao.selectList(new TestTwoPo());

        for (TestTwoPo item : testTwoPoList) {
            LOG.info("資料來源 ymqTwoBaseDao：查詢結果:{}", JSONObject.toJSONString(item));
        }

        String onePoList = JSONObject.toJSONString(testOnePoList);
        String twoPoList = JSONObject.toJSONString(testTwoPoList);

        return "資料來源 ymqOneBaseDao ：查詢結果:" + onePoList + "<br/> 資料來源 ymqTwoBaseDao ：查詢結果:" + twoPoList;
    }
}
複製程式碼

引數配置

application.properties

#############SERVER CONFIG############
spring.application.name=ymq-mybatis-spring-boot

#資料來源 one
ymq.one.datasource.url=jdbc:mysql://10.4.82.6:3306/ymq_one?useUnicode=true&characterEncoding=UTF-8
ymq.one.datasource.username=root
ymq.one.datasource.password=123456

#資料來源 two
ymq.two.datasource.url=jdbc:mysql://10.4.82.6:3306/ymq_two?useUnicode=true&characterEncoding=UTF-8
ymq.two.datasource.username=root
ymq.two.datasource.password=123456

server.port=80
server.tomcat.max-threads=1000
server.tomcat.max-connections=2000
複製程式碼

啟動服務

@SpringBootApplication
@ComponentScan(value = {"io.ymq.mybatis"})
public class Startup {

    public static void main(String[] args) {
        SpringApplication.run(Startup.class, args);
    }
}
複製程式碼

在頁面上輸入 http://localhost/ 可以看到 Controller 執行情況：

資料來源 ymqOneBaseDao ：查詢結果:[{"id":1,"name":"測試","remark":"這是測試 ymq_one 資料庫"}]
資料來源 ymqTwoBaseDao ：查詢結果:[{"id":1,"name":"測試","remark":"這是測試 ymq_two 資料庫"}]
複製程式碼

在頁面上輸入 http://localhost/druid/ 可以看到監控到的sql語句執行情況：

druid 監控

程式碼我已放到 Github ，匯入spring-boot-mybatis 專案

github github.com/souyunku/sp…

Contact

  • 作者：鵬磊
  • 出處：www.ymq.io
  • Email：admin@souyunku.com
  • 版權歸作者所有，轉載請註明出處
  • Wechat：關注公眾號，搜雲庫，專注於開發技術的研究與知識分享

關注公眾號-搜雲庫
搜雲庫

相關文章