CODEWITHSUNDEEP
javadatabasejdbcprepared-statement
Mangu Singh Rajpurohit
Mangu Singh Rajpurohit

Reputation: 11410

Creating multiple instances of preparedstatement

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?

Upvotes: 0

Views: 718

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 108937

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?

Upvotes: 2

wero
wero

Reputation: 32980

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

Upvotes: 3

Related Questions