Merhaba Arkadaslar
Bu bolumde Criteria API , Where clause ‘u incelemeyece baslayacagiz Where clause kullanimini JPQL sorgularimizda incelemistik. Burada oncelikle Comparison Operator ‘lerin Criteria API de kullanimini inceleyecegiz.

=	equal()
<>	notequal()
>	greaterThan() , gt()
>=	greaterThanOrEqualTo() , ge()
<	lessThan() , lt()
<=	lessThanOrEqualTo() , le()
LIKE , NOT LIKE	like() , notLike()
BETWEEN	between()
IN	in()
ISNULL	isNull()

ParameterExpression

Onceki orneklerimizde Criteria API’de parameter olarak variable kullanmistik. Bir baska kullanim olarak ParameterExpression arabirimini kullanabiliriz.
using a ParameterExpression versus a variable in JPA Criteria API

equal()

Onceki bolumde yaptigimiz su ornegi inceleyelim ;

@Override
public String getDepartmentOfEmployeeCriteriaAPI(String employeeName) {
   CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
   CriteriaQuery<String> criteriaQuery = criteriaBuilder.createQuery(String.class);
   Root<Employee31> empRoot = criteriaQuery.from(Employee31.class);
   criteriaQuery.select(empRoot.get("department").get("name")).where(
   criteriaBuilder.equal(empRoot.get("name"), employeeName));
   TypedQuery<String> query = entityManager.createQuery(criteriaQuery);
   return query.getSingleResult();
}

Asagidaki kodu inceleyecek olursak , yeni bir ParameterExpression objesi olusturuyoruz. equal metodunda employeeName yerine empNameExpression , ParameterExpression objesini kullaniyoruz.Dikkat edecek olursak createQuery metodundan sonra setParameter metodunu kullaniyoruz.

@Override
public String getDepartmentOfEmployeeWithParameterExpression(String employeeName) {
   CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
   CriteriaQuery<String> criteriaQuery = criteriaBuilder.createQuery(String.class);
   ParameterExpression<String> empNameExpression = criteriaBuilder.parameter(String.class, "empName");
   Root<Employee31> empRoot = criteriaQuery.from(Employee31.class);
   criteriaQuery.select(empRoot.get("department").get("name")).where(
   criteriaBuilder.equal(empRoot.get("name"), empNameExpression));
   TypedQuery<String> query = entityManager.createQuery(criteriaQuery).setParameter("empName", employeeName);
   return query.getSingleResult();
 }

CriteriaApiTest.java

 // ### Where clause examples ###
 // ###Comparison Operator ###
 // equal
 System.out.println("### getDepartmentOfEmployeeWithParameterExpression ###");
 String deptName = jpqlService.getDepartmentOfEmployeeWithParameterExpression("John");
 System.out.println(deptName);

Ornegimizi calistirdigimizda;

### getDepartmentOfEmployeeWithParameterExpression ###
QA

notEqual

equal() metoduna benzer sekilde notEqual() metodunu kullanabiliriz;

//notEqual
public List<Tuple> getEmployeeNameSalaryNotEqual(int salary) {
  CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
  CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();

  ParameterExpression<Integer> empNameExpression = criteriaBuilder.parameter(Integer.class, "salaryParam");
  Root<Employee31> empRoot = criteriaQuery.from(Employee31.class);
  criteriaQuery.select(criteriaBuilder.tuple(empRoot.get("name"), empRoot.get("salary")));
  criteriaQuery.where(criteriaBuilder.notEqual(empRoot.get("salary"), empNameExpression));

  TypedQuery<Tuple> query = entityManager.createQuery(criteriaQuery).setParameter(empNameExpression, salary);
  return query.getResultList();

}

 System.out.println("### getEmployeeNameSalaryNotEqual ###");
 List<Tuple> notEquals = jpqlService.getEmployeeNameSalaryNotEqual(60000);
 for (Tuple tp : notEquals) {
    System.out.println(tp.get(0) + " " + tp.get(1));
 }
 System.out.println();

Ornegimizi calistirdigimizda salary degeri 60000 olmayan tum Employee name/salary degerleri gelecek.

### getEmployeeNameSalaryNotEqual ###
John 55000
Rob 53000
Peter 40000
Frank 41000
Rod  62000
Sue 54000
Stephanie 45000
Jennifer 52000
Sarah 59000
Marcus 35000
Joe 36000
Jack 43000

greaterThan

Bir baska ornek olarak greaterThan() , lessThan() gibi metotlari kullanabiliriz.

//greaterThan
public List<Tuple> getEmployeeNameSalaryGreaterThan(int salary) {
   CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
   CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();

   ParameterExpression<Integer> empNameExpression = criteriaBuilder.parameter(Integer.class, "salaryParam");
   Root<Employee31> empRoot = criteriaQuery.from(Employee31.class);
   criteriaQuery.select(criteriaBuilder.tuple(empRoot.get("name"), empRoot.get("salary")));
   criteriaQuery.where(criteriaBuilder.greaterThan(empRoot.get("salary"), empNameExpression));

   TypedQuery<Tuple> query = entityManager.createQuery(criteriaQuery).setParameter(empNameExpression, salary);
   return query.getResultList();

 }

 System.out.println("### getEmployeeNameSalaryGreaterThan ###");
 List<Tuple> greaterThan = jpqlService.getEmployeeNameSalaryGreaterThan(55000);
 for (Tuple tp : greaterThan) {
    System.out.println(tp.get(0) + " " + tp.get(1));
 }
 System.out.println();

Ornegimizi calistirdigimizda maasi 55000den buyuk Employee name/salary degerleri gelecek.

### getEmployeeNameSalaryGreaterThan ###
Scott 60000
Rod  62000
Sarah 59000

between

//between
public List<Tuple> getEmployeeNameSalaryBetween(int salary, int salary2) {
   CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
   CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();
   Root<Employee31> empRoot = criteriaQuery.from(Employee31.class);
 criteriaQuery.select(criteriaBuilder.tuple(empRoot.get("name"), empRoot.get("salary")));

   criteriaQuery.where(criteriaBuilder.between(empRoot.get("salary"),
   criteriaBuilder.parameter(Integer.class, "lowSalary"),
   criteriaBuilder.parameter(Integer.class, "highSalary")));

   TypedQuery<Tuple> query = entityManager.createQuery(criteriaQuery).setParameter("lowSalary", salary).setParameter("highSalary", salary2);
   return query.getResultList();

 }

 //between
 System.out.println("### getEmployeeNameSalaryBetween ###");
 List<Tuple> between = jpqlService.getEmployeeNameSalaryBetween(50000, 55000);
 for (Tuple tp : between) {
    System.out.println(tp.get(0) + " " + tp.get(1));
 }
 System.out.println();

Ornegimizi calistirdigimizda maasi 50000 ile 55000 arasinda olan (between) Employee name/salary degerleri gelecek.

### getEmployeeNameSalaryBetween ###
John 55000
Rob 53000
Sue 54000
Jennifer 52000

like

// like
public List<Tuple> getEmployeeNameSalaryLike(String like) {
   CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
   CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();
   Root<Employee31> empRoot = criteriaQuery.from(Employee31.class);
 
   criteriaQuery.select(criteriaBuilder.tuple(empRoot.get("name"), empRoot.get("salary")));
   criteriaQuery.where(criteriaBuilder.like(empRoot.get("name"), like));
   TypedQuery<Tuple> query = entityManager.createQuery(criteriaQuery);
   return query.getResultList();
}

 System.out.println("### getEmployeeNameSalaryLike ###");
 List<Tuple> like = jpqlService.getEmployeeNameSalaryLike("%ar%");
 for (Tuple tp : like) {
    System.out.println(tp.get(0) + " " + tp.get(1));
 }
 System.out.println();

Ornegimizi calistirdigimizda %ar% gecen isimli Employee name/salary kayitlari gelecektir.

### getEmployeeNameSalaryLike ###
Sarah 59000
Marcus 35000

in

public List<Tuple> getEmployeeNameSalaryIn(List<Integer> salaries) {
   CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
   CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();
   Root<Employee31> empRoot = criteriaQuery.from(Employee31.class);
 
   criteriaQuery.select(criteriaBuilder.tuple(empRoot.get("name"), empRoot.get("salary")));
   criteriaQuery.where(criteriaBuilder.in(empRoot.get("salary")).value(salaries.get(0))  .value(salaries.get(1)).value(salaries.get(2)));
   //2.yontem
   // criteriaQuery.where(empRoot.get("salary").in(salaries));

   TypedQuery<Tuple> query = entityManager.createQuery(criteriaQuery);
   return query.getResultList();
}

 System.out.println("### getEmployeeNameSalaryIn ###");
 List<Integer> salaries = new ArrayList<Integer>();
 salaries.add(53000);
 salaries.add(54000);
 salaries.add(62000);
 List<Tuple> in = jpqlService.getEmployeeNameSalaryIn(salaries);
 for (Tuple tp : in) {
    System.out.println(tp.get(0) + " " + tp.get(1));
 }
 System.out.println();

Ornegimizi calistirdigimizda maasi 53000 , 54000 ya da 62000 olan Employee name/salary bilgileri gelecektir.

### getEmployeeNameSalaryIn ###
Rob 53000
Rod  62000
Sue 54000

isNull

// isNull
@Override
public List<String> getDepartmentOfEmployeeIsNull() {
   CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
   CriteriaQuery<String> criteriaQuery = criteriaBuilder.createQuery(String.class);
   Root<Employee31> empRoot = criteriaQuery.from(Employee31.class);
   criteriaQuery.select(empRoot.get("name")).where(criteriaBuilder.isNull(empRoot.get("department")));
   TypedQuery<String> query = entityManager.createQuery(criteriaQuery);
   return query.getResultList();
 }

 System.out.println("### getDepartmentOfEmployeeIsNull ###");
 List<String> isNull = jpqlService.getDepartmentOfEmployeeIsNull();
 for (String name : isNull) {
    System.out.println(name);
 }
 System.out.println();

Ornegimizi calistirdigimizda Employee kayitlarinda Department alani null olan Employee name kayitlari gelecektir.

### getDepartmentOfEmployeeIsNull ###
Joe
Jack

Kaynak kodlar : Injavawetrust.jpa
Yazimi burada sonlandiriyorum.
Herkese Bol Javali Gunler dilerim.
Be an oracle man , import java.*;
Levent Erguder
OCP, Java SE 6 Programmer
OCE, Java EE 6 Web Component Developer