idea springboot 多数据源（mysql,sqlServer）

github源码地址：https://github.com/heylilang/manyJdbc.git

 

• 先用idea创建springboot项目导入相关maven包

 

• 项目结构如下：

 

• application.yml文件内容：

spring:
  datasource:
    test1:
      jdbc-url: jdbc:mysql://localhost:3306/数据库名?serverTimezone=CTT&useUnicode=true&characterEncoding=utf8
      driverClassName: com.mysql.cj.jdbc.Driver
      username: 用户名
      password: 密码
    test2:
      jdbc-url: jdbc:sqlserver://ip:端口;DatabaseName=数据库名
      driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
      username: 用户名
      password: 密码

mybatis:
  mapper-locations: classpath:*/mapper/**.xml

 

• DataSourceConfig1内容：

package com.example.demo.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
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 org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.example.demo.mapper.test", sqlSessionFactoryRef = "test1SqlSessionFactory")
public class DataSourceConfig1 {

    // 将这个对象放入Spring容器中
    @Bean(name = "test1DataSource")
    // 表示这个数据源是默认数据源
    @Primary
    // 读取application.properties中的配置参数映射成为一个对象
    // prefix表示参数的前缀
    @ConfigurationProperties(prefix = "spring.datasource.test1")
    public DataSource getDateSource1()
    {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "test1SqlSessionFactory")
    // 表示这个数据源是默认数据源
    @Primary
    // @Qualifier表示查找Spring容器中名字为test1DataSource的对象
    public SqlSessionFactory test1SqlSessionFactory(@Qualifier("test1DataSource") DataSource datasource)
            throws Exception
    {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        bean.setMapperLocations(
                // 设置mybatis的xml所在位置
                new PathMatchingResourcePatternResolver().getResources("classpath:mapper/test/*.xml"));
        return bean.getObject();
    }

    @Bean("test1SqlSessionTemplate")
    // 表示这个数据源是默认数据源
    @Primary
    public SqlSessionTemplate test1SqlSessionTemplate(
            @Qualifier("test1SqlSessionFactory") SqlSessionFactory sessionFactory)
    {
        return new SqlSessionTemplate(sessionFactory);
    }
}

 

• DataSourceConfig2内容：

package com.example.demo.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
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.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.example.demo.mapper.testTwo", sqlSessionFactoryRef = "test2SqlSessionFactory")
public class DataSourceConfig2 {
    @Bean(name = "test2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.test2")
    public DataSource getDateSource2()
    {
        return DataSourceBuilder.create().build();
    }

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

    @Bean("test2SqlSessionTemplate")
    public SqlSessionTemplate test2SqlSessionTemplate(
            @Qualifier("test2SqlSessionFactory") SqlSessionFactory sessionFactory)
    {
        return new SqlSessionTemplate(sessionFactory);
    }
}

• TestController

package com.example.demo.controller;

import com.example.demo.entity.CoinClass;
import com.example.demo.entity.Wallets;
import com.example.demo.mapper.test.CoinClassMapper;
import com.example.demo.mapper.testTwo.WalletsMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.List;

@RequestMapping("/test")
@RestController
public class TestController {

    @Autowired
    private CoinClassMapper coinClassMapper;

    @Resource
    private WalletsMapper walletsMapper;

    @RequestMapping(value = "/select",method = RequestMethod.GET)
    public void select(){
        List<CoinClass> coinClasses = coinClassMapper.selectAll();
        System.out.println(coinClasses);
    }

    @RequestMapping(value = "/select2",method = RequestMethod.GET)
    public void select2(){
        List<Wallets> wallets = walletsMapper.selectAll();
        System.out.println(wallets);
    }
}

 

• entity包： Wallets

package com.example.demo.entity;

import lombok.Data;

@Data
public class Wallets {

    private Integer walletsID;
    private String name;
}

 

• WalletsMapper

package com.example.demo.mapper.testTwo;

import com.example.demo.entity.Wallets;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface WalletsMapper {

    List<Wallets> selectAll();

}

 

• WalletsMapper.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.example.demo.mapper.testTwo.WalletsMapper">
    <resultMap id="BaseResultMap" type="com.example.demo.entity.Wallets">
        <id column="WalletsID" jdbcType="BIGINT" property="walletsID" />
        <result column="Name" jdbcType="VARCHAR" property="name" />
    </resultMap>
  <select id="selectAll" resultMap="BaseResultMap">
      SELECT
          *
      FROM
          ( SELECT *, ROW_NUMBER () OVER ( ORDER BY WalletsID ) AS RowId FROM Wallets ) AS b
      WHERE
          RowId BETWEEN 1
          AND 100
  </select>

</mapper>

 

• Controller,mapper,entity根据自己的需求创建，和单一数据库一样的，对应不同数据库建立实体和xml文件。