A simple entity is defined as
{code :java }
@Entity
public class Employee {
@Id
private String name;
private String title;
...
}
{code}
A query is constructed as below
{code :java }
String jpql = "SELECT e FROM Employee e WHERE SUBSTRING(e.name,3,1) != '-' ORDER BY e.name DESC";
TypedQuery<Employee> query = entityManager.createQuery(jpql, Employee.class);
query.setFirstResult(1);
query.setMaxResults(10);
for (Employee e : query.getResultList()) {
log.info("------> Found entity('" + e.getName() + "', '" + e.getTitle() + "')");
}
{code}
The generated SQL for the query is:
{code :java }
select
*
from
( select
inner2_.*,
rownumber() over(
order by
order of inner2_) as rownumber_ from
( select
employee0_.name as name1_0_,
employee0_.title as title2_0_
from
Employee employee0_
where
substr(employee0_.name, 3, 1)<>'-'
order by
employee0_.name DESC fetch first 11 rows only ) as inner2_ ) as inner1_
where
rownumber_ > 1
order by
rownumber_
{code}
The query fails with DB2 12 (with "SQLCODE=-199, SQLSTATE=42601") but works with DB2 11. |
|
