Spring Data JPA Fetching data based on a timestamp column

Question

How do i retrieve data from a table using spring data JPA on a column that has a timestamp type. Database is mySQL

Column Definition

last_update timestamp

Domain Object

public class Film implements Serializable {
    @Column(name="last_update")
    private Timestamp lastUpdate;

    public Timestamp getLastUpdate() {
        return this.lastUpdate;
    }

    public void setLastUpdate(Timestamp lastUpdate) {
        this.lastUpdate = lastUpdate;
    }
}

Spring Data JPA Code

@Repository                                                        
public interface FilmRepository extends CrudRepository<Film, Integer> 
{
    Film findByLastUpdate(Date date);
}

Service Class

@Override
public Film readFilmbyDate(){
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    Film film=null;
    try {
        film= filmRepository.findByLastUpdate(sdf.parse(sdf.format(new Date())));
    } catch (ParseException e) {
        e.printStackTrace();
    }
    return film;
}

This always returns null as the DB column has a timestamp(2017-05-07 06:45:19), basically I want to return all records that have the last updated date to be today / current date.

Jens Schauder · Accepted Answer · 2017-05-07 05:54:20Z

1

Change your method to findByLastUpdateBetween(Date date, Date after) and pass in the beginning and the end of the date you are looking for. If you are on java 8 you can add a default method to create the dates from one input date and call findLastUpdateBetween, otherwise, you can use a custom implementation for that.

answered May 7, 2017 at 5:54

Jens Schauder

82.6k35 gold badges199 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

onlinejava Over a year ago

thanks Jens, it worked i assume this is the only way for timestamp columns, i cannot use the equal directly

Jens Schauder Over a year ago

With a timestamp, you have to use a range query. If read performance is critical you could introduce an additional date column, possibly supported by a functional index (don't know if MySQL has something like this)

Collectives™ on Stack Overflow

Spring Data JPA Fetching data based on a timestamp column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related