Creating multiple instances of preparedstatement

Question

I am new to jdbc programming. I am creating instance of PreparedStatement multiple times and assigning it to same reference variable. Do I need to close the first instance of PreparedStatement prior to creating second instance of PreparedStatement?

oPrepStmt = oRoot.con.prepareStatement(strSql);

if (strProviderType.length() > 0) {

        strSql += " and users.usertype IN (?)";

        // DO I need to close prepare statement, before creating another instance of preparestatement and assigning to same reference variable.
        // i.e. oPrepStmt.close();
        oPrepStmt = oRoot.con.prepareStatement(strSql);
        oPrepStmt.setString(2,strProviderType);
}

oPrepStmt.setInt(1,oRoot.getTrUserId());

Does the unclosed first instance of preparedstatement causes resource leaks?

Why are you doing it like this? Why not first construct the full query string and then prepare the statement? — Mark Rotteveel, Mar 11 '16 at 10:29
"*Does the unclosed first instance of preparedstatement causes resource leaks*" - yes it does (although it depends a bit on the DBMS and JDBC driver). — , Mar 11 '16 at 10:30
Can you give me a bit more details regarding this ? Moreover can you suggest the best way to write such code ? — Mangu Singh Rajpurohit, Mar 11 '16 at 10:31
You could first determine the needed sql, prepare the statement and then determine if you need to set the parameter or not. The overhead of preparing a statement multiple times is higher than having two if statements instead of one. — Mark Rotteveel, Mar 11 '16 at 10:32

score 3 · Answer 1 · answered Mar 11 '16 at 10:41

JDBC statements implement AutoCloseable and therefore indicate that should explicitly be closed when no longer needed.

An object that may hold resources (such as file or socket handles) until it is closed. The close() method of an AutoCloseable object is called automatically when exiting a try-with-resources block for which the object has been declared in the resource specification header. This construction ensures prompt release, avoiding resource exhaustion exceptions and errors that may otherwise occur.

So as suggested by the Javadoc use a try-with-resources statement:

try (PreparedStatement pstmt = oRoot.con.prepareStatement(strSql)) {
     ... run sql commands ...
}

In your example you create a statement and discard it for some cases. Better to avoid this and write like:

boolean checkUserType = strProviderType.length();

try (PreparedStatement pstmt = oRoot.con.prepareStatement(checkUserType ? strSql : strSql + " and users.usertype IN (?)") {
    oPrepStmt.setInt(1,oRoot.getTrUserId());
    if (checkUserType)
        oPrepStmt.setString(2,strProviderType);
    ...
}

score 2 · Accepted Answer · edited May 23 '17 at 12:23

You should always close a statement when you are done with it. In some databases/JDBC drivers, a statement also has a serverside handle. Not closing the statement will leave that handle open on the server, causing unnecessary resource consumption (mostly memory, but it might lock certain metadata objects).

On top of that on the driver side not closing the statement could also have additional resource consumption (memory, listeners to connection events, etc). Closing as soon as possible is therefor advisable.

A driver will likely deallocate this eventually, either through a finalizer, or when you close the connection, but it is not a good idea to rely on that (eg connections in a connection pool do not always correctly close statements when they are returned to the pool, etc).

Now as to your specific problem, you should modify your code to something like:

if (strProviderType.length() > 0) {
    strSql += " and users.usertype IN (?)";
}

try (PreparedStatement oPrepStmt = oRoot.con.prepareStatement(strSql)) {
    oPrepStmt.setInt(1,oRoot.getTrUserId());
    if (strProviderType.length() > 0) {
        oPrepStmt.setString(2, strProviderType);
    }

    oPrepStmt.execute(); // or executeQuery or executeUpdate
}

I also included a try-with-resources to ensure the statement is closed as soon as possible.

BTW, likely your use of IN(?) is not going to work like this on most (all?) databases. See PreparedStatement IN clause alternatives?

Creating multiple instances of preparedstatement

2 Answers2