Tomcat 6 中的 SQL Server 连接管理
我们在使用 Tomcat 6 中运行的 Java Web 应用程序（使用 JDBC 连接到 SQL Server 数据库）时遇到问题。
经过几次请求后，应用程序服务器终止，并且在日志文件中我们发现与数据库连接失败相关的异常。
我们现在没有使用任何连接池，而是使用标准 JDBC/ODBC/ADO 驱动程序桥来连接到 SQL Server。
我们是否应该考虑使用连接池来消除这个问题？
另外，我们是否应该将驱动程序更改为 jTDS 之类的东西？
We are having trouble with a Java web application running within Tomcat 6 that uses JDBC to connect to a SQL Server database.
After a few requests, the application server dies and the in the log files we find exceptions related to database connection failures.
We are not using any connection pooling right now and we are using the standard JDBC/ODBC/ADO driver bridge to connect to SQL Server.
Should we consider using connection pooling to eliminate the problem?
Also, should we change our driver to something like jTDS?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您没有关闭 JDBC 连接，那么这是正确的行为。
当您使用完每个 JDBC 资源以及通过它获得的其他 JDBC 资源时，必须调用该资源的 close() 方法。
这适用于 Connection、Statement/PreparedStatement/CallableStatement、ResultSet 等。
如果您不这样做，那么对于初学者来说，您将在 SQL Server 上囤积潜在巨大且可能非常有限的资源。
最终，连接将不会被授予，要执行的查询和返回结果将失败或挂起。
如果您未将 autoCommit 属性设置为 true，则如果在每个事务结束时未能 commit() 或 rollback()，您还可能会注意到 INSERT/UPDATE/DELETE 语句挂起。
我所看到的是，如果您将上述严格性应用到您的 JDBC 客户端代码中，那么 JDBC 和您的 SQL 服务器将非常顺利地工作。 如果你写的是垃圾，那么一切都会变得像垃圾一样。
许多人编写 JDBC 调用，期望通过调用 close() 来释放其他“东西”，因为这很无聊，并且当他们忽略这一点时，应用程序和服务器不会立即失败。
确实如此，但是那些程序员编写了他们的程序来与他们的服务器一起玩“墙上的 99 瓶啤酒”。
资源将耗尽，并且请求往往会导致以下一种或多种情况发生：连接请求立即失败、SQL 语句立即失败或永远挂起，或者直到某个冗长的事务超时计时器到期等。
因此，最快的方法是解决这些类型的 SQL 问题不能归咎于 SQL 服务器、应用程序服务器、Web 容器、JDBC 驱动程序，也不能归咎于 Java 垃圾收集器中嵌入的人工智能的令人失望的缺乏。
解决这些问题的最快方法是用 Nerf dart 射杀在您的应用程序中编写与 SQL 服务器通信的 JDBC 调用的人。 当他说：“你这么做是为了什么……？！” 只需指向这篇文章并告诉他阅读即可。 （记住不要射击他的眼睛、他手中的东西、可能危险/脆弱的东西等）
至于连接池解决你的问题......不。 抱歉，连接池只是通过向应用程序提供预先分配的（可能是回收的）连接来加快应用程序中获取连接的调用速度。
牙仙子把钱放在你的枕头下，复活节兔子把鸡蛋和鸡蛋放在你的枕头下。 糖果在你的灌木丛下，圣诞老人把礼物放在你的树下。 但是，抱歉打破了您的幻想 - SQL 服务器和 JDBC 驱动程序不会关闭所有内容，因为您“忘记”关闭您自己分配的所有内容。
That is the correct behavior if you are not closing your JDBC connections.
You have to call the close() method of each JDBC resource when you are finished using it and the other JDBC resources you obtained with it.
That goes for Connection, Statement/PreparedStatement/CallableStatement, ResultSet, etc.
If you fail to do that, you are hoarding potentially huge and likely very limited resources on the SQL server, for starters.
Eventually, connections will not be granted, get queries to execute and return results will fail or hang.
You could also notice your INSERT/UPDATE/DELETE statements hanging if you fail to commit() or rollback() at the conclusion of each transaction, if you have not set autoCommit property to true.
What I have seen is that if you apply the rigor mentioned above to your JDBC client code, then JDBC and your SQL server will work wonderfully smoothly. If you write crap, then everything will behave like crap.
Many people write JDBC calls expecting "something" else to release each thing by calling close() because that is boring and the application and server do not immediately fail when they leave that out.
That is true, but those programmers have written their programs to play "99 bottles of beer on the wall" with their server(s).
The resources will become exhausted and requests will tend to result in one or more of the following happening: connection requests fail immediately, SQL statements fail immediately or hang forever or until some godawful lengthy transaction timeout timer expires, etc.
Therefore, the quickest way to solve these types of SQL problems is not to blame the SQL server, the application server, the web container, JDBC drivers, or the disappointing lack of artificial intelligence embedded in the Java garbage collector.
The quickest way to solve them is to shoot the guy who wrote the JDBC calls in your application that talk to your SQL server with a Nerf dart. When he says, "What did you do that for...?!" Just point to this post and tell him to read it. (Remember not to shoot for the eyes, things in his hands, stuff that might be dangerous/fragile, etc.)
As for connection pooling solving your problems... no. Sorry, connection pools simply speed up the call to get a connection in your application by handing it a pre-allocated, perhaps recycled connection.
The tooth fairy puts money under your pillow, the Easter bunny puts eggs & candy under your bushes, and Santa Clause puts gifts under your tree. But, sorry to shatter your illusions - the SQL server and JDBC driver do not close everything because you "forgot" to close all the stuff you allocated yourself.
我肯定会尝试一下 jTDS。 我过去在 Tomcat 5.5 中使用过它，没有任何问题。 作为调试步骤，这似乎是一个相对快速、影响较小的更改。 我想你会发现它更快、更稳定。 它还具有开源的优势。
从长远来看，我认为出于性能原因您会需要研究连接池。 当您这样做时，我建议您查看c3p0。 我认为它比 Tomcat 的内置池选项更灵活，而且我通常更喜欢“容器外”解决方案，这样将来切换容器就不会那么痛苦。
I would definitely give jTDS a try. I've used it in the past with Tomcat 5.5 with no problems. It seems like a relatively quick, low impact change to make as a debugging step. I think you'll find it faster and more stable. It also has the advantage of being open source.
In the long term, I think you'll want to look into connection pooling for performance reasons. When you do, I recommend having a look at c3p0. I think it's more flexible than the built in pooling options for Tomcat and I generally prefer "out of container" solutions so that it's less painful to switch containers in the future.
很难说清楚，因为您提供的有关实际故障的信息太少：
你能告诉我们：
你看到
您所在位置的代码示例
连接并为您的其中之一提供服务
requests
它的交易数量
失败了，或者看起来是随机的
我已经编写了很多与数据库相关的java代码（几乎所有我的代码都与数据库相关），并使用了MS驱动程序，jdt驱动程序和来自jnetDirect的驱动程序。
我相信如果您向我们提供更多详细信息，我们可以为您提供帮助。
It's hard to tell really because you've provided so little information on the actual failure:
Can you tell us:
you're seeing
example of the code where you
connect and service one of your
requests
number of transactions that it
fails, or is it seemingly random
I have written a lot of database related java code (pretty much all my code is database related), and used the MS driver, the jdt driver, and the one from jnetDirect.
I'm sure if you provide us more details we can help you out.