Joshua Lee Joshua Lee - 1 month ago 17
Java Question

Checking the sql statement being run from JdbcTemplate [SOLVED]

I am trying to run a sql statment

select `key`, avg(score) from score group by `key`


to find the average score grouped by the column key. I am using JdbcTemplate to query with a mySQL db. Below is the line of java code

List<MarkupScore> scores = mysqlDbTemplate.query(sql, new BeanPropertyRowMapper(MarkupScore.class));


The query works as planned in my db manager (picture below)

Running query

but when I try and access the List scores. I have a list of 3 MarkupScore which indicates the group by worked but the average scores are set to 0. I know my sql statement is not the problem since it works on the actual database. I think the problem is somewhere when the jdbctemplate translates the sql statement? I have also tried to run just

select avg(score) from score


and this too returns a 0. Does jdbctemplate not support average?

I have included a picture of the db that I am working with below

datbase

Answer

Since you're mapping the values with BeanPropertyRowMapper, it's trying to map a column named avg(score) to a similarly named property which doesn't exist. Give the avg(score) an alias (i.e. avg(score) AS score) that maps to the correct property in MarkupScore.

And when asking a question, include all the necessary information.