March 3rd, 2023
Criteria Queries
Use of Criteria queries instead of writing the SQL queries
insert into course(id, name, created_date, last_updated_date)
values(10001, 'JPA in 5 Steps', LOCALTIMESTAMP, LOCALTIMESTAMP);
insert into course(id, name, created_date, last_updated_date)
values(10002, 'JDBC in 100 Steps', LOCALTIMESTAMP, LOCALTIMESTAMP);
insert into course(id, name, created_date, last_updated_date)
values(10003, 'JPQL in 50 Steps', LOCALTIMESTAMP, LOCALTIMESTAMP);
insert into passport(id, number)
values(40001,'E12345');
insert into passport(id, number)
values(40002,'F212345');
insert into passport(id, number)
values(40003,'GE23451');
insert into student(id, name, passport_id)
values(20001,'Ranga',40001);
insert into student(id, name, passport_id)
values(20002,'Adam',40002);
insert into student(id, name, passport_id)
values(20003,'Jane',40003);
insert into review(id, rating, description, course_id)
values(50001,'1', 'Great course', 10001);
insert into review(id, rating, description, course_id)
values(50002,'3', 'Nice course', 10001);
insert into review(id, rating, description, course_id)
values(50004,'5', 'Good you are writing', 10003);
insert into student_course(student_id, course_id)
values(20001, 10001);
insert into student_course(student_id, course_id)
values(20002, 10001);
insert into student_course(student_id, course_id)
values(20003, 10001);
insert into student_course(student_id, course_id)
values(20001, 10003);
package com.brains.jpa.hibernate.jpahibernatedemo.entity;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import jakarta.persistence.ManyToMany;
import jakarta.persistence.OneToMany;
import lombok.AccessLevel;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.NonNull;
import lombok.RequiredArgsConstructor;
import lombok.Setter;
@Entity
@Data
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class Course {
@Id
@GeneratedValue
private Long id;
@NonNull
private String name;
@Setter(AccessLevel.NONE)
@OneToMany(mappedBy = "course", fetch = FetchType.LAZY)
private List reviews = new ArrayList<>();
@ManyToMany(mappedBy = "courses")
@Setter(value = AccessLevel.NONE)
private List students = new ArrayList<>();
public void addStudent(Student student) {
this.students.add(student);
}
public void addReview(Review review) {
this.reviews.add(review);
}
public void removeReview(Review review) {
this.reviews.remove(review);
}
@UpdateTimestamp
private LocalDateTime lastUpdatedDate;
@CreationTimestamp
private LocalDateTime createdDate;
public Course(String name) {
this.name = name;
}
}
package com.brains.jpa.hibernate.jpahibernatedemo;
import java.util.List;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import com.brains.jpa.hibernate.jpahibernatedemo.entity.Course;
import jakarta.persistence.EntityManager;
import jakarta.persistence.TypedQuery;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Predicate;
import jakarta.persistence.criteria.Root;
import jakarta.transaction.Transactional;
@SpringBootTest(classes = JpaHibernateDemoApplication.class)
class CriteriaQueryTest {
private Logger logger = LoggerFactory.getLogger(getClass());
@Autowired
EntityManager em;
@Test
@Transactional
void criteria_query_get_all_courses() {
//"Select * From Course c"
// 1. Use Criteria builder to create CriteriaQuery returning the expected result
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(Course.class);
// 2. Define root for the tables which are involved in the query
Root courseRoot = cq.from(Course.class); // 'From Course c' part of main query
// 3. Define Predicates for adding conditions
// TypedQuery createQuery = em.createQuery("select c from Course c", Course.class);
TypedQuery createQuery = em.createQuery(cq.select(courseRoot));
List resultList = createQuery.getResultList();
logger.info("criteria_query_get_all_courses");
logger.info("Size: -> {}", resultList.size());
logger.info("Courses: -> {}", resultList);
}
@Test
@Transactional
void criteria_query_all_courses_with_100Steps() {
//"Select * From Course c where name like '%100 Steps%'"
// 1. Use Criteria builder to create CriteriaQuery returning the expected result
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(Course.class);
// 2. Define root for the tables which are involved in the query
Root courseRoot = cq.from(Course.class); // 'From Course c' part of main query
// 3. Define Predicates for adding conditions
Predicate like100Steps = cb.like(courseRoot.get("name"), "%100 Steps"); // 'like '%100' part of main query
// 4. Add predicate to criteria query
cq.where(like100Steps); // 'where' part of main query
// TypedQuery createQuery = em.createQuery("select c from Course c", Course.class);
TypedQuery createQuery = em.createQuery(cq.select(courseRoot));
List resultList = createQuery.getResultList();
logger.info("criteria_query_all_courses_with_100Steps");
logger.info("Size: -> {}", resultList.size());
logger.info("Courses: -> {}", resultList);
}
@Test
@Transactional
void all_courses_without_students() {
// "Select * from Course c where c.students is empty"
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(Course.class);
Root courseRoot = cq.from(Course.class);
Predicate emptyStudents = cb.isEmpty(courseRoot.get("students"));
cq.where(emptyStudents);
TypedQuery query = em.createQuery(cq.select(courseRoot));
List resultList = query.getResultList();
logger.info("all_courses_without_students");
logger.info("Size: -> {}", resultList.size());
logger.info("Courses: -> {}", resultList);
}
}
2023-03-06T17:14:20.343+05:30 INFO 69425 --- [ main] c.b.j.h.j.CriteriaQueryTest : criteria_query_get_all_courses
2023-03-06T17:14:20.344+05:30 INFO 69425 --- [ main] c.b.j.h.j.CriteriaQueryTest : Size: -> 3
2023-03-06T17:14:20.344+05:30 INFO 69425 --- [ main] c.b.j.h.j.CriteriaQueryTest : Courses: -> [Course(id=10001, name=JPA in 5 Steps, reviews=[Review(id=50001, rating=1, description=Great course), Review(id=50002, rating=3, description=Nice course)], students=[Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345)), Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345)), Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))], lastUpdatedDate=2023-03-06T17:14:19.983050, createdDate=2023-03-06T17:14:19.983050), Course(id=10002, name=JDBC in 100 Steps, reviews=[], students=[], lastUpdatedDate=2023-03-06T17:14:19.984303, createdDate=2023-03-06T17:14:19.984303), Course(id=10003, name=JPQL in 50 Steps, reviews=[Review(id=50004, rating=5, description=Good you are writing)], students=[Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))], lastUpdatedDate=2023-03-06T17:14:19.984514, createdDate=2023-03-06T17:14:19.984514)]
2023-03-06T17:14:20.313+05:30 INFO 69425 --- [ main] c.b.j.h.j.CriteriaQueryTest : criteria_query_all_courses_with_100Steps
2023-03-06T17:14:20.313+05:30 INFO 69425 --- [ main] c.b.j.h.j.CriteriaQueryTest : Size: -> 1
2023-03-06T17:14:20.313+05:30 INFO 69425 --- [ main] c.b.j.h.j.CriteriaQueryTest : Courses: -> [Course(id=10002, name=JDBC in 100 Steps, reviews=[], students=[], lastUpdatedDate=2023-03-06T17:14:19.984303, createdDate=2023-03-06T17:14:19.984303)]
2023-03-06T17:37:24.958+05:30 INFO 73453 --- [ main] c.b.j.h.j.CriteriaQueryTest : all_courses_without_students
2023-03-06T17:37:24.958+05:30 INFO 73453 --- [ main] c.b.j.h.j.CriteriaQueryTest : Size: -> 1
2023-03-06T17:37:24.958+05:30 INFO 73453 --- [ main] c.b.j.h.j.CriteriaQueryTest : Courses: -> [Course(id=10002, name=JDBC in 100 Steps, reviews=[], students=[], lastUpdatedDate=2023-03-06T17:37:24.683421, createdDate=2023-03-06T17:37:24.683421)]
Leave a Reply