使用SpringBoot 配置Oracle和H2雙數據源及問題

Posted on by

在上節使用瞭H2之後感覺很爽，很輕便，正好有個項目要求簡單，最好不適用外部數據庫，於是就想著把H2數據庫集成進來，這個系統已經存在瞭一個Oracle，正好練習下配置多數據源，而在配置多數據源時，H2的schema配置不生效真是花瞭我好長時間才解決。。。所以也記錄一下

配置POM

<!-- oracle -->
 <dependency>
     <groupId>com.github.noraui</groupId>
     <artifactId>noraui</artifactId>
     <version>2.4.0</version>
 </dependency>
<!-- h2-->
 <dependency>
     <groupId>com.h2database</groupId>
     <artifactId>h2</artifactId>
     <version>1.4.197</version>
 </dependency>
 <!-- mybatisplus -->
 <dependency>
 	  <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>3.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
  h2:
    console:
      enabled: true
      path: /h2-console

可以看到配置中配置瞭兩個數據源，主數據源是H2，第二個數據源是Oracle，接下來是通過配置類來註入數據源

配置註入

配置H2主數據源

package com.caxs.warn.config;
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.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
/**
 * @Author: TheBigBlue
 * @Description:
 * @Date: 2019/9/18
 */
@Configuration
@MapperScan(basePackages = "com.caxs.warn.mapper.h2", sqlSessionFactoryRef = "h2SqlSessionFactory")
public class H2DSConfig {
    @Bean(name = "h2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }
    @Bean(name = "h2TransactionManager")
    public DataSourceTransactionManager transactionManager() {
        return new DataSourceTransactionManager(this.dataSource());
    }
    @Bean(name = "h2SqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("h2DataSource") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return sessionFactory.getObject();
    }
    @Bean(name = "h2Template")
    public JdbcTemplate h2Template(@Qualifier("h2DataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

配置oracle從數據源

package com.caxs.warn.config;
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.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
/**
 * @Author: TheBigBlue
 * @Description:
 * @Date: 2019/9/18
 */
@Configuration
@MapperScan(basePackages = "com.caxs.warn.mapper.oracle",sqlSessionFactoryRef = "oracleSqlSessionFactory")
public class OracleDSConfig {
    @Bean(name = "oracleDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.oracle")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }
    @Bean(name = "oracleTransactionManager")
    public DataSourceTransactionManager transactionManager() {
        return new DataSourceTransactionManager(this.dataSource());
    }
    @Bean(name = "oracleSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("oracleDataSource") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return sessionFactory.getObject();
    }
    @Bean(name = "oracleTemplate")
    public JdbcTemplate oracleTemplate(@Qualifier("oracleDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

問題

Schema “classpath:h2/schema-h2.sql” not found

  

經過上面的配置就可以使用雙數據源瞭，但是當我們測試時會發現報如下錯誤：Schema “classpath:h2/schema-h2.sql” not found，這個問題我也是找瞭好久，因為在配置但數據源的時候沒有這個問題的，在配置多數據源才有瞭這個問題。

在這裡插入圖片描述

  

單數據源時，是直接SpringBoot自動配置DataSource的，這個時候是正常的，而當配置多數據源時，我們是通過@Configuration來配置數據源的，懷疑問題出在 DataSourceBuilder 創建數據源這個類上，而單數據源自動裝載時不會出現這樣的問題。然後百度搜瞭下這個DataSourceBuilder，看到文章中實例的配置中schema是這樣寫的：

package com.caxs.warn.service;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
/**
 * @Author: TheBigBlue
 * @Description: 服務啟動後，初始化數據庫
 * @Date: 2019/9/19
 */
@Component
public class ApplicationRunnerService implements ApplicationRunner {
    private static final Logger LOGGER = LoggerFactory.getLogger(ApplicationRunnerService.class);
    @Autowired
    @Qualifier("h2Template")
    private JdbcTemplate h2Template;
    @Value("${invoke.schema.location}")
    private String schema;
    @Value("${invoke.data.location}")
    private String data;
    /**
     * @Author: TheBigBlue
     * @Description: 項目啟動，執行sql文件初始化
     * @Date: 2019/9/19
     * @Param args:
     * @Return:
     **/
    @Override
    public void run(ApplicationArguments args) {
        String schemaContent = this.getFileContent(schema);
        String dataContent = this.getFileContent(data);
        h2Template.execute(schemaContent);
        h2Template.execute(dataContent);
    }
    /**
     * @Author: TheBigBlue
     * @Description: 獲取classpath下sql文件內容
     * @Date: 2019/9/19
     * @Param filePath:
     * @Return:
     **/
    private String getFileContent(String filePath) {
        BufferedReader bufferedReader = null;
        String string;
        StringBuilder data = new StringBuilder();
        try {
            ClassPathResource classPathResource = new ClassPathResource(filePath);
            bufferedReader = new BufferedReader(new InputStreamReader(classPathResource.getInputStream()));
            while ((string = bufferedReader.readLine()) != null) {
                data.append(string);
            }
        } catch (IOException e) {
            LOGGER.error("加載ClassPath資源失敗", e);
        }finally {
            if(null != bufferedReader){
                try {
                    bufferedReader.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return data.toString();
    }
}

在這裡插入圖片描述   

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

在這裡插入圖片描述

在這裡插入圖片描述

Table “USER” not found; SQL statement:

在這裡插入圖片描述   

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

在這裡插入圖片描述   

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

package com.caxs.warn.service;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
/**
 * @Author: TheBigBlue
 * @Description: 服務啟動後，初始化數據庫
 * @Date: 2019/9/19
 */
@Component
public class ApplicationRunnerService implements ApplicationRunner {
    private static final Logger LOGGER = LoggerFactory.getLogger(ApplicationRunnerService.class);
    @Autowired
    @Qualifier("h2Template")
    private JdbcTemplate h2Template;
    @Value("${invoke.schema.location}")
    private String schema;
    @Value("${invoke.data.location}")
    private String data;
    /**
     * @Author: TheBigBlue
     * @Description: 項目啟動，執行sql文件初始化
     * @Date: 2019/9/19
     * @Param args:
     * @Return:
     **/
    @Override
    public void run(ApplicationArguments args) {
        String schemaContent = this.getFileContent(schema);
        String dataContent = this.getFileContent(data);
        h2Template.execute(schemaContent);
        h2Template.execute(dataContent);
    }
    /**
     * @Author: TheBigBlue
     * @Description: 獲取classpath下sql文件內容
     * @Date: 2019/9/19
     * @Param filePath:
     * @Return:
     **/
    private String getFileContent(String filePath) {
        BufferedReader bufferedReader = null;
        String string;
        StringBuilder data = new StringBuilder();
        try {
            ClassPathResource classPathResource = new ClassPathResource(filePath);
            bufferedReader = new BufferedReader(new InputStreamReader(classPathResource.getInputStream()));
            while ((string = bufferedReader.readLine()) != null) {
                data.append(string);
            }
        } catch (IOException e) {
            LOGGER.error("加載ClassPath資源失敗", e);
        }finally {
            if(null != bufferedReader){
                try {
                    bufferedReader.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return data.toString();
    }
}

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。

推薦閱讀：