Merhaba Arkadaslar,
Bu bolumde WHERE Clause’u inceleyecegiz.
Onceki ornegimizde kullandigimiz siniflar ve data uzerinden devam edebiliriz.

Ornegin salary degeri belirttigimiz degerden buyuk Employee’leri getirmek icin , ornegin 58000 den buyuk salary sahip Employee’ler icin ;

SELECT e from Employee30 e where e.salary > ?1

Yukaridaki JPQL sorgumuzun SQL karsiligi ;

SELECT * from Employee30 e where e.salary > 58000;

System.out.println("INPUT_PARAMETER");

List<Employee30> inputParameterList = jpqlService.executeResultListQuery(INPUT_PARAMETER, Employee30.class,58000);

for (Employee30 employee : inputParameterList) {
    System.out.println(employee);
}

Ornegimizi calistirdigimizda ;

INPUT_PARAMETER
Employee30 [id=5, name=Scott, surname=Brown, salary=60000]
Employee30 [id=6, name=Rod , surname=Johnson , salary=62000]
Employee30 [id=10, name=Sarah, surname=Proval, salary=59000]

BETWEEN

Bir baska ornek olarak ;

SELECT e FROM Employee30 e WHERE e.salary >= ?1 AND e.salary <= ?2

JPQL sorgumuzun SQL karsiligi ;

SELECT * from Employee30 e WHERE e.salary >=45000 AND e.salary <=55000;

System.out.println("GREAT_AND_LESS");

List<Employee30> greatAndLessList = jpqlService.executeResultListQuery(GREAT_AND_LESS, Employee30.class,45000, 55000);

for (Employee30 employee : greatAndLessList) {
    System.out.println(employee);
}

Ornegimizi calistirdigimizda , maas araligi [45000 – 55000] olan Employee’ler listenelecektir.

GREAT_AND_LESS
Employee30 [id=1, name=John, surname=Robbins, salary=55000]
Employee30 [id=2, name=Rob, surname=Freeman, salary=53000]
Employee30 [id=7, name=Sue, surname=Bellows, salary=54000]
Employee30 [id=8, name=Stephanie, surname=Whitmore, salary=45000]
Employee30 [id=9, name=Jennifer, surname=Libby, salary=52000]

Benzer ornegi BETWEEN keyword’unu kullanarak da yapabiliriz ;

SELECT e FROM Employee30 e WHERE e.salary BETWEEN ?1 AND ?2"

JPQL sorgumuzun SQL karsiligi ;

SELECT * FROM Employee30 e WHERE e.salary BETWEEN 45000 AND 55000;

System.out.println("BETWEEN_EXPRESSION");

List<Employee30> betweenList = jpqlService.executeResultListQuery(BETWEEN_EXPRESSION, Employee30.class,45000, 55000);

for (Employee30 employee : betweenList) {
     System.out.println(employee);
}

Ornegimizi calistirdigimizda salary araligi [45000 – 55000] disinda olan Employee’ler gelecektir

BETWEEN_EXPRESSION
Employee30 [id=1, name=John, surname=Robbins, salary=55000]
Employee30 [id=2, name=Rob, surname=Freeman, salary=53000]
Employee30 [id=7, name=Sue, surname=Bellows, salary=54000]
Employee30 [id=8, name=Stephanie, surname=Whitmore, salary=45000]
Employee30 [id=9, name=Jennifer, surname=Libby, salary=52000]

NOT anahtar kelimesini kullandigimizda ;

SELECT e FROM Employee30 e WHERE e.salary NOT BETWEEN ?1 AND ?2

SELECT * FROM Employee30 e WHERE e.salary NOT BETWEEN 45000 AND 55000;

System.out.println("NOT_BETWEEN_EXPRESSION");

List<Employee30> notBetweenList = jpqlService.executeResultListQuery(NOT_BETWEEN_EXPRESSION, Employee30.class,45000, 55000);

for (Employee30 employee : notBetweenList) {
    System.out.println(employee);
}

Ornegimizi calistirdigimizda ;

NOT_BETWEEN_EXPRESSION
Employee30 [id=3, name=Peter, surname=Gunton, salary=40000]
Employee30 [id=4, name=Frank, surname= Sadler, salary=41000]
Employee30 [id=5, name=Scott, surname=Brown, salary=60000]
Employee30 [id=6, name=Rod , surname=Johnson , salary=62000]
Employee30 [id=10, name=Sarah, surname=Proval, salary=59000]
Employee30 [id=11, name=Marcus, surname=Ragno, salary=35000]
Employee30 [id=12, name=Joe, surname=Hatlen, salary=36000]
Employee30 [id=13, name=Jack, surname=Snooze, salary=43000]

LIKE

SELECT d FROM Department5 d WHERE d.name LIKE ?1

SELECT * FROM Department5 d WHERE d.name LIKE 'Eng%';

System.out.println("LIKE_EXPRESSION");

List<Department5> likeList = jpqlService.executeResultListQuery(LIKE_EXPRESSION, Department5.class,"%Eng%");

for (Department5 department : likeList) {
   System.out.println(department);
}

Ornegimizi calistirdigimizda ;

LIKE_EXPRESSION
Department4 [id=1, name=Engineering]

IN

Address state bilgisi NY ya da NJ olan Employee kayitlarini getirmek istersek ;

SELECT e FROM Employee30 e WHERE e.address.state IN (?1, ?2)

SELECT * FROM Employee30 e JOIN Address2 a on e.address_id = a.id 
WHERE a.state IN ('NY' , 'NJ');

System.out.println("IN_EXPRESSION");

List<Employee30> inExpression = jpqlService.executeResultListQuery(IN_EXPRESSION, Employee30.class,"NY","NJ");

for (Employee30 employee : inExpression) {
    System.out.println(employee);
}

IN_EXPRESSION
Employee30 [id=1, name=John, surname=Robbins, salary=55000]
Employee30 [id=2, name=Rob, surname=Freeman, salary=53000]
Employee30 [id=3, name=Peter, surname=Gunton, salary=40000]
Employee30 [id=8, name=Stephanie, surname=Whitmore, salary=45000]
Employee30 [id=9, name=Jennifer, surname=Libby, salary=52000]

ALL , ANY

SELECT e from Employee30 e WHERE e.salary> ALL
(SELECT e2.salary FROM Employee30 e2 WHERE e2.department.id =?1)

SELECT * from Employee30 e WHERE e.salary > ALL 
(SELECT e2.salary FROM Employee30 e2 WHERE e2.dept_id = '2');

Sorgumuzu inceleyecek olursak , department id degeri 2 olan tum calisanlarin salary degerinden fazla olan Employee kayitlarini getirmektedir.

Ornegimizi calistirdigimizda ;

ALL_EXPRESSION
Employee30 [id=10, name=Sarah, surname=Proval, salary=59000]
Employee30 [id=5, name=Scott, surname=Brown, salary=60000]
Employee30 [id=6, name=Rod , surname=Johnson , salary=62000]

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