Spring JdbcTemplate execute vs update

Question

What is the difference between execute(String sql) and update(String sql) in JdbcTemplate?

If my statement is a straight CRUD and not an object creation DDL (as the execute javadoc implies), does it make sense to use execute vs the seemingly more lightweight update?

Answer 1

The method execute(String sql) returns void if a call of the method succeeds without errors. (see execute(..) JavaDoc). As for plain JDBC, it should/can be used to define database schema elements (DDL), for instance with CREATE TABLE... statements.

By contrast, update(String sql) is typically used for DML statements which correspond to SQL INSERT/UPDATE/DELETE operations. In these cases, in which data are manipulated, from a programmer's perspective it is important to know how many rows have been added/changed/deleted by the respective DML operation.

For this reason, the update(...) method returns a non negative int value to let you know:

Returns: the number of rows affected

As the JavaDoc indicates by using the term "typically" in its description, you could, however, use execute(String sql) to manipulate data without the need to use the returned int value. In theory, and for some DBMS implementations, this call could be some nanoseconds quicker, as no return value needs to be transferred.

Yet, from my personal and a programmer's perspective, you should use both operations with the difference between DDL vs. DML statements in mind, as by its nature update signals a data manipulation operation being conducted.

Hope, it helps.