I am writing a Springboot JPA REST API that talks to DB2 database and should query a table containing a TIMESTAMP
field.
Using SQL, the DB2 query to filter rows between two TIMESTAMPs would be like below and it will return 1 record from my test data:
SELECT * FROM CARS WHERE SOLD_DATE BETWEEN '2020-01-01' AND '2022-01-01'
Since I am using Spring Data JPA, I have defined CarEntity
which has a java.sql.Timestamp
field
@Entity
public class CarEntity {
....
Timestamp soldDate;
...
//getters and setters
}
I am trying to retrieve data like in above SQL query.
To do so, I pass the start and end data in Postman as Long
values representing start and end date through URL like
http://localhost:8080/cars/sold/1420070400/1640995200
This endpoint hits my controller method which converts the Long
into java.sql.Date
and passes it to repository and in repository, I use @Query
annotation like below:
@Repository
public interface CarRepository extends JpaRepository<CarEntity, Timestamp>{
@Query("select c from CarEntity c where c.carModel = 'Toyota' and c.soldDate between :startDate and :endDate")
List<CarEntity> getCarsSoldBetween(Date startDate, Date endDate);
}
However, this does not work and it returns no data although I know it should return me 1 record.
But if I hardcode the start and end date like below, I get the 1 record:
@Query("select c from CarEntity c where c.carModel = 'Toyota' and c.soldDate between '2020-01-01' and '2022-01-01'")
List<CarEntity> getCarsSoldBetween(Date startDate, Date endDate);
Of course, problem with that is that I hardcoded startDate and endDate instead of using the ones passed into getCarsSoldBetween()
method.
UPDATE-1
Thanks to @HYUNJUN, I added couple of changes:
- I use
java.sql.Timestamp
in my Entity like before but my Controller, Service, and Repository usejava.util.Date
instead ofjava.sql.Date
which I was using initially. - In my application.properties added below to be able to view what parameters are passed in SQL (NOTE that this introduces significant slowdown, so use only for debugging purposes):
logging.level.org.hibernate.sql=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
Now, when I go to DB2 Bench and issue following query, I will get 2 rows back which is correct:
SELECT * FROM MYSCHEMA.CARS WHERE SOLD_TIMESTAMP BETWEEN '2021-10-04 15:00:00' AND '2021-10-20 00:00:00';
// RETURNS 2 ROWS
However, my repository query which looks like:
@Repository
public interface CarRepository extends JpaRepository<CarEntity, Timestamp>{
@Query("select c from CarEntity c where c.carModel = 'Toyota' and c.soldDate between :startDate and :endDate")
List<CarEntity> getCarsSoldBetween(Date startDate, Date endDate);
}
, returns nothing and I would expect to return 2 rows since the start and end date are same as per the logging output:
type.descriptor.sql.BasicBinder binding parameter [1] as [TIMESTAMP] - [Mon Oct 04 15:00:00 PDT 2021]
type.descriptor.sql.BasicBinder binding parameter [2] as [TIMESTAMP] - [Wed Oct 20 00:00:00 PDT 2021]
So, I am passing the same date range and would expect the same result, but that is not happening
CodePudding user response:
I wrote code almost same with your code above. But i didn't meet issue that you mentioned. I upload my code below. Why don't you compare my code with your code?
package com.springboot.springbootinternals.db2;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import java.util.concurrent.TimeUnit;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.NoArgsConstructor;
import lombok.RequiredArgsConstructor;
import lombok.ToString;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequiredArgsConstructor
public class Db2Controller {
private final CarRepository carRepository;
@GetMapping("db2/{start-date}/{end-date}")
public String db2(
@PathVariable("start-date") Long startDate,
@PathVariable("end-date") Long endDate
) {
carRepository.save(Car.builder().date(new Timestamp(1420070401L * 1000)).build());
Date start = new Date(TimeUnit.SECONDS.toMillis(startDate));
Date end = new Date(TimeUnit.SECONDS.toMillis(endDate));
List<Car> cars = carRepository.findAll(start, end);
System.out.println(">>> " cars);
return "success";
}
}
@Entity
@Builder
@NoArgsConstructor
@ToString
@AllArgsConstructor
class Car {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private Timestamp date;
}
@Repository
interface CarRepository extends JpaRepository<Car, Long> {
@Query("select c from Car c where c.date between :startDate and :endDate")
List<Car> findAll(Date startDate, Date endDate);
}
Additionally, put this option in applicaion.yml for checking correct value is passed to SQL.
logging:
level:
org:
hibernate:
SQL: DEBUG
type:
descriptor:
sql:
BasicBinder: TRACE # show_parameter_value
like this.
CodePudding user response:
You can use the following code to retrieve desired results
@Repository
public interface CarEntityRepository extends JpaRepository<CarEntity,Long> {
@Query("SELECT c FROM CarEntity c WHERE c.carModel like %:model% and c.soldDate > :start and c.soldDate < :end ")
List<CarEntity> getCarsSoldBetween(@Param("model") String model, @Param("start") Date start, @Param("end") Date end);
}