JPA Criteria Query Examples

来源：互联网发布：淘宝商品规格怎么填写编辑：程序博客网时间：2024/05/22 22:05

Get All Accounts

@Overridepublic 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=?

@Overridepublic 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@Transactionalpublic 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

@Overridepublic 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();

1 0