Code:
public List<PaymentRequestData> getTransactionsforDateRange(
PaymentType thePaymentType, Date FromDate, Date ToDate,
PaymentRequestState theTransactionStatus, int startIndex, int rec,
String branchName,String prdId) {
DetachedCriteria criteria = DetachedCriteria
.forClass(PaymentRequest.class);
if (thePaymentType != null && !thePaymentType.equals(""))
criteria.add(Restrictions.eq("paymentType", thePaymentType));
criteria.add(Restrictions.between("transactionDate", FromDate, ToDate));
if (theTransactionStatus != null && !theTransactionStatus.equals(""))
criteria.add(Restrictions.eq("status", theTransactionStatus));
if (branchName != null) {
criteria.add(Restrictions.ilike("customerAccountNumber",
branchName, MatchMode.START));
}
if (prdId != null) {
}
criteria.addOrder(Order.asc("transactionDate"));
List<PaymentRequest> datas = getHibernateTemplate().findByCriteria(
criteria, startIndex, rec);
List<PaymentRequestData> datas2 = new ArrayList<PaymentRequestData>();
for (Iterator iterator = datas.iterator(); iterator.hasNext();) {
PaymentRequest paymentRequest = (PaymentRequest) iterator.next();
PaymentRequestData paymentRequestData = PaymentRequestHelper
.getPaymentRequestData(paymentRequest);
datas2.add(paymentRequestData);
}
return datas2;
}
customerAccountNumber is a 12 digit String, digit 4 to digit 6 gives product id. I need to extract product id from customerAccountNumber, so that i will be able to get all the accounts of certain product .
The Sql version will be this way,
Code:
select * from paymentrequest where substring(customeraccountnumber,4,3)='003'
where 003 is my product id
how to write this above sql query using hibernate criteria