springboot+mybatis 配置雙數據源（mysql,oracle,sqlserver,db2）
以上如圖為項目文件夾目錄。
1，首先配置application.yml 配置文件
server: port: 8084 tomcat: uri-encoding: utf-8 servlet: context-path: /services #spring: # datasource: # type: com.alibaba.druid.pool.DruidDataSource # # name: skynet # driver-class-name: com.mysql.jdbc.Driver # url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false # username: root # password: root # # # jpa: # show-sql: true #是否在控制臺打印sql語句 # 自定義的mysql主數據源配置信息 primary: datasource: #druid相關配置 druid: #監控統計攔截的filters filters: stat driverClassName: com.mysql.jdbc.Driver #配置基本屬性 url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8 username: root password: root #配置初始化大小/最小/最大 initialSize: 1 minIdle: 1 maxActive: 20 #獲取連接等待超時時間 maxWait: 60000 #間隔多久進行一次檢測，檢測需要關閉的空閑連接 timeBetweenEvictionRunsMillis: 60000 #一個連接在池中最小生存的時間 minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 'x' testWhileIdle: true testOnBorrow: false testOnReturn: false #打開PSCache，并指定每個連接上PSCache的大小。oracle設為true，mysql設為false。分庫分表較多推薦設置為false poolPreparedStatements: false maxPoolPreparedStatementPerConnectionSize: 20 # 自定義的sqlserver數據源配置信息 或者oracle, db2 數據庫，只需要更改driverClassName以及數據庫基本屬性 back: datasource: #druid相關配置 druid: #監控統計攔截的filters filters: stat driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver #配置基本屬性 url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=tiuweb_drgs username: admin password: 123 #配置初始化大小/最小/最大 initialSize: 1 minIdle: 1 maxActive: 20 #獲取連接等待超時時間 maxWait: 60000 #間隔多久進行一次檢測，檢測需要關閉的空閑連接 timeBetweenEvictionRunsMillis: 60000 #一個連接在池中最小生存的時間 minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 'x' testWhileIdle: true testOnBorrow: false testOnReturn: false #打開PSCache，并指定每個連接上PSCache的大小。oracle設為true，mysql設為false。分庫分表較多推薦設置為false poolPreparedStatements: true maxPoolPreparedStatementPerConnectionSize: 20 jackson: time-zone: GMT+8 date-format: yyyy-MM-dd HH:mm:ss resources: static-locations: classpath:/static/,classpath:/views/ #mybatis mybatis: mapper-locations: classpath:mapper/**/*.xml configuration: map-underscore-to-camel-case: true default-fetch-size: 500 default-statement-timeout: 80 # mybatis 分頁插件 需要配置pom.xml坐標 pagehelper: helperDialect: sqlserver reasonable: true supportMethodsArguments: true pageSizeZero: true params: count=countSql #logback日志配置 需要配置pom.xml坐標以及在resources下配置config配置文件 logging: config: classpath:config/logback-spring.xml level: com.tiuweb.skynet.dao: DEBUG file: path: DRGS_LOG #日志保存文件夾
2，配置config數據源配置文件 PrimaryDataBaseConfig 和 BackDataBaseConfig
package com.tw.skynet.config; import com.alibaba.druid.pool.DruidDataSource; import lombok.Data; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; 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.datasource.DataSourceTransactionManager; import org.springframework.stereotype.Component; import javax.sql.DataSource; import java.sql.SQLException; @Data @Configuration @Component // 前綴為primary.datasource.druid的配置信息 @ConfigurationProperties(prefix = "primary.datasource.druid") @MapperScan(basePackages = PrimaryDataBaseConfig.PACKAGE, sqlSessionFactoryRef = "primarySqlSessionFactory") public class PrimaryDataBaseConfig { /** * dao層的包路徑 */ static final String PACKAGE = "com.tw.skynet.dao"; /** * mapper文件的相對路徑 */ private static final String MAPPER_LOCATION = "classpath:mapper/primary/*.xml"; private String filters; private String url; private String username; private String password; private String driverClassName; private int initialSize; private int minIdle; private int maxActive; private long maxWait; private long timeBetweenEvictionRunsMillis; private long minEvictableIdleTimeMillis; private String validationQuery; private boolean testWhileIdle; private boolean testOnBorrow; private boolean testOnReturn; private boolean poolPreparedStatements; private int maxPoolPreparedStatementPerConnectionSize; // 主數據源使用@Primary注解進行標識 @Primary @Bean(name = "primaryDataSource") public DataSource primaryDataSource() throws SQLException { DruidDataSource druid = new DruidDataSource(); // 監控統計攔截的filters druid.setFilters(filters); // 配置基本屬性 druid.setDriverClassName(driverClassName); druid.setUsername(username); druid.setPassword(password); druid.setUrl(url); //初始化時建立物理連接的個數 druid.setInitialSize(initialSize); //最大連接池數量 druid.setMaxActive(maxActive); //最小連接池數量 druid.setMinIdle(minIdle); //獲取連接時最大等待時間，單位毫秒。 druid.setMaxWait(maxWait); //間隔多久進行一次檢測，檢測需要關閉的空閑連接 druid.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); //一個連接在池中最小生存的時間 druid.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); //用來檢測連接是否有效的sql druid.setValidationQuery(validationQuery); //建議配置為true，不影響性能，并且保證安全性。 druid.setTestWhileIdle(testWhileIdle); //申請連接時執行validationQuery檢測連接是否有效 druid.setTestOnBorrow(testOnBorrow); druid.setTestOnReturn(testOnReturn); //是否緩存preparedStatement，也就是PSCache，oracle設為true，mysql設為false。分庫分表較多推薦設置為false druid.setPoolPreparedStatements(poolPreparedStatements); // 打開PSCache時，指定每個連接上PSCache的大小 druid.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize); return druid; } // 創建該數據源的事務管理 @Primary @Bean(name = "primaryTransactionManager") public DataSourceTransactionManager primaryTransactionManager() throws SQLException { return new DataSourceTransactionManager(primaryDataSource()); } // 創建Mybatis的連接會話工廠實例 @Primary @Bean(name = "primarySqlSessionFactory") public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource primaryDataSource) throws Exception { final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean(); sessionFactory.setDataSource(primaryDataSource); // 設置數據源bean sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver() .getResources(PrimaryDataBaseConfig.MAPPER_LOCATION)); // 設置mapper文件路徑 return sessionFactory.getObject(); } }
package com.tw.skynet.config; import com.alibaba.druid.pool.DruidDataSource; import lombok.Data; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; 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.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import org.springframework.stereotype.Component; import javax.sql.DataSource; import java.sql.SQLException; /** * @Description: 后臺數據源配置類 */ @Data @Configuration @Component @ConfigurationProperties(prefix = "back.datasource.druid") @MapperScan(basePackages = BackDataBaseConfig.PACKAGE, sqlSessionFactoryRef = "backSqlSessionFactory") public class BackDataBaseConfig { /** * dao層的包路徑 */ static final String PACKAGE = "com.tw.skynet.dao.back"; /** * mapper文件的相對路徑 */ private static final String MAPPER_LOCATION = "classpath:mapper/back/*.xml"; private String filters; private String url; private String username; private String password; private String driverClassName; private int initialSize; private int minIdle; private int maxActive; private long maxWait; private long timeBetweenEvictionRunsMillis; private long minEvictableIdleTimeMillis; private String validationQuery; private boolean testWhileIdle; private boolean testOnBorrow; private boolean testOnReturn; private boolean poolPreparedStatements; private int maxPoolPreparedStatementPerConnectionSize; @Bean(name = "backDataSource") public DataSource backDataSource() throws SQLException { DruidDataSource druid = new DruidDataSource(); // 監控統計攔截的filters druid.setFilters(filters); // 配置基本屬性 druid.setDriverClassName(driverClassName); druid.setUsername(username); druid.setPassword(password); druid.setUrl(url); //初始化時建立物理連接的個數 druid.setInitialSize(initialSize); //最大連接池數量 druid.setMaxActive(maxActive); //最小連接池數量 druid.setMinIdle(minIdle); //獲取連接時最大等待時間，單位毫秒。 druid.setMaxWait(maxWait); //間隔多久進行一次檢測，檢測需要關閉的空閑連接 druid.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); //一個連接在池中最小生存的時間 druid.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); //用來檢測連接是否有效的sql druid.setValidationQuery(validationQuery); //建議配置為true，不影響性能，并且保證安全性。 druid.setTestWhileIdle(testWhileIdle); //申請連接時執行validationQuery檢測連接是否有效 druid.setTestOnBorrow(testOnBorrow); druid.setTestOnReturn(testOnReturn); //是否緩存preparedStatement，也就是PSCache，oracle設為true，mysql設為false。分庫分表較多推薦設置為false druid.setPoolPreparedStatements(poolPreparedStatements); // 打開PSCache時，指定每個連接上PSCache的大小 druid.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize); return druid; } @Bean(name = "backTransactionManager") public DataSourceTransactionManager backTransactionManager() throws SQLException { return new DataSourceTransactionManager(backDataSource()); } @Bean(name = "backSqlSessionFactory") public SqlSessionFactory backSqlSessionFactory(@Qualifier("backDataSource") DataSource backDataSource) throws Exception { final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean(); sessionFactory.setDataSource(backDataSource); sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver() .getResources(BackDataBaseConfig.MAPPER_LOCATION)); return sessionFactory.getObject(); } }
3，以上配置就完成了，接下來就是dao文件夾和mapper文件夾分包
現在所有配置已經完了，最后不要忘記pom.xml 導入所需要的數據庫驅動jar包
浙公網安備 33010602011771號