Variable reuse in java

Question

In a class for handling SQL transactions in Java, I'm find myself using the classes PreparedStatement and ResultSet all the time.

I got curious to know what would be better (more efficient) practice in Java; declaring them as members of the class ...

class SqlThingy {
    PreparedStatement pstx;
    ResultSet rs;
    public void SqlThingyMethod() {
         pstx = database.connection.prepareStatement("...");
         ....
    }
}

... or as local variables of individual methods?

class SqlThingy {
    public void SqlThingyMethod() {
        PreparedStatement pstx;
        ResultSet rs;
        pstx = database.connection.prepareStatement("...");
    }
}

Does the VM merely overwrite the contents of the class member with the (reference to the) new preparedstatement, or will it do some additional initialization that also claims resources and even out the difference of allocating local variables every time?

Thats upto based on your needs. If you want to use those variables further in the same class declares it outside of any method (1). — SatyaTNV, Feb 11 '16 at 13:36
There is a very slight performance gain in using a local variable. That said, you should be more focused on making your Java code readable. If you need to reuse the variable make it a field, if not, make it local. You should read this answer which does some benchmarks http://stackoverflow.com/a/21614413/3280538 — flakes, Feb 11 '16 at 13:44
You're not even using the correct syntax for methods in your example, so you have more important things to worry about than efficiency. — Kayaman, Feb 11 '16 at 14:31

score 2 · Answer 1 · answered Feb 11 '16 at 13:49

Be sure to distinguish between variables and the objects they point to. As a general principle, do not reuse variables to point to different objects. This is extremely error prone.

In your specific example, since you are recreating the connection object in each call to SqlThingyMethod there is likely no benefit to storing it in a field. Use a local variable.

Variables are cheap. Objects are, if not exactly expensive, less cheap. In this case, the object may actually be expensive, but the variable is not.

score 1 · Answer 2 · answered Feb 11 '16 at 13:40

Instance level fields are not thread-safe hence the JVM will have to ensure that they are accessed / used properly (although escape-analysis optimises this to a really good extent).

Method local variables are thread-safe, so the JVM doesn't have to worry too much about how the instances are used.

So, its better to use method 2 (unless you are getting the references from somewhere else and the references are not contained within the method).

Nathan Hughes · Answer 3 · 2018-08-09T20:27:13.747

Your instance variable pstx is just a slot holding a reference. You're not really reusing the same object, you're just holding onto an obsolete reference until another method invocation comes along and overwrites it.

The PreparedStatement and ResultSet are created from the database connection, if you get a new database connection you have to get a new PreparedStatement too. You can't reuse either a PreparedStatement or ResultSet across connections.

Use local variables for the PreparedStatements and ResultSets, and close them when you're done with them: get your connection, perform your operations, and close all jdbc resources on the way out of the method. Letting these objects hang around longer than absolutely necessary doesn't do you any good and it can keep database server resources from being freed as quickly as they could be.

The only optimization available is that if you are doing repeated operations using the same connection it makes sense to keep the same PreparedStatement instance for all of them, so that the database server can prepare and reuse that sql.

You don't say what context you're writing this code in. For web applications you need a connection pool, see Is there a reason for never closing a JDBC connection? if you're reusing the same connection for all queries in a web application.

score 0 · Answer 4 · answered Feb 11 '16 at 13:41

The short answer is : That depends.

The long answer is:

If the variable is to be used in the whole program, then declaring them as members of the class would be a better option.
If the variable is to be used only in one method or block, then declaring it in the block would be better.

score 0 · Answer 5 · answered Feb 11 '16 at 13:42

You seem to think you are reusing something but you aren't. Each time the method is called, a new object is created in both examples.

Two main differences:

in your second example, the objects are eligible for garbage collection as soon as the method exits - in the first one they won't be collected until the method is called again or the containing object becomes eligible for garbage collection
in your second example, no external code can modify your statement while you are using it - in the first example, other code with a reference to your class could modify the prepared statement and mess up with what you are doing

Bottom line: always try to keep scopes as minimal as possible (i.e. use a local variable if you can).

score 0 · Answer 6 · answered Feb 11 '16 at 13:42

It is considered a good practise to restrict the scope of variables to the minimum you will be using them. In this particular case, although you will be reusing your variables, take into account that variables will be referencing objects, and for example, the ResultSet will be referencing the result of your query. If you let the variable fall out of scope (you declare it local), the referenced values (and the memory it fills) will be prepared to be collected by the GC. If you let it as a class variable, the time will pass and your memory will stay referenced by the variable, so it won't be free until your "SqlThingy" class instance falls out of scope.

score 0 · Answer 7 · answered Feb 11 '16 at 13:43

The answer is "it depends", but in general the second is better for performance and safety both.

The first one could cause pstx to have a longer life time than the second one, and will also disable JVM optimisations such as escape analysis, stack allocation etc.

Pretty much the only reason when the first one is preferred is when the instance can be reused and thus avoid initialisation costs.

Variable reuse in java

7 Answers7