March 1st, 2023
JPQL Queries
4.0.0
org.springframework.boot
spring-boot-starter-parent
3.0.2
com.brains.jpa.hibernate
jpa-hibernate-demo
0.0.1-SNAPSHOT
jpa-hibernate-demo
Demo project for Spring Boot
17
org.springframework.boot
spring-boot-starter-data-jpa
org.springframework.boot
spring-boot-starter-web
com.h2database
h2
runtime
org.projectlombok
lombok
true
org.springframework.boot
spring-boot-starter-test
test
org.springframework.boot
spring-boot-maven-plugin
org.projectlombok
lombok
spring.datasource.url=jdbc:h2:mem:testdb;NON_KEYWORDS=USER;DB_CLOSE_ON_EXIT=FALSE
spring.h2.console.enabled=true
spring.jpa.defer-datasource-initialization=true
# Turn Statistics ON
#spring.jpa.properties.hibernate.generate_statistics=true
logging.level.org.hibernate.stat=debug
#logging.level.root=trace
# Show all queries
spring.jpa.show-sql=true
# Format the queries
spring.jpa.properties.hibernate.format_sql=true
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.entity;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import jakarta.persistence.ManyToOne;
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 Review {
@Id
@GeneratedValue
private Long id;
@NonNull
private String rating;
@NonNull
private String description;
@ToString.Exclude
@ManyToOne
private Course course;
}
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 10 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;
import static org.junit.jupiter.api.Assertions.*;
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 com.brains.jpa.hibernate.jpahibernatedemo.entity.Student;
import jakarta.persistence.EntityManager;
import jakarta.persistence.TypedQuery;
import jakarta.transaction.Transactional;
@SpringBootTest(classes = JpaHibernateDemoApplication.class)
class JPQLQueryTest {
private Logger logger = LoggerFactory.getLogger(getClass());
@Autowired
EntityManager em;
// get course which do not have any student
// @Test
@Transactional
void jpql_courses_without_students() {
TypedQuery createQuery = em.createQuery("select c from Course c where c.students is empty", Course.class);
List resultList = createQuery.getResultList();
logger.info("Courses: -> {}", resultList);
}
// get courses where which has more thatn 2 students
// @Test
@Transactional
void jpql_courses_with_atleast_2_students() {
TypedQuery createQuery = em.createQuery("select c from Course c where size(c.students) > 2", Course.class);
List resultList = createQuery.getResultList();
logger.info("Courses: -> {}", resultList);
}
// get courses order by student size
// by default, order by is ascending(0-10)
// @Test
@Transactional
void jpql_courses_ordered_by_students() {
TypedQuery createQuery = em.createQuery("select c from Course c order by size(c.students) desc", Course.class);
List resultList = createQuery.getResultList();
logger.info("Courses: -> {}", resultList);
}
// get students whose passport number contains 12345
/**
* Other functions
* ===============
* like
* Between 100 and 500
* Is Null
*
* ---- On String ------
* upper, lower, trim, length
*/
@Test
@Transactional
void jpql_sudent_passport_matching() {
TypedQuery createQuery = em.createQuery("select s from Student s where s.passport.number like '%1234%'", Student.class);
List resultList = createQuery.getResultList();
logger.info("Courses: -> {}", resultList);
}
}
Leave a Reply