Jpa group by java

JPQL GROUP BY and HAVING Clauses

The GROUP BY clause allows to divide the query results into groups.

Optional HAVING clause can be used with GROUP BY to filter over the groups.

Quick Example

Query query = em.createQuery( "SELECT e.dept, MAX(e.salary) FROM Employee e GROUP BY e.dept HAVING e.dept IN ('IT', 'Admin')"); List resultList = query.getResultList();

Examples

Entity

@Entity public class Employee

Using GROUP BY — HAVING

public class ExampleMain < private static EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("example-unit"); public static void main(String[] args) < try < persistEmployees(); findEmployeeCountGroupByDept(); findEmployeeAvgSalariesGroupByDept(); findEmployeeCountGroupBySalary(); findEmployeeMaxSalariesGroupBySelectedDept(); >finally < entityManagerFactory.close(); >> public static void persistEmployees() < Employee employee1 = Employee.create("Diana", "IT", 3000); Employee employee2 = Employee.create("Rose", "Admin", 2000); Employee employee3 = Employee.create("Denise", "Admin", 4000); Employee employee4 = Employee.create("Mike", "IT", 3500); Employee employee5 = Employee.create("Linda", "Sales", 2000); EntityManager em = entityManagerFactory.createEntityManager(); em.getTransaction().begin(); em.persist(employee1); em.persist(employee2); em.persist(employee3); em.persist(employee4); em.persist(employee5); em.getTransaction().commit(); em.close(); >private static void findEmployeeCountGroupByDept() < System.out.println("-- Employee count group by dept --"); EntityManager em = entityManagerFactory.createEntityManager(); Query query = em.createQuery( "SELECT e.dept, COUNT(e) FROM Employee e GROUP BY e.dept"); ListresultList = query.getResultList(); resultList.forEach(r -> System.out.println(Arrays.toString(r))); em.close(); > private static void findEmployeeAvgSalariesGroupByDept() < System.out.println("-- Employees avg salary group by dept --"); EntityManager em = entityManagerFactory.createEntityManager(); Query query = em.createQuery( "SELECT e.dept, AVG(e.salary) FROM Employee e GROUP BY e.dept"); ListresultList = query.getResultList(); resultList.forEach(r -> System.out.println(Arrays.toString(r))); em.close(); > private static void findEmployeeCountGroupBySalary() < System.out.println("-- Employee count group by salary --"); EntityManager em = entityManagerFactory.createEntityManager(); Query query = em.createQuery( "SELECT e.salary, COUNT(e) FROM Employee e GROUP BY e.salary"); ListresultList = query.getResultList(); resultList.forEach(r -> System.out.println(Arrays.toString(r))); em.close(); > private static void findEmployeeMaxSalariesGroupBySelectedDept() < System.out.println("-- Employees max salary group by dept - only in IT and Admin dept --"); EntityManager em = entityManagerFactory.createEntityManager(); Query query = em.createQuery( "SELECT e.dept, MAX(e.salary) FROM Employee e GROUP BY e.dept HAVING e.dept IN ('IT', 'Admin')"); ListresultList = query.getResultList(); resultList.forEach(r -> System.out.println(Arrays.toString(r))); em.close(); > >

-- Employee count group by dept --
[IT, 2]
[Sales, 1]
[Admin, 2]
-- Employees avg salary group by dept --
[IT, 3250.0]
[Sales, 2000.0]
[Admin, 3000.0]
-- Employee count group by salary --
[2000, 2]
[4000, 1]
[3500, 1]
[3000, 1]
-- Employees max salary group by dept - only in IT and Admin dept --
[IT, 3500]
[Admin, 4000]

Example Project

Dependencies and Technologies Used:

• h2 1.4.197: H2 Database Engine.
• hibernate-core 5.2.13.Final: The core O/RM functionality as provided by Hibernate.
  Implements javax.persistence:javax.persistence-api version 2.1
• JDK 1.8
• Maven 3.3.9

Источник

Java group by in spring data jpa

Now how do we map this result into JPA. JpaConverterJson.java DepartmentWiseEmployees.java Then a simple JPQL inside the Repository Reference: How to map a map JSON column to Java Object with JPA Solution 1: As stated in https://www.techonthenet.com/oracle/group_by.php: «The Oracle GROUP BY clause is used in a SELECT statement to collect data across multiple records and group the results by one or more columns» and has the following syntax: In your query is missing an aggregate_function such as SUM, COUNT, MIN, MAX , or AVG .

Spring data repository group by column, list of object with other columns

I ended up making a «virtual» parent entity, which acts kind of like a view

@Data @AllArgsConstructor @NoArgsConstructor @ToString @Entity @EqualsAndHashCode @Immutable @Subselect( "SELECT" + " DISTINCT SomeEntityParent.currency_source " + " FROM some_entity SomeEntityParent" + " JOIN some_entity SomeEntity" + " ON SomeEntityParent.businessValue1 = SomeEntityParent.businessValue1" ) public class SomeEntityParent < private String businessValue1; private ListsomeEntity; > 

And then a Repository interface based on that

@Repository public interface SomeEntityParentRepository extends JpaRepository

Which can then be used in services

@Autowired private SomeEntityParentRepository repository; List results = repository.findAll(); 

The flat structure is now a hierarchical object.

JPA Criteria GROUP BY Clause, The GROUP BY clause is used to collect data from one or more tables and arrange them in a group. In Criteria API, the groupBy() method of AbstractQuery

Spring data JpaRepository pagination and group by

As stated in https://www.techonthenet.com/oracle/group_by.php: «The Oracle GROUP BY clause is used in a SELECT statement to collect data across multiple records and group the results by one or more columns» and has the following syntax:

SELECT expression1, expression2, . expression_n, aggregate_function (aggregate_expression) FROM tables [WHERE conditions] GROUP BY expression1, expression2, . expression_n;

In your query is missing an aggregate_function such as SUM, COUNT, MIN, MAX , or AVG .

You usually use a groupBy when you want to aggregate results. Here, you are doing select * instead of aggregation.

@Query("SELECT l.someCol, count(*) as numOfRows from SomeTable l GROUP BY l.someCol") Page findSomething(Pageable pageable); 

The Something object can be an interface with getter methods for someCol and numOfRows.

If you’re using native query, you need to use countQuery

@Query(value = "SELECT someCol, count(*) as numRows from SomeTable GROUP BY someCol", countQuery= "SELECT count(*) FROM (SELECT someCol, count(*) as numRows from SomeTable GROUP BY someCol)", nativeQuery=true) Page findSomething(Pageable pageable); 

Ignoring why I need to do a Group By without an aggregate, I found my solution:

@Query("SELECT pr FROM PermissionRule pr WHERE pr.organizationId = ?1 AND pr.type = ?2 GROUP BY objectReference") Page getFunds(Long organizationId, String type, Pageable pageable); 

Seems more straight forward than the native_query approach.

Guide to Java 8 groupingBy Collector, The static factory methods Collectors.groupingBy() and Collectors.groupingByConcurrent() provide us with functionality similar to the ‘GROUP BY’

Spring Data Jpa not support Groupby

If you don’t like @Query for some reason, you, at least, have 2 more options:

1. CriteriaAPI with spring-data-jpa Specifications http://docs.spring.io/spring-data/jpa/docs/1.9.1.RELEASE/reference/html/#specifications
2. QueryDSL http://www.querydsl.com/static/querydsl/4.0.7/reference/html_single/#d0e372

They both support grouping operations.

Yes CriteriaAPI supports groupBy but spring Specification not! class SimpleJpaRepository in getQuery replace query select/multiselect by query.select(root); 🙁

Is there any form to group a conditions in a query method with spring, public List findByTypeAndStatusAndValue(int type, int status, int value);. I am not sure if above code works as I expect. java

JPA group by query

I am assuming you are using MySQL.

MySQL has JSON_OBJECTAGG , JSON_OBJECT

Then your query will look like following

SELECT dept_id, json_arrayagg(JSON_OBJECT('name', name, 'emp_id', empId)) as employees from EMPLOYEE group by dept; 

It will return following result set

Now how do we map this result into JPA.

JpaConverterJson.java

@Converter(autoApply = true) public class JpaConverterJson implements AttributeConverter  < private final static ObjectMapper objectMapper = new ObjectMapper(); @Override public String convertToDatabaseColumn(Object meta) < try < return objectMapper.writeValueAsString(meta); >catch (JsonProcessingException ex) < return null; // or throw an error >> @Override public Object convertToEntityAttribute(String dbData) < try < return objectMapper.readValue(dbData, Object.class); >catch (IOException ex) < // logger.error("Unexpected IOEx decoding json from database: " + dbData); return null; >> > 

DepartmentWiseEmployees.java

class DepartmentWiseEmployees < private Long deptId; @Convert(converter = JpaConverterJson.class) private Listemployees; // omitting getter and setter > 

Then a simple JPQL inside the Repository

public interface EmployeeRepository extends JPARepository  < @Query(value = "SELECT dept_id, json_arrayagg(JSON_OBJECT('name', name, 'emp_id', empId)) as employees from EMPLOYEE group by dept;", native = true) public ListfindDepartmentWiseEmployees(); > 

Writing a group by expression with spring jpa, I don’t think you can get that kind of result without writing code (i.e. in a custom repository class). BTW, I don’t see why you’re using a

Источник

Spring Data JPA Specification groupBy

sorry for my english first. i want use jpa to groupby, like : select scrip, dustup, count(*) from data flow group by scrip, dstip. so, write these code:

public class DataflowSpec < public static Specificationsearch(final String[] group, final String[] sort, final String[] desc) < return new Specification() < @Override public Predicate toPredicate(Rootroot1, CriteriaQuery query1, CriteriaBuilder builder) < // TODO Auto-generated method stub CriteriaQueryquery = builder.createQuery(Tuple.class); Root root = query.from(Dataflow.class); query.multiselect(root.get("srcip"), root.get("dstip"), builder.count(root)); query.groupBy(root.get("srcip"), root.get("dstip")); query.orderBy(builder.desc(root.get("srcip").as(BigInteger.class))); return query.getRestriction(); > >; > > 

select count(dataflow0_.id) as col_0_0_ from Dataflow dataflow0_ 

select dataflow0_.id as id1_2_, dataflow0_.byteall as byteall2_2_, dataflow0_.bytedn as bytedn3_2_, dataflow0_.byteup as byteup4_2_, dataflow0_.dstip as dstip5_2_, dataflow0_.dstport as dstport6_2_, dataflow0_.engieid as engieid7_2_, dataflow0_.flag as flag8_2_, dataflow0_.netid as netid9_2_, dataflow0_.pkgall as pkgall10_2_, dataflow0_.pkgdn as pkgdn11_2_, dataflow0_.pkgup as pkgup12_2_, dataflow0_.protocolid as protoco17_2_, dataflow0_.rtt as rtt13_2_, dataflow0_.srcip as srcip14_2_, dataflow0_.srcport as srcport15_2_, dataflow0_.updatetime as updatet16_2_ from Dataflow dataflow0_ limit ? 

so you return the Predicate for the WHERE clause (to somewhere) . and what happens to the SELECT clause part?

3 Answers 3

For people still looking for how to apply «group by» in Spring jpa Specification, you can use something like the following snippet:

 . private Dataflow dataflowFilter; @Override public Predicate toPredicate(Root<Dataflow> root, CriteriaQuery<?> cq, CriteriaBuilder cb) < Predicate predicate = cb.conjunction(); predicate.getExpressions().add(cb.equal(root.get("id"), dataflowFilter.getId())); . cq.groupBy(root.get("id")); . return predicate; > 

You can achieve spring data group by by specification , just follow
[section 2.6][1] or [section 3.6][2] for version before or after 2.0. For single repository manipulation, the two versions have identical solution. For the *all * repository solution, before 2.0 use [customized factory bean][3], while after 2.0 this factory bean manipulation is omitted.

public Map testSpecification(String neId) < SingularAttribute attribute = AlarmData_.isClear; Specificationwhere = Specification.where( (root, query, cb) -> cb.equal(root.get(attribute), false) ); final Map result = alarmDataRepository.groupAndCount(AlarmData_.alarmLevel, where ); return result; > 

public interface AlarmDataRepository extends JpaRepository, JpaSpecificationExecutor, CustomizedGroupCountRepository  

public interface CustomizedGroupCountRepository < MapgroupAndCount(SingularAttribute singularAttribute, Specification where); > public class CustomizedGroupCountRepositoryImpl implements CustomizedGroupCountRepository < private final EntityManager entityManager; public CustomizedGroupCountRepositoryImpl(EntityManager entityManager) < Assert.notNull(entityManager, "EntityManager must not be null!"); this.entityManager = entityManager; >@Override public Map groupAndCount(SingularAttribute singularAttribute, Specification where) < final CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); final CriteriaQueryquery = criteriaBuilder.createQuery(Tuple.class); final Root root = query.from(AlarmData.class); final Path expression = root.get(singularAttribute); query.multiselect(expression, criteriaBuilder.count(root)); query.select(criteriaBuilder.tuple(expression, criteriaBuilder.count(root))); query.where(where.toPredicate(root, query, criteriaBuilder)); query.groupBy(expression); final List resultList = entityManager.createQuery(query).getResultList(); return resultList.stream() .collect(toMap( t -> t.get(0, AlarmMsg.AlarmLevel.class), t -> t.get(1, Long.class)) ); > >