Shulou(Shulou.com)06/02 Report--

This article mainly introduces springboot+mybatisplus+druid how to achieve multi-data sources + distributed transactions, the article is very detailed, has a certain reference value, interested friends must read it!

Jdk environment: 1.8

Springboot:2.1.3.RELEASE

Mybatisplus:3.2.0

This article mainly uses the AtomikosDataSourceBean+druid of atomikos to configure the connection pool, and the other is AtomikosNonXADataSourceBean

1. Maven should note that the mysql-connector-java version is 6.x. If you exceed this version, an exception may be reported.

Com.baomidou

Mybatis-plus-boot-starter

3.2.0

Org.springframework.boot

Spring-boot-starter-jta-atomikos

Mysql

Mysql-connector-java

6.0.6

Org.springframework.boot

Spring-boot-starter-aop

Org.projectlombok

Lombok

True

Org.springframework.boot

Spring-boot-configuration-processor

True

Com.alibaba

Druid-spring-boot-starter

1.1.10

2. The new DataSourceContextHolder.java provides methods to set, get, and clear the current data source.

Public class DataSourceContextHolder {

Private static final ThreadLocal contextHolder = new InheritableThreadLocal ()

/ * *

* set the data source

*

* @ param db

* /

Public static void setDataSource (String db) {

ContextHolder.set (db)

}

/ * *

* get the current data source

*

* @ return

* /

Public static String getDataSource () {

Return contextHolder.get ()

}

/ * *

* clear context data

* /

Public static void clear () {

ContextHolder.remove ()

}

}

3. DataSource.java data source annotations and DataSourceKeyEnum.java data source enumeration classes; and provide methods to obtain enumerations

Import java.lang.annotation.*

@ Target ({ElementType.METHOD, ElementType.TYPE})

@ Retention (RetentionPolicy.RUNTIME)

@ Documented

Public @ interface DataSource {

DataSourceKeyEnum value ()

}

Import lombok.Getter

Import org.apache.commons.lang3.StringUtils

Import java.util.Arrays

Import java.util.Collections

Import java.util.List

Public enum DataSourceKeyEnum {

MASTER ("master")

/ * *

* indicates all SLAVE. Select a SLAVE0 or SLAVE1 at random

* /

SLAVE ("slave")

SLAVE0 ("slave0")

SLAVE1 ("slave1")

@ Getter

Private String value

DataSourceKeyEnum (String value) {

This.value = value

}

Public static List getSlaveList () {

Return Arrays.asList (SLAVE0, SLAVE1)

}

/ * *

* Select according to method name

*

* @ param name method name

* /

Public static DataSourceKeyEnum getDSKeyByMethodName (String name) {

If (StringUtils.isEmpty (name)) {

Return null

}

If (name.contains ("update") | | name.contains ("delete") | | name.contains ("remove") | | name.contains ("insert")) {

Return MASTER

}

If (name.contains ("select") | | name.contains ("query") | | name.contains ("find") | | name.contains ("get")) {

List list = getSlaveList ()

Collections.shuffle (list)

Return list.get (0)

}

Return MASTER

}

/ * *

* obtain data sources according to annotations

*

* @ param dataSource

* @ return

* /

Public static DataSourceKeyEnum getDataSourceKey (DataSource dataSource) {

If (dataSource = = null) {

Return MASTER

}

If (dataSource.value ()) = = DataSourceKeyEnum.SLAVE) {

List dataSourceKeyList = DataSourceKeyEnum.getSlaveList ()

/ / FIXME is currently out of order

Collections.shuffle (dataSourceKeyList)

Return dataSourceKeyList.get (0)

} else {

Return dataSource.value ()

}

}

/ * *

* get data source enumeration based on className and Method

*

* @ param className

* @ return

* /

Public static String getByClassName (String className, Method method) {

/ / comments on the method

DataSource dataSource = AnnotationUtils.findAnnotation (method, DataSource.class)

DataSourceKeyEnum keyEnum

If (dataSource! = null) {/ / annotations exist mainly as annotations.

KeyEnum = DataSourceKeyEnum.getDataSourceKey (dataSource)

} else {

KeyEnum = DataSourceKeyEnum.getDSKeyByMethodName (method.getName ())

}

Return keyEnum.getValue ()

}

}

4. DataSourceAspect.java data source aspect class. Here we only intercept the mapper layer, including the public BaseMapper that intercepts mybatisplus.

Import lombok.extern.slf4j.Slf4j

Import org.aspectj.lang.ProceedingJoinPoint

Import org.aspectj.lang.annotation.Around

Import org.aspectj.lang.annotation.Aspect

Import org.aspectj.lang.annotation.Pointcut

Import org.aspectj.lang.reflect.MethodSignature

Import org.springframework.aop.support.AopUtils

Import org.springframework.context.annotation.Lazy

Import org.springframework.core.annotation.Order

Import org.springframework.stereotype.Component

Import java.lang.reflect.Method

Import java.lang.reflect.Type

@ Component

@ Slf4j

@ Aspect

@ Order (- 1)

Public class DataSourceAspect {

@ Pointcut ("execution (* com.admin.*.dao.*Mapper.* (..)) | | execution (* com.baomidou.mybatisplus.core.mapper.*Mapper.* (..))")

Public void pointCut () {

}

@ Around ("pointCut ()")

Public Object doBefore (ProceedingJoinPoint pjp) throws Throwable {

MethodSignature signature = (MethodSignature) pjp.getSignature ()

Method method = signature.getMethod ()

/ / the interfaces in the intercepted public BaseMapper can obtain the information of the specific implementation class in this way.

Type [] types = AopUtils.getTargetClass (pjp.getTarget ()) .getGenericInterfaces (); / / the getGenericInterfaces method can get all the interfaces implemented by the class / interface

String name = types [0] .getTypeName ()

String dataSource = DataSourceKeyEnum.getByClassName (name, method)

Log.info ("selected data source:" + dataSource)

DataSourceContextHolder.setDataSource (dataSource)

Object o=pjp.proceed ()

DataSourceContextHolder.clear ()

Return o

}

}

5. Application.yml related configuration

Spring:

Datasource:

Druid:

Master:

XaDataSourceClassName: com.alibaba.druid.pool.xa.DruidXADataSource

UniqueResourceName: master

XaDataSource:

Url: jdbc:mysql://127.0.0.1:3306/test?charset=utf8mb4&useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&serverTimezone=Asia/Shanghai

Username: root

Password: 123456

Driver-class-name: com.mysql.cj.jdbc.Driver

Initial-size: 5

Min-idle: 5

Max-active: 20

# get the connection wait timeout

Max-wait: 60000

# how often is it checked to detect idle connections that need to be closed?

Time-between-eviction-runs-millis: 60000

# minimum survival time for a connection in the pool

Min-evictable-idle-time-millis: 300000

# specify the sql query statement for connection verification when obtaining a connection

Validation-query: SELECT'x'

# verify the validity of the connection

Test-while-idle: true

# Verification when obtaining a connection will affect performance (true is not recommended)

Test-on-borrow: false

# Open PSCache and specify the size of the PSCache on each connection. Oracle is set to true,mysql and set to false. It is recommended to set it to false for more sub-libraries and tables.

Pool-prepared-statements: false

Max-pool-prepared-statement-per-connection-size: 20

# configure the filters intercepted by monitoring statistics. After the monitoring interface is removed, the sql cannot be counted. 'wall' is used for firewalls

Filters: config,wall,stat

Slave0:

XaDataSourceClassName: com.alibaba.druid.pool.xa.DruidXADataSource

UniqueResourceName: slave0

XaDataSource:

Url: jdbc:mysql://127.0.0.1:3306/test?charset=utf8mb4&useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&serverTimezone=Asia/Shanghai

Username: root

Password: 123456

Driver-class-name: com.mysql.cj.jdbc.Driver

Initial-size: 5

Min-idle: 5

Max-active: 20

# get the connection wait timeout

Max-wait: 60000

# how often is it checked to detect idle connections that need to be closed?

Time-between-eviction-runs-millis: 60000

# minimum survival time for a connection in the pool

Min-evictable-idle-time-millis: 300000

# specify the sql query statement for connection verification when obtaining a connection

Validation-query: SELECT'x'

# verify the validity of the connection

Test-while-idle: true

# Verification when obtaining a connection will affect performance (true is not recommended)

Test-on-borrow: false

# Open PSCache and specify the size of the PSCache on each connection. Oracle is set to true,mysql and set to false. It is recommended to set it to false for more sub-libraries and tables.

Pool-prepared-statements: false

Max-pool-prepared-statement-per-connection-size: 20

# configure the filters intercepted by monitoring statistics. After the monitoring interface is removed, the sql cannot be counted. 'wall' is used for firewalls

Filters: config,wall,stat

Slave1:

XaDataSourceClassName: com.alibaba.druid.pool.xa.DruidXADataSource

UniqueResourceName: slave1

XaDataSource:

Url: jdbc:mysql://127.0.0.1:3306/test?charset=utf8mb4&useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&serverTimezone=Asia/Shanghai

Username: root

Password: 123456

Driver-class-name: com.mysql.cj.jdbc.Driver

Initial-size: 5

Min-idle: 5

Max-active: 20

# get the connection wait timeout

Max-wait: 60000

# how often is it checked to detect idle connections that need to be closed?

Time-between-eviction-runs-millis: 60000

# minimum survival time for a connection in the pool

Min-evictable-idle-time-millis: 300000

# specify the sql query statement for connection verification when obtaining a connection

Validation-query: SELECT'x'

# verify the validity of the connection

Test-while-idle: true

# Verification when obtaining a connection will affect performance (true is not recommended)

Test-on-borrow: false

# Open PSCache and specify the size of the PSCache on each connection. Oracle is set to true,mysql and set to false. It is recommended to set it to false for more sub-libraries and tables.

Pool-prepared-statements: false

Max-pool-prepared-statement-per-connection-size: 20

# configure the filters intercepted by monitoring statistics. After the monitoring interface is removed, the sql cannot be counted. 'wall' is used for firewalls

Filters: config,wall,stat

Web-stat-filter:

Enabled: true

Url-pattern: / *

Exclusions: / druid/*,*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico

Session-stat-enable: true

Session-stat-max-count: 10

Stat-view-servlet:

Enabled: true

Url-pattern: / druid/*

Reset-enable: true

Login-username: admin

Login-password: admin

Jta:

Atomikos:

Properties:

Log-base-dir:.. / logs

Transaction-manager-id: txManager # take the IP address of the computer by default to ensure that the production environment value is unique

6. Copy all the code in SqlSessionTemplate.java, create it to MySqlSessionTemplate.java, then inherit SqlSessionTemplate.java, and replace the corresponding method below (only the changes are posted)

Import lombok.Getter

Import lombok.Setter

Public class MySqlSessionTemplate extends SqlSessionTemplate {

@ Getter

@ Setter

Private Map targetSqlSessionFactories

@ Getter

@ Setter

Private SqlSessionFactory defaultTargetSqlSessionFactory

Public MySqlSessionTemplate (SqlSessionFactory sqlSessionFactory, ExecutorType executorType

PersistenceExceptionTranslator exceptionTranslator) {

Super (sqlSessionFactory, executorType, exceptionTranslator)

NotNull (sqlSessionFactory, "Property 'sqlSessionFactory' is required")

NotNull (executorType, "Property 'executorType' is required")

This.sqlSessionFactory = sqlSessionFactory

This.executorType = executorType

This.exceptionTranslator = exceptionTranslator

This.sqlSessionProxy = (SqlSession) newProxyInstance (SqlSessionFactory.class.getClassLoader ()

New Class [] {SqlSession.class}, new MySqlSessionTemplate.SqlSessionInterceptor ()

This.defaultTargetSqlSessionFactory = sqlSessionFactory

}

/ / TODO mainly modifies this block, and where sqlSessionFactory is used, call this method to get

Public SqlSessionFactory getSqlSessionFactory () {

SqlSessionFactory targetSqlSessionFactory = targetSqlSessionFactories.get (DataSourceContextHolder.getDataSource ())

If (targetSqlSessionFactory! = null) {

Return targetSqlSessionFactory

} else if (defaultTargetSqlSessionFactory! = null) {

Return defaultTargetSqlSessionFactory

} else {

Assert.notNull (targetSqlSessionFactories, "Property 'targetSqlSessionFactories' or' defaultTargetSqlSessionFactory' are required")

}

Return this.sqlSessionFactory

}

/ * *

* {@ inheritDoc}

* /

@ Override

Public Configuration getConfiguration () {

Return this.getSqlSessionFactory () .getConfiguration ()

}

Private class SqlSessionInterceptor implements InvocationHandler {

@ Override

Public Object invoke (Object proxy, Method method, Object [] args) throws Throwable {

SqlSession sqlSession = getSqlSession (MySqlSessionTemplate.this.getSqlSessionFactory ()

MySqlSessionTemplate.this.executorType, MySqlSessionTemplate.this.exceptionTranslator)

Try {which is the best http://www.wxbhffk.com/ for Wuxi stream of people?

Object result = method.invoke (sqlSession, args)

If (! isSqlSessionTransactional (sqlSession, MySqlSessionTemplate.this.getSqlSessionFactory () {

/ / force commit even on non-dirty sessions because some databases require

/ / a commit/rollback before calling close ()

SqlSession.commit (true)

}

Return result

} catch (Throwable t) {

Throwable unwrapped = unwrapThrowable (t)

If (MySqlSessionTemplate.this.exceptionTranslator! = null & & unwrapped instanceof PersistenceException) {

/ / release the connection to avoid a deadlock if the translator is no loaded. See issue # 22

CloseSqlSession (sqlSession, MySqlSessionTemplate.this.sqlSessionFactory)

SqlSession = null

Throwable translated = MySqlSessionTemplate.this.exceptionTranslator

.translateExceptionIfPossible ((PersistenceException) unwrapped)

If (translated! = null) {

Unwrapped = translated

}

}

Throw unwrapped

} finally {

If (sqlSession! = null) {

CloseSqlSession (sqlSession, MySqlSessionTemplate.this.getSqlSessionFactory ())

}

}

}

}

}

7. New MyBatisPlusConfiguration.java,mybatisplus configuration and multi-data source configuration

Import com.alibaba.druid.pool.xa.DruidXADataSource

Import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean

Import com.baomidou.mybatisplus.autoconfigure.SpringBootVFS

Import com.baomidou.mybatisplus.core.MybatisConfiguration

Import com.baomidou.mybatisplus.core.parser.ISqlParser

Import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor

Import com.baomidou.mybatisplus.extension.plugins.tenant.TenantHandler

Import com.baomidou.mybatisplus.extension.plugins.tenant.TenantSqlParser

Import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean

Import com.admin.util.datasource.DataSourceKeyEnum

Import net.sf.jsqlparser.expression.Expression

Import net.sf.jsqlparser.expression.LongValue

Import org.apache.ibatis.mapping.BoundSql

Import org.apache.ibatis.session.SqlSessionFactory

Import org.apache.ibatis.type.JdbcType

Import org.mybatis.spring.annotation.MapperScan

Import org.springframework.beans.factory.annotation.Value

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 java.util.ArrayList

Import java.util.HashMap

Import java.util.List

Import java.util.Map

@ Configuration

@ MapperScan (basePackages = {"com.admin.*.dao", "com.baomidou.mybatisplus.samples.quickstart.mapper"}, sqlSessionTemplateRef = "sqlSessionTemplate")

Public class MyBatisPlusConfiguration {

@ Bean

@ Primary / / multiple data sources need to add this comment, just add one

@ ConfigurationProperties (prefix = "spring.datasource.druid.master")

Public AtomikosDataSourceBean userMaster () {

Return new AtomikosDataSourceBean ()

}

@ Bean

@ ConfigurationProperties (prefix = "spring.datasource.druid.slave0")

Public AtomikosDataSourceBean userSlave0 () {

Return new AtomikosDataSourceBean ()

}

@ Bean

@ ConfigurationProperties (prefix = "spring.datasource.druid.slave1")

Public AtomikosDataSourceBean userSlave1 () {

Return new AtomikosDataSourceBean ()

}

@ Bean (name = "sqlSessionTemplate")

Public MySqlSessionTemplate customSqlSessionTemplate () throws Exception {

Map sqlSessionFactoryMap = new HashMap () {{

Put (DataSourceKeyEnum.MASTER.getValue (), createSqlSessionFactory (userMaster ()

Put (DataSourceKeyEnum.SLAVE0.getValue (), createSqlSessionFactory (userSlave0 ()

Put (DataSourceKeyEnum.SLAVE1.getValue (), createSqlSessionFactory (userSlave1 ()

}}

MySqlSessionTemplate sqlSessionTemplate = new MySqlSessionTemplate (sqlSessionFactoryMap.get (DataSourceKeyEnum.MASTER.getValue ()

SqlSessionTemplate.setTargetSqlSessionFactories (sqlSessionFactoryMap)

Return sqlSessionTemplate

}

/ * *

* create a data source

*

* @ param dataSource

* @ return

* /

Private SqlSessionFactory createSqlSessionFactory (AtomikosDataSourceBean dataSource) throws Exception {

DataSource.setMaxPoolSize (10)

DataSource.setMinPoolSize (2)

DataSource.setPoolSize (2)

DataSource.setMaxIdleTime (60); / / maximum idle time, connections exceeding the minimum connection pool will be closed

DataSource.setMaxLifetime (1200); / / connection maximum idle time unit s all connection timeouts will be closed

DataSource.setTestQuery (druidDataSource.getValidationQuery ()); / / perform this operation before each request to ensure that the connection is valid, and can be executed with scheduled tasks later.

DataSource.setMaintenanceInterval (60); / / regular maintenance of thread cycle unit seconds

/ / the above configuration can be extracted into .yml and injected through ConfigurationProperties annotations

DataSource.init (); / / initialize the connection when the project starts

MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean ()

SqlSessionFactory.setDataSource (dataSource)

SqlSessionFactory.setMapperLocations (new PathMatchingResourcePatternResolver () .getResources ("classpath*:/com/admin/*/dao/xml/*.xml")

SqlSessionFactory.setVfs (SpringBootVFS.class)

MybatisConfiguration configuration = new MybatisConfiguration ()

/ / configuration.setDefaultScriptingLanguage (MybatisXMLLanguageDriver.class)

Configuration.setJdbcTypeForNull (JdbcType.NULL)

Configuration.setMapUnderscoreToCamelCase (false)

Configuration.setCacheEnabled (false)

SqlSessionFactory.setConfiguration (configuration)

SqlSessionFactory.setPlugins (paginationInterceptor ())

SqlSessionFactory.afterPropertiesSet ()

Return sqlSessionFactory.getObject ()

}

/ *

* Custom paging plug-in to automatically identify database types

* /

@ Bean

Public PaginationInterceptor paginationInterceptor () {

Return new PaginationInterceptor ()

}

}

Transactions are used in the same way as single data sources: add @ Transactional annotations to the corresponding methods or classes

These are all the contents of the article "how springboot+mybatisplus+druid implements multiple data sources + distributed transactions". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.