logo标签列表

使用Spring Boot实现多个数据源Oracle和H2

javadatabasespring-bootdatabase-connectiondatasource
4
我将为您翻译以下it技术相关内容，涉及Spring Boot RestController的开发和数据库配置：

我开发了一个用于Angular项目的Spring Boot RestController，但遇到了一个问题。在我的rest服务逻辑中，我使用了两个不同的数据库来获取数据。

下面是数据源的配置：

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

记录输出：（此问题从未引发异常）
...
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()

问题在于，我的DataSourceService中每个使用的工作线程（http-nio-8080-?，...）在datasource.getConnection()处挂起。它永远冻结并等待无法获取的连接。

@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%的时间）它会挂起。

在我的pom.xml文件中，我有以下数据库驱动程序依赖项:

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

如果所有数据源都使用相同的驱动程序，则永远不会发生这种情况，但是在组合中，我发现了这种奇怪的行为。
我的想法是，只有在初始化oracle之前初始化h2Database时才会发生这种情况。此时，Spring类加载器会加载h2-driver类并将它们缓存。接下来，oracle被初始化，并且某些类非常相似，因此一些h2类用于oracle。由此产生了奇怪的冻结状态。（也许反之亦然）。
现在我的问题是：为什么我不能同时使用H2和Oracle数据库与Spring-boot？
编辑：
经过一些调试，我找到了更多信息：
[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中，可以通过className查找Driver-Class。"org.h2.Driver"的className可以被成功解析，但是"oracle.jdbc.OracleDriver"无法解析。

之后我进行了线程转储，在其中可以看到卡住的点。此时有5个不同位置被卡住的线程。

http-nio-8080-exec-2@8307（可运行）（仅使用oracle数据库）

"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 (可运行状态)
(Oracle与H2数据比较)

"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 (可运行) （Oracle和H2数据之间的比较）

（与exec-2相同）

http-nio-8080-exec-5@8310 (可运行) （仅使用H2数据库）

"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" 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)
  ...
您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - Stephane Nicoll
1个回答
2

我曾经遇到和你一样的问题，但是在使用h2和mysql时。我没有同时使用h2和oracle，在这个帖子中，也许你能找到一些有用的信息。

以下是我的双数据源配置：

Maven依赖项：

<!-- 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 配置类：

```

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配置类：

```

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来注释你的映射器类，例如：

```

@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() 方法以获取更多详细信息。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接