SpringBoot&Mybatis多数据源连接

前言： 项目中有时候会需要同时连接多个数据源，例如做数据迁移、数据备份，或者一些其他的业务功能，这时候就需要对项目进行多数据源配置。

多数据源连接方案

添加依赖

确保在pom.xml文件中添加必要的依赖，包括Spring Boot Starter、MyBatis Starter以及数据库驱动程序等。

<!-- Spring Boot Starter for JDBC -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

<!-- MyBatis Starter -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>${mybatis.version}</version>
</dependency>

<!-- 数据库驱动程序，例如MySQL -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>${mysql.version}</version>
</dependency>

配置数据源

在application.properties或application.yml中配置多个数据源的连接信息。

# 数据源1配置
spring.datasource.datasource1.url=jdbc:mysql://localhost:3306/db1
spring.datasource.datasource1.username=username1
spring.datasource.datasource1.password=password1

# 数据源2配置
spring.datasource.datasource2.url=jdbc:mysql://localhost:3306/db2
spring.datasource.datasource2.username=username2
spring.datasource.datasource2.password=password2

创建数据源配置类

创建一个配置类来配置多个数据源。

@Configuration
public class DataSourceConfig {

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

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

配置MyBatis SqlSessionFactory

针对每个数据源创建对应的SqlSessionFactory。

@Configuration
@MapperScan(basePackages = "com.example.mapper1", sqlSessionFactoryRef = "sqlSessionFactory1")
public class MyBatisConfig1 {

    @Bean
    public SqlSessionFactory sqlSessionFactory1(@Qualifier("datasource1") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        // 设置Mapper文件位置
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper1/*.xml"));
        return factoryBean.getObject();
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate1(@Qualifier("sqlSessionFactory1") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

对于第二个数据源，创建类似的配置类，只需修改配置内容以及相应的Mapper文件位置即可。

编写Mapper接口和对应的XML文件

创建Mapper接口并编写对应的SQL语句，在XML文件中定义这些SQL语句的实现。

// 示例Mapper接口
package com.example.mapper1;

import com.example.model.Entity1;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface Entity1Mapper {
    Entity1 getEntityById(Long id);
    // 其他方法...
}

<!-- 示例Mapper XML -->
<!-- src/main/resources/mapper1/Entity1Mapper.xml -->
<mapper namespace="com.example.mapper1.Entity1Mapper">
    <select id="getEntityById" resultType="com.example.model.Entity1">
        SELECT * FROM entity1 WHERE id = #{id}
    </select>
    <!-- 其他SQL语句的定义... -->
</mapper>

使用多数据源

在需要使用数据源的服务类中，注入不同的Mapper，并在需要的时候指定使用哪个数据源。

@Service
public class SomeService {

    private final Entity1Mapper entity1Mapper;
    private final Entity2Mapper entity2Mapper;

    public SomeService(@Qualifier("sqlSessionTemplate1") SqlSessionTemplate sqlSessionTemplate1,
                       @Qualifier("sqlSessionTemplate2") SqlSessionTemplate sqlSessionTemplate2) {
        this.entity1Mapper = sqlSessionTemplate1.getMapper(Entity1Mapper.class);
        this.entity2Mapper = sqlSessionTemplate2.getMapper(Entity2Mapper.class);
    }

    public Entity1 getEntity1ById(Long id) {
        return entity1Mapper.getEntityById(id);
    }

    public Entity2 getEntity2ById(Long id) {
        return entity2Mapper.getEntityById(id);
    }
}

数据迁移工具提取出的多数据源连接方案

配置启动类

禁用数据源自动配置

@EnableCaching
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
@ComponentScan("com.thunisoft.DataMigrationTool")
public class DataMigrationToolApplication {
    public static void main(String[] args) {
    	SpringApplication.run(DataMigrationToolApplication.class, args);
    }
}

配置文件

db1:
  host: 172.16.192.255
  port: 5236
  username: SYSDBA
  password: ENCRYPT#GqNuVLGLKao=
  schema: DB_ZKR
db2:
  host: 172.16.192.255
  port: 5236
  username: SYSDBA
  password: ENCRYPT#GqNuVLGLKao=
  schema: DB_WRIT

spring:
  datasource:
    first:
      name: dataSource1
      url: jdbc:dm://${db1.host}:${db1.port}/${db1.schema}
      username: ${db1.username}
      password: ${db1.password}
      driverClassName: dm.jdbc.driver.DmDriver
    second:
      name: dataSource2
      url: jdbc:dm://${db2.host}:${db2.port}/${db2.schema}
      username: ${db2.username}
      password: ${db2.password}
      driverClassName: dm.jdbc.driver.DmDriver

配置实体类

以数据源1为例，数据源2需要再创建一个相同的类

@Data
@Component
@ConfigurationProperties(prefix = "spring.datasource.first")
public class FirstDataBaseProperties {
    String url;
    String username;
    String password;
    String driverClassName;
}

数据源配置类

以数据源1为例，数据源2需要再创建一个相同的类

但是需要注意：需要使用@Primary指定一个主数据源，只有一个数据源添加这个注解

注意：
这里配置了数据源扫描mapper文件的路径，实际使用时需注意mapper文件放在对应的目录下
classpath:mapper/first/.xml;
classpath:mapper/second/.xml;

@Configuration
@MapperScan(basePackages = "com.thunisoft.DataMigrationTool.mapper.first",sqlSessionTemplateRef ="firstSqlSessionTemplate")
public class FirstDataSourceConfig {

    @Autowired
    private FirstDataBaseProperties prop;
    
    /**本数据源扫描的mapper路径*/
    static final String MAPPER_LOCATION = "classpath:mapper/first/*.xml";

    //    创建数据源
    @Bean(name = "firstDS")
    @Primary
    public DataSource getFirstDataSource() {
        DataSource build = DataSourceBuilder.create()
                .driverClassName(prop.driverClassName)
                .url(prop.url)
                .username(prop.username)
                .password(prop.password)
                .build();
        return build;
    }

    // 创建SessionFactory
    @Bean(name = "firstSqlSessionFactory")
    @Primary
    public SqlSessionFactory firstSqlSessionFactory(@Qualifier("firstDS") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);

        ResourcePatternResolver resolver=new PathMatchingResourcePatternResolver();
        try{
            bean.setMapperLocations(resolver.getResources("classpath*:/mapper/first/*.xml"));
//            bean.setConfigLocation(resolver.getResource("classpath:/mybatis-config.xml"));
            return bean.getObject();
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }

    // 创建事务管理器
    @Bean("firstTransactionManger")
    @Primary
    public DataSourceTransactionManager firstTransactionManger(@Qualifier("firstDS") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    // 创建SqlSessionTemplate
    @Bean(name = "firstSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate firstSqlSessionTemplate(@Qualifier("firstSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    private Class getType(String type) {
        try {
            return Class.forName(type);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return null;
    }

共享交换项目提取出的多数据源连接方案（比较全的代码实现）

配置文件

spring:
  datasource:
    gaussdb:
      name: gaussdb
      url: jdbc:gaussdb://xxx.xxx.xxx.xxx:xxxxx/sjzt
      username: root
      password: xxxxxx
      driverClassName: com.huawei.gauss200.jdbc.Driver
      type: com.alibaba.druid.pool.DruidDataSource
    abase:
      name: abase
      url: jdbc:log4jdbc:ArteryBase://xxx.xxx.xxx.xxx:xxxx/db_gxpt?currentSchema=db_tse
      username: root
      password: xxxxx
      driverClassName: net.sf.log4jdbc.sql.jdbcapi.DriverSpy
      type: com.alibaba.druid.pool.DruidDataSource

配置文件实体类

@Data
@Component
@ConfigurationProperties(prefix = "spring.datasource.gaussdb")
public class GaussDBDataBaseProperties {
    String name;
    String url;
    String username;
    String password;
    String driverClassName;
}

@Data
@Component
@ConfigurationProperties(prefix = "spring.datasource.abase")
public class AbaseDataBaseProperties {
    String name;
    String url;
    String username;
    String password;
    String driverClassName;
}

数据源配置类

主数据源，必须使用@Primary指定一个主数据源

@Configuration
@MapperScan(basePackages = "com.thunisoft.sjzt.tyjkpt.mapper.gaussdb",sqlSessionTemplateRef ="gaussDBSqlSessionTemplate")
public class GaussDBDataSourceConfig {

    @Autowired
    private GaussDBDataBaseProperties prop;

    /**
     * 本数据源扫描的mapper路径
     */
    static final String MAPPER_LOCATION = "classpath:mapper/gaussdb/*.xml";

    //    创建数据源
    @Bean(name = "gaussDB")
    @Primary
    public DataSource getGaussDBDataSource() {
        DataSource build = DataSourceBuilder.create()
                .driverClassName(prop.driverClassName)
                .url(prop.url)
                .username(prop.username)
                .password(prop.password)
                .build();
        return build;
    }

    // 创建SessionFactory
    @Bean(name = "gaussDBSqlSessionFactory")
    @Primary
    public SqlSessionFactory gaussDBSqlSessionFactory(@Qualifier("gaussDB") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);

        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        try {
            bean.setMapperLocations(resolver.getResources("classpath*:/mapper/gaussdb/*.xml"));
            return bean.getObject();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    // 创建事务管理器
    @Bean("gaussDBTransactionManger")
    @Primary
    public DataSourceTransactionManager gaussDBTransactionManger(@Qualifier("gaussDB") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    // 创建SqlSessionTemplate
    @Bean(name = "gaussDBSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate gaussDBSqlSessionTemplate(@Qualifier("gaussDBSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean(name = "gaussTemplate")
    @Primary
    public JdbcTemplate gaussTemplate(@Qualifier("gaussDB") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

}

非主数据源

@Configuration
@MapperScan(basePackages = "com.thunisoft.sjzt.tyjkpt.mapper.abase",sqlSessionTemplateRef ="abaseSqlSessionTemplate")
public class AbaseDataSourceConfig {

    @Autowired
    private AbaseDataBaseProperties prop;

    /**
     * 本数据源扫描的mapper路径
     */
    static final String MAPPER_LOCATION = "classpath:mapper/abase/*.xml";

    //    创建数据源
    @Bean(name = "abase")
    public DataSource getAbaseDataSource() {
        DataSource build = DataSourceBuilder.create()
                .driverClassName(prop.driverClassName)
                .url(prop.url)
                .username(prop.username)
                .password(prop.password)
                .build();
        return build;
    }

    // 创建SessionFactory
    @Bean(name = "abaseSqlSessionFactory")
    public SqlSessionFactory abaseSqlSessionFactory(@Qualifier("abase") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);

        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        try {
            bean.setMapperLocations(resolver.getResources("classpath*:/mapper/abase/*.xml"));
            return bean.getObject();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    // 创建事务管理器
    @Bean("abaseTransactionManger")
    public DataSourceTransactionManager abaseTransactionManger(@Qualifier("abase") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    // 创建SqlSessionTemplate
    @Bean(name = "abaseSqlSessionTemplate")
    public SqlSessionTemplate abaseSqlSessionTemplate(@Qualifier("abaseSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean(name = "abaseTemplate")
    public JdbcTemplate abaseTemplate(@Qualifier("abase") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

}

启动类

禁用SpringBoot的数据源自动配置

@EnableCaching
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
@MapperScan({"com.thunisoft.sjzt.tyjkpt.mapper.*"})
public class MainApplication {

	/**
     * @description 主类
     * @param args 启动参数
     */
    public static void main(String[] args) {
    	SpringApplication.run(MainApplication.class, args);
    }
}

具体使用

• Mapper.java文件和Mapper.xml文件必须放到数据源配置类中指定的路径下，否则会报错。
• 如果前面不指定具体位置的话，也可以使用下面的方式指定数据源

@Service
public class YourService {

    private final JdbcTemplate primaryJdbcTemplate;

    public YourService(@Qualifier("primaryJdbcTemplate") JdbcTemplate primaryJdbcTemplate) {
        this.primaryJdbcTemplate = primaryJdbcTemplate;
    }

    // ... 其他代码 ...
}

文书服务提取出的多数据源连接方案

配置文件

spring.datasource.ds1.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.datasource.ds1.url=jdbc:log4jdbc:ArteryBase://${wts.base.database.url}/db_tpl?currentSchema=db_tpl&ApplicationName=db_tpl&Charset=utf8
spring.datasource.ds1.username=${wts.base.database.username}
spring.datasource.ds1.password=${wts.base.database.password}
spring.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.ds1.maxActive=200
spring.datasource.ds1.minIdle=30

spring.datasource.ds2.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.datasource.ds2.url=jdbc:log4jdbc:ArteryBase://${wts.base.database.url}/db_wts_pub?currentSchema=db_wts_pub&ApplicationName=db_wts_pub&Charset=utf8
spring.datasource.ds2.username=${wts.base.database.username}
spring.datasource.ds2.password=${wts.base.database.password}
spring.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.ds2.maxActive=50
spring.datasource.ds2.minIdle=15

spring.datasource.ds.driver-class-name=dm.jdbc.driver.DmDriver
spring.datasource.ds.url=jdbc:dm://${wts.base.database.dm.url}?DAMENG&Charset=utf8
spring.datasource.ds.username=${wts.base.database.dm.username}
spring.datasource.ds.password=${wts.base.database.dm.password}

配置文件实体

@ConfigurationProperties(prefix = "spring.datasource")
public class DataSourceProperties implements BeanClassLoaderAware, InitializingBean {
    private ClassLoader classLoader;
    private String name;
    private boolean generateUniqueName;
    private Class<? extends DataSource> type;
    private String driverClassName;
    private String url;
    private String username;
    private String password;
    private String jndiName;
    ……
    ……
    ……
}

数据源配置类

/**
 * Mybatis-plus 主数据源dsDBDis配置
 * 多数据源配置依赖数据源配置
 */
@Configuration
@MapperScan(basePackages ="com.thunisoft.writDispatch.server.mybatis.mapper", sqlSessionTemplateRef  = "dsDBDisSqlSessionTemplate")
public class MybatisPlusConfig4DBDis {

    /**
     * 数据源属性配置
     */
    @Primary
    @Bean(name = "dsDBDisDataSourceProperties")
    @ConfigurationProperties(prefix = "spring.datasource.ds1")
    @Conditional(AbaseDataSourceCondition.class)
    public DataSourceProperties dsDBDisDataSourcseProperties() {
        return new DataSourceProperties();
    }

    /**
     * 数据源的datasource
     * @param dataSourceProperties dataSourceProperties
     * @return datasource
     */
    @Primary
    @Bean(name = "dsDBDisDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.ds1")
    @Conditional(AbaseDataSourceCondition.class)
    public DataSource dsDBDisDataSource(@Qualifier("dsDBDisDataSourceProperties") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

    /**
     * DM数据源属性配置
     */
    @Primary
    @Bean(name = "dsDBDisDataSourceProperties")
    @ConfigurationProperties(prefix = "spring.datasource.ds")
    @Conditional(DMDataSourceCondition.class)
    public DataSourceProperties dmDsDBDisDataSourcseProperties() {
        return new DataSourceProperties();
    }

    /**
     * DM数据源的datasource
     * @param dataSourceProperties dataSourceProperties
     * @return datasource
     */
    @Primary
    @Bean(name = "dsDBDisDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.ds")
    @Conditional(DMDataSourceCondition.class)
    public DataSource dmDsDBDisDataSource(@Qualifier("dsDBDisDataSourceProperties") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

    /**
     * jdbc模板
     * @param dataSource dataSource
     * @return jdbc模板
     */
    @Primary
    @Bean(name = "dsDBDisJdbcTemplate")
    public JdbcTemplate dsDBDisJdbcTemplate(@Qualifier("dsDBDisDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    /**
     * SqlSessionFactory
     * @param dataSource dataSource
     * @return SqlSessionFactory
     * @throws Exception Exception
     */
    @Primary
    @Bean("dsDBDisSqlSessionFactory")
    public SqlSessionFactory dsDBDisSqlSessionFactory(@Qualifier("dsDBDisDataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(dataSource);
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setDefaultScriptingLanguage(MybatisXMLLanguageDriver.class);
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        sqlSessionFactory.setConfiguration(configuration);
        sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().
                getResources("classpath*:com/thunisoft/writDispatch/server/mybatis/**/*.java"));
        sqlSessionFactory.setPlugins(new Interceptor[]{
                new PaginationInterceptor()
        });
        return sqlSessionFactory.getObject();
    }

    /**
     * 事务管理
     * @param dataSource dataSource
     * @return DataSourceTransactionManager
     */
    @Primary
    @Bean(name = "dsDBDisTransactionManager")
    public DataSourceTransactionManager dsDBDisTransactionManager(
            @Qualifier("dsDBDisDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * SqlSessionTemplate
     * @param sqlSessionFactory  sqlSessionFactory
     * @return SqlSessionTemplate
     */
    @Primary
    @Bean(name = "dsDBDisSqlSessionTemplate")
    public SqlSessionTemplate dsDBDisSqlSessionTemplate(
            @Qualifier("dsDBDisSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

MyBatis动态Sql查询语句拼接注解写法

1. 注解形式

在Mapper类的方法上直接加@Select，@Update，@Delete，@Insert注解

2. xml形式

通过interface接口与mapper.xml的映射，找到对应的xml拼接的sql语句

3. @SelectProvider形式

@SelectProvider可以用在复杂SQL语句拼接，替代XML写法，比如条件查询等，增改删也有对应的@InsertProvider、@UpdateProvider、@DeleteProvider

type 属性用于指定获取sql语句的指定类
method 属性用于指定类中要执行获取sql语句的方法

@Mapper
@Component
public interface ZkrMapper {
    @SelectProvider(type = SqlBuilder.class, method = "select")
    List<WritZkr> select(String schema);
}

public class SqlBuilder {
    public String select(String schema){
        return "SELECT * FROM "+schema+".DOC_INFO";
    }
}

内部类的方式实现

@Mapper  
public interface MybatisDao {  
    //使用UserDaoProvider类的findUserById方法来生成sql  
    @SelectProvider(type = UserDaoProvider.class, method = "findUserById")  
    public List<User> findUserById(User user);  
      
    class UserDaoProvider {  
        public String findUserById(User user) {  
            String sql = "SELECT * FROM user";  
            if(user.getId()!=null){  
                sql += " where id = #{id}";  
            }  
            return sql;  
        }  
    }
}

4. 动态脚本SQL

脚本前后添加<script></script>标签，里面可以使用循环、判断等，更加方便了编写复杂SQL语句的能力

 /**
     * 批量添加任务
     *
     * @param tasks
     */
    @Override
    @Insert("<script>" +
            "INSERT INTO DB_YWST.T_WS_PL_TASK(C_BH, C_ZTBH, C_REQ_BH, C_YWBH, N_PLZXYWLX, N_ZXZT, " +
            "N_CSCS, J_SJ, DT_CJSJ, N_ZHXGFS, DT_ZHGXSJ) VALUES " +
            "<foreach collection =\"list\" item=\"item\" index= \"index\" separator =\",\"> " +
            "(#{item.cBh}, #{item.cZtbh}, #{item.cReqBh}, #{item.cYwbh}, #{item.nPlzxywlx} ,#{item.nZxzt}, " +
            "#{item.nCscs}, #{item.jSj}::json, #{item.dtCjsj}, #{item.nZhxgfs}, #{item.dtZhgxsj}) " +
            "</foreach > " +
            "</script>")
    int insertBatchTasks(List<TWsPlTask> tasks);

5. 结构化SQL形式(MyBatis 3支持)

XML动态SQL
结构化SQL语句构建器

public String selectUserById(Map<String, Object> para){
    return new SQL(){{
        SELECT("*");
        FROM("base_user");
        WHERE("id="+para.get("id"));
        if(StringUtils.isNotBlank((String)para.get("username"))){
            WHERE("username="+para.get("username"));
        }
    }}.toString();
}