CODEWITHSUNDEEP
javamysqlhibernatejpa
subbu royal
subbu royal

Reputation: 624

How to convert mysql query to jpa named query

I have one Order table

order_id | customer_id | submit_date | order_number | ....

currently I am fetching all records based on customer_id by using jpa named query 

public List<Order> readOrders(Long customerId){
 Query query = em.createNamedQuery("select order from com.mycompany.Order where order.customer.id = :customerId");
 query.setParameter("customerId", customerId);
 return query.getResultLis();
}

above query is working fine and it is fetching all records from db, but my task is to fetch the records only from past 15 days for this I know the sql query

select * from Oder where SUBMI_DATE between date_sub(sydate(), interval 15 day) and sydate() and customer_id = 101;

above sql query is working fine but my problem I don't know how to convert above sql query to jpa named query can anyone please help on this

Upvotes: 1

Views: 800

Answers (1)

Rafik BELDI
Rafik BELDI

Reputation: 4158

Assuming that your object equivalent of sql SUBMI_DATE is submitDate

public List<Order> readOrders(Long customerId){
  Query query = em.createNamedQuery("select order from com.mycompany.Order where order.customer.id = :customerId and order.submitDate > :specificDate");
  // I use Joda time you can use java.util.Date directly
  DateTime specificDate= new DateTime().minusDays(15);

  query.setParameter("customerId", customerId);
  query.setParameter("specificDate", specificDate.toDate(),TemporalType.DATE);

 return query.getResultLis();
}

Upvotes: 1

Related Questions