Spring Boot Backend Development Course, Practicing Database Manipulation with SQL Statements

Spring Boot is a powerful framework for developing web applications based on Java. In this course, we will cover backend development using Spring Boot and learn how to manipulate databases using SQL statements.

Table of Contents

• 1. Introduction to Spring Boot
• 2. Project Setup
• 3. Database Integration
• 4. Manipulating Data with SQL Statements
• 5. Implementing CRUD
• 6. Testing and Deployment
• 7. Conclusion

1. Introduction to Spring Boot

Spring Boot is a lightweight application development framework based on the Spring framework. It simplifies configuration and helps to easily build stand-alone applications.

Characteristics of Spring Boot include:

• Auto Configuration
• Embedded Web Server
• Production Ready

2. Project Setup

The best way to create a Spring Boot project is to use the Spring Initializr website. Here, you can enter project metadata and add the necessary dependencies.

1. Visit Spring Initializr: https://start.spring.io/
2. Select Project: Maven Project or Gradle Project
3. Language: Java
4. Group: com.example
5. Artifact: demo
6. Dependencies: Spring Web, Spring Data JPA, H2 Database

3. Database Integration

To integrate a database with Spring Boot, you need to set database information in the application.properties file.

# H2 Database Configuration
spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

4. Manipulating Data with SQL Statements

We will learn how to manipulate data in the database using SQL statements. Basic SQL statements include SELECT, INSERT, UPDATE, DELETE.

4.1 SELECT Statement

The SELECT statement is used to retrieve data from the database. For example, you can use the following query to retrieve all users:

SELECT * FROM users;

4.2 INSERT Statement

The INSERT statement is used to add new data to the database. The following query adds a new user:

INSERT INTO users (username, email) VALUES ('testuser', 'test@example.com');

4.3 UPDATE Statement

The UPDATE statement is used to modify existing data. An example of changing a specific user’s email is as follows:

UPDATE users SET email = 'newemail@example.com' WHERE username = 'testuser';

4.4 DELETE Statement

The DELETE statement is used to delete data from the database. The query to delete a user is as follows:

DELETE FROM users WHERE username = 'testuser';

5. Implementing CRUD

Implementing CRUD (Create, Read, Update, Delete) functionality is essential for database manipulation. We will set up Repository, Service, and Controller to implement CRUD in Spring Boot.

5.1 Repository

The Repository defines interactions with the database. Using JPA, you create an interface, and basic CRUD methods are automatically provided:

import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User, Long> {
    // Additional query methods can be defined here.
}

5.2 Service

The Service handles business logic. It injects the Repository to implement the necessary functionalities:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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

    public User createUser(User user) {
        return userRepository.save(user);
    }

    // Add other CRUD methods
}

5.3 Controller

The Controller handles HTTP requests. It sets up appropriate endpoints to manage communication with the client:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

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

    @PostMapping
    public User createUser(@RequestBody User user) {
        return userService.createUser(user);
    }

    // Add other CRUD endpoints
}

6. Testing and Deployment

Testing is essential for discovering and fixing bugs in the application. In Spring Boot, you can perform unit testing using JUnit.

import org.junit.jupiter.api.Test;
import static org.assertj.core.api.Assertions.assertThat;

public class UserServiceTest {
    @Test
    public void testCreateUser() {
        // User creation test code
    }
}

7. Conclusion

In this course, we explored backend development using Spring Boot and how to manipulate databases using SQL statements. Through this course, we hope you will be equipped to build practical web applications.