spring-boot 2.3.3.RELEASE 整合oracle、mysql多数据源

spring-boot-multiple-data-sources

• spring-boot 2.3.3.RELEASE 整合oracle、mysql多数据源
• 主数据源配置为web（oracle），其他为从数据源（oracle、mysql）
• 单元测试通过，支持查询，更新
• 数据源连接池由默认com.zaxxer.hikari.HikariDataSource改为org.apache.tomcat.jdbc.pool.DataSource
• tomcat连接池默认配置org.apache.tomcat.jdbc.pool.PoolProperties

码云地址

application.yml

spring:
  datasource:
    web:
      driver-class-name: oracle.jdbc.OracleDriver
      url: jdbc:oracle:thin:@192.168.92.100:1521:orcl
      username: web
      password: 123456
      #在连接池启动时要建立的连接数
      initialSize: 5
      #连接池中的最大活跃连接数
      maxActive: 20
      #连接池里始终应该保持的最小连接数(用于DBCP和Tomcat连接池)
      minIdle: 5
      #连接池中的最大空闲连接数
      maxIdle: 10
      #连接池在等待返回连接时，最长等待多少毫秒再抛出异常
      maxWait: 30000
      #从连接池中借用连接时是否要进行测试
      testOnBorrow: true
      #在将连接归还到连接池时是否要进行测试
      testOnReturn: false
      #在连接空闲时是否要进行测试
      testWhileIdle: true
      #在两次空闲连接验证、弃用连接清理和空闲池大小调整之间睡眠的毫秒数
      timeBetweenEvictionRunsMillis: 5000
      #一个空闲连接被空闲连接释放器（如果存在的话）优雅地释放前，最短会在连接池里停 留多少毫秒。
      minEvictableIdleTimeMillis: 300000
      #在连接池里的连接返回给调用者或连接池时，要执行的验证 SQL 查询
      #客户端在使用一个无效的连接时会先对该连接进行测试，如果发现该连接已经无效，则重新从连接池获取有效数据库连接来使用
      validation-query: select 1 from dual
      #执行连接验证的间隔时间，单位为毫秒
      validationInterval: 30000
      #被弃用的连接在到达弃用超时后是否应该被移除
      removeAbandoned: true
      #连接在多少秒后应该考虑弃用
      removeAbandonedTimeout: 120
      #连接的默认事务隔离级别,未设置（2？）
      #defaultTransactionIsolation: -1
    deploy:
      driver-class-name: oracle.jdbc.OracleDriver
      url: jdbc:oracle:thin:@192.168.92.100:1521:orcl
      username: deploy
      password: 123456
      initialSize: 5
      maxActive: 20
      minIdle: 5
      maxIdle: 10
      maxWait: 30000
      testOnBorrow: true
      testOnReturn: false
      testWhileIdle: true
      timeBetweenEvictionRunsMillis: 5000
      minEvictableIdleTimeMillis: 300000
      validation-query: select 1 from dual
      validationInterval: 30000
      removeAbandoned: true
      removeAbandonedTimeout: 120
    flower:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/flower?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
      username: root
      password:
      initialSize: 5
      maxActive: 20
      minIdle: 5
      maxIdle: 10
      maxWait: 30000
      testOnBorrow: true
      testOnReturn: false
      testWhileIdle: true
      timeBetweenEvictionRunsMillis: 5000
      minEvictableIdleTimeMillis: 300000
      validation-query: select 1
      validationInterval: 30000
      removeAbandoned: true
      removeAbandonedTimeout: 120
  mvc:
    view:
      prefix: classpath:templates/
      suffix: .html

server:
  port: 8080
  servlet:
    context-path: /api/demo

mybatisconfig

import java.util.Properties;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import org.apache.ibatis.session.AutoMappingBehavior;
import org.apache.ibatis.session.AutoMappingUnknownColumnBehavior;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.LocalCacheScope;
import org.apache.ibatis.type.JdbcType;

/**
 * @author ylm-sigmund
 * @since 2020/8/25 21:55
 */
public class DefaultMyBatisConfig {

    public static final String ORACLE = "oracle";
    public static final String MYSQL = "mysql";

    /**
     * mybatis配置数据
     * 
     * @param databaseId
     *            databaseId
     * @return Configuration
     */
    public static Configuration getDefaultConfiguration(String databaseId) {
        Configuration configuration = new Configuration();
        setSettings(configuration);
        setVariables(configuration);
        configuration.setDatabaseId(databaseId);
        return configuration;
    }

    private static void setSettings(Configuration configuration) {
        configuration.setCacheEnabled(true);
        configuration.setLazyLoadingEnabled(true);
        configuration.setMultipleResultSetsEnabled(true);
        configuration.setUseColumnLabel(true);
        configuration.setUseGeneratedKeys(false);
        configuration.setAutoMappingBehavior(AutoMappingBehavior.PARTIAL);
        configuration.setAutoMappingUnknownColumnBehavior(AutoMappingUnknownColumnBehavior.WARNING);
        configuration.setDefaultExecutorType(ExecutorType.SIMPLE);
        configuration.setDefaultStatementTimeout(30);
        configuration.setDefaultFetchSize(200);
        configuration.setSafeRowBoundsEnabled(false);
        configuration.setMapUnderscoreToCamelCase(false);
        configuration.setLocalCacheScope(LocalCacheScope.SESSION);
        configuration.setJdbcTypeForNull(JdbcType.OTHER);
        configuration.setLazyLoadTriggerMethods(
            Stream.of("equals", "clone", "hashCode", "toString").collect(Collectors.toSet()));
    }

    /**
     * 设置mybatis全局变量
     * 
     * @param configuration
     *            configuration
     */
    private static void setVariables(Configuration configuration) {
        Properties properties = new Properties();
        properties.setProperty("web", "web.");
        configuration.setVariables(properties);
    }
}

ojdbc6jar获取

IDEA mysql&oracle mybatis-generator plugin插件 自动生成实体类和mapper.xml实战

console提示Registered driver with driverClassName=com.mysql.jdbc.Driver was not found, trying direct instantiation.

• driver-class-name: com.mysql.jdbc.Driver
• 改为
• com.mysql.cj.jdbc.Driver

console提示Registered driver with driverClassName=oracle.jdbc.driver.OracleDriver was not found, trying direct instantiation.

• driver-class-name: oracle.jdbc.driver.OracleDriver
• 改为
• oracle.jdbc.OracleDriver

资料参考

• springboot整合mybatis（多数据源、mysql/Oracle数据库版）
• springboot整合mybatis并配置多数据源
• 从jdbc到spring-boot-starter-jdbc
• java.sql.SQLException: Io 异常: Connection reset
• spring-boot application.properties配置详情之DataSource
• MyBatis配置文件mybatis-config详解
• Spring Boot（16）——使用DataSource
• Spring Boot中Datasource配置（Spring Boot dbcp2数据源配置）