使用SpringBoot 配置Oracle和H2雙數(shù)據(jù)源及問題

第使用SpringBoot配置Oracle和H2雙數(shù)據(jù)源及問題目錄配置POM配置yml配置注入問題在上節(jié)使用了H2之后感覺很爽，很輕便，正好有個項(xiàng)目要求簡單，最好不適用外部數(shù)據(jù)庫，于是就想著把H2數(shù)據(jù)庫集成進(jìn)來，這個系統(tǒng)已經(jīng)存在了一個Oracle，正好練習(xí)下配置多數(shù)據(jù)源，而在配置多數(shù)據(jù)源時，H2的schema配置不生效真是花了我好長時間才解決。。。所以也記錄一下

配置POM

!--oracle--

dependency

groupIdcom.github.noraui/groupId

artifactIdnoraui/artifactId

version2.4.0/version

/dependency

!--h2--

dependency

groupIdcom.h2database/groupId

artifactIdh2/artifactId

version1.4.197/version

/dependency

!--mybatisplus--

dependency

groupIdcom.baomidou/groupId

artifactIdmybatis-plus-boot-starter/artifactId

version3.1.1/version

/dependency

配置yml

spring:

http:

encoding:

charset:UTF-8

enabled:true

force:true

datasource:

driver-class-name:org.h2.Driver

schema:classpath:h2/schema-h2.sql

data:classpath:h2/data-h2.sql

jdbc-url:jdbc:h2:file:D:/Cache/IdeaWorkSpace/BigData/CustomerModel/src/main/resources/h2/data/h2_data

username:root

password:a123456

initialization-mode:always

oracle:

driver-class-name:oracle.jdbc.driver.OracleDriver

jdbc-url:jdbc:oracle:thin:@xxx:1521:cmis

username:xxx

password:xxx

console:

enabled:true

path:/h2-console

可以看到配置中配置了兩個數(shù)據(jù)源，主數(shù)據(jù)源是H2，第二個數(shù)據(jù)源是Oracle，接下來是通過配置類來注入數(shù)據(jù)源

配置注入

配置H2主數(shù)據(jù)源

packagecom.caxs.warn.config;

importorg.apache.ibatis.session.SqlSessionFactory;

importorg.mybatis.spring.SqlSessionFactoryBean;

importorg.mybatis.spring.annotation.MapperScan;

importorg.springframework.beans.factory.annotation.Qualifier;

importperties.ConfigurationProperties;

importorg.springframework.boot.jdbc.DataSourceBuilder;

importorg.springframework.context.annotation.Bean;

importorg.springframework.context.annotation.Configuration;

importorg.springframework.jdbc.core.JdbcTemplate;

importorg.springframework.jdbc.datasource.DataSourceTransactionManager;

importjavax.sql.DataSource;

*@Author:TheBigBlue

*@Description:

*@Date:2025/9/18

@Configuration

@MapperScan(basePackages="com.caxs.warn.mapper.h2",sqlSessionFactoryRef="h2SqlSessionFactory")

publicclassH2DSConfig{

@Bean(name="h2DataSource")

@ConfigurationProperties(prefix="spring.datasource")

publicDataSourcedataSource(){

returnDataSourceBuilder.create().build();

@Bean(name="h2TransactionManager")

publicDataSourceTransactionManagertransactionManager(){

returnnewDataSourceTransactionManager(this.dataSource());

@Bean(name="h2SqlSessionFactory")

publicSqlSessionFactorysqlSessionFactory(@Qualifier("h2DataSource")DataSourcedataSource)throwsException{

finalSqlSessionFactoryBeansessionFactory=newSqlSessionFactoryBean();

sessionFactory.setDataSource(dataSource);

sessionFactory.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);

returnsessionFactory.getObject();

@Bean(name="h2Template")

publicJdbcTemplateh2Template(@Qualifier("h2DataSource")DataSourcedataSource){

returnnewJdbcTemplate(dataSource);

配置oracle從數(shù)據(jù)源

packagecom.caxs.warn.config;

importorg.apache.ibatis.session.SqlSessionFactory;

importorg.mybatis.spring.SqlSessionFactoryBean;

importorg.mybatis.spring.annotation.MapperScan;

importorg.springframework.beans.factory.annotation.Qualifier;

importperties.ConfigurationProperties;

importorg.springframework.boot.jdbc.DataSourceBuilder;

importorg.springframework.context.annotation.Bean;

importorg.springframework.context.annotation.Configuration;

importorg.springframework.jdbc.core.JdbcTemplate;

importorg.springframework.jdbc.datasource.DataSourceTransactionManager;

importjavax.sql.DataSource;

*@Author:TheBigBlue

*@Description:

*@Date:2025/9/18

@Configuration

@MapperScan(basePackages="com.caxs.warn.mapper.oracle",sqlSessionFactoryRef="oracleSqlSessionFactory")

publicclassOracleDSConfig{

@Bean(name="oracleDataSource")

@ConfigurationProperties(prefix="spring.datasource.oracle")

publicDataSourcedataSource(){

returnDataSourceBuilder.create().build();

@Bean(name="oracleTransactionManager")

publicDataSourceTransactionManagertransactionManager(){

returnnewDataSourceTransactionManager(this.dataSource());

@Bean(name="oracleSqlSessionFactory")

publicSqlSessionFactorysqlSessionFactory(@Qualifier("oracleDataSource")DataSourcedataSource)throwsException{

finalSqlSessionFactoryBeansessionFactory=newSqlSessionFactoryBean();

sessionFactory.setDataSource(dataSource);

sessionFactory.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);

returnsessionFactory.getObject();

@Bean(name="oracleTemplate")

publicJdbcTemplateoracleTemplate(@Qualifier("oracleDataSource")DataSourcedataSource){

returnnewJdbcTemplate(dataSource);

問題

Schema“classpath:h2/schema-h2.sql”notfound

經(jīng)過上面的配置就可以使用雙數(shù)據(jù)源了，但是當(dāng)我們測試時會發(fā)現(xiàn)報(bào)如下錯誤：Schema“classpath:h2/schema-h2.sql”notfound，這個問題我也是找了好久，因?yàn)樵谂渲玫珨?shù)據(jù)源的時候沒有這個問題的，在配置多數(shù)據(jù)源才有了這個問題。

單數(shù)據(jù)源時，是直接SpringBoot自動配置DataSource的，這個時候是正常的，而當(dāng)配置多數(shù)據(jù)源時，我們是通過@Configuration來配置數(shù)據(jù)源的，懷疑問題出在DataSourceBuilder創(chuàng)建數(shù)據(jù)源這個類上，而單數(shù)據(jù)源自動裝載時不會出現(xiàn)這樣的問題。然后百度搜了下這個DataSourceBuilder，看到文章中實(shí)例的配置中schema是這樣寫的：

packagecom.caxs.warn.service;

importorg.slf4j.Logger;

importorg.slf4j.LoggerFactory;

importorg.springframework.beans.factory.annotation.Autowired;

importorg.springframework.beans.factory.annotation.Qualifier;

importorg.springframework.beans.factory.annotation.Value;

importorg.springframework.boot.ApplicationArguments;

importorg.springframework.boot.ApplicationRunner;

importorg.springframework.core.io.ClassPathResource;

importorg.springframework.jdbc.core.JdbcTemplate;

importorg.springframework.stereotype.Component;

importjava.io.BufferedReader;

importjava.io.IOException;

importjava.io.InputStreamReader;

*@Author:TheBigBlue

*@Description:服務(wù)啟動后，初始化數(shù)據(jù)庫

*@Date:2025/9/19

@Component

publicclassApplicationRunnerServiceimplementsApplicationRunner{

privatestaticfinalLoggerLOGGER=LoggerFactory.getLogger(ApplicationRunnerService.class);

@Autowired

@Qualifier("h2Template")

privateJdbcTemplateh2Template;

@Value("${invoke.schema.location}")

privateStringschema;

@Value("${invoke.data.location}")

privateStringdata;

*@Author:TheBigBlue

*@Description:項(xiàng)目啟動，執(zhí)行sql文件初始化

*@Date:2025/9/19

*@Paramargs:

*@Return:

@Override

publicvoidrun(ApplicationArgumentsargs){

StringschemaContent=this.getFileContent(schema);

StringdataContent=this.getFileContent(data);

h2Template.execute(schemaContent);

h2Template.execute(dataContent);

*@Author:TheBigBlue

*@Description:獲取classpath下sql文件內(nèi)容

*@Date:2025/9/19

*@ParamfilePath:

*@Return:

privateStringgetFileContent(StringfilePath){

BufferedReaderbufferedReader=null;

Stringstring;

StringBuilderdata=newStringBuilder();

try{

ClassPathResourceclassPathResource=newClassPathResource(filePath);

bufferedReader=newBufferedReader(newInputStreamReader(classPathResource.getInputStream()));

while((string=bufferedReader.readLine())!=null){

data.append(string);

}catch(IOExceptione){

LOGGER.error("加載ClassPath資源失敗",e);

}finally{

if(null!=bufferedReader){

try{

bufferedReader.close();

}catch(IOExceptione){

e.printStackTrace();

returndata.toString();

抱著嘗試的態(tài)度改了下，發(fā)現(xiàn)果然沒問題了！！原來是在SpringBoot2.0之后schema對應(yīng)的DataSourceProperties類中schema屬性是一個List，所以需要前面加-(yml中加-映射集合)，記錄下防止后面再踩坑。

Table“USER”notfound;SQLstatement:

這個問題也是在只有配置多數(shù)據(jù)源時才會碰到的問題，就是配置的spring.datasource.schema和spring.datasource.data無效。這個我看了下如果是配置單數(shù)據(jù)源，springboot自動加載Datasource，是沒問題的，但是現(xiàn)在是我們自己維護(hù)的datasource：returnDataSourceBuilder.create().build();所以感覺還是DataSourceBuilder在加載數(shù)據(jù)源的時候的問題，但是還是沒有找到原因。有網(wǎng)友說必須加initialization-mode:ALWAYS這個配置，但是我配置后也是不能用的。

最后沒辦法就配置了一個類，在springboot啟動后，自己加載文件，讀取其中的sql內(nèi)容，然后用jdbcTemplate去執(zhí)行了下，模擬了下初始化的操作。。。后面如果有時間再來解決這個問題。

packagecom.caxs.warn.service;

importorg.slf4j.Logger;

importorg.slf4j.LoggerFactory;

importorg.springframework.beans.factory.annotation.Autowired;

importorg.springframework.beans.factory.annotation.Qualifier;

importorg.springframework.beans.factory.annotation.Value;

importorg.springframework.boot.ApplicationArguments;

importorg.springframework.boot.ApplicationRunner;

importorg.springframework.core.io.ClassPathResource;

importorg.springframework.jdbc.core.JdbcTemplate;

importorg.springframework.stereotype.Component;

importjava.io.BufferedReader;

importjava.io.IOException;

importjava.io.InputStreamReader;

*@Author:TheBigBlue

*@Description:服務(wù)啟動后，初始化數(shù)據(jù)庫

*@Date:2025/9/19

@Component

publicclassApplicationRunnerServiceimplementsApplicationRunner{

privatestaticfinalLoggerLOGGER=LoggerFactory.getLogger(ApplicationRunnerService.class);

@Autowired

@Qualifier("h2Template")

privateJdbcTemplateh2Template;

@Value("${invoke.schema.location}")

privateStringschema;

@Value("${invoke.data.location}")

privateStringdata;

*@Author:TheBigBlue

*@Description:項(xiàng)目啟動，執(zhí)行sql文件初始化

*@Date:2025/9/19

*@Paramargs:

*@Return:

@Override

public