目前业界操作数据库的框架一般是 Mybatis
,但在很多业务场景下,我们需要在一个工程里配置多个数据源来实现业务逻辑。在 SpringBoot
中也可以实现多数据源并配合 Mybatis
框架编写xml文件来执行SQL。在 SpringBoot
中,配置多数据源的方式十分便捷,
下面开始上代码:
pom.xml
文件中需要添加一些依赖 <!-- Spring Boot Mybatis 依赖 --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.2.0</version> </dependency> <!-- MySQL 连接驱动依赖 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.39</version> </dependency> <!-- Druid 数据连接池依赖 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.18</version> </dependency>
# master 数据源配置 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 # second 数据源配置 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
多数据源配置的时候注意,必须要有一个主数据源,即 MasterDataSourceConfig
配置
MasterDataSourceConfig
的代码:
@Configuration // 扫描 Mapper 接口并容器管理 @MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory") public class MasterDataSourceConfig { // 精确到 master 目录,以便跟其他数据源隔离 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(); } }
第二个数据源 SecondDataSourceConfig
的配置如下:
@Configuration // 扫描 Mapper 接口并容器管理 @MapperScan(basePackages = SecondDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "secondSqlSessionFactory") public class SecondDataSourceConfig { // 精确到 cluster 目录,以便跟其他数据源隔离 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(); } }
到此,不同的数据源配置就已经完成,剩下的只需要将将 Mybatis
的xml文件和 DAO
层的接口写好,并在 Service
层注入,直接使用就行。
Service
层的代码:
@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("清华"); userVo.setSchoolVo(schoolVo); return userVo; } }
Mybatis的xml文件 UserDao.xml
和 SchoolDao.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); } }
创建数据库和表SQL:
CREATE DATABASE springbootdb; CREATE DATABASE springbootdb_second; CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户编号', `user_name` varchar(25) DEFAULT NULL COMMENT '用户名称', `description` varchar(25) DEFAULT NULL COMMENT '描述', 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_describe` varchar(128) DEFAULT NULL COMMENT '学校描述', 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', '清华', '自强不息,厚德载物');
创建好数据后,整个工程的机构如下:
启动程序,在浏览器输入: http://localhost :8080/api/v1/test/getUser?id=1即可返回结果。
github地址: Spring Boot 教程、技术栈、示例代码