by 0 comments on "SQL Query with JPQL in a Spring Data JPA Project"

SQL Query with JPQL in a Spring Data JPA Project

In this tutorial, you will learn how to run an SQL query in your project, which uses Spring Data JPA Query Methods. You will learn how to annotate the Spring Data JPA Query method so that you can use it to perform a custom SQL query to SELECT, UPDATE or DELETE a record from a database. 

To learn how to write a Native SQL Query with Spring Data JPA, read this tutorial: Spring Data JPA Native SQL Query.

JPQL Query

I will begin with an example that uses JPQL to run an SQL query to select all users who have verified their email addresses. So my SQL query will look like this:

@Query("select users from UserEntity users where users.emailVerificationStatus = 'true'")

we could also write this query this way:

@Query("select u from UserEntity u where u.emailVerificationStatus = 'true'")

IMPORTANT:

In the above query:

UserEntity – is the name of the Entity class, which you can find below in this tutorial. When we create SQL queries with JPQL, we query not the database table by its name but by an Entity class name. This is a very important difference between JPQL SQL Queries and the Native SQL queries. When creating a Native SQL query, we would use a database name in the query, but in the JPQL, we need to use an Entity class name and not the database table name.

emailVerificationStatus  – is the name of the UserEntity class property. If you look at the code of the below UserEntity class, you will see that it has an emailVerificationStatus property name of a String data type.

Since the entity class name is UserEntity, my SQL query looks like the one above.

Below is the UserEntity @Entity class:

User Entity Class

package com.appsdeveloperblog.app.ws.io.entity;

import java.io.Serializable;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;

@Entity
@Table(name="users")
public class UserEntity implements Serializable {
 
 private static final long serialVersionUID = 5313493413859894403L;
 
 @Id
 @GeneratedValue
 private long id;
 
 @Column(nullable=false)
 private String userId;

 @Column(nullable=false, length=50)
 private String firstName;
 
 @Column(nullable=false, length=50)
 private String lastName;
 
 @Column(nullable=false, length=120)
 private String email;
 
 @Column(nullable=false)
 private String encryptedPassword;
 
 private String emailVerificationToken;
 
 @Column(nullable=false)
 private Boolean emailVerificationStatus = false;
 
 @OneToMany(mappedBy="userDetails", cascade=CascadeType.ALL)
 private List<AddressEntity> addresses;

 public long getId() {
  return id;
 }

 public void setId(long id) {
  this.id = id;
 }

 public String getUserId() {
  return userId;
 }

 public void setUserId(String userId) {
  this.userId = userId;
 }

 public String getFirstName() {
  return firstName;
 }

 public void setFirstName(String firstName) {
  this.firstName = firstName;
 }

 public String getLastName() {
  return lastName;
 }

 public void setLastName(String lastName) {
  this.lastName = lastName;
 }

 public String getEmail() {
  return email;
 }

 public void setEmail(String email) {
  this.email = email;
 }

 public String getEncryptedPassword() {
  return encryptedPassword;
 }

 public void setEncryptedPassword(String encryptedPassword) {
  this.encryptedPassword = encryptedPassword;
 }

 public String getEmailVerificationToken() {
  return emailVerificationToken;
 }

 public void setEmailVerificationToken(String emailVerificationToken) {
  this.emailVerificationToken = emailVerificationToken;
 }

 public Boolean getEmailVerificationStatus() {
  return emailVerificationStatus;
 }

 public void setEmailVerificationStatus(Boolean emailVerificationStatus) {
  this.emailVerificationStatus = emailVerificationStatus;
 }

 public List<AddressEntity> getAddresses() {
  return addresses;
 }

 public void setAddresses(List<AddressEntity> addresses) {
  this.addresses = addresses;
 }
 
}

once again, because this entity class is called UserEntity and because we use JPQL and not Native Queries to select all records from a database table of type UserEntity we will need to write our SQL query this way:

@Query("select u from UserEntity u where u.emailVerificationStatus = 'true'")

JPA Repository Interface

Because I am selecting all records from a database table and because there might be thousands of them, I am going to use Paginations and thus, my initial JPA Repository will look like this:

package com.appsdeveloperblog.app.ws.io.repository;
 
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import com.appsdeveloperblog.app.ws.io.entity.UserEntity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

@Repository
public interface UserRepository extends JpaRepository<UserEntity, Long>{
}

the main difference between the regular CrudRepositoty and the one I used above is that in the above repository, I make my interface extend the JpaRepository. If you do not need to support pagination, then you can use a regular CrudRepositoty and JPQL will work.

JPQL Query to Select a Single Entity

Let’s first look at a very simple JPQL Query which selects only one record from our database and matches the value of the user id we pass in as a named parameter.

A bit later in this tutorial, you will also find an example that selects all records from a database table with support for pagination.

Remember that when writing JPQL queries, we use Entity name and Entity class fields instead of a database table name and table column names. 

@Query("select u from UserEntity u where u.userId = :userId")
UserEntity getUserEntityById(@Param("userId") String userId);

as you can see in the query, I am referencing a UserEntity rather than a users database table name. I also give a UserEntity an alias u, which is then needed to be used in all other parts of a query.

and here is a unit test to test this query.

@Test 
public void testGetUserEntityById()
{
    String userId = "1a2b3c";
    UserEntity user = userRepository.getUserEntityById(userId);
    
    assertNotNull(user);
    assertTrue(user.getUserId().equals(userId));
}

JPQL SELECT Query with Pagination

Let’s add a Query method to select all users and add support for pagination.

package com.appsdeveloperblog.app.ws.io.repository;
 
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import com.appsdeveloperblog.app.ws.io.entity.UserEntity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

@Repository
public interface UserRepository extends JpaRepository<UserEntity, Long>{

  @Query("select users from UserEntity users where users.emailVerificationStatus = 'true'", 
         countQuery="select count(users) from UserEntity users where users.emailVerificationStatus = 'true'")
  Page<UserEntity> findAllUsersWithConfirmedEmailAddress( Pageable pageableRequest );

}

To perform the JPQL SQL query, I will need to:

  1. Annotate my Spring Data JPA Query Method with a @Query annotation,
  2. In the SQL query itself, I need to SELECT FROM <Entity Class Name>,
  3. In the WHERE clause, I need to use <Entity Class Property Field Name>. Have a look at the above UserEntity class. It has a property field called “emailVerificationStatus” of a String data type.
  4. The countQuery inside of @Query annotation is used only because the above example uses Pagination. For cases that do not need to use Pageable request and Pagination, there is no need to provide the countQuery.

And this is it. To test the above code, you can use the below JUnit test case:

Run JUnit Test Case to See How It Works

The below code example uses @SpringBootTest annotation to execute an integration test. There are many JUnit tutorials on this site. But if you want to learn how to test Data Layer in Spring Boot application, there is a very detailed video course for beginners called “Testing Java with JUnit & Mockito“.

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.appsdeveloperblog.app.ws.mobileappws.repository;

import com.appsdeveloperblog.app.ws.io.entity.AddressEntity;
import com.appsdeveloperblog.app.ws.io.entity.UserEntity;
import com.appsdeveloperblog.app.ws.io.repository.UserRepository;
import com.appsdeveloperblog.app.ws.shared.dto.UserDto;
import com.appsdeveloperblog.app.ws.shared.utils.UserProfileUtils;
import java.util.ArrayList;
import java.util.List;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import static org.mockito.ArgumentMatchers.anyInt;
import static org.mockito.ArgumentMatchers.anyString;
import org.mockito.Mock;
import static org.mockito.Mockito.when;
import org.mockito.MockitoAnnotations;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.springframework.test.context.junit4.SpringRunner;

@RunWith(SpringRunner.class)
@SpringBootTest
public class JPQLTests {

    @Autowired
    UserRepository userRepository;
    
    @Mock
    UserProfileUtils userProfileUtils;

    @Mock
    BCryptPasswordEncoder bCryptPasswordEncoder;
    
    UserEntity userEntity;
    UserDto userDto;

    @Before
    public void setUp() {
        MockitoAnnotations.initMocks(this);
        
         // Prepare User Addresses
        AddressEntity addressEntity = new AddressEntity();
        addressEntity.setType("shipping");
        addressEntity.setAddressId("ahgyt74hfy");
        addressEntity.setCity("Vancouver");
        addressEntity.setCountry("Canada");
        addressEntity.setPostalCode("ABCCDA");
        addressEntity.setStreetAddress("123 Street Address");

        List<AddressEntity> addresses = new ArrayList<>();
        addresses.add(addressEntity);
 

        // Prepare User Entity
        userEntity = new UserEntity();
        userEntity.setFirstName("Sergey");
        userEntity.setLastName("Kargopolov");
        userEntity.setId(1L);
        userEntity.setUserId("1a2b3c");
        userEntity.setEncryptedPassword("xxx");
        userEntity.setAddresses(addresses);
        userEntity.setEmail("[email protected]");
        userEntity.setEmailVerificationStatus(true);

        String generatedId = "xxx";
        String emailToken = "aaa";

        // Mock method calls
        when(bCryptPasswordEncoder.encode(anyString())).thenCallRealMethod();

        when(userProfileUtils.generateId(anyInt())).thenReturn(generatedId);

        when(userProfileUtils.generateEmailVerificationToken(anyString())).thenReturn(emailToken);
        
        userRepository.save(userEntity);
    }

    @Test
    public void testGetVerifiedUsers() {
        Pageable pageableRequest = PageRequest.of(0, 10);
        Page<UserEntity> users = userRepository.findAllUsersWithConfirmedEmailAddress(pageableRequest);

        assertNotNull(users);

        List<UserEntity> userEntities = users.getContent();
        assertNotNull(userEntities);
        assertTrue(userEntities.size() == 1);

    }
}

I hope this tutorial was helpful to you. To learn more about building RESTful Web Services that use Spring Data JPA, please check my other tutorials following this link: RESTful Web Services with Spring MVC

And if you are interested in video lessons that teach Spring Data JPA and JPQL, have a look at the below list of video courses:

Related Posts:

Powered by Contextual Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *