Criteria API, ikkinchi qism

Guruplar va aggregatsion funksiyalar

Criteria API yordamida qanday qilib oddiy so'rovlar qilish mumkinligini tushunib olding. Keling, endi yanada murakkabroq so'rovlar qilishni ko'rib chiqamiz.

Masalan, kompaniyadagi xodimlar sonini aniqlash uchun so'rov yozmoqchimiz. HQLda bu quyidagicha bo'ladi:

select count(*) from Employee

A Criteria API-da quyidagicha:

CriteriaQuery<Long> critQuery = builder.createQuery(Long.class);
critQuery.select(builder.count(critQuery.from(Employee.class)));

Butun Java-kod quyidagicha ko'rinadi:

CriteriaBuilder builder = session.getCriteriaBuilder();
 
CriteriaQuery<Long> critQuery = builder.createQuery(Long.class);
critQuery.select(builder.count(critQuery.from(Employee.class)));
 
Query<Long> query = session.createQuery(critQuery);
Long count = query.getSingleResult();

Va HQLni ishlatganda esa:

String hqlQuery = "select count(*) from Employee";
 
Query<Long> query = session.createQuery(hqlQuery);
Long count = query.getSingleResult();

Endi kompaniyada o'rtacha oylikni hisoblashga harakat qilaylik. HQLda so'rov quyidagicha bo'ladi:

select avg(salary) from Employee

A Criteria API-da quyidagicha:

CriteriaQuery<Double> critQuery = builder.createQuery(Double.class);
critQuery.select(builder.avg( critQuery.from(Employee.class).get("salary")));

Butun Java-kod quyidagicha bo'ladi:

CriteriaBuilder builder = session.getCriteriaBuilder();
 
CriteriaQuery<Double> critQuery = builder.createQuery(Double.class);
critQuery.select(builder.avg( critQuery.from(Employee.class).get("salary")));
 
Query<Double> query = session.createQuery(critQuery);
Double avgSalary = query.getSingleResult();

CriteriaUpdate

CriteriaBuilderda createCriteriaUpdate() maxsus metodi mavjud bo'lib, ular baza ma'lumotlarini yangilaydigan CriteriaUpdate<T> obyektini yaratadi.

10 mingdan past maosh oluvchilarning maoshini oshiramiz. HQLda so'rov quyidagicha bo'ladi:

update Employee set salary = salary+20000 where salary<=10000

Criteria API-da esa quyidagicha ko'rinadi:

CriteriaUpdate<Employee> criteriaUpdate = builder.createCriteriaUpdate(Employee.class);
Root<Employee> root = criteriaUpdate.from(Employee.class);
criteriaUpdate.set("salary", "salary+20000");
criteriaUpdate.where(builder.lt(root.get("salary"), 10000));
 
Transaction transaction = session.beginTransaction();
session.createQuery(criteriaUpdate).executeUpdate();
transaction.commit();

CriteriaDelete

Yozuvlarni o'chirish juda oson, bu createCriteriaDelete() metodi orqali amalga oshiriladi, u CriteriaDelete<T> obyektini yaratadi.

10 mingdan past maosh olgan xodimlarni kamaytiramiz. HQLda so'rov quyidagicha bo'ladi:

delete from Employee where salary<=10000

Criteria API-da esa quyidagicha ko'rinadi:

CriteriaDelete<Employee> criteriaDelete = builder.createCriteriaDelete(Employee.class);
Root<Employee> root = criteriaDelete.from(Employee.class);
criteriaDelete.where(builder.lt(root.get("salary"), 10000));
 
Transaction transaction = session.beginTransaction();
session.createQuery(criteriaDelete).executeUpdate();
transaction.commit();

Criteria API foydasi

Criteria APIning foydasi nimada o'zi? So'rovlar juda qiyinchilik tug'diradi, HQLda tushunarliroq bo'lardi.

Birinchidan, agar so'rovlarga parametrlarni uzatish kerak bo'lsa, HQLda so'rovlar unchalik qisqa emas. Solishtiring:

String hqlQuery = "from Employee where salary < :sal"; Query<Employee> query = session.createQuery(hqlQuery); query.setParametr("sal", 10000); List<Employee> results = query.getResultList();

CriteriaBuilder builder = session.getCriteriaBuilder();
critQuery.select(critQuery.from(Employee.class)).where(builder.lt(root.get("salary"), 10000));
Query<Employee> query = session.createQuery(critQuery);
List<Employee> results = query.getResultList();

Ikkinchidan, ko'pincha dinamika bilan so'rovni tuzish kerak bo'ladi. Masalan, web-sahifanga siz filtrlaydigan xodimlar, kvartiralar mavjud, va foydalanuvchi parametrni kiritmasa, null bo'ladi.

Masalan, ma'lum kasb (occupation), maosh (salary) va ishga kirgan yili (YEAR (join_date)) bo'yicha xodimlarni tanlash kerak. Ammo agar parametrning qiymati nullga teng bo'lsa, filtrga kiritmaslik kerak.

HQLdagi so'rov taxminan quyidagicha ko'rinadi:

from Employee
where (occupation = :ocp)
   	and (salary = :sal)
   	and ( YEAR(join_date) = :jny)

Ammo, bu to'g'ri ishlamaydi, chunki agar "jny" parametri null bo'lsa, so'rov quyidagicha ko'rinishini xohlaymiz:

from Employee
where (occupation = :ocp)
   	and (salary = :sal)

Parametrning null qiymatini tekshirish bilan so'rovni qayta yozish mumkin, bu esa quyidagicha bo'ladi:

from Employee
where (occupation = :ocp or :ocp is null)
   	and (salary = :sal or :sal is null)
   	and ( YEAR(join_date)= :jny or :jny is null)

Haqiqatda esa hammasi murakkablanib ketadi. Haqiqat shunday :)

Filtrni yanada murakkab qilish mumkin. Masalan, "sotib olish" so'zi bor vazifalarni topish yoki kechiktirilgan vazifalari bor foydalanuvchilarni qidirish kabi.

from Employee
where (occupation = :ocp)
   	and (salary = :sal)
   	and (YEAR(join_date) = :jny)
   	and (tasks.name like '%купить%')
   	and (tasks.deadline < curdate())

Agar bunday so'rovda or is nullni yozsangiz, bu jadvallar orasidagi joinni bekor qilmaydi.

Shunday qilib, bir nechta jadvallarning maydonlariga qiyosiy murakkab filtrga ehtiyoj sezsangiz, Criteria API sizni juda kuchli yordamchi bo'ladi. Ana shunday.

Batafsil ma'lumotni rasmiy hujjatlardan topishingiz mumkin.