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

Question

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

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()

我正在 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);                           
}

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

谢谢。

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

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)

更新方法:

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()

维护历史:

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()

Answer 1

can I use same JDBC connection, statement

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

and resultset

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