SpringBoot and Mybatis configure multiple data sources to connect to multiple databases

Currently Mybatis is the framework for the industry to operate databases, but in many business scenarios, we need to configure multiple data sources in one project to implement business logic.You can also implement multiple data sources in SpringBoot and write xml files to execute SQL with the Mybatis framework.In SpringBoot, it's easy to configure multiple data sources.

Start with code below:

• Some dependencies need to be added to the pom.xml file

<!-- Spring Boot Mybatis rely on -->
<dependency>
  <groupId>org.mybatis.spring.boot</groupId>
  <artifactId>mybatis-spring-boot-starter</artifactId>
  <version>1.2.0</version>
</dependency>

<!-- MySQL Connection Driven Dependency -->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.39</version>
</dependency>

<!-- Druid Data Connection Pool Dependency -->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
  <version>1.0.18</version>
</dependency>

• application.properties Configuration Two data source configurations

# master data source configuration
master.datasource.url=jdbc:mysql://localhost:3306/springbootdb?useUnicode=true&characterEncoding=utf8
master.datasource.username=root
master.datasource.password=321
master.datasource.driverClassName=com.mysql.jdbc.Driver

# Seconds data source configuration
second.datasource.url=jdbc:mysql://localhost:3306/springbootdb_second?useUnicode=true&characterEncoding=utf8
second.datasource.username=root
second.datasource.password=321
second.datasource.driverClassName=com.mysql.jdbc.Driver

• Data Source Configuration

Code for MasterDataSourceConfig:

@Configuration
// Scan Mapper interfaces and container management
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
 
    // Precise to master directory to isolate from other data sources
    static final String PACKAGE = "org.spring.springboot.dao.master";
    static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml";
 
    @Value("${master.datasource.url}")
    private String url;
 
    @Value("${master.datasource.username}")
    private String user;
 
    @Value("${master.datasource.password}")
    private String password;
 
    @Value("${master.datasource.driverClassName}")
    private String driverClass;
 
    @Bean(name = "masterDataSource")
    @Primary
    public DataSource masterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }
 
    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(masterDataSource());
    }
 
    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MasterDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

The second data source, SecondDataSourceConfig, is configured as follows:

@Configuration
// Scan Mapper interfaces and container management
@MapperScan(basePackages = SecondDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "secondSqlSessionFactory")
public class SecondDataSourceConfig {

    // Precise to the cluster directory to isolate from other data sources
    static final String PACKAGE = "com.springboot.dao.second";
    static final String MAPPER_LOCATION = "classpath:mapper/second/*.xml";

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

    @Value("${second.datasource.username}")
    private String user;

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

    @Value("${second.datasource.driverClassName}")
    private String driverClass;

    @Bean(name = "secondDataSource")
    public DataSource clusterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    @Bean(name = "secondTransactionManager")
    public DataSourceTransactionManager clusterTransactionManager() {
        return new DataSourceTransactionManager(clusterDataSource());
    }

    @Bean(name = "secondSqlSessionFactory")
    public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("secondDataSource") DataSource clusterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(clusterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(SecondDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

Now that the different data source configurations have been completed, all that remains is to write the xml file for Mybatis and the interface for the DAO layer, inject it into the Service layer, and use it directly.

Code for the Service layer:

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDao userDao;
    @Autowired
    private SchoolDao schoolDao;

    public UserVo getUser(Long id) {
        UserVo userVo = userDao.findById(id);
        SchoolVo schoolVo = schoolDao.findByName("tsinghua");
        userVo.setSchoolVo(schoolVo);
        return userVo;
    }

}

The contents of Mybatis's XML file UserDao.xml and ChoolDao.xml:

UserDao.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.springboot.dao.master.UserDao">
	<resultMap id="BaseResultMap" type="com.springboot.vo.UserVo">
		<result column="id" property="id" />
		<result column="user_name" property="userName" />
	</resultMap>

	<sql id="Base_Column_List">
		id, user_name
	</sql>

	<select id="findById" resultMap="BaseResultMap" parameterType="java.lang.Long">
		select
			<include refid="Base_Column_List" />
		from
			user
		where
			id = #{id}
	</select>
</mapper>

SchoolDao.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.springboot.dao.second.SchoolDao">
	<resultMap id="BaseResultMap" type="com.springboot.vo.SchoolVo">
		<result column="id" property="id" />
		<result column="school_name" property="schoolName" />
		<result column="school_describe" property="schoolDescribe" />
	</resultMap>

	<sql id="Base_Column_List">
		id, school_name, school_describe
	</sql>

	<select id="findByName" resultMap="BaseResultMap" parameterType="java.lang.String">
		select
			<include refid="Base_Column_List" />
		from
			school
		where
			school_name = #{schoolName}
	</select>

</mapper>

TestController

@Controller
@RequestMapping(value = "/api/v1/test")
public class TestController {

    @Autowired
    private UserService userService;

    @RequestMapping(value = "/getUser")
    @ResponseBody
    public UserVo getUser(@RequestParam(value = "id") Long id) {
        return userService.getUser(id);
    }

}

Create a database and table SQL:

CREATE DATABASE springbootdb;

CREATE DATABASE springbootdb_second;

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'User Number',
  `user_name` varchar(25) DEFAULT NULL COMMENT 'User Name',
  `description` varchar(25) DEFAULT NULL COMMENT 'describe',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

CREATE TABLE `school` (
  `id` bigint(20) NOT NULL,
  `school_name` varchar(64) DEFAULT NULL COMMENT 'School Name',
  `school_describe` varchar(128) DEFAULT NULL COMMENT 'School Description',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into `springbootdb`.`user` ( `user_name`, `description`) values ( 'shuai', 'so handsome');

insert into `springbootdb_second`.`school` ( `id`, `school_name`, `school_describe`) values ( '1', 'tsinghua', 'Constant self-reliance, good carrier');

Once the data has been created, the organization for the entire project is as follows:

Start the program and enter: http://localhost:8080/api/v1/test/getUser?id=1 returns the result.