Merhaba Arkadaslar
Onceki bolumde JPQL sorgularimizda JOIN kullanimini inceleyecegiz.

JOINS

join sorgulari JPQL ve SQL icin mantiksal olarak aynidir.

Employee30 ve Phone2 Entity siniflarimiz arasinda @OneToMany iliski olacak;
@OneToMany annotation’i ekstra tablo olmadan da kullanabiliriz. Bunun icin @JoinTable annotation yerine @JoinColumn annotation’i kullanabiliriz.

Employee30.java

@Entity
public class Employee30 {
	
	@OneToMany
	@JoinColumn(name="EMP_ID")
	private List<Phone2> phones = new ArrayList<Phone2>();

	...

	public void addPhoneList(Phone2 phone){
		if(!getPhones().contains(phone)){
			getPhones().add(phone);
		}
	}
	...

}

Phone2.java

@Entity
public class Phone2 {
	@Id
	@TableGenerator(name = "PHN_GEN")
	@GeneratedValue(generator = "PHN_GEN")
	private int id;
	private String no;
	@Enumerated(EnumType.STRING)
	private PhoneType phoneType;

...
}

Basit bir Join ifadesi iceren sorgu inceleyelim ;

SELECT p FROM Employee30 e JOIN e.phones p

Klasik SQL sorgusunu dusundugumuzde 2 tablo icin JOIN islemi gerceklestirdigimizde ON keyword’unu kullaniriz. JPQL icin buna gerek yoktur.

JPQL sorgumuzun SQL sorgu karsiligi ;

select p.id , p.no , p.phoneType
from Employee30 e join Phone2 p on e.ID = p.emp_id;

Yukaridaki JPQL sorgumuzu su sekilde de yazabiliriz ;

SELECT e.phones FROM Employee30 e where e.phones IS NOT EMPTY;

JoinTest.java

...
System.out.println("JOIN_EMPLOYEE_AND_PHONE");
List<Phone2> phoneList = jpqlService.executeResultListQuery(JOIN_EMPLOYEE_AND_PHONE, Phone2.class);

for (Phone2 phone : phoneList) {
	System.out.println(phone);
}

System.out.println("SELECT_ALL_PHONES_FROM_EMPLOYEE");
List<Phone2> phoneListFromEmployee = jpqlService.executeResultListQuery(SELECT_ALL_PHONES_FROM_EMPLOYEE, Phone2.class);

for (Phone2 phone : phoneListFromEmployee) {
	System.out.println(phone);
}
...

Ornegimizi calistirdigimizda ;

JOIN_EMPLOYEE_AND_PHONE
Phone2 [id=1, no=(212)555-1234, phoneType=OFFICE]
Phone2 [id=2, no=(315)555-6253, phoneType=HOME]
Phone2 [id=3, no=(212)555-9843, phoneType=OFFICE]
Phone2 [id=4, no=(315)555-6253, phoneType=OFFICE]
Phone2 [id=5, no=(516)555-9837, phoneType=MOBILE]
Phone2 [id=6, no=(650)555-7583, phoneType=OFFICE]
Phone2 [id=7, no=(650)555-5345, phoneType=HOME]
Phone2 [id=8, no=(650)555-4885, phoneType=MOBILE]
Phone2 [id=9, no=(516)555-9837, phoneType=OFFICE]
Phone2 [id=10, no=(650)555-4759, phoneType=OFFICE]
Phone2 [id=11, no=(585)555-0693, phoneType=HOME]
Phone2 [id=12, no=(516)555-9837, phoneType=MOBILE]
Phone2 [id=13, no=(650)555-2346, phoneType=HOME]
Phone2 [id=14, no=(650)555-9874, phoneType=MOBILE]
Phone2 [id=15, no=(585)555-1457, phoneType=MOBILE]
SELECT_ALL_PHONES_FROM_EMPLOYEE
Phone2 [id=1, no=(212)555-1234, phoneType=OFFICE]
Phone2 [id=2, no=(315)555-6253, phoneType=HOME]
Phone2 [id=3, no=(212)555-9843, phoneType=OFFICE]
Phone2 [id=4, no=(315)555-6253, phoneType=OFFICE]
Phone2 [id=5, no=(516)555-9837, phoneType=MOBILE]
Phone2 [id=6, no=(650)555-7583, phoneType=OFFICE]
Phone2 [id=7, no=(650)555-5345, phoneType=HOME]
Phone2 [id=8, no=(650)555-4885, phoneType=MOBILE]
Phone2 [id=9, no=(516)555-9837, phoneType=OFFICE]
Phone2 [id=10, no=(650)555-4759, phoneType=OFFICE]
Phone2 [id=11, no=(585)555-0693, phoneType=HOME]
Phone2 [id=12, no=(516)555-9837, phoneType=MOBILE]
Phone2 [id=13, no=(650)555-2346, phoneType=HOME]
Phone2 [id=14, no=(650)555-9874, phoneType=MOBILE]
Phone2 [id=15, no=(585)555-1457, phoneType=MOBILE]

Bir baska ornek olarak Department5 Employee30 Entity’lerimiz arasinda @ManyToOne iliski olsun ;

Employee30.java

...
@ManyToOne
@JoinColumn(name = "DEPT_ID")
private Department5 department;
...

Department5.java

@Entity
public class Department5 {

	@Id
	@TableGenerator(name="Dep_Gen")
	@GeneratedValue(generator="Dep_Gen")
	private int id;
	private String name;

	@OneToMany(mappedBy = "department")
	private Collection<Employee30> employees;

..
}

Bir Department’da birden fazla Employee olabilir bu nedenle duplicate olarak sonuc gelecektir. Bunu engellemek icin Distinct keyword’unu kullanabiliriz.

Select Distinct d FROM Employee30 e JOIN e.department d"

Select Distinct e.department from Employee30 e IS NOT NULL"

JPQL sorgumuza karsilik olarak SQL sorgumuz ;

select distinct department5.id , department5.name from Employee30 join Department5 on Employee30.dept_id = Department5.id;

JoinTest.java

...
System.out.println("JOIN_SELECT_EMPLOYEE_AND_DEPARTMENT");
List<Department5> departmentJoinList = jpqlService.executeResultListQuery(JOIN_SELECT_EMPLOYEE_AND_DEPARTMENT,
		Department5.class);

for (Department5 dept : departmentJoinList) {
	System.out.println(dept);
}

System.out.println("SELECT_ALL_DEPARTMENTS_FROM_EMPLOYEE");
List<Department5> departmentFromEmployeeList = jpqlService.executeResultListQuery(
		SELECT_ALL_DEPARTMENTS_FROM_EMPLOYEE, Department5.class);

for (Department5 dept : departmentFromEmployeeList) {
	System.out.println(dept);
}
...

Ornegimizi calistirdigimizda ;

...
JOIN_SELECT_EMPLOYEE_AND_DEPARTMENT
Department5 [id=1, name=Engineering]
Department5 [id=3, name=Accounting]
Department5 [id=2, name=QA]
SELECT_ALL_DEPARTMENTS_FROM_EMPLOYEE
Department5 [id=1, name=Engineering]
Department5 [id=3, name=Accounting]
Department5 [id=2, name=QA]
...

JPQL sorgusu ile JOIN islemlerini yapmak bize pratiklik saglayacaktir. Bunu daha iyi anlayabilmek icin baska bir ornek yapalim.

Select Distinct e.department from Employee30 e
join Project3 p where p.name='Release1' and e.address.state='CA'

Yukaridaki JPQL sorgumuzun SQL karsiligi;

select distinct d.id , d.name
from Project3 p JOIN Employee30_Project3  emp_prj on p.id = emp_prj.PROJECTS_ID
JOIN Employee30 e ON e.id = emp_prj.EMPLOYEES_ID
JOIN Department5 d ON d.id = e.dept_id
JOIN Address2 a ON a.id = e.address_id
where p.name='Release1' and a.state='CA';

Dikkat edecek olursak burada 4 tane JOIN ifadesi yazmamiz gerekli.

JoinTest.java

System.out.println("JOIN_DEPARTMENT_PROJECT_ADDRESS");
List<Department5> departmentProjectAddress = jpqlService.executeResultListQuery(JOIN_DEPARTMENT_PROJECT_ADDRESS,
		Department5.class);

for (Department5 dept : departmentProjectAddress) {
	System.out.println(dept);
}

Ornegimizi calistirdigimizda ;

JOIN_DEPARTMENT_PROJECT_ADDRESS
Department5 [id=1, name=Engineering]

Bir baska ornek olarak ;

SELECT e.name, p  FROM Employee30 e JOIN e.phones p

JPQL sorgumuzun SQL karsiligi ;

select e.name , p.id , p.no , p.phoneType
from Employee30 e join Phone2 p on e.ID = p.emp_id;

JoinTest.java

System.out.println("MORE_JOIN");
List<Object[]>oreJoinList = jpqlService.executeResultListQuery(MORE_JOIN, Object[].class);

for (Object[] element : moreJoinList) {
	System.out.println(element[0] + " " + element[1]);
}

Ornegimizi calistirdigimizda ;

MORE_JOIN
John Phone2 [id=1, no=(212)555-1234, phoneType=OFFICE]
John Phone2 [id=2, no=(315)555-6253, phoneType=HOME]
Rob Phone2 [id=3, no=(212)555-9843, phoneType=OFFICE]
Peter Phone2 [id=4, no=(315)555-6253, phoneType=OFFICE]
Peter Phone2 [id=5, no=(516)555-9837, phoneType=MOBILE]
Frank Phone2 [id=6, no=(650)555-7583, phoneType=OFFICE]
Frank Phone2 [id=7, no=(650)555-5345, phoneType=HOME]
Scott Phone2 [id=8, no=(650)555-4885, phoneType=MOBILE]
Rod  Phone2 [id=9, no=(516)555-9837, phoneType=OFFICE]
Sue Phone2 [id=10, no=(650)555-4759, phoneType=OFFICE]
Stephanie Phone2 [id=11, no=(585)555-0693, phoneType=HOME]
Jennifer Phone2 [id=12, no=(516)555-9837, phoneType=MOBILE]
Jennifer Phone2 [id=13, no=(650)555-2346, phoneType=HOME]
Sarah Phone2 [id=14, no=(650)555-9874, phoneType=MOBILE]
Joe Phone2 [id=15, no=(585)555-1457, phoneType=MOBILE]

Outer Join

Simdiye kadarki orneklerimiz Inner Join diye tabir ettigimiz Join sorgulariydi.
Bildigimiz uzere Outer Join yaklasimi ;

• Left Outer Join
• Right Outer Join
• Full Outer Join olmak uzere uctanedir.

Outer Join yapisinda eslesmeyen veriler de gelir ve eslesmeyen verinin karsisinda null degeri olur.
JPQL Left Outer Join’i direkt olarak desteklemektedir. Outer Join’in farkini gorebilmek icin bir kactane daha Department ekliyorum ve herhangi bir Employee ile iliskilendirmiyorum.
Yine benzer sekilde employee12 ve employee13 icin yorum satiri haline getiriyorum ;

   //department3.addEmployee(employee12);
   //department3.addEmployee(employee13);

Sorgumuzu inceleyelim ;

SELECT e , d  FROM Employee30 e LEFT JOIN e.department d"

JPQL sorgumuzun SQL karsiligi ;

SELECT *
FROM employee30 e LEFT OUTER JOIN department5 d
ON (d.id = e.dept_id);

JPQL ,LEFT JOIN i desteklemektedir. Burada Employee30 ve Department5 entity’lerini getiriyoruz.
LEFT JOIN yaptigimiz icin d (Department5) NULL olanlar icin de kayitlar getirilecek. INNER JOIN olsaydi bu NULL kayitlar gelmeyecektir.

LEFT OUTER JOIN
Employee30 [id=10, name=Sarah, surname=Proval, salary=59000] Department5 [id=1, name=Engineering]
Employee30 [id=8, name=Stephanie, surname=Whitmore, salary=45000] Department5 [id=1, name=Engineering]
Employee30 [id=7, name=Sue, surname=Bellows, salary=54000] Department5 [id=1, name=Engineering]
Employee30 [id=6, name=Rod , surname=Johnson , salary=62000] Department5 [id=1, name=Engineering]
Employee30 [id=5, name=Scott, surname=Brown, salary=60000] Department5 [id=1, name=Engineering]
Employee30 [id=4, name=Frank, surname= Sadler, salary=41000] Department5 [id=1, name=Engineering]
Employee30 [id=9, name=Jennifer, surname=Libby, salary=52000] Department5 [id=2, name=QA]
Employee30 [id=3, name=Peter, surname=Gunton, salary=40000] Department5 [id=2, name=QA]
Employee30 [id=2, name=Rob, surname=Freeman, salary=53000] Department5 [id=2, name=QA]
Employee30 [id=1, name=John, surname=Robbins, salary=55000] Department5 [id=2, name=QA]
Employee30 [id=11, name=Marcus, surname=Ragno, salary=35000] Department5 [id=3, name=Accounting]
Employee30 [id=13, name=Jack, surname=Snooze, salary=43000] null
Employee30 [id=12, name=Joe, surname=Hatlen, salary=36000] null

Burada dikkat etmemiz gereken nokta gelen son 2 kayit , eger LEFT JOIN yerine sadece JOIN keyword’unu kullansaydik bu son 2 kayit gelmeyecektir.

JPQL RIGHT keyword’unu desteklememektedir. Bunun icin JPQL sorgumuzu tersten yazabiliriz ;

SELECT e , d FROM Department5 d LEFT JOIN d.employees e

Ornegimizi calistirdigimizda ;

RIGHT OUTER JOIN
Employee30 [id=1, name=John, surname=Robbins, salary=55000] Department5 [id=2, name=QA]
Employee30 [id=2, name=Rob, surname=Freeman, salary=53000] Department5 [id=2, name=QA]
Employee30 [id=3, name=Peter, surname=Gunton, salary=40000] Department5 [id=2, name=QA]
Employee30 [id=4, name=Frank, surname= Sadler, salary=41000] Department5 [id=1, name=Engineering]
Employee30 [id=5, name=Scott, surname=Brown, salary=60000] Department5 [id=1, name=Engineering]
Employee30 [id=6, name=Rod , surname=Johnson , salary=62000] Department5 [id=1, name=Engineering]
Employee30 [id=7, name=Sue, surname=Bellows, salary=54000] Department5 [id=1, name=Engineering]
Employee30 [id=8, name=Stephanie, surname=Whitmore, salary=45000] Department5 [id=1, name=Engineering]
Employee30 [id=9, name=Jennifer, surname=Libby, salary=52000] Department5 [id=2, name=QA]
Employee30 [id=10, name=Sarah, surname=Proval, salary=59000] Department5 [id=1, name=Engineering]
Employee30 [id=11, name=Marcus, surname=Ragno, salary=35000] Department5 [id=3, name=Accounting]
null Department5 [id=6, name=CADocOtt]
null Department5 [id=5, name=USEngCal]
null Department5 [id=4, name=CAEngOtt]

Dikkat edecek olursak bu sefer de tum Department5 kayitlari geldi. Son 3 kayit icin Employee30 karsiligi null olarak geldi.

Uzun olmamasi acisindan kodlarin tam kismini paylasmiyorum. Tum kodlari kaynak dosya icerisinde bulabilirsiniz.

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