Error - between date not working in Jparepository

Question

between not working JPA.

Modal class:

public class Record extends AbstractTimestampEntity{

string id;
string senderId;
string reciverId;
string status;
@Column(name = "txn_Date", columnDefinition = "DATE")
Date txnDate;

}

JPA Query:

findByTxnDateBetweenAndSenderIdOrReciverIdAndStatus(Date from,Date to,String serderId,String reciverId,String status);

even I am sending from and to date filter not applying in thw query.

I tried same in mysql query here is the Query

select * from record where  txn_date between "2020-01-09" and "2020-05-09" and sender_id = "98bd543e-942d-4725-b9bc-32c99eb089ae" or reciver_id = "98bd543e-942d-4725-b9bc-32c99eb089ae" and status = "COMPLETED";

here I am getting expected result.but in JPA not getting

Can you describe your `And-Or` condition for your sql query ? — Eklavya, May 14 '20 at 05:47

score 0 · Answer 1 · answered May 14 '20 at 05:58

0

Try using @Temporal annotation :-

public class Record{

string id;
string senderId;
string reciverId;
string status;
@Temporal(TemporalType.TIMESTAMP)
Date txnDate;
}

answered May 14 '20 at 05:58

Gaurav Dhiman

953
6
11

score 0 · Answer 2 · answered May 14 '20 at 08:12

0

Despite the fact that it's an ambiguous question without enough details about error message, but try to use java.sql.Timestamp data type for your field type and emit the columnDefinition.

answered May 14 '20 at 08:12

Mohsen Msr

9
2

Eklavya · Accepted Answer · 2020-05-14T08:31:41.333

0

Combine multiple And and Or through method name is difficult and duplication of field needed. Your current method represents something else that you want.

For this condition A and (B or C) you have to query in method like this (A and B) or (A and C). For your query will be more complex. .

It's better to use JPQL using @Query annotation. Assuming you want the condition like

(txn_date and sender_id) or (reciver_id and status)

Then you can do this way

@Query("select r from Record r where ((r.txnDate BETWEEN ?1 AND ?2) and senderId = ?3) or (reciverId = ?4 and status = ?5)")
List<Record> findByTxnDateBetweenAndSenderIdOrReciverIdAndStatus(Date from,Date to,String serderId,String reciverId,String status);
   User findByEmailAddress(String emailAddress);

edited May 14 '20 at 08:31

answered May 14 '20 at 08:25

Eklavya

17,618
4
28
57

I am able to get the currect details.Here i am seeing one issue . it is loading with in time stamp range is there any solutuion – May 14 '20 at 11:11
`it is loading with in time stamp range` I don't understand this part, can you elaborate your problem – Eklavya May 14 '20 at 11:15
1) @Column(name = "transaction_date", columnDefinition = "DATE") – May 14 '20 at 11:39
If you need to cast TimeStamp to Date follow this answer https://stackoverflow.com/a/61792189/4207306 – Eklavya May 14 '20 at 11:44
When you send Date in query(to,from) try to set time also, which data you send, it will query on the date with time you send. See here how to change date object time https://stackoverflow.com/questions/10308356/how-to-obtain-the-start-time-and-end-time-of-a-day – Eklavya May 14 '20 at 12:07
i am trying to add onemore option like operator @Query("select r from Record r where ((r.txnDate BETWEEN ?1 AND ?2) and senderId = ?3) or (reciverId = ?4 and status = ?5 AND recordType like ?6)") – Jun 01 '20 at 05:17

Error - between date not working in Jparepository

3 Answers3