Correct way of reusing PreparedStatement instance?

Question

What's the right way to create a PreparedStatement, reuse it a few times, then clean it up? I'm using the following pattern:

Connection conn = null;
PreparedStatement stmt = null;

try {
    conn = getConnection(...);

    // first use
    stmt = conn.prepareStatement("some statement ?");
    stmt.setString(1, "maybe some param");
    if (stmt.execute()) {
        ...
    }

    // second use
    stmt = conn.prepareStatement("some statement ?");
    stmt.setString(1, "maybe some param");
    if (stmt.execute()) {
        ...
    }

    // third use.
    stmt = conn.prepareStatement("some statement");
    stmt.execute();
}
finally {
    if (stmt != null) {
        try {
            stmt.close();
        } catch (Exception sqlex) {
            sqlex.printStackTrace();
        }

        stmt = null;
    }

    if (conn != null) {
        try {
            conn.close();
        } catch (Exception sqlex) {
        sqlex.printStackTrace();
        }

        conn = null;
    }
}

Can we reuse the "stmt" object like that, or do we have to call stmt.close() between each query?

Thanks

---------- Update ------------------------

Ah ok I see, each of my statements will be different. So is this a more correct pattern?:

Connection conn = null;
PreparedStatement stmt = null;

try {
    conn = getConnection(...);

    // first use
    PreparedStatement stmt1 = null;
    try {
        stmt1 = conn.prepareStatement("some statement ?");
        stmt1.setString(1, "maybe some param");
        if (stmt1.execute()) {
            ...
        }
    }
    finally {
        if (stmt1 != null) {
            try {
                stmt1.close();
            } catch (Exception ex) {}
        }
    }

    // second use
    PreparedStatement stmt2 = null;
    try {
        stmt2 = conn.prepareStatement("some different statement ?");
        stmt2.setString(1, "maybe some param");
        if (stmt2.execute()) {
            ...
        }
    }
    finally {
        if (stmt2 != null) {
            try {
                stmt2.close();
            } catch (Exception ex) {}
        }
    }

    // third use
    PreparedStatement stmt3 = null;
    try {
        stmt3 = conn.prepareStatement("yet another statement ?");
        stmt3.setString(1, "maybe some param");
        if (stmt3.execute()) {
            ...
        }
    }
    finally {
        if (stmt3 != null) {
            try {
                stmt3.close();
            } catch (Exception ex) {}
        }
    }
}
finally {
    if (conn != null) {
        try {
            conn.close();
        } catch (Exception sqlex) {
        sqlex.printStackTrace();
        }

        conn = null;
    }
}

So each different statement will be closed individually before the next one executes.

possible duplicate of [Reusing a PreparedStatement multiple times](http://stackoverflow.com/questions/2467125/reusing-a-preparedstatement-multiple-times) — BalusC, Feb 10 '11 at 18:24
[Consecutive-preparedstatement-good-practice](http://stackoverflow.com/questions/7245406/consecutive-preparedstatement-good-practice), this is probably what you are looking for. — Mzq, Dec 19 '11 at 23:41

score 5 · Answer 1 · answered Feb 10 '11 at 15:09

It's the other way around -- you only need to prepare it once, and then reuse it.

I.E. This:

// second use
    stmt = conn.prepareStatement("some statement ?");
    stmt.setString(1, "maybe some param");
    if (stmt.execute()) {
        ...
    }

should become this:

// second use
    stmt.setString(1, "maybe some param");
    if (stmt.execute()) {
        ...
    }

Your third use, which is a different statement, should either be a new variable, or close your prepared statement first. (Though usually with PreparedStatements, you keep them around and reuse them).

Correct way of reusing PreparedStatement instance?

1 Answers1