JPA - calculated column as entity class property?

Question

Relatively new to JPA, so I have one kind of architectural question. Let's say I have tables EMPLOYEE and DEPARTMENT with many to one relationship (i.e. many employees work for one department):

EMPLOYEE
  EMPLOYEE_ID
  EMPLOYEE_NAME
  DEPARTMENT_ID 

DEPARTMENT
  DEPARTMENT_ID
  DEPARTMENT_NAME

So I can define proper entities for Employee and Department, there's no problem. However, in one view I would like to display list of departments with number of employees working for that department, something like this:

SELECT D.DEPARTMENT_NAME, 
       (SELECT COUNT(*) FROM EMPLOYEE E WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID) NUMBER_OF_EMPLOYEES
FROM DEPARTMENT D

I'm just not sure what is the right strategy to accomplish this using JPA... I don't want to always fetch number of employees for Department entity, as there is only one view when it is needed.

It looks like Hibernate's @Formula would be one possible approach, but afaik it does not conform with JPA standard.

Answer 1

You can create any object in your QL using the "new" syntax - your class just needs a constructor that takes the values returned by your query.

For example, with a class like DepartmentEmployeeCount, with a constructor:

public DepartmentEmployeeCount(String departmentName, Integer employeeCount)

you could use QL something like:

SELECT NEW DepartmentEmployeeCount(D.DEPARTMENT_NAME, count(E.id)) from Department D left join D.employees E GROUP BY D.DEPARTMENT_NAME

Or if you were just selecting the count(*) you could simply cast the query result to a Number.

Alternatively, to do the same without the DepartmentEmployeeCount class, you could leave out the NEW, so:

SELECT D.DEPARTMENT_NAME, count(E.id)    

This would return a List<Object[]> where each list item was an array of 2 elements, departmentName and count.

To answer your later question in the comments, to populate all fields of a Department plus a transient employeeCount field, one suggestion would be to do 2 queries. This would still be more efficient than your original query (a subselect for each employee count).

So one query to read the departments

SELECT D from Department D

giving you a List<Department>

Then a 2nd query returning a temporary array:

SELECT D.DEPARTMENT_ID, count(E.id) from Department D left join D.employees E GROUP BY D.DEPARTMENT_ID

giving you a List<Object[]> with DEPARTMENT_ID and count in it.

Then you use the 2nd list to update the transient count property on your first list. (You could try selecting into a Map to make this lookup easier, but I think that's a Hibernate feature).

Answer 2

Option 1: I suggested this since you didn't like the constructor route MattR was suggesting. You mentioned the word "view" several times, and I know you were talking about the view to the user, but why not setup a view in your database that includes the computed columns and then create a read-only entity that maps to the computed columns?

Option 2: In response to your comment about not wanting to create a view. You could create a container object that holds the entity and the calculated column, then much like MattR suggests, you use a new in your select. Something like:

public class DepartmentInfo {
    private Department department;

    // this might have to be long or something
    // just see what construct JPA tries to call
    private int employeeCount;

    public DepartmentInfo( Department d, int count ) { 
        department = d;
        employeeCount = count;
    }
    // getters and setters here
}

Then your select becomes

SELECT new my.package.DepartmentInfo( D, 
       (SELECT COUNT(*) FROM EMPLOYEE E WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID))
FROM DEPARTMENT D

With that you can use the DepartmentInfo to get the properties you are interested in.