Spring Data JPA - Find Records From MySQL

Spring Data JPA simplifies database interactions in Spring Boot applications by providing a seamless way to work with relational databases like MySQL. It eliminates boilerplate code and allows developers to perform CRUD operations efficiently using JPA repositories. With Spring Boot and Spring Data JPA, fetching records from a MySQL database becomes straightforward, making it an essential approach for building scalable applications. In this article, we will explore how to retrieve a record from a MySQL table using Spring Data JPA’s findById() method.

Note: findById() method of JpaRepository<> is used to retrieve a record by its primary key from the MySQL database.

Step-by-Step Implementation

Step 1: Create a Spring Boot Project

Go to Spring Initializr and generate a Spring Boot project with the following dependencies:

• Spring Web
• Spring Data JPA
• MySQL Driver

Extract the zip file. Now open a suitable IDE and then go to File -> New -> Project from Existing Sources and select pom.xml. Click on import changes on prompt and wait for the project to sync.

Note: In the Import Project for Maven window, make sure you choose the same version of JDK which you selected while creating the project. For database operation we have to configure the Spring application with the database also it is required to add configuration of the database before executing the Spring project. All the configuration is added in the file application.properties of the Spring project. 

Step 2: Add Required Dependencies

Modify the pom.xml file to include the necessary dependencies:

<dependencies>
    <!-- Spring Boot Data JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <!-- Spring Boot Web Starter -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!-- MySQL Connector -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
</dependencies>

Step 3: Configure Database Connection

For database operations, the Spring application must be properly configured with the database. This includes adding the necessary database configurations before executing the Spring project. All required configurations should be specified in the application.properties file of the Spring project.

Add the following properties in src/main/resources/application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/userdb

spring.datasource.username=root

spring.datasource.password=yourpassword

spring.jpa.hibernate.ddl-auto=update

Replace yourpassword with the actual MySQL password. 

Project Structure:

Step 4: Create the Entity Class

Create a User entity in src/main/java/com/example/model/User.java:

import jakarta.persistence.*;

@Entity
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String name;
    
    // Constructors
    public User() {}
    
    public User(String name) {
        this.name = name;
    }

    // Getters and Setters
    public int getId() { 
        return id; 
        
    }
    public String getName() { 
        return name;
        
    }
    public void setName(String name) { 
        this.name = name; 
        
    }
}

Step 5: Create Repository Interface

Define a repository interface in src/main/java/com/example/repository/UserRepository.java:

import org.springframework.data.jpa.repository.JpaRepository;
import com.example.model.User;

public interface UserRepository extends JpaRepository<User, Integer> {}

Step 6: Implement Service Class

Create a service to interact with the database in src/main/java/com/example/service/UserService.java:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.example.model.User;
import com.example.repository.UserRepository;
import java.util.Optional;

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

    public Optional<User> findUserById(int id) {
        return userRepository.findById(id);
    }
}

Step 7: Create Controller for API

Implement a controller to handle HTTP requests in src/main/java/com/example/controller/UserController.java:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import com.example.model.User;
import com.example.service.UserService;
import java.util.Optional;

@RestController
@RequestMapping("/users")
public class UserController {
    @Autowired
    private UserService userService;

    @GetMapping("/{id}")
    public Optional<User> getUserById(@PathVariable int id) {
        return userService.findUserById(id);
    }
}

Step 8: Run the Application 

Start the Spring Boot application using:

mvn spring-boot:run

Output:

Once the application is running, test the API using Postman or a browser:

GET http://localhost:8080/users/1 

Expected Output (JSON Response):

{

"id": 1,

"name": "Aayush"

}