Top 10 Spring Boot Interview Questions for SQL & PostgreSQL

Spring Boot and PostgreSQL are a powerful combination for building robust, scalable, and production-ready applications. Spring Boot streamlines application configuration while PostgreSQL provides a reliable and high-performance database. For developers, understanding how these two technologies work together is not just beneficial—it’s essential.

This blog answers the Top 10 Spring Boot Interview Questions for SQL & PostgreSQL. With technical explanations and real-world examples, these answers prepare you for your next technical discussion.

Table of Contents

1. How to Configure PostgreSQL with Spring Boot?
2. JDBC vs JPA in Spring Boot
3. Using Spring Data JPA Repositories
4. Writing Native Queries
5. Entity Relationships (OneToMany, ManyToOne)
6. Handling Transactions and Rollbacks
7. Pagination and Sorting with Spring JPA
8. Database Migrations Using Flyway or Liquibase
9. Optimizing Query Performance
10. Common JPA Pitfalls in Production

---

1. How to Configure PostgreSQL with Spring Boot?

Setting up PostgreSQL in a Spring Boot project is straightforward. Spring Boot’s autoconfiguration handles most of the complexity for database integrations.

Step-by-Step Configuration:

1. Include the PostgreSQL Driver:

Add the following dependency to the pom.xml:

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

2. Configureapplication.properties:

Define PostgreSQL connection details.

   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

• • Set ddl-auto to update for development but avoid this in production.

3. Test Database Connection:

Use a CommandLineRunner to confirm the setup:

   @Component
public class DatabaseTest implements CommandLineRunner {
@Autowired private DataSource dataSource;
@Override
public void run(String... args) {
System.out.println("Connected to: " + dataSource.getConnection().getMetaData().getURL());
}
}
```  
[Learn more about Spring Boot database configuration](https://spring.io/projects/spring-boot)  
---  
## 2. JDBC vs JPA in Spring Boot  
Java Database Connectivity (JDBC) and Java Persistence API (JPA) are the two primary approaches for interacting with databases in Spring Boot applications.
| **Aspect**     | **JDBC**                                   | **JPA**                                   |  
|----------------|--------------------------------------------|-------------------------------------------|  
| **Purpose**    | Direct interactions with SQL queries.      | Abstracts database operations with ORM.   |  
| **Flexibility**| Highly flexible with raw SQL control.      | Simplified with mappings and entities.    |  
| **Ease of Use**| Requires manual query and result handling. | Handles queries and result mappings.      |  
### Example of JPA:
```java
@Entity
public class Employee {
@Id @GeneratedValue
private Long id;
private String name;
}

Use Cases:

• Use JDBC for database features like stored procedures.
• Use JPA for object-relational mapping (ORM) and easier development.

---

3. Using Spring Data JPA Repositories

Spring Data JPA simplifies database operations by providing repository abstractions backed by JPA.

Example:

   public interface EmployeeRepository extends JpaRepository<Employee, Long> {
List<Employee> findByDepartment(String department);
}

Features:

• Derived queries (findBy<Property>()).
• Pagination and sorting support.

Explore Spring Data JPA Repositories

---

4. Writing Native Queries

Sometimes, native queries are necessary for advanced scenarios. You can annotate methods with @Query.

Example:

   @Query(value = "SELECT * FROM employee WHERE salary > :salary", nativeQuery = true)
List<Employee> findBySalaryGreaterThan(@Param("salary") double salary);

Pro Tip: Use native queries sparingly—only when JPQL or Criteria API cannot achieve the desired functionality.

---

5. Entity Relationships (OneToMany, ManyToOne)

Spring Data JPA maps relationships between entities using annotations.

Example Relationships:

• OneToMany:

   @OneToMany(mappedBy = "department")
private List<Employee> employees;

• ManyToOne:

   @ManyToOne
private Department department;

Pro Tip: Use proper fetch strategies (LAZY vs EAGER) based on your performance needs.

---

6. Handling Transactions and Rollbacks

Spring Framework manages transactions using the @Transactional annotation.

Example:

   @Transactional
public void processEmployee(Employee employee) {
employeeRepository.save(employee);
throw new RuntimeException("Simulating rollback.");
}

Features:

• Automatic rollback on runtime exceptions.
• Support for both declarative and programmatic transactions.

Details about Spring’s transaction management

---

7. Pagination and Sorting with Spring JPA

Pagination is achieved easily with Pageable.

Example:

   Page<Employee> employees = employeeRepository.findAll(PageRequest.of(0, 5, Sort.by("name").ascending()));

Use Case: Essential for managing large datasets and improving query efficiency in web applications.

---

8. Database Migrations Using Flyway or Liquibase

Database migration tools keep your database schema synchronized across environments.

Flyway Example:

1. Add Flyway dependency:

   <dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
</dependency>

2. Create migration scripts in src/main/resources/db/migration.

Pro Tip: Use Liquibase for more complex, XML-based change definitions.

Read more about Flyway

---

9. Optimizing Query Performance

Performance can be improved by:

• Indexing frequently queried columns.
• Using projections (@Query with SELECT specific columns).
• Reviewing Hibernate’s generated SQL queries in the log.

Example Query Optimization:

   @Query("SELECT e.name FROM Employee e WHERE e.salary > :salary")
List<String> findNamesWithHighSalaries(@Param("salary") double salary);

---

10. Common JPA Pitfalls in Production

1. N+1 Query Problem:

Use EntityGraph to fetch related entities efficiently.

2. Incorrectfetch Settings:

Defaulting to EAGER can load huge amounts of data unnecessarily.

3. Improper Transaction Boundaries:

Ensure @Transactional is used correctly to handle rollbacks.

---

FAQs

What is the default schema for PostgreSQL in Spring Boot?

The default schema is public, but it can be customized using the spring.datasource.url property.

How to enable lazy loading in JPA?

Set relationships to FetchType.LAZY and access them only when required.

Which database migration tool is better, Flyway or Liquibase?

Both are excellent, but Flyway is code-centric while Liquibase is suited for XML-based complex migrations.

---

Summary

Spring Boot and PostgreSQL provide developers with a highly efficient platform for creating scalable applications. By mastering topics like configuring PostgreSQL, managing transactions, optimizing queries, and avoiding pitfalls, developers can confidently leverage this stack for powerful results.

Continue exploring Spring Data JPA, Flyway, and native queries to refine your skills for real-world implementations!