JdbcTemplate queryForInt/Long is deprecated in Spring 3.2.2. What should it be replaced by?

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.

Guarantor

This details deprecated methods: static.springsource.org/spring/docs/current/javadoc-api/… – Guarantor

You're right, I don't know why my source doesn't have @Deprecated – Shotten

Updated the Spring version to 3.2.2 - as it seems it is first deprecated here – Guarantor

I upgraded existing codebase from 3.1 to 3.2.2 and these methods are used all over the place. Need to understand why and how to update the code. – Guarantor

Be aware that queryForObject could return null (not the case in your example). I found no other way than to duplicate now the null check code from queryForInt/Long. – Parricide

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, ...);

Morel

That isn't true. The third code snippet is NOT equal to the implementation! Because there's a hidden NPE with the auto unboxing. If your query returned results, but they were null, the previous code would return 0 instead of null - to properly reproduce the previous behavior, it would be: Integer result = queryForObject(sql, args, Integer.class); return result == null ? 0 : result; – Accusal

@MetroidFan2002: Indeed your observation is true! However, from the viewpoint of the API design, if the query just return one NULL value, I believe it is better to return it as is, instead of asumming that (as queryForInt do) a NULL is equivalent to 0. It is the job of the API user to evaluate that kind of conditions. – Morel

The problem is that if and when a user gets an NPE there, unless they explicitly set certain things up in their environment (for example, Eclipse has an option to highlight uses of autoboxing), the NPE on that line will look like the JDBCOperations instance is null. Previously, zero would have been returned. Now why you would use this in a query that returns null, I have no idea (this is basically due to n00bs doing it, which they will), but taking these away is not a great move IMO. – Accusal

I found a possible reason is because of inaccuracy. I had a long value of 10000000233174211 being returned by queryForLong(String), but instead it was returning 10000000233174212. i.e. +1. I looked in the code and it converts a Double to a Long, so perhaps there is some issue with the conversion. – Dunleavy

Thinking about my comment above a bit further, the data type for the column was number(19,0), so maybe this is why double came into play? I got around the issue by using queryForObject(sql, Long.class) anyhow. – Dunleavy

Answer 2

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.

Hearthstone

good point. It would work fine without the parenthesis too. :) – Hearthstone

Answer 3

Deprecated in favor of queryForObject(String, Class).

Hierology

Answer 4

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.

Urinate

2 cents: The compiler may warn you about it, if you enabled autoboxing warning. – Fisher

Answer 5

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.

Vernonvernor

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;
}

Explicate

Please explain your answer. – Leenaleeper