EclipseLink – 22 – Query – 05 – Aggregate Functions & Update & Delete
Merhaba Arkadaslar
Bu bolumde Aggregate Function’lari , Group By ve Having kullanimini inceleyecegiz. Burada ilgili konular ve keywordler icin bilgi vermeyecegim. Bunun icin Oracle DB yazilarima bakabilirsiniz. Burada konumuz JPQL , SQL degil.
Aggregate Functions
Aggregate Function’lar ;
- SUM
- AVG
- COUNT
- MAX
- MIN
SUM fonksiyonu, ilgili numerik alanin toplam degerini verir.
AVG fonksiyonu , ilgili numerik alanin ortalama degerini hesaplar (average).
COUNT fonksiyonu ilgili kayit sayisi icin kullanilabilir.
MAX fonksiyonu ilgili numerik alan icin en buyuk degeri dondurur.
MIN fonksiyonu ilgili numerik alan icin en kucuk degeri dondurur.
SELECT AVG(e.salary) , SUM(e.salary) , COUNT(e.id) , MAX(e.salary) , MIN(e.salary) FROM Employee30 e
System.out.println("AGGREGATE_FUNCTIONS"); List<Object[]>aggregateFunctions= jpqlService.executeResultListQuery(AGGREGATE_FUNCTIONS, Object[].class); for (Object[] element : aggregateFunctions) { System.out.println(element[0] + " " + element[1]+ " " + element[2] + " " + element[3] + " " +element[4]); }
Ornegimizi calistirdigimizda ;
AGGREGATE_FUNCTIONS 48846.153846153844 635000 13 62000 35000
GROUP BY
Departmana gore ortalama/AVG maas bilgisini getirmek istersek;
SELECT d.name , AVG(e.salary) FROM Department5 d JOIN d.employees e GROUP BY d.name
SQL karsiligi ;
SELECT d.name , AVG(e.salary) FROM Department5 d JOIN Employee30 e ON d.id = e.dept_id GROUP BY d.name;
System.out.println("AVG_SALARY_GROUP_BY_DEPARTMENT"); List<Object[]> avgSalaryGroupByDepartment = jpqlService.executeResultListQuery(AVG_SALARY_GROUP_BY_DEPARTMENT, Object[].class); for (Object[] element : avgSalaryGroupByDepartment) { System.out.println(element[0] + " " + element[1]); }
AVG_SALARY_GROUP_BY_DEPARTMENT Accounting 35000.0 QA 50000.0
HAVING BY
SELECT d.name , AVG(e.salary) FROM Department5 d JOIN d.employees e GROUP BY d.name HAVING AVG(e.salary)>?1";
SQL karsiligi olarak ;
SELECT d.name , AVG(e.salary) FROM Department5 d JOIN Employee30 e ON d.id = e.dept_id GROUP BY d.name HAVING AVG(e.salary)>50000;
Ornegimizi calistirdigimizda ;
System.out.println("AVG_SALARY_GROUP_BY_HAVING_DEPARTMENT"); List<Object[]> avgSalaryGroupByDepartmentHaving = jpqlService.executeResultListQuery(AVG_SALARY_GROUP_BY_DEPARTMENT_HAVING, Object[].class,50000); for (Object[] element : avgSalaryGroupByDepartmentHaving) { System.out.println(element[0] + " " + element[1]); }
ORDER BY
Employee kayitlari icin salary degerine gore buyukten kucuge dogru siralamak istersek ;
SELECT e FROM Employee30 e ORDER BY e.salary DESC
SQL karsiligi ;
SELECT * FROM Employee30 e ORDER BY e.salary DESC
System.out.println("SELECT_EMPLOYEE_ORDER_BY_SALARY"); List<Employee30> selectEmployeeOrderBySalary = jpqlService.executeResultListQuery(SELECT_EMPLOYEE_ORDER_BY_SALARY, Employee30.class); for (Employee30 employee : selectEmployeeOrderBySalary) { System.out.println(employee); }
Ornegimizi calistirdigimizda Employee kayitlari icin salary degerine gore DESCENDING olarak siralandigini gorebiliriz;
SELECT_EMPLOYEE_ORDER_BY_SALARY Employee30 [id=6, name=Rod , surname=Johnson , salary=62000] Employee30 [id=5, name=Scott, surname=Brown, salary=60000] Employee30 [id=10, name=Sarah, surname=Proval, salary=59000] Employee30 [id=1, name=John, surname=Robbins, salary=55000] Employee30 [id=7, name=Sue, surname=Bellows, salary=54000] Employee30 [id=2, name=Rob, surname=Freeman, salary=53000] Employee30 [id=9, name=Jennifer, surname=Libby, salary=52000] Employee30 [id=8, name=Stephanie, surname=Whitmore, salary=45000] Employee30 [id=13, name=Jack, surname=Snooze, salary=43000] Employee30 [id=4, name=Frank, surname= Sadler, salary=41000] Employee30 [id=3, name=Peter, surname=Gunton, salary=40000] Employee30 [id=12, name=Joe, surname=Hatlen, salary=36000] Employee30 [id=11, name=Marcus, surname=Ragno, salary=35000]
UPDATE
Ornek olarak id degeri 1 olan Employee icin salary degerini guncellemek istersek ;
UPDATE Employee30 e SET e.salary=80000 WHERE e.id=1
DELETE
Ornek olarak id degeri 13 olan Employee kaydini silmek istersek ;
DELETE FROM Employee30 e WHERE e.id=13
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
Leave a Reply