Hey guys in this post, we will discuss JPQL select query with an example. We will create a spring boot project step by step and connect it to the database. Follow this tutorial till the end to understand about JPQL select query.
JPQL stands for Java Persistence Query Language is a platform-independent object-oriented query language defined as part of the Java Persistence specification. JPQL is used to make queries against entities stored in a relational database.
Table of Contents
Complete example
Let’s create a step-by-step spring boot project and create a JPQL select query. We will write a JPQL query to retrieve the list of laptops for a specific brand.
Note: Of course, we can achieve this using the finder method or query methods but the goal here is to understand how to write JPQL select queries.
Create database and insert sample data
Open MySQL workbench and execute the following commands
CREATE DATABASE mydb;
USE mydb;
CREATE TABLE tbl_laptops(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
description VARCHAR(255) NOT NULL,
brand VARCHAR(255) NOT NULL,
price DOUBLE(10, 2) NOT NULL,
created_at DATE NOT NULL
);
INSERT INTO tbl_laptops(name, description, brand, price, created_at)
VALUES("Dell Inspiron", "dell company laptop", "Dell", 60000.00, '2021-02-10');
INSERT INTO tbl_laptops(name, description, brand, price, created_at)
VALUES("Dell XPS", "dell company laptop", "Dell", 70000.00, '2020-12-31');
INSERT INTO tbl_laptops(name, description, brand, price, created_at)
VALUES("Macbook Air", "apple company laptop", "Apple", 85000.00, '2021-01-31');
INSERT INTO tbl_laptops(name, description, brand, price, created_at)
VALUES("Macbook Pro", "apple company laptop", "Apple", 160000.00, '2021-12-25');
INSERT INTO tbl_laptops(name, description, brand, price, created_at)
VALUES("HP", "hp company laptop", "HP", 50000.00, '2021-02-10');
INSERT INTO tbl_laptops(name, description, brand, price, created_at)
VALUES("Lenovo", "lenovo company laptop", "Lenovo", 50000.00, '2020-11-21');
SELECT * FROM tbl_laptops;
We have created a table tbl_laptops
that contains 5 fields. 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.4.4</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>in.bushansirgur</groupId>
<artifactId>findbyfieldname</artifactId>
<version>v1</version>
<name>findbyfieldname</name>
<description>Spring boot data jpa find by field name</description>
<properties>
<java.version>1.8</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>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</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-boot-starter-web
dependency for building web applications using Spring MVC. It uses the tomcat as the default embedded container.
spring-boot-devtools
dependency for automatic reloads or live reload of applications. spring-boot-starter-data-jpa
dependency is a starter for using Spring Data JPA with Hibernate. lombok
dependency is a java library that will reduce the boilerplate code that we usually write inside every entity class like setters, getters, and toString()
Configure the datasource
spring.datasource.url=jdbc:mysql://localhost:3306/mydb
spring.datasource.username=scbushan05
spring.datasource.password=scbushan05
spring.jpa.hibernate.ddl-auto=update
Create an entity class
Create Laptop.java
inside the in.bushansirgur.springboot.entity
package and add the following content
package in.bushansirgur.springboot.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.Data;
@Entity
@Table(name="tbl_laptops")
@Data
public class Laptop {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String brand;
private String description;
private BigDecimal price;
@Column(name="created_at")
private Date createdAt;
}
We have added @Data
annotation which is a Lombok annotation, that will automatically create setters, getters, toString(), and equals() for us.
@Entity
is a mandatory annotation that indicates that this class is a JPA entity and is mapped with a database table.
@Table
annotation is an optional annotation that contains the table info like table name.
@Id
annotation is a mandatory annotation that marks a field as the primary key
Create a Repository
Create an interface LaptopRepository.java
inside the in.bushansirgur.springboot.repos
package and add the following content
package in.bushansirgur.springboot.repository;
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.springboot.entity.Laptop;
@Repository
public interface LaptopRepository extends JpaRepository<Laptop, Long>{
@Query("FROM Laptop WHERE brand = :brand")
List<Laptop> getLaptopsByBrand (String brand);
}
Inside the repository, we will create a method, we can give any name for this method. This method will accept the brand as a parameter. We should annotate this method with @Query
annotation, and we will write the JPQL query. The JPQL is similar to SQL query but we will use field names instead of column names. The :brand
in the query is the name of the variable, it should match with the parameter in the method.
If you change the method argument name then you need to use @Param
annotation
@Query("FROM Laptop WHERE brand = :brand")
List<Laptop> getLaptopsByBrand (@Param("brand") String brandName);
There is also one more way instead of variable names you can use indexes. Consider the following query
@Query("FROM Laptop WHERE brand = ?1")
List<Laptop> getLaptopsByBrand (String brandName);
Here ?1
is the index of the method argument. Index starts from 1.
Create a Rest controller
Create LaptopController.java
inside the in.bushansirgur.springboot.controller
package and add the following content
package in.bushansirgur.springboot.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;
import in.bushansirgur.springboot.entity.Laptop;
import in.bushansirgur.springboot.repository.LaptopRepository;
@RestController
public class LaptopController {
@Autowired
private LaptopRepository lRepo;
@GetMapping("/laptops/{brand}")
public List<Laptop> getListOfLaptopsByBrand (@PathVariable String brand) {
return lRepo.getLaptopsByBrand(brand);
}
}
Run the app
Run the application using the below maven command –
mvn spring-boot:run
Open the browser and enter the following URL –
localhost:8080/laptops/dell
[
{
"id": 1,
"name": "Dell Inspiron",
"brand": "Dell",
"description": "dell company laptop",
"price": 60000.0,
"createdAt": "2021-02-10"
},
{
"id": 2,
"name": "Dell XPS",
"brand": "Dell",
"description": "dell company laptop",
"price": 70000.0,
"createdAt": "2020-12-31"
}
]