Following criteria produces invalid sql on some db platforms. First, the entity Foo.class, then criteria that creates sql grammar exception below:
{code:java}
@Entity
@Table(name = "FOO")
public class Foo {
@Id
private Long fooId;
private String name;
}
{code}
Then, the criteria in question:
{code:java}
Criteria criteria = getSessionFactory().getCurrentSession().createCriteria(Foo.class);
criteria.setProjection(Projections.projectionList()
.add(Projections.alias(Projections.max("name"), "sortProperty"))
.add(Projections.groupProperty("fooId").as("id")))
.addOrder(Order.asc("sortProperty").ignoreCase());
{code}
Results in following sql:
{code:sql}
select
max(this_.NAME) as y0_,
this_.FOO_ID as y1_
from
FOO this_
group by
this_.FOO_ID
order by
lower(y0_) asc;
{code}
In Oracle, this case-insensitive query runs fine. But other db platforms (e.g., H2 and Postgres) do not support an alias in the lower() function. |
|
