openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dain Sundstrom <>
Subject Re: possible to write a JPA Query to that filters both an Entity and its relationship entities?
Date Fri, 23 Feb 2007 15:21:26 GMT
Is it now legal to select multiple items using EJBQL?  I haven't  
looked at the 3.0 syntax much.


On Feb 22, 2007, at 4:54 PM, Craig L Russell wrote:

> How about
> select dept, oldtimers from Department dept LEFT JOIN  
> dept.employeeCollection oldtimers WHERE dept.deptno >= 100 AND   
> oldtimers.yearsOfService >= 15
> That should give you only departments that satisfy the deptno  
> constraint and since you are not using outer join, only departments  
> that also have at least one employee satisfying the yearsOfService  
> constraint.
> The results would have one element for each employee. Each result  
> row would contain the department and the employee. So you would  
> have the results:
> [looks better in monospace font]
> dept            oldtimers
> =============   ==============
> R&D             Larry
> R&D             Curly
> R&D             Moe
> Entertainment   Fred
> Entertainment   Ginger
> Craig
> On Feb 22, 2007, at 12:21 PM, Tom Mutdosch wrote:
>> Hi there,
>> I have a question about JPA Queries containing relationships and a  
>> WHERE filter.  I basically want to have a JSF page containing a  
>> datatable that shows a list of Departments - each Department row  
>> shows relevant columns (department name, ID, etc) as well as a  
>> nested list of that Department's Employees with 15 or more years  
>> of service.
>> I was wondering if there was any way to create a JPA query that  
>> would return me such a result using just a single query.  From  
>> what I have heard, I don't believe this is possible but thought  
>> I'd ask here just to be sure.  As far as I know, JPA will just  
>> return me the Departments that match that query and each  
>> Department will contain every Employee that belongs to it.
>> Background: I have a DEPARTMENT entity with a one-to-many  
>> relationship to an EMPLOYEE entity.  On my web page, I want to  
>> display "departments with a department number >= 100 and  
>> containing EMPLOYEES with more than 15 years of service".
>> Using SDOs I was able to write a query that would return a List of  
>> results which I would then bind to my JSF datatable.  This list  
>> would contain Departments, and the Departments would have the  
>> Employees that matched that criteria.  To do something similar in  
>> JPA, I think I now need to do n + 1 queries.  That is, one query  
>> to retrieve the departments that I want to display, and then for  
>> each department another query to get the filtered list of its  
>> Employees.
>> In summary, I basically want to do something like:
>> select d from Department d LEFT JOIN d.employeeCollection e WHERE  
>> d.deptno >= 100 AND  e.yearsOfService >= 15
>> But I think I need to do two separate queries.  One to get the  
>> departments:
>> select d from Department d WHERE d.deptno >= 100
>> Then for each Department returned in that result set, a separate  
>> query to get the employees I'm after:
>> select e from Employee e where e.department.deptno = d.deptno and
>> e.yearsOfService >= 15
>> Does this sound like the right/only way to do this?
>> Thanks,
>> Tom
> Craig Russell
> Architect, Sun Java Enterprise System
> 408 276-5638
> P.S. A good JDO? O, Gasp!

View raw message