CODEWITHSUNDEEP
springpostgresqlspring-bootspring-data-jpa
Supriya Shelar
Supriya Shelar

Reputation: 121

JPA query to work with daterange of postgresql

Actual DB table:

 Column |   Type    | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
 room   | text      |           |          |
 during | daterange |           |          |

Working DB query:

select * from room_reservation where during && daterange('[2020-10-15,2020-11-14)');

Entity Mapping:

@Column(name = "during", columnDefinition = "daterange")
private Range<Date> during;

Jpa Repository:

@Query(value = "select r.room from Room_Occupancy r where r.during && daterange('[?1, ?2)')", nativeQuery = true)
    List<Long> findOccupiedRooms(@Param("d1") Date d1, @Param("d2") Date d2);

Exception:

Caused by: org.postgresql.util.PSQLException: ERROR: invalid input syntax for type date: "?1"
  Position: 65

How can I write same query in JPA?

Upvotes: 2

Views: 1763

Answers (4)

Supriya Shelar
Supriya Shelar

Reputation: 121

 @Query(value = "select r.room from Room_Occupancy r where r.during && daterange(''||'[' || :d1 ||',' || :d2 ||')' || '')", nativeQuery = true)
        List<Long> findOccupiedRooms(@Param("d1") Date d1, @Param("d2") Date d2);

This worked

Upvotes: 0

Eklavya
Eklavya

Reputation: 18410

Here, '[?1, ?2)' inside string literal parameter can't be replaced. You can use || to concat as string where the parameters can be replaced.

@Query(value = "select r.room from Room_Occupancy r where r.during &&"
      + " daterange('[' || :d1 || ',' || :d2 || ')')", nativeQuery = true)
  List<Long> findOccupiedRooms(@Param("d1") Date d1, @Param("d2") Date d2);

Upvotes: 2

Gaurav Raghav
Gaurav Raghav

Reputation: 187

Try this code:-

    @Query(value = "select r.room from Room_Occupancy r where r.during >= ?1 and r.during < ?2", nativeQuery = true)
    List<String> findOccupiedRooms(Date d1,Date d2); //d1 < d2

Upvotes: 0

Simon Martinelli
Simon Martinelli

Reputation: 36103

As it's a native query use ? as parameter placeholders:

@Query(value = "select r.room from Room_Occupancy r where r.during && daterange('[?, ?)')", nativeQuery = true)
List<Long> findOccupiedRooms(@Param("d1") Date d1, @Param("d2") Date d2);

Upvotes: 0

Related Questions