mybatis 手把手配置多个数据源（多个数据库连接）

您所在的位置：网站首页 › mybatis的数据源类型有哪些 › mybatis 手把手配置多个数据源（多个数据库连接）

mybatis 手把手配置多个数据源（多个数据库连接）

2024-07-14 05:25:45| 来源: 网络整理| 查看: 265

需求：项目中特别是做一个项目的外挂时，我们不希望直接在原库中修改，而是从一个库（业务库）读取，然后存入另一个库中（本地库）。这种需求非常常见；那么如何解决这个问题呢！首先用原来的数据源配置文件指定是不行的（因为数据源配置机制决定）；准备工作，配置一个Spring boot +mybatis出来。项目结构如下：其中confing/dds/用来实现AOP切面基础类在这里插入图片描述 1.数据库准备 2.数据源配置：application.yml

#运行端口 server: port: 8080 #数据库配置 spring: datasource: master: driver-class-name: com.mysql.cj.jdbc.Driver type: com.zaxxer.hikari.HikariDataSource #spring boot都默认的源据配置 jdbcUrl: jdbc:mysql://localhost:3306/testdb?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8 username: root password: 123456 slave: driver-class-name: com.mysql.cj.jdbc.Driver type: com.zaxxer.hikari.HikariDataSource #spring boot都默认的源据配置 jdbcUrl: jdbc:mysql://localhost:3306/APSDB?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8 username: root password: 123456 # 打印sql 这个是目的是为了在运行时打印时输出SQL语句，方便调试 logging: level: mybatisdemo.demo.dao : debug

3.配置maven 依赖 :pom.xml

4.0.0 org.springframework.boot spring-boot-starter-parent 2.1.5.RELEASE com.louis.springboot demo 0.0.1-SNAPSHOT demo Demo project for Spring Boot 1.8 org.springframework.boot spring-boot-starter-web org.springframework.boot spring-boot-starter-test test org.springframework.boot spring-boot-starter-aop org.mybatis.spring.boot mybatis-spring-boot-starter 2.0.0 mysql mysql-connector-java io.springfox springfox-swagger2 2.9.2 io.springfox springfox-swagger-ui 2.9.2 org.springframework.boot spring-boot-maven-plugin src/main/java **/sqlmap/*.xml false src/main/resources **/*.* true

4.配置Swagger接口 SwaggerConfig

package mybatisdemo.demo.config; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import springfox.documentation.builders.ApiInfoBuilder; import springfox.documentation.builders.PathSelectors; import springfox.documentation.builders.RequestHandlerSelectors; import springfox.documentation.service.ApiInfo; import springfox.documentation.spi.DocumentationType; import springfox.documentation.spring.web.plugins.Docket; import springfox.documentation.swagger2.annotations.EnableSwagger2; @Configuration @EnableSwagger2 class SwaggerConfig { @Bean public Docket buildDocket() { return new Docket(DocumentationType.SWAGGER_2) .apiInfo(apiInfo())//调用下面apiInfo()方法 .select() .apis(RequestHandlerSelectors.basePackage("mybatisdemo.demo"))//注意这里的路径，新手容易在这里出错导致打不开。 .paths(PathSelectors.any()) .build(); } public ApiInfo apiInfo() { return new ApiInfoBuilder() .title("swagger2 API") .description("小型demo") .termsOfServiceUrl("http://www.163.com")//这里可以是项目地址 .version("1.0.1") .build(); } }

5.配置动态注解，用于数据源标识（创建在config\dds）:DataSource.java 其目的为了实现动态数据源注解。

package mybatisdemo.demo.config.dds; import java.lang.annotation.Documented; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * 动态数据源注解 */ @Target({ElementType.METHOD, ElementType.TYPE}) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface DataSource { /** * 数据源key值 * @return */ String value(); }

6.配置数据源操作类（创建在config\dds）:DynamicDataSource 并继承AbstractRoutingDataSource 类

package mybatisdemo.demo.config.dds; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; import javax.sql.DataSource; import java.util.Map; public class DynamicDataSource extends AbstractRoutingDataSource { /** * 如果不希望数据源在启动配置时就加载好，可以定制这个方法，从任何你希望的地方读取并返回数据源 * 比如从数据库、文件、外部接口等读取数据源信息，并最终返回一个DataSource实现类对象即可 */ @Override protected DataSource determineTargetDataSource() { return super.determineTargetDataSource(); } /** * 如果希望所有数据源在启动配置时就加载好，这里通过设置数据源Key值来切换数据，定制这个方法 */ @Override protected Object determineCurrentLookupKey() { return DynamicDataSourceContextHolder.getDataSourceKey(); } /** * 设置默认数据源 * @param defaultDataSource */ public void setDefaultDataSource(Object defaultDataSource) { super.setDefaultTargetDataSource(defaultDataSource); } /** * 设置数据源 * @param dataSources */ public void setDataSources(Map dataSources) { super.setTargetDataSources(dataSources); // 将数据源的 key 放到数据源上下文的 key 集合中，用于切换时判断数据源是否有效 DynamicDataSourceContextHolder.addDataSourceKeys(dataSources.keySet()); } }

7.数据源切换操作（创建在config\dds）:DynamicDataSourceAspect

package mybatisdemo.demo.config.dds; import org.aspectj.lang.JoinPoint; import org.aspectj.lang.annotation.After; import org.aspectj.lang.annotation.Aspect; import org.aspectj.lang.annotation.Before; import org.springframework.core.annotation.Order; import org.springframework.stereotype.Component; @Aspect @Order(-1) // 该切面应当先于 @Transactional 执行 @Component public class DynamicDataSourceAspect { /** * 切换数据源 * @param point * @param dataSource */ @Before("@annotation(dataSource))") public void switchDataSource(JoinPoint point, DataSource dataSource) { if (!DynamicDataSourceContextHolder.containDataSourceKey(dataSource.value())) { System.out.println("DataSource [{}] doesn't exist, use default DataSource [{}] " + dataSource.value()); } else { // 切换数据源 DynamicDataSourceContextHolder.setDataSourceKey(dataSource.value()); System.out.println("Switch DataSource to [" + DynamicDataSourceContextHolder.getDataSourceKey() + "] in Method [" + point.getSignature() + "]"); } } /** * 重置数据源 * @param point * @param dataSource */ @After("@annotation(dataSource))") public void restoreDataSource(JoinPoint point, DataSource dataSource) { // 将数据源置为默认数据源 DynamicDataSourceContextHolder.clearDataSourceKey(); System.out.println("Restore DataSource to [" + DynamicDataSourceContextHolder.getDataSourceKey() + "] in Method [" + point.getSignature() + "]"); } }

8.数据源实现层（创建在config\dds）:DynamicDataSourceContextHolder

package mybatisdemo.demo.config.dds; import java.util.ArrayList; import java.util.Collection; import java.util.List; public class DynamicDataSourceContextHolder { private static final ThreadLocal contextHolder = new ThreadLocal() { /** * 将 master 数据源的 key作为默认数据源的 key */ @Override protected String initialValue() { return "master"; } }; /** * 数据源的 key集合，用于切换时判断数据源是否存在 */ public static List dataSourceKeys = new ArrayList(); /** * 切换数据源 * @param key */ public static void setDataSourceKey(String key) { contextHolder.set(key); } /** * 获取数据源 * @return */ public static String getDataSourceKey() { return contextHolder.get(); } /** * 重置数据源 */ public static void clearDataSourceKey() { contextHolder.remove(); } /** * 判断是否包含数据源 * @param key 数据源key * @return */ public static boolean containDataSourceKey(String key) { return dataSourceKeys.contains(key); } /** * 添加数据源keys * @param keys * @return */ public static boolean addDataSourceKeys(Collection keys) { return dataSourceKeys.addAll(keys); } }

9.配置Mybatis MybatisConfig

package mybatisdemo.demo.config; import javax.sql.DataSource; import mybatisdemo.demo.config.dds.DynamicDataSource; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.annotation.MapperScan; import org.springframework.beans.factory.annotation.Autowired; 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 java.util.HashMap; import java.util.Map; @Configuration @MapperScan("mybatisdemo.demo.**.dao") // 扫描DAO public class MybatisConfig { @Bean("master") @Primary @ConfigurationProperties(prefix = "spring.datasource.master") public DataSource master() { return DataSourceBuilder.create().build(); } @Bean("slave") @ConfigurationProperties(prefix = "spring.datasource.slave") public DataSource slave() { return DataSourceBuilder.create().build(); } @Bean("dynamicDataSource") public DataSource dynamicDataSource() { DynamicDataSource dynamicDataSource = new DynamicDataSource(); Map dataSourceMap = new HashMap(2); dataSourceMap.put("master", master()); dataSourceMap.put("slave", slave()); // 将 master 数据源作为默认指定的数据源 dynamicDataSource.setDefaultDataSource(master()); // 将 master 和 slave 数据源作为指定的数据源 dynamicDataSource.setDataSources(dataSourceMap); return dynamicDataSource; } @Bean public SqlSessionFactoryBean sqlSessionFactoryBean() throws Exception { SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean(); // 配置数据源，此处配置为关键配置，如果没有将 dynamicDataSource作为数据源则不能实现切换 sessionFactory.setDataSource(dynamicDataSource()); sessionFactory.setTypeAliasesPackage("mybatisdemo.demo.**.model"); // 扫描Model PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver(); sessionFactory.setMapperLocations(resolver.getResources("classpath*:**/sqlmap/*.xml")); // 扫描映射文件 return sessionFactory; } @Bean public PlatformTransactionManager transactionManager() { // 配置事务管理, 使用事务时在方法头部添加@Transactional注解即可 return new DataSourceTransactionManager(dynamicDataSource()); } }

10.配置程序启动扫描

package mybatisdemo.demo; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration; import org.springframework.context.annotation.ComponentScan; @SpringBootApplication(exclude = {DataSourceAutoConfiguration.class}) // 禁用数据源自动配置 @ComponentScan(basePackages = "mybatisdemo.demo") public class DemoApplication { public static void main(String[] args) { SpringApplication.run(DemoApplication.class, args); } }

11.业务实现创建实体类User.java (model)

package mybatisdemo.demo.model; public class User { private Integer id; private String name; private String password; public User() { } public User(Integer id, String name, String password) { this.id = id; this.name = name; this.password = password; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } }

12.服务接口（service）

package mybatisdemo.demo.service; import mybatisdemo.demo.model.User; import java.util.List; public interface UserService { List findAll(); }

13.映射类 (dao)

package mybatisdemo.demo.dao; import mybatisdemo.demo.model.User; import java.util.List; public interface UserMapper { /** * 查询全部用户 * @return */ List selectAll(); }

14.sqlmap

id, name, password select from user

15.服务实现类(service\impl)

package mybatisdemo.demo.service.impl; import mybatisdemo.demo.dao.UserMapper; import mybatisdemo.demo.model.User; import mybatisdemo.demo.service.UserService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; @Service public class UserServiceimpl implements UserService { @Autowired private UserMapper userMapper; @Override public List findAll() { return userMapper.selectAll(); } }

16.接口(controller)

package mybatisdemo.demo.controller; import mybatisdemo.demo.config.dds.DataSource; import mybatisdemo.demo.service.UserService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; @RestController @RequestMapping("user") public class UserController { @Autowired private UserService userService; @DataSource(value="master") @PostMapping(value="/findAll") public Object findAll() { return userService.findAll(); } @DataSource(value="slave") @PostMapping(value="/findAll2") public Object findAll2() { return userService.findAll(); } }

17.测试在这里插入图片描述