MySql connection with Spring boot using JPA

Introduction

Spring Boot is a popular Java-based framework that provides a rapid development environment for building web applications. One of the most important components of any web application is a database. In this blog, we will discuss how to establish an SQL connection in Spring Boot with JPA.

SQL Connection with Spring Boot

Spring Boot provides support for the Java Persistence API (JPA), which is a specification for accessing, managing, and persisting data between Java objects and relational databases. JPA simplifies the development of database-driven applications by providing an object-oriented interface to the underlying database.

To establish an SQL connection with Spring Boot and JPA, we need to follow these steps:

1. Add the required dependencies We need to add the required dependencies in the pom.xml file of our Spring Boot project. Here are the dependencies that we need to add for MySQL.

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <scope>runtime</scope>
</dependency>

2. Now configure the database connection details, we need to configure the database connection details in the application.properties or application.yml file, which is located in the src/main/resources directory of our Spring Boot project. Here is an example of how to configure the MySQL database connection:

spring.datasource.url=jdbc:mysql://localhost:3306/mydb
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

3. Create an Entity class An entity class represents a table in the database. We need to create an entity class for each table in the database. Here is an example of an entity class for a table named “employee”.

@Entity
@Table(name = "employee")
public class Employee {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
 
    @Column(name = "name")
    private String name;
 
    @Column(name = "age")
    private Integer age;
 
    // Getters and setters
}

4. Create a Repository interface A repository interface defines the CRUD (Create, Read, Update, Delete) operations for an entity. We need to create a repository interface for each entity class. Here is an example of a repository interface for the “employee” entity.

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
}

Why do we use JpaRepostory?

JpaRepository provides some additional methods over and above the CrudRepository methods for CRUD (Create, Read, Update, Delete) operations, and also provides functionality for pagination and sorting of data. It allows us to write queries using method names and provides a way to execute native SQL queries as well.

Furthermore, JpaRepository is very flexible and customizable. It allows us to use a variety of different data sources and configure how we want our data to be accessed and manipulated.

5. Use the Repository interface in a Service class A service class contains the business logic of our application. We need to use the repository interface in a service class to interact with the database. Here is an example of a service class that uses the “employee” repository.

@Service
public class EmployeeService {
 
    @Autowired
    private EmployeeRepository employeeRepository;
 
    public Employee saveEmployee(Employee employee) {
        return employeeRepository.save(employee);
    }
 
    public List<Employee> getAllEmployees() {
        return employeeRepository.findAll();
    }
 
    public Employee getEmployeeById(Long id) {
        return employeeRepository.findById(id).orElse(null);
    }
 
    public void deleteEmployee(Long id) {
        employeeRepository.deleteById(id);
    }
}

6. After creating the service class now work on the controller to define all the REST endpoints.

@RestController
@RequestMapping("/employees")
public class EmployeeController {
    
    @Autowired
    private EmployeeService employeeService;
    
    @PostMapping("")
    public Employee saveEmployee(@RequestBody Employee employee) {
        return employeeService.saveEmployee(employee);
    }
    
    @GetMapping("")
    public List<Employee> getAllEmployees() {
        return employeeService.getAllEmployees();
    }
    
    @GetMapping("/{id}")
    public Employee getEmployeeById(@PathVariable Long id) {
        return employeeService.getEmployeeById(id);
    }
    
    @DeleteMapping("/{id}")
    public void deleteEmployee(@PathVariable Long id) {
        employeeService.deleteEmployee(id);
    }
}

7. After writing complete code our code structure looks like this.

8. After our connection is established our stacktrace looks like this.

Conclusion

In this blog, we discussed how to establish an SQL connection in Spring Boot with JPA. We followed the steps of configuring the database connection details, adding the required dependencies, creating an entity class, creating a repository interface, and using the repository interface in a service class. With these steps, we can easily interact with the database in our Spring Boot applications.

For a more technical blog, you can refer to the Knoldus blog: https://blog.knoldus.com/