CODEWITHSUNDEEP
oracle-databasehibernatespring-data
arun_kk
arun_kk

Reputation: 392

TIMESTAMP in NamedNativeQuery using Spring Data JPA

The scenario is to fetch values from OracleDB where one of the search input is DateTime in the format "07/11/2017 10:12:16 AM" (which is a string) and the DB Column is TIMESTAMP.

The Data in the column is saved in the format "07/11/2017 10:12:16 AM" but it is a TIMESTAMP.

while querying from the Oracle DB, it is possible to convert the search input to the appropriate TIMESTAMP using TO_TIMESTAMP function

Example select * from Table where SI_ID='12345'and COLUMN >= TO_TIMESTAMP ('07/11/2017 10:12:16 AM', 'mm/DD/YYYY HH:MI:SS AM';

I need to achieve the same in Java using Spring Data JPA and NamedQuery. The NativeNamedQuery is called from the JPA repository is as follows

@Query(name = "Sample.findRecordByIdAndTime", nativeQuery = true)
 Sample findByIdAndTime (@Param("id") Long id, @Param("timestamp") String timestamp);

But how to convert the string to TIMESTAMP in the Named query called from JPA repository which is given below:

@NamedNativeQuery(name="Sample.findRecordByIdAndTime", query="select * from TABLE where SI_ID= ? and TS_COLUMN >= TO_TIMESTAMP(?, 'mm/DD/YYYY HH:MI:SS AM')", resultClass = Sample.class)

Any help will be appreciated.

Upvotes: 0

Views: 3081

Answers (1)

xyz
xyz

Reputation: 5407

your query should work (as it native query), just add bind params : :id and :timestamp

@NamedNativeQuery(name="Sample.findRecordByIdAndTime", 
                 query="select * from TABLE where SI_ID= :id 
                    and TS_COLUMN >= TO_TIMESTAMP(:timestamp , 'mm/DD/YYYY HH:MI:SS AM')", 
                 resultClass = Sample.class)

Upvotes: 1

Related Questions