SpringBoot-多数据源配置-Mysql-SqlServer-Oracle
Maven依赖
<!-- mysql的jdbc依赖 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.16</version> </dependency> <!-- sqlserver的jdbc依赖 --> <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>mssql-jdbc</artifactId> <version>7.4.1.jre8</version> </dependency>
application.yml配置
注意是jdbc-url不是url,否则报jdbcUrl is required with driverClassName错误
spring: application: name: demo-multi-datasource datasource: mysql: jdbc-url: jdbc:mysql://localhost:3306/demo?characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&nullCatalogMeansCurrent=true username: root password: root driver-class-name: com.mysql.cj.jdbc.Driver mssql: jdbc-url: jdbc:sqlserver://localhost:1433;databasename=demo username: sa password: sa driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
mysql配置类
其中com.htkm.demo.mysql.dao是DAO层，
com/htkm/demo/mysql/mapping/dao/是映射文件目录
package com.htkm.demo.mysql.config; 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.boot.jdbc.DataSourceBuilder; 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 javax.sql.DataSource; /** * @author viwofer * @created 2019-12-25 21:44 */ @Configuration(value="mysql") @MapperScan(basePackages = "com.htkm.demo.mysql.dao", sqlSessionFactoryRef = "MysqlSqlSessionFactory") public class DataSourceConfig { @Bean(name = "MysqlDataSource") @ConfigurationProperties(prefix = "spring.datasource.mysql") public DataSource getDateSource() { return DataSourceBuilder.create().build(); } /** * 配置事务管理 */ @Bean(name = "MysqlTransactionManager") public DataSourceTransactionManager testTransactionManager(@Qualifier("MysqlDataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } /** * 配置工厂 * @param datasource * @return * @throws Exception */ @Bean(name = "MysqlSqlSessionFactory") public SqlSessionFactory testSqlSessionFactory(@Qualifier("MysqlDataSource") DataSource datasource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(datasource); bean.setMapperLocations( new PathMatchingResourcePatternResolver().getResources("classpath*:com/htkm/demo/mysql/mapping/dao/*.xml")); return bean.getObject(); } /** * 配置会话 * @param sessionfactory * @return */ @Bean("MysqlSqlSessionTemplate") public SqlSessionTemplate testsqlsessiontemplate( @Qualifier("MysqlSqlSessionFactory") SqlSessionFactory sessionfactory) { return new SqlSessionTemplate(sessionfactory); } }
mssql配置类
其中com.htkm.demo.mssql.dao是DAO层，
com/htkm/demo/mssql/mapping/dao/是映射文件目录
package com.htkm.demo.mssql.config; 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.boot.jdbc.DataSourceBuilder; 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 javax.sql.DataSource; /** * @author viwofer * @created 2019-12-25 21:44 */ @Configuration(value = "mssql") @MapperScan(basePackages = "com.htkm.demo.mssql.dao", sqlSessionFactoryRef = "MssqlSqlSessionFactory") public class DataSourceConfig { /** * 配置数据源 * @return */ @Bean(name = "MssqlDataSource") @ConfigurationProperties(prefix = "spring.datasource.mssql") public DataSource getDateSource() { return DataSourceBuilder.create().build(); } /** * 配置事务管理 */ @Bean(name = "MssqlTransactionManager") public DataSourceTransactionManager testTransactionManager(@Qualifier("MssqlDataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } /** * 配置工厂 * @param datasource * @return * @throws Exception */ @Bean(name = "MssqlSqlSessionFactory") public SqlSessionFactory testSqlSessionFactory(@Qualifier("MssqlDataSource") DataSource datasource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(datasource); bean.setMapperLocations( new PathMatchingResourcePatternResolver().getResources("classpath*:com/htkm/demo/mssql/mapping/dao/*.xml")); return bean.getObject(); } /** * 配置会话 * @param sessionfactory * @return */ @Bean("MssqlSqlSessionTemplate") public SqlSessionTemplate testsqlsessiontemplate( @Qualifier("MssqlSqlSessionFactory") SqlSessionFactory sessionfactory) { return new SqlSessionTemplate(sessionfactory); } }
Oracle配置
首先要得到Oracle JDBC Driver
1.通过Oracle官方网站下载相应版本：
https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html
2.通过Oracle的安装目录获得，位置在“{ORACLE_HOME}\jdbc\lib\ojdbc14.jar”
手动安装安装
命令如下：
mvn install:install-file -Dfile={Path/to/your/ojdbc.jar} -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0 -Dpackaging=jar
pom.xml添加依赖
<dependencies> <!-- 添加oracle jdbc driver --> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc8</artifactId> <version>11.2.0.4.0</version> </dependency> </dependencies> <!--报错不支持字符集用这个--> <!-- https://mvnrepository.com/artifact/cn.easyproject/orai18n --> <dependency> <groupId>cn.easyproject</groupId> <artifactId>orai18n</artifactId> <version>12.1.0.2.0</version> </dependency>
application.ym配置
spring.datasource.jdbc-url=jdbc:oracle:thin:@localhost:1521:data
spring.datasource.username=xxx
spring.datasource.password=xxx
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
jdbcUrl is required with driverClassName
错误：Cause: java.lang.IllegalArgumentException: jdbcUrl is required with driverClassName.
application.yml：
spring: application: name: demo-multi-datasource datasource: mysql: url: jdbc:mysql://localhost:3306/demo?characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&nullCatalogMeansCurrent=true username: root password: root driver-class-name: com.mysql.cj.jdbc.Driver mssql: url: jdbc:sqlserver://localhost:1433;databasename=demo username: sa password: sa driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
将url更改为jdbc-url
spring: application: name: demo-multi-datasource datasource: mysql: jdbc-url: jdbc:mysql://localhost:3306/demo?characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&nullCatalogMeansCurrent=true username: root password: root driver-class-name: com.mysql.cj.jdbc.Driver mssql: jdbc-url: jdbc:sqlserver://localhost:1433;databasename=demo username: sa password: sa driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url 数据库的 JDBC URL。
spring.datasource.jdbc-url 用来重写自定义连接池
官方文档的解释是：
因为连接池的实际类型没有被公开，所以在您的自定义数据源的元数据中没有生成密钥，而且在IDE中没有完成(因为DataSource接口没有暴露属性)。另外，如果您碰巧在类路径上有Hikari，那么这个基本设置就不起作用了，因为Hikari没有url属性(但是确实有一个jdbcUrl属性)。在这种情况下，您必须重写您的配置。
