Spring Boot CrudRepository Example with custom query

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 and limitations of CrudRepository.

➔ CrudRepository is a Spring Data interface that provides generic CRUD (Create, Read, Update, Delete) operations for entities.

➔ CrudRepository allows developers to perform basic operations to interact with the database without writing boilerplate code.

➔ Developers simply need to extend this interface and specify the entity type and ID type.

➔ The core methods it offers like save(), saveAll(), findById(), findAll(), deleteById(), deleteAll(), count().

➔ CrudRepository can be used for simple crud applications where basic CRUD functionalities are most common.

➔ This does not suppot batch delete, flush operation, pagination and sorting but can delete entity one at a time.

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 built-in methods)

package com.exam.sbspringdatamysql.repository;

import com.exam.sbspringdatamysql.entity.User;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface UserRepository extends CrudRepository<User,Long> {
}

CrudRepository code (with query methods that follow the standard naming conventions of Spring JPA. )

Spring JPA can automatically generate SQL queries based on the method names of the repository interface, and these methods are known as query methods or derived methods.

package com.exam.sbspringdatamysql.repository;

import com.exam.sbspringdatamysql.entity.User;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserRepository extends CrudRepository<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);
}

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

package com.exam.sbspringdatamysql.repository;

import com.exam.sbspringdatamysql.entity.User;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserRepository extends CrudRepository<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);
}

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, 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.stereotype.Service;

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

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

    public List<User> fetchUsers() {
        return (List<User>) 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);
    }
}

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.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 + "%");
    }

}