JOIN Query

by with No Comment NamedQuery

Different types of SQL Joins


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

JOIN Types

Join Course with Student

JOIN => Select c, s from Course c JOIN c.students s
(INNER) JOIN : Returns records that have matching values in both tables.

LEFT JOIN => Select c, s from Course c LEFT JOIN c.students s
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.

CROSS JOIN = > 3 Course X 3 Student => 9 Result Takes all

(INNER) Join
				
					@Test
	@Transactional
	public void join() {
		Query query = em.createQuery("Select c, s from Course c JOIN c.students s");
		List<Object[]> list = query.getResultList();
		logger.info("List size: -> {}", list.size());
		
		for(Object[] result: list) {
			logger.info("Course: {} Student: {}", result[0], result[1]);
		}
	}
				
			
				
					2023-03-01T20:20:41.461+05:30  INFO 384446 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : List size: -> 4
2023-03-01T20:20:41.461+05:30  INFO 384446 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : Course: 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-01T20:20:40.706694, createdDate=2023-03-01T20:20:40.706694) Student: Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))
2023-03-01T20:20:41.489+05:30  INFO 384446 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : Course: 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-01T20:20:40.706694, createdDate=2023-03-01T20:20:40.706694) Student: Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345))
2023-03-01T20:20:41.490+05:30  INFO 384446 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : Course: 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-01T20:20:40.706694, createdDate=2023-03-01T20:20:40.706694) Student: Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))
2023-03-01T20:20:41.490+05:30  INFO 384446 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : Course: 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-01T20:20:40.709764, createdDate=2023-03-01T20:20:40.709764) Student: Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))
LEFT Join
				
					@Test
	@Transactional
	public void left_join() {
		Query query = em.createQuery("Select c, s from Course c LEFT JOIN c.students s");
		List<Object[]> list = query.getResultList();
		logger.info("List size: -> {}", list.size());
		
		for(Object[] result: list) {
			logger.info("Course: {} Student: {}", result[0], result[1]);
		}
	}
				
			
				
					2023-03-01T20:22:31.038+05:30  INFO 384824 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : List size: -> 5
2023-03-01T20:22:31.038+05:30  INFO 384824 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : Course: 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-01T20:22:30.396516, createdDate=2023-03-01T20:22:30.396516) Student: Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))
2023-03-01T20:22:31.071+05:30  INFO 384824 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : Course: 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-01T20:22:30.396516, createdDate=2023-03-01T20:22:30.396516) Student: Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345))
2023-03-01T20:22:31.071+05:30  INFO 384824 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : Course: 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-01T20:22:30.396516, createdDate=2023-03-01T20:22:30.396516) Student: Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))
2023-03-01T20:22:31.071+05:30  INFO 384824 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : Course: Course(id=10002, name=JDBC in 10 steps, reviews=[], students=[], lastUpdatedDate=2023-03-01T20:22:30.398139, createdDate=2023-03-01T20:22:30.398139) Student: null
2023-03-01T20:22:31.072+05:30  INFO 384824 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : Course: 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-01T20:22:30.398423, createdDate=2023-03-01T20:22:30.398423) Student: Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))
Cross Join
				
					@Test
	@Transactional
	public void cross_join() {
		Query query = em.createQuery("Select c, s from Course c, Student s");
		List<Object[]> list = query.getResultList();
		logger.info("List size: -> {}", list.size());
		
		for(Object[] result: list) {
			logger.info("Course: {} Student: {}", result[0], result[1]);
		}
	}
				
			
				
					2023-03-03T16:21:01.511+05:30  INFO 93909 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : List size: -> 9
2023-03-03T16:21:01.511+05:30  INFO 93909 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : Course: 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-03T16:21:00.807627, createdDate=2023-03-03T16:21:00.807627) Student: Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))
2023-03-03T16:21:01.541+05:30  INFO 93909 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : Course: 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-03T16:21:00.807627, createdDate=2023-03-03T16:21:00.807627) Student: Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345))
2023-03-03T16:21:01.541+05:30  INFO 93909 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : Course: 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-03T16:21:00.807627, createdDate=2023-03-03T16:21:00.807627) Student: Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))
2023-03-03T16:21:01.541+05:30  INFO 93909 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : Course: Course(id=10002, name=JDBC in 10 steps, reviews=[], students=[], lastUpdatedDate=2023-03-03T16:21:00.810388, createdDate=2023-03-03T16:21:00.810388) Student: Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))
2023-03-03T16:21:01.542+05:30  INFO 93909 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : Course: Course(id=10002, name=JDBC in 10 steps, reviews=[], students=[], lastUpdatedDate=2023-03-03T16:21:00.810388, createdDate=2023-03-03T16:21:00.810388) Student: Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345))
2023-03-03T16:21:01.543+05:30  INFO 93909 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : Course: Course(id=10002, name=JDBC in 10 steps, reviews=[], students=[], lastUpdatedDate=2023-03-03T16:21:00.810388, createdDate=2023-03-03T16:21:00.810388) Student: Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))
2023-03-03T16:21:01.543+05:30  INFO 93909 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : Course: 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-03T16:21:00.810832, createdDate=2023-03-03T16:21:00.810832) Student: Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))
2023-03-03T16:21:01.545+05:30  INFO 93909 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : Course: 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-03T16:21:00.810832, createdDate=2023-03-03T16:21:00.810832) Student: Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345))
2023-03-03T16:21:01.545+05:30  INFO 93909 --- [           main] c.b.j.h.jpahibernatedemo.JPQLJoins       : Course: 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-03T16:21:00.810832, createdDate=2023-03-03T16:21:00.810832) Student: Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))

Related Tutorials

JPQL Queries

by with No Comment NamedQuery 
				
					<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>3.0.2</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.brains.jpa.hibernate</groupId>
	<artifactId>jpa-hibernate-demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>jpa-hibernate-demo</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>17</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>

</project>

				
			
				
					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<Review> reviews = new ArrayList<>();
	
	@ManyToMany(mappedBy = "courses")
	@Setter(value = AccessLevel.NONE)
	private List<Student> 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<Course> 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<Course> createQuery = em.createQuery("select c from Course c where c.students is empty", Course.class);
		List<Course> 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<Course> createQuery = em.createQuery("select c from Course c where size(c.students) > 2", Course.class);
		List<Course> 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<Course> createQuery = em.createQuery("select c from Course c order by size(c.students) desc", Course.class);
		List<Course> 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<Student> createQuery = em.createQuery("select s from Student s where s.passport.number like '%1234%'", Student.class);
		List<Student> resultList = createQuery.getResultList();
		logger.info("Courses: -> {}", resultList);
	}

}

Related Tutorials

NamedQuery Annotations

by with No Comment NamedQuery

With NamedQuery, we can reuse the query in multiple locations.

				
					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

For writing multiple NamedQuery, we need to use the @NamedQueries

				
					@NamedQueries(
	value = {
		@NamedQuery(name = "query_get_all_courses", query = "Select c from Course c"),
		@NamedQuery(name = "query_get_10_courses", query = "select c from Course c where name like '%10%'")
	}
)
public class Course {
				
			
				
					package com.brains.jpa.hibernate.jpahibernatedemo.entity;

import java.time.LocalDateTime;

import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import jakarta.persistence.NamedQuery;
import jakarta.persistence.Table;
import lombok.AccessLevel;
import lombok.Data;
import lombok.NoArgsConstructor;

@Entity
@Data
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@Table
@NamedQuery(name = "query_get_all_courses", query = "Select c from Course c")
public class Course {
	
	@Id
	@GeneratedValue
	private Long id;
	
	private String name;
	
	@UpdateTimestamp
	private LocalDateTime lastUpdatedDate;
	
	@CreationTimestamp
	private LocalDateTime createdDate;
	
	public Course(String name) {
		this.name = name;
	}
}

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

Use NativeQueries when multiple rows are required to be updated/inserted. Or in case there the JPA is not supported and need to use the database functionality directly.

				
					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.Query;
import jakarta.persistence.TypedQuery;
import jakarta.transaction.Transactional;

@SpringBootTest(classes = JpaHibernateDemoApplication.class)
class JPQLTest {

	@Autowired
	EntityManager em;
	
	private Logger logger = LoggerFactory.getLogger(this.getClass());
	
//	@Test
	void jpql_basic() {
		Query query = em.createNamedQuery("query_get_all_courses");
		List resultList = query.getResultList();
		logger.info("list - jpql_basic: -> {}", resultList);
	}
	
//	@Test
	void jpql_typed() {
		TypedQuery<Course> query = em.createNamedQuery("query_get_all_courses", Course.class);
		List resultList = query.getResultList();
		logger.info("Course - jpql_typed: -> {}", resultList);
	}

//	@Test
	void jpql_where() {
		TypedQuery<Course> query = em.createNamedQuery("query_get_10_courses", Course.class);
		List resultList = query.getResultList();
		logger.info("Named Course: -> {}", resultList);
	}
	
	//@Test
	void native_queries_basic() {
		Query query = em.createNativeQuery("select * from course", Course.class);
		List resultList = query.getResultList();
		logger.info("list - native_basic: -> {}", resultList);
	}
	
	//@Test
	void native_queries_with_param() {
		Query query = em.createNativeQuery("select * from course where id = ?", Course.class);
		query.setParameter(1, 10001L);
		List resultList = query.getResultList();
		logger.info("list - native_basic: -> {}", resultList);
	}
	
//	@Test
	void native_queries_with_named_param() {
		Query query = em.createNativeQuery("select * from course where id = :id", Course.class);
		query.setParameter("id", 10002L);
		List resultList = query.getResultList();
		logger.info("list - native_basic: -> {}", resultList);
	}
	
	@Test
	@Transactional
	void native_queries_to_update() {
		Query query = em.createNativeQuery("update course set last_updated_date = LOCALTIMESTAMP", Course.class);
		int numOfRowsUpdated = query.executeUpdate();
		logger.info("numOfRowsUpdated: -> {}", numOfRowsUpdated);
	}
}

Related Tutorials

JUnit with JPA

by with No Comment JUnit

JUnit test cases for the Repositories

Project Structure for adding Test files

				
					<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>3.0.2</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.brains.jpa.hibernate</groupId>
	<artifactId>jpa-hibernate-demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>jpa-hibernate-demo</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>17</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>

</project>

application.properties

				
					
spring.datasource.url=jdbc:h2:mem:testdb;NON_KEYWORDS=USER;DB_CLOSE_ON_EXIT=FALSE
spring.h2.console.enabled=true
#spring.data.jpa.repositories.bootstrap-mode=default
spring.jpa.defer-datasource-initialization=true

# Turn Statistics ON
spring.jpa.properties.hibernate.generate_statistics=true
logging.level.org.hibernate.stat=debug
logging.level.org.hibernate=debug
# Show all queries
spring.jpa.show-sql=true

# Format the queries
spring.jpa.properties.hibernate.format_sql=true

# Enable seeing the parameters with query
logging.level.org.hibernate.type=trace

data.sql

				
					insert into course(id, name) values(10001, 'JPA in 5 steps');
insert into course(id, name) values(10002, 'JDBC in 10 steps');
insert into course(id, name) values(10003, 'JPQL in 50 steps');
				
			
				
					package com.brains.jpa.hibernate.jpahibernatedemo.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import lombok.AccessLevel;
import lombok.Data;
import lombok.NoArgsConstructor;

@Entity
@Data
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class Course {
	
	@Id
	@GeneratedValue
	private Long id;
	
	private String name;
	
	public Course(String name) {
		this.name = name;
	}

}

EntityManager – An interface to the PersistentContext. PersistentContext keeps tracks of the entities which are changed during transactions.

@Transactional – For making any changes to the database, this annotation needs to be enabled.

  • em.persist(Object) – saves the data, after this all the transactions are automatically saved and stored in the database
  • em.flush() – sends the data to the database
  • em.refresh() – get the data from the database
  • em.clear() – Clears the memory cache of EntityManager
  • em.detach(Object) – Detaches the object from the EntityManager cache memory and changes to the entity are no longer tracked.
				
					package com.brains.jpa.hibernate.jpahibernatedemo.repository;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.brains.jpa.hibernate.jpahibernatedemo.entity.Course;

import jakarta.persistence.EntityManager;
import jakarta.transaction.Transactional;

@Repository
@Transactional
public class CourseRepository {

	private Logger logger = LoggerFactory.getLogger(this.getClass());
	
	@Autowired
	EntityManager em;
	
	public Course findById(Long id) {
		return em.find(Course.class, id);
	}
	
	public void deleteById(Long id) {
		Course course = findById(id);
		em.remove(course);
	}
	
	public Course save(Course course) {
		if(course.getId() == null) {
			//insert
			em.persist(course);
		}else {
			//update
			em.merge(course);
		}
		return course;
	}
	
	public void playWithEntityManager() {
		logger.info("playWithEntityManager - start");
		Course course1 = new Course("AngularJs in 100 Steps");
		em.persist(course1);
		em.flush();

		course1.setName("AngularJs in 100 Steps -- updated");
		em.refresh(course1);
		Course course2 = new Course("Microservices in 50 Steps");
		em.persist(course2);
		em.flush();
		course2.setName("Microservices in 50 Steps -- updated");
		
	}
}

				
			
				
					package com.brains.jpa.hibernate.jpahibernatedemo;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import com.brains.jpa.hibernate.jpahibernatedemo.repository.CourseRepository;

@SpringBootApplication
public class JpaHibernateDemoApplication implements CommandLineRunner {

	public static void main(String[] args) {
		SpringApplication.run(JpaHibernateDemoApplication.class, args);
	}

	private Logger logger = LoggerFactory.getLogger(this.getClass());
	
	@Autowired
	private CourseRepository courseRepository;
	
	@Override
	public void run(String... args) throws Exception {
		// TODO Auto-generated method stub
		logger.info("FindById 10001: -> {}", courseRepository.findById(10001L));
		courseRepository.playWithEntityManager();
	}

}

				
			
				
					package com.brains.jpa.hibernate.jpahibernatedemo;

import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class JpaHibernateDemoApplicationTests {

	@Test
	void contextLoads() {
	}

}

@SpringBootTest(classes = JpaHibernateDemoApplication.class)

This will load the SpringContext of the defined class on application startup.  Run this file as “JUnit Test”.

@DirtiesContext – Resets the database for other methods

				
					package com.brains.jpa.hibernate.jpahibernatedemo.repository;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNull;

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 org.springframework.test.annotation.DirtiesContext;

import com.brains.jpa.hibernate.jpahibernatedemo.JpaHibernateDemoApplication;
import com.brains.jpa.hibernate.jpahibernatedemo.entity.Course;

@SpringBootTest(classes = JpaHibernateDemoApplication.class)
class CourseRepositoryTest {

	private Logger logger = LoggerFactory.getLogger(this.getClass());
	
	@Autowired
	CourseRepository courseRepository;
	
	@Test
	void find_by_id() {
		Course course = courseRepository.findById(10001L);
		assertEquals("JPA in 5 steps", course.getName());
	}
	
	@Test
	@DirtiesContext
	void delete_by_id() {
		courseRepository.deleteById(10002L);
		assertNull(courseRepository.findById(10002L));
	}
	
	@Test
	@DirtiesContext
	void save() {
		// getById 10001
		Course course = courseRepository.findById(10001L);
		assertEquals("JPA in 5 steps", course.getName());
		
		// update course
		course.setName("JPA in 5 steps -- updated");
		courseRepository.save(course);
		// check the value
		assertEquals("JPA in 5 steps -- updated", courseRepository.findById(10001L).getName());
		
		// insert new course
		Course course1 = courseRepository.save(new Course("JPA_JDBC"));
		// check the value
		assertEquals("JPA_JDBC", course1.getName());
	}

}

JPQL – we use entities to make queries.

				
					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.Query;
import jakarta.persistence.TypedQuery;

@SpringBootTest(classes = JpaHibernateDemoApplication.class)
class JPQLTest {

	@Autowired
	EntityManager em;
	
	private Logger logger = LoggerFactory.getLogger(this.getClass());
	
	@Test
	void jpql_basic() {
		Query query = em.createQuery("select c from Course c");
		List resultList = query.getResultList();
		logger.info("list: -> {}", resultList);
	}
	
	// List of fixed type
	@Test
	void jpql_typed() {
		TypedQuery<Course> query = em.createQuery("select c from Course c", Course.class);
		List resultList = query.getResultList();
		logger.info("Course: -> {}", resultList);
	}

    // Query with where clause
	@Test
	void jpql_where() {
		TypedQuery<Course> query = em.createQuery("select c from Course c where name like '%10%'", Course.class);
		List resultList = query.getResultList();
		logger.info("Where Course: -> {}", resultList);
	}

}

Related Tutorials