Spring Boot实现优雅地连接多个数据库

悠悠 2022-11-18 04:52 431阅读 0赞

### 三步实现 ###

*   *  一：更改application.yml
     *  二：添加两个配置类
     *   *   *  （1）DataSourceOneConfig
             *  （2）DataSourceTwoConfig
     *  三：测试连接效果
     *   *   *  （1）新建Bean
             *  （2）新建Mapper
             *  （3）新建Service
             *  （4）新建ServiceImpl
             *  （5）新建Controller

连接多个数据库其实也很简单，只是在连接一个数据库的基础上，添加多一个访问的数据源而已，详情请往下看，这里以连接两个数据库为例，更多的连接，请阅览后参考写法继续添加即可！

## 一：更改application.yml ##

spring:
      datasource:
    # 配置多个数据源
        one:
          type: com.alibaba.druid.pool.DruidDataSource
          username: root
          password: root
          jdbc-url: jdbc:mysql://localhost:3306/business?useSSL=true&userUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
          driver-class-name: com.mysql.cj.jdbc.Driver  #数据库链接驱动
    
        two:
          type: com.alibaba.druid.pool.DruidDataSource
          username: root
          password: root
          jdbc-url: jdbc:mysql://localhost:3306/test?useSSL=true&userUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
          driver-class-name: com.mysql.cj.jdbc.Driver  #数据库链接驱动

**需要注意：**

连接多个数据库时，url要更改为jdbc-url，否则会报错。

**报错内容：**

java.lang.IllegalArgumentException: jdbcUrl is required with driverClassName

## 二：添加两个配置类 ##

#### （1）DataSourceOneConfig ####

package com.example.business.config;
    
    
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.SqlSessionTemplate;
    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 tk.mybatis.spring.annotation.MapperScan;
    
    import javax.sql.DataSource;
    
    @Configuration
    @MapperScan(basePackages = "com.example.business.mapper.basemapper", sqlSessionTemplateRef = "db1SqlSessionTemplate")
    public class DataSourceOneConfig { 
        @Bean
        @ConfigurationProperties(prefix = "spring.datasource.one")
        @Primary
        public DataSource db1DataSource() { 
            return DataSourceBuilder.create().build();
        }
    
        @Bean
        @Primary
        public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception { 
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
            return bean.getObject();
        }
    
        @Bean
        @Primary
        public DataSourceTransactionManager db1TransactionManager(@Qualifier("db1DataSource") DataSource dataSource) { 
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean
        @Primary
        public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { 
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }

#### （2）DataSourceTwoConfig ####

package com.example.business.config;
    
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.SqlSessionTemplate;
    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 org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import tk.mybatis.spring.annotation.MapperScan;
    
    import javax.sql.DataSource;
    
    @Configuration
    @MapperScan(basePackages = "com.example.business.mapper.test", sqlSessionTemplateRef = "db2SqlSessionTemplate")
    public class DataSourceTwoConfig { 
    
        @Bean
        @ConfigurationProperties(prefix = "spring.datasource.two")
        public DataSource db2DataSource() { 
            return DataSourceBuilder.create().build();
        }
    
        @Bean
        public SqlSessionFactory db2SqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception { 
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
            return bean.getObject();
        }
    
        @Bean
        public DataSourceTransactionManager db2TransactionManager(@Qualifier("db2DataSource") DataSource dataSource) { 
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean
        public SqlSessionTemplate db2SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { 
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }

**需要注意：**

（1） basePackages

@MapperScan(basePackages = "com.example.business.mapper.test", sqlSessionTemplateRef = "db2SqlSessionTemplate")

basePackages 里面是你的mapper存放的位置，由于这里连接的是两个数据库，所有这里两个值不能一样，即第一个和第二个数据库的mapper应该放在两个不同的目录下。否则启动会报找不到表的错误。

（2）如果启动类添加了@MapperScan，需要去掉。

package com.example.business;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    @SpringBootApplication
    public class BusinessApplication { 
    
        public static void main(String[] args) { 
            SpringApplication.run(BusinessApplication.class, args);
    
        }
    
    }

## 三：测试连接效果 ##

#### （1）新建Bean ####

package com.example.business.bean;
    
    import lombok.Data;
    import tk.mybatis.mapper.annotation.KeySql;
    
    import javax.persistence.Column;
    import javax.persistence.Id;
    import javax.persistence.Table;
    
    @Data
    @Table(name = "user")
    public class User { 
        @Id//主键
        @KeySql(useGeneratedKeys = true)//自动生成主键
        private Integer id;
    
        @Column(name = "name")
        private String name;
    
        @Column(name = "password")
        private String password;
    }

#### （2）新建Mapper ####

package com.example.business.mapper.test;
    
    import com.example.business.bean.User;
    import com.example.business.mapper.BaseMapper;
    import org.springframework.stereotype.Repository;
    
    @Repository
    public interface UserMapper extends BaseMapper<User> { 
    
    }

#### （3）新建Service ####

package com.example.business.service;
    
    import com.example.business.bean.User;
    
    import java.util.List;
    
    public interface TestService { 
        public List<User> selectUserAll();
    }

#### （4）新建ServiceImpl ####

package com.example.business.serviceImpl;
    
    import com.example.business.bean.User;
    import com.example.business.mapper.test.UserMapper;
    import com.example.business.service.TestService;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    
    @Service
    @Slf4j
    public class TestServiceImpl implements TestService { 
    
        @Autowired
        UserMapper userMapper;
    
        @Override
        public List<User> selectUserAll() { 
            return userMapper.selectAll();
        }
    }

#### （5）新建Controller ####

由于我前面第一个已经测试通过，没问题，所以这里只测试第二个数据库。

package com.example.business.controller;
    
    import com.example.business.service.TestService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    @RestController
    @RequestMapping(value = "/SimRecommendController")
    public class SimRecommendController { 
    
        private Object result;
    
        @Autowired
        private TestService testService;
    
        @RequestMapping(value = "/selectUserAll")
        public Object selectUserAll() { 
            result = testService.selectUserAll();
            return result;
        }
    }

经过访问，完美连接！

PS：项目目录结构图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU2MzU3Mw_size_16_color_FFFFFF_t_70_pic_center]

目录结构并没有强制限制，按照你自己的项目目录来就可以了，只要注意mapper要区分两个数据库的mapper，要放在不同目录下就可以！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU2MzU3Mw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221022/1ecff4eba18849d794f792cebd673787.png