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&useSSL=false&autoReconnect=true&useUnicode=yes&characterEncoding=UTF8&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

Answer 1

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