Hi Markus,
Thanks for the reply. I know there's faster ways to get the data that I need,
but if I did do it this way (n+1 queries) do you know if/how JPA caches this
data? Once I queried for all of the Departments (containing their Employees),
when I executed a separate query to get the filtered set of Employees for each
Department object, would it go to the Database each time? Or would it just
retrieve the Employees from a cache somehow? I couldn't find anything on this
when I searched the spec.
Thanks for the helpful info,
Tom
Markus Fuchs wrote:
> Hi Tom,
>
> Tom Mutdosch wrote:
> > Hi Markus,
> >
> > Thanks for the reply. That makes sense; I just wasn't sure if JPA was
> > able to more easily handle these more complex scenarios. I'll give
> > you an example of where I would want to do this:
> >
> > I have a JSF page with a datatable showing a list of departments -
> > each department row shows relevant fields (department name, ID, etc)
> > as well as a nested list of Employees with 15 years of service. It
> > would be nice if I could just bind the result of a single JPA query to
> > that datatable. That is, I could just bind the list of Departments,
> > and each one would have the appropriate list of Employees I want to
> > display.
> >
> > So in JPA to do this, I have to do n + 1 separate 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?
> >
> That sounds correct.
> > That is doable, but it does complicate things a bit in that in my JSF
> > page I can't just bind a list of Departments to my datatable; I would
> > need to bind a list of DepartmentBeans that would wrapper my
> > Department and for the employees property would instead return the
> > results of a separate query to populate the Employees list for each
> > row. Does that sound correct?
> >
> I'm not an JSF expert, but you'd need to execute n + 1 queries.
>
> Regards,
>
> -- markus.
> > Thanks
> > Tom
> >
> > Markus Fuchs wrote:
> >> Hi Tom,
> >>
> >> JPA queries are not intended to return incompletely initialized fields.
> >> As the Department in question really contains Employees besides those
> >> having more than 15 years of service, the employeeCollection will
> always
> >> contain all Employees from that Department.
> >>
> >> A query returning a Collection containing the Employees with more that
> >> 15 years of service is:
> >>
> >> select e from Employee e where e.department.deptno = 100 and
> >> e.yearsOfService >= 15
> >>
> >> Tom Mutdosch wrote:
> >> > Hi there,
> >> > I have a question regarding JPA Query
> >> >
> >> > I have a DEPARTMENT entity with a one-to-many relationship to an
> >> > EMPLOYEE entity. Say I want to get "departments with a department
> >> > number of 100 and containing EMPLOYEES with more than 15 years of
> >> > service".
> >> >
> >> > Is it possible to get back a single result containing the DEPARTMENT
> >> > object; and its relationship collection list consisting of only the
> >> > EMPLOYEES matching the criteria?
> >> >
> >> No. Please see above.
> >>
> >> > I've tried creating various queries using joins, but they always
> >> > return me back the DEPARTMENT I want, but the its employee list
> >> > contains ALL of the employees belonging to that department, not just
> >> > the ones matching the "15 years of service" filter.
> >> >
> >> > For example:
> >> > select d from Department d LEFT JOIN d.employeeCollection e WHERE
> >> > d.deptno = 100 AND e.yearsOfService >= 15
> >> >
> >> > This returns one result - a Department with ALL of the employees
> that
> >> > belong to that department (not just the ones matching
> >> e.yearsOfService
> >> > >= 15)
> >> >
> >> > I also tried this:
> >> > select d, e from Department d LEFT JOIN d.employeeCollection e WHERE
> >> > d.deptno = 100 AND e.yearsOfService >= 15
> >> >
> >> > And that returns 3 results, each an array containing a Department
> and
> >> > Employee. This is giving me the right data (there are 3 employees
> >> > that match the criteria), but I really just want to get back a
> single
> >> > department result containing the filtered employees. Is this
> >> possible?
> >> >
> >> No. How should the expected result look like in this case? An array
> >> containing one Department and three Employees?
> >>
> >> HTH,
> >>
> >> -- markus.
>