oracle JDBC 基本概念，池化和线程化

JDBC fundamental concepts, Pooling and Threading

提问by David Hofmann

I was always using JDBC in JavaSE on single-threaded environment. But now I need to use a connection pool and let many threads to have interaction with the database (MSSQL and Oracle) and I am having a hard time trying to make it as it seems that I am lacking some fundamental undestanding of the api.

我一直在单线程环境中的 JavaSE 中使用 JDBC。但是现在我需要使用连接池并让许多线程与数据库（MSSQL 和 Oracle）进行交互，我很难做到这一点，因为我似乎对 api 缺乏一些基本的理解。

AFAIK after connect and logging a Connectionrepresents a phisical tcp/ip connection to the database. It creates Statement(s) that can be seen as SQL interaction(s) with the database over the Connection.

AFAIK 连接并记录 a 后Connection表示与数据库的物理 tcp/ip 连接。它创建了一个Statement或多个可以被视为与数据库的 SQL 交互的Connection.

• Where does the transaction and rollback comes in ? Is it at the Connectionor Statementlevel.
• Is it safe that 'one' Connectioncreate N statements and give it to diferent threads so to let each one own the use of that Statement?

• 事务和回滚从何而来？是在Connection还是Statement级别。
• 'one'Connection创建 N 个语句并将其提供给不同的线程以便让每个人拥有该语句的使用权是否安全Statement？

If not, and after configuring the pool something like this:

如果没有，在配置池后如下所示：

OracleDataSource ods = new OracleDataSource(); 
ods.setURL("jdbc:oracle:thin:@tnsentryname");
ods.setUser("u");
ods.setPassword("p");

• BTW, where do I set the connection pool size ?

• Is this what I would be doing in each thread in order to correctly use the connection ?

• 顺便说一句，我在哪里设置连接池大小？

• 这是我为了正确使用连接而在每个线程中要做的事情吗？

//thead run method

//thead运行方法

Connection conn = ods.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("the sql");
// do what I need to do with rs
rs.close();
int updateStatus = stmt.executeUpdate("the update");
stmt.close();
conn.close();

// end of thread run method

//线程结束运行方法

• If any physical Connection of the Pool somehow crashes or disconects, will the pool automaticaly try to reconnect and inject the new connection in the pool so that subsequent pool.getConnection() will just get a health connection ?

• 如果池的任何物理连接以某种方式崩溃或断开连接，池是否会自动尝试重新连接并将新连接注入池中，以便后续的 pool.getConnection() 将获得健康连接？

Thanks a lot and forgive my bad english please.

非常感谢，请原谅我糟糕的英语。

采纳答案by teabot

Connection pools decorate Connection and Statement instances with their own wrapper implementations. When you call close on a connection you are actually just releasing it back to the pool. When you call close on a prepared statement you are actually just releasing it back to the connection's statement cache. When you prepare a statement you might just be fetching a cached statement instance from the connection. All this is hidden from view so that you don't have to worry about it.

连接池用它们自己的包装器实现来装饰 Connection 和 Statement 实例。当您对连接调用 close 时，您实际上只是将其释放回池中。当您对准备好的语句调用 close 时，您实际上只是将其释放回连接的语句缓存。准备语句时，您可能只是从连接中获取缓存的语句实例。所有这些都是隐藏的，因此您不必担心。

When a connection is given to a client it is no longer available for any other client to use until the connection is released back to the pool. You generally just fetch connections when you need them and then return them as soon as you are finished with them. Because the connections are being held open in the pool there is little overhead in fetching and releasing connections.

当一个连接被提供给一个客户端时，它不再可供任何其他客户端使用，直到该连接被释放回池中。您通常只在需要时获取连接，然后在完成后立即返回它们。由于连接在池中保持打开状态，因此获取和释放连接的开销很小。

You should use a connection from the pool just as you would a single JBDC connection and follow best-practices regarding the closing of resources so that you do not leak any connections or statements. See the try/catch/finally examples in some of the other answers.

您应该像使用单个 JBDC 连接一样使用池中的连接，并遵循有关关闭资源的最佳实践，以免泄漏任何连接或语句。请参阅其他一些答案中的 try/catch/finally 示例。

Pools can manage the connection resources and test them before handing them out to clients to ensure that they aren't stale. Also a pool will create and destroy connections as needed.

池可以管理连接资源并在将它们分发给客户端之前对其进行测试以确保它们不是陈旧的。此外，池将根据需要创建和销毁连接。

回答by Jay

If you've mastered JDBC with single-threading, going to multi-threading and connection pools shouldn't be a big deal. All you need to do differently is: 1. When you need a connection, get it from the pool instead of directly. 2. Each thread should get its own connections.

如果您已经掌握了单线程的 JDBC，那么使用多线程和连接池应该不是什么大问题。所有你需要做的不同是： 1. 当你需要一个连接时，从池中而不是直接获取它。2. 每个线程应该有自己的连接。

To clarify point 2: If you get a connection and then pass it to multiple threads, you could have two threads trying to execute queries against the same connection at the same time. Java will throw exceptions on this. You can only have one active Statement per Connection and one active query (i.e. ResultSet) per Statement. If two threads are both holding the same Connection object, they are likely to promptly violate this rule.

澄清第 2 点：如果您获得一个连接，然后将其传递给多个线程，则可能有两个线程尝试同时针对同一个连接执行查询。Java 会对此抛出异常。每个连接只能有一个活动语句，每个语句只能有一个活动查询（即结果集）。如果两个线程都持有相同的 Connection 对象，则它们很可能会立即违反此规则。

One other caveat: With Connection pooling, be very very careful to always close your connections when you're done. The pool manager has no definitive way to know when you're done with a connection, so if you fail to close one, it's going to sit out there dangling for a long time, possibly forever depending on the pool manager. I always always always follow every "getConnection" with a try block, and close the connection in the finally block. Then I KNOW that I've closed it before the function exits.

另一个警告：使用连接池，在完成后要非常小心地关闭连接。池管理器没有确定的方法来知道您何时完成了连接，因此如果您未能关闭一个连接，它将在那里徘徊很长时间，可能永远取决于池管理器。我总是总是在每个“getConnection”后面加上一个 try 块，并在 finally 块中关闭连接。然后我知道我已经在函数退出之前关闭了它。

Besides that, everything should be the same as what you're used to.

除此之外，一切都应该和你习惯的一样。

回答by Aaron Digulla

1. Transactions happen at the connection level.

2. No. Usually, the JDBC driver will make sure that you can't execute a second statement over the same connection while another one is active.

1. 事务发生在连接级别。

2. 不会。通常，JDBC 驱动程序会确保您不能在同一连接上执行第二条语句，而另一条语句处于活动状态。

If you need connection pooling, try the DBCP framework. It offers pretty decent failure handling (like noticing stale connections and connections that haven't been returned by client code).

如果您需要连接池，请尝试DBCP 框架。它提供了相当不错的故障处理（例如注意过时的连接和客户端代码尚未返回的连接）。

As for your code: Always wrap the code in try{...}finally{...}:

至于您的代码：始终将代码包装在try{...}finally{...}：

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
     conn = ds.getConnection ();
     stmt = ...
     rs = ...
}
finally {
     rs = close (rs);
     stmt = close (stmt);
     conn = close (conn);
}

public static Connection close (Connection conn) {
    if (conn != null) {
        try {
            conn.close ();
        }
        catch (SQLException e) {
            e.printStackTrace(); // Log, don't rethrow!!
        }
    }
    return null;
}

This code will make sure that all connections, etc, are always correctly closed and that any exception during close won't hide a previous error.

此代码将确保所有连接等始终正确关闭，并且关闭期间的任何异常都不会隐藏先前的错误。

回答by Yishai

I think you should start with the Sun tutorialon connection pooling. Besides this, there are many implementations of connection pooling, some open source, including one from Apache. You should really start there rather than reinvent the wheel here.

我认为您应该从有关连接池的 Sun教程开始。除此之外，还有许多连接池的实现，一些是开源的，包括来自 Apache 的一个。你真的应该从那里开始，而不是在这里重新发明轮子。

回答by Nick

You can only keep one Statement open on any given Connection. Creating more than one Connection using a connection pool isn't that difficult, although the way to go is to use one of the greater used ones out there.

您只能在任何给定的连接上保持一个语句打开。使用连接池创建多个连接并不是那么困难，尽管要走的路是使用其中一个使用率更高的连接。

Also, if you're going to go the way of standard JDBC, I recommend using PreparedStatement over Statement.

此外，如果您打算采用标准 JDBC 的方式，我建议您使用 PreparedStatement 而不是 Statement。

I've been using iBatis and out of the box is pretty nice. Brings a few other things to the table as well.

我一直在使用 iBatis，开箱即用非常好。也带来了一些其他的东西。

回答by Everyone

Take a gander at this(+:

看看这个（+：

回答by djna

Extra bits:

额外位：

1. Application server tend to provide connection pooling, and it can get quite clever. If you are using an app server investigate carefully what you get out of the box before adding anything of your own.

2. Transactions: if you have

   Begin transaction

   get connnection work close connection // meaning return to pool

   get connection (with same isolation level etc.)
   // you will get SAMEconnection, the pool reserves it for your transaction

   work // happens in same transacction close connection

   Commit transaction // commits all the work

3. connections and errors

1. 应用服务器倾向于提供连接池，它可以变得非常聪明。如果您使用的是应用服务器，那么在添加您自己的任何内容之前，请仔细调查一下您的开箱即用内容。

2. 交易：如果你有

   开始交易

   获取连接工作关闭连接 // 意味着返回到池

   获取连接（具有相同的隔离级别等）
   //您将获得相同的连接，池为您的事务保留它

   工作 // 发生在同一个事务关闭连接中

   Commit transaction // 提交所有的工作

3. 连接和错误

Pool implementations can be clever. If any one connection from the pool experiences certain errors which indicate that the DB server has bounced then the pool can choose to to discard all pool members.

池实现可以很聪明。如果池中的任何一个连接遇到某些错误，表明数据库服务器已反弹，则池可以选择丢弃所有池成员。