使用SpringBoot 配置Oracle和H2雙數據源及問題
在上節使用瞭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。
推薦閱讀:
- Spring操作JdbcTemplate數據庫的方法學習
- Springboot動態切換數據源的具體實現與原理分析
- springboot如何連接兩個數據庫(多個)
- 詳解SpringBoot封裝使用JDBC
- SpringBoot封裝JDBC的實現步驟