Advanced SQL Features in H2 with Spring Boot

November 03, 2025

#1

This guide shows how to run advanced SQL features in H2 with Spring Boot for learning and testing real database logic.

1. Add Dependencies

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
</dependency>

2. Configure H2 in Memory

spring.datasource.url=jdbc:h2:mem:advancedsql
spring.datasource.driver-class-name=org.h2.Driver
spring.jpa.hibernate.ddl-auto=create
spring.h2.console.enabled=true

3. Try Common Table Expressions (CTE)

WITH dept_count AS (
  SELECT department, COUNT(*) AS total
  FROM employee
  GROUP BY department
)
SELECT * FROM dept_count WHERE total > 5;

4. Use Window Functions

SELECT name, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY name) AS rank
FROM employee;

5. Native Query in Repository

public interface EmployeeRepository extends JpaRepository<Employee, Long> {
  @Query(value = "SELECT department, COUNT(*) FROM employee GROUP BY department",
         nativeQuery = true)
  List<Object[]> countEmployeesByDept();
}

6. Test Advanced Queries

@SpringBootTest
class EmployeeRepoTest {
  @Autowired EmployeeRepository repo;

  @Test
  void testQuery() {
    repo.countEmployeesByDept().forEach(r ->
      System.out.println(r[0] + " -> " + r[1]));
  }
}