NamedParameterJdbcTemplate query method returning wrong integer value

Question

I am running a simple mysql query using NamedParameterJdbcTemplate in java.

The problem is that my java query is returning binary values for the count field i.e 1 for all non-zero values and 0 for all zero values. When I run the same query that jdbc is sending to mysql, directly on mysql server I get the correct answer.

Details below:

Here is the query:

SELECT CAST(COUNT(DISTINCT :fieldName) AS UNSIGNED) count, user_id userId FROM "+ tableName + " WHERE user_id IN (:userIdList) GROUP BY userId

The java LOC that does the query:

NamedParameterJdbcTemplate namedJdbcTemplate = new   NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());

    List<CountRow> countRows = namedJdbcTemplate.query(query,parameters,new CountRowMapper());

The countRow class is:

public class CountRow {
private int count;
private String userId;

public CountRow() {
    super();
    // TODO Auto-generated constructor stub
}

public void setUserId(String userId) {
    this.userId = userId;
}
public void setCount(int count) {
    this.count = count;
}

public int getCount() {
    return count;
}

public String getUserId() {
    return userId;
}

}

The details for the columns that I am querying:

  `user_id` varchar(30) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `fieldName` varchar(15) DEFAULT NULL,
ENGINE=MyISAM DEFAULT CHARSET=latin1

Finally, my jdbc connection url is:

jdbc:mysql://127.0.0.1/mydb?zeroDateTimeBehavior=convertToNull&amp;useSSL=false&amp;autoReconnect=true&amp;useUnicode=yes&amp;characterEncoding=UTF8&amp;characterSetResults=UTF8

I am completely clueless about why this is happening. It would be really great if somebody could shed some light on this.

Thanks

When you're using parameters, what you get in the end will not be a reference to a table - it will be a static string or number value, like this: `select count(distinct 'id') from my_table`. Which basically returns 0 if no rows match predicate, and 1 if at least one row matches (all rows return same value, "id"). — M. Prokhorov, Nov 24 '17 at 11:29
I can not believe that I missed this yet again. Thank you for pointing out my sheer negligence. — eytnihca, Nov 24 '17 at 11:57

score 0 · Accepted Answer · answered Nov 24 '17 at 11:16

0

I don't think that you can use the parameter :fieldName in that way. try to build the sql query using concatenation as you did with tablename

"SELECT COUNT(DISTINCT " + fieldName + ") count, user_id userId 
FROM "+ tableName + " WHERE user_id IN (:userIdList) GROUP BY userId

Hope this helps

answered Nov 24 '17 at 11:16

gtosto

1,381
1
14
18

As I commented above, the mistake was as silly as they come and it seems that I always fall for it. Anyways, thanks for the answer, hopefully I would not fall for this again. Cheers! – eytnihca Nov 24 '17 at 12:03

NamedParameterJdbcTemplate query method returning wrong integer value

1 Answers1