尝试编写一个标准api查询。
持卡人id
应该作为参数传递,并且可以更改。我在代码中添加了实体。
感谢任何帮助或指导。
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;
这就是我目前的情况:
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();
}
我现在能够获取正确的数据,但列表没有排序。这是我在日志中看到的,生成了两个查询,一个有顺序,另一个没有顺序。
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=? )
这是我最终做的事情,而不是查看持卡人表。我查看了TransactionEntry,并能够订购列表。当我走到持卡人桌旁时,我仍然没有找到任何可以订购的东西。希望这能帮助别人。
public List<TransactionEntryEntity> createCriteriaQuery(String cardHolderId, String accountId, int pageNumber,
int pageSize, Map<String, String> allRequestParams) {
EntityManager entityManager = entityManagerFactory.createEntityManager();
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<TransactionEntryEntity> cq = cb.createQuery(TransactionEntryEntity.class);
// Root<CardHolderEntity> cardHolder = cq.from(CardHolderEntity.class);
Root<TransactionEntryEntity> transactionEntry = cq.from(TransactionEntryEntity.class);
Join<TransactionEntryEntity, TransactionEntity> transaction = transactionEntry
.join(TransactionEntryEntity_.transactionEntity);
List<Predicate> predicates = new ArrayList<Predicate>();
// Default Predicates for transactions
predicates
.add(cb.equal(transactionEntry.get(TransactionEntryEntity_.cardHolderEntity).get(CardHolderEntity_.id),
cardHolderId));
predicates.add(cb.equal(transactionEntry.get(TransactionEntryEntity_.valid), 'Y'));
predicates.add(cb.equal(transaction.get(TransactionEntity_.retcode), "00"));
// For Transaction API with acountId Path Variable
if ((accountId != null) && (!accountId.trim().isEmpty())) {
Join<TransactionEntryEntity, AccountEntity> account = transactionEntry
.join(TransactionEntryEntity_.accountEntity);
predicates.add(cb.equal(account.get(AccountEntity_.id), accountId));
}
if (!allRequestParams.isEmpty()) {
String startDate = allRequestParams.get("minDate");
String endDate = allRequestParams.get("maxDate");
String merchantName = allRequestParams.get("merchantName");
String date = allRequestParams.get("date");
// Filter for Transactions with specific Date or with Start/End
// date.
if ((date != null) && !date.trim().isEmpty()) {
predicates.add(cb.between(transaction.get(TransactionEntity_.date), DateUtil.getSqlStartDate(date),
DateUtil.getSqlEndDate(date)));
} else if ((startDate != null) && (!startDate.trim().isEmpty()) && (endDate != null)
&& (!endDate.trim().isEmpty())) {
predicates.add(cb.between(transaction.get(TransactionEntity_.date), DateUtil.getSqlStartDate(startDate),
DateUtil.getSqlEndDate(endDate)));
} else if (((null == endDate) || (endDate.trim().isEmpty())) && (startDate != null)
&& (!startDate.trim().isEmpty())) {
predicates.add(cb.greaterThanOrEqualTo(transaction.get(TransactionEntity_.date),
DateUtil.getSqlStartDate(startDate)));
} else if (((null == startDate) || (startDate.trim().isEmpty())) && (endDate != null)
&& (!endDate.trim().isEmpty())) {
predicates.add(cb.lessThanOrEqualTo(transaction.get(TransactionEntity_.date),
DateUtil.getSqlEndDate(endDate)));
}
// Filter by merchant name
if (((merchantName != null) && !merchantName.trim().isEmpty())) {
Join<TransactionEntity, MerchantEntity> merchant = transaction.join(TransactionEntity_.merchantEntity);
predicates.add(
cb.like(cb.lower(merchant.get(MerchantEntity_.name)), "%" + merchantName.toLowerCase() + "%"));
}
}
cq.select(transactionEntry).where(predicates.toArray(new Predicate[] {}));
cq.orderBy(cb.desc(transaction.get(TransactionEntity_.date)));
TypedQuery<TransactionEntryEntity> qry = entityManager.createQuery(cq);
// For Paging of Transactions
if ((pageNumber > 0) && (pageSize > 0)) {
qry.setFirstResult((pageNumber - 1) * pageSize);
qry.setMaxResults(pageSize);
}
return qry.getResultList();
}
此查询用于检索一对多关系中的最后记录(请参阅SQL连接:选择一对多关系中的最后记录) 我的问题是:如何使用jpa criteria api使用subselect构建这个连接?可能吗?如果没有,可以使用jpql吗? 到目前为止我的代码:
主要内容:创建条件查询的步骤,标准API查询子句的方法标准(Criteria)API是构建实体及其持久状态查询的最常用方法之一。 它只是定义JPA查询的另一种方法。 Criteria API定义了一个独立于平台的条件查询,用Java编程语言编写。 它是在JPA 2.0中引入的。 这背后的主要目的是提供一种类型安全的方式来表达查询。 创建条件查询的步骤 要创建标准查询,请按照以下步骤操作: - 第1步: 通过在接口实例上调用方法创建接口的对象。 第2步
我正在尝试实现以下内容: 问:我如何用JPA 2标准实现这个? 我可以单独获得asCount结果,但不知道如何将其加入公司 提前感谢。 PS 1。有一个类似的线程使用HiberNate应用编程接口询问子级计数:HiberNate子级计数标准 2。另一个描述该主题的有用线程: JPA CriteriaBuilder——按一对多关系中关联实体的数量排序
问题内容: 我在Java中有2个POJO类,Answer和Collaborator,具有多对多关系。 类有一套,但没有一套。我需要在Hibernate中进行的工作是找到合作者,以获得id给出的答案。 我已经使用结果转换器使用Hibernate ()进行了此操作,但是在使用时我陷入了困境,因为我没有要提供给联接的答案列表。 问题答案: 完成了,终于… 这是代码: