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>