Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to query LocalDateTime with LocalDate?

I've got a class which contains an atttribute of java.time.LocalDateTime type.

public class MyClass{
    // ...
    private LocalDateTime fecha;
    // ...
}

I'm using Spring Data repositories. What I want to accomplish is to query entities according to a date:

@Service
public interface IRepository extends CrudRepository<MyClass, UUID> {
    // ...
    public void deleteByFecha(LocalDate fecha);
    // ...
}

But this does not work, as an exception is thrown:

org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value [2016-10-05] did not match expected type [java.time.LocalDateTime (n/a)];

So the question is how can I query MyClass in database by fecha but with a LocalDate?

EDIT Just in case somebody faces the same issue, I've come up with one solution: modify the Repository's method so that it looks as follows:

import org.springframework.transaction.annotation.Transactional;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
// ...

@Service
public interface IRepository extends CrudRepository<MyClass, UUID> {

    @Transactional
    @Modifying
    @Query("DELETE FROM MyClass mtc WHERE YEAR(mtc.fecha)=?1 AND MONTH(mtc.fecha)=?2 AND DAY(mtc.fecha)=?3")
    public void deleteByFecha(Integer year, Integer month, Integer day);

}
like image 384
russellhoff Avatar asked May 15 '17 10:05

russellhoff


2 Answers

Try this (not tested):

public interface IRepository extends CrudRepository<MyClass, UUID> {
    // ...
    default void delByFecha(LocalDate fecha) {

        deleteByFechaBetween(fecha.atStartOfDay(), fecha.plusDays(1).atStartOfDay());

    }

    void deleteByFechaBetween(LocalDateTime from, LocalDateTime to);
    // ...
}
like image 60
Cepr0 Avatar answered Oct 08 '22 06:10

Cepr0


Zombie Thread, but thought I'd throw my solution into the ring.

I had a similar issue, I ended up using a Hibernate @Formula

For example:

class MyClass {

  private LocalDateTime fecha;

  /* fecha referenced within the annotation should be the column name. 
   * So, if it's different the Java field name (i.e. fecha_dtm or something), 
   * make sure to use it.
  */
  @Formula("CAST(fecha as DATE)") 
  private LocalDate fechaDate;

}

Then your repository:

public interface IRepository extends CrudRepository<MyClass, UUID> {
  deleteByFechaDate(LocalDate fecha); // note it's FechaDate, not Fetcha
}

Try to stick to ANSI SQL compliant functions (CAST is SQL-92 compliant, so pretty widely accepted) to keep things consistent across database implementations. However, DB specific functions can be used, you'll just lose portability.

Hopefully this helps you!

like image 21
dardo Avatar answered Oct 08 '22 07:10

dardo