I'm working on a connection manager class at the moment which will be called multiple times. Its purpose is to establish a connection and return results as a result set, but i have some questions about it.

Is it faster to create a result set variable in the function or change the result set field of the object and than return it?

Here are two examples

Option 1:

public class ConnectionManager {
    private static String url = "jdbc:mysql://localhost:3306/*********";
    private static String username = "root";
    private static String password = "admin123";

    public ResultSet executeQuery(String query) {
        ResultSet results = null;
        try {
            Connection connect = DriverManager.getConnection(this.url, this.username, this.password);
            Statement statement = connect.createStatement();
            results = statement.executeQuery(query);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return results;
    }

}

option 2:

public class ConnectionManager {
    private static String url = "jdbc:mysql://localhost:3306/*********";
    private static String username = "root";
    private static String password = "admin123";
    private ResultSet results = null;


    public ResultSet executeQuery(String query) {
        try {
            Connection connect = DriverManager.getConnection(this.url, this.username, this.password);
            Statement statement = connect.createStatement();
            this.results = statement.executeQuery(query);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return this.results;
    }

}

CodePudding user response：

It's not about performance, it's about design. And primary difference between two options is about having a state or not. Shared field will very likely bring problems with multithreading and mixed usage scenarios, whereas local variable is closer to "functional" aproach keeping the ResultSet isolated in caller's context.

And IMHO, performance in this case should only be considered after utility and usability.

CodePudding user response：

As written, Option 1 is not very different from Option 2: in Option 2, the ResultSet attribute is private (as mutable class attributes should generally be), and therefore not accessible to the outside world (= code outside of this class). Since Option 2 has no advantages over Option 2 (local variables are essentially free, but instance attributes should be chosen more wisely), you should go with Option 1. But there are other problems:

• you are not closing all those connections that you are opening. This may result in resource exhaustion and your app crashing. However, this does not have an easy fix, because you should not close the connection until the ResultSet has been processed, or it may be impossible to process it later.
• in Option 2, if you call executeQuery from 2 threads at approximately the same time, it is possible that both calls will return the 2nd ResultSet. So Option 2 is dangerous in the presence of concurrency.

I would propose the following fixes:

• store a private Connection connection; as a class attribute. This will give you much better performance, as establishing connections to a DB is costly, and reestablishing connections once per query and never closing them has bad consequences. Establish the connection in the constructor.
• have a public void close() method, which simply does connection.close(). This fixes the "not closing connections" problem. Call this only when you are sure that no new queries will be made, for instance when the application is shutting down.
• have executeQuery return the corresponding resultSet by querying the previously-opened connection, instead of connecting anew in each and every call. Of course, we are going with Option 1: we return a fresh ResultSet for each new query.