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?

Then why aren't you using EntityManager pointing to respective persistence unit configured for different data-source. Can refer this link http://stackoverflow.com/a/4601469/366964 for further details. — Nayan Wadekar, Feb 19 '13 at 07:41
Can you not use mysql replication? How about the mysql proxy library: http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy.html? — Travis Stevens, Feb 21 '13 at 22:33

score 2 · Accepted Answer · answered Feb 21 '13 at 22:22

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.

After posting my answer I realized that you you want to commit the same thing to the same database. You will probably have to use the XA Datasource Driver to ensure that both databases received the statement. — Travis Stevens, Feb 21 '13 at 22:28

Connecting two MySQL data source using a single Connection object

1 Answers1

Linked