springboot+ mybatisplus+druid 實現多資料來源+分散式事務

ckxllf發表於2019-10-10

  jdk環境:1.8

  springboot:2.1.3.RELEASE

  mybatisplus:3.2.0

  本文主要用atomikos的AtomikosDataSourceBean+druid配置連線池,另一個是AtomikosNonXADataSourceBean

  1、maven 需要注意mysql-connector-java版本為6.x,超過這個版本可能會報異常

  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、新建DataSourceContextHolder.java 提供設定、獲取、清除當前資料來源的方法;

  public class DataSourceContextHolder {

  private static final ThreadLocal contextHolder = new InheritableThreadLocal<>();

  /**

  * 設定資料來源

  *

  * @param db

  */

  public static void setDataSource(String db) {

  contextHolder.set(db);

  }

  /**

  * 取得當前資料來源

  *

  * @return

  */

  public static String getDataSource() {

  return contextHolder.get();

  }

  /**

  * 清除上下文資料

  */

  public static void clear() {

  contextHolder.remove();

  }

  }

  3、DataSource.java 資料來源註解 及DataSourceKeyEnum.java 資料來源列舉類;並提供獲取列舉的方法

  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"),

  /**

  * 表示 所有的SLAVE, 隨機選擇一個SLAVE0, 或者SLAVE1

  */

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

  }

  /**

  * 根據方法名稱選擇

  *

  * @param 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;

  }

  /**

  * 根據註解獲取資料來源

  *

  * @param dataSource

  * @return

  */

  public static DataSourceKeyEnum getDataSourceKey(DataSource dataSource) {

  if (dataSource == null) {

  return MASTER;

  }

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

  List dataSourceKeyList = DataSourceKeyEnum.getSlaveList();

  // FIXME 目前亂序

  Collections.shuffle(dataSourceKeyList);

  return dataSourceKeyList.get(0);

  } else {

  return dataSource.value();

  }

  }

  /**

  * 根據className和Method 獲取資料來源列舉

  *

  * @param className

  * @return

  */

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

  //方法上的註解

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

  DataSourceKeyEnum keyEnum;

  if (dataSource != null) {//註解存在則以註解為主

  keyEnum = DataSourceKeyEnum.getDataSourceKey(dataSource);

  } else {

  keyEnum = DataSourceKeyEnum.getDSKeyByMethodName(method.getName());

  }

  return keyEnum.getValue();

  }

  }

  4、DataSourceAspect.java 資料來源切面類,這裡是只做了對mapper層攔截,包括攔截了mybatisplus的公共BaseMapper

  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();

  //攔截到的公共BaseMapper裡的介面可透過該方式獲得具體實現類的資訊

  Type[] types = AopUtils.getTargetClass(pjp.getTarget()).getGenericInterfaces(); // getGenericInterfaces方法能夠獲取類/介面實現的所有介面

  String name = types[0].getTypeName();

  String dataSource = DataSourceKeyEnum.getByClassName(name, method);

  log.info("選擇的資料來源:"+dataSource);

  DataSourceContextHolder.setDataSource(dataSource);

  Object o=pjp.proceed();

  DataSourceContextHolder.clear();

  return o;

  }

  }

  5、application.yml 相關配置

  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

  #獲取連線等待超時時間

  max-wait: 60000

  #間隔多久進行一次檢測,檢測需要關閉的空閒連線

  time-between-eviction-runs-millis: 60000

  #一個連線在池中最小生存的時間

  min-evictable-idle-time-millis: 300000

  #指定獲取連線時連線校驗的sql查詢語句

  validation-query: SELECT 'x'

  #驗證連線的有效性

  test-while-idle: true

  #獲取連線時候驗證,會影響效能(不建議true)

  test-on-borrow: false

  #開啟PSCache,並指定每個連線上PSCache的大小。oracle設為true,mysql設為false。分庫分表較多推薦設定為false

  pool-prepared-statements: false

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

  # 配置監控統計攔截的filters,去掉後監控介面sql無法統計,'wall'用於防火牆

  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

  #獲取連線等待超時時間

  max-wait: 60000

  #間隔多久進行一次檢測,檢測需要關閉的空閒連線

  time-between-eviction-runs-millis: 60000

  #一個連線在池中最小生存的時間

  min-evictable-idle-time-millis: 300000

  #指定獲取連線時連線校驗的sql查詢語句

  validation-query: SELECT 'x'

  #驗證連線的有效性

  test-while-idle: true

  #獲取連線時候驗證,會影響效能(不建議true)

  test-on-borrow: false

  #開啟PSCache,並指定每個連線上PSCache的大小。oracle設為true,mysql設為false。分庫分表較多推薦設定為false

  pool-prepared-statements: false

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

  # 配置監控統計攔截的filters,去掉後監控介面sql無法統計,'wall'用於防火牆

  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

  #獲取連線等待超時時間

  max-wait: 60000

  #間隔多久進行一次檢測,檢測需要關閉的空閒連線

  time-between-eviction-runs-millis: 60000

  #一個連線在池中最小生存的時間

  min-evictable-idle-time-millis: 300000

  #指定獲取連線時連線校驗的sql查詢語句

  validation-query: SELECT 'x'

  #驗證連線的有效性

  test-while-idle: true

  #獲取連線時候驗證,會影響效能(不建議true)

  test-on-borrow: false

  #開啟PSCache,並指定每個連線上PSCache的大小。oracle設為true,mysql設為false。分庫分表較多推薦設定為false

  pool-prepared-statements: false

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

  # 配置監控統計攔截的filters,去掉後監控介面sql無法統計,'wall'用於防火牆

  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 #預設取計算機的IP地址 需保證生產環境值唯一

  6、複製SqlSessionTemplate.java裡所有程式碼新建到MySqlSessionTemplate.java然後繼承SqlSessionTemplate.java,並按照下方替換相應的方法(只貼出了更改了的地方)

  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 主要修改了這一塊,並且用到sqlSessionFactory的地方都改呼叫該方法獲取

  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 { 無錫人流哪家好

  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、新建MyBatisPlusConfiguration.java,mybatisplus配置 和多資料來源配置

  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 //多資料來源時需要加上該註解,加一個即可

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

  }

  /**

  * 建立資料來源

  *

  * @param dataSource

  * @return

  */

  private SqlSessionFactory createSqlSessionFactory(AtomikosDataSourceBean dataSource) throws Exception {

  dataSource.setMaxPoolSize(10);

  dataSource.setMinPoolSize(2);

  dataSource.setPoolSize(2);

  dataSource.setMaxIdleTime(60);//最大閒置時間,超過最小連線池的連線將關閉

  dataSource.setMaxLifetime(1200);//連線最大閒置時間 單位s 全部的連線超時將關閉

  dataSource.setTestQuery(druidDataSource.getValidationQuery());//前期先每次請求前都執行該操作保證連線有效,後期可用定時任務執行

  dataSource.setMaintenanceInterval(60);//定時維護執行緒週期 單位秒

  //以上配置可提取到.yml內透過ConfigurationProperties註解注入

  dataSource.init();//專案啟動則初始化連線

  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();

  }

  /*

  * 自定義的分頁外掛,自動識別資料庫型別

  */

  @Bean

  public PaginationInterceptor paginationInterceptor() {

  return new PaginationInterceptor();

  }

  }

  事務使用方法如單資料來源一樣:對應的方法或類上加@Transactional註解即可


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69945560/viewspace-2659340/,如需轉載,請註明出處,否則將追究法律責任。

相關文章