Java Statement Object Reuse?

Question

I would like to know if we can reuse the same Statement object for executing more than one query. Or, should we create a new statement for different queries.

For example,

Connection con = getDBConnection();
Statement st1 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
int i = st1.executeUpdate("update tbl_domu set domU_status=1 where domU_id=" + dom_U_id);
Statement st2 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
String date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Calendar.getInstance().getTime());
int j = st2.executeUpdate("insert into tbl_domU_action_history values('" + dom_U_name + "', 1, '" + date + "')");

In the above case, is there any harm in using the same statement st1 for both the executeUpdate() queries? Can I use the same Statement object st1 for another executeQuery()?

Basil Bourque · Accepted Answer · 2022-04-02 00:23:17Z

23

I came across the response I was looking for in the Javadoc for Statement:

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a current ResultSet object of the statement if an open one exists.

edited Apr 2, 2022 at 0:23

Basil Bourque

347k130 gold badges950 silver badges1.3k bronze badges

answered Jan 7, 2009 at 1:08

Epitaph

3,16810 gold badges35 silver badges44 bronze badges

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

2 Comments

Philippe Girolami Over a year ago

It don't see how this answers the question : should we call the close() method on the Statement ?

Basil Bourque Over a year ago

@PhilippeGirolami The Question is about reusing a Statement object, not about closing. So this Answer directly addresses the Question, with Javadoc explaining that a Statement can indeed be used again to produce another ResultSet but only one ResultSet at a time is usable.

Tom Hawtin - tackline · Accepted Answer · 2008-12-23 19:32:31Z

17

Yes, you can. However, it is very much better to use PreparedStatement to avoid SQL injection vulnerabilities.

answered Dec 23, 2008 at 19:32

Tom Hawtin - tackline

148k30 gold badges222 silver badges314 bronze badges

Comments

Steve B. · Accepted Answer · 2008-12-23 20:15:01Z

2

The original point of using prepared statements was to avoid having the database parse and recompile the statement, so it's supposed to be faster.

I had not considered the SQL injection vulnerabilities use, but I'm not sure what, if any data checking is done. I suspect that it's driver-dependant, as the driver implementation is free to just glue the statements together. If anyone has further details, please post.

edited Dec 23, 2008 at 20:15

answered Dec 23, 2008 at 20:06

Steve B.

57.6k12 gold badges99 silver badges134 bronze badges

3 Comments

Tom Hawtin - tackline Over a year ago

It appears that the JDBC spec does not require correct handling of special characters. I'd like to hear about any drivers that do not so they can be avoided.

Dave Costa Over a year ago

Steve, the idea re: SQL injection is that using parameterized statements avoids the risk. See en.wikipedia.org/wiki/SQL_injection#Preventing_SQL_Injection. Of course you are right that their original purpose was to avoid unnecessary parsing.

Steve B. Over a year ago

Yes, however as the drivers merely implement the JDBC interface(s) it's unclear to what extent this is actually happening, and it's totally dependent on the driver implementors. I was hoping someone would have more specific knowledge based on having had to deal with this issue in the past.

Ted Johnson · Accepted Answer · 2011-01-07 20:06:05Z

1

I am not sure that the result sets get garbage collected if only they are only closed. I think I had a cool memory leak when I was writing a connection pool so the same statement/connection was re-used a million times. Kept bringing down my JVM. I guess this could also be implementation specific as garabage collection of a closed result set may not be tested.

answered Jan 7, 2011 at 20:06

Ted Johnson

4,3733 gold badges32 silver badges31 bronze badges

Comments

Powerlord · Accepted Answer · 2008-12-23 19:58:33Z

0

Whenever you assign something to a reference type, you're replacing the old reference with a new one.

For example...

MyObject obj = new MyObject("foo");
obj = new MyObject("bar");

Would have a now non-referenced instance of MyObject with some property set to "foo" that will eventually be garbage collected.

obj stores a reference to a MyObject with some property set to "bar".

edited Dec 23, 2008 at 19:58

answered Dec 23, 2008 at 19:44

Powerlord

89k17 gold badges130 silver badges179 bronze badges

1 Comment

n4rzul Over a year ago

This is not quite what he is doing. He is asking if he can reuse the same instance, not just the same reference

Collectives™ on Stack Overflow

Java Statement Object Reuse?

5 Answers 5

2 Comments

Comments

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related