Exception :
MySQLSyntaxErrorException: Unknown column 'y2_' in 'where clause'
出現時機 :
JPA : Criteria : Group by
Trace sql :
config file : jdbc.properties
property : hibernate.showsql=true
Description :
使用 Projections.groupProperty 時 , hibernate 產生的 sql 會自動設定 select alias y0_, y1_, y2_, ... , 依此類推 ; 所以如果為了可以以 map 取得 criteria results , 通常會在 projectionList.add 加入 alias , 但是如果 alias 與原 propery name 相同 , 又在 criteria有 add 同名 property 的 condition, 則 hibernate 產生的 sql 會以 yi_ alias 出現在 where clause 中 , 然後就會出現 Exception : Unkonwn column 'yi_' in 'where clause'
e.g.
Criteria criteria = this.getSession().createCriteria(DailyActiveCalendar.class);
ProjectionList projectionList = Projections.projectionList();
projectionList.add(Projections.groupProperty("room"), "r")
.add(Projections.groupProperty("aDate"), "date")
.add(Projections.groupProperty("active"), "active");
criteria.setProjection(projectionList);
[STDOUT] Hibernate:
select this_.room as y0_, this_.aDate as y1_, this_.active as y2_
from daily_active_calendar this_
group by this_.room, this_.aDate, this_.active
如果加入 where conditions :
criteria.add(Restrictions.eq("room", r))
.add(Restrictions.le("amount", r.getMaxSection()))
.add(Restrictions.lt("aDate", maxDate))
.add(Restrictions.eq("active", Boolean.TRUE));
[STDOUT] Hibernate:
select this_.room as y0_, this_.aDate as y1_, this_.active as y2_
from daily_active_calendar this_
where this_.room=? and this_.amount<=? and this_.aDate<? and
y2_=?
group by this_.room, this_.aDate, this_.active
Solution :
在 where clause 中如果會使用到group by的項目時 , property name 與 alias 不要相同 , 例如上例中的 .add(Projections.groupProperty("active"), "active");
改為 .add(Projections.groupProperty("active"), "isActive");
[STDOUT] Hibernate:
select this_.room as y0_, this_.aDate as y1_, this_.active as y2_
from daily_active_calendar this_
where this_.room=? and this_.amount<=? and this_.aDate<? and
active_=?
group by this_.room, this_.aDate, this_.active
