Working with Relational Databases in Spring Boot

In this article, we will explore how to work with relational databases in a Spring Boot application. Relational databases are widely used in enterprise applications to store and query structured data. We will focus on popular databases like MySQL and PostgreSQL and show how to integrate them with a Spring Boot application.

Setting up the Database

To get started, we need to set up our database server. For MySQL or PostgreSQL, you can download and install the respective database server from their official websites. Once installed, create a new database for our Spring Boot application.

Configuring the Database Connection

Next, we need to configure the database connection in our Spring Boot application. Open the application.properties file in your project's src/main/resources folder and provide the necessary properties for the database connection.

For MySQL, the configuration properties would look like this:

spring.datasource.url=jdbc:mysql://localhost:3306/<database-name>
spring.datasource.username=<username>
spring.datasource.password=<password>
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect

For PostgreSQL, the configuration properties would be:

spring.datasource.url=jdbc:postgresql://localhost:5432/<database-name>
spring.datasource.username=<username>
spring.datasource.password=<password>
spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect

Replace <database-name>, <username>, and <password> with the appropriate values for your database setup.

Defining Entity Classes

Now, let's define our entity classes. These classes map to the tables in our database. Annotate each class with @Entity and provide the necessary mappings for the fields using annotations like @Column, @Id, @GeneratedValue, etc.

@Entity
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    // getters and setters
}

Creating a Repository

Next, we need to create a repository interface that extends the JpaRepository interface provided by Spring Data JPA. This interface will provide us with all the necessary CRUD operations for our entity.

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

Performing Database Operations

We can now use the EmployeeRepository to perform various database operations. For example, to save an employee to the database, simply call the save() method on the repository.

@Service
public class EmployeeService {
    private EmployeeRepository employeeRepository;

    public EmployeeService(EmployeeRepository employeeRepository) {
        this.employeeRepository = employeeRepository;
    }

    public Employee save(Employee employee) {
        return employeeRepository.save(employee);
    }

    // other methods for querying and manipulating data
}

Testing the Database Operations

Finally, we can test our database operations by creating a simple API using Spring MVC. Create a REST controller and inject the EmployeeService into it.

@RestController
@RequestMapping("/employees")
public class EmployeeController {
    private EmployeeService employeeService;

    public EmployeeController(EmployeeService employeeService) {
        this.employeeService = employeeService;
    }

    @PostMapping
    public Employee createEmployee(@RequestBody Employee employee) {
        return employeeService.save(employee);
    }

    // other API methods
}

Start the Spring Boot application and use tools like Postman or curl to send API requests and interact with the database.

Conclusion

Working with relational databases in Spring Boot is made easy with the help of Spring Data JPA. We can easily connect to databases like MySQL and PostgreSQL, define entity classes, and perform various database operations using repositories. This enables us to build robust and scalable applications that can handle complex data requirements.