Java JDBC 效率:一个连接应该维持多久?

Java JDBC efficiency: How long should a connection be maintained?(Java JDBC 效率:一个连接应该维持多久?)

问题描述

我仍在处理同样的问题提到 这里.它似乎工作正常，尤其是在创建如下所示的 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;
        }
    }

}

我已经在上述问题的链接中发布了存储过程和单例模式实现.

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 次数据事务是否正确?例如.对用户输入的某种形式的验证检查，或者类似于谷歌的自动建议的东西......这些都是基于用户输入的单独存储的函数调用.我可以使用一个连接实例，而不是在每次数据事务后连接和断开连接吗?哪个效率更高?

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.

谢谢.

推荐答案

你的代码不应该依赖于这样一个事实，即你的应用程序当前是数据库的唯一客户端，或者你只有 30 个用户.因此，您应该处理数据库连接，例如文件、套接字和所有其他类型的您可能会耗尽的稀缺资源.

Your code should never depend on the fact, that your application is currently the only client to the database or that you have only 30 users. So you should handle database connections like files, sockets and all other kinds of scarce resources that you may run ouf of.

因此，您应该始终自行清理.无论你做什么.打开连接，做你的事情(一个或 SQL 语句)并关闭连接.总是！

Thus you should always clean up after yourself. No matter what you do. Open connection, do your stuff (one or SQL statements) and close connection. Always!

在您的代码中，您创建连接并将其保存到静态变量中 - 只要您的 AbstractModel 类存在，此连接将持续存在，可能永远存在 - 这很糟糕.与所有类似情况一样，将您的代码放在 try/finally 中，以确保连接始终关闭.

In your code you create your connection and save it into a static variable - this connection will last as long as your AbstractModel class lives, probably forever - this is bad. As with all similar cases put you code inside try/finally to make sure the connection gets always closed.

我看到由于 Web 应用程序未关闭连接而导致应用程序服务器出现连接中断.或者因为他们在注销时关闭，有人说我们永远不会同时拥有那么多用户"，但它只是扩大了一点.

I have seen application servers running ouf of connections because of web applications not closing connections. Or because they closed at logout and somebody said "we will never have more then that much users at the same time" but it just scaled a little to high.

现在您的代码正在运行并正确关闭连接，就像 zaske 所说的那样添加连接池.这将解决打开/关闭数据库连接的性能问题，这确实是昂贵的.在逻辑层(您的应用程序)上，您不想知道何时打开/关闭物理连接，数据库层(数据库池)将为您处理.

Now as you have your code running and closing the connections properly add connection pooling, like zaske said. This will remedy the performance problem of opening/closing database connection, which truely is costly. On the logical layer (your application) you doesn't want to know when to open/close physical connection, the db layer (db pool) will handle it for you.

然后，您甚至可以为整个会话模型设置单个连接，这也受 DBCP 支持 - 这没有危险，因为您可以在需要时重新配置池，而无需接触您的客户端代码.

Then you can even go and set up a single connection for your whole session model, which is also supported by DBCP - this is no danger, since you can reconfigure the pool afterwards if you need without touching your client code.

这篇关于Java JDBC 效率:一个连接应该维持多久?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！