You will be redirect to our new portal geekmonks.com in 10, Happy Learning. Click here to redirect now.

📚 JPA with H2 Database

👉 Integrate H2 Databases using JPA.

Updated on: 04 Dec 2024 - Vivek Singh

Table of Contents


🚀 Introduction

Connecting to a database is fundamental to 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.
  • Let’s dive in to understand required configuration in Spring Boot to leverage JPA for seamless data access. Explore setting up the connection details and enabling the necessary components to get a fully functional data layer integrated with H2.


⚙️ Project Implementation

H2 Configuration

The application.properties file configures the in-memory H2 database.

# 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 JPA Entity, representing a table in the database.

// 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 repository interface extends JpaRepository to leverage automatic CRUD functionality.

// 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);
}


📄 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.


Code Implementation Example

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


Repository (ProductRepository)

  • No change is required as JpaRepository already provides methods that accept Pageable.


Service Implementation (ProductServiceImpl)

// 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 (ProductController)

// 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) Implementation

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).


📝 Code Implementation

Repository (ProductRepository)

// 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 Implementation (ProductServiceImpl)

// 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.


📝 Code Implementation

Entity Class (Product)

// 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 Implementation (ProductServiceImpl)

// 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:

ApproachKey Activity / Tools UsedPrimary Goal
TracingUse 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).
LoggingConfigure 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.

🧠 Key Awareness Points for Experienced Engineers

Area of AwarenessDescription and ImpactRecommended Action
Transaction BoundariesImproper 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 ProblemThe 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 CheckingJPA 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/InsertsStandard 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.