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

Question

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

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

Answer 1

can I use same JDBC connection, statement

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.

Answer 2

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.

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

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

In this case, you can reuse the same PreparedStatement multiple 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.

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

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:

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.

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