CODEWITHSUNDEEP
javastringhibernatejpaaggregate
Stephan
Stephan

Reputation: 43023

How to aggregate strings with JPA?

Base Data:

DEPTNO ENAME
------ ------
    20 SMITH
    30 ALLEN
    30 WARD
    20 JONES
    30 MARTIN
    30 BLAKE
    10 CLARK
    20 SCOTT
    10 KING
    30 TURNER
    20 ADAMS
    30 JAMES
    20 FORD
    10 MILLER

Desired Output:

DEPTNO EMPLOYEES
------ ---------
    10 CLARK,KING,MILLER
    20 SMITH,FORD,ADAMS,SCOTT,JONES
    30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

I have tried this jpql query:

 SELECT deptno, new list(ename) as employees
 FROM dept_emp

However an exception is raised:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: new near line 1, column 15

JPA 2.1
Hibernate 4.3.1

See also: https://stackoverflow.com/a/24570617/363573

Upvotes: 5

Views: 7508

Answers (3)

Saurav
Saurav

Reputation: 1

This worked for me.

SELECT deptno,  STRING_AGG(ename, ', ') as employees
FROM dept_emp
GROUP BY deptno

Refer to this

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 691755

You're trying to implement UI-related functionality (how data should be displayed) in the persistence layer, using a JPQL query. That's a bad idea. Retrieve the data in the UI layer using the persistence layer, and then format the data as you wish. For example:

List<Employee> employees = em.createQuery("select e from Employee e").getResultList();

And in your presentation layer:

Multimap<String, Employee> employeesByDepartment = 
    Multimaps.index(employees, Employee::getDepartmentNumber);
for (String departmentNumber : employeesByDepartment.keySet()) {
    System.out.print(departmentNumber);
    System.out.print("\t");
    System.out.println(Joiner.on(", ").join(employeesByDepartment.get(departmentNumber)));
}

Upvotes: 0

yunus kula
yunus kula

Reputation: 909

Firstly to use jpa , you should entity class.

@Entity
class Employee {
   int deptNo;
   String name;

}

Query is

SELECT e.deptNo , new list(e.name) FROM Employee e GROUP BY e.deptNo

Upvotes: 1

Related Questions