Spring Boot with multiple datasources Oracle and H2

Question

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]

#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]

@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()

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

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.

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.

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).

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

EDIT:

After some debugging I have found more informations:

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

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

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) (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) (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) (comparison between oracle and h2 data)

(same as exec-2)

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 (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)
  ...

doesn't seem legit to me and I'd argue it has nothing to do with Spring Boot. Since you are configuring your own DataSource you completely bypass what Spring Boot does for you. Maybe the pool has not enough connection and you're not closing the connection properly? Again, not related to Spring Boot. — Stephane Nicoll
– Stephane Nicoll, Commented Nov 22, 2016 at 14:38

BeeNoisy · Accepted Answer · 2016-11-23 01:18:02Z

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 dependency:

 <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.1.1</version> </dependency>  <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

```

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

```

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

```

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
    );
...

```

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.

Collectives™ on Stack Overflow

Spring Boot with multiple datasources Oracle and H2

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related