Java JDBC效率：连接需要维护多长时间？ [英] Java JDBC efficiency: How long should a connection be maintained?
问题描述
我仍然在处理相同的问题此处。它似乎工作很好，特别是在创建一个AbstractModel类，如下所示：
I'm still working on the same problem mention here. It seems to work fine especially after creating an AbstractModel class shown below:
public abstract class AbstractModel {
protected static Connection myConnection = SingletonConnection.instance().establishConnection();
protected static Statement stmt;
protected static ResultSet rs;
protected boolean loginCheck; // if userId and userLoginHistoryId are valid - true, else false
protected boolean userLoggedIn; // if user is already logged in - true, else false
public AbstractModel (int userId, Long userLoginHistoryId){
createConnection(); // establish connection
loginCheck = false;
userLoggedIn = false;
if (userId == 0 && userLoginHistoryId == 0){ // special case for login
loginCheck = true; // 0, 0, false, false
userLoggedIn = false; // set loginCheck to true, userLogged in to false
} else {
userLoggedIn = true;
try{
String query = "select \"user_login_session_check\"(" + userId + ", " + userLoginHistoryId + ");";
System.out.println("query: " + query);
stmt = myConnection.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()){
loginCheck = rs.getBoolean(1);
}
} catch (SQLException e){
System.out.println("SQL Exception: ");
e.printStackTrace();
}
}
}
// close connection
public void closeConnection(){
try{
myConnection.close();
} catch (SQLException e){
System.out.println("SQL Exception: ");
e.printStackTrace();
}
}
// establish connection
public void createConnection(){
myConnection = SingletonConnection.instance().establishConnection();
}
// login session check
public boolean expiredLoginCheck (){
if (loginCheck == false && userLoggedIn == true){
closeConnection();
return false;
} else {
return true;
}
}
}
ve已经在上面的早期问题的链接中发布了存储过程和Singleton Pattern实现。
I've already posted the stored procedures and Singleton Pattern implementation in the link to the earlier question above.
我的印象是，我不需要关闭连接到每个单个数据事务之后的数据库，因为它只会减慢应用程序。我正在查看大约30个用户对这个系统我正在建设，所以性能和可用性是很重要的。
I'm under the impression that I don't need to close the connection to the database after each single data transaction, as it would just slow the application. I'm looking at about 30 users for this system I'm building, so performance and usability is important.
这是正确的延长连接至少3- 4数据事务？例如。验证检查用户输入的某种形式，或类似于google的自动建议...这些都是基于用户输入的单独的存储函数调用。我可以使用一个连接实例，而不是在每次数据事务后连接和断开连接？这更有效吗？
Is it correct to prolong the connection for at least 3-4 data transactions? Eg. Validation checks to user inputs for some form, or, something similar to google's auto-suggest ... These are all separate stored function calls based on user input. Can I use one connection instance, instead of connecting and disconnecting after each data transaction? Which is more efficient?
如果我的假设是正确的（使用一个连接实例更高效），然后打开和关闭连接应该在控制器中处理，这是为什么我创建了createConnection（）和closeConnection（）方法。
If my assumptions are correct (more efficient to use one connection instance) then opening and closing of the connection should be handled in the controller, which is why I created the createConnection() and closeConnection() methods.
谢谢。
推荐答案
像Tomasz所说，你应该从不 过 取决于你的应用程序将被少量客户端使用的事实。事实上，驱动程序 在一定时间后超时并不保证您将有足够的可用连接。图片说明：很多数据库预配置了最大连接数设置为（假设）15和超时（假设）10-15分钟。如果您有30个客户端，而且每个都有一个操作，在半途中间，您会遇到连接上的问题。
Like Tomasz said, you should never ever depend on the fact that your application will be used by a small number of clients. The fact that the driver will timeout after a certain amount of time does not guarantee you that you will have enough available connections. Picture this: a lot of databases come pre-configured with a maximum number of connections set to (say) 15 and a timeout of (let's say) 10-15 minutes. If you have 30 clients and each does an operation, somewhere around half-way you'll be stuck short on connections.
您应该处理连接，文件，资源如下：
You should handle connections, files, streams and other resources the following way:
public void doSomething()
{
Connection connection = null;
Statement stmt = null;
ResultSet rs = null;
final String sql = "SELECT ....");
try
{
connection = getConnection();
stmt = connection.createStatement();
rs = stmt.executeQuery(sql);
if (rs.next())
{
// Do something here...
}
}
catch (SQLException e)
{
e.printStackTrace();
}
finally
{
closeResultSet(rs);
closeStatement(stmt);
closeConnection(connection);
}
}
The try/catch/finally guarantees you that the connection will get closed no matter the outcome. If there is some sort of failure, the finally block will still close the connection, just like it would do, if things were okay.
同样，对于文件和流你需要做同样的事情。在你的try / catch / finally之外初始化相应的对象 null ，然后按照上面的方法。
Similarly, with file and streams you need to do the same thing. Initialize the respective object as null outside your try/catch/finally, then follow the approach above.
使得许多Java应用程序在Windows下不正确，人们不会关闭文件（流到文件等），这些文件被锁定，迫使你杀死JVM，甚至重新启动你的机器。
This misconception makes a lot of Java applications misbehave under Windows, where people don't close files (streams to files, etc) and these files become locked, forcing you to either kill the JVM, or even restart your machine.
您还可以使用连接池，例如Apache的DBCP，但即使这样，您应该关心您的资源，尽管内部不同的连接池实现不一定关闭连接。
You can also use a connection pool such as for example Apache's DBCP, but even then you should take care of closing your resources, despite the fact that internally, the different connection pool implementations do not necessarily close the connections.
这篇关于Java JDBC效率：连接需要维护多长时间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
