Kriteria API Part 2

Fungsi klompok lan agregasi

Sampeyan wis ngerti carane nggawe panjalukan prasaja menyang API Kriteria. Ayo ndeleng carane nggawe pitakon sing luwih rumit.

Contone, kita pengin nulis pitakon kanggo nemtokake jumlah karyawan ing perusahaan. Mangkene carane bakal katon ing HQL:

select count(*) from Employee

Lan kaya iki ing API Kriteria:

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

Kode Jawa lengkap bakal katon kaya iki:

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

Lan padha nggunakake HQL:

String hqlQuery = "select count(*) from Employee";

Query<Long> query = session.createQuery(hqlQuery);
Long count = query.getSingleResult();

Saiki ayo nyoba ngetung gaji rata-rata ing perusahaan. Pitakon HQL bakal katon kaya iki:

select avg(salary) from Employee

Lan kaya iki ing API Kriteria:

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

Kode Jawa lengkap bakal katon kaya iki:

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

Kriteria Nganyari

Ngowahi tabel gampang kaya njupuk data saka iku. Kanggo nindakake iki, CriteriaBuilder duwe cara khusus - createCriteriaUpdate () , sing nggawe obyekKriteria Update<T>Sing nganyari entitas ing database.

Ayo diunggahake gaji karyawan sing nampa kurang saka 10 ewu. Mangkene pitakon HQL iki:

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

Lan iki bakal katon ing API Kriteria:

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

Kriteria Busak

Lan mbusak cathetan malah luwih gampang tinimbang ngganti. Kanggo nindakake iki, ana cara khusus createCriteriaDelete () , sing nggawe obyekKriteria Busak <T>.

Ayo dipotong kabeh karyawan sing ora ana regane: gajine kurang saka 10 ewu. Mangkene pitakon HQL iki:

delete from Employee where salary<=10000

Lan iki bakal katon ing API Kriteria:

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

Keuntungan saka API Kriteria

Dadi apa keuntungan saka API Kriteria? Pitakonan rumit, HQL mesthi bakal luwih kompak.

Kaping pisanan, pitakon HQL ora cendhak yen sampeyan kudu ngirim parameter kasebut. mbandhingake:

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

Kapindho, asring ana kahanan nalika pitakon kudu dibangun kanthi dinamis. Contone, sampeyan duwe kaca web nyaring karyawan, apartemen, lan apa wae. Lan yen sawetara parameter ora penting kanggo pangguna, mula dheweke ora nuduhake. Mulane, null diterusake menyang server.

Iki tugas sampeyan: milih karyawan kanthi profesi (pekerjaan), gaji (gaji) lan taun kerja (TAHUN (tanggal_gabung)). Nanging yen ana nilai parameter null, banjur aja nggunakake ing Filter.

Banjur pitakon HQL bakal katon kaya iki:

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

Nanging ora bakal bisa digunakake kanthi bener, amarga kita pengin yen parameter "jny" nol, mula panyuwunan bakal katon kaya iki:

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

Kita bisa nyoba nulis ulang panyuwunan kanthi mriksa parameter kanggo null, banjur kita bakal entuk kaya iki:

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)

Waca carane kasunyatan dadi luwih rumit? Realitanya sering kaya gini :)

Nanging panyaring bisa luwih rumit. Kepiye carane nggoleki pangguna sing duwe tugas kanthi tembung "tuku"? Utawa pangguna sing duwe tugas telat?

from Employee
where (occupation = :ocp)
   	and (salary = :sal)
   	and (YEAR(join_date) = :jny)
   	and (tasks.name like '%buy%')
   	and (tasks.deadline < curdate())

Yen sampeyan nulis utawa null nang endi wae ing pitakonan kuwi , banjur iki ora bakal mbatalake gabung antarane tabel.

Dadi ing praktik, nalika sampeyan nindakake saringan kompleks ing sawetara tabel, API Kriteria mung bisa mbantu sampeyan. Dadi dadi.

Rincian liyane bisa ditemokake ing dokumentasi resmi .