Using a created statement more than one time

Hello,

My question is about which one is better. I guess second is better but I dont really know using one statement again and again is good.
I am using 3 queries in 1 try catch block. Firstly, I created one statement and one result set, used it for 1 query and then closed it. I did it for three times. But then I thought about creating one statement, then use it for all queries one by one. Finally close that statement and resultset.

My last code block,

I didn't want to add an irrelevant question but may be someone can answer it. Second question is about open cursors. Closing statements and resultsets are enough for not reaching to maximum open cursors ?

Thanks.

Welcome to the ranch Ertoo

I think some of your post has gone missing as there is no code relating to closing statements or result sets.

And as for which method is better as long as everything is closed at the end, I think all is well.

I FEEL it would be better to close everything before you re-use it again, as that FEELS (with no actual knowledge) to be better.

you don't mention if you have a finally block but you are meant to close everything in one of them, in case of exceptions.

The details of this are driver specific and (ideally) should be described in the driver's documentation.

Sometimes reusing a previously created statement can have some performance benefits. For example, in some databases/drivers an open PreparedStatement can be tied to a structure in the database. Reusing that statement saves database resources, since closing and re-creating it would require either to rebuild this database structure, or at least look it up and link it again to the statement being opened. Even so, sometimes the database connection can cache such statements (at the client side) and reuse them even when they are closed and created again.

On the other hand, closing everything up after every use should always work and will certainly prevent any leakage of resources, even if it would not provide the best performance sometimes. If your code is expected to run against many disparate JDBC drivers, this would probably be the way to go.

In any case, you should use PreparedStatement and not Statement, not only for performance reason, but mainly to prevent SQL injection attacks.

Martin Vajsar wrote:The details of this are driver specific and (ideally) should be described in the driver's documentation.

Sometimes reusing a previously created statement can have some performance benefits. For example, in some databases/drivers an open PreparedStatement can be tied to a structure in the database. Reusing that statement saves database resources, since closing and re-creating it would require either to rebuild this database structure, or at least look it up and link it again to the statement being opened. Even so, sometimes the database connection can cache such statements (at the client side) and reuse them even when they are closed and created again.

On the other hand, closing everything up after every use should always work and will certainly prevent any leakage of resources, even if it would not provide the best performance sometimes. If your code is expected to run against many disparate JDBC drivers, this would probably be the way to go.

In any case, you should use PreparedStatement and not Statement, not only for performance reason, but mainly to prevent SQL injection attacks.

you are talking about using exactly the same sql statement again, not just the same java statement object.
I am not sure which of these the OP is refering to as he calls it with query1 and query2

More specifically, I was talking about reusing PreparedStatements. You can set different parameters between executions and therefore obtain different resultsets, for example.

Calling Statement.executeQuery() that way is most probably bad practice (SQL injection and so on). Actually, I'd be repeating myself at this point...

Thanks for all answers. I m really glad to be here. I learnt a lot.