JPA Criteria Query Examples

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