SpringBoot配置多个数据库源

整体结构

.properties添加多个数据库源

注意url要改为jdbc-url

spring.datasource.login.driver-class-name=org.sqlite.JDBC
spring.datasource.login.jdbc-url=jdbc:sqlite:C:/Users/catface/Desktop/user.db

spring.datasource.register.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.register.jdbc-url=jdbc:mysql://127.0.0.1:3306/register?characterEncoding=UTF-8
spring.datasource.register.username=root
spring.datasource.register.password=root

注入数据源并扫描dao路径

主数据库(项目启动默认连接该数据库)有@Primary注解，从数据库没有

@Configuration
// basePackages为dao文件所在目录
@MapperScan(basePackages = "cc.catface.sbt_test.multi_sql_source.login.dao", sqlSessionTemplateRef = "loginSqlSessionTemplate")
public class LoginDataSourceConfig {

    @Bean(name = "loginDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.login")
    @Primary
    public DataSource loginDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "loginSqlSessionFactory")
    @Primary
    public SqlSessionFactory loginSqlSessionFactory(@Qualifier("loginDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/login/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "loginTransactionManager")
    @Primary
    public DataSourceTransactionManager loginTransactionManager(@Qualifier("loginDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "loginSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate loginSqlSessionTemplate(@Qualifier("loginSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

---

@Configuration
// basePackages为dao文件所在目录
@MapperScan(basePackages = "cc.catface.sbt_test.multi_sql_source.register.dao", sqlSessionTemplateRef = "registerSqlSessionTemplate")
public class RegisterDataSourceConfig {

    @Bean(name = "registerDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.register")
    public DataSource registerDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "registerSqlSessionFactory")
    public SqlSessionFactory registerSqlSessionFactory(@Qualifier("registerDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/register/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "registerTransactionManager")
    public DataSourceTransactionManager registerTransactionManager(@Qualifier("registerDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "registerSqlSessionTemplate")
    public SqlSessionTemplate registerSqlSessionTemplate(@Qualifier("registerSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

编写Dao、Service、Controller

0. pojo

   public class User {
       private String username;
       private String password;
       // setter&getter...
   }
   

1. dao

   @Repository
   @Mapper
   public interface LoginDao {
       List<User> getUsers();
   }
   

   ---

   @Repository
   @Mapper
   public interface RegisterDao {
       List<User> getUsers();
   }
   

2. mapper

   文件目录分别在resources/mapper/login和resources/mapper/register下

   注意命名和dao保持一致，LoginDao对应LoginMapper，RegisterDao对应RegisterMapper

   <!-- LoginMapper.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="cc.catface.sbt_test.multi_sql_source.login.dao.LoginDao">
       <select id="getUsers" resultType="cc.catface.sbt_test.multi_sql_source.pojo.User">
           select username,password from user;
       </select>
   </mapper>
   

   ---

   <!-- RegisterMapper.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="cc.catface.sbt_test.multi_sql_source.register.dao.RegisterDao">
       <select id="getUsers" resultType="cc.catface.sbt_test.multi_sql_source.pojo.User">
           select username,password from register;
       </select>
   </mapper>
   

3. service

   @Service
   public class LoginService {
   
       @Autowired
       LoginDao mLoginDao;
   
       public List<User> getUsers() {
           return mLoginDao.getUsers();
       }
   }
   

   ---

   @Service
   public class RegisterService {
   
       @Autowired
       RegisterDao mRegisterDao;
   
       public List<User> getUsers() {
           return mRegisterDao.getUsers();
       }
   }
   

4. controller

   @Controller
   @RequestMapping(value = "/multi_sql")
   public class TestController {
       
       @Autowired
       LoginService loginService;
       @Autowired
       RegisterService registerService;
       
       @ResponseBody
       @RequestMapping("/login")
       public String getUsersByLogin() {
           return loginService.getUsers().toString();
       }
       
       @ResponseBody
       @RequestMapping("/register")
       public String getUsersByRegister() {
           return registerService.getUsers().toString();
       }
   }