springboot+mybatis多数据源配置(亲测)

- 日理万妓 2022-09-02 04:02 182阅读 0赞

> 原创不易，转载请注明出处

### 文章目录 ###

*   *   *  前言
         *  1.maven依赖
         *  2.配置文件
         *  3.配置类
         *  4.测试
         *   *  4.1 准备-数据库
             *  4.2 准备-实体
             *  4.3 准备-dao、mapper xml文件
             *  4.4 准备-测试service、controller
             *  4.5 测试

### 前言 ###

主要是记录下springboot+mybatis+druid 配置多数据源，话不多说，直接开干。

### 1.maven依赖 ###

我这边用的springboot版本是2.5.2

<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.4</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.10</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>ognl</groupId>
        <artifactId>ognl</artifactId>
        <version>3.2.15</version>
        <scope>compile</scope>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>org.javassist</groupId>
        <artifactId>javassist</artifactId>
        <version>3.27.0-GA</version>
        <scope>compile</scope>
        <optional>true</optional>
    </dependency>

### 2.配置文件 ###

在application.properties（yaml 文件也是ok的）中

#数据源1
    spring.datasource.druid.main.driver-class-name=com.mysql.jdbc.Driver
    spring.datasource.druid.main.url=jdbc:mysql://localhost:3306/mybatis_test?useSSL=false&characterEncoding=utf8
    spring.datasource.druid.main.username=root
    spring.datasource.druid.main.password=root
    spring.datasource.druid.main.initial-size=5
    spring.datasource.druid.main.min-idle=5
    spring.datasource.druid.main.maxActive=20
    spring.datasource.druid.main.maxWait=60000
    spring.datasource.druid.main.timeBetweenEvictionRunsMillis=60000
    spring.datasource.druid.main.minEvictableIdleTimeMillis=300000
    spring.datasource.druid.main.validationQuery=SELECT 1 FROM DUAL
    spring.datasource.druid.main.testWhileIdle=true
    spring.datasource.druid.main.testOnBorrow=false
    spring.datasource.druid.main.testOnReturn=false
    spring.datasource.druid.main.poolPreparedStatements=true
    spring.datasource.druid.main.maxPoolPreparedStatementPerConnectionSize=20
    
    
    # 数据源2
    spring.datasource.druid.secondary.driver-class-name=com.mysql.jdbc.Driver
    spring.datasource.druid.secondary.url=jdbc:mysql://localhost:3306/mybatis_test2?useSSL=false&characterEncoding=utf8
    spring.datasource.druid.secondary.username=root
    spring.datasource.druid.secondary.password=root
    spring.datasource.druid.secondary.initial-size=5
    spring.datasource.druid.secondary.min-idle=5
    spring.datasource.druid.secondary.maxActive=20
    spring.datasource.druid.secondary.maxWait=60000
    spring.datasource.druid.secondary.timeBetweenEvictionRunsMillis=60000
    spring.datasource.druid.secondary.minEvictableIdleTimeMillis=300000
    spring.datasource.druid.secondary.validationQuery=SELECT 1 FROM DUAL
    spring.datasource.druid.secondary.testWhileIdle=true
    spring.datasource.druid.secondary.testOnBorrow=false
    spring.datasource.druid.secondary.testOnReturn=false
    spring.datasource.druid.secondary.poolPreparedStatements=true
    spring.datasource.druid.secondary.maxPoolPreparedStatementPerConnectionSize=20

其中main 与secondary 是区分不同数据源的。

### 3.配置类 ###

主数据源配置：

@Configuration
    @MapperScan(basePackages ="com.example.testcanal.dao.main" ,sqlSessionFactoryRef ="mainSqlSessionFactory" )
    public class MainDataSourceConfiguration { 
        /** * 创建main数据库的数据源 * @return */
        @Primary
        @Bean("mainDataSource")
        @ConfigurationProperties("spring.datasource.druid.main")
        public DataSource createMainDataSource(){ 
            return DruidDataSourceBuilder.create().build();
        }
    
        /** * 创建main的SqlSessionFactory * @param dataSource * @return * @throws Exception */
        @Primary
        @Bean("mainSqlSessionFactory")
        public SqlSessionFactory  createMainSqlSessionFactory(@Qualifier("mainDataSource") DataSource dataSource) throws Exception { 
            SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
            sqlSessionFactory.setDataSource(dataSource);
            sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().
                    getResources("classpath:mapper/main/*.xml"));
            return sqlSessionFactory.getObject();
        }
    
        /** * 事务管理器 * @param dataSource * @return */
        @Primary
        @Bean(name = "mainTransactionManager")
        public DataSourceTransactionManager mainTransactionManager(@Qualifier("mainDataSource") DataSource dataSource) { 
            return new DataSourceTransactionManager(dataSource);
        }
    }

@MapperScan(basePackages =“com.example.testcanal.dao.main” ,sqlSessionFactoryRef =“mainSqlSessionFactory” )

basePackages 是告诉mybatis com.example.testcanal.dao.main 这个包下面的dao接口类使用 mainSqlSessionFactory 这个SqlSessionFactory 创建SqlSession。  
而在创建mainSqlSessionFactory 的方法中`getResources("classpath:mapper/main/*.xml")` 就是创建SqlSessionFactory的时候接下这个路径下的所有mapper xml文件。  
需要注意的是项目中要有一个主数据源。也就是加 @Primary 注解

第二个数据源配置：

@Configuration
    @MapperScan(basePackages ="com.example.testcanal.dao.secondary" ,sqlSessionFactoryRef ="secondarySqlSessionFactory" )
    public class SecondaryDataSourceConfiguration { 
        /** * 创建secondary数据库的数据源 * @return */
        @Bean("secondaryDataSource")
        @ConfigurationProperties("spring.datasource.druid.secondary")
        public DataSource createMainDataSource(){ 
            return DruidDataSourceBuilder.create().build();
        }
    
        /** * 创建secondary的SqlSessionFactory * @param dataSource * @return * @throws Exception */
        @Bean("secondarySqlSessionFactory")
        public SqlSessionFactory  createMainSqlSessionFactory(@Qualifier("secondaryDataSource") DataSource dataSource) throws Exception { 
            SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
            sqlSessionFactory.setDataSource(dataSource);
            sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().
                    getResources("classpath:mapper/secondary/*.xml"));
            return sqlSessionFactory.getObject();
        }
        /** * 事务管理器 * @param dataSource * @return */
        @Bean(name = "secondaryTransactionManager")
        public DataSourceTransactionManager mainTransactionManager(@Qualifier("secondaryDataSource") DataSource dataSource) { 
            return new DataSourceTransactionManager(dataSource);
        }
    }

### 4.测试 ###

#### 4.1 准备-数据库 ####

我这里是创建了2个数据库。一个是mybatis\_test ,一个是mybatis\_test2

mybatis\_test 创建表

CREATE TABLE `user` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `username` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

mybatis\_test2 创建表

CREATE TABLE `order_info` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `goods_id` int(10) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

#### 4.2 准备-实体 ####

OrderEntity :

public class OrderEntity implements Serializable { 
        private Integer id;
        private Integer goodsId;
        public Integer getId() { 
            return id;
        }
        public void setId(Integer id) { 
            this.id = id;
        }
        public Integer getGoodsId() { 
            return goodsId;
        }
        public void setGoodsId(Integer goodsId) { 
            this.goodsId = goodsId;
        }
    }

UserEntity :

public class UserEntity implements Serializable { 
        private Integer id;
        private String userName;
        private String password;
        public Integer getId() { 
            return id;
        }
        public void setId(Integer id) { 
            this.id = id;
        }
        public String getUserName() { 
            return userName;
        }
        public void setUserName(String userName) { 
            this.userName = userName;
        }
        public String getPassword() { 
            return password;
        }
        public void setPassword(String password) { 
            this.password = password;
        }
    }

#### 4.3 准备-dao、mapper xml文件 ####

由于我们在上面的配置类中指定了 main数据源 的dao接口只能写在com.example.testcanal.dao.main 包下面， mapper xml只能写在mapper/main路径下面。  
com.example.testcanal.dao.main 包下面创建UserDao

public interface UserDao { 
        boolean addUser(UserEntity userEntity);
    }

classpath: mapper/main 路径下创建 UserMapper.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.example.testcanal.dao.main.UserDao" >
        <insert id="addUser" parameterType="com.example.testcanal.model.UserEntity">
            insert into user  (username,password)  values (#{userName},#{password})
        </insert>
    </mapper>

secondary 数据源的 dao接口 只能写在com.example.testcanal.dao.secondary 包下面， mapper xml只能写在mapper/secondary 路径下面。

com.example.testcanal.dao.secondary 包下面创建OrderDao

public interface OrderDao { 
        boolean addOrder(OrderEntity orderEntity);
    }

classpath: mapper/secondary 路径下创建 OrderMapper.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.example.testcanal.dao.secondary.OrderDao" >
        <insert id="addOrder" parameterType="com.example.testcanal.model.OrderEntity">
            insert into order_info (goods_id)  values (#{goodsId})
        </insert>
    </mapper>

#### 4.4 准备-测试service、controller ####

service：

public interface UserService { 
        boolean add(UserEntity userEntity);
    }
    
    @Service
    public class UserServiceImpl implements UserService { 
        @Resource
        private UserDao userDao;
        @Resource
        private OrderDao orderDao;
        @Override
        public boolean add(UserEntity userEntity) { 
            OrderEntity order = new OrderEntity();
            order.setGoodsId(101001);
            boolean orderResult= orderDao.addOrder(order);
            System.out.println("添加订单信息："+orderResult);
            return userDao.addUser(userEntity);
        }
    }

controller：

@RestController
    @RequestMapping("/test")
    public class TestController { 
        @Autowired
        private UserService userService;
        @GetMapping("/user/add")
        public UserEntity add(){ 
            UserEntity userEntity=new UserEntity();
            userEntity.setUserName("xxxx");
            userEntity.setPassword("pass");
            userService.add(userEntity);
            return userEntity;
        }
    }

#### 4.5 测试 ####

启动项目，postman 请求url测试  
127.0.0.1:8099/test/user/add  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l1YW5zaGFuZ3NoZW5naHVv_size_16_color_FFFFFF_t_70]

查看数据库  
![在这里插入图片描述][6d44513605b643798824e0f0b4265d2a.png]  
![在这里插入图片描述][52be4da8f5f04561b47c1f3ea5fd33a4.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l1YW5zaGFuZ3NoZW5naHVv_size_16_color_FFFFFF_t_70]: /images/20220829/3c43073b2a20497ca23ff1f0182b0a16.png
[6d44513605b643798824e0f0b4265d2a.png]: /images/20220829/27d3010323104a9cb1e6f61ddc78f6ff.png
[52be4da8f5f04561b47c1f3ea5fd33a4.png]: /images/20220829/429011aa71694c71bf21e0f43b946358.png