CODEWITHSUNDEEP
databasehibernatejakarta-eejpa
AndreiM
AndreiM

Reputation: 4446

JPA - calculated column as entity class property?

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.

Upvotes: 7

Views: 10319

Answers (3)

Josue Ruiz Salazar
Josue Ruiz Salazar

Reputation: 71

You could create a member in your entity as an additional column, and then reference it with an alias in your query. The column name in the @Column annotation must match the alias.

Say, for your original query, you can add a countEmployees member as following. Also add insertable=false and updatable=false so the entity manager wont try to include it in insert or update statements:

public class Department {

    @Column(name="DEPARTMENT_ID")
    Long departmentId;

    @Column(name="DEPARTMENT_NAME")
    String departmentName;

    @Column(name="countEmployees", insertable=false, updatable=false)
    Long countEmployees;

    //accessors omitted

}

And your query:

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

This also applies when working with Spring Data Jpa Repositories.

Upvotes: 2

digitaljoel
digitaljoel

Reputation: 26574

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.

Upvotes: 4

MattR
MattR

Reputation: 7048

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

Upvotes: 4

Related Questions