How to convert mysql query to jpa named query

Question

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

Rafik BELDI · Accepted Answer · 2016-04-11 16:28:32Z

1

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();
}

answered Apr 11, 2016 at 16:28

Rafik BELDI

4,1684 gold badges26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to convert mysql query to jpa named query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related