Merhaba Arkadaslar
Bu bolumde Criteria API de Join kullanimini inceleyecegiz.
JPQL konusunda JOIN sorgularimizi incelemistik , aslinda tek JOIN gerektiren noktalarda JPA’nin bizim icin sagladigi esnekligi kullanarak JOIN kullanmadan istedigimiz sonuca erisebiliyorduk.
Ornegin calisana ait departman ismini getirmek istersek bu ifanin SQL karsiligi ;

SELECT e.name ,d.name FROM Employee31 e JOIN Department6 d ON
e.dept_id = d.id
Where e.name='EMPNAME'

Bu SQL sorgusunu JPQL kullanarak yapmak istersek  , dikkat edecek olursak JOIN ifadesi kullanmadan da istedigimiz sonuca . (nokta) operatoru yardimiyla ulasabiliriz.

@Override
public String getDepartmentOfEmployeeJPQL(String employeeName) {
   TypedQuery<String> query = entityManager.createQuery(
   "Select e.department.name from Employee31 e WHERE e.name = :empName ", String.class   ).setParameter("empName",
   employeeName);
   return query.getSingleResult();
}

Ayni ornegi Criteria API kullanarak yapmak istersek ;
Dikkat edecek olursak select metodu icerisinde  empRoot.get(“department”).get(“name”) seklinde bir ifade mevcut , bu JPQL sorgumuzdaki e.department.name ifadesine karsilik gelmektedir.
Daha sonrasinda where ve equal metodunu kullaniyoruz.

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

Ayni ornegi Criteria API Join kullanarak yapmak istersek ;
Dikkat edecek olursak empRoot objesi uzerinden join metodunu kullandik.
Daha sonrasinda select metodunda deparmentJoin.get(“name”) diyerek deparment.name bilgisini donmekteyiz.
Son olarak yine where ve equal metodunu kullanarak where sartini sorgumuza ekliyoruz.

@Override
public String getDepartmentOfEmployeeCriteriaJoin(String employeeName) {
   CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
   CriteriaQuery<String> criteriaQuery = criteriaBuilder.createQuery(String.class);
   Root<Employee31> empRoot = criteriaQuery.from(Employee31.class);
   Join<Employee31, Department6> departmentJoin = empRoot.join("department");
   criteriaQuery.select(departmentJoin.get("name")).where(criteriaBuilder.equal(empRoot.get("name"), employeeName));

   TypedQuery<String> query = entityManager.createQuery(criteriaQuery);
   return query.getSingleResult();
 }

CriteriaApiTest.java

System.out.println("### getDepartmentOfEmployeeJPQL ###");
String departmentOfEmployeeJPQL = jpqlService.getDepartmentOfEmployeeJPQL("Frank");
System.out.println(departmentOfEmployeeJPQL);
System.out.println();

System.out.println("### getDepartmentOfEmployeeCriteriaAPI ###\n");
String departmentOfEmployeeCriteriaAPI = jpqlService.getDepartmentOfEmployeeCriteriaAPI("Frank");
System.out.println(departmentOfEmployeeCriteriaAPI);
System.out.println();

System.out.println("### getDepartmentOfEmployeeCriteriaJoin ###\n");
String departmentOfEmployeeJoin = jpqlService.getDepartmentOfEmployeeCriteriaJoin("Frank");
System.out.println(departmentOfEmployeeJoin);
System.out.println();

Ornegimizi calistirdigimizda;

### getDepartmentOfEmployeeJPQL ###
Engineering

### getDepartmentOfEmployeeCriteriaAPI ###

Engineering

### getDepartmentOfEmployeeCriteriaJoin ###

Engineering

Bir baska SQL sorgusu olarak ; ornegin departman ismi Engineering olan sehir adresi San Jose olan calisanlari getirmek istersek;

SELECT e.name from Employee31 e
JOIN Address3 a ON e.address_id= a.id
JOIN DEPARTMENT6 d ON e.DEPT_ID = d.id
WHERE d.NAME='Engineering'
and a.city='San Jose';

Kodumuzu inceleyecek olursak 2 tane Join objesi olusturuyoruz. Burada dikkat etmemiz gereken nokta empRoot objesi uzerinden 2 defa join metodunu cagirdigimizda burada onceki degerler temizlenmeyecektir , burada uzerine eklenerek devam etme mantigi soz konusudur.
Birinci join islemi sonrasinda empRoot.getJoins().size() 1 olacak , ikinci defa join islemi yaptigimizda size 2 olarak gorebiliriz.
Daha sonrasinda where metodu icerisinde criteriaBuilder.equal metodunu 2 defa kullaniyoruz. Varsayilan olarak bu sartlari kendi arasinda AND yapacaktir. Sonraki yazilarda WHERE clause u daha detayli olarak inceleyecegiz.

@Override
   public List<String> getNameOfEmployeesByCity(String departmentName, String city) {
   CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
   CriteriaQuery<String> criteriaQuery = criteriaBuilder.createQuery(String.class);

   Root<Employee31> empRoot = criteriaQuery.from(Employee31.class);
   Join<Employee31, Department6> departmentJoin = empRoot.join("department");
   System.out.println(empRoot.getJoins().size());

   Join<Employee31, Address3> addressJoin = empRoot.join("address");
   System.out.println(empRoot.getJoins().size());

   criteriaQuery.select(empRoot.get("name")).where(
   criteriaBuilder.equal(departmentJoin.get("name"), departmentName),
   criteriaBuilder.equal(addressJoin.get("city"), city));

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

CriteriaAPITest.java

System.out.println("### getNameOfEmployeesByCity ###\n");
List<String> nameOfEmployeesByCity = jpqlService.getNameOfEmployeesByCity("Engineering", "San Jose");
for (String name : nameOfEmployeesByCity) {
  System.out.println(name);
}
System.out.println();

Ornegimizi calistirdigimizda ;

### getNameOfEmployeesByCity ###

1
2
Scott
Rod 

Outer Join

Outer Join’i uygulamak son derece kolaydir.Bunun icin join metodunda JoinType kullanabiliriz.
Oncelikle SQL sorgumuzu yazalim , Employee.name ve Project.name ifadelerini getiriyoruz. Bu durumda 3 tane JOIN yapmamiz gerekli.
LEFT JOIN kullandigimiz icin Projesi olmayan Employee kayitlari da gelecektir.

SELECT e.name , p.name
FROM Employee31 e
LEFT JOIN EMPLOYEE31_PROJECT4 ep
ON e.ID = ep.EMPLOYEES_ID
LEFT JOIN Project4 p 
On ep.PROJECTS_ID= p.id;

@Override
public List<Tuple> getNameAndProjectName() {
   CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
   CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();
 
   Root<Employee31> empRoot = criteriaQuery.from(Employee31.class);
   Join<Employee31, Project4> departmentJoin = empRoot.join("projects", JoinType.LEFT;
   criteriaQuery.multiselect(empRoot.get("name"), departmentJoin.get("name"));
   TypedQuery<Tuple> query = entityManager.createQuery(criteriaQuery);
   return query.getResultList();
 }

System.out.println("### getNameAndProjectName ###\n");
List<Tuple> nameAndProjectName = jpqlService.getNameAndProjectName();
for (Tuple tp : nameAndProjectName) {
   System.out.println(tp.get(0) + " " + tp.get(1));
}
System.out.println();

Ornegimizi calistirdigimizda , sonuclara dikkat edelim , Project.name karsiligi null olan Employee kayitlari da gelmektedir.

### getNameAndProjectName ###

John Design Release2
Rob Release1
Rob Test Release2
Peter Design Release2
Peter Release1
Peter Test Release2
Frank Design Release2
Scott Release1
Scott Test Release2
Rod  Design Release2
Rod  Release1
Sue Test Release2
Stephanie Design Release2
Stephanie Release1
Jennifer Design Release2
Jennifer Test Release2
Sarah Design Release2
Sarah Release1
Sarah Test Release2
Joe null
Jack null
Marcus null

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