使用SpringBoot 配置Oracle和H2双数据源及问题
目录
- 配置POM
- 配置yml
- 配置注入
- 问题
在上节使用了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(); } }
以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。