Problem with Spring Boot/Hibernate while retrieving the number of recent records from MySQL database

I am using the following query in the userRepository to fetch the recent records which is created in the last month from MySQL

@Query("select count(*) from User user where user.created_on > date_add(now(), interval -1 month)") 

But I am getting the following errors.

antlr.MismatchedTokenException: expecting CLOSE, found 'month'
    at antlr.Parser.match(Parser.java:211) ~[antlr-2.7.7.jar:na]

antlr.MismatchedTokenException: expecting EOF, found ')'
    at antlr.Parser.match(Parser.java:211) ~[antlr-2.7.7.jar:na]

How can I resolve this issue?

Hi i will go against using jpql/sql query in this case. This is how i have done it. Create repository and use method findAllByCreatedOnBetween pass current and last month as date object, so you are basically selecting all rows between given dates.
Here is the sql generated by hibernate

Generated SQL:

select user0_.id as id1_20_, user0_.created_on as created_2_20_, user0_.name as name3_20_ from user user0_ where user0_.created_on between ? and ?

Entity Class User.java

import org.hibernate.annotations.Type;
import javax.persistence.*;
import java.util.Date;
import java.util.UUID;

@Entity
@Table(name = "user")
public class User {
    @Id
    @GeneratedValue
    @Type(type = "uuid-char")
    private UUID id;

    private String name;

    @Temporal(value = TemporalType.DATE)
    private Date createdOn;

    public UUID getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Date getCreatedOn() {
        return createdOn;
    }

    public void setCreatedOn(Date createdOn) {
        this.createdOn = createdOn;
    }
}

Repository

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.UUID;

@Repository
public interface UserRepository extends JpaRepository<User, UUID> {
    List<User> findAllByCreatedOnBetween(Date lastMonth, Date currentMonth);
}

Unit Test

import in.silentsudo.hibernatetips.TestApplication;
import in.silentsudo.hibernatetips.domain.User;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit.jupiter.SpringExtension;
import org.springframework.transaction.annotation.Transactional;

import java.util.Calendar;
import java.util.Date;
import java.util.List;

@ExtendWith(SpringExtension.class)
@Transactional
@SpringBootTest(classes = TestApplication.class)
class UserCreatedInLastMonth {

    @Autowired
    private UserRepository userRepository;

    @Test
    void testUserCreatedLastMonth() {
        User silentSudo = new User();
        silentSudo.setName("silentsudo");
        silentSudo.setCreatedOn(getCreatedDate(5, Calendar.JULY, 2019));
        userRepository.save(silentSudo);
        Assertions.assertNotNull(silentSudo.getId());

        User randomUser = new User();
        randomUser.setName("random-user");
        randomUser.setCreatedOn(getCreatedDate(1, Calendar.OCTOBER, 2020));
        userRepository.save(randomUser);
        Assertions.assertNotNull(randomUser.getId());

        //Current Calendar
        Calendar currentCalendar = Calendar.getInstance();
        Date today = currentCalendar.getTime();

        //A month before
        currentCalendar.add(Calendar.MONTH, -1);
        Date monthBefore = currentCalendar.getTime();

        Assertions.assertTrue(today.after(monthBefore));

        List<User> allUserInLastMonth = userRepository.findAllByCreatedOnBetween(monthBefore, today);
        Assertions.assertEquals(1, allUserInLastMonth.size());

    }


    private Date getCreatedDate(int day, int month, int year) {
        Calendar createdDate = Calendar.getInstance();
        createdDate.set(Calendar.DAY_OF_MONTH, day);
        createdDate.set(Calendar.MONTH, month);
        createdDate.set(Calendar.YEAR, year);
        return createdDate.getTime();
    }

}

You are using JPQL, since it is used by default with @Query annotation.
Enable native SQL query with:

@Query(nativeQuery = true, value = "select count(*) from User user where user.created_on > date_add(now(), interval -1 month)"