背景:
新写了一个项目 需要兼容mysql和Oracle数据库 因此需要在springboot中配置多数据源
1.配置pom.xml 文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.1</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>
<name>demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--引入druid数据源-->
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<!-- driud整合springboot jar包 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<!-- springboot 整合easyui-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.2.0</version>
</dependency>
<!-- 小辣椒 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.10</version>
</dependency>
<!-- mybatis -->
<!-- mybatis整合springboot jar包 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.0</version>
</dependency>
<!-- 本地依赖 -->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc8</artifactId>
<version>12.2.0.1</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
2. 配置yml文件
spring:
datasource:
primary:
username: root
password: 123
jdbc-url: jdbc:mysql://49.232.211.129:3306/demo
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
secondary:
username: triger
password: triger
jdbc-url: jdbc:oracle:thin:@154.31.71.22:8001:TEST
driver-class-name: oracle.jdbc.OracleDriver
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
# schema:
# - classpath:department.sql
thymeleaf:
cache: false
encoding: UTF-8
enabled: true
mode: HTML
prefix: classpath:/templates/
suffix: .html
servlet:
content-type: text/html
#配置mybatis
mybatis:
#扫描xml文件
mapper-locations: classpath:mapper/*.xml
configuration:
#开启驼峰命名法
map-underscore-to-camel-case: true
3. 编写配置JAVA类
package com.example.demo.mybatis.config;
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.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
@Configuration
public class DataSourceConfig {
@Bean(name = "primaryDataSource")
@Qualifier("primaryDataSource")
@ConfigurationProperties(prefix="spring.datasource.primary")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "secondaryDataSource")
@Qualifier("secondaryDataSource")
@Primary
@ConfigurationProperties(prefix="spring.datasource.secondary")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "primaryJdbcTemplate")
public JdbcTemplate primaryJdbcTemplate(
@Qualifier("primaryDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
@Bean(name = "secondaryJdbcTemplate")
@Primary
public JdbcTemplate secondaryJdbcTemplate(
@Qualifier("secondaryDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
4. 编写测试文件类
package com.example.demo.mybatis.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
@RestController
public class DoubleDataSource {
@Autowired
@Qualifier("primaryJdbcTemplate")
protected JdbcTemplate jdbcTemplate1;
@Autowired
@Qualifier("secondaryJdbcTemplate")
protected JdbcTemplate jdbcTemplate2;
@RequestMapping("/test3")
public List<Map<String, Object>> test3(){
String sql = "SELECT * FROM department";
List<Map<String, Object>> maps = jdbcTemplate2.queryForList(sql);
return maps;
}
@RequestMapping("/test4")
public List<Map<String, Object>> test4(){
String sql = "SELECT * FROM user";
List<Map<String, Object>> maps = jdbcTemplate1.queryForList(sql);
return maps;
}
}
成功
遇到问题汇总
问题1 :java.lang.AbstractMethodError: oracle.jdbc.driver.T4CConnection.isValid(I)Z
原因 之前用的jdbc是 ojdbc14,同事提供的,在spring boot学习的时候 发现无法连接oracle。看了maven,发现其实这个jar只能支持JDK1.4。于是从新从官网下载jar。
解决方案:参考博客 : https://blog.csdn.net/db0n15/article/details/81068925
下载ojdbc8.jar 下载地址 : https://www.oracle.com/database/technologies/appdev/jdbc-ucp-19-6-c-downloads.html
将下载后的jar包放到 maven的仓库中 如果文件夹不存在需要创建新的文件夹。因为无法直接从远程仓库下载ojdbc.jar,所以需要下载好jar包后放到相应的jar包里
打开maven仓库
2.问题2 :jdbcUrl is required with driverClassName错误解决
参考博客:https://blog.csdn.net/newbie_907486852/article/details/81391525
springboot2.0配置多数据源:
application.properties 版本
spring.datasource.primary.url=jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.primary.username=root
spring.datasource.primary.password=root
spring.datasource.primary.driver-class=com.mysql.cj.jdbc.Driver
spring.datasource.secondary.url=jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.secondary.username=root
spring.datasource.secondary.password=root
spring.datasource.secondary.driver-class=com.mysql.cj.jdbc.Driver
修改为
spring.datasource.primary.jdbc-url=jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.primary.username=root
spring.datasource.primary.password=root
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.secondary.jdbc-url=jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.secondary.username=root
spring.datasource.secondary.password=root
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver
application.yml 修改前
spring:
datasource:
primary:
username: root
password: 123
url: jdbc:mysql://49.232.211.129:3306/demo
driver-class: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
secondary:
username: triger
password: triger
url: jdbc:oracle:thin:@154.31.71.22:8001:TEST
driver-class: oracle.jdbc.OracleDriver
修改后
spring:
datasource:
primary:
username: root
password: 123
jdbc-url: jdbc:mysql://49.232.211.129:3306/demo
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
secondary:
username: triger
password: triger
jdbc-url: jdbc:oracle:thin:@154.31.71.22:8001:TEST
driver-class-name: oracle.jdbc.OracleDriver
spring.datasource.url 数据库的 JDBC URL。
spring.datasource.jdbc-url 用来重写自定义连接池
官方文档的解释是:
因为连接池的实际类型没有被公开,所以在您的自定义数据源的元数据中没有生成密钥,而且在IDE中没有完成(因为DataSource接口没有暴露属性)。另外,如果您碰巧在类路径上有Hikari,那么这个基本设置就不起作用了,因为Hikari没有url属性(但是确实有一个jdbcUrl属性)。在这种情况下,您必须重写您的配置如下: