java JDBC - 连接多个数据库

JDBC - Connect Multiple Databases

提问by jagamot

I am working on an application where I need to connect N number of database systems [N ranges any where between 1 to 350].

我正在开发一个应用程序，我需要连接 N 个数据库系统 [N 的范围在 1 到 350 之间]。

The idea is - the user will be presented with a list of databases and will be asked to select any or all of the databases from the list.

这个想法是 - 用户将看到一个数据库列表，并被要求从列表中选择任何或所有数据库。

Once the databases are selected, I need to connect to each of the database and execute a stored procedure.

选择数据库后，我需要连接到每个数据库并执行存储过程。

I am planning to use plain old JDBC and obtain connection for each of them one a time [or by running them in multiple threads] and execute the store procedure and close the connection.

我打算使用普通的旧 JDBC 并一次为每个连接获取连接 [或通过在多个线程中运行它们] 并执行存储过程并关闭连接。

And all this should happen in a transaction. What is the best way to do this?

而这一切都应该在交易中发生。做这个的最好方式是什么？

If not JDBC...any other efficient way?

如果不是 JDBC ......还有其他有效的方法吗？

Update -

更新 -

The stored procedure is actually involved in running some sql - for example updating a column, grant permission for a user etc.

存储过程实际上涉及运行一些 sql - 例如更新列、授予用户权限等。

回答by BalusC

I'd create a threadpool with a reasonable maximum amount of threads, between ten and twenty threads maybe, with help of Executors#newFixedThreadPool()and invoke the separate DB connecting and SP executing tasks each as a Callableusing ExecutorService#invokeAll(). You would like to play with the threadcount and profile which yields the best performance after all.

我会创建一个具有合理最大线程数的线程池，可能在 10 到 20 个线程之间，借助Executors#newFixedThreadPool()并调用单独的 DB 连接和 SP 执行任务，每个任务都作为Callableusing ExecutorService#invokeAll(). 您想使用线程数和配置文件，这毕竟会产生最佳性能。

Each Callableimplementation should take the connection details and SP name as constructor argument so that you can reuse the same implementation for different DB calls.

每个Callable实现都应将连接详细信息和 SP 名称作为构造函数参数，以便您可以为不同的 DB 调用重用相同的实现。

---

---

Update: OK, it's a webapplication. You don't want to waste threads. If it is supposed to be used by a single concurrent user, then you should really ensure that the threadpool is properly shutdownat end of request or at highest end of session. But if it is supposed to be used by multiple concurrent users, then you'd like to share the threadpool in the application scope. Also here, you need to ensure that it is properly shutdown when the webapp shuts down. The ServletContextListeneris useful here.

更新：好的，这是一个网络应用程序。你不想浪费线程。如果它应该由单个并发用户使用，那么您应该真正确保线程池在请求结束或会话最高结束时正确关闭。但是如果它应该被多个并发用户使用，那么您希望在应用程序范围内共享线程池。同样在这里，您需要确保在 webapp 关闭时正确关闭它。在ServletContextListener这里很有用。

回答by Zon

If it is acceptable for you to use two connections, use connection pool c3p0to manage them. To connect two databases I declare:

如果您可以使用两个连接，则使用连接池c3p0来管理它们。为了连接两个数据库，我声明：

public Connection connection1;
public Connection connection2;
DataSource dataSource1;
DataSource dataSource2;

Then two similar methods:

然后是两个类似的方法：

public Connection dbConnect1() throws SQLException {
    ComboPooledDataSource cpds = new ComboPooledDataSource();
    try {
        cpds.setDriverClass("com.mysql.jdbc.Driver");
    } catch (PropertyVetoException e) {
    }
    cpds.setJdbcUrl("jdbc:mysql://localhost:3306/myDatabase1?autoReconnect=true"); 
    cpds.setUser("myMYSQLServerLogin");
    cpds.setPassword("myMYSQLServerPassword");
    cpds.setMinPoolSize(5);
    cpds.setAcquireIncrement(5);
    cpds.setMaxPoolSize(20);
    cpds.setMaxIdleTime(60);
    cpds.setMaxStatements(100);
    cpds.setPreferredTestQuery("SELECT 1");
    cpds.setIdleConnectionTestPeriod(60);
    dataSource1 = cpds;
    connection1 = dataSource1.getConnection();
    return connection1;
}

public Connection dbConnect2() throws SQLException {
    ComboPooledDataSource cpds = new ComboPooledDataSource();
    try {
        cpds.setDriverClass("com.mysql.jdbc.Driver");
    } catch (PropertyVetoException e) {
    }
    cpds.setJdbcUrl("jdbc:mysql://localhost:3306/myDatabase2?autoReconnect=true"); 
    cpds.setUser("myMYSQLServerLogin");
    cpds.setPassword("myMYSQLServerPassword");
    cpds.setMinPoolSize(5);
    cpds.setAcquireIncrement(5);
    cpds.setMaxPoolSize(20);
    cpds.setMaxIdleTime(60);
    cpds.setMaxStatements(100);
    cpds.setPreferredTestQuery("SELECT 1");
    cpds.setIdleConnectionTestPeriod(60);
    dataSource2 = cpds;
    connection2 = dataSource2.getConnection();
    return connection2;
}

回答by Romain Hippeau

As duffymo indicated in his comment, you will only be able to do transactions across multiple databases if you have a transaction coordinator and two phase commit.

正如 duffymo 在他的评论中指出的那样，如果您有事务协调器和两阶段提交，您将只能跨多个数据库执行事务。

For this you will need a J2EE stack that will handle JTA. If you are running in Tomcat or another container that does not have JTA, there are several options you can download and install.

为此，您需要一个处理 JTA 的 J2EE 堆栈。如果您在 Tomcat 或其他没有 JTA 的容器中运行，您可以下载和安装多个选项。

Of course you will need to let the Container, not the database/stored procedure handle the transaction commits and rollbacks.

当然，您需要让容器而不是数据库/存储过程来处理事务提交和回滚。

回答by Sandeep Sharma

public static Connection getconnection(String db,String host){
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://"+**Pass Your Host Here 
Like Localhost**+"/"+Pass Your DB Name** +"?useUnicode=yes&characterEncoding=UTF- 
8","root","root");
return con;
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
} 
}

回答by duffymo

This sounds like a big mess, but it's your problem.

这听起来像是一团糟，但这是你的问题。

You need one connection pool per database. I wouldn't advise that you try to handle the connection lifecycle yourself. Let the app server do that for you.

每个数据库需要一个连接池。我不建议您尝试自己处理连接生命周期。让应用服务器为你做这件事。

If you want a group of databases to participate in one big transaction you'll have to use the JDBC XA drivers for allof them. You'll also need a JTA transaction manager to oversee the transaction for you.

如果您希望一组数据库参与一个大事务，则必须对所有数据库使用 JDBC XA 驱动程序。您还需要一个 JTA 事务管理器来为您监督事务。

The stored procedures cannot contain any logic to handle transactions; you have to let JTA do it.

存储过程不能包含任何处理事务的逻辑；你必须让 JTA 去做。

You don't say what the stored procedure is doing. If it doesn't need to return anything, an alternative design might be JMS, a queue, and a listener pool. I'd be worried about threading if I were you. I'd find a way to let the container do that complicated stuff for meif I could.

你没有说存储过程在做什么。如果不需要返回任何内容，替代设计可能是 JMS、队列和侦听器池。如果我是你，我会担心穿线。如果可以的话，我会找到一种方法让容器为我做那些复杂的事情。