CODEWITHSUNDEEP
sqldatejpaspring-data-jpah2
Martin Larizzate
Martin Larizzate

Reputation: 910

JPA Native Query Date compare throws

I'm using Spring boot + jpa + H2 for this project.

I need to get the rows that are overlaped with the given range, so I builded the query that checks in the seclect if the dateFrom and dateTo fields are in the received range but i'm receiveing this error.

I'm very confused about this error, because i'm handling only Date Objoects in Java and I stored the dates just as Dates in the database too. But in the console looks like it's trying to handle it as a VARCHAR.

This is My Model Class:

package com.campsite.reservations.model;
imports

@Entity
@Table(name = "reservations")
public class Reservation {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @Column(name = "reservation_code")
    private String reservationCode;

    @Column(name = "user_id")
    private long userId;

    @ManyToOne(fetch=FetchType.LAZY)
    @JoinColumn(name="place_id")
    private Place place;

    @Column(name = "date_from")
    private Date dateFrom;

    @Column(name = "date_to")
    private Date dateTo;

GETTERS Y SETTER`S

}

And this is my repository Query:

 @Query(
                value = "select * FROM Reservations res "+
                "where  ((res.date_from >= ?1 || res.date_from <= ?2) "+
                 "|| (res.date_to >= ?1 || res.date_to <= ?2))",
                nativeQuery = true)
        List<Reservation> findOverlapings(Date dateFrom, Date dateTo);

I'm using Spring boot so my console looks a bit poor of information. Console Messages:

2018-11-15 02:44:19.227 DEBUG 9201 --- [nio-8090-exec-1] m.m.a.RequestResponseBodyMethodProcessor : Read [class com.campsite.reservations.model.Reservation] as "application/json;charset=UTF-8" with [org.springframework.http.converter.json.MappingJackson2HttpMessageConverter@5b5b53c6]
2018-11-15 02:44:24.271  INFO 9201 --- [nio-8090-exec-1] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
Hibernate: select reservatio0_.id as id1_1_, reservatio0_.date_from as date_fro2_1_, reservatio0_.date_to as date_to3_1_, reservatio0_.place_id as place_id6_1_, reservatio0_.reservation_code as reservat4_1_, reservatio0_.user_id as user_id5_1_ from reservations reservatio0_ left outer join places place1_ on reservatio0_.place_id=place1_.id where place1_.id=? and reservatio0_.date_from=? and reservatio0_.date_to=?
Hibernate: select * FROM Reservations res where  ((res.date_from >= ? || res.date_from <= ?) || (res.date_to >= ? || res.date_to <= ?))
2018-11-15 02:44:27.595  WARN 9201 --- [nio-8090-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 22007, SQLState: 22007
2018-11-15 02:44:27.598 ERROR 9201 --- [nio-8090-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : Cannot parse "TIMESTAMP" constant "2018-11-01 21:00:00.02018-10-31 21:00:00.0"; SQL statement:
select * FROM Reservations res where  ((res.date_from >= ? || res.date_from <= ?) || (res.date_to >= ? || res.date_to <= ?)) [22007-196]
2018-11-15 07:51:26.564 DEBUG 11342 --- [nio-8090-exec-1] o.s.w.s.m.m.a.HttpEntityMethodProcessor  : Written [CampsiteErrorResponse{status=500, message='could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not extract ResultSet', errors=[could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not extract ResultSet]}] as "application/json" using [org.springframework.http.converter.json.MappingJackson2HttpMessageConverter@2b2b7e3c]

Upvotes: 1

Views: 4541

Answers (1)

Ara Kokeba
Ara Kokeba

Reputation: 199

Look at this in Reservation class: 

@Column(name = "date_from")
private Date dateFrom;

@Column(name = "date_to")
private Date dateTo;

First of all, you are missing to specify @Temporal annotation in your entity class. Change to: 

@Column(name = "date_from")
@Temporal(TemporalType.DATE)
private Date dateFrom;

@Column(name = "date_to")
@Temporal(TemporalType.DATE)
private Date dateTo;

If you use spring data repository, I would recommend you to take advantage of JpaRepository and use a query method like this:

Collection<Reservation> findByDateFromBetweenOrDateToBetweenOrder(Date dateFrom, Date dateTo);

Hope this helps.

Upvotes: 1

Related Questions