JdbcTemplate queryForInt/Long is deprecated in Spr

Question

The queryforInt/queryforLong methods in JdbcTemplate are deprecated in Spring 3.2. I can't find out why or what is considered the best practice to replace existing code using these methods.

A typical method:

int rowCount = jscoreJdbcTemplate.queryForInt(
    "SELECT count(*) FROM _player WHERE nameKey = ? AND teamClub = ?",
    playerNameKey.toUpperCase(),
    teamNameKey.toUpperCase()
);

OK the above method needs to be re-written as follows:

Object[] params = new Object[] { 
   playerNameKey.toUpperCase(), 
   teamNameKey.toUpperCase()
};
int rowCount = jscoreJdbcTemplate.queryForObject(
    "SELECT count(*) FROM _player WHERE nameKey = ? AND teamClub = ?",
    params, Integer.class);

Obviously this deprecation makes the JdbcTemplate class simpler (or does it?). QueryForInt was always a convenience method (I guess) and has been around a long time. Why has it been removed. The code becomes more complicated as a result.

Answer 1

What I think is that somebody realized that the queryForInt/Long methods has confusing semantics, that is, from JdbcTemplate source code you can see its current implementation:

@Deprecated
public int queryForInt(String sql, Object... args) throws DataAccessException {
    Number number = queryForObject(sql, args, Integer.class);
    return (number != null ? number.intValue() : 0);
}

which may lead you to think that if the result set is empty it will return 0, however it throws an exception:

org.springframework.dao.EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0

so the the following implementation is essentially equivalent to the current one:

@Deprecated
public int queryForInt(String sql, Object... args) throws DataAccessException {
    return queryForObject(sql, args, Integer.class);
}

And then the non deprecated code now must be replaced with the ugly:

    queryForObject(sql, new Object { arg1, arg2, ...}, Integer.class);

or this (nicer):

    queryForObject(sql, Integer.class, arg1, arg2, ...);

Answer 2

JdbcTemplate#queryForInt returns 0 if the column value is SQL NULL or 0. There is no way to distinguish one case from the other. I think this is the main reason why the method is deprecated. BTW, ResultSet#getInt behaves similarly. Though, we can distinguish between these two cases by ResultSet#wasNull.

Answer 3

Replacing such code:

long num = jdbcTemplate.queryForLong(sql);

With this code:

long num = jdbcTemplate.queryForObject(sql, Long.class);

is very dangerous because if column have null value queryForObject return null and as we know primitive types can't be null and You will have NullPointerException. The compiler didn't warn You about this. You will know about this error at runtime. The same error You will have if You have method that return primitive type:

public long getValue(String sql) {
    return = jdbcTemplate.queryForObject(sql, Long.class);
}

The deprecated method queryForLong in JdbcTemplate in Spring 3.2.2 have the following body:

@Deprecated
public long queryForLong(String sql) throws DataAccessException {
    Number number = queryForObject(sql, Long.class);
    return (number != null ? number.longValue() : 0);
}

You see before they return primitive value there is check that this is not null and if it is null they return 0. By the way - Should be 0L.

Answer 4

I agree with the original poster that deprecating the convenience method queryForLong(sql) is an inconvenience.

I had developed an app using Spring 3.1 and just updated to the latest Spring version (3.2.3) and noticed that it was deprecated.

Fortunately, it was a one line change for me:

return jdbcTemplate.queryForLong(sql);  // deprecated in Spring 3.2.x

was changed to

return jdbcTemplate.queryForObject(sql, Long.class);

And a couple of Unit Tests seem to indicate, the above change works.

Answer 5

Deprecated in favor of queryForObject(String, Class).

Answer 6

public int getCircleCount() {
    Object param = "1";
    String sql = "select count(*) from circle where id = ? ";
    jdbcTemplate.setDataSource(getDataSource());
    int result = getJdbcTemplate().queryForObject(sql, new Object[] { param }, Integer.class);
    return result;
}