how to retrieve an aggregation function query with spring-data-jpa

Question

I am using Spring data jpa 1.2 and I can't find anyway to retrieve an aggregate query result like the following one.

select count(v), date(v.createTimestamp) from UserEntity v
    group by date(v.createTimestamp)

Which work perfectly with the native JPA

@Entity()
public class UserEntity {

   @Id
   private long id;
   .
   .
   @Column(columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP")
   private Timestamp createTimestamp;

}

any my JPA repository is

public interface UserRepository extends JpaRepository<UserEntity, Long>, 
      JpaSpecificationExecutor<UserEntity> {
}

so how can I do an aggregate queries throw Spring data, I find absolutely nothing in the documentation

Ehab Al-Hakawati · Accepted Answer · 2014-04-16 11:26:41Z

8

I found a way to do this

public interface UserRepository extends JpaRepository<UserEntity, Long>, 
      JpaSpecificationExecutor<UserEntity> {

      @Query(value = "select count(v), date(v.createTimestamp) from UserEntity v group by date(v.createTimestamp)", 
             countQuery = "select count(1) from (select count(1) from UserEntity v group by date(v.createTimestamp)) z")
      public List<Object[]> findCountPerDay();
}

This way we can get aggregate data along with actual count ( aggregated records )

answered Apr 16, 2014 at 11:26

Ehab Al-Hakawati

9924 gold badges11 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:34:21Z

5

"countBy" is currently not supported in Spring Data:

Support to 'countBy' query methods

Does Spring Data JPA have any way to count entites using method name resolving?

edited May 23, 2017 at 12:34

CommunityBot

11 silver badge

answered Mar 15, 2013 at 10:30

ezgi

3762 silver badges3 bronze badges

1 Comment

chrismarx Over a year ago

The second you list actually has been updated to reflect that as of spring data 1.7, the countby predicate has been added

Bob · Accepted Answer · 2015-10-27 15:43:50Z

1

you can also use @NativeQuery option

edited Oct 27, 2015 at 15:43

Bob

5,64810 gold badges51 silver badges81 bronze badges

answered Oct 27, 2015 at 4:23

Charles Raimond

112 bronze badges

Comments

manocha_ak · Accepted Answer · 2013-03-18 08:04:36Z

0

You can have @Query for custom queries for your unsupporeted methods in spring-data.

answered Mar 18, 2013 at 8:04

manocha_ak

9027 silver badges19 bronze badges

Collectives™ on Stack Overflow

how to retrieve an aggregation function query with spring-data-jpa

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related