Spring引导与多个数据源Oracle和H2 [英] Spring Boot with multiple datasources Oracle and H2

问题描述

我开发一个春天启动RestController为我的角项目和遇到一个问题。在我的逻辑休息服务我使用两个不同的数据库来获取数据。

I develop a spring boot RestController for my angular Project and encountered a problem. In my logic for the rest-service I used two different databases to get data from.

在这里你可以看到数据源配置：

Here you can see the datasource configuration:

[application.properties]

[application.properties]

#datasource1
spring.datasource.url=[url]
spring.datasource.username=[username]
spring.datasource.password=[password]
spring.datasource.driverClassName=org.h2.Driver

#datasource2
spring.secondDatasource.url=[url]
spring.secondDatasource.username=[username]
spring.secondDatasource.password=[password]
spring.secondDatasource.driverClassName=oracle.jdbc.OracleDriver

[DatasourceConfig.java]

[DatasourceConfig.java]

@Bean
@Primary
@ConfigurationProperties(prefix="spring.datasource")
public DataSource h2DataSource() {
    return DataSourceBuilder.create().build();
}

@Bean
@ConfigurationProperties(prefix="spring.secondDatasource")
public DataSource oracleDataSource() {
    return DataSourceBuilder.create().build();
}

记录输出：（此异常不会引发异常）

Logging output: (No Exceptions are ever thrown for this problem)

...
2016-11-22 13:20:25.853 [INFO ] 1  [main] d.b.s.Application : Started Application in 7.757 seconds (JVM running for 12.515)
2016-11-22 13:20:31.731 [INFO ] 62 [http-nio-8080-exec-1] o.s.w.s.DispatcherServlet : FrameworkServlet 'dispatcherServlet': initialization started
2016-11-22 13:20:31.757 [INFO ] 62 [http-nio-8080-exec-1] o.s.w.s.DispatcherServlet : FrameworkServlet 'dispatcherServlet': initialization completed in 26 ms
2016-11-22 13:20:34.984 [INFO ] 65 [http-nio-8080-exec-5] d.b.s.r.RESTclient : /rest/getMyData
2016-11-22 13:20:34.992 [INFO ] 63 [http-nio-8080-exec-2] d.b.s.r.RESTclient : /rest/getMyData2
2016-11-22 13:20:34.993 [INFO ] 69 [http-nio-8080-exec-8] d.b.s.r.RESTclient : /rest/getMyData3
2016-11-22 13:20:35.004 [DEBUG] 65 [http-nio-8080-exec-5] d.b.s.DataSourceService : getH2Connection()
2016-11-22 13:20:35.022 [DEBUG] 63 [http-nio-8080-exec-2] d.b.s.DataSourceService : getOracleConnection()
2016-11-22 13:20:35.022 [DEBUG] 69 [http-nio-8080-exec-8] d.b.s.DataSourceService : getH2Connection()

$ b < ...）在我的DataSourceService中的datasource.getConnection（）挂起。它永远冻结，并等待无法获取的连接。

The problem is, that every used worker thread (http-nio-8080-?, ...) for the rest-service hangs up at datasource.getConnection() in my DataSourceService. It freeze forever and waits for a Connection that can't be get.

@Service
public class DataSourceService {
    private final DataSource h2DataSource;
    private final DataSource oracleDataSource;

    @Autowired
    public DataSourceService(DataSource h2DataSource, DataSource oracleDataSource) {
        this.h2DataSource = h2DataSource;
        this.oracleDataSource = oracleDataSource;
    }

    public Connection getH2Connection() throws SQLException {
        LoggerUtil.logDebug(getClass(), "getH2Connection()");
        return h2Connection.getConnection();
    }

    public Connection getOracleConnection() throws SQLException {
        LoggerUtil.logDebug(getClass(), "getOracleConnection()");
        return oracleConnection.getConnection();
    }
}

奇怪的是，没有改变一行代码（5％的所有时间），大多数（95％的时间）它挂起。

The strange thing about it is, that sometimes it works without ever changed a line of code (5% of all times) and mostly (95% of all times) it hangs up.

在我的pom.xml我有以下数据库驱动程序依赖性：

in my pom.xml I have following database driver dependencies:

<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.4</version>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.193</version>
    <scope>runtime</scope>
</dependency>

如果所有数据源都有相同的驱动程序，这将永远不会发生，但在组合中，我发现这个奇怪的行为。

If all datasources have the same driver, this will never happen, but in combination I found this strange behavior.

我的想法是，只有当h2Database在oracle之前初始化时才会发生。此时，spring类加载器加载h2-driver类，它们将被缓存。在下一步，oracle被初始化，一些类将关闭相同，所以一些h2类用于oracle。因此，产生了奇怪的冻结状态。 （也许反之亦然）。

My ideas are that this only happen if the h2Database is initialized before the oracle. At this point the spring classloader loads the h2-driver classes and they will be cached. At next the oracle gets initialized and some classes are to close identical, so that some of the h2-classes are used for oracle. Because of that, the strange state of freeze is created. (perhaps also vice versa).

我的问题现在：为什么我不能同时使用H2和Oracle数据库与Spring- / strong>

My question now: Why can't I use the H2 and Oracle database at the same time with Spring-boot?

编辑：

经过一些调试后，我发现了更多的信息：

After some debugging I have found more informations:

[org.apache.tomcat.jdbc.pool.ClassLoaderUtil]

[org.apache.tomcat.jdbc.pool.ClassLoaderUtil]

Row 29:  loadClass(...)
...
Row 38: return Class.forName(className, true, cl);  //className: "oracle.jdbc.OracleDriver"  cl: Launcher$AppClassLoader

在ClassLoaderUtil中， for Driver-Class by className。 classNameorg.h2.Driver可以解决没有问题，但是oracle.jdbc.OracleDriver不。

In ClassLoaderUtil is a lookup for Driver-Class by className. The className "org.h2.Driver" can be resolved without problem, but "oracle.jdbc.OracleDriver" don't.

之后，我做了线程转储在那里你可以看到卡住的点。

After that I have made a thread dump where you can see the points for stuck. At this time there are 5 stucked threads which stucked at different positions.

http-nio-8080-exec-2 @ 8307（runnable）
（正在使用只有oracle数据库）

http-nio-8080-exec-2@8307 (runnable) (is using only oracle database)

"http-nio-8080-exec-2@8307" daemon prio=5 tid=0x40 nid=NA runnable
  java.lang.Thread.State: RUNNABLE
     blocks http-nio-8080-exec-6@8311
      at java.lang.Class.forName0(Class.java:-1)
      at java.lang.Class.forName(Class.java:348)
      at org.apache.tomcat.jdbc.pool.ClassLoaderUtil.loadClass(ClassLoaderUtil.java:38)
      at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:271)
      at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:203)
      at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:718)
      at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:650)
      at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:468)
      at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:143)
      at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:118)
      - locked <0x2139> (a org.apache.tomcat.jdbc.pool.DataSource)
      at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:107)
      at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:131)
      at d.b.s.DataSourceService.getConnection(DataSourceService.java:51)                        //<-- That's my package
      ...

http-nio-8080-exec-3 @ 8308（runnable）
（oracle和h2数据之间的比较）

http-nio-8080-exec-3@8308 (runnable) (comparison between oracle and h2 data)

"http-nio-8080-exec-3@8308" daemon prio=5 tid=0x41 nid=NA runnable
  java.lang.Thread.State: RUNNABLE
  at oracle.jdbc.driver.OracleDriver.<clinit>(OracleDriver.java:190)
  at java.lang.Class.forName0(Class.java:-1)
  at java.lang.Class.forName(Class.java:348)
  at org.apache.tomcat.jdbc.pool.ClassLoaderUtil.loadClass(ClassLoaderUtil.java:38)
  at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:271)
  at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:203)
  at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:718)
  at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:650)
  at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:468)
  at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:143)
  at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:118)
  - locked <0x213d> (a org.apache.tomcat.jdbc.pool.DataSource)
  at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:107)
  at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:131)
  at d.b.s.DataSourceService.getConnection(DataSourceService.java:51)
  ...

http-nio-8080-exec-4 @ 8309（runnable）
（oracle和h2数据之间的比较）

http-nio-8080-exec-4@8309 (runnable) (comparison between oracle and h2 data)

（与exec-2相同）

(same as exec-2)

http-nio-8080-exec-5 @ 8310（runnable）
只有h2数据库）

http-nio-8080-exec-5@8310 (runnable) (is using only h2 database)

"http-nio-8080-exec-5@8310" daemon prio=5 tid=0x43 nid=NA runnable
   java.lang.Thread.State: RUNNABLE
  at sun.reflect.GeneratedConstructorAccessor109.newInstance(Unknown Source:-1)
  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:-1)
  at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
  at java.lang.Class.newInstance(Class.java:442)
  at java.util.ServiceLoader$LazyIterator.nextService(ServiceLoader.java:380)
  at java.util.ServiceLoader$LazyIterator.next(ServiceLoader.java:404)
  at java.util.ServiceLoader$1.next(ServiceLoader.java:480)
  at java.sql.DriverManager$2.run(DriverManager.java:603)
  at java.sql.DriverManager$2.run(DriverManager.java:583)
  at java.security.AccessController.doPrivileged(AccessController.java:-1)
  at java.sql.DriverManager.loadInitialDrivers(DriverManager.java:583)
  at java.sql.DriverManager.<clinit>(DriverManager.java:101)
  at org.h2.Driver.load(Driver.java:155)
  at org.h2.Driver.<clinit>(Driver.java:41)
  at java.lang.Class.forName0(Class.java:-1)
  - locked <0x211b> (a java.lang.Class)
  at java.lang.Class.forName(Class.java:348)
  at org.apache.tomcat.jdbc.pool.ClassLoaderUtil.loadClass(ClassLoaderUtil.java:38)
  at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:271)
  at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:203)
  at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:718)
  at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:650)
  at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:468)
  at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:143)
  at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:118)
  at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:107)
  at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:131)
  - locked <0x20e9> (a org.apache.tomcat.jdbc.pool.DataSource)
  at d.b.s.DataSourceService.getConnection(DataSourceService.java:51)
  ...

http-nio-8080-exec-6 @ 8311（等待监视器输入）
（在oracle和h2数据之间进行比较）

http-nio-8080-exec-6@8311 (waiting for monitor entry) (comparison between oracle and h2 data)

"http-nio-8080-exec-6@8311" daemon prio=5 tid=0x44 nid=NA waiting for monitor entry
   java.lang.Thread.State: BLOCKED
 waiting for http-nio-8080-exec-2@8307 to release lock on <0x2139> (a org.apache.tomcat.jdbc.pool.DataSource)
  at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:115)
  at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:107)
  at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:131)
  at d.b.s.DataSourceService.getConnection(DataSourceService.java:51)
  ...

推荐答案

我遇到了像你一样的问题，但在h2和mysql。我不是使用h2和oracle同时，在这 post ，也许你可以找到一些有用的。

I have meet the same question like you, but in h2 and mysql.I am not use h2 and oracle at same time, in this post, maybe you can find some useful.

这里是我的双数据源配置：

Here is my double datasource config:

maven依赖项：


<！ - stater mybatis - >
< dependency>
< groupId> org.mybatis.spring.boot< / groupId>
< artifactId> mybatis-spring-boot-starter< / artifactId>
< version> 1.1.1< / version>
< / dependency>
<！ - jdbc - >
< dependency>
< groupId> mysql< / groupId>
< artifactId> mysql-connector-java< / artifactId>
< scope> runtime< / scope>
< / dependency>


```

datasource:
  km:
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/km?useUnicode=true&characterEncoding=UTF-8&useSSL=false
    username: root
    password: 123456
    max-active: 100
    max-idle: 10
    max-wait: 10000
    test-while-idle: true
  esb:
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/esb?useUnicode=true&characterEncoding=UTF-8&useSSL=false
    username: root
    password: 123456
    max-active: 100
    max-idle: 10
    max-wait: 10000
    test-while-idle: true

```

资料来源km配置类别：
```

Datasource km config class: ```

import com.package.km.api.commons.config.datasource.annotation.UseDatasourceKM;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.lang.invoke.MethodHandles;
import java.sql.SQLException;

/**
 * km datasource config
 * Created by BeeNoisy on 16/5/23.
 */
@Configuration
@MapperScan(basePackages = "package.km", annotationClass = UseDatasourceKM.class, sqlSessionFactoryRef = KMDatasourceConfig.SQL_SESSION_FACTORY_NAME)
public class KMDatasourceConfig {
    public static final String SQL_SESSION_FACTORY_NAME = "sessionFactoryKm";
    public static final String TX_MANAGER = "txManagerKm";
    private static final Logger logger = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass());

    @Bean(name = "datasourceKm")
    @Primary
    @ConfigurationProperties(prefix = "datasource.km")
    public DataSource dataSourceKm() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = TX_MANAGER)
    @Primary
    public PlatformTransactionManager txManagerKm() {
        return new DataSourceTransactionManager(dataSourceKm());
    }

    @Bean(name = KMDatasourceConfig.SQL_SESSION_FACTORY_NAME)
    @Primary
    public SqlSessionFactory sqlSessionFactoryBean() throws Exception {

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setConfigLocation(new ClassPathResource("/mybatis/mybatis-conf.xml"));
        sqlSessionFactoryBean.setDataSource(dataSourceKm());
        return sqlSessionFactoryBean.getObject();
    }
}

```

这里是数据源esb config类：

Here is datasource esb config class:

```

import com.package.km.api.commons.condition.EsbEnabledCondition;
import com.package.km.api.commons.config.datasource.annotation.UseDatasourceESB;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Conditional;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.lang.invoke.MethodHandles;

/**
 * Created by BeeNoisy on 16/5/23.
 */
@Configuration
@Conditional(EsbEnabledCondition.class)
@MapperScan(basePackages = "package.esb", annotationClass = UseDatasourceESB.class, sqlSessionFactoryRef = EsbDatasourceConfig.SQL_SESSION_FACTORY_NAME)
public class EsbDatasourceConfig {
    public static final String SQL_SESSION_FACTORY_NAME = "sessionFactoryEsb";
    public static final String TX_MANAGER = "txManagerEsb";
    private static final Logger logger = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass());

    @Bean(name = "datasourceEsb")
    @Conditional(EsbEnabledCondition.class)
    @ConfigurationProperties(prefix = "datasource.esb")
    public DataSource dataSourceEsb() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = TX_MANAGER)
    @Conditional(EsbEnabledCondition.class)
    public PlatformTransactionManager txManagerEsb() {
        return new DataSourceTransactionManager(dataSourceEsb());
    }

    @Bean(name = EsbDatasourceConfig.SQL_SESSION_FACTORY_NAME)
    @Conditional(EsbEnabledCondition.class)
    public SqlSessionFactory sqlSessionFactoryBean() throws Exception {

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setConfigLocation(new ClassPathResource("/mybatis/mybatis-conf.xml"));
        sqlSessionFactoryBean.setDataSource(dataSourceEsb());
        return sqlSessionFactoryBean.getObject();
    }
}

```

然后，您可以使用两个注释： UseDatasourceESB 或 UseDatasourceKM ：

Then you can use the two annotation: UseDatasourceESB or UseDatasourceKM to annotate your mapper class like:

```

@UseDatasourceKM
public interface GroupBaseDAO {
    public static final String COL_ALL = " id, name, create_time, last_update_time "; 
    public static final String TABLE = " group_base "; 

    @Select(" select " + COL_ALL + " from " + TABLE + " where id = #{id} ")
    public GroupBase findById(@Param("id") int id);

    @Select(" select " +
            COL_ALL +
            " from " +
            TABLE +
            " where id < #{lastId} " +
            " limit #{count} ")
    public List<GroupBase> list(
            @Param("lastId") int lastId,
            @Param("count") int count
    );
...

```

mysql\h2和oracle中有两个数据源。
你可以在调试模型中运行你的代码，然后进入 getConnection（）方法查找更多细节。

There is two datasource in mysql\h2 and oracle. y the way, you can run your code in debug model, and step into getConnection() method to find more details.

这篇关于Spring引导与多个数据源Oracle和H2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！