SpringBoot series -- Mysql, Sqlserver dual data source configuration

In the recent project development, Mysql and Sqlserverl databases are needed, that is, the configuration of dual data sources is needed. After reading it on the Internet, most of them are cumbersome and not clear enough. Today, I'd like to share a simple and efficient scheme for configuring dual data sources in spring boot. The project structure is as follows:

application.properties configuration file

spring.datasource.mysql.username=root
spring.datasource.mysql.password=123456
spring.datasource.mysql.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.mysql.url=jdbc:mysql://localhost:3306/test

spring.datasource.sqlserver.username=root
spring.datasource.sqlserver.password=123456
spring.datasource.sqlserver.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.sqlserver.url=jdbc:sqlserver://localhost:1433;DatabaseName=test

Connection pool configuration

package com.tcwong.demo.config;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {

	[@Primary](https://my.oschina.net/primary)
	[@Bean](https://my.oschina.net/bean)
	@ConditionalOnProperty(prefix = "spring.datasource.mysql")
	public DataSource mysqlDataSource() {
		return DruidDataSourceBuilder.create().build();
	}

	[@Bean](https://my.oschina.net/bean)
	@ConditionalOnProperty(prefix = "spring.datasource.sqlserver")
	public DataSource sqlserverDataSource() {
		return DruidDataSourceBuilder.create().build();
	}

}

perhaps

package com.tcwong.demo.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig1 {

	@Value("${spring.datasource.mysql.username}")
	private String mysqlUserName;
	@Value("${spring.datasource.mysql.password}")
	private String mysqlPassword;
	@Value("${spring.datasource.mysql.url}")
	private String mysqlUrl;
	@Value("${spring.datasource.mysql.driver-class-name}")
	private String mysqlDriverClass;

	@Value("${spring.datasource.sqlserver.username}")
	private String sqlserverPassword;
	@Value("${spring.datasource.sqlserver.password}")
	private String sqlserverUserName;
	@Value("${spring.datasource.sqlserver.url}")
	private String sqlserverUrl;
	@Value("${spring.datasource.sqlserver.driver-class-name}")
	private String sqlserverDriverClass;

	@Primary
	@Bean
	public DataSource mysqlDataSource() {
		DruidDataSource druidDataSource = new DruidDataSource();
		druidDataSource.setUsername(mysqlUserName);
		druidDataSource.setPassword(mysqlPassword);
		druidDataSource.setUrl(mysqlUrl);
		druidDataSource.setDriverClassName(mysqlDriverClass);
		return druidDataSource;
	}

	@Bean
	public DataSource sqlserverDataSource() {
		DruidDataSource druidDataSource = new DruidDataSource();
		druidDataSource.setUsername(sqlserverUserName);
		druidDataSource.setPassword(sqlserverPassword);
		druidDataSource.setUrl(sqlserverUrl);
		druidDataSource.setDriverClassName(sqlserverDriverClass);
		return druidDataSource;
	}

}

MyBatis configuration

Mysql configuration

package com.tcwong.demo.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.annotation.Resource;
import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.tcwong.demo.dao.mysql"
		,sqlSessionFactoryRef = "mysqlSqlSessionFactory",sqlSessionTemplateRef = "mysqlSqlSessionTemplate")
public class MysqlMapperConfig {

	@Resource
	private DataSource mysqlDataSource;

	@Primary
	@Bean
	SqlSessionFactory mysqlSqlSessionFactory() {
		SqlSessionFactory sqlSessionFactory = null;
		try {
			SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
			sqlSessionFactoryBean.setDataSource(mysqlDataSource);
			sqlSessionFactoryBean.setTypeAliasesPackage("com.tcwong.demo.bean");
			sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
					.getResource("classpath*:mapper/**/*.xml"));
			sqlSessionFactory = sqlSessionFactoryBean.getObject();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return sqlSessionFactory;
	}

	@Primary
	@Bean
	SqlSessionTemplate mysqlSqlSessionTemplate() {
		return new SqlSessionTemplate(mysqlSqlSessionFactory());
	}
}

Sqlserver configuration

package com.tcwong.demo.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.annotation.Resource;
import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.tcwong.demo.dao.sqlserver"
		,sqlSessionFactoryRef = "sqlserverSqlSessionFactory", sqlSessionTemplateRef = "sqlserverSqlSessionTemplate")
public class sqlserverMapperConfig {

	@Resource
	private DataSource sqlserverDataSource;

	@Bean
	SqlSessionFactory sqlserverSqlSessionFactory() {
		SqlSessionFactory sqlSessionFactory = null;
		try {
			SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
			sqlSessionFactoryBean.setDataSource(sqlserverDataSource);
			sqlSessionFactoryBean.setTypeAliasesPackage("com.tcwong.demo.bean");
			sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
					.getResource("classpath*:mapper/**/*.xml"));
			sqlSessionFactory = sqlSessionFactoryBean.getObject();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return sqlSessionFactory;
	}

	@Bean
	SqlSessionTemplate sqlserverSqlSessionTemplate() {
		return new SqlSessionTemplate(sqlserverSqlSessionFactory());
	}
}

The file path of xml and the JavaBean path of database mapping are specified here. Put the Mapper corresponding to Mysql and Sqlserver in the corresponding Dao.

Tags: Programming Spring MySQL Mybatis JDBC

Posted on Sat, 11 Apr 2020 07:46:59 -0700 by grudz