Top 10 Spring Boot REST API with SQL Interview Questions

Mastering Spring Boot for building RESTful APIs connected to SQL databases is a critical skill for software developers. Spring Boot’s simplicity, combined with its seamless database integrations, allows developers to create reliable and efficient APIs quickly. To help you prepare for interviews or sharpen your skills, here are the Top 10 Spring Boot REST API with SQL Interview Questions with comprehensive answers and practical examples.

Table of Contents

1. Designing a RESTful Controller in Spring Boot
2. Connecting REST API to PostgreSQL
3. Using DTOs and ModelMapper
4. Exception Handling with @ControllerAdvice
5. Validating Input Using @Valid and @NotNull
6. Pagination and Sorting APIs
7. Handling Nulls and Optionals in API Response
8. OpenAPI/Swagger Documentation
9. Versioning REST APIs
10. Testing REST Endpoints with JUnit + MockMvc

---

1. Designing a RESTful Controller in Spring Boot

Overview

A RESTful controller in Spring Boot handles API requests and responses at specific endpoints. It leverages annotations like @RestController and follows REST principles such as resource-based URLs and HTTP methods (GET, POST, PUT, DELETE).

Example:

   @RestController
@RequestMapping("/employees")
public class EmployeeController {
@GetMapping("/{id}")
public ResponseEntity<Employee> getEmployee(@PathVariable Long id) {
Employee employee = employeeService.findById(id);
return ResponseEntity.ok(employee);
}
@PostMapping
public ResponseEntity<Employee> createEmployee(@RequestBody Employee employee) {
Employee savedEmployee = employeeService.save(employee);
return ResponseEntity.status(HttpStatus.CREATED).body(savedEmployee);
}
}

Pro Tip: Use meaningful endpoint names (e.g., /employees instead of /get-employee).

Learn more about Spring boot controllers.

---

2. Connecting REST API to PostgreSQL

Setup Steps:

1. Include PostgreSQL Dependency:

Add the PostgreSQL driver in pom.xml.

   <dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>

2. Configureapplication.properties:

   spring.datasource.url=jdbc:postgresql://localhost:5432/your_database
spring.datasource.username=your_username
spring.datasource.password=your_password
spring.jpa.hibernate.ddl-auto=update

3. Create an Entity and Repository:

   @Entity
public class Employee {
@Id @GeneratedValue
private Long id;
private String name;
private String position;
}
public interface EmployeeRepository extends JpaRepository<Employee, Long> {}

PostgreSQL and Spring Boot provide a seamless integration to manage your database easily.

---

3. Using DTOs and ModelMapper

Data Transfer Objects (DTOs) are used to transfer data between layers while hiding implementation details.

Example With ModelMapper:

   public class EmployeeDTO {
private String name;
private String position;
}
@Service
public class EmployeeService {
@Autowired private ModelMapper modelMapper;
public EmployeeDTO convertToDto(Employee entity) {
return modelMapper.map(entity, EmployeeDTO.class);
}
}

Pro Tip: Using DTOs improves maintainability and prevents exposing sensitive database information.

---

4. Exception Handling with @ControllerAdvice

Global exception handling centralizes error responses for REST APIs.

Example:

   @ControllerAdvice
public class GlobalExceptionHandler {
@ExceptionHandler(ResourceNotFoundException.class)
public ResponseEntity<String> handleNotFound(ResourceNotFoundException ex) {
return ResponseEntity.status(HttpStatus.NOT_FOUND).body(ex.getMessage());
}
}

Pro Tip: Always return meaningful error messages in a standard format (e.g., JSON).

---

5. Validating Input Using @Valid and @NotNull

Input validation ensures that APIs only accept well-formed requests.

Example:

   public class EmployeeRequest {
@NotNull(message = "Name cannot be null")
private String name;
@NotNull
private String position;
}
@PostMapping
public ResponseEntity<String> addEmployee(@Valid @RequestBody EmployeeRequest employeeRequest) {
// Handle request
}
```  
**Pro Tip:** Combine validation with exception handling to provide clear error responses.  
---  
## 6. Pagination and Sorting APIs  
Large datasets require efficient pagination and sorting to limit results.  
### Example:
```java
@GetMapping
public Page<Employee> getEmployees(Pageable pageable) {
return employeeService.findAll(pageable);
}

Use the Pageable object to determine page size, number, and sorting criteria.

---

7. Handling Nulls and Optionals in API Response

Spring Boot offers Optional to avoid null checks and provide optional responses.

Example:

   @GetMapping("/{id}")
public ResponseEntity<Employee> getEmployeeById(@PathVariable Long id) {
Optional<Employee> employee = employeeService.findById(id);
return employee.map(ResponseEntity::ok).orElse(ResponseEntity.status(HttpStatus.NOT_FOUND).build());
}
```  
**Pro Tip:** Using Optional is an effective way to eliminate `NullPointerExceptions`.  
---  
## 8. OpenAPI/Swagger Documentation  
OpenAPI (Swagger) provides interactive documentation for APIs.  
### Steps to Integrate:
1. Add Swagger dependency:
```xml
<dependency>
<groupId>org.springdoc</groupId>
<artifactId>springdoc-openapi-ui</artifactId>
<version>1.6.0</version>
</dependency>

2. Access the generated documentation at /swagger-ui.html.

Learn more about OpenAPI integration.

---

9. Versioning REST APIs

Versioning helps avoid breaking changes when modifying APIs over time.

Example of URL Versioning:

   @RestController
@RequestMapping("/v1/employees")
public class EmployeeV1Controller {}
@RestController
@RequestMapping("/v2/employees")
public class EmployeeV2Controller {}

Pro Tip: Version APIs in a way that supports backward compatibility.

---

10. Testing REST Endpoints with JUnit + MockMvc

Unit and integration testing ensure reliability of REST endpoints.

Example:

   @WebMvcTest(EmployeeController.class)
public class EmployeeControllerTest {
@Autowired private MockMvc mockMvc;
@Test
public void testGetEmployee() throws Exception {
mockMvc.perform(get("/employees/1"))
.andExpect(status().isOk())
.andExpect(jsonPath("$.name").value("John Doe"));
}
}
```  
**Pro Tip:** Incorporate automated tests into your CI/CD pipelines to catch bugs early.  
---  
## FAQs  
### Why use DTOs instead of entities in Spring Boot APIs?  
DTOs hide sensitive fields and keep API responses aligned with requirements.  
### How can I document REST APIs effectively?  
Integrate OpenAPI/Swagger tools to provide interactive documentation.  
### What is the advantage of using exception handling with @ControllerAdvice?  
It centralizes error handling, leading to cleaner code and consistent error messages.  
---  
## Summary  
Spring Boot, in combination with a SQL database like PostgreSQL, provides a powerful framework for building scalable REST APIs. Topics like input validation, exception handling, pagination, and API documentation are essential for building real-world applications and acing technical interviews.  
Continue refining your skills in testing, validation, and advanced configurations to master REST API development with Spring Boot and SQL!