spring boot 双数据源配置，两种方式jdbc和mybatis（jdbcTemplate和 sqlsessionTemplate）及需要注意的问题

一、jdbcTemplate：jdbc

application.yml文件：

spring:
  application:
    name: doubledatasource
  mysql:
      datasource:
          url: jdbc:mysql://127.0.0.1:3306/user?useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true
          username: root
          password: root
          driver-class-name: com.mysql.jdbc.Driver
          type: com.alibaba.druid.pool.DruidDataSource
  sqlserver:
      datasource:
        url: jdbc:mysql://192.168.xx.xx:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true
        username: root
        password: 12345
        driver-class-name: com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource

注意：这里自定义数据源名称和datasource的位置不能写反；

pom文件：

        <dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.21</version>
		</dependency>

		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.2</version>
		</dependency>

java代码：

加载yml文件中的配置类：

@Configuration
public class DataSourceConfig {

    @Primary
    @Bean(name = "mysql")
    @Qualifier("mysql")
    @ConfigurationProperties(prefix = "spring.datasource.mysql")
    public DataSource mysqlDataSource(){

        return DataSourceBuilder.create().build();
    }

//    @Primary（主数据源配置）
    @Bean(name = "sqlserver")
    @Qualifier("sqlserver")
    @ConfigurationProperties(prefix = "spring.datasource.sqlserver")
    public DataSource sqlServerDataSource(){

        return DataSourceBuilder.create().build();
    }
}

注意：这里是两个数据源，所以只需要指定一个主数据源配置即可（指定方式：@Primary）；

jdbcTemplate类：加载已配置好的数据源

@Component
public class TemplateTest {
	@Bean(name = "mysqlJdbcTemplate")
	public JdbcTemplate primaryJdbcTemplate(@Qualifier("mysql") DataSource dataSource) {
		return new JdbcTemplate(dataSource);
	}

	@Bean(name = "sqlserverJdbcTemplate")
	public JdbcTemplate secondaryJdbcTemplate(@Qualifier("sqlserver") DataSource dataSource) {
		return new JdbcTemplate(dataSource);
	}

}

测试类：

    @Autowired
    @Qualifier("mysqlJdbcTemplate")
    protected JdbcTemplate jdbcTemplate1;

    @Autowired
    @Qualifier("sqlserverJdbcTemplate")
    protected JdbcTemplate jdbcTemplate2;

List<Map<String, Object>> queryForList = jdbcTemplate1.queryForList("select * from user");

List<Map<String, Object>> queryForList = jdbcTemplate2.queryForList("select * from test");

这里的sql只有手动添加，再改测试中注意user表和test表中的数据量不要太大哦。

二、sqlsessionTemplate：mybatis

pom文件和yml文件的配置和一里面的一致；

java代码：

mysql数据源类：

@Configuration
@MapperScan(basePackages = "com.xxx.dao.impl", sqlSessionFactoryRef = "mysqlSessionFactory")
public class MybatisMysqlConfig {

    @Primary
    @Bean(name = "mysql")
    @ConfigurationProperties(prefix = "spring.mysql.datasource")
    public DataSource mysqlDataSource() {

        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "mysqlTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("mysql") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Primary
    @Bean(name = "mysqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("mysql") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setMapUnderscoreToCamelCase(true);
        bean.setConfiguration(configuration);
        bean.setDataSource(dataSource);
        ResourcePatternResolver rsourcePatternResolver = new PathMatchingResourcePatternResolver();
        bean.setMapperLocations(rsourcePatternResolver.getResources("classpath*:com/xxx/fw/dao/*.xml"));
        return bean.getObject();
    }

    @Primary
    @Bean(name = "mysqlTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("mysqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        SqlSessionTemplate sqlSessionTemplate = new SqlSessionTemplate(sqlSessionFactory);
        return sqlSessionTemplate;
    }
}

sqlserver数据源类：

@Configuration
@MapperScan(basePackages = "com.xxx.fw.dao.impl", sqlSessionFactoryRef = "sqlserverSessionFactory")
public class MybatisSqlServerConfig {


    @Bean(name = "sqlserver")
    @ConfigurationProperties(prefix = "spring.sqlserver.datasource")
    public DataSource mysqlDataSource() {

        return DataSourceBuilder.create().build();
    }

    @Bean(name = "sqlserverTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("sqlserver") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "sqlserverSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("sqlserver") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setMapUnderscoreToCamelCase(true);
        bean.setConfiguration(configuration);
        bean.setDataSource(dataSource);
        ResourcePatternResolver rsourcePatternResolver = new PathMatchingResourcePatternResolver();
        bean.setMapperLocations(rsourcePatternResolver.getResources("classpath*:com/xxx/fw/dao/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "sqlserverTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlserverSessionFactory") SqlSessionFactory sqlSessionFactory) {
        SqlSessionTemplate sqlSessionTemplate = new SqlSessionTemplate(sqlSessionFactory);
        return sqlSessionTemplate;
    }
}

注意：这两个类中的@MapperScan(basePackages = "com.xxx.fw.dao.impl", sqlSessionFactoryRef = "sqlserverSessionFactory")，特别重要，千万别配置错误，尤其是basePackages的值，因为这里是双数据源，所以需要一个映射实现类来获取具体某个数据源的sqlsessionTemplate类；

如：MysqlMapper接口对应MysqlMapperImpl实现类；

一般我们用mybatis直接用接口包路径就行，但在这里，为了选择数据源，多加了一个实现类，且必须在basePackages中配置实现类（不能配置接口类的包路径），否则，只有一个数据源（主数据源）；

MysqlMapper接口及其实现类MysqlMapperImpl类：

public interface MysqlMapper {
    Users getUser(Long id);
}


@Repository
public class MysqlMapperImpl implements MysqlMapper {

    @Autowired
    @Resource(name = "mysqlTemplate")
    private SqlSessionTemplate sqlSessionTemplate;
    

    @Override
    public Users getUser(Long id) {
        Users o = sqlSessionTemplate.selectOne("com.hhzx.fw.dao.MysqlMapper.getUser",id);

        return o;
    }
}

xml映射文件：必须和接口类名称相同（MysqlMapper.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="com.xxx.dao.MysqlMapper">

    <resultMap id="Users" type="com.xxx.entity.Users"></resultMap>

    <select id="getUser" parameterType="java.lang.Long" resultMap="Users">
        SELECT * FROM USER WHERE id = #{id}
    </select>
    
</mapper>

SqlServerMapper接口及其实现类SqlServerMapperImpl：

public interface SqlServerMapper {

    UserCert getTest(Integer id);
}

@Repository
public class SqlServerMapperImpl implements SqlServerMapper {
	
	 @Autowired
	 @Resource(name = "sqlserverTemplate")
	 SqlSessionTemplate sqlSessionTemplate;

	
	@Override
	public UserCert getCert(Integer id) {
		UserCert o = sqlSessionTemplate.selectOne("com.hhzx.fw.dao.UserCertMapper.getCert", 
		return o;
	}
}

xml映射文件：必须和接口类名称相同（SqlServerMapper.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="com.xxx.dao.SqlServerMapper">

    <resultMap id="Test" type="com.xxx.entity.Test">

    </resultMap>


    <select id="getCert" parameterType="java.lang.Integer" resultMap="Test">
        SELECT * FROM test WHERE id = #{id}
    </select>

</mapper>

entity实体类因为字段和数据库字段相同，所以没有在resultMap标签里面具体配置；

两个实体类：Users、Test

public class Users implements Serializable {
    private Long id;
    private String name;
    private String userName;
    private Integer age;
    private Double balance;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Double getBalance() {
        return balance;
    }

    public void setBalance(Double balance) {
        this.balance = balance;
    }

    }



public class Test implements Serializable {
    private Long id;
    private String name;
    private Integer age;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }


    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    }

 

测试类：

  @Autowired
    MysqlMapper mysqlMapper;

    @Autowired
    SqlServerMapper sqlServerMapper;

    @RequestMapping(value = "/getUser")
    public Users getTowDataResource(){
        Users user = mysqlMapper.getUser(1L);
        return user;
    }

    @RequestMapping(value = "/getTest")
    public Test getSqlServerData(){
        return userCertMapper.getTest(1143242);
    }

ok，到此，jdbc双数据源和mybatis双数据源都已搞定，如果按照上述步骤，及需要注意点，完成后，不出意外，一定能测试成功；

如果有更多的意见或疑问，请在下方留言，不要忘了点赞哦；

谢谢关注；