JdbcTemplate.update() insert return values

Question

JdbcTemplate.update() returns number of rows affected - so you not only know that delete/update was sucessfull, you also know how many rows were deleted/updated.

What will be the return value if I try to insert a row.

Is it possible to get return value as "0"??

 private static final String insertSql =
 "INSERT INTO employee (" +
 " name, " +
 " surname, " +
 " title, " +
 " created) " +
 "VALUES (John, Smith, Software developer, new Date())";
 int row = template.update(insertSql, params, types);

Shelby

Answer 1

Yes, in theory you should get 0 or 1, but if no row was inserted, it would be due to an error, so a DataAccessException would be thrown, which is how you would know that something went wrong and no row was created.

Joettejoey

Actually, on thinking about it, there is also the case where you have a data truncation warning, and an exception is thrown, but the row would be saved, so the count should be 1. – Joettejoey

Answer 2

jdbctemplate.update will return in integer format as we know. in order to find 0 or 1 just do this below simple code

int i=jdbctemplate.update(.your db call..);

it will return 1 for success and 0 for failure case so, according to it you can process your logic.

Behalf

Answer 3

To answer your question, yes, as @Turophile pointed out, you will get a 0 or 1 in response as you can tell from the method signature (which returns an int).

In response to @Turophile comment (sorry I can't add a comment :/ ), to avoid this and avoid partial data being saved to the database, use Springs Transaction Management (tx). This would allow you to rollback transactions based on specific exceptions or all exceptions from the Exception class. Mind you, by default, @Rollback rolls back transactions for runtime, unchecked exceptions only.

@Transactional(rollbackFor = Exception.class)
public Employee updateEmployee(Employee pEmployee) { ... }

The @Transactional can also be added to the class as well, but I would read up more on it if you are interested in implementing this feature. There is a lot of good documentation on tx.

As a side note, I don't recommend lying to the caller of your method. If you call a method that says "update", run a query that'll update the record, if you want to insert/create a new record, create or call a method that "inserts" a new record into your table -- inserting a duplicate record wouldn't work anyways if the primary key(s) are unique.

Pastrami

Answer 4

The Spring jdbcTemplate class doesn't provide an 'insert' method, only query and update.

Barogram

There are still ways to use jdbcTemplate for insert. Check out other's answers. – Randarandal

Thanks. I'm doing that. It's a great tool for us in using Java for AWS lambda and api gateway. – Barogram