Introduction

H2 is an open-source, embedded, in-memory Java database that provides a fast, lightweight SQL database solution for development, testing, and production applications. It supports both in-memory and persistent modes with a small footprint (~2MB).

Setup and Configuration

Maven Dependencies

<dependencies>
<!-- H2 Database -->
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>2.2.224</version>
</dependency>
<!-- Spring Boot Starter Data JPA -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- JDBC Template -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
</dependencies>

Spring Boot Configuration

# application.properties
# In-Memory Database Configuration
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
# H2 Console (for development)
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console
# JPA Configuration
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
# Persistent file-based configuration (alternative)
# spring.datasource.url=jdbc:h2:file:/path/to/database/testdb

@Configuration
public class H2Config {
@Bean
@ConfigurationProperties("spring.datasource")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
// Initialize schema and data
@Bean
public DataSourceInitializer dataSourceInitializer(DataSource dataSource) {
DataSourceInitializer initializer = new DataSourceInitializer();
initializer.setDataSource(dataSource);
ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
populator.addScript(new ClassPathResource("schema.sql"));
populator.addScript(new ClassPathResource("data.sql"));
initializer.setDatabasePopulator(populator);
return initializer;
}
// H2 Web Console (for development)
@Bean
@Profile("dev")
public ServletRegistrationBean<WebServlet> h2ServletRegistration() {
ServletRegistrationBean<WebServlet> registration = 
new ServletRegistrationBean<>(new WebServlet());
registration.addUrlMappings("/h2-console/*");
registration.addInitParameter("webAllowOthers", "false");
registration.addInitParameter("webAdminPassword", "admin");
return registration;
}
}

Entity Classes and Data Model

JPA Entity Definitions

@Entity
@Table(name = "users")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "username", unique = true, nullable = false, length = 50)
private String username;
@Column(name = "email", unique = true, nullable = false)
private String email;
@Column(name = "password", nullable = false)
private String password;
@Column(name = "first_name", length = 50)
private String firstName;
@Column(name = "last_name", length = 50)
private String lastName;
@Enumerated(EnumType.STRING)
@Column(name = "role")
private UserRole role;
@Column(name = "created_at")
private LocalDateTime createdAt;
@Column(name = "updated_at")
private LocalDateTime updatedAt;
@OneToMany(mappedBy = "user", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private List<Order> orders = new ArrayList<>();
// Constructors
public User() {
this.createdAt = LocalDateTime.now();
this.updatedAt = LocalDateTime.now();
}
public User(String username, String email, String password, String firstName, String lastName, UserRole role) {
this();
this.username = username;
this.email = email;
this.password = password;
this.firstName = firstName;
this.lastName = lastName;
this.role = role;
}
// Getters and Setters
// Pre-update hook
@PreUpdate
public void preUpdate() {
this.updatedAt = LocalDateTime.now();
}
// toString, equals, hashCode
}
@Entity
@Table(name = "products")
public class Product {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "name", nullable = false, length = 100)
private String name;
@Column(name = "description", length = 500)
private String description;
@Column(name = "price", nullable = false, precision = 10, scale = 2)
private BigDecimal price;
@Column(name = "stock_quantity")
private Integer stockQuantity;
@Enumerated(EnumType.STRING)
@Column(name = "category")
private ProductCategory category;
@Column(name = "created_at")
private LocalDateTime createdAt;
@Column(name = "updated_at")
private LocalDateTime updatedAt;
@OneToMany(mappedBy = "product", cascade = CascadeType.ALL)
private List<OrderItem> orderItems = new ArrayList<>();
// Constructors, getters, setters
public Product() {
this.createdAt = LocalDateTime.now();
this.updatedAt = LocalDateTime.now();
}
public Product(String name, String description, BigDecimal price, Integer stockQuantity, ProductCategory category) {
this();
this.name = name;
this.description = description;
this.price = price;
this.stockQuantity = stockQuantity;
this.category = category;
}
@PreUpdate
public void preUpdate() {
this.updatedAt = LocalDateTime.now();
}
}
@Entity
@Table(name = "orders")
public class Order {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "user_id", nullable = false)
private User user;
@Column(name = "order_date", nullable = false)
private LocalDateTime orderDate;
@Column(name = "total_amount", precision = 10, scale = 2)
private BigDecimal totalAmount;
@Enumerated(EnumType.STRING)
@Column(name = "status")
private OrderStatus status;
@Column(name = "shipping_address")
private String shippingAddress;
@OneToMany(mappedBy = "order", cascade = CascadeType.ALL, orphanRemoval = true)
private List<OrderItem> orderItems = new ArrayList<>();
// Constructors, getters, setters
public Order() {
this.orderDate = LocalDateTime.now();
this.status = OrderStatus.PENDING;
}
public Order(User user, String shippingAddress) {
this();
this.user = user;
this.shippingAddress = shippingAddress;
}
// Helper method to add order items
public void addOrderItem(OrderItem item) {
orderItems.add(item);
item.setOrder(this);
}
// Calculate total amount
public void calculateTotal() {
this.totalAmount = orderItems.stream()
.map(OrderItem::getSubtotal)
.reduce(BigDecimal.ZERO, BigDecimal::add);
}
}
@Entity
@Table(name = "order_items")
public class OrderItem {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "order_id", nullable = false)
private Order order;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "product_id", nullable = false)
private Product product;
@Column(name = "quantity", nullable = false)
private Integer quantity;
@Column(name = "unit_price", precision = 10, scale = 2)
private BigDecimal unitPrice;
@Column(name = "subtotal", precision = 10, scale = 2)
private BigDecimal subtotal;
// Constructors, getters, setters
public OrderItem() {}
public OrderItem(Order order, Product product, Integer quantity) {
this.order = order;
this.product = product;
this.quantity = quantity;
this.unitPrice = product.getPrice();
calculateSubtotal();
}
public void calculateSubtotal() {
this.subtotal = unitPrice.multiply(BigDecimal.valueOf(quantity));
}
}
// Enums
enum UserRole {
ADMIN, USER, MODERATOR
}
enum ProductCategory {
ELECTRONICS, CLOTHING, BOOKS, HOME_APPLIANCES, SPORTS
}
enum OrderStatus {
PENDING, CONFIRMED, SHIPPED, DELIVERED, CANCELLED
}

Repository Layer with Spring Data JPA

JPA Repository Interfaces

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
// Derived queries
Optional<User> findByUsername(String username);
Optional<User> findByEmail(String email);
List<User> findByRole(UserRole role);
List<User> findByFirstNameContainingIgnoreCase(String firstName);
List<User> findByLastNameContainingIgnoreCase(String lastName);
// Custom query with @Query
@Query("SELECT u FROM User u WHERE u.createdAt BETWEEN :startDate AND :endDate")
List<User> findUsersCreatedBetween(@Param("startDate") LocalDateTime startDate, 
@Param("endDate") LocalDateTime endDate);
@Query("SELECT u FROM User u WHERE u.firstName LIKE %:name% OR u.lastName LIKE %:name%")
List<User> findByNameContaining(@Param("name") String name);
// Native query
@Query(value = "SELECT * FROM users u WHERE u.role = :role ORDER BY u.created_at DESC", 
nativeQuery = true)
List<User> findByRoleNative(@Param("role") String role);
// Count queries
long countByRole(UserRole role);
// Exists queries
boolean existsByUsername(String username);
boolean existsByEmail(String email);
// Delete queries
void deleteByUsername(String username);
}
@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
List<Product> findByCategory(ProductCategory category);
List<Product> findByPriceBetween(BigDecimal minPrice, BigDecimal maxPrice);
List<Product> findByStockQuantityGreaterThan(Integer quantity);
List<Product> findByNameContainingIgnoreCase(String name);
@Query("SELECT p FROM Product p WHERE p.price < :maxPrice AND p.stockQuantity > 0")
List<Product> findAvailableProductsUnderPrice(@Param("maxPrice") BigDecimal maxPrice);
@Query("SELECT p.category, COUNT(p) FROM Product p GROUP BY p.category")
List<Object[]> countProductsByCategory();
@Modifying
@Query("UPDATE Product p SET p.stockQuantity = p.stockQuantity - :quantity WHERE p.id = :productId")
void decreaseStockQuantity(@Param("productId") Long productId, @Param("quantity") Integer quantity);
}
@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
List<Order> findByUser(User user);
List<Order> findByStatus(OrderStatus status);
List<Order> findByOrderDateBetween(LocalDateTime start, LocalDateTime end);
@Query("SELECT o FROM Order o JOIN o.user u WHERE u.username = :username")
List<Order> findByUsername(@Param("username") String username);
@Query("SELECT o FROM Order o WHERE o.totalAmount > :amount")
List<Order> findOrdersWithTotalGreaterThan(@Param("amount") BigDecimal amount);
@Query("SELECT o.status, COUNT(o) FROM Order o GROUP BY o.status")
List<Object[]> countOrdersByStatus();
@Query("SELECT o FROM Order o JOIN FETCH o.user JOIN FETCH o.orderItems WHERE o.id = :id")
Optional<Order> findByIdWithUserAndItems(@Param("id") Long id);
}
// Custom repository implementation
public interface CustomOrderRepository {
List<Order> findRecentOrdersWithItems(int days);
BigDecimal calculateTotalRevenue(LocalDateTime start, LocalDateTime end);
}
@Repository
public class CustomOrderRepositoryImpl implements CustomOrderRepository {
@PersistenceContext
private EntityManager entityManager;
@Override
public List<Order> findRecentOrdersWithItems(int days) {
String jpql = """
SELECT DISTINCT o FROM Order o 
JOIN FETCH o.orderItems oi 
JOIN FETCH oi.product 
WHERE o.orderDate >= :cutoffDate 
ORDER BY o.orderDate DESC
""";
return entityManager.createQuery(jpql, Order.class)
.setParameter("cutoffDate", LocalDateTime.now().minusDays(days))
.getResultList();
}
@Override
public BigDecimal calculateTotalRevenue(LocalDateTime start, LocalDateTime end) {
String jpql = """
SELECT SUM(o.totalAmount) FROM Order o 
WHERE o.orderDate BETWEEN :start AND :end 
AND o.status = 'DELIVERED'
""";
return entityManager.createQuery(jpql, BigDecimal.class)
.setParameter("start", start)
.setParameter("end", end)
.getSingleResult();
}
}

Service Layer with Business Logic

Service Implementations

@Service
@Transactional
public class UserService {
private final UserRepository userRepository;
private final PasswordEncoder passwordEncoder;
public UserService(UserRepository userRepository, PasswordEncoder passwordEncoder) {
this.userRepository = userRepository;
this.passwordEncoder = passwordEncoder;
}
public User createUser(User user) {
// Validate unique constraints
if (userRepository.existsByUsername(user.getUsername())) {
throw new IllegalArgumentException("Username already exists: " + user.getUsername());
}
if (userRepository.existsByEmail(user.getEmail())) {
throw new IllegalArgumentException("Email already exists: " + user.getEmail());
}
// Encrypt password
user.setPassword(passwordEncoder.encode(user.getPassword()));
return userRepository.save(user);
}
public Optional<User> getUserById(Long id) {
return userRepository.findById(id);
}
public Optional<User> getUserByUsername(String username) {
return userRepository.findByUsername(username);
}
public List<User> getUsersByRole(UserRole role) {
return userRepository.findByRole(role);
}
public List<User> searchUsers(String searchTerm) {
return userRepository.findByNameContaining(searchTerm);
}
public User updateUser(Long id, User userDetails) {
return userRepository.findById(id)
.map(existingUser -> {
if (!existingUser.getUsername().equals(userDetails.getUsername()) && 
userRepository.existsByUsername(userDetails.getUsername())) {
throw new IllegalArgumentException("Username already exists");
}
if (!existingUser.getEmail().equals(userDetails.getEmail()) && 
userRepository.existsByEmail(userDetails.getEmail())) {
throw new IllegalArgumentException("Email already exists");
}
existingUser.setFirstName(userDetails.getFirstName());
existingUser.setLastName(userDetails.getLastName());
existingUser.setEmail(userDetails.getEmail());
existingUser.setRole(userDetails.getRole());
return userRepository.save(existingUser);
})
.orElseThrow(() -> new IllegalArgumentException("User not found with id: " + id));
}
public void deleteUser(Long id) {
userRepository.deleteById(id);
}
public long countUsersByRole(UserRole role) {
return userRepository.countByRole(role);
}
}
@Service
@Transactional
public class ProductService {
private final ProductRepository productRepository;
public ProductService(ProductRepository productRepository) {
this.productRepository = productRepository;
}
public Product createProduct(Product product) {
return productRepository.save(product);
}
public Optional<Product> getProductById(Long id) {
return productRepository.findById(id);
}
public List<Product> getAllProducts() {
return productRepository.findAll();
}
public List<Product> getProductsByCategory(ProductCategory category) {
return productRepository.findByCategory(category);
}
public List<Product> searchProducts(String searchTerm) {
return productRepository.findByNameContainingIgnoreCase(searchTerm);
}
public List<Product> getAvailableProductsUnderPrice(BigDecimal maxPrice) {
return productRepository.findAvailableProductsUnderPrice(maxPrice);
}
public Product updateProductStock(Long productId, Integer newStock) {
Product product = productRepository.findById(productId)
.orElseThrow(() -> new IllegalArgumentException("Product not found: " + productId));
product.setStockQuantity(newStock);
return productRepository.save(product);
}
public void decreaseStock(Long productId, Integer quantity) {
productRepository.decreaseStockQuantity(productId, quantity);
}
public Map<ProductCategory, Long> getProductCountByCategory() {
List<Object[]> results = productRepository.countProductsByCategory();
return results.stream()
.collect(Collectors.toMap(
result -> (ProductCategory) result[0],
result -> (Long) result[1]
));
}
public void deleteProduct(Long id) {
productRepository.deleteById(id);
}
}
@Service
@Transactional
public class OrderService {
private final OrderRepository orderRepository;
private final UserRepository userRepository;
private final ProductRepository productRepository;
private final CustomOrderRepository customOrderRepository;
public OrderService(OrderRepository orderRepository, UserRepository userRepository,
ProductRepository productRepository, CustomOrderRepository customOrderRepository) {
this.orderRepository = orderRepository;
this.userRepository = userRepository;
this.productRepository = productRepository;
this.customOrderRepository = customOrderRepository;
}
public Order createOrder(Long userId, String shippingAddress, Map<Long, Integer> productQuantities) {
User user = userRepository.findById(userId)
.orElseThrow(() -> new IllegalArgumentException("User not found: " + userId));
Order order = new Order(user, shippingAddress);
for (Map.Entry<Long, Integer> entry : productQuantities.entrySet()) {
Long productId = entry.getKey();
Integer quantity = entry.getValue();
Product product = productRepository.findById(productId)
.orElseThrow(() -> new IllegalArgumentException("Product not found: " + productId));
if (product.getStockQuantity() < quantity) {
throw new IllegalArgumentException("Insufficient stock for product: " + product.getName());
}
OrderItem orderItem = new OrderItem(order, product, quantity);
order.addOrderItem(orderItem);
// Decrease stock
productRepository.decreaseStockQuantity(productId, quantity);
}
order.calculateTotal();
return orderRepository.save(order);
}
public Optional<Order> getOrderById(Long id) {
return orderRepository.findByIdWithUserAndItems(id);
}
public List<Order> getOrdersByUser(Long userId) {
User user = userRepository.findById(userId)
.orElseThrow(() -> new IllegalArgumentException("User not found: " + userId));
return orderRepository.findByUser(user);
}
public List<Order> getOrdersByStatus(OrderStatus status) {
return orderRepository.findByStatus(status);
}
public List<Order> getRecentOrders(int days) {
return customOrderRepository.findRecentOrdersWithItems(days);
}
public Order updateOrderStatus(Long orderId, OrderStatus newStatus) {
Order order = orderRepository.findById(orderId)
.orElseThrow(() -> new IllegalArgumentException("Order not found: " + orderId));
order.setStatus(newStatus);
return orderRepository.save(order);
}
public BigDecimal calculateRevenue(LocalDateTime start, LocalDateTime end) {
BigDecimal revenue = customOrderRepository.calculateTotalRevenue(start, end);
return revenue != null ? revenue : BigDecimal.ZERO;
}
public Map<OrderStatus, Long> getOrderStatusStatistics() {
List<Object[]> results = orderRepository.countOrdersByStatus();
return results.stream()
.collect(Collectors.toMap(
result -> (OrderStatus) result[0],
result -> (Long) result[1]
));
}
public void cancelOrder(Long orderId) {
Order order = orderRepository.findById(orderId)
.orElseThrow(() -> new IllegalArgumentException("Order not found: " + orderId));
if (order.getStatus() == OrderStatus.SHIPPED || order.getStatus() == OrderStatus.DELIVERED) {
throw new IllegalStateException("Cannot cancel shipped or delivered order");
}
// Restore stock
for (OrderItem item : order.getOrderItems()) {
Product product = item.getProduct();
product.setStockQuantity(product.getStockQuantity() + item.getQuantity());
productRepository.save(product);
}
order.setStatus(OrderStatus.CANCELLED);
orderRepository.save(order);
}
}

Database Initialization Scripts

Schema and Data Initialization

-- schema.sql
CREATE TABLE IF NOT EXISTS users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(50),
role VARCHAR(20) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS products (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description VARCHAR(500),
price DECIMAL(10,2) NOT NULL,
stock_quantity INT DEFAULT 0,
category VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
total_amount DECIMAL(10,2),
status VARCHAR(20) DEFAULT 'PENDING',
shipping_address VARCHAR(500),
FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE IF NOT EXISTS order_items (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2),
subtotal DECIMAL(10,2),
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
-- Indexes for better performance
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_order_date ON orders(order_date);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- data.sql
-- Insert sample users
INSERT INTO users (username, email, password, first_name, last_name, role) VALUES
('admin', '[email protected]', '$2a$10$xyz123', 'System', 'Administrator', 'ADMIN'),
('john_doe', '[email protected]', '$2a$10$xyz456', 'John', 'Doe', 'USER'),
('jane_smith', '[email protected]', '$2a$10$xyz789', 'Jane', 'Smith', 'USER'),
('bob_wilson', '[email protected]', '$2a$10$xyz012', 'Bob', 'Wilson', 'MODERATOR');
-- Insert sample products
INSERT INTO products (name, description, price, stock_quantity, category) VALUES
('iPhone 14', 'Latest Apple smartphone with advanced features', 999.99, 50, 'ELECTRONICS'),
('MacBook Pro', 'Powerful laptop for professionals', 1999.99, 25, 'ELECTRONICS'),
('Java Programming Book', 'Comprehensive guide to Java programming', 49.99, 100, 'BOOKS'),
('Running Shoes', 'Comfortable shoes for running and sports', 89.99, 75, 'SPORTS'),
('Coffee Maker', 'Automatic coffee maker for home use', 129.99, 30, 'HOME_APPLIANCES'),
('T-Shirt', 'Cotton t-shirt in various colors', 19.99, 200, 'CLOTHING'),
('Headphones', 'Wireless noise-cancelling headphones', 199.99, 40, 'ELECTRONICS');
-- Insert sample orders
INSERT INTO orders (user_id, total_amount, status, shipping_address) VALUES
(2, 1049.98, 'DELIVERED', '123 Main St, New York, NY'),
(3, 219.98, 'SHIPPED', '456 Oak Ave, Los Angeles, CA'),
(2, 129.99, 'PENDING', '123 Main St, New York, NY');
-- Insert sample order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price, subtotal) VALUES
(1, 1, 1, 999.99, 999.99),
(1, 6, 2, 19.99, 39.99),
(2, 3, 1, 49.99, 49.99),
(2, 4, 1, 89.99, 89.99),
(2, 6, 4, 19.99, 79.99),
(3, 5, 1, 129.99, 129.99);

Testing with H2 Database

Test Configuration

@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@TestPropertySource(properties = {
"spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE",
"spring.jpa.hibernate.ddl-auto=create-drop"
})
public class H2DatabaseTest {
@Autowired
private TestEntityManager entityManager;
@Autowired
private UserRepository userRepository;
@Autowired
private ProductRepository productRepository;
@Autowired
private OrderRepository orderRepository;
@Test
public void testUserPersistence() {
// Given
User user = new User("testuser", "[email protected]", "password", "Test", "User", UserRole.USER);
// When
User saved = entityManager.persistAndFlush(user);
// Then
assertThat(saved.getId()).isNotNull();
assertThat(saved.getUsername()).isEqualTo("testuser");
}
@Test
public void testFindByUsername() {
// Given
User user = new User("finduser", "[email protected]", "password", "Find", "User", UserRole.USER);
entityManager.persistAndFlush(user);
// When
Optional<User> found = userRepository.findByUsername("finduser");
// Then
assertThat(found).isPresent();
assertThat(found.get().getEmail()).isEqualTo("[email protected]");
}
@Test
public void testProductSearch() {
// Given
Product product = new Product("Test Product", "Test Description", new BigDecimal("99.99"), 10, ProductCategory.ELECTRONICS);
entityManager.persistAndFlush(product);
// When
List<Product> results = productRepository.findByNameContainingIgnoreCase("test");
// Then
assertThat(results).hasSize(1);
assertThat(results.get(0).getName()).isEqualTo("Test Product");
}
@Test
public void testOrderCreationWithItems() {
// Given
User user = new User("orderuser", "[email protected]", "password", "Order", "User", UserRole.USER);
entityManager.persistAndFlush(user);
Product product = new Product("Order Product", "Order Desc", new BigDecimal("49.99"), 5, ProductCategory.ELECTRONICS);
entityManager.persistAndFlush(product);
Order order = new Order(user, "Test Address");
OrderItem item = new OrderItem(order, product, 2);
order.addOrderItem(item);
order.calculateTotal();
// When
Order saved = entityManager.persistAndFlush(order);
// Then
assertThat(saved.getId()).isNotNull();
assertThat(saved.getTotalAmount()).isEqualByComparingTo("99.98");
assertThat(saved.getOrderItems()).hasSize(1);
}
}
@SpringBootTest
@TestPropertySource(properties = {
"spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1",
"spring.jpa.hibernate.ddl-auto=create-drop"
})
public class ServiceLayerTest {
@Autowired
private UserService userService;
@Autowired
private ProductService productService;
@Autowired
private OrderService orderService;
@Test
public void testCreateUser() {
// Given
User user = new User("serviceuser", "[email protected]", "password", "Service", "User", UserRole.USER);
// When
User saved = userService.createUser(user);
// Then
assertThat(saved.getId()).isNotNull();
assertThat(saved.getUsername()).isEqualTo("serviceuser");
}
@Test
public void testCreateOrder() {
// Given
User user = new User("orderuser2", "[email protected]", "password", "Order", "User2", UserRole.USER);
User savedUser = userService.createUser(user);
Product product = new Product("Order Product 2", "Desc", new BigDecimal("29.99"), 10, ProductCategory.BOOKS);
Product savedProduct = productService.createProduct(product);
Map<Long, Integer> productQuantities = Map.of(savedProduct.getId(), 3);
// When
Order order = orderService.createOrder(savedUser.getId(), "Test Address", productQuantities);
// Then
assertThat(order.getId()).isNotNull();
assertThat(order.getTotalAmount()).isEqualByComparingTo("89.97");
assertThat(order.getStatus()).isEqualTo(OrderStatus.PENDING);
}
}

Advanced H2 Features

Stored Procedures and Functions

public class H2AdvancedFeatures {
// H2 supports stored procedures and functions
@Component
public class DatabaseInitializer {
@Autowired
private DataSource dataSource;
@PostConstruct
public void initialize() {
try (Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement()) {
// Create stored function
stmt.execute("""
CREATE ALIAS IF NOT EXISTS CALCULATE_DISCOUNT FOR 
"com.example.h2.H2AdvancedFeatures.calculateDiscount"
""");
// Create utility function
stmt.execute("""
CREATE ALIAS IF NOT EXISTS FORMAT_PRICE FOR 
"com.example.h2.H2AdvancedFunctions.formatPrice"
""");
} catch (SQLException e) {
throw new RuntimeException("Failed to initialize database functions", e);
}
}
}
// Java method that can be called as SQL function
public static double calculateDiscount(double price, double discountRate) {
return price * (1 - discountRate / 100);
}
}
// Custom H2 functions
public class H2AdvancedFunctions {
public static String formatPrice(BigDecimal price) {
if (price == null) return "N/A";
return "$" + price.setScale(2, RoundingMode.HALF_UP);
}
}
// Using custom functions in repositories
@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
@Query(value = "SELECT FORMAT_PRICE(price) FROM products WHERE id = :id", 
nativeQuery = true)
String getFormattedPrice(@Param("id") Long id);
@Query(value = "SELECT * FROM products WHERE price > CALCULATE_DISCOUNT(:originalPrice, :discountRate)", 
nativeQuery = true)
List<Product> findProductsAfterDiscount(@Param("originalPrice") Double originalPrice, 
@Param("discountRate") Double discountRate);
}

Database Migration with Flyway

// Flyway configuration for H2
@Configuration
public class FlywayConfig {
@Bean
@ConfigurationProperties(prefix = "spring.flyway")
public Flyway flyway(DataSource dataSource) {
return Flyway.configure()
.dataSource(dataSource)
.locations("classpath:db/migration")
.baselineOnMigrate(true)
.load();
}
}
// Migration files in resources/db/migration
// V1__Create_initial_tables.sql
// V2__Add_indexes.sql
// V3__Insert_sample_data.sql

Performance Optimization

Connection Pooling and Optimization

@Configuration
public class H2PerformanceConfig {
@Bean
@ConfigurationProperties("spring.datasource.hikari")
public DataSource dataSource() {
HikariDataSource dataSource = new HikariDataSource();
dataSource.setJdbcUrl("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE");
dataSource.setUsername("sa");
dataSource.setPassword("");
dataSource.setDriverClassName("org.h2.Driver");
// Performance optimizations
dataSource.setMaximumPoolSize(10);
dataSource.setMinimumIdle(2);
dataSource.setConnectionTimeout(30000);
dataSource.setIdleTimeout(300000);
dataSource.setMaxLifetime(1200000);
dataSource.setAutoCommit(true);
return dataSource;
}
@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource) {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(dataSource);
em.setPackagesToScan("com.example.entity");
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
vendorAdapter.setDatabasePlatform("org.hibernate.dialect.H2Dialect");
em.setJpaVendorAdapter(vendorAdapter);
Properties properties = new Properties();
properties.setProperty("hibernate.hbm2ddl.auto", "create-drop");
properties.setProperty("hibernate.dialect", "org.hibernate.dialect.H2Dialect");
properties.setProperty("hibernate.show_sql", "true");
properties.setProperty("hibernate.format_sql", "true");
properties.setProperty("hibernate.jdbc.batch_size", "20");
properties.setProperty("hibernate.order_inserts", "true");
properties.setProperty("hibernate.order_updates", "true");
properties.setProperty("hibernate.jdbc.batch_versioned_data", "true");
em.setJpaProperties(properties);
return em;
}
}

Conclusion

H2 in-memory database provides a powerful, lightweight solution for Java applications with:

• Fast in-memory operations for development and testing
• Spring Boot integration with auto-configuration
• Full SQL compliance with advanced features
• Minimal configuration and easy setup
• Production-ready for certain use cases

Best practices include:

• Use proper connection pooling
• Implement comprehensive testing with H2
• Utilize database migrations
• Monitor performance with proper indexing
• Consider file-based persistence for production data

H2 is ideal for prototyping, testing, and applications where a lightweight embedded database is sufficient, while still offering the power of a full SQL database.