EclipseLink/Development/2.1/AdvancedJPA Queries

bug 219814

Extensions to be implemented - in priority order

All database functions

PostGIS Intersects (forum)

http://www.eclipse.org/forums/index.phpt=msg&th=124404&start=0&S=6eb7686393e997064947f7e51e5aca88

Spatial Functionality via JPQL

http://www.eclipse.org/newsportal/article.php?id=848&group=eclipse.rt.eclipselink#848

Functions in select and order by clause

http://forums.oracle.com/forums/thread.jspa?messageID=3903302&#3903302

http://dev.eclipse.org/mhonarc/lists/eclipselink-users/msg03720.html

Already works for select clause (tested ABS), doesn’t work for order by clause.

However fails:

SELECT CONCAT(e.firstName, e.lastName) FROM Employee e

but works:

SELECT CONCAT(e.firstName, e.lastName) FROM Employee e WHERE e.id > 0

Order by should be applicable to any field type

http://forums.oracle.com/forums/thread.jspa?threadID=870722

Fields and tables, "from X x, Table(Y) y", "x.field(y)"

?

Object parameters and nested parameters "(:employee).id"

Saw several times users attempting something like:

SELECT e FROM Employee e WHERE e.address IN (SELECT a FROM Address…)

Fetch groups "Select emp(id, name) from Employee emp"

?

SQL hints

?

Connect by

?

Pessimistic locking "for update of emp"

Accessible through hints ("for update" only).

Batch joins

http://www.eclipse.org/forums/index.php?t=msg&th=123943&start=0&S=f9832b9dae179e13094b3c027540647f

Accessible through hints.

Extensions already implemented in 2.0

Like with escapes, any types

Works for strings:

SELECT e FROM Employee e WHERE e.firstName LIKE "John"

Have not tested for other types.

Which types should be tested?

Nested fetch joins (both inner and outer)

http://www.eclipse.org/forums/index.php?t=msg&goto=379275&#msg_379275

http://www.eclipse.org/forums/index.php?&t=msg&th=125842

Inner:

SELECT p FROM SmallProject p JOIN p.teamLeader e JOIN e.address a WHERE p.name = 'Project1' or e.firstName = 'John' and a.province = 'ON'

with setHint("eclipselink.join-fetch", "p.teamLeader.address")

Outer:

SELECT p FROM SmallProject p LEFT JOIN p.teamLeader e LEFT JOIN e.address a WHERE p.name = 'Project1' or e.firstName = 'John' and a.province = 'ON'

with setHint("eclipselink.left-join-fetch", "p.teamLeader.address")

Note that aliasing on the right side of FETCH is explicitly prohibited by the spec., so seems like no extension in JBQL is possible.

Pass list as parameter

SELECT e FROM Employee e WHERE e.id in :list

http://forums.oracle.com/forums/thread.jspa?threadID=871532