SpringBoot连接两个以上数据库(多个mongo、多个pg)

缺乏、安全感 2023-06-27 03:21 3阅读 0赞

# SpringBoot连接多个数据库 #

## 1. 主要应用场景 ##

1.  数据的迁移：数据库数据由A库迁移至B库；
2.  当业务逻辑有要求时，需要对不同的数据库进行操作。

## 2. 连接一个Mongo和一个Pg ##

当连接的是两个不同类型的数据库时，直接在application.yml文件中进行相应的正常配置即可。

spring:
      datasource:  #pg配置
          url: jdbc:postgresql://ip:port/dbName
          driverClassName: org.postgresql.Driver
          username: username
          password: password
      data:
        mongodb: #mongo配置
          uri: mongodb://username:password@ip:port/dbName
    
    mybatis:
      configuration:
        mapUnderscoreToCamelCase: true

## 3. 连接一个Mongo和多个Pg ##

### 操作步骤 ###

1.  在application.yml文件中，配置一个mongo数据库和两个pg数据库的连接；

spring:
      data:
        mongodb: #mongo配置
          uri: mongodb://username:password@ip:port/dbName
      datasource:
        pgdb1: #配置第一个数据库
          jdbcUrl: jdbc:postgresql://ip:port/dbName
          driverClassName: org.postgresql.Driver
          username: username
          password: password
        pgdb2: #配置第二个数据库
          jdbcUrl: jdbc:postgresql://ip:port/dbName
          driverClassName: org.postgresql.Driver
          username: username
          password: password
    
    mybatis:
      configuration:
        mapUnderscoreToCamelCase: true

1.  启动类中使用@EnableAutoConfiguration注解，关闭SpringBoot关于mybatis的一些自动注入,或者采用这种方式关闭：@SpringBootApplication(exclude = \{DataSourceAutoConfiguration.class, DataSourceTransactionManagerAutoConfiguration.class, MybatisAutoConfiguration.class\}) ；

package com.example.multidatabase;
    
    import org.mybatis.spring.boot.autoconfigure.MybatisAutoConfiguration;
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
    
    //EnableAutoConfiguration注解，关闭springBoot关于mybatis的一些自动注入
    @EnableAutoConfiguration(exclude = { DataSourceAutoConfiguration.class, DataSourceTransactionManagerAutoConfiguration.class, MybatisAutoConfiguration.class})
    @SpringBootApplication
    public class MultidatabaseApplication { 
        public static void main(String[] args) { 
            SpringApplication.run(MultidatabaseApplication.class, args);
        }
    }

1.  创建两个pg数据库连接的配置类；

(1). pgdb1的数据源配置

package com.example.multidatabase.datasource;
    
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    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 javax.sql.DataSource;
    
    
    /** * @author QuS * @date 2019/12/31 14:25 */
    
    @Configuration
    @MapperScan(basePackages = "com.example.multidatabase.mapper.pgdb1", sqlSessionFactoryRef = "pgdb1SqlSessionFactory")
    public class PgDb1DataSourceConfig { 
        @Primary
        @Bean(name = "pgdb1DataSource")
        @ConfigurationProperties("spring.datasource.pgdb1")
        public DataSource masterDataSource() { 
            return DataSourceBuilder.create().build();
        }
    
        @Bean(name = "pgdb1SqlSessionFactory")
        public SqlSessionFactory sqlSessionFactory(@Qualifier("pgdb1DataSource") DataSource dataSource) throws Exception { 
            SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
            sessionFactoryBean.setDataSource(dataSource);
    /* sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver() .getResources("com.example.doubledb.mapper.gsiot.*"));*/
            return sessionFactoryBean.getObject();
        }
    }

(2). pgdb2的数据源配置

package com.example.multidatabase.datasource;
    
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    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 javax.sql.DataSource;
    
    /** * @author QuS * @date 2019/12/31 14:26 */
    
    @Configuration
    @MapperScan(basePackages = "com.example.multidatabase.mapper.pgdb2", sqlSessionFactoryRef = "pgdb2SqlSessionFactory")
    public class PgDb2DataSourceConfig { 
    // @Primary
        @Bean(name = "pgdb2DataSource")
        @ConfigurationProperties("spring.datasource.pgdb2")
        public DataSource masterDataSource() { 
            return DataSourceBuilder.create().build();
        }
    
        @Bean(name = "pgdb2SqlSessionFactory")
        public SqlSessionFactory sqlSessionFactory(@Qualifier("pgdb2DataSource") DataSource dataSource) throws Exception { 
            SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
            sessionFactoryBean.setDataSource(dataSource);
    /* sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver() .getResources("com.example.doubledb.mapper.org.*"));*/
            return sessionFactoryBean.getObject();
        }
    }

1.  在对应的mapper地址下编写各自的mapper文件，然后service层注入mapper，最后controller调用service进行测试即可。

### 注意事项 ###

1.  application.yml文件中，配置多个数据库连接时，要用jdbcUrl或jdbc-url；
2.  如果需要更多的数据库连接，按照这种方式扩展添加即可

## 4. 连接两个Mongo ##

### 操作步骤 ###

1.  在application.yml中配置两个mongo,分别为mongo1，mongo2

spring:
      datasource: 
          url: jdbc:postgresql://ip:port/dbName
          driverClassName: org.postgresql.Driver
          username: username
          password: password
      data:
        mongodb:
          mongo1:
            uri: mongodb://username:password@ip:port/dbName
          mongo2:
            uri: mongodb://username:password@ip:port/dbName
    
    mybatis:
      configuration:
        mapUnderscoreToCamelCase: true
    server:
      port: 8088

1.  启动类中使用@SpringBootApplication(exclude = \{MongoAutoConfiguration.class, MongoDataAutoConfiguration.class\})去排除MongoDB的自动配置；

package com.example.multidatabase;
    
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.boot.autoconfigure.data.mongo.MongoDataAutoConfiguration;
    import org.springframework.boot.autoconfigure.mongo.MongoAutoConfiguration;
    
    @MapperScan("com.example.multidatabase.mapper")
    @SpringBootApplication(exclude = { MongoAutoConfiguration.class, MongoDataAutoConfiguration.class})
    public class PgmongoApplication { 
    
        public static void main(String[] args) { 
            SpringApplication.run(PgmongoApplication.class, args);
        }
    
    }

1.  创建Mongo1Config和Mongo2Config两个配置类  
    (1). Mongo1Config

package com.example.multidatabase.config;
    
    import com.mongodb.MongoClientURI;
    import org.springframework.boot.autoconfigure.mongo.MongoProperties;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    import org.springframework.data.mongodb.MongoDbFactory;
    import org.springframework.data.mongodb.core.MongoTemplate;
    import org.springframework.data.mongodb.core.SimpleMongoDbFactory;
    import org.springframework.data.mongodb.repository.config.EnableMongoRepositories;
    
    /** * @author QuS * @date 2020/1/3 10:58 */
    @Configuration
    @EnableMongoRepositories(basePackages = "com.example.multidatabase.repository.mongo1", mongoTemplateRef = "mongo1Template")
    public class Mongo1Config { 
    
       @Bean
       @Primary
       @ConfigurationProperties(prefix = "spring.data.mongodb.mongo1")
       public MongoProperties mongo1Properties() { 
           return new MongoProperties();
       }
    
       @Primary
       @Bean(name = "mongo1Template")
       public MongoTemplate mongo1Template() throws Exception { 
           return new MongoTemplate(mongo1Factory(mongo1Properties()));
       }
    
       @Bean
       @Primary
       public MongoDbFactory mongo1Factory(MongoProperties mongoProperties) throws Exception     { 
           return new SimpleMongoDbFactory(new MongoClientURI(mongo1Properties().getUri()));
       }
    
    }

(2). Mongo2Config

package com.example.multidatabase.config;
    
    import com.mongodb.MongoClientURI;
    import org.springframework.boot.autoconfigure.mongo.MongoProperties;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    import org.springframework.data.mongodb.MongoDbFactory;
    import org.springframework.data.mongodb.core.MongoTemplate;
    import org.springframework.data.mongodb.core.SimpleMongoDbFactory;
    import org.springframework.data.mongodb.repository.config.EnableMongoRepositories;
    
    /** * @author QuS * @date 2020/1/3 10:58 */
    @Configuration
    @EnableMongoRepositories(basePackages = "com.example.multidatabase.repository.mongo2", mongoTemplateRef = "mongo2Template")
    public class Mongo2Config { 
    
        @Bean
        @ConfigurationProperties(prefix = "spring.data.mongodb.mongo2")
        public MongoProperties mongo2MongoProperties() { 
            return new MongoProperties();
        }
    
        @Bean(name = "mongo2Template")
        public MongoTemplate mongo2MongoTemplate() throws Exception { 
            return new MongoTemplate(mongo2Factory(mongo2MongoProperties()));
        }
    
        @Bean
        public MongoDbFactory mongo2Factory(MongoProperties mongoProperties) throws Exception    { 
            return new SimpleMongoDbFactory(new MongoClientURI(mongo2MongoProperties().getUri()));
        }
    }

1.  创建Controller进行测试
    
    在上述指定的Repository地址下，编写Mongo1Repository和Mongo2Repository接口（注意继承MongoRepository）
    
    下述代码为测试代码：

package com.example.multidatabase.controller;
    
    import com.example.multidatabase.entity.Status;
    import com.example.multidatabase.repository.mongo1.Mongo1Repository;
    import com.example.multidatabase.repository.mongo2.Mongo2Repository;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.data.mongodb.core.MongoTemplate;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.List;
    
    /** * @author QuS * @date 2020/1/3 11:25 */
    @RestController
    public class DoubleMongoDbController { 
    
        @Autowired
        private Mongo1Repository mongo1Repository;
    
        @Autowired
        private Mongo2Repository mongo2Repository;
    
        @Autowired
        @Qualifier("mongo1Template")
        private MongoTemplate mongo1Template;
    
        @Autowired
        @Qualifier("mongo2Template")
        private MongoTemplate mongo2Template;
    
        @RequestMapping(value = "/mongo1Query")
        public List<Status> mongo1Query() { 
            List<Status> all = mongo1Repository.findAll();
            return all;
        }
    
        @RequestMapping(value = "/mongo2Query")
        public List<Status> mongo2Query() { 
            List<Status> all = mongo2Repository.findAll();
            return all;
        }
        
        /** * ---------------------------------------------- * Template部分 * ---------------------------------------------- */
        @RequestMapping(value = "/mongo1TemplateQuery")
        public List<Status> mongo1TemplateQuery() { 
            List<Status> all = mongo1Template.findAll(Status.class);
            return all;
        }
        
        @RequestMapping(value = "/mongo2TemplateQuery")
        public List<Status> mongoTemplate2Insert() { 
            List<Status> all = mongo2Template.findAll(Status.class);
            return all;
        }
    }

### 注意 ###

此处注入mongo1Template和mongo2Template时，如果采用 @Autowired的注入方式，一定要使用@Qualifier()注解指定注入的bean的名字。如果只采用了@Autowired注入方式，则mongo1Template和mongo2Template都相当于注入了mongoTemplate，则都将操作同一个主库。