Hey guys in this article, you will learn how to write a JPQL query for the Ending With condition in the Spring Data JPA repository.
Read More:
- Check the Complete JavaServer Faces (JSF) Tutorial
- Check the Spring Boot JdbcTemplate Tutorials
- Check the Complete Spring Boot and Data JPA Tutorials
- Check the Complete Spring MVC Tutorials
- Check the Complete JSP Tutorials
- Check the Complete Spring Boot Tutorials [100+ Examples]
- Check the Complete Spring Boot and Thymeleaf Tutorial
- Check the Complete AWS Tutorial
- Check the Complete JavaServer Faces (JSF) Tutorial
- Check the Complete Spring Data JPA Tutorial
- Check the Complete Spring Security Tutorial
- Check the Javascript Projects for Beginners
JPQL stands for Java persistence query language defined in JPA specification. It is used to create queries against entities to store in a relational database. JPQL is developed based on SQL syntax. Behind the scenes Data JPA convert these JPQL queries into native SQL queries.
Scenario: Consider the Employee entity class and if we want to retrieve the employees by their name which contains the letter/keyword ‘th’ then here is the Spring data JPA JPQL query:
@Query("FROM Employee WHERE name like :keyword")
List<Employee> getEmployeesByNameContaining(String keyword);
@Query
annotation to write the JPQL query. We use field names instead of column names and Entity name instead of table name.
Another way of writing JPQL query:
@Query("FROM Employee WHERE name like ?1")
List<Employee> getEmployeesByNameContaining(String keyword);
Table of Contents
Complete Example
We will create this example step by step, follow this tutorial till the end
Create database and insert sample data
Open MySQL workbench and execute the following commands
CREATE DATABASE demodb;
USE demodb;
CREATE TABLE `tbl_employees` (
`id` bigint NOT NULL AUTO_INCREMENT,
`age` bigint DEFAULT NULL,
`department` varchar(255) DEFAULT NULL,
`location` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`salary` decimal(19,2) DEFAULT NULL,
`joining_date` date DEFAULT NULL,
`last_date` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO tbl_employees
(age, department, location, name, salary, joining_date, last_date)
VALUES
(24, "IT", "India", "Bushan", 20000.00, "2019-01-01", "2022-01-10");
INSERT INTO tbl_employees
(age, department, location, name, salary, joining_date, last_date)
VALUES
(28, "Sales", "India", "Shankar", 20000.00, "2018-05-31", "2020-10-25");
INSERT INTO tbl_employees
(age, department, location, name, salary, joining_date, last_date)
VALUES
(32, "Marketing", "Australia", "Ramesh", 20000.00, "2020-01-10", "2021-10-15");
INSERT INTO tbl_employees
(age, department, location, name, salary, joining_date, last_date)
VALUES
(33, "IT", "Canada", "Bharath", 20000.00, "2020-06-10", "2021-05-31");
INSERT INTO tbl_employees
(age, department, location, name, salary, joining_date, last_date)
VALUES
(25, "IT", "China", "Chethan", 20000.00, "2017-09-05", "2020-12-31");
INSERT INTO tbl_employees
(age, department, location, name, salary, joining_date, last_date)
VALUES
(25, null, "China", "Sachin", 20000.00, "2017-09-05", "2020-12-31");
We have created the table tbl_employees
. We have inserted the sample data as well.
Create spring boot project
There are many different ways to create a spring boot application, you can follow the below articles to create one –
>> Create spring boot application using Spring initializer
>> Create spring boot application in Spring tool suite [STS]
>> Create spring boot application in IntelliJ IDEA
Add maven dependencies
Open pom.xml
and add the following dependencies –
<?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>2.5.4</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>in.bushansirgur</groupId>
<artifactId>springbootdemo</artifactId>
<version>1.0.0</version>
<name>springbootdemo</name>
<description>Spring boot demo</description>
<properties>
<java.version>11</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
Configure the datasource
Open application.properties
file and add the following contents
spring.datasource.url=jdbc:mysql://localhost:3306/demodb
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.hibernate.ddl-auto=update
Create entity class
Create Employee.java
class in.bushansirgur.springsecurityjwt.entity
package and add the following content
package in.bushansirgur.springbootdemo.entity;
import java.math.BigDecimal;
import java.sql.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Entity
@Table(name = "tbl_employees")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String location;
private String department;
private Long age;
private BigDecimal salary;
@Column(name = "joining_date")
private Date joinedDate;
@Column(name = "last_date")
private Date lastDate;
}
Create Repository class
Create UserRepository.java
class in.bushansirgur.springsecurityjwt.repository
package and add the following content
package in.bushansirgur.springbootdemo.repository;
import java.sql.Date;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import in.bushansirgur.springbootdemo.entity.Employee;
@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long>{
@Query("FROM Employee WHERE name like :keyword")
List<Employee> getEmployeesByNameContaining(String keyword);
}
Call repository method from main() method
In order to call the repository method that we have created, we gonna use CommandLineRunner.run()
method to call the repository method. Once the application starts, spring boot will execute the following code –
package in.bushansirgur.springbootdemo;
import java.sql.Date;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import in.bushansirgur.springbootdemo.entity.Employee;
import in.bushansirgur.springbootdemo.repository.EmployeeRepository;
@SpringBootApplication
public class SpringbootdemoApplication implements CommandLineRunner{
@Autowired
private EmployeeRepository employeeRepository;
public static void main(String[] args) {
SpringApplication.run(SpringbootdemoApplication.class, args);
}
@Override
public void run(String... args) throws Exception {
List<Employee> list = employeeRepository.getEmployeesByNameContaining("%th%");
System.out.println("----------------List of Employees---------------------");
list.forEach(e -> {
System.out.println(e);
});
}
}
Run the app
Run the application using the below maven command –
mvn spring-boot:run
Once Spring boot application execution completes, you can able to see generated SQL statements in a console and the distinct values
Hibernate:
select
employee0_.id as id1_0_,
employee0_.age as age2_0_,
employee0_.department as departme3_0_,
employee0_.joining_date as joining_4_0_,
employee0_.last_date as last_dat5_0_,
employee0_.location as location6_0_,
employee0_.name as name7_0_,
employee0_.salary as salary8_0_
from
tbl_employees employee0_
where
employee0_.name like ?
----------------List of Employees---------------------
Employee(id=24, name=Bharath, location=Canada, department=IT, age=33, salary=20000.00, joinedDate=2020-06-10, lastDate=2021-05-31)
Employee(id=25, name=Chethan, location=China, department=IT, age=25, salary=20000.00, joinedDate=2017-09-05, lastDate=2020-12-31)
References
That’s it for this post, if you like this post, share this with your friends and colleagues or you can share this within your social media platform. Thanks, I will see you in our next post.