2

New to using JDBC and I was wondering if all operations produce a result set. For example I am making statements to insert/update to a database via:

StringBuffer query1 = new StringBuffer("UPDATE table SET col1 = value, WHERE some_col = some_val");
PreparedStatement pstmt1 = con.prepareStatment(query1.toString());
ResultSet rs1 = pstmt1.executeQuery();

So would this snippet, when executed just act out the appropriate update and be done? Or would I need to handle the result set in some way in order to complete the operation?

Thanks for the help in advance.

Chris
  • 23
  • 2
  • I'm following a template of sorts given to me by my Project Manager. I'm a new grad and new to the company, so I'm trying to stick with their procedures while I learn. – Chris Aug 27 '10 at 15:05

3 Answers3

9

You should be using PreparedStatement#executeUpdate() rather than executeQuery(). It returns an int indicating the amount of affected rows.

int affectedRows = preparedStatement.executeUpdate();

That said, constructing a SQL string that way is not the normal idiom. You would rather like to use placeholders ? and use the PreparedStatement setters to set the values.

String sql = "UPDATE table SET col1 = ? WHERE some_col = ?";
// ...    
preparedStatement = connection.prepareStatment(sql);
preparedStatement.setString(1, col1);
preparedStatement.setString(2, someCol);
int affectedRows = preparedStatement.executeUpdate();
// ...

See also:

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
  • Thanks for the links. And I am using the PreparedStatement setters in my actual code. I probably should have been more specific in my snippet. Apologies on my part. – Chris Aug 27 '10 at 15:07
1

No, you don't have to handle the ResultSet - it will be empty anyway, because an update operation shouldn't return results from the database. Usually you would call a different metho on the statement: 

StringBuffer query1 = new StringBuffer("UPDATE table SET col1 = value, WHERE some_col = some_val");
PreparedStatement pstmt1 = con.prepareStatment(query1.toString());
int rowCount = pstmt1.executeUpdate();
Andreas Dolk
  • 113,398
  • 19
  • 180
  • 268
  • Actually there are situations where an UPDATE _can_ return a ResultSet and in that case execute() should be used together with getResultSet() –  Aug 27 '10 at 15:06
  • @a_horse: That depends on the SQL string and even then, you'd rather like to use `CallableStatement` instead. I'll roughly guess that you're talking about returning insert ID, for that JDBC offers the `getGeneratedKeys()` method which can be used afterwards (you're only dependent on the JDBC driver if it's supported; as of now most of them supports it, only Oracle's one doesn't (yet?)). See also [this question](http://stackoverflow.com/questions/3552260/plsql-jdbc-how-to-get-last-row-id). – BalusC Aug 27 '10 at 15:12
  • I'm not talking about getGeneratedKeys(). Several DBMS support the "RETURNING" clause (for UPDATE, DELETE and INSERT statements). For an UPDATE statement, execute() would then return a result set with all modified rows. A DELETE statement would return a result set that contains all deleted rows. –  Aug 27 '10 at 15:17
  • @a_horse: `CallableStatement` thus. See also the link. – BalusC Aug 27 '10 at 15:20
  • Seems to depend on the JDBC driver and the DBMS. I can run "DELETE FROM emp WHERE id > 42 RETURNING *" (in PostgreSQL). When run through execute(), getResultSet() will then return a result set with all deleted rows. No need for a CallableStatement ;) –  Aug 27 '10 at 15:26
0

tthere is another method for such statements:
s.execute("..");

christian
  • 391
  • 2
  • 10