Geek asks and answers

Computed column as a property of an object class?

Relatively new to JPA, so I have one architectural question. Let's say I have EMPLOYEE and DEPARTMENT tables with many relationships (i.e. many employees work in the same department):

EMPLOYEE
  EMPLOYEE_ID
  EMPLOYEE_NAME
  DEPARTMENT_ID 

DEPARTMENT
  DEPARTMENT_ID
  DEPARTMENT_NAME

Therefore, I can determine the correct objects for Employee and Department, there are no problems. However, in one view, I would like to display a list of departments with the number of employees working in this 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 if this is the right strategy for this using JPA ... I don’t always want to get the number of employees for the unit, since there is only one idea when it is needed.

It seems that Hibernate @Formula will be one of the possible approaches, but afaik does not comply with the JPA standard.

+5
3

QL - , , .

, , DepartmentEmployeeCount, :

public DepartmentEmployeeCount(String departmentName, Integer employeeCount)

QL - :

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

, (*), .

, DepartmentEmployeeCount, NEW, :

SELECT D.DEPARTMENT_NAME, count(E.id)

a List<Object[]>, 2 , _ count.

, employeeCount, - 2 . , ( ).

,

SELECT D from Department D

List<Department>

:

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

List<Object[]> DEPARTMENT_ID .

. ( , , , Hibernate).

+4

1: , , MattR. "", , , , , , ?

2: , . -, , , MattR, . - :

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
}


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

DepartmentInfo .

+3

, . @Column .

Let's say for your original query you can add a member countEmployeesas follows. Also add insertable=falseand updatable=falseso that the object manager does not try to include it in the insert or update instructions:

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 request:

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 Jpa data repositories.

+1
source

More articles:


All Articles