Select count(*) query from JdbcTemplate returns different value than running the query from SQL terminal

I have this simple query :

sqlCount="  SELECT count(*)"+
            " FROM mytable "+
            " WHERE ID = ?"+
            " AND CANCEL_DATE IS NULL"+
            " AND LAST_NAME IS NOT NULL";




int count = jdbcTemplate.queryForObject(
                sqlCount, new Object[] { id }, Integer.class);

called from Spring Boot.

There are no rows with the ID (150) passed in. When running queryForObject, variable count comes up as 1 , while when running the same query from SQL Developer it comes up as 0!!

What’s going on here? What am I missing?

Answer

Possible causes

a) you are counting the number of rows returned (which is ONE) not the value inside that one row that is returned

b) you have an uncommitted insert transaction, which means SQL Developer cannot see it

But whatever the case, your comment where you said “After I do that check I continue with insertin rows”, then your method needs to be revisited anyway.

The concept of: query first for existence, then insert if not, is basically flawed unless it is backed up via an appropriate database constraint, or similar locking regime. Because otherwise, you end up in the situation of:

Session 1 – check for existence, nothing found, we’re OK, do the insert… Session 2 – check for existence, nothing found, we’re OK, do the insert… Session 1 – commit Session 2 – commit

and voila…you have multiple occurrences of a value when you were expecting only 1.

If you do indeed have an appropriate unique constraint/index defined, then you don’t need to do the count check at all, because the insert will either work or be rejected as a duplicate. Less code and no issue of data corruption.