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.

This details deprecated methods: http://static.springsource.org/spring/docs/current/javadoc-api/deprecated-list.html — Dan MacBean, Mar 27 '13 at 14:28
You're right, I don't know why my source doesn't have `@Deprecated` — Sotirios Delimanolis, Mar 27 '13 at 14:30
Updated the Spring version to 3.2.2 - as it seems it is first deprecated here — Dan MacBean, Mar 27 '13 at 14:30
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. — Dan MacBean, Mar 27 '13 at 14:33
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. — hochraldo, Apr 04 '13 at 10:02

score 113 · Accepted Answer · answered Apr 05 '13 at 16:40

113

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

answered Apr 05 '13 at 16:40

Gabriel Belingueres

2,945
1
24
32

12

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; – MetroidFan2002 Dec 05 '13 at 18:26
@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. – Gabriel Belingueres Dec 06 '13 at 14:31
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. – MetroidFan2002 Dec 06 '13 at 16:20
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. – mrswadge Oct 06 '15 at 16:25
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. – mrswadge Oct 06 '15 at 21:00

score 38 · Answer 2 · edited May 19 '16 at 12:18

38

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.

edited May 19 '16 at 12:18

Dariusz

21,561
9
74
114

answered Jul 25 '13 at 15:09

SGB

2,118
6
28
35

good point. It would work fine without the parenthesis too. :) – SGB Apr 04 '15 at 13:30

score 15 · Answer 3 · answered Mar 27 '13 at 14:33

15

Deprecated in favor of queryForObject(String, Class).

answered Mar 27 '13 at 14:33

vertti

7,539
4
51
81

score 13 · Answer 4 · answered Aug 06 '13 at 09:22

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.

2 cents: The compiler may warn you about it, if you enabled autoboxing warning. — keiki, Apr 22 '14 at 07:20

score 2 · Answer 5 · answered Mar 17 '16 at 16:23

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.

score -1 · Answer 6 · answered Mar 05 '18 at 06:36

-1

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

answered Mar 05 '18 at 06:36

Manikannan Arumugam

1

Please explain your answer. – Harsh Wardhan Mar 05 '18 at 06:56

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

6 Answers6

Linked