SQL DELETE FROM by JdbcTemplate doesn't work

Question

I'm trying to delete a row from a table using JDBC template but somehow it just doesn't work, no errors just nothing. The Insert Into works just fine with almost same logic.

@Override
  public void deleteEntry(long id) {
    String deleteQuery = "DELETE FROM company WHERE id = ?";
    jdbcTemplate.update(deleteQuery, id);
  }

and for example, the working sniped

@Override
public long addEntry(Company entry) {
    String insertSql = "INSERT INTO company VALUES (?,?,?,?,?,?,?,?,?)";
    jdbcTemplate.update(insertSql, companyObject(entry));
    return 0;
}

I'm really confused at the moment so if you have any idea that will be great.

You code looks ok. Your problem is elsewhere. E.g. are you sure you call method with correct `id` value? Are you sure you're *committing* the change? — Andreas, Apr 13 '18 at 20:42
Isn't JDBC connections start out with auto-commit mode enabled? — lukaszgo3, Apr 13 '18 at 20:48
I have no idea how you manage your connection and your transactions, so I commented on a *potential* cause of your problem. Don't you use Spring transactions, to ensure that if/when you do multiple related changes, they are committed or rollback as a group? — Andreas, Apr 13 '18 at 20:53
Have you traced the `int` value returned by `update`? It should report the number of records affected by the DELETE, so if it was 0, then you should pay attention to the value of the ID (as @Andreas suggested). — Little Santi, Apr 13 '18 at 20:56
I would recommend you to print the value of the `id` that you pass in to the function `deleteEntry`. Print the value to check it is passing the value correctly — whatthefish, Apr 13 '18 at 21:15

score 1 · Answer 1 · answered Apr 15 '18 at 07:48

The update method of JdbcTemplate has one return parameter giving the number of rows affected so there is no exception if you delete nothing. You must check the return value with your expected count.

Here a small example in a pseudocode

def id = 1 
String deleteQuery = "DELETE FROM company WHERE id = ?";
def updCnt = jdbcTemplate.update(deleteQuery, id);
println "deleting ID ${id}, deleted rows ${updCnt}"

gives e.g.

deleting ID 1, deleted rows 1

The most probable couse of not deleteting is that you use a non existing ID and the returned value is zero.

If you observe the expected deleted count but the record is not deleted, your DataSource is probably configured with autoCommit FALSE setting. Check here the details.

SQL DELETE FROM by JdbcTemplate doesn't work

1 Answers1