Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

Difference between revisions of "EclipseLink/Development/2.1/AdvancedJPA Queries"

(Fetch groups "Select emp(id, name) from Employee emp")
(Functions in select and order by clause)
Line 16: Line 16:
 
====Functions in select and order by clause====
 
====Functions in select and order by clause====
  
: [http://forums.oracle.com/forums/thread.jspa?messageID=3903302&#3903302 ORDER BY RAND()] - platform specific?
+
* [http://forums.oracle.com/forums/thread.jspa?messageID=3903302&#3903302 ORDER BY RAND()] - platform specific?
: [http://dev.eclipse.org/mhonarc/lists/eclipselink-users/msg03720.html Aggregate functions in ORDER BY]
+
* [http://dev.eclipse.org/mhonarc/lists/eclipselink-users/msg03720.html Aggregate functions in ORDER BY]
:Already works for select clause (tested ABS), doesn’t work for order by clause.
+
* CONCAT projection query without WHERE
:However fails:
+
<source lang="java">
:SELECT CONCAT(e.firstName, e.lastName) FROM Employee e  
+
// This fails:
:but works:
+
SELECT CONCAT(e.firstName, e.lastName) FROM Employee e  
 +
// This works
 
:SELECT CONCAT(e.firstName, e.lastName) FROM Employee e WHERE e.id > 0
 
:SELECT CONCAT(e.firstName, e.lastName) FROM Employee e WHERE e.id > 0
 
+
</source>
 
: [http://forums.oracle.com/forums/thread.jspa?threadID=870722 ORDER BY boolean]
 
: [http://forums.oracle.com/forums/thread.jspa?threadID=870722 ORDER BY boolean]
  

Revision as of 10:20, 19 January 2010

bug 219814

Extensions to be implemented - in priority order

All database functions

http://www.eclipse.org/forums/index.phpt=msg&th=124404&start=0&S=6eb7686393e997064947f7e51e5aca88
JPQL equivalent to Oracle's NVL function

Spatial Functionality via JPQL

PostGIS Intersects (forum)
Oracle Spatial
  • SDO_WITHIN_DISTANCE
  • SDO_NN
  • SDO_RELATE
  • SDO_FILTER

Functions in select and order by clause

// This fails:
SELECT CONCAT(e.firstName, e.lastName) FROM Employee e 
// This works
:SELECT CONCAT(e.firstName, e.lastName) FROM Employee e WHERE e.id > 0
ORDER BY boolean

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…)

Dynamic FetchGroup in JPQL

This refers to specifying a dynamic FetchGroup that is used instead of the default one using JPQL.

Hint Example

Query query = em.createQuery("Select emp from Employee emp");
FetchGroup fg = new FetchGroup();
fg.addAttribute("name); 
query.setHint(QueryHints.FETCH_GROUP, fg);
 
List<Employee> results = query.getResultsList();

Proposed JPQL:

Query query = em.createQuery("Select emp(name) from Employee emp");
 
List<Employee> results = query.getResultsList();

SQL Hints

Currently EclipseLink supports specifying (Oracle) SQL hints using [ DatabaseQuery.setHintString("...")] (see Using_Advanced_Query_API_(ELUG)#How_to_Use_Oracle_Hints) or through JPA using QueryHints.HINT. These hints are limited in where they can be generated in the resulting SQL.

These appear to be Oracle specific. Is this approach useful on other database platforms?

HIERARCHICAL QUERIES (Oracle)

ELUG: Using_Advanced_Query_API_(ELUG)#How_to_Use_Hierarchical_Queries


  • START WITH
  • CONNECT BY
  • ORDER SIBLINGS

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

Back to the top