JPA H2 Database Integration

👉 Quick Spring Boot Guide to Configure and Test using the H2 In-Memory Database for Development with Spring Data JPA.

Overview of H2 DB

Connecting to a database is fundamental need of any enterprise application, and Spring Data JPA simplifies this immensely.

For rapid development and testing, the H2 in-memory database is the ideal choice, offering a lightweight and quick setup.

Overview of H2 DB
Table of Contents
Database Configuration
Entity Class
Repository Class
Paging and Sorting
Custom Queries (JPQL/SQL)
Optimistic Locking (Version Control)
Observability
Takeaways

Database Configuration

Below configuration establish a complete, ready-to-use, in-memory H2 database environment for a Spring Boot application.

This configuration is standard for rapid development, testing, and prototyping because the database is temporary and requires no external setup.

# application.properties
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console
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
spring.jpa.hibernate.ddl-auto=update
logging.level.org.hibernate.SQL=debug

Entity Class

The Product class is the foundational piece for persistence, defining the structure and schema for the database table.

In JPA, this class serves as the Entity, mapping directly to a table.

// src/main/java/com/geekmonks/entity/Product.java
package com.geekmonks.entity;

import jakarta.persistence.*;
import java.time.LocalDateTime;

@Entity
@Table(name = "product")
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long productId;

    private String productName;
    private Double price;
    private String category;

    // Standard constructor, getters, and setters...
    // Removed for brevity but essential in the actual class.
    
    // Minimal constructor for example
    public Product() {}
    public Product(String productName, Double price, String category) {
        this.productName = productName;
        this.price = price;
        this.category = category;
    }
}

Repository Class

The ProductRepository interface extends JpaRepository to leverage automatic CRUD functionality is scanned by Spring Data JPA and generate implemention for defined methods.

This minimal code provides a comprehensive data access layer for the Product entity without writing a single line of implementation logic.

// src/main/java/com/geekmonks/repository/ProductRepository.java
package com.geekmonks.repository;

import com.geekmonks.entity.Product;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    // Custom query derivation methods can be added here
    Product findByProductName(String productName);
}

Project Reference:
Refer the POC Project on Github: Spring Data JPA and H2 Database.

Paging and Sorting

Paging and Sorting are essential for handling large datasets efficiently by retrieving a small, manageable chunk of data at a time.

The approach uses the PagingAndSortingRepository or, more commonly, the JpaRepository (which extends it) and utilizes the Pageable interface in the repository method signature.

Use Case: Displaying a list of products in an e-commerce catalog or showing search results where the total number of records is very large.
Benefits:Improved application performance, reduced database load, and a better User Experience (UX) as users only load visible data.

This involves modifications in the Controller, Service, and Repository classes.

Note:
No change required in Repository as JpaRepository already provides methods that accept Pageable.

Service

Create a PageRequest object for pagination and sorting defining current pageNo, pageSize and a field on which sorting will be applied and pass this to the respository API.

// src/main/java/com/geekmonks/service/impl/ProductServiceImpl.java

// ... imports ...
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;

@Service
public class ProductServiceImpl implements ProductService {

    @Autowired
    private ProductRepository productRepository;

    @Override
    public Page<Product> getAllProductsPagedAndSorted(int pageNo, int pageSize, String sortBy) {
        
        // Create a PageRequest object for pagination and sorting
        PageRequest pageable = PageRequest.of(pageNo, pageSize, Sort.by(sortBy).descending());

        // Use findAll(Pageable) provided by JpaRepository
        return productRepository.findAll(pageable);
    }
    // ... other methods ...
}

Controller

Construct the response using Page<Product> instance received with paged data from service.

// src/main/java/com/geekmonks/controller/ProductController.java

// ... imports ...
import org.springframework.data.domain.Page;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/api/products")
public class ProductController {

    @Autowired
    private ProductService productService;

    @GetMapping("/paged")
    public ResponseEntity<Page<Product>> getProducts(
            @RequestParam(defaultValue = "0") int page,
            @RequestParam(defaultValue = "10") int size,
            @RequestParam(defaultValue = "productId") String sort) {

        Page<Product> productsPage = productService.getAllProductsPagedAndSorted(page, size, sort);
        return ResponseEntity.ok(productsPage);
    }
}

Custom Queries (JPQL/SQL)

When query derivation by method name becomes too complex or involves custom logic, aggregates, or joins not easily expressed, the @Query annotation is used. It allows defining queries using JPQL (Java Persistence Query Language) or Native SQL.

Use Case: Fetching products whose prices are between two values and belong to a specific category, or executing complex join operations.
Benefits: Provides full control over the executed query, allows using named parameters for safety, and supports more complex SQL features (like stored procedures via Native SQL).

Repository

// src/main/java/com/geekmonks/repository/ProductRepository.java

// ... imports ...
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;

public interface ProductRepository extends JpaRepository<Product, Long> {
    
    // 1. JPQL Example: Uses entity and field names (case sensitive)
    @Query("SELECT p FROM Product p WHERE p.category = :category AND p.price BETWEEN :minPrice AND :maxPrice")
    List<Product> findByCategoryAndPriceRangeJpql(
            @Param("category") String category, 
            @Param("minPrice") Double minPrice, 
            @Param("maxPrice") Double maxPrice);

    // 2. Native SQL Example: Uses table and column names (as per DB schema)
    @Query(value = "SELECT * FROM product p WHERE p.product_name LIKE %:name%", nativeQuery = true)
    List<Product> findProductsByNameNative(@Param("name") String namePart);
}

Service

// src/main/java/com/geekmonks/service/impl/ProductServiceImpl.java

// ... imports ...

@Service
public class ProductServiceImpl implements ProductService {

    @Autowired
    private ProductRepository productRepository;

    @Override
    public List<Product> findProductsByCategoryAndRange(String category, Double min, Double max) {
        return productRepository.findByCategoryAndPriceRangeJpql(category, min, max);
    }
    
    // ... other methods ...
}

Optimistic Locking (Version Control)

Optimistic Locking is a concurrency control mechanism used to prevent lost updates in a multi-user environment without holding long-term database locks. It assumes that multiple transactions rarely conflict. It is implemented by adding a version field (usually an integer) to the entity, annotated with @Version.

Use Case: Updating a shared resource, like a product’s inventory count, where multiple users might try to modify the record simultaneously.
Benefits:High concurrency and scalability since database locks are avoided. If a conflict is detected (the version number doesn’t match), JPA throws an ObjectOptimisticLockingFailureException, and the application can retry the transaction.

Entity

// src/main/java/com/geekmonks/entity/Product.java

// ... imports ...
import jakarta.persistence.Version;

@Entity
@Table(name = "product")
public class Product {
    
    // ... existing fields ...
    
    @Version // JPA manages this field automatically
    private Integer version;

    // ... getters and setters for version (optional, but good practice) ...
}

Service

// src/main/java/com/geekmonks/service/impl/ProductServiceImpl.java

// ... imports ...
import org.springframework.orm.ObjectOptimisticLockingFailureException;
import org.springframework.transaction.annotation.Transactional;

@Service
public class ProductServiceImpl implements ProductService {

    @Autowired
    private ProductRepository productRepository;

    @Transactional
    public Product updateProductPrice(Long productId, Double newPrice) {
        Product product = productRepository.findById(productId)
            .orElseThrow(() -> new RuntimeException("Product not found"));

        // Simulate a scenario where another transaction might have updated it
        // and the version number in the database is now different from the one 
        // loaded by this transaction.

        product.setPrice(newPrice);
        
        try {
            return productRepository.save(product); // This will check the @Version field
        } catch (ObjectOptimisticLockingFailureException e) {
            // Handle the conflict, typically by logging and retrying the transaction
            // or informing the user to retry the operation.
            throw new RuntimeException("Update failed due to concurrent modification. Please retry.", e);
        }
    }
}

Observability

In enterprise applications, observability (understanding the internal state from external outputs) is key. For a data access layer using Spring Data JPA, this primarily involves:

Approach	Key Activity / Tools Used	Primary Goal
Tracing	Use `Sleuth/Micrometer` Tracing (or `OpenTelemetry`) to trace requests.	Capture the end-to-end flow of a request, specifically isolating the time spent in the database to locate slow operations.
Metrics (Monitoring)	Collect database connection pool metrics and monitor the duration/count of repository method calls via `Micrometer` and `Actuator`.	Assess the `performance and resource utilization` of the data access layer (e.g., connection saturation).
Logging	Configure Hibernate SQL logging (`logging.level.org.hibernate.SQL=debug`) and use a structured format (e.g., JSON).	Enables engineers to see the actual SQL query being executed along with parameters for debugging and performance analysis.
Application Performance Monitoring (APM)	Integrate commercial tools like `New Relelc, Dynatrace, or DataDog`.	Provides automatic instrumentation of JPA/Hibernate calls and deep-dive transaction analysis, including detection of the N+1 problem.

Takeaways

Area of Awareness	Description and Impact	Recommended Action
`Transaction Boundaries`	Improper placement of `@Transactional` leads to issues like `LazyInitializationException` or inefficient resource handling.	Place `@Transactional` primarily on the Service Layer methods to define clear transaction scope.
`The N+1 Select Problem`	The most common performance anti-pattern, resulting in N+1 database queries (1 parent + N children queries).	Always use `FETCH JOIN` in JPQL or `EntityGraphs` to eagerly fetch required associations in a single query.
`Dirty Checking`	JPA automatically detects and persists changes to managed entities inside a transaction, even without calling `save()`.	Be aware that any modification to a managed entity will result in an automatic database update at the transaction commit.
`Batching Updates/Inserts`	Standard JPA operations can be slow for high-volume data inserts or updates.	Configure Hibernate for JDBC batching (e.g., set `spring.jpa.properties.hibernate. jdbc.batch_size`) and use batch methods like `saveAll()` effectively.

JPA H2 Database Integration

👉 Quick Spring Boot Guide to Configure and Test using the H2 In-Memory Database for Development with Spring Data JPA.

Overview of H2 DB

Table of Contents

Database Configuration

Entity Class

Repository Class

Paging and Sorting

Service

Controller

Custom Queries (JPQL/SQL)

Repository

Service

Optimistic Locking (Version Control)

Entity

Service

Observability

Takeaways