March 6th, 2023
Criteria Query with Join
Using Join with Criteria 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.entity;
import java.util.ArrayList;
import java.util.List;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.JoinTable;
import jakarta.persistence.ManyToMany;
import jakarta.persistence.OneToOne;
import lombok.AccessLevel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.NonNull;
import lombok.RequiredArgsConstructor;
import lombok.Setter;
import lombok.ToString;
@Entity
@Data @NoArgsConstructor(access = AccessLevel.PROTECTED)
@AllArgsConstructor
@RequiredArgsConstructor
public class Student {
@Id
@GeneratedValue
private Long id;
@NonNull
@Column(nullable = false)
private String name;
@OneToOne(fetch = FetchType.LAZY)
private Passport passport;
@ToString.Exclude
@ManyToMany
@Setter(value = AccessLevel.NONE)
@JoinTable(name = "STUDENT_COURSE",
joinColumns = @JoinColumn(name = "STUDENT_ID"),
inverseJoinColumns = @JoinColumn(name = "COURSE_ID")
)
private List courses = new ArrayList<>();
public void addCourse(Course course) {
this.courses.add(course);
}
}
package com.brains.jpa.hibernate.jpahibernatedemo.entity;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import jakarta.persistence.OneToOne;
import lombok.AccessLevel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.NonNull;
import lombok.RequiredArgsConstructor;
import lombok.ToString;
@Entity
@Data @NoArgsConstructor(access = AccessLevel.PROTECTED)
@AllArgsConstructor
@RequiredArgsConstructor
public class Passport {
@Id
@GeneratedValue
private Long id;
@NonNull
@Column(nullable = false)
private String number;
@ToString.Exclude
@OneToOne(fetch = FetchType.LAZY, mappedBy = "passport")
private Student student;
}
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.Join;
import jakarta.persistence.criteria.JoinType;
import jakarta.persistence.criteria.Predicate;
import jakarta.persistence.criteria.Root;
import jakarta.transaction.Transactional;
@SpringBootTest(classes = JpaHibernateDemoApplication.class)
class CriteriaJoinQueryTest {
private Logger logger = LoggerFactory.getLogger(getClass());
@Autowired
EntityManager em;
@Test
@Transactional
void join() {
//"Select * From Course c join c.students s"
// 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
Join
2023-03-06T18:29:25.574+05:30 INFO 81680 --- [ main] c.b.j.h.j.CriteriaJoinQueryTest : join
2023-03-06T18:29:25.574+05:30 INFO 81680 --- [ main] c.b.j.h.j.CriteriaJoinQueryTest : Size: -> 2
2023-03-06T18:29:25.574+05:30 INFO 81680 --- [ main] c.b.j.h.j.CriteriaJoinQueryTest : 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-06T18:29:25.317624, createdDate=2023-03-06T18:29:25.317624), 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-06T18:29:25.319045, createdDate=2023-03-06T18:29:25.319045)]
2023-03-06T18:29:25.608+05:30 INFO 81680 --- [ main] c.b.j.h.j.CriteriaJoinQueryTest : left_join
2023-03-06T18:29:25.608+05:30 INFO 81680 --- [ main] c.b.j.h.j.CriteriaJoinQueryTest : Size: -> 3
2023-03-06T18:29:25.608+05:30 INFO 81680 --- [ main] c.b.j.h.j.CriteriaJoinQueryTest : 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-06T18:29:25.317624, createdDate=2023-03-06T18:29:25.317624), Course(id=10002, name=JDBC in 100 Steps, reviews=[], students=[], lastUpdatedDate=2023-03-06T18:29:25.318861, createdDate=2023-03-06T18:29:25.318861), 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-06T18:29:25.319045, createdDate=2023-03-06T18:29:25.319045)]
Leave a Reply