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()?

Answer 1

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

Answer 2

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".

Answer 3

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.

Answer 4

I came across the response I was looking for in the Javadocs

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.