欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

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

单数据源时,是直接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();
    }
}

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

抱着尝试的态度改了下,发现果然没问题了!!原来是在springboot2.0之后schema对应的datasourceproperties类中schema属性是一个list,所以需要前面加 - (yml中加-映射集合),记录下防止后面再踩坑。

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

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

table “user” not found; sql statement:

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

这个问题也是在只有配置多数据源时才会碰到的问题,就是配置的spring.datasource.schema和spring.datasource.data无效。这个我看了下如果是配置单数据源,springboot自动加载datasource,是没问题的,但是现在是我们自己维护的datasource: return datasourcebuilder.create().build();所以感觉还是datasourcebuilder在加载数据源的时候的问题,但是还是没有找到原因。有网友说必须加initialization-mode: always这个配置,但是我配置后也是不能用的。

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

最后没办法就配置了一个类,在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();
    }
}

以上为个人经验,希望能给大家一个参考,也希望大家多多支持。