Can Spring, JdbcTemplate not use Columns not specified in the Select List?

Can Spring, JdbcTemplate not use Columns not specified in the Select List?

I'm learning spring.
I can execute this query fine in MySQL (of course):
SELECT notes, month(dt) month, dayofmonth(dt) day, hour(dt) hour,
minute(dt) minute,
avg(temperature2) temperature2, avg(temperature) temperature,
avg(temperature1) temperature1
FROM temperature WHERE dt > curdate() - 1
GROUP BY notes, month(dt), dayofmonth(dt), hour(dt), minute(dt)
ORDER BY dt DESC
Python can do this fine.
Just using plain old Jdbc works:
Connection conn = null ;
try {
System.out.println("Start try");
String driver = "com.mysql.jdbc.Driver";
Class.forName(driver).newInstance();
conn =
DriverManager.getConnection("jdbc:mysql://localhost:3306/brew",
"root", "");
String sql = "SELECT notes, month(dt) month, dayofmonth(dt) day,
hour(dt) hour, "
+ "minute(dt) minute, avg(temperature2) temperature2,
avg(temperature) temperature, "
+ "avg(temperature1) temperature1, "
+ "FROM temperature WHERE dt > curdate() - 1 "
+ "GROUP BY notes, month(dt), dayofmonth(dt), hour(dt),
minute(dt) "
+ "ORDER BY dt DESC ";
PreparedStatement ps = conn.prepareStatement(sql) ;
ResultSet rs = ps.executeQuery();
if (rs.next()) {
System.out.println(rs.getString("notes") + " " +
rs.getDouble("temperature"));
} else {
System.out.println("No results");
}
rs.close();
ps.close();
But not JdbcTemplate in Spring?
public List<Temperature> getMinutelyTemperatures() {
String sql = "SELECT notes, month(dt) month, dayofmonth(dt) day,
hour(dt) hour, "
+ "minute(dt) minute, avg(temperature2) temperature2,
avg(temperature) temperature, "
+ "avg(temperature1) temperature1, "
+ "FROM temperature WHERE dt > curdate() - 1 "
+ "GROUP BY notes, month(dt), dayofmonth(dt), hour(dt),
minute(dt) "
+ "ORDER BY dt DESC ";
//return namedParameterJdbcTemplate.query(sql, new
MapSqlParameterSource(), new TemperatureMapper());
return jdbcTemplate.query(sql, new TemperatureMapper());
}
private static final class TemperatureMapper implements
RowMapper<Temperature> {
@Override
public Temperature mapRow(ResultSet resultSet, int rowNum) throws
SQLException {
Temperature temperature = new Temperature();
temperature.setNotes(resultSet.getString("notes"));
temperature.setDt(resultSet.getDate("dt"));
temperature.setTemperature(resultSet.getDouble("temperature"));
temperature.setTemperature1(resultSet.getDouble("temperature1"));
temperature.setTemperature2(resultSet.getDouble("temperature2"));
return temperature;
}
}
And I can't even use a column from a subquery to order on ?
public List<Temperature> getMinutelyTemperatures() {
String sql = "SELECT notes, month(dt) month, dayofmonth(dt) day,
hour(dt) hour, "
+ "minute(dt) minute, avg(temperature2) temperature2,
avg(temperature) temperature, "
+ "avg(temperature1) temperature1 "
+ "FROM (SELECT notes, dt, temperature2, temperature,
temperature1 FROM temperature WHERE dt > curdate() - 1) b "
+ "GROUP BY notes, month(dt), dayofmonth(dt), hour(dt),
minute(dt) "
+ "ORDER BY b.dt DESC ";
//return namedParameterJdbcTemplate.query(sql, new
MapSqlParameterSource(), new TemperatureMapper());
return jdbcTemplate.query(sql, new TemperatureMapper());
}
What am I overlooking here?
Here is the error:
nested exception is java.sql.SQLException: Column 'dt' not found.
If I just remove it from the order by clause, or just remove it from the
where clause, I receive the same error. I need to remove it from both or
place it in the select list (which requires aggregating on it--not want I
want to do)