Geek asks and answers

Can I use one JDBC connection, statement and result set to execute two queries in JDBC

I check the user as

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 close the connection at dataManager.putConnection(connection). I want to ask as soon as the user receives the login, then I need to update the status of the user and save the history of the log. 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);                           
}

Does the same connection use the same statement and the same result set to execute different queries or is this the wrong approach?

Thank.

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()
+5
source share
4 answers

Is it possible to use a single JDBC connection, the operator

Yes. You can reuse them before closing.

and resultset

. . . . , .

+4

, , , , .

, PreparedStatement. , SQL. , , , , . PreparedStatement , PreparedStatement .

, , PreparedStatement, :

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

PreparedStatement , . , PreparedStatements. , .

PreparedStatement, : " "

ResultSets - , , . , . Javadoc:

ResultSet , Statement , , .

, . - ,

+1

.

JDBC, Statement ResultSet JDBC

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

}
  • Connection a Statement, Statement, ResultSet.
  • Statement ResultSet, .
  • ReultSet , Statement.
  • ResultSet Statement .

:

  • , - .
  • , ConnectionPool .
  • Close ResultSetas soon as you are done with ResultSet.
0
source

You can use UpdatableResultSet .

-1
source

More articles:


All Articles