| Hello, I'm encountering an issue with a EntityManger created TypedQuery object. The query below has several named parameters ("otlx", "obrx", "otly", and "obry").
query = entityManager.createQuery(
"select new map(COUNT(incident.id) as incidentCount, " +
"(round((((stx(incident.point) - :otlx)/(:obrx - :otlx))/5),2)*5) as xPercent, " +
"(round((((sty(incident.point) - :otly)/(:obry - :otly))/5),2)*5) as yPercent) " +
"from UFRIncident incident " +
"where incident.point is not null " +
"group by (round((((stx(incident.point) - :otlx)/(:obrx - :otlx))/5),2)*5), " +
"(round((((sty(incident.point) - :otly)/(:obry - :otly))/5),2)*5)", Map.class);
query.setParameter("otlx", (float)otl.getCoordinate().x);
query.setParameter("otly", (float)otl.getCoordinate().y);
query.setParameter("obrx", (float)obr.getCoordinate().x);
query.setParameter("obry", (float)obr.getCoordinate().y);
The issue is that when this query gets sent down to SQL Server, it gets converted into the following:
exec sp_executesql N'select count(ufrinciden0_.id) as col_0_0_,
round((ufrinciden0_.point.STX- @P0 )/( @P1 - @P2 )/5, 2)*5 as col_1_0_,
round((ufrinciden0_.point.STY- @P3 )/( @P4 - @P5 )/5, 2)*5 as col_2_0_
from
ufr_forms ufrinciden0_
where
ufrinciden0_.point is not null
group by
round((ufrinciden0_.point.STX- @P6 )/( @P7 - @P8 )/5, 2)*5 ,
round((ufrinciden0_.point.STY- @P9 )/( @P10 - @P11 )/5, 2)*5'
,N'@P0 real,@P1 real,@P2 real,@P3 real,@P4 real,@P5 real,@P6 real,@P7 real,@P8 real,@P9 real,@P10 real,@P11 real',-71,-97.5,-71,26,49,26,-71,-97.5,-71,26,49,26
The issue with this is that the group by clause is not recognized as being the same as one of the selected fields and the following error is returned:
Caused by: java.sql.SQLException: Column 'ufr_forms.POINT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
If I modify the sp_executesql string to only use @P0 to @P3, it all works fine but you'll notice that right now, even though the named parameters in the original TypedQuery object is the same, the rendered SQL Query has 11 different parameter values! How would I go about addressing this issue? Thanks! |
