1 SpringBoot配置多个数据库
在开发中,经常会碰到需要从两个不同的数据库查询数据。
今天,本项目将讲解通过 SpringBoot 配置多个数据源。
2 项目配置
2.1 创建SpringBoot项目
项目结构
2.2 pom文件导入需要的依赖
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.8</version> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.2.2</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.22</version> </dependency> </dependencies>
2.4 配置yml文件
Springboot工程中修改配置文件 aplication.yml (注意:如果是单数据源使用 url ,多数据源要使用 jdbc-url )
server:
# 服务器的HTTP端口
port: 8097
spring:
# 配置数据源
datasource:
master:
jdbc-url: jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
test:
jdbc-url: jdbc:mysql://localhost:3306/test2?useSSL=false&serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
# MyBatis配置
mybatis:
# 搜索指定包别名
# (如果 typeAliasesPackage不进行配置,resultType就得写全名resultType="com.liyh.entity.User",
# 但是如果配置了别名,就可以不用写全路径直接写resultType="User"即可)
type-aliases-package: com.liyh.entity
# 配置mapper的扫描,找到所有的mapper.xml映射文件
mapper-locations: classpath*:mapper/**/*Mapper.xml
# 使用驼峰命名
# 数据库表列:user_name
# 实体类属性:userName
configuration:
map-underscore-to-camel-case: true
2.5 创建数据库和数据库表
2.5.1 在 test 数据库创建表并加一条数据
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for sys_user -- ---------------------------- DROP TABLE IF EXISTS `sys_user`; CREATE TABLE `sys_user` ( `user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID', `user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户账号', `nick_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户昵称', PRIMARY KEY (`user_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户信息表' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Records of sys_user -- ---------------------------- INSERT INTO `sys_user` VALUES (1, 'admin', '超级管理员'); SET FOREIGN_KEY_CHECKS = 1;
2.5.2 在 test2 数据库创建表并加一条数据
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for sys_user -- ---------------------------- DROP TABLE IF EXISTS `sys_user`; CREATE TABLE `sys_user` ( `user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID', `user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户账号', `nick_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户昵称', PRIMARY KEY (`user_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户信息表' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Records of sys_user -- ---------------------------- INSERT INTO `sys_user` VALUES (1, '测试', '测试员'); SET FOREIGN_KEY_CHECKS = 1;
3 业务代码
3.1 创建读取第一个数据库配置文件配置类
package com.liyh.config; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.annotation.MapperScan; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.jdbc.DataSourceBuilder; 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 org.springframework.jdbc.datasource.DataSourceTransactionManager; import org.springframework.transaction.PlatformTransactionManager; import javax.sql.DataSource; @Configuration @MapperScan(basePackages = {"com.liyh.mapper"}, sqlSessionFactoryRef = "masterSqlSessionFactory") public class MybatisMasterConfig { @Bean(name = "masterDataSource") @ConfigurationProperties(prefix = "spring.datasource.master") public DataSource dataSource() { return DataSourceBuilder.create().build(); } /** * 配置事务管理器,不然事务不起作用 * * @return */ @Bean public PlatformTransactionManager transactionManager() { return new DataSourceTransactionManager(this.dataSource()); } @Primary @Bean(name = "masterSqlSessionFactory") public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(dataSource); sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver() .getResources("classpath*:mapper/*.xml")); sqlSessionFactoryBean.setTypeAliasesPackage("com.liyh.entity"); sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true); return sqlSessionFactoryBean.getObject(); } }
3.2 创建读取第二个数据库配置文件配置类
package com.liyh.config; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.annotation.MapperScan; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.jdbc.DataSourceBuilder; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.core.io.support.PathMatchingResourcePatternResolver;import javax.sql.DataSource; @Configuration @MapperScan(basePackages = "com.liyh.test.mapper", sqlSessionFactoryRef = "testSqlSessionFactory") public class MybatisTestConfig { @Bean(name = "testDataSource") @ConfigurationProperties(prefix = "spring.datasource.test") public DataSource dataSource() { return DataSourceBuilder.create().build(); } @Bean("testSqlSessionFactory") public SqlSessionFactory sqlSessionFactory(@Qualifier("testDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean (); sqlSessionFactoryBean.setDataSource(dataSource); sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver() .getResources("classpath*:mapper/test/*.xml")); sqlSessionFactoryBean.setTypeAliasesPackage("com.liyh.entity"); sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true); return sqlSessionFactoryBean.getObject(); } }
3.3 创建 Entity 接口数据对象
package com.liyh.entity; import lombok.Data; /** * 用户对象 sys_user * * @author liyh */ @Data public class SysUser { /** * 用户ID */ private Long userId; /** * 用户账号 */ private String userName; /** * 用户昵称 */ private String nickName; }
3.4 创建 UserController 接口
package com.liyh.controller; import com.liyh.entity.SysUser; import com.liyh.service.UserService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; /** * @Author: liyh */ @RestController @RequestMapping("/user") public class UserController { @Autowired UserService userService; /** * 根据用户id查询用户信息 * * @param userId * @return */ @RequestMapping("/getMasterInfo") public SysUser getMasterInfo(String userId) { SysUser user = userService.getMasterInfo(userId); return user; } /** * 根据用户id查询用户信息 * * @param userId * @return */ @RequestMapping("/getTestInfo") public SysUser getTestInfo(String userId) { SysUser user = userService.getTestInfo(userId); return user; } }
3.5 创建 UserService 接口
package com.liyh.service; import com.liyh.entity.SysUser; /** * 用户业务接口 * * @Author: liyh */ public interface UserService { SysUser getMasterInfo(String userId); SysUser getTestInfo(String userId); }
3.6 创建 UserService 实现类
package com.liyh.service.impl; import com.liyh.entity.SysUser; import com.liyh.mapper.MasterMapper; import com.liyh.service.UserService; import com.liyh.test.mapper.TestMapper; import org.springframework.stereotype.Service; import javax.annotation.Resource; /** * 用户业务实现类 * * @Author: liyh */ @Service public class UserServiceImpl implements UserService { @Resource private MasterMapper masterMapper; @Resource private TestMapper testMapper; @Override public SysUser getMasterInfo(String userId) { return masterMapper.getMasterInfo(userId); } @Override public SysUser getTestInfo(String userId) { return testMapper.getTestInfo(userId); } }
3.6 在 mapper 包下创建 mapper和xml
package com.liyh.mapper; import com.liyh.entity.SysUser; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; /** * mapper接口 * * @Author: liyh */ @Mapper public interface MasterMapper { SysUser getMasterInfo(@Param("userId") String userId); }
<?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.liyh.mapper.MasterMapper"> <resultMap type="com.liyh.entity.SysUser" id="UserResult"> <id property="userId" column="user_id"/> <id property="userName" column="user_name"/> <id property="nickName" column="nick_name"/> </resultMap> <select id="getMasterInfo" resultType="SysUser"> select * from sys_user where user_id = #{userId} </select> </mapper>
3.6 在 test/mapper 包下创建 mapper和xml
package com.liyh.test.mapper; import com.liyh.entity.SysUser; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; /** * test接口 * * @Author: liyh */ @Mapper public interface TestMapper { SysUser getTestInfo(@Param("userId") String userId); }
<?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.liyh.test.mapper.TestMapper"> <resultMap type="com.liyh.entity.SysUser" id="UserResult"> <id property="userId" column="user_id"/> <id property="userName" column="user_name"/> <id property="nickName" column="nick_name"/> </resultMap> <select id="getTestInfo" resultType="SysUser"> select * from sys_user where user_id = #{userId} </select> </mapper>
4 测试接口
4.1 测试第一个数据库查询接口
http://localhost:8097/user/getMasterInfo?userId=1
4.2 测试第二个数据库查询接口
http://localhost:8097/user/getTestInfo?userId=1