springboot2.x mybatis 多数据库开发

前言

太懒，就不整理完整的代码了，只把需要注意的关键部分贴出来。

项目支持

	<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <dependencies>
    	<dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.9</version>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.4.0-atlassian-hosted</version>
        </dependency>
    </dependencies>

application.properties配置

#####=========================== 设置服务端口/项目路径 ===========================#####
server.port=8081
#server.servlet.context-path=/provider

#####=========================== 数据库(sample)相关配置 ===========================#####
spring.datasource.druid.sample.db-type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.sample.name=rbac_db
spring.datasource.druid.sample.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.druid.sample.url=jdbc:oracle:thin:@localhost:1521:ORCL
spring.datasource.druid.sample.username=SAMPLE_TEST
spring.datasource.druid.sample.password=SAMPLE_TEST
#配置初始化大小/最小/最大
spring.datasource.druid.sample.initial-size=10
spring.datasource.druid.sample.min-idle=10
spring.datasource.druid.sample.max-active=30
#获取连接等待超时时间
spring.datasource.druid.sample.max-wait=60000
#间隔多久进行一次检测，检测需要关闭的空闲连接
spring.datasource.druid.sample.time-between-eviction-runs-millis=60000
#一个连接在池中最小生存的时间
spring.datasource.druid.sample.min-evictable-idle-time-millis=300000
spring.datasource.druid.sample.validation-query=SELECT 1 FROM DUAL
spring.datasource.druid.sample.test-while-idle=true
spring.datasource.druid.sample.test-on-borrow=false
spring.datasource.druid.sample.test-on-return=false
#打开PSCache，并指定每个连接上PSCache的大小;oracle设为true，mysql设为false.分库分表较多推荐设置为false
spring.datasource.druid.sample.pool-prepared-statements=false
spring.datasource.druid.sample.max-pool-prepared-statement-per-connection-size=30

#####=========================== 数据库(example)相关配置 ===========================#####
spring.datasource.druid.example.db-type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.example.name=signature_db
spring.datasource.druid.example.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.druid.example.url=jdbc:oracle:thin:@localhost:1521:ORCL
spring.datasource.druid.example.username=EXAMPLE_TEST
spring.datasource.druid.example.password=EXAMPLE_TEST
#配置初始化大小/最小/最大
spring.datasource.druid.example.initial-size=10
spring.datasource.druid.example.min-idle=10
spring.datasource.druid.example.max-active=30
#获取连接等待超时时间
spring.datasource.druid.example.max-wait=60000
#间隔多久进行一次检测，检测需要关闭的空闲连接
spring.datasource.druid.example.time-between-eviction-runs-millis=60000
#一个连接在池中最小生存的时间
spring.datasource.druid.example.min-evictable-idle-time-millis=300000
spring.datasource.druid.example.validation-query=SELECT 1 FROM DUAL
spring.datasource.druid.example.test-while-idle=true
spring.datasource.druid.example.test-on-borrow=false
spring.datasource.druid.example.test-on-return=false
#打开PSCache，并指定每个连接上PSCache的大小;oracle设为true，mysql设为false.分库分表较多推荐设置为false
spring.datasource.druid.example.pool-prepared-statements=false
spring.datasource.druid.example.max-pool-prepared-statement-per-connection-size=30

mybatis-config.xml全局配置

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>

    <settings>
        <!-- 指定发现自动映射目标未知列（或者未知属性类型）的行为;默认:NONE
             NONE: 不做任何反应;
             WARNING: 输出提醒日志 ('org.apache.ibatis.session.AutoMappingUnknownColumnBehavior' 的日志等级必须设置为 WARN)）
             FAILING: 映射失败 (抛出 SqlSessionException)
              -->
        <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
        <!-- 是否开启自动驼峰命名规则（camel case）映射，即从经典数据库列名 A_COLUMN 到经典 Java 属性名 aColumn 的类似映射。 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!-- 允许值为null -->
        <setting name="jdbcTypeForNull" value="NULL"/>
    </settings>

</configuration>

数据库类型配置(DataSourceType.java)

import org.apache.ibatis.mapping.DatabaseIdProvider;
import org.apache.ibatis.mapping.VendorDatabaseIdProvider;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.context.annotation.Bean;

import java.util.Properties;

/*实现多数据源时,此配置方式无效,需要数据源实现类中进行手动引用*/
@SpringBootConfiguration
public class DataSourceType {

    /**
     * 自动识别使用的数据库类型
     * 在mapper.xml中databaseId的值就是跟这里对应，
     * 如果没有databaseId选择则说明该sql适用所有数据库
     * */
    @Bean
    public DatabaseIdProvider getDatabaseIdProvider(){
        DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
        Properties properties = new Properties();
        properties.setProperty("Oracle","oracle");
        properties.setProperty("MySQL","mysql");
        properties.setProperty("DB2","db2");
        properties.setProperty("Derby","derby");
        properties.setProperty("H2","h2");
        properties.setProperty("HSQL","hsql");
        properties.setProperty("Informix","informix");
        properties.setProperty("MS-SQL","ms-sql");
        properties.setProperty("PostgreSQL","postgresql");
        properties.setProperty("Sybase","sybase");
        properties.setProperty("Hana","hana");
        databaseIdProvider.setProperties(properties);
        return databaseIdProvider;
    }
}

多数据源配置

sample数据源配置

@Primary 标识默认使用此连接

import com.alibaba.druid.pool.DruidDataSource;
import com.common.configuration.DataSourceType;
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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = {"com.sample.*.dao"},sqlSessionTemplateRef = "sampleSqlSessionTemplate")
public class SampleDataSourceConfig {

    @Bean(name = "sampleDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid.sample")
    @Primary
    public DataSource sampleDataSource() {
        return new DruidDataSource();//使用druid连接池
    }

    @Bean(name = "sampleSqlSessionFactory")
    @Primary
    public SqlSessionFactory sampleSqlSessionFactory(@Qualifier("sampleDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        /*多数据源配置时,mybatis配置需要使用此处配置进行*/
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/**/*.xml"));
        bean.setTypeAliasesPackage("com.beans");
        bean.setConfigLocation(new PathMatchingResourcePatternResolver().getResource("mapperconfig/mybatis-config.xml"));
        bean.setDatabaseIdProvider(new DataSourceType().getDatabaseIdProvider());//加载多数据库类型的配置
        return bean.getObject();
    }

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

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

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

example数据源配置

import com.alibaba.druid.pool.DruidDataSource;
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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = {"com.example.*.dao"},sqlSessionTemplateRef = "exampleSqlSessionTemplate")
public class ExampleDataSourceConfig {

    @Bean(name = "exampleDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid.example")
    public DataSource exampleDataSource() {
        return new DruidDataSource();//使用druid连接池
    }

    @Bean(name = "exampleSqlSessionFactory")
    public SqlSessionFactory exampleSqlSessionFactory(@Qualifier("exampleDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        /*多数据源配置时,mybatis配置需要使用此处配置进行*/
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/**/*.xml"));
        bean.setTypeAliasesPackage("com.beans");
        bean.setConfigLocation(new PathMatchingResourcePatternResolver().getResource("mapperconfig/mybatis-config.xml"));
        bean.setDatabaseIdProvider(new DataSourceType().getDatabaseIdProvider());//加载多数据库类型的配置
        return bean.getObject();
    }

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

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

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

多数据库公共函数配置(文件位置：mapper/xxx/xx.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.common" >
    <resultMap id="dataBean" type="com.common.bean.DataBean">
        <result property="operId" column="CREATE_USER_ID" javaType="java.lang.String" jdbcType="NVARCHAR"/>
    </resultMap>

    <sql id="getSystemDate" databaseId="mysql">NOW()</sql>
    <sql id="getSystemDate" databaseId="oracle">SYSDATE</sql>

    <!-- DISTINCT用于祛除重复值,引用sql是注意:使用mysql的拼接函数时,如果拼接字符串第一位或仅最后一位是空值,会多一个分隔符,如:,1,2; 10,11, -->
    <sql id="getSystemConcatBegin" databaseId="mysql">GROUP_CONCAT(DISTINCT </sql>
    <sql id="getSystemConcatBegin" databaseId="oracle">WM_CONCAT(</sql>

    <!-- ORACLE中拼接字符串使用的英文逗号","作为分隔符,mysql中保持一致,请勿擅自改动 -->
    <sql id="getSystemConcatEnd" databaseId="mysql"> SEPARATOR ',')</sql>
    <sql id="getSystemConcatEnd" databaseId="oracle">)</sql>

</mapper>

sample的mapper配置(位置：mapper/sample/xx.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.sample.sample.dao.ISampleDao" >

	<!-- 支持对SampleBean的父类进行赋值,其中“com.common.dataBean”在“多数据库公共函数配置”有相关配置 -->
    <resultMap id="resultBean" type="ResultSampleBean" extends="com.common.dataBean">

    </resultMap>

	<!-- 没有多地引用,没必要单独提出来,此处单独提出来只是为了方便初学者学习 -->
    <sql id="totalSql">
        SELECT COUNT(ID) FROM TB_SAMPLE_INFO
        <choose>
            <when test="deleteFlg!=null and deleteFlg!=''">
                <choose>
                    <when test="deleteFlg=='10'">
                        WHERE 1=1
                    </when>
                    <otherwise>
                        WHERE DELETE_FLG = #{deleteFlg}
                    </otherwise>
                </choose>
            </when>
            <otherwise>
                WHERE DELETE_FLG = 1
            </otherwise>
        </choose>
        <if test="dataState!=null and dataState!=''">
            AND DATA_STATE=#{dataState}
        </if>
    </sql>

    <select id="total" parameterType="SampleBean" resultType="java.lang.Long">
        <include refid="totalSql"/>
    </select>

</mapper>

example的mapper配置(位置：mapper/example/xx.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.example.dao.IExampleDao" >

	<!-- 支持对ExampleBean的父类进行赋值,其中“com.common.dataBean”在“多数据库公共函数配置”有相关配置 -->
    <resultMap id="resultBean" type="ResultExampleBean" extends="com.common.dataBean">

    </resultMap>

    <select id="total" parameterType="ExampleBean" resultType="java.lang.Long">
        SELECT COUNT(ID) FROM TB_EXAMPLE_INFO
        <choose>
            <when test="deleteFlg!=null and deleteFlg!=''">
                <choose>
                    <when test="deleteFlg=='10'">
                        WHERE 1=1
                    </when>
                    <otherwise>
                        WHERE DELETE_FLG = #{deleteFlg}
                    </otherwise>
                </choose>
            </when>
            <otherwise>
                WHERE DELETE_FLG = 1
            </otherwise>
        </choose>
        <if test="dataState!=null and dataState!=''">
            AND DATA_STATE=#{dataState}
        </if>
    </select>

</mapper>