JPA Criteria Query Examples

Get All Accounts

public Collection<DWAccount> getListAccount() {
          CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();
	   CriteriaQuery<DWAccount> criteriaBuilderQuery = criteriaBuilder.createQuery(DWAccount.class);
	   CriteriaQuery<DWAccount> accountCriteriaQuery =;
	   return this.entityManager.createQuery(accountCriteriaQuery).setMaxResults(200).getResultList();

Select * from Account where SSN=?

public DWAccount findAccountBySSN(DWAccount accountWithMatchingSSN) {
		CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
	    CriteriaQuery<DWAccount> criteriaQuery = criteriaBuilder.createQuery(DWAccount.class);
	    Root<DWAccount> root = criteriaQuery.from(DWAccount.class);;
	    criteriaQuery.where(criteriaBuilder.equal(root.get("ssn"), accountWithMatchingSSN.getSsn()));
	    List<DWAccount> retrievedMatchedSSNAccounts = this.entityManager.createQuery(criteriaQuery).getResultList();
	    if (!retrievedMatchedSSNAccounts.isEmpty())
	    	return retrievedMatchedSSNAccounts.get(0);
	    	return null;

Select * from DWLoan where aid=? and chngStatus is not null

	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")));;
			for (DWLoan dwLoan : this.entityManager.createQuery(criteriaQuery).getResultList()){

Count number of rows in Account Table

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"))

List<Tuple> result = em.createQuery(cq).getResultList();

One Comment on “JPA Criteria Query Examples

  1. Can you supply and example on how you would create a query with multiple groups, for example:
    select * from Account where (SSN=? and ROLE=?) OR (LAST_NAME=? and ROLE=?)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: