Shulou(Shulou.com)06/02 Report--

This article is about how to use SpringBoot to configure Oracle and H2 dual data sources. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Configure POM com.github.noraui noraui 2.4.0 com.h3database h3 1.4.197 com.baomidou mybatis-plus-boot-starter 3.1.1 configure ymlspring: http: encoding: charset: UTF-8 enabled: true force: true datasource: driver-class-name: org.h3.Driver schema: classpath:h3/schema-h3.sql data: classpath:h3 / data-h3.sql jdbc-url: jdbc:h3:file:D:/Cache/IdeaWorkSpace/BigData/CustomerModel/src/main/resources/h3/data/h3_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 h3: console: enabled: true path: / h3-console

You can see that two data sources are configured in the configuration, the main data source is H2, and the second data source is Oracle. The next step is to inject the data source through the configuration class.

Configuration injection

Configure H2 primary data source

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.h3", sqlSessionFactoryRef = "h3SqlSessionFactory") public class H2DSConfig {@ Bean (name = "h3DataSource") @ ConfigurationProperties (prefix = "spring.datasource") public DataSource dataSource () {return DataSourceBuilder.create () .build } @ Bean (name = "h3TransactionManager") public DataSourceTransactionManager transactionManager () {return new DataSourceTransactionManager (this.dataSource ());} @ Bean (name = "h3SqlSessionFactory") public SqlSessionFactory sqlSessionFactory (@ Qualifier ("h3DataSource") DataSource dataSource) throws Exception {final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean (); sessionFactory.setDataSource (dataSource); sessionFactory.getObject (). GetConfiguration (). SetMapUnderscoreToCamelCase (true); return sessionFactory.getObject () } @ Bean (name = "h3Template") public JdbcTemplate h3Template (@ Qualifier ("h3DataSource") DataSource dataSource) {return new JdbcTemplate (dataSource);}}

Configure oracle from the data source

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);}} question

Schema "classpath:h3/schema-h3.sql" not found

After the above configuration, we can use dual data sources, but when we test, we will find the following error: Schema "classpath:h3/schema-h3.sql" not found. I have been looking for this problem for a long time, because there is no such problem when configuring data sources, but only when configuring multiple data sources.

When configuring a single data source, SpringBoot automatically configures DataSource, which is normal. When configuring multiple data sources, we configure the data source through @ Configuration. We suspect that the problem lies in the class that DataSourceBuilder creates the data source, but this problem will not occur when the single data source is automatically loaded. Then Baidu searched the DataSourceBuilder and saw that the schema in the configuration of the instance in the article reads as follows:

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: after the service starts, initialize the database * @ Date: 2019-9-19 * / @ Componentpublic class ApplicationRunnerService implements ApplicationRunner {private static final Logger LOGGER = LoggerFactory.getLogger (ApplicationRunnerService.class); @ Autowired @ Qualifier ("h3Template") private JdbcTemplate h3Template; @ Value ("${invoke.schema.location}") private String schema; @ Value ("${invoke.data.location}") private String data / * * @ Author: TheBigBlue * @ Description: project start, perform sql file initialization * @ Date: 2019-9-19 * @ Param args: * @ Return: * * / @ Override public void run (ApplicationArguments args) {String schemaContent = this.getFileContent (schema); String dataContent = this.getFileContent (data); h3Template.execute (schemaContent) H3Template.execute (dataContent);} / * * @ Author: TheBigBlue * @ Description: get the contents of the sql file under classpath * @ 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 ("failed to load ClassPath resource", e) } finally {if (null! = bufferedReader) {try {bufferedReader.close ();} catch (IOException e) {e.printStackTrace ();} return data.toString ();}}

I changed it with the attitude of trying, and found that there was no problem! It turns out that the schema attribute in the DataSourceProperties class corresponding to schema after SpringBoot2.0 is a List, so you need to add-(yml-mapping set) in front of it to prevent further trampling.

Table "USER" not found; SQL statement:

This problem is also encountered only when configuring multiple data sources, that is, the configured spring.datasource.schema and spring.datasource.data are invalid. I took a look at this if it is to configure a single data source, springboot automatically loads Datasource, it is no problem, but now it is our own maintenance datasource: return DataSourceBuilder.create (). Build (); so it still feels like a problem when DataSourceBuilder loads the data source, but still can't find the reason. Some netizens said that the configuration of initialization-mode: ALWAYS must be added, but I can't use it after I configure it.

Finally, there is no way to configure a class, after springboot starts, load the file, read the sql contents, and then use jdbcTemplate to execute the next, simulate the initialization operation. If you have time to solve this problem later.

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: after the service starts, initialize the database * @ Date: 2019-9-19 * / @ Componentpublic class ApplicationRunnerService implements ApplicationRunner {private static final Logger LOGGER = LoggerFactory.getLogger (ApplicationRunnerService.class); @ Autowired @ Qualifier ("h3Template") private JdbcTemplate h3Template; @ Value ("${invoke.schema.location}") private String schema; @ Value ("${invoke.data.location}") private String data / * * @ Author: TheBigBlue * @ Description: project start, perform sql file initialization * @ Date: 2019-9-19 * @ Param args: * @ Return: * * / @ Override public void run (ApplicationArguments args) {String schemaContent = this.getFileContent (schema); String dataContent = this.getFileContent (data); h3Template.execute (schemaContent) H3Template.execute (dataContent);} / * * @ Author: TheBigBlue * @ Description: get the contents of the sql file under classpath * @ 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 ("failed to load ClassPath resource", e) } finally {if (null! = bufferedReader) {try {bufferedReader.close ();} catch (IOException e) {e.printStackTrace ();} return data.toString ();}} Thank you for reading! This is the end of the article on "how to use SpringBoot to configure Oracle and H2 dual data sources". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.