java 我可以使用相同的 JDBC 连接、语句和结果集在 JDBC 中执行两个查询吗

can i use same JDBC connection, statement and resultset to execute two queries in JDBC

提问by Basit

I am authenticating user like

我正在对用户进行身份验证

public static boolean login(DataManager dataManager, String userName, String password) {    
    boolean authenticated = false;      
    Connection connection = dataManager.getConnection();        
    if (connection != null) {           
         try {                           
             Statement s = connection.createStatement();
             String sql = "query";                 
             try {                   
                 ResultSet rs = s.executeQuery(sql);                                     
                 try {                      
                     while (rs.next()) {                             
                        String group_code = rs.getString(1);
                        String orgaunit = rs.getString(2);

                        authenticated = true;                            
                     } //end of while()                      
                 } finally {                     
                     rs.close();                     
                 }                   
             } finally {
                 s.close();                  
             }

         } catch(SQLException e) {               
             //System.out.println("Could not login from dataabse:" + e.getMessage());                
         } finally {             
             dataManager.putConnection(connection);                          
         }                  
    } //end of if (connection != null)      
    return authenticated;       
} //end of login()

I am closing connection in dataManager.putConnection(connection). I want to ask once user get login then i have to update the status of user and maintain log history. Can i use something like this

我正在关闭dataManager.putConnection(connection). 我想问一下，一旦用户登录，我就必须更新用户的状态并维护日志历史记录。我可以使用这样的东西吗

try {
    Statement s = connection.createStatement();
    String sql = "query";                 
    try {                    
        ResultSet rs = s.executeQuery(sql);                                  
        try {
            while (rs.next()) {                          
                String group_code = rs.getString(1);                                                
                authenticated = true;                            
            } //end of while()  

            if (autherntcated == true) {
                sql = "query2(update status)"; 
                rs = s.executeQuery(sql);

                while (rs.next()) {
                    //dos tuff
                }

                sql = "anotherQuery";
                rs = s.executeQuery(sql);
                while (rs.next()) {
                    //do stuff
                }

            }

        } finally {                      
            rs.close();                      
        }                    
    } finally {
        s.close();                   
    }                
} catch(SQLException e) {
    //System.out.println("Could not login from dataabse:" + e.getMessage());
} finally {              
    dataManager.putConnection(connection);                           
}

Means using same connection, same statement and same resultset execute other queries or is it wrong approach ?

意味着使用相同的连接、相同的语句和相同的结果集执行其他查询还是错误的方法？

Thank you.

谢谢你。

Edit --------------------------------------------------------------

编辑 - - - - - - - - - - - - - - - - - - - - - - - - - -------------

if (connection != null) {
    try {
        String sql = "query";
        PreparedStatement prepStatement = connection.prepareStatement(sql);
        try {
            ResultSet rs = prepStatement.executeQuery(sql);                 
            try {
                while (rs.next()) {
                    String group_code = rs.getString(1);
                    authenticated = true;
                } //end of while()
            } finally {                      
                rs.close();                      
            }
        } finally {
            prepStatement.close();                   
        }

        /// Addition   
        if (authenticated == true) {
            updateUser(connection, userName);
        }
    } catch(SQLException e) {
        //System.out.println("Could not login from dataabse:" + e.getMessage());
    } finally {
        dataManager.putConnection(connection);
    }
} //end of if (connection != null)

UPdate method:

更新方法：

private static void updateUser(Connection connection, String userName) {

    try {
        String sql = "UPDATE users SET status_code = 'A' WHERE login_id = '" + userName + "'";    
        PreparedStatement prepStatement = connection.prepareStatement(sql);
        try {
            int numberOfRowsUpdated = prepStatement.executeUpdate(sql);                 
        } finally {
            prepStatement.close();                   
        }

        maintainHistory(connection);

    } catch(SQLException e) {
    //System.out.println("Could not login from dataabse:" + e.getMessage());
    }

} //end of updateUser()

maintainHistory:

维护历史：

private static void maintainHistory(Connection connection) {

    try {
        String sql = "INSERT INTO auditlog_user_logins(user_code,logintime,prstid) VALUES ();";
        PreparedStatement prepStatement = connection.prepareStatement(sql);          
        try {            
          int numberOfRowsUpdated = prepStatement.executeUpdate(sql);                   

       } finally {

        prepStatement.close();                   

       }

     } catch(SQLException e) {

         //System.out.println("Could not login from dataabse:" + e.getMessage());

     }

} //end of maintainHistory()

回答by user207421

can I use same JDBC connection, statement

我可以使用相同的 JDBC 连接、语句吗

Yes. You can reuse them prior to closing.

是的。您可以在关闭之前重复使用它们。

and resultset

和结果集

No. The question doesn't make sense. The result set is the result of executing a query or update. There is no question of re-using it. I imagine you need to close the preceding result set prior to executing the next query or update.

不。这个问题没有意义。结果集是执行查询或更新的结果。没有重新使用它的问题。我想您需要在执行下一个查询或更新之前关闭前面的结果集。

回答by Sujay

I would suggest reusing connection because establishing connection every single time you're trying to query your database maybe a performace overhead.

我建议重用连接，因为每次尝试查询数据库时都建立连接可能会造成性能开销。

As for statements, I would suggest switching to PreparedStatement. They are not only cached but one good way to protect yourself from SQL injections. So build up your query before-hand, execute them and finally close them when you're done. Reusing PreparedStatement would mean that you're substituting the parameter values for the the same PreparedStatement and executing the same.

至于语句，我建议切换到 PreparedStatement。它们不仅被缓存，而且是一种保护自己免受 SQL 注入的好方法。所以事先建立你的查询，执行它们，最后在你完成后关闭它们。重用 PreparedStatement 意味着您要替换同一个 PreparedStatement 的参数值并执行相同的操作。

So for example, if you've a PreparedStatement like:

例如，如果您有一个 PreparedStatement，例如：

PreparedStatement preparedStatement = connection.prepareStatement("SELECT [col_names] from [table_name] where [col_1_value] = ? and [col_2_value] = ?")

In this case, you can reusethe same PreparedStatementmultiple times by just substituting new values for the parameters. Generally, for your case you'll have multiple PreparedStatements. Since these statements are cached, they wouldn't be a big hit on performance when you execute the same.

在这种情况下，您可以通过仅替换参数的新值多次重复使用相同的PreparedStatement。通常，对于您的情况，您将有多个 PreparedStatements。由于这些语句已被缓存，因此当您执行它们时，它们不会对性能造成很大影响。

Here's a good tutorial to introduce you PreparedStatement in case you're in need of one: "Using Prepared Statements"

这是一个很好的教程，可以在您需要时向您介绍 PreparedStatement：“使用准备好的语句”

ResultSets - well I don't see how can you reuse it. Close it as and when you're done with them. As per the Javadoc:

ResultSets - 好吧，我不知道您如何重用它。完成后关闭它。根据Javadoc：

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

当生成它的 Statement 对象关闭、重新执行或用于从多个结果的序列中检索下一个结果时，ResultSet 对象将自动关闭。

However, make sure to close your connection once you're done using it. Re-usability is a good thing, but resource mismanagement isn't

但是，请确保在使用完毕后关闭连接。可重用性是好事，但资源管理不善则不然

回答by Premraj

Answered different Test case with same question.

用相同的问题回答了不同的测试用例。

Can I use same JDBC connection, Statement and ResultSet to execute two queries in JDBC

我可以使用相同的 JDBC 连接、Statement 和 ResultSet 在 JDBC 中执行两个查询吗

We can not reuse Connection, Statement and ResultSet parallelly or concurrent as follows:

我们不能并行或并发地重用 Connection、Statement 和 ResultSet，如下所示：

Connection con = databaseConnector.getConnection();
PreparedStatement stmt1=con.prepareStatement("select * from emp");  
ResultSet rs1=stmt.executeQuery();  
while(rs1.next()){  // get SQLException in second iteration
System.out.println(rs1.getInt(1)+" "+rs1.getString(2));  
    //As soon as you execute the following query, the previous Statement and ResultSet are implicitly closed.
    // to resolve we the problem, we should not use the above used Connection. We have to use new Connection.
    PreparedStatement stmt2=con.prepareStatement("select * from address");  
    ResultSet rs2=stmt2.executeQuery();  
    System.out.println(rs2.getString(1)+" "+rs2.getString(2));  

}  

• Closing a Connectioncloses a Statement, when Statementclosed implicitly closes ResultSetalso.
• Closing a Statementcloses a ResultSetbut not Connection.
• Closing a ReultSetonly closes itself, not Statement.
• By default, only one ResultSetper Statementcan be open at the same time.

• 关闭 aConnection关闭 a Statement，当Statement关闭时ResultSet也隐式关闭。
• 关闭一个Statement关闭一个ResultSet但不是连接。
• 关闭 aReultSet只会关闭它自己，而不是Statement.
• 默认情况下，ResultSet每个Statement只能同时打开一个。

Best Practice:

最佳实践：

• Connections are not thread safe, so sharing them across requests is not a good idea.
• Opening a DB connection is an expensive, should use a ConnectionPool for sharing connections.
• Close ResultSetas soon as you finish working with ResultSet.

• 连接不是线程安全的，因此跨请求共享它们不是一个好主意。
• 打开一个数据库连接很昂贵，应该使用 ConnectionPool 来共享连接。
• ResultSet完成工作后立即关闭ResultSet。

回答by Vitaliy

You can use the UpdatableResultSet.

您可以使用UpdatableResultSet。