CODEWITHSUNDEEP
javamysqlsqlspringspring-data-jpa
Thiago Pereira
Thiago Pereira

Reputation: 614

Spring Boot + Spring Data JPA query from java.util.Date type using only the month

I'm using Spring Boot 1.4.0 with Spring Data JPA 1.10.2, and I find myself in an impasse to create a query like this:

SELECT * FROM myDatabase
WHERE
MONTH(nascimento) = '11';

My goal is list all birthdays of the current month, of a field with sql format year-month-day.

In my @Entity, I store a java.util.Date type attribute, named nascimento.

@Entity
public class Cliente {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @NotEmpty
    private String nome;

    @NotNull
    private Date nascimento;

    // Get/Set...
}

In my @Repository I use the Spring Data CrudRepository interface.

@Repository
public interface ClienteRepository extends CrudRepository<Cliente, Integer> {
    // Methods...
}

Is there any way to perform a query using only the month on attribute of java Date type?

I would like to create an interoperable query between relational DBMS using the Query methods supported by Spring Data framework.

Currently I solve this problem with this approach (but I believe it is not appropriate for all Databases):

@Query(value = "SELECT * FROM Cliente WHERE MONTH(nascimento) like ?1", nativeQuery = true)
Iterable<Cliente> findAllByNascimentoLike(String Month);

To think of the solution, and if necessary use the month and day?
Thanks for trying to help me.

Upvotes: 3

Views: 2999

Answers (1)

Pau
Pau

Reputation: 16106

Use JPA Query Expressions this language is independent of the database. The JPA query expresion it would be similar to your native Query (you may use function MONTH):

@Query("SELECT u FROM Cliente u WHERE MONTH(u.nascimento) = ?1")
Iterable<Cliente> findAllByNascimentoLike(Integer month);

Upvotes: 2

Related Questions