29

In JDBC, can I use single Statement object to call executeQuery("") multiple times? Is it safe? Or should I close the statement object after each query, and create new object for executing another query.

E.G:

Connection con;
Statement s;
ResultSet rs;
ResultSet rs2;
try
{
    con = getConnection();
    // Initially I was creating the Statement object in an
    // incorrect way. It was just intended to be a pseudocode.
    // But too many answerers misinterpretted it wrongly. Sorry 
    // for that. I corrected the question now. Following is the 
    // wrong way, commented out now. 
    // s = con.prepareStatement();
    
    // Following is the way which is correct and fits for my question.
    s = con.createStatement();

    try
    {
        rs = s.executeQuery(".......................");

        // process the result set rs
    }
    finally
    {
        close(rs);
    }

    // I know what to do to rs here
    // But I am asking, should I close the Statement s here? Or can I use it again for the next query?

    try
    {
        rs2 = s.executeQuery(".......................");

        // process the result set rs2
    }
    finally
    {
        close(rs2);
    }
}
finally
{
    close(s);
    close(con);
}
Improve this question
2
  • 4
    Re-using the Statement instance is definitely possible even recommended for performance reasons. Commented Jan 24, 2014 at 12:07
  • That link is dead. I only mention it because it says Database error The database has encountered a problem which is pretty ironic. Commented Mar 12, 2021 at 19:41

5 Answers 5

Reset to default
29

Yes you can re-use a Statement(specifically a PreparedStatement) and should do so in general with JDBC. It would be inefficient & bad style if you didn't re-use your statement and immediately created another identical Statement object. As far as closing it, it would be appropriate to close it in a finally block, just as you are in this snippet.

For an example of what you're asking check out this link: jOOq Docs

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

What about reusing a ResultSet? Is that a good practice too? On the code provided above, the person created two ResultSet objects
@Erick Yes reusing a ResultSet is ok as well as long as you're properly closing it after you're done with it. You can reuse a single ResultSet and Statement as long as the operations are done in serial, not parallel. ie instantiate run query 1 with your Statement, get back value xyz from the ResultSet; close the statement, instantiate & run query 2 with your Statement using value xyz
What about Statements that aren't PreparedStatements?
10

I am not sure why you are asking. The API design and documentation show it is perfectly fine (and even intended) to reuse a Statement object for multiple execute, executeUpdate and executeQuery calls. If it wouldn't be allowed that would be explicitly documented in the Java doc (and likely the API would be different).

Furthermore the apidoc of Statement says:

All execution methods in the Statement interface implicitly close a statment's [sic] current ResultSet object if an open one exists.

This is an indication that you can use it multiple times.

TL;DR: Yes, you can call execute on single Statement object multiple times, as long as you realize that any previously opened ResultSet will be closed.

Your example incorrectly uses PreparedStatement, and you cannot (or: should not) be able to call any of the execute... methods accepting a String on a PreparedStatement:

SQLException - if [...] the method is called on a PreparedStatement or CallableStatement

But to answer for PreparedStatement as well: the whole purpose of a PreparedStatement is to precompile a statement with parameter placeholders and reuse it for multiple executions with different parameter values.

Improve this answer

2 Comments

Great, thanks for detailed explanation of what I asked and what I missed. I just revisited this after years, and I am tempted to mark it as accepted answer, even if the other one has more votes. Because this one also answers the confusion behind the question in first place.
And also this answer sheds light on wrong part of question, corrects it, and answers both correct and wrong versions of question. Thanks again. Would want to upvote it 10 times.
5

A Prepared Statement tells the database to remember your query and to be prepared to accept parameterized variables to execute in that query. It's a lot like a stored procedure.

Prepared Statement accomplishes two main things:

  1. It automatically escapes your query variables to help guard against SQL Injection.

  2. It tells the database to remember the query and be ready to take variables.

Number 2 is important because it means the database only has to interpret your query once, and then it has the procedure ready to go. So it improves performance.

You should not close a prepared statement and/or the database connection in between execute calls. Doing so is incredibly in-efficient and it will cause more overhead than using a plain old Statement since you instruct the database each time to create a procedure and remember it. Even if the database is configured for "hot spots" and remembers your query anyways even if you close the PreparedStatement, you still incur network overhead as well as small processing time.

In short, keep the Connection and PreparedStatement open until you are done with them.

Edit: To comment on not returning a ResultSet from the execution, this is fine. executeQuery will return the ResultSet for whatever query just executed.

Improve this answer

3 Comments

@MarkRotteveel how so? OP asked if it's OK to reuse PreparedStatement, and I've explained it is ok, and why.
After re-reading the question and your answer, it might be me who is confused. The OP creates a PreparedStatement (in an invalid way), but uses it as a normal Statement object by using the execute methods that accept a query string (which isn't allowed, see my answer). I initially read the question to be about Statement, and in that case your answer doesn't answer it.
@MarkRotteveel ya, the OP has a lot wrong in the snippet. Since PreparedStatement is really a best practice in probably round-a-bouts 95% of the time, then I chose to address that.
4

I can't find anything in the API docs that would state, that you shouldn't call executeQuery() on a given PreparedStatement instance more than once.

However your code does not close the PreparedStatement - a call to executeQuery() would throw a SQLException in that case - but the ResultSet that is returned by executeQuery(). A ResultSet is automatically closed, when you reexecute a PreparedStatement. Depending on your circumstances you should close it, when you don't need it anymore. I would close it, because i think it's bad style not to do so.

UPDATE Upps, I missed your comment between the two try blocks. If you close your PreparedStatement at this point, you shouldn't be able to call executeQuery() again without getting a SQLException.

Improve this answer

1 Comment

Your comment in the middle asks if you should close it there. But anyways, yes it is safe to reuse it.
2

Firstly I am confused about your code

s = con.prepareStatement();

Is it work well?I can't find such function in JAVA API,at least one parameter is needed.Maybe you want to invoke this function

s = con.createStatement();

I just ran my code to access DB2 for twice with one single Statement instance without close it between two operation.It's work well.I think you can try it yourself too.

    String sql = "";
    String sql2 = "";
    String driver = "com.ibm.db2.jcc.DB2Driver";
    String url = "jdbc:db2://ip:port/DBNAME";
    String user = "user";
    String password = "password";
    Class.forName(driver).newInstance();
    Connection conn = DriverManager.getConnection(url, user, password);
    Statement statement = conn.createStatement();
    ResultSet resultSet = statement.executeQuery(sql);
    int count = 0;
    while (resultSet.next()) {
        count++;
    }
    System.out.println("Result row count of query number one is: " + count);
    count = 0;
    resultSet = statement.executeQuery(sql2);
    while (resultSet.next()) {
        count++;
    }
    System.out.println("Result row count of query number two is: " + count);
Improve this answer

1 Comment

I recon for the ease of reading he used this non working kind of pseudo code. close(rs) should also be rs.close() and you would need a null check in finally and another try/catch etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.