Connecting two MySQL data source using a single Connection object

Question

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

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

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

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

Answer 1

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.

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.

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.