JPA Criteria Query Examples

本文介绍了使用Java进行数据库操作的方法,包括获取所有账户信息、通过社会安全号码(SSN)查找账户、更新贷款状态及查询账户总数等核心操作。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

  • Get All Accounts
@Override
public Collection<DWAccount> getListAccount() {
          CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();
       CriteriaQuery<DWAccount> criteriaBuilderQuery = criteriaBuilder.createQuery(DWAccount.class);
       CriteriaQuery<DWAccount> accountCriteriaQuery = criteriaBuilderQuery.select(criteriaBuilderQuery.from(DWAccount.class));
       return this.entityManager.createQuery(accountCriteriaQuery).setMaxResults(200).getResultList();
    }
  • Select * from Account where SSN=?
@Override
public DWAccount findAccountBySSN(DWAccount accountWithMatchingSSN) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<DWAccount> criteriaQuery = criteriaBuilder.createQuery(DWAccount.class);
        Root<DWAccount> root = criteriaQuery.from(DWAccount.class);
        criteriaQuery.select(root);
        criteriaQuery.where(criteriaBuilder.equal(root.get("ssn"), accountWithMatchingSSN.getSsn()));
        List<DWAccount> retrievedMatchedSSNAccounts = this.entityManager.createQuery(criteriaQuery).getResultList();
        if (!retrievedMatchedSSNAccounts.isEmpty())
            return retrievedMatchedSSNAccounts.get(0);
        else
            return null;
}
  • Select * from DWLoan where aid=? and chngStatus is not null
@Override
@Transactional
public void findAndUpdateLoanByAccountId(Map<DWAccount, UpsertResult> accountsLoadedResult) {
    for (DWAccount dwAccounts : accountsLoadedResult.keySet()){
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<DWLoan> criteriaQuery = criteriaBuilder.createQuery(DWLoan.class);
        Root<DWLoan> root = criteriaQuery.from(DWLoan.class);
        Predicate p = criteriaBuilder.conjunction();
        p = criteriaBuilder.and(criteriaBuilder.equal(root.get("aid"), dwAccounts.getId()), criteriaBuilder.isNotNull(root.get("chngStatus")));
        criteriaQuery.select(root);
        criteriaQuery.where(p);
        for (DWLoan dwLoan : this.entityManager.createQuery(criteriaQuery).getResultList()){
            dwLoan.setSfAmid(accountsLoadedResult.get(dwAccounts).getId());
            this.entityManager.merge(dwLoan);
        }
    }
}
  • Count number of rows in Account Table
@Override
public int getAccountTotal() {
        Number result = (Number) this.entityManager.createNativeQuery("Select count(id) from DW$SF$ACCOUNT").getSingleResult();
        return result.intValue();
}
  • SELECT id,status,created_at from transactions where status=’1′ and currency=’USD’ and appId=’123′ order by id
Map<String, Object> params = ...;
CriteriaBuilder cb = em.getCriteriaBuilder();           
CriteriaQuery<Tuple> cq = cb.createTupleQuery();     
Root<Transaction> r = cq.from(Transaction.class);

Predicate p= cb.conjunction();
for (Map.Entry<String, Object> param: params.entrySet())
    p = cb.and(p, cb.equal(r.get(param.getKey()), param.getValue()));

cq.multiselect(r.get("id"), r.get("status"), r.get("created_at"))
    .where(p)
    .orderBy(cb.asc(r.get("id")));

List<Tuple> result = em.createQuery(cq).getResultList();
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值