Perform a date search on a timestamp column using spring data

Question

Perform a date search on a timestamp column using spring data

Navigation

#1 by (1 votes)

2

I am trying to perform a search only for the current date in a timestamp column, ie without informing the time, but only the date.

Template:

@DateTimeFormat(pattern = "dd/MM/yyyy hh:mm")
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "data")
private Date date;

I currently use Spring Data to conduct the query.

Class of service:

public List<Order> findByDate(){
    return orderRepository.findByDate(new Date());
}

Spring Data Repository:

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    List<Order> findByDate(Date date); 
}

The problem that query is made taking into account the current timestamp with minute and second hour values and I want select to be done just by selecting the current date records.

Select with the example binding:

SELECT 
    order0_.id AS id1_1_,
    order0_.data AS data2_1_,
    order0_.pedido AS pedido3_1_,
    order0_.pagamento AS pagament4_1_,
    order0_.telefone AS telefone5_1_,
    order0_.status AS status6_1_,
    order0_.total AS total7_1_,
    order0_.usuario AS usuario8_1_
FROM
    spring_admin.pedidos_zuhause order0_
WHERE
    order0_.data = ?;

binding parameter [1] as [TIMESTAMP] - [Wed Sep 20 10:15:35 BRT 2017]

I know I could see MySQL I can do this using the date() function, but how can I do this by hibernate ?

mysql spring-data jpa

asked by anonymous 20.09.2017 / 15:19

1 answer

Create action on an html button How do I resolve struct declaration warning?

score 1 · Accepted Answer

As the column in bank also stores information that not only day / month / year you have to make the generated query consider this.

One way to do this is to search for purchases with date between the beginning and end of the day. The query in your repository looks like this:

List<Order> findByDateBetween(final Date start, final Date end);

The call in your service would look something like this:

public List<Order> findByDate(){
    final Date start = // recupera o início do dia;
    final Date end = // recupera o fim do dia;
    return orderRepository.findByDate(start, end);
}

Here are ways to get start and end dates for the day: How to get the start time and end time of a day?

As you have quoted native functions of the DBMS can be used. Spring Data provides other means for creating queries, such as @Query and QueryDSL , in addition to what JPA has already supported by default.