使用单个Connection对象连接两个MySQL数据源

问题描述

实际上，我需要连接在我的应用程序服务器中创建的两个 mysql 数据源。我正在使用 JPA2.0

Actually I need to connect two mysql datasource which are created in my application server. I'm using JPA2.0

我尝试了以下代码。但是我收到异常 java.lang.IllegalStateException：本地事务已具有1个非XA资源：无法添加更多资源。 。

I tried the following code. but i'm getting exception "java.lang.IllegalStateException: Local transaction already has 1 non-XA Resource: cannot add more resources.".

import com.google.common.collect.Lists;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import javax.annotation.Resource;
import javax.sql.DataSource;


public class SampleClass {

@Resource(name = "jdbc/source1")
DataSource source1;

@Resource(name = "jdbc/source2")
DataSource source2;

final List<String> list = Lists.newArrayList("Source1", "Source2");

public void getresponse() throws Exception {

    for (String source : list) {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = getConnection(source);
            statement = connection.createStatement();
        } catch (SQLException ex) {
        } finally {
            statement.close();
            connection.close();
        }
    }
}

public Connection getConnection(String source) {
    Connection conn = null;
    try {
    if(source.equalsIgnoreCase("Source1")){
        conn = source1.getConnection();
    } else if(source.equalsIgnoreCase("Source2")){
        conn = source2.getConnection();
    }
    } catch(SQLException se) {

    }

    return conn;
 }
}

对于我在代码中的逻辑。 我需要使用一个Connection对象连接所有数据源。

For my logic in the code. I need to connect all the data source using a single Connection object.

如何关闭现有连接（ conn ）对象并连接下一个数据源？

How can I close the existing connection (conn) object and connect the next datasource?

推荐答案

根据您的需要，您可以启用非事务连接

Depending on what you want, you can either enable 'Non Transactional Connections' on one of the datasources or you have to use an XA datasource on both.

要在Glassfish 3管理员中启用非事务连接，请导航至JDBC-> JDBC连接池-> YourPoolYouWantToEbleNontransactional，然后单击事务部分下的启用复选框。请注意，对于非事务连接来说，这意味着什么。

To enable a 'Non Transaction Connection' in the Glassfish 3 admin, navigate to JDBC -> JDBC Connection Pools -> YourPoolYouWantToEbleNontransactional, and click the enable check box under the Transactions section. Please be aware of what it means for a connection to be Non Transactional.

您还可以使用XA数据源驱动程序，该驱动程序实质上实现了两阶段提交。 Google告诉我数据源类的名称为com.mysql.jdbc.jdbc2.optional.MysqlXADataSource，但我不知道它是否以标准mysql jdbc分发形式分发。

You can also use an XA Datasource Driver which essentially implements 2-phase commit. Google tells me the datasource class name is 'com.mysql.jdbc.jdbc2.optional.MysqlXADataSource' but I do not know if it is distributed in the standard mysql jdbc distribution.

这篇关于使用单个Connection对象连接两个MySQL数据源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！