Count Date in JPA or JPQL

Question

I need to count registered users in current month. I use a primitive method

I use H2 Database.

1. In Model(Reader) i have a field: private String LocalDate because i save the date filed in database as String:

   @Override
   public String getCurrentDate() {
       LocalDate localDate = LocalDate.now();
       DateTimeFormatter format = DateTimeFormatter.ofPattern("yyyy-MM-dd");
       String formatDate = localDate.format(format);
       return  formatDate;
   }
   

2. In ReaderRepository I have this:

   Integer getCountDatesCurrentMonth();
   List<String> allDates();
   

Where in List I get all dates from table.

3. In ReaderServiceImpl I have this method:

   @Override
   public Integer getCurrentMonthRegisteredReaders() {
       List<String> dates = readerRepository.allDates();
       Integer date = LocalDate.now().getMonthValue();
       String s;
       int count=0;
   
       for(int i =0;i<dates.size();i++){
           s = dates.get(i).charAt(5)+""+dates.get(i).charAt(6);
           if(s.equals(date.toString()))
               count++;
       }
   
       return count;
   

Where I get the count of users registered in current month

The code works perfect and shows me on the web page the readers registered in current month, but I want do this in one line or two with JPA or JPQL and I cant do that.

I've tried with JPA, but doesn't work:

Integer countByDateMonthValue(int currentMonth);

I get error:

 Caused by: java.lang.IllegalStateException: Illegal attempt to dereference path source           [null.date] of basic type

Or with JPQL:

@Query("select count(date) from Reader where... ")

but I don't know further what should I do...

Answer 1

The challenge here is that JPQL doesn't define many useful methods to manipulate dates. So the query will either depend on your JPA implementation (probably Hibernate or EclipseLink) or on the database you are using or both.

This one should work with Hibernate

@Query("select count(date) from Reader r where month(r.date) = :month")
int countForMonth(int month);

This one should work with Oracle

@Query("select count(date) from Reader r where EXTRACT(month FROM r.date) = :month", nativeQuery = true)
int countForMonth(int month);

Or if you always want the current month as a basis

@Query("select count(date) from Reader r where EXTRACT(month FROM r.date) = EXTRACT(month FROM sysdate)", nativeQuery = true)
int countForMonth();

Your current logic an the queries above ignore the year, so rows from all years get counted, if the month matches. If you really want only the rows from the current month counted, or if it doesn't matter because you have rows from a single row anyway, you could create the date range using a SpEL expression. You could register custom functions that provide the beginning and end of the current month an do something like this in the query:

@Query("select count(date) from Reader r where r.date between :#{startOfMonth(month)} and :#{endOfMonth(month)}")
int countForMonth(int month);

See this blog article how to use SpEL expressions in queries and how to register custom extensions to the evaluation context.