Spring Boot JpaRepository Example using MySQL

Spring Boot and Data Access.

➔ Spring Boot offers many benefits to developers and creates a production-ready environment for application development without the hassle of cumbersome application configuration and setup.

➔ Spring Boot no longer requires boilerplate configuration code and it helps developers focus on business development and solving business problems.

➔ Spring Boot is built on its core Spring Framework and this is why all the core features of Spring are also available in Spring Boot.

➔ Spring Boot has become very popular among developers these days due to its fast application development speed and developer-friendly features. For example, using the CrudRepository interface, developers can perform CRUD operations without writing any database persistence code.

Spring Data JPA.

➔ Spring Data JPA is an essential tool in the Spring ecosystem that simplifies database interaction and data access.

➔ Spring JPA (Java Persistence API) is a module of Spring Data that works with Spring Boot and abstracts the complexities of database interaction by providing a simple and integrated approach to data persistence.

➔ Spring Data JPA uses JPA as the standard API and Hibernate as the default implementation of object-relational mapping (ORM) to simplify data manipulation and persistence.

Features of JpaRepository.

➔ JpaRepository is a Spring Data JPA interface that provides generic and advanced data access object (DAO) facilities for entities.

➔ JpaRepository extends CrudRepository and PagingAndSortingRepository, which enables automatic creation of database tables for entity models and provides database access functionality and pagination/sorting functionality for Java objects without any boilerplate code.

➔ The core methods it provides are save(), saveAll(), findById(), findAll(), deleteById(), deleteAll(), count() as well as flush(), saveAndFlush(), saveAllAndFlush(), deleteAllInBatch().

➔ Spring Data JPA automatically generates queries at runtime using the names of standard methods contained in the interface definition.

➔ Spring Data defines query methods following JPA naming conventions; for example, if a Java property is named username, the method name would be findByUsername().

➔ Spring Data JPA supports custom queries using the @Query annotation for both JPQL (Jakarta Persistence Query Language) and native SQL (Structured Query Language).

➔ JpaRepository can be used in simple and complex enterprise applications where basic CRUD functionality, paging, sorting, and read-heavy operations are most common.

➔ It supports pagination and sorting, batch delete, flush operations, automated queries, custom queries, and more.

Creating a Spring Boot project using MySQL database and IntelliJ IDEA..

Project dependencies pom.xml file:

➔ pom.xml (Project Object Model) is the main configuration file for Maven-based Spring Boot applications, which defines project metadata, manages project dependencies, and configures the build process.

➔ The pom.xml file must be in the root directory of your project and is an ideal location for Maven-based applications.

code

<?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>4.0.5</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.exam</groupId>
    <artifactId>sbspringdatamysql</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sbspringdatamysql</name>
    <description>sbspringdatamysql</description>
    <url/>
    <licenses>
        <license/>
    </licenses>
    <developers>
        <developer/>
    </developers>
    <scm>
        <connection/>
        <developerConnection/>
        <tag/>
        <url/>
    </scm>
    <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-webmvc</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-webmvc-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

Spring Boot application.properties file:

➔ To establish a database connection and perform data access operations, a Spring Boot application needs to configure MySQL database properties and Hibernate properties through its application.properties file.

➔ Most of the entries in the application.properties file are self-explanatory.

Step 04: Spring Boot application.properties

code

spring.application.name=sbspringdatamysql
# If the project cannot be run due to port conflicts, change the server port.
server.port=8090
# Spring DATASOURCE DataSourceProperties
spring.datasource.url = jdbc:mysql://localhost:3306/demodb
spring.datasource.username = root
spring.datasource.password = mysql123
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# Hibernate Properties
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQLDialect
# Hibernate ddl-auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = update

Organize code into packages:

➔ The project code can be organized into packages according to function, such as the models, repositories, services, controllers listed below, and some classes and interfaces can be created inside these packages.

➔ Using packages to organize coding files in a project is a good programming practice.

05 Spring Boot Project structure (create packages)

Entity or Model class:

➔ In Spring Boot, the terms 'Model' and 'Entity' are often used to describe data-holding classes, but their function can be described slightly differently.

➔ Entity: An Entity is a Java class that is directly mapped to a database table, and the main function of the Spring Data JPA layer is to represent the data in the database table.

➔ Model: Model is a broad term for classes that represent data for business logic or user interfaces, and these classes are used to exchange data between the frontend or backend, or both.

Step 06: Entity class User.java

code

package com.exam.sbspringdatamysql.entity;

import jakarta.persistence.*;

@Entity
@Table(name = "user")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long userId;
    private String name;
    private String email;

    public User() {
    }

    public User(Long userId) {
        this.userId = userId;
    }

    public User(Long userId, String name, String email) {
        this.userId = userId;
        this.name = name;
        this.email = email;
    }

    public Long getUserId() {
        return userId;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

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

    @Override
    public String toString() {
        return "User{" +
                "userId=" + userId +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}

CrudRepository

➔ CrudRepository is a Java Persistence API (JPA) interface.

➔ CrudRepository has full API support for basic CRUD operations, including methods like save(S entity), findById(ID id), findAll(), deleteById(ID id), etc.

➔ In addition to the basic predefined standard queries, it is also possible to implement custom queries using the query method naming convention or the @Query annotation.

Step 07: Repository interface UserRepository.java

code (with derive methods and custom query methods using JPQL or Native SQL, no need to follow the standard naming conventions of Spring JPA for custom methods.)

package com.exam.sbspringdatamysql.repository;

import com.exam.sbspringdatamysql.entity.User;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    // Query method, Find users by name
    //SELECT * FROM user WHERE name = :name
    List<User> findByName(String name);

    // Query method, Find users whose name contains a keyword
    List<User> findByNameContaining(String keyword);

    // Query method, Find all users with name containing a keyword
    List<User> findByNameLike(String keyword);

    // Query method, Find users by name and email
    // SELECT * FROM user  WHERE name = :name AND email = :email
    List<User> findByNameAndEmail(String name, String email);

    // Query method, Find all students and sort by name
    List<User> findAllByEmail(String email);



    //Custom Query, JPQL to find users by name
    @Query("SELECT u FROM User u WHERE u.name = :name")
    List<User> jpaQueryByName(@Param("name") String name);

    //Custom Query, JPQL  to find users with names containing a keyword
    @Query("SELECT u FROM User u WHERE u.name LIKE :keyword")
    List<User> jpaQueryByNameLike(@Param("keyword") String keyword);


    //Custom Native Query, SQL to find users by name
    @Query(value = "SELECT * FROM user WHERE name = :name", nativeQuery = true)
    List<User> nativeQueryByName(@Param("name") String name);

    //Custom Native Query, SQL  to find users with names containing a keyword
    @Query(value = "SELECT * FROM user u WHERE u.name LIKE :keyword", nativeQuery = true)
    List<User> nativeQueryByNameLike(@Param("keyword") String keyword);

    //Custom Query, JPQL  to find users by names and sort
    @Query("SELECT u FROM User u WHERE u.name LIKE :keyword")
    List<User> findByNameSort(String keyword, Sort sort);

    //Custom Query, JPQL  to find users by names and pagination
    @Query("SELECT u FROM User u WHERE u.name LIKE :keyword")
    List<User> findByNamePagination(String keyword, Pageable pageable);

    //Custom Query, JPQL  to find all users and implement pagination.
    @Query("SELECT u FROM User u")
    List<User> findAllPagination(Pageable pageable);
}

JPA Parameter placeholder, Named Parameters vs Positional Parameters.

➔ In JPA (Java Persistence API) and Spring Data JPA, question marks (?) and colons (:) are used within the query string (@Query) to represent placeholders for parameters.

➔ This helps pass dynamic values to queries at runtime and provides protection against SQL injection, as well as making queries reusable in programs.

Colon (:name) for Named Parameters

➔ This refers to a parameter by name rather than its position, and a unique identifier (e.g., :name, :email, :id) is used after a colon.

Question Mark (?1, ?2) for Positional Parameters.

➔ It binds the parameters in the query string in the order they appear. For example, the first ?1 corresponds to the first method argument, the second ?2 corresponds to the second method argument, and so on.

➔ An integer index starting from 1 is used after the question mark (e.g., ?1, ?2).

Service Layer

➔ Process business logic and core application rules.

➔ Works with domain models, DTOs, Entities, and Java objects.

➔ Calls the repository layer to process data.

Step 08: Service class UserService.java

code

package com.exam.sbspringdatamysql.service;

import com.exam.sbspringdatamysql.entity.User;
import com.exam.sbspringdatamysql.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Optional;

@Service
public class UserService {
    @Autowired
    private UserRepository repository;

    public List<User> fetchUsers() {
        return repository.findAll();
    }

    public Optional<User> fetchUsersById(Long id) {
        return repository.findById(id);
    }

    public User insertUser(User user) {
        return repository.save(user);
    }

    public User editUser(User user) {
        return repository.save(user);
    }

    public String removeUser(Long id) {
        repository.deleteById(id);
        return "Deleted Successfully";
    }

    //Query methods
    public List<User> fetchByName(String name) {
        return repository.findByName(name);
    }

    //Query methods
    public List<User> fetchByNameContaining(String name) {
        return repository.findByNameContaining(name);
    }

    //Query methods
    public List<User> fetchByNameLike(String name) {
        return repository.findByNameLike(name);
    }

    //Custom Query, JPQL
    public List<User> jpaQueryfetchByNameLike(String name) {
        return repository.jpaQueryByNameLike(name);
    }

    //Custom Native Query, SQL
    public List<User> nativeQueryfetchByNameLike(String name) {
        return repository.nativeQueryByNameLike(name);
    }

    //Custom Query, JPQL for sort
    public List<User> fetchByNameSort(String name, Sort sort) {
        return repository.findByNameSort(name, sort);
    }

    //Custom Query, JPQL for pagination
    public List<User> fetchByNamePagination(String name, Pageable pageable) {
        return repository.findByNamePagination(name, pageable);
    }

    //Custom Query, JPQL for pagination
    public List<User> fetchUsersPagination(Pageable pageable) {
        return repository.findAllPagination(pageable);
    }
}

Controller Layer

➔ Handles HTTP requests and communicates with the presentation layer.

➔ Works with HTTP Requests, RequestParam, PathVariable, and JSON.

➔ The controller calls the service layer to process the data.

Step 09: Controller class UserController.java

code

package com.exam.sbspringdatamysql.controller;

import com.exam.sbspringdatamysql.entity.User;
import com.exam.sbspringdatamysql.service.UserService;
import jakarta.validation.Valid;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Optional;

@RestController
@RequestMapping("api")
public class UserController {
    @Autowired
    private UserService service;

    // Insert operation
    @PostMapping("/user")
    public User insertUser(@Valid @RequestBody User user) {
        return service.insertUser(user);
    }

    //Fetch operation
    @GetMapping("/user")
    public List<User> fetchUsers() {
        return service.fetchUsers();
    }

    //Fetch operation
    @GetMapping("/user/{id}")
    public User fetchUsersById(@PathVariable Long id) {
        Optional<User> user = service.fetchUsersById(id);
        if (user.isPresent()) {
            return user.get();
        }
        return null;
    }

    //Edit operation
    @PutMapping("user/{id}")
    public User editUser(@PathVariable Long id, @RequestBody User frontendUser) {
        Optional<User> user = service.fetchUsersById(id);
        if (user.isPresent()) {
            User backendUser = user.get();
            backendUser.setName(frontendUser.getName());
            backendUser.setEmail(frontendUser.getEmail());
            return service.editUser(backendUser);
        }
        return null;
    }

    //Remove operation
    @DeleteMapping("user/{id}")
    public String removeUser(@PathVariable Long id) {
        return service.removeUser(id);
    }


    //Use Query methods here
    @GetMapping("/user/name/{name}")
    public List<User> fetchUsersByName(@PathVariable String name) {
        return service.fetchByName(name);
    }

    //Use Query methods here
    @GetMapping("/user/containing/{name}")
    public List<User> fetchUsersByNameContaining(@PathVariable String name) {
        return service.fetchByNameContaining(name);
    }

    //Use Query methods here
    @GetMapping("/user/like/{name}")
    public List<User> fetchUsersByNameLike(@PathVariable String name) {
        return service.fetchByNameLike("%" + name + "%");
    }

    //Use Custom Query, JPQL
    @GetMapping("/user/jpqllike/{name}")
    public List<User> jpaQueryfetchByNameLike(@PathVariable String name) {
        return service.jpaQueryfetchByNameLike("%" + name + "%");
    }

    //Use Custom Native Query, SQL
    @GetMapping("/user/sqllike/{name}")
    public List<User> nativeQueryfetchByNameLike(@PathVariable String name) {
        return service.nativeQueryfetchByNameLike("%" + name + "%");
    }


    //Use Custom Query, JPQL for sort Ascending
    @GetMapping("/user/jpqlsorta/{name}")
    public List<User> fetchByNameSortAsc(@PathVariable String name) {
        Sort sort = Sort.by(Sort.Direction.ASC, "name");
        return service.fetchByNameSort("%" + name + "%", sort);
    }
    //Use Custom Query, JPQL for sort Descending
    @GetMapping("/user/jpqlsortd/{name}")
    public List<User> fetchByNameSortDesc(@PathVariable String name) {
        Sort sort = Sort.by(Sort.Direction.DESC, "name");
        return service.fetchByNameSort("%" + name + "%", sort);
    }

    //Use Custom Query, JPQL for Pagination
    @GetMapping("/user/jpqlpagination/{name}")
    public List<User> fetchByNamePagination(@PathVariable String name) {
        Pageable pageable = PageRequest.of(0, 10);
        return service.fetchByNamePagination("%" + name + "%", pageable);
    }

    //Use Custom Query, JPQL for Pagination, pagesize>0 given at runtime
    @GetMapping("/user/pagination/{pageSize}")
    public List<User> fetchUsersPagination(@PathVariable Integer pageSize) {
        Pageable pageable = PageRequest.of(0, pageSize);
        return service.fetchUsersPagination(pageable);
    }


}