H2 In-Memory Database in Java

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.