Trying to write a criteria api query.
cardholder id is suppose to be passed as a parameter and can change. I have added the entities that i have in my code.
Any help or guidance is appreciated.
select tx.id,tx.date,tx.settled,tx.settledDate,te.debit,te.amount,te.account,tx.card, acc.accountType, pl.name,mer.id, mer.name , ch.id
from cardholder ch
join txn_entry te on ch.id=te.cardholder
join txn tx on te.txn = tx.id
join merchant mer on tx.merchant=mer.id
join account acc on te.account = acc.id
join plan pl on acc.plan= pl.id
where te.cardholder= ? and tx.retcode = 00 and te.valid='Y'
order by tx.date desc;
THis is what i Have so far:
public List<CardHolderEntity> createCriteriaQuery(String cardHolderId) {
EntityManager entityManager = entityManagerFactory.createEntityManager();
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
char valid = 'Y';
String returnCode = "00";
CriteriaQuery<CardHolderEntity> cq = cb.createQuery(CardHolderEntity.class);
Root<CardHolderEntity> cardHolder = cq.from(CardHolderEntity.class);
Join<CardHolderEntity, TransactionEntryEntity> transactionEntries = cardHolder
.join(CardHolderEntity_.transactionEntryEntity);
Join<TransactionEntryEntity, AccountEntity> account = transactionEntries
.join(TransactionEntryEntity_.accountEntity);
Join<AccountEntity, PlanEntity> plans = account.join(AccountEntity_.planEntity);
Join<TransactionEntryEntity, TransactionEntity> transaction = transactionEntries
.join(TransactionEntryEntity_.transactionEntity);
Join<TransactionEntity, CardEntity> cards = transaction.join(TransactionEntity_.cardEntity);
Join<TransactionEntity, MerchantEntity> merchants = transaction.join(TransactionEntity_.merchantEntity);
Predicate p1 = cb.equal(cardHolder.get(CardHolderEntity_.id), cardHolderId);
Predicate p2 = cb.equal(transactionEntries.get(TransactionEntryEntity_.valid), valid);
Predicate p3 = cb.equal(transaction.get(TransactionEntity_.retcode), returnCode);
cq.select(cardHolder).where(cb.and(p1, p2, p3))
.orderBy(cb.desc(transactionEntries.get(TransactionEntryEntity_.id)));
TypedQuery<CardHolderEntity> qry = entityManager.createQuery(cq);
return qry.getSingleResult();
}
I am able to fetch the correct data now but the list is not ordered. This is what i see in my logs and there are two queries getting generated one has the order and the other doesnot.
Hibernate: select cardholder0_.id as id1_2_, cardholder0_.email as email2_2_, cardholder0_.firstname as firstnam3_2_, cardholder0_.issuer as issuer6_2_, cardholder0_.lastname as lastname4_2_, cardholder0_.phone as phone5_2_ from cardholder cardholder0_ inner join txn_entry transactio1_ on cardholder0_.id=transactio1_.cardholder inner join account accountent2_ on transactio1_.account=accountent2_.id inner join plan planentity3_ on accountent2_.plan=planentity3_.id inner join txn transactio4_ on transactio1_.txn=transactio4_.id inner join card cardentity5_ on transactio4_.card=cardentity5_.id inner join merchant merchanten6_ on transactio4_.merchant=merchanten6_.id where cardholder0_.id=? and transactio1_.valid=? and transactio4_.retcode=? order by transactio1_.id desc
Hibernate: select transactio0_.cardholder as cardhold6_2_6_, transactio0_.id as id1_8_6_, transactio0_.id as id1_8_5_, transactio0_.account as account5_8_5_, transactio0_.amount as amount2_8_5_, transactio0_.cardholder as cardhold6_8_5_, transactio0_.debit as debit3_8_5_, transactio0_.txn as txn7_8_5_, transactio0_.valid as valid4_8_5_, accountent1_.id as id1_0_0_, accountent1_.accounttype as accountt2_0_0_, accountent1_.active as active3_0_0_, accountent1_.enddate as enddate4_0_0_, accountent1_.plan as plan5_0_0_, planentity2_.id as id1_6_1_, planentity2_.onlineaccess as onlineac2_6_1_, planentity2_.enddate as enddate3_6_1_, planentity2_.name as name4_6_1_, transactio3_.id as id1_7_2_, transactio3_.card as card6_7_2_, transactio3_.date as date2_7_2_, transactio3_.merchant as merchant7_7_2_, transactio3_.retcode as retcode3_7_2_, transactio3_.settled as settled4_7_2_, transactio3_.settleddate as settledd5_7_2_, cardentity4_.id as id1_1_3_, cardentity4_.active as active2_1_3_, cardentity4_.cardholder as cardhold8_1_3_, cardentity4_.number as number3_1_3_, cardentity4_.createddate as createdd4_1_3_, cardentity4_.enddate as enddate5_1_3_, cardentity4_.issueddate as issuedda6_1_3_, cardentity4_.startdate as startdat7_1_3_, merchanten5_.id as id1_5_4_, merchanten5_.name as name2_5_4_ from txn_entry transactio0_ left outer join account accountent1_ on transactio0_.account=accountent1_.id left outer join plan planentity2_ on accountent1_.plan=planentity2_.id left outer join txn transactio3_ on transactio0_.txn=transactio3_.id left outer join card cardentity4_ on transactio3_.card=cardentity4_.id left outer join merchant merchanten5_ on transactio3_.merchant=merchanten5_.id where transactio0_.cardholder in (select cardholder0_.id from cardholder cardholder0_ inner join txn_entry transactio1_ on cardholder0_.id=transactio1_.cardholder inner join account accountent2_ on transactio1_.account=accountent2_.id inner join plan planentity3_ on accountent2_.plan=planentity3_.id inner join txn transactio4_ on transactio1_.txn=transactio4_.id inner join card cardentity5_ on transactio4_.card=cardentity5_.id inner join merchant merchanten6_ on transactio4_.merchant=merchanten6_.id where cardholder0_.id=? and transactio1_.valid=? and transactio4_.retcode=? )
