Statement.executeUpdate
After creating a statement, one can recieve a resultSet by two means:
...
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
or
...
Statement stmt = conn.createStatement();
int rows = stmt.executeUpdate(sql);
ResultSet rs = stmt.getResultSet(sql);
I've always used the latter because it easily supplies me with the row count without having to go through rs.last() and rs.getRow() etc..
However, I only recently read the javaDoc for executeUpdate(), which claimed that the method should only be used for INSERT,UPDATE, or DELETE statements and would throw a SQLException if a ResultSet was produced. This, obviously, is false considering I've been using it for a while with SELECT statements.
Is this just an small error on Sun's part? Do the rest of you use executeUpdate() with SELECT statements also?
[941 byte] By [
MrRogersa] at [2007-11-26 16:05:58]
# 1
> Do the rest of you use executeUpdate() with SELECT statements also?
I don't think we do. It's not for select. If you have a JDBC driver where it works it's a non-standard extension, or an accident of the implementation.
There is no portable/efficient/one-size-fits-all way of getting the row count of a ResultSet (nothing to do with JDBC, that's just how databases work.)
# 4
> I've always used the latter because it easily
> supplies me with the row count without having to go
> through rs.last() and rs.getRow() etc..
I have not heard of this, thank you for providing the information and example..
This anomaly might work very nicely for your need now but as you mentioned it is contrary to the JavaDoc so you cannot count on it continuing to work on the next release of the Oracle driver.
I would also be concerned at the cost (the performance hit) of it returning the row count. No way to know how they the Oracle driver is determining the row count, but it may be that they are pulling the entire ResultSet across the network and storing the whole ResultSet locally in order to accomplish it (which could be a significant resource concern).
> Is this just an small error on Sun's part? Do the
> rest of you use executeUpdate() with SELECT
> statements also?
I doubt if this is a Sun error. I'm guessing it's just happens to work as a side effect of some other functionality. Again, I wouldn't count on it working in future releases, although Oracle is in general quite good about maintaining known and unknown functionality to allow backward compatibility.
# 5
> > without having to go
> > through rs.last() and rs.getRow() etc..
>
> Have you ever considered using
> > while(rs.next()) {
>// do your stuff for each row in ResultSet here...
>
>
?
Yes, I've used that before, however there are some cases in which it is helpful to know the row count prior to moving through the ResultSet(like if you need to declare the size of your String array in which you will be storing the data).
Well, thanks for your replies. I probably should stop using that method so that I don't get used to the wrong way.
# 6
> Yes, I've used that before, however there are some
> cases in which it is helpful to know the row count
> prior to moving through the ResultSet(like if you
> need to declare the size of your String array in
> which you will be storing the data).
That's true. But I never ever store the data I get out of a ResultSet in an array, so I never ever encounter that problem. I always store the data in a List -- specifically an ArrayList, but there might be some use cases where some other kind of List might be better.
