使用SpringBoot 配置Oracle和H2双数据源及问题

程序员文章站 2022-04-06 20:37:46

目录配置pom配置yml配置注入问题在上节使用了h2之后感觉很爽，很轻便，正好有个项目要求简单，最好不适用外部数据库，于是就想着把h2数据库集成进来，这个系统已经存在了一个oracle，正好练习下配置...

在上节使用了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，这个问题我也是找了好久，因为在配置但数据源的时候没有这个问题的，在配置多数据源才有了这个问题。