springboot2.x实现多数据源请求

偏执的太偏执、 2022-12-22 04:51 148阅读 0赞

在一个项目中有多个数据源（不同库jdbc) ，同时在一个服务中需要调用多个数据库的时候，就需要采用多数据源的方式来实现，数据的获取：  
**第一步：创建二个数据库**  
数据库一：test1  
数据库二：test2  
**二个数据库中建立相同的表**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center]  
**项目的目录结构：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 1]  
**根据不同的包名连接不同的数据源，实现数据源的分离：**  
这是项目需要引入maven包

<dependencies>
            
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jdbc</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>1.1.1</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <optional>true</optional>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
                <exclusions>
                    <exclusion>
                        <groupId>org.junit.vintage</groupId>
                        <artifactId>junit-vintage-engine</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
        </dependencies>

**application.properties文件的配置：**

###datasource1--master
    spring.datasource.test1.driver-class-name = com.mysql.jdbc.Driver
    spring.datasource.test1.jdbc-url = jdbc:mysql://192.168.0.75:3339/test?characterEncoding=utf8&useUnicode=true&useSSL=true&serverTimezone=UTC
    spring.datasource.test1.username = root
    spring.datasource.test1.password = 123456
    ###datasource2--slavel
    spring.datasource.test2.driver-class-name = com.mysql.jdbc.Driver
    spring.datasource.test2.jdbc-url = jdbc:mysql://192.168.0.75:3340/test?characterEncoding=utf8&useUnicode=true&useSSL=true&serverTimezone=UTC
    spring.datasource.test2.username = root
    spring.datasource.test2.password = 123456

这里需要注意的地方是数据源需要配置：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 2]  
解决MySQL的版本问题和时区问题  
**开始配置数据源的config文件：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 3]  
几个数据源配置几个config，各个数据源进行分离，互相不影响  
**datasouce1Config代码：**

package com.zj.demo.zjdemo.config;
    
    /** * 数据源（主） * @Author: zj * @Description: DataSource1Config * @Date: 2020/11/17 0017 下午 16:31 * @Version: 1.0 */
    import javax.sql.DataSource;
    
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.SqlSessionTemplate;
    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.jdbc.datasource.DataSourceTransactionManager;
    
    /** * 主库 */
    //DataSource01数据源的读取
    @Configuration // 注册到springboot容器中
    @MapperScan(basePackages = "com.zj.demo.zjdemo.test01", sqlSessionFactoryRef = "test1SqlSessionFactory")
    //这个里的basePackages中配置的包指向mapper的包
    //指向了下面的名为 test1SqlSessionFactory 的工厂
    public class DataSource1Config { 
    
        @Bean(name = "test1DataSource")  //表示注入到Spring 容器中去
        @ConfigurationProperties(prefix = "spring.datasource.test1")  //表以此为开头，去properties去读 （ 后面是写死的）
        @Primary
        public DataSource testDataSource() { 
            return DataSourceBuilder.create().build();
        }
    
    
        @Bean(name = "test1SqlSessionFactory") //
        @Primary
        public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource)
                throws Exception { 
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            // bean.setMapperLocations(
            // new
            // PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test1/*.xml"));
            // 如果有一些mapper文件的话 可以把上面的注解放开
            return bean.getObject();
        }
    
    
        @Bean(name = "test1TransactionManager")  //配置事务
        @Primary
        public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) { 
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean(name = "test1SqlSessionTemplate")
        @Primary
        public SqlSessionTemplate testSqlSessionTemplate(
                @Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { 
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    
    }

**datasouce2Config代码**：

package com.zj.demo.zjdemo.config;
    import javax.sql.DataSource;
    
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.SqlSessionTemplate;
    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.jdbc.datasource.DataSourceTransactionManager;
    
    //DataSource2
    @Configuration // 注册到springboot容器中
    @MapperScan(basePackages = "com.zj.demo.zjdemo.test02", sqlSessionFactoryRef = "test2SqlSessionFactory")
    public class DataSource2Config { 
    
    
        @Bean(name = "test2DataSource")
        @ConfigurationProperties(prefix = "spring.datasource.test2")
        public DataSource testDataSource() { 
            return DataSourceBuilder.create().build();
        }
    
    
        @Bean(name = "test2SqlSessionFactory")
        public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource)
                throws Exception { 
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            // bean.setMapperLocations(
            // new
            // PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test2/*.xml"));
            return bean.getObject();
        }
    
    
        @Bean(name = "test2TransactionManager")
        public DataSourceTransactionManager testTransactionManager(@Qualifier("test2DataSource") DataSource dataSource) { 
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean(name = "test2SqlSessionTemplate")
        public SqlSessionTemplate testSqlSessionTemplate(
                @Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { 
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    
    }

**对应的实体类如下：**

package com.zj.demo.zjdemo.entity;
    
    import lombok.Data;
    
    /** * @ProjectName: zjdemo * @Package: com.zj.demo.zjdemo.entity * @ClassName: User * @Author: zj * @Description: User * @Date: 2020/11/17 0017 下午 16:47 * @Version: 1.0 */
    @Data
    public class User { 
        private Integer age;
        private String name;
        private  Integer id;
    
    }

**mappertest01和mappertest02如下：**

package com.zj.demo.zjdemo.test01.mappertest01;
    
    import org.apache.ibatis.annotations.Insert;
    import org.apache.ibatis.annotations.Param;
    import org.apache.ibatis.annotations.Select;
    
    import java.util.List;
    import java.util.Map;
    
    /** * @ProjectName: zjdemo * @Package: com.zj.demo.zjdemo.test01.mappertest01 * @ClassName: UserMapperTest01 * @Author: zj * @Description: UserMapperTest01 * @Date: 2020/11/17 0017 下午 16:49 * @Version: 1.0 */
    public interface UserMapperTest01 { 
        @Insert("insert into dd_user values(null,#{name},#{age});")
        public int insert(@Param("name") String name, @Param("age") Integer age);
    }

和

package com.zj.demo.zjdemo.test02.mappertest02;
    
    import org.apache.ibatis.annotations.Insert;
    import org.apache.ibatis.annotations.Param;
    import org.apache.ibatis.annotations.Select;
    
    import java.util.List;
    import java.util.Map;
    
    /** * @ProjectName: zjdemo * @Package: com.zj.demo.zjdemo.test02.mappertest02 * @ClassName: UserMapperTest02 * @Author: zj * @Description: UserMapperTest02 * @Date: 2020/11/17 0017 下午 16:49 * @Version: 1.0 */
    public interface UserMapperTest02 { 
    	//插入
        @Insert("insert into dd_user values(null,#{name},#{age});")
        public int insert(@Param("name") String name, @Param("age") Integer age);
    
    	//查询
        @Select("select * from dd_user;")
        List<Map<String, Object>> queryList();
    }

**service1和service2如下：**

package com.zj.demo.zjdemo.test01.service01;
    
    import com.zj.demo.zjdemo.test01.mappertest01.UserMapperTest01;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    import java.util.Map;
    
    /** * @ProjectName: zjdemo * @Package: com.zj.demo.zjdemo.test01.service01 * @ClassName: UserService01 * @Author: zj * @Description: UserService01 * @Date: 2020/11/17 0017 下午 16:50 * @Version: 1.0 */
    @Service
    @Slf4j
    public class UserService01 { 
        @Autowired
        private UserMapperTest01 userMapperTest01;
    
        public int insertUser(String name, Integer age){ 
            int result = userMapperTest01.insert(name, age);
            log.info("####################",result);
            return result;
        }
    }

和

package com.zj.demo.zjdemo.test02.service02;
    
    import com.zj.demo.zjdemo.test02.mappertest02.UserMapperTest02;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    import java.util.Map;
    
    /** * @ProjectName: zjdemo * @Package: com.zj.demo.zjdemo.test02.service02 * @ClassName: UserService02 * @Author: zj * @Description: UserService02 * @Date: 2020/11/17 0017 下午 16:51 * @Version: 1.0 */
    @Service
    @Slf4j
    public class UserService02 { 
        @Autowired
        private UserMapperTest02 userMapperTest02;
    
        public int insertUser(String name, Integer age){ 
            int result = userMapperTest02.insert(name, age);
            log.info("####################",result);
            return result;
        }
    
        public List<Map<String,Object>> queryList() { 
            return userMapperTest02.queryList();
        }
    }

**controler类：**

package com.zj.demo.zjdemo.controller;
    import com.zj.demo.zjdemo.test01.service01.UserService01;
    import com.zj.demo.zjdemo.test02.service02.UserService02;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    /** * 多数据源处理类 * @Author: zj * @Date: 2020/11/17 0017 下午 16:51 * @Version: 1.0 */
    @RestController
    public class MultiplyController { 
        @Autowired
        private UserService01 userService01;
    
        @Autowired
        private UserService02 userService02;
    
        @RequestMapping("/insertUser01")
        public Integer insertUser1(String name, Integer age){ 
    
            return userService01.insertUser(name, age);
        }
    
        @RequestMapping("/insertUser02")
        public Integer insertUser2(String name, Integer age){ 
    
            return userService02.insertUser(name, age);
        }
    
        @RequestMapping("/insertOrQuery")
        public List<Map<String, Object>> insertOrQuery(String name, Integer age){ 
            List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            Integer n = userService01.insertUser(name, age);
            if (n>0){ 
                list = userService02.queryList();
            }
            return list;
        }
    }

**application启动类：**

package com.zj.demo.zjdemo;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.context.annotation.ComponentScan;
    
    /** * 多数据源启动类 * @author zj */
    @SpringBootApplication(scanBasePackages={ "com.zj.demo.zjdemo.*"})
    public class ZjdemoApplication { 
    
        public static void main(String[] args) { 
            SpringApplication.run(ZjdemoApplication.class, args);
            System.out.println(" run to ZjdemoApplication success !");
        }
    }

**测试结果：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 4]  
数据库插入结果：  
![在这里插入图片描述][20201117201516220.png_pic_center]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 5]  
![在这里插入图片描述][2020111720155833.png_pic_center]  
**该案列是为了达到一个服务下请求多个数据源（插入，查询）**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/2cd8fd1ec0c6428da7543f93201b511c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/b0bc1d03ebd44f909ed0c95eac7d023c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/b74ad451fc2e43d496a7ebf3c2bd43fb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221120/7272946acb024c65843edcc55a010b5b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221120/d68c191bdae442da9e71767724f0a2e4.png
[20201117201516220.png_pic_center]: /images/20221120/6a81258b47ca444b930081a4ad49ec03.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221120/537df9d57c4340c195d7e140e3560804.png
[2020111720155833.png_pic_center]: /images/20221120/a461df6010c94d46a41d114da542b931.png