springboot+mybatis 多類型資料庫+多資料源開發

前言
- 項目支援
- application.properties配置
- mybatis-config.xml全局配置
- 資料庫類型配置(DataSourceType.java)
- 多資料源配置
- - sample資料源配置
  - example資料源配置
- 多資料庫公共函數配置(檔案位置：mapper/xxx/xx.xml)
- sample的mapper配置(位置：mapper/sample/xx.xml)
- example的mapper配置(位置：mapper/example/xx.xml)

前言

太懶，就不整理完整的代碼了，隻把需要注意的關鍵部分貼出來。

項目支援

<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>

springboot2.x mybatis 多資料庫開發前言

springboot+mybatis 多類型資料庫+多資料源開發

前言

項目支援

application.properties配置

mybatis-config.xml全局配置

資料庫類型配置(DataSourceType.java)

多資料源配置

sample資料源配置

example資料源配置

多資料庫公共函數配置(檔案位置：mapper/xxx/xx.xml)

sample的mapper配置(位置：mapper/sample/xx.xml)

example的mapper配置(位置：mapper/example/xx.xml)

繼續閱讀

關于Springboot中JSCH使用說明1. JSCH簡介2. JSCH依賴3. 使用方法4. 使用連接配接池

路徑錯誤Maven：Non-resolvable parent POM: Failure to find錯誤

jsch連接配接伺服器執行指令傳回的資料中文亂碼問題問題分析解決

myeclipse 搭建mybatis架構時報錯（1）Communications link failure 通訊連結故障

springboot項目時間不一緻解決方案一、springboot項目時間不一緻解決方案

spring mvc 頁面傳遞數組背景接收方式spring mvc 頁面傳遞數組背景接收方式

Swagger-UI的使用一、添加依賴二、建立配置類三、注解

将swagger-ui中的接口導入postman

聊一聊Swagger-UI的那些事情軟體簡介Swagger 原生Bootstrap-ui Bootstrap的UIKnife4j Bootstrap的更新版的UI

springboot使用 swagger-ui 2.10.5 有關版本更新帶來的問題

微服務-性能壓測\緩存redis和分布式鎖redisson和SpringCache

MyBatis學習筆記Mybatis學習筆記（maven、Idea版）

java 8 Lamda 求 List 中 Bigdecimal類型的各種值求最大值求最小值求和求平均值

(SpringBoot)日志種類：log、monitor、access、out、gc、backup

用mybatis的generator插件在項目中自動生成dao及entity

從mybatis到mybatis plus(mp)