JDBI is a modern, lightweight SQL convenience library for Java that provides a more natural, easy-to-use interface for database interactions compared to raw JDBC. It sits between JDBC and full ORMs like Hibernate.

Basic JDBI Setup

1. Dependencies Configuration

Maven:

<properties> <jdbi3.version>3.37.1</jdbi3.version> </properties> <dependencies> <!-- JDBI Core --> <dependency> <groupId>org.jdbi</groupId> <artifactId>jdbi3-core</artifactId> <version>${jdbi3.version}</version> </dependency> <!-- JDBI SQL Object Extension --> <dependency> <groupId>org.jdbi</groupId> <artifactId>jdbi3-sqlobject</artifactId> <version>${jdbi3.version}</version> </dependency> <!-- PostgreSQL Driver (example) --> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.6.0</version> </dependency> <!-- H2 Database for Testing --> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <version>2.1.214</version> <scope>test</scope> </dependency> </dependencies>

Gradle:

dependencies { implementation 'org.jdbi:jdbi3-core:3.37.1' implementation 'org.jdbi:jdbi3-sqlobject:3.37.1' implementation 'org.postgresql:postgresql:42.6.0' testImplementation 'com.h2database:h2:2.1.214' }

2. Basic JDBI Configuration

import org.jdbi.v3.core.Jdbi; import org.jdbi.v3.core.statement.SqlLogger; import org.jdbi.v3.core.statement.StatementContext; import java.sql.SQLException; import java.time.Duration; public class JdbiConfiguration { public static Jdbi createJdbiInstance(String jdbcUrl, String username, String password) { return Jdbi.create(jdbcUrl, username, password) .installPlugins() // Auto-detect and install plugins .setSqlLogger(new SqlLogger() { @Override public void logAfterExecution(StatementContext context) { System.out.printf("SQL: %s | Time: %dms%n", context.getRenderedSql(), Duration.between( context.getExecutionMoment(), context.getCompletionMoment() ).toMillis()); } }); } // For testing with H2 public static Jdbi createH2JdbiInstance() { return Jdbi.create("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1", "sa", "") .installPlugins(); } // With connection pool (HikariCP example) public static Jdbi createJdbiWithConnectionPool() { HikariConfig config = new HikariConfig(); config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb"); config.setUsername("user"); config.setPassword("password"); config.setMaximumPoolSize(20); config.setMinimumIdle(5); config.setConnectionTimeout(30000); HikariDataSource dataSource = new HikariDataSource(config); return Jdbi.create(dataSource).installPlugins(); } }

Core JDBI Usage Patterns

3. Fluent API Usage

import org.jdbi.v3.core.Handle; import org.jdbi.v3.core.result.ResultIterable; import org.jdbi.v3.core.statement.Update; import java.util.List; import java.util.Optional; public class UserRepository { private final Jdbi jdbi; public UserRepository(Jdbi jdbi) { this.jdbi = jdbi; } // Basic CRUD operations using fluent API public void createUser(User user) { jdbi.useHandle(handle -> { handle.createUpdate("INSERT INTO users (id, username, email, created_at) " + "VALUES (:id, :username, :email, :createdAt)") .bind("id", user.getId()) .bind("username", user.getUsername()) .bind("email", user.getEmail()) .bind("createdAt", user.getCreatedAt()) .execute(); }); } public Optional<User> findUserById(Long id) { return jdbi.withHandle(handle -> handle.createQuery("SELECT * FROM users WHERE id = :id") .bind("id", id) .mapToBean(User.class) .findOne() ); } public List<User> findAllUsers() { return jdbi.withHandle(handle -> handle.createQuery("SELECT * FROM users ORDER BY created_at DESC") .mapToBean(User.class) .list() ); } public int updateUserEmail(Long id, String newEmail) { return jdbi.withHandle(handle -> handle.createUpdate("UPDATE users SET email = :email WHERE id = :id") .bind("email", newEmail) .bind("id", id) .execute() ); } public boolean deleteUser(Long id) { return jdbi.withHandle(handle -> handle.createUpdate("DELETE FROM users WHERE id = :id") .bind("id", id) .execute() > 0 ); } // Batch operations public void createUsersBatch(List<User> users) { jdbi.useHandle(handle -> { Update update = handle.createUpdate( "INSERT INTO users (id, username, email, created_at) " + "VALUES (:id, :username, :email, :createdAt)"); users.forEach(user -> update.bind("id", user.getId()) .bind("username", user.getUsername()) .bind("email", user.getEmail()) .bind("createdAt", user.getCreatedAt()) .add() ); update.execute(); }); } // Complex queries with joins public List<UserWithProfile> findUsersWithProfiles() { return jdbi.withHandle(handle -> handle.createQuery(""" SELECT u.id, u.username, u.email, p.first_name, p.last_name, p.bio FROM users u LEFT JOIN user_profiles p ON u.id = p.user_id ORDER BY u.created_at DESC """) .mapToBean(UserWithProfile.class) .list() ); } // Transaction management public void transferPoints(Long fromUserId, Long toUserId, int points) { jdbi.useTransaction(handle -> { // Deduct points from source user handle.createUpdate("UPDATE users SET points = points - :points WHERE id = :id") .bind("points", points) .bind("id", fromUserId) .execute(); // Add points to target user handle.createUpdate("UPDATE users SET points = points + :points WHERE id = :id") .bind("points", points) .bind("id", toUserId) .execute(); // Log the transaction handle.createUpdate(""" INSERT INTO point_transfers (from_user_id, to_user_id, points, transferred_at) VALUES (:fromUserId, :toUserId, :points, NOW()) """) .bind("fromUserId", fromUserId) .bind("toUserId", toUserId) .bind("points", points) .execute(); }); } // Streaming large result sets public void processAllUsers(UserProcessor processor) { jdbi.useHandle(handle -> { try (ResultIterable<User> users = handle.createQuery("SELECT * FROM users") .mapToBean(User.class)) { users.stream().forEach(processor::process); } }); } } // Domain models class User { private Long id; private String username; private String email; private java.time.LocalDateTime createdAt; private Integer points; // Constructors public User() {} public User(String username, String email) { this.username = username; this.email = email; this.createdAt = java.time.LocalDateTime.now(); this.points = 0; } // Getters and setters public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public java.time.LocalDateTime getCreatedAt() { return createdAt; } public void setCreatedAt(java.time.LocalDateTime createdAt) { this.createdAt = createdAt; } public Integer getPoints() { return points; } public void setPoints(Integer points) { this.points = points; } } class UserWithProfile { private Long id; private String username; private String email; private String firstName; private String lastName; private String bio; // Getters and setters public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getFirstName() { return firstName; } public void setFirstName(String firstName) { this.firstName = firstName; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getBio() { return bio; } public void setBio(String bio) { this.bio = bio; } } @FunctionalInterface interface UserProcessor { void process(User user); }

SQL Object API (Declarative API)

4. SQL Object Annotations

import org.jdbi.v3.sqlobject.config.RegisterBeanMapper; import org.jdbi.v3.sqlobject.config.RegisterConstructorMapper; import org.jdbi.v3.sqlobject.customizer.Bind; import org.jdbi.v3.sqlobject.customizer.BindBean; import org.jdbi.v3.sqlobject.customizer.BindList; import org.jdbi.v3.sqlobject.statement.*; import org.jdbi.v3.sqlobject.transaction.Transaction; import java.util.List; import java.util.Optional; // User DAO using SQL Object API public interface UserDao { // Basic CRUD operations @SqlUpdate("INSERT INTO users (username, email, created_at) VALUES (:username, :email, :createdAt)") void insert(@BindBean User user); @SqlQuery("SELECT * FROM users WHERE id = :id") @RegisterBeanMapper(User.class) Optional<User> findById(@Bind("id") Long id); @SqlQuery("SELECT * FROM users ORDER BY created_at DESC") @RegisterBeanMapper(User.class) List<User> findAll(); @SqlUpdate("UPDATE users SET email = :email WHERE id = :id") int updateEmail(@Bind("id") Long id, @Bind("email") String email); @SqlUpdate("DELETE FROM users WHERE id = :id") int deleteById(@Bind("id") Long id); // Complex queries @SqlQuery("SELECT * FROM users WHERE username LIKE :searchTerm OR email LIKE :searchTerm") @RegisterBeanMapper(User.class) List<User> searchUsers(@Bind("searchTerm") String searchTerm); @SqlQuery("SELECT * FROM users WHERE id IN (<ids>)") @RegisterBeanMapper(User.class) List<User> findByIds(@BindList("ids") List<Long> ids); @SqlQuery("SELECT COUNT(*) FROM users WHERE created_at >= :since") long countSince(@Bind("since") java.time.LocalDateTime since); // Join queries with custom result mapping @SqlQuery(""" SELECT u.id, u.username, u.email, u.created_at, p.first_name, p.last_name, p.bio FROM users u LEFT JOIN user_profiles p ON u.id = p.user_id WHERE u.id = :userId """) @RegisterConstructorMapper(UserWithProfile.class) Optional<UserWithProfile> findUserWithProfile(@Bind("userId") Long userId); // Batch operations @SqlBatch("INSERT INTO users (username, email, created_at) VALUES (:username, :email, :createdAt)") void insertBatch(@BindBean List<User> users); // Transactional method @Transaction default void transferPoints(Long fromUserId, Long toUserId, int points) { updatePoints(fromUserId, -points); updatePoints(toUserId, points); logTransfer(fromUserId, toUserId, points); } @SqlUpdate("UPDATE users SET points = points + :points WHERE id = :userId") void updatePoints(@Bind("userId") Long userId, @Bind("points") int points); @SqlUpdate(""" INSERT INTO point_transfers (from_user_id, to_user_id, points, transferred_at) VALUES (:fromUserId, :toUserId, :points, NOW()) """) void logTransfer(@Bind("fromUserId") Long fromUserId, @Bind("toUserId") Long toUserId, @Bind("points") int points); // Get generated keys @SqlUpdate("INSERT INTO users (username, email, created_at) VALUES (:username, :email, :createdAt)") @GetGeneratedKeys long insertAndReturnId(@BindBean User user); } // Product DAO with advanced features public interface ProductDao { @SqlQuery("SELECT * FROM products WHERE id = :id") @RegisterBeanMapper(Product.class) Optional<Product> findById(@Bind("id") Long id); @SqlQuery("SELECT * FROM products WHERE category = :category AND price BETWEEN :minPrice AND :maxPrice") @RegisterBeanMapper(Product.class) List<Product> findByCategoryAndPriceRange(@Bind("category") String category, @Bind("minPrice") Double minPrice, @Bind("maxPrice") Double maxPrice); @SqlUpdate("UPDATE products SET stock = stock - :quantity WHERE id = :id AND stock >= :quantity") int reduceStock(@Bind("id") Long id, @Bind("quantity") int quantity); @SqlCall("{call update_product_rating(:productId, :newRating)}") void updateProductRating(@Bind("productId") Long productId, @Bind("newRating") Double newRating); } // Custom row mapper for complex results class UserWithProfileMapper implements RowMapper<UserWithProfile> { @Override public UserWithProfile map(ResultSet rs, StatementContext ctx) throws SQLException { return new UserWithProfile( rs.getLong("id"), rs.getString("username"), rs.getString("email"), rs.getString("first_name"), rs.getString("last_name"), rs.getString("bio") ); } } // Usage of SQL Object API public class UserService { private final Jdbi jdbi; public UserService(Jdbi jdbi) { this.jdbi = jdbi; } public UserDao getUserDao() { return jdbi.onDemand(UserDao.class); } public void createUser(User user) { UserDao dao = getUserDao(); dao.insert(user); } public Optional<User> getUser(Long id) { UserDao dao = getUserDao(); return dao.findById(id); } public List<User> searchUsers(String term) { UserDao dao = getUserDao(); return dao.searchUsers("%" + term + "%"); } }

Advanced JDBI Features

5. Custom Mappers and Argument Binding

import org.jdbi.v3.core.argument.Argument; import org.jdbi.v3.core.argument.ArgumentFactory; import org.jdbi.v3.core.config.ConfigRegistry; import org.jdbi.v3.core.mapper.ColumnMapper; import org.jdbi.v3.core.mapper.RowMapper; import org.jdbi.v3.core.statement.StatementContext; import java.sql.*; import java.time.LocalDate; import java.util.Optional; // Custom argument for LocalDate class LocalDateArgument implements Argument { private final LocalDate localDate; public LocalDateArgument(LocalDate localDate) { this.localDate = localDate; } @Override public void apply(int position, PreparedStatement statement, StatementContext ctx) throws SQLException { if (localDate != null) { statement.setDate(position, Date.valueOf(localDate)); } else { statement.setNull(position, Types.DATE); } } } // Argument factory for LocalDate class LocalDateArgumentFactory implements ArgumentFactory { @Override public Optional<Argument> build(Type type, Object value, ConfigRegistry config) { if (type instanceof Class && LocalDate.class.equals(type)) { return Optional.of(new LocalDateArgument((LocalDate) value)); } return Optional.empty(); } } // Custom column mapper for LocalDate class LocalDateColumnMapper implements ColumnMapper<LocalDate> { @Override public LocalDate map(ResultSet r, int columnNumber, StatementContext ctx) throws SQLException { Date date = r.getDate(columnNumber); return date != null ? date.toLocalDate() : null; } @Override public LocalDate map(ResultSet r, String columnLabel, StatementContext ctx) throws SQLException { Date date = r.getDate(columnLabel); return date != null ? date.toLocalDate() : null; } } // Custom row mapper for complex joins class OrderWithItemsMapper implements RowMapper<OrderWithItems> { @Override public OrderWithItems map(ResultSet rs, StatementContext ctx) throws SQLException { OrderWithItems order = new OrderWithItems(); order.setId(rs.getLong("order_id")); order.setUserId(rs.getLong("user_id")); order.setTotalAmount(rs.getBigDecimal("total_amount")); order.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime()); // Map order items if they exist Long itemId = rs.getLong("item_id"); if (!rs.wasNull()) { OrderItem item = new OrderItem(); item.setId(itemId); item.setProductId(rs.getLong("product_id")); item.setQuantity(rs.getInt("quantity")); item.setUnitPrice(rs.getBigDecimal("unit_price")); order.addItem(item); } return order; } } // Register custom mappers and arguments public class JdbiCustomConfiguration { public static Jdbi configureJdbi(Jdbi jdbi) { return jdbi .registerArgument(new LocalDateArgumentFactory()) .registerColumnMapper(new LocalDateColumnMapper()) .registerRowMapper(new OrderWithItemsMapper()); } }

6. Transaction Management and Error Handling

import org.jdbi.v3.core.JdbiException; import org.jdbi.v3.core.transaction.TransactionException; public class TransactionalService { private final Jdbi jdbi; public TransactionalService(Jdbi jdbi) { this.jdbi = jdbi; } // Manual transaction management public void processOrder(Order order) { jdbi.useTransaction(handle -> { try { // Insert order Long orderId = handle.createUpdate(""" INSERT INTO orders (user_id, total_amount, status, created_at) VALUES (:userId, :totalAmount, 'PENDING', NOW()) """) .bind("userId", order.getUserId()) .bind("totalAmount", order.getTotalAmount()) .executeAndReturnGeneratedKeys("id") .mapTo(Long.class) .one(); // Insert order items Update itemInsert = handle.createUpdate(""" INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (:orderId, :productId, :quantity, :unitPrice) """); for (OrderItem item : order.getItems()) { itemInsert .bind("orderId", orderId) .bind("productId", item.getProductId()) .bind("quantity", item.getQuantity()) .bind("unitPrice", item.getUnitPrice()) .execute(); } // Update inventory for (OrderItem item : order.getItems()) { int updated = handle.createUpdate(""" UPDATE products SET stock = stock - :quantity WHERE id = :productId AND stock >= :quantity """) .bind("productId", item.getProductId()) .bind("quantity", item.getQuantity()) .execute(); if (updated == 0) { throw new InsufficientStockException( "Insufficient stock for product: " + item.getProductId()); } } } catch (JdbiException e) { // Transaction will be automatically rolled back throw new OrderProcessingException("Failed to process order", e); } }); } // Transaction with custom isolation level public void updateInventoryWithLock(Long productId, int quantity) { jdbi.useTransaction(TransactionIsolationLevel.REPEATABLE_READ, handle -> { // Check current stock with lock Integer currentStock = handle.createQuery(""" SELECT stock FROM products WHERE id = :productId FOR UPDATE """) .bind("productId", productId) .mapTo(Integer.class) .one(); if (currentStock < quantity) { throw new InsufficientStockException("Insufficient stock"); } // Update stock handle.createUpdate("UPDATE products SET stock = stock - :quantity WHERE id = :productId") .bind("productId", productId) .bind("quantity", quantity) .execute(); }); } // Retry logic for optimistic locking public boolean updateProductWithRetry(Product product, int maxRetries) { for (int attempt = 0; attempt < maxRetries; attempt++) { try { return jdbi.inTransaction(handle -> { // Check version for optimistic locking Integer currentVersion = handle.createQuery(""" SELECT version FROM products WHERE id = :id """) .bind("id", product.getId()) .mapTo(Integer.class) .one(); if (!currentVersion.equals(product.getVersion())) { throw new OptimisticLockingException("Product was modified by another transaction"); } // Update product int updated = handle.createUpdate(""" UPDATE products SET name = :name, price = :price, version = version + 1 WHERE id = :id AND version = :version """) .bindBean(product) .bind("version", product.getVersion()) .execute(); if (updated == 0) { throw new OptimisticLockingException("Update failed due to version conflict"); } return true; }); } catch (OptimisticLockingException e) { if (attempt == maxRetries - 1) { throw e; } // Wait before retry try { Thread.sleep(100 * (attempt + 1)); } catch (InterruptedException ie) { Thread.currentThread().interrupt(); throw new RuntimeException("Interrupted during retry", ie); } } } return false; } } // Custom exceptions class InsufficientStockException extends RuntimeException { public InsufficientStockException(String message) { super(message); } } class OrderProcessingException extends RuntimeException { public OrderProcessingException(String message, Throwable cause) { super(message, cause); } } class OptimisticLockingException extends RuntimeException { public OptimisticLockingException(String message) { super(message); } }

Spring Boot Integration

7. Spring Configuration

@Configuration public class JdbiConfig { @Bean @ConfigurationProperties("spring.datasource.hikari") public DataSource dataSource() { return new HikariDataSource(); } @Bean public Jdbi jdbi(DataSource dataSource) { return Jdbi.create(dataSource) .installPlugins() .registerArgument(new LocalDateArgumentFactory()) .registerColumnMapper(new LocalDateColumnMapper()) .setSqlLogger(new Slf4jSqlLogger()); } @Bean public UserDao userDao(Jdbi jdbi) { return jdbi.onDemand(UserDao.class); } @Bean public ProductDao productDao(Jdbi jdbi) { return jdbi.onDemand(ProductDao.class); } } // SQL Logger for Spring Boot class Slf4jSqlLogger implements SqlLogger { private static final Logger logger = LoggerFactory.getLogger(Slf4jSqlLogger.class); @Override public void logAfterExecution(StatementContext context) { if (logger.isDebugEnabled()) { Duration duration = Duration.between( context.getExecutionMoment(), context.getCompletionMoment() ); logger.debug("SQL: {} | Time: {}ms", context.getRenderedSql(), duration.toMillis()); } } @Override public void logException(StatementContext context, SQLException ex) { logger.error("SQL Error: {} | Exception: {}", context.getRenderedSql(), ex.getMessage(), ex); } }

8. Spring Service with JDBI

@Service @Transactional public class UserManagementService { private final UserDao userDao; private final ProductDao productDao; private final Jdbi jdbi; public UserManagementService(UserDao userDao, ProductDao productDao, Jdbi jdbi) { this.userDao = userDao; this.productDao = productDao; this.jdbi = jdbi; } public User createUser(CreateUserRequest request) { User user = new User(request.getUsername(), request.getEmail()); userDao.insert(user); return user; } public Optional<User> getUser(Long id) { return userDao.findById(id); } public List<User> searchUsers(String searchTerm) { return userDao.searchUsers("%" + searchTerm + "%"); } public void updateUserEmail(Long userId, String newEmail) { int updated = userDao.updateEmail(userId, newEmail); if (updated == 0) { throw new UserNotFoundException("User not found: " + userId); } } // Complex business logic with manual transaction public Order placeOrder(OrderRequest request) { return jdbi.inTransaction(handle -> { // Verify products and calculate total BigDecimal totalAmount = BigDecimal.ZERO; for (OrderItemRequest itemRequest : request.getItems()) { Product product = productDao.findById(itemRequest.getProductId()) .orElseThrow(() -> new ProductNotFoundException( "Product not found: " + itemRequest.getProductId())); if (product.getStock() < itemRequest.getQuantity()) { throw new InsufficientStockException( "Insufficient stock for product: " + product.getName()); } totalAmount = totalAmount.add( product.getPrice().multiply(BigDecimal.valueOf(itemRequest.getQuantity()))); } // Create order Order order = new Order(request.getUserId(), totalAmount); Long orderId = handle.createUpdate(""" INSERT INTO orders (user_id, total_amount, status, created_at) VALUES (:userId, :totalAmount, 'PENDING', NOW()) """) .bind("userId", order.getUserId()) .bind("totalAmount", order.getTotalAmount()) .executeAndReturnGeneratedKeys("id") .mapTo(Long.class) .one(); order.setId(orderId); // Create order items and update inventory for (OrderItemRequest itemRequest : request.getItems()) { // Insert order item handle.createUpdate(""" INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (:orderId, :productId, :quantity, (SELECT price FROM products WHERE id = :productId)) """) .bind("orderId", orderId) .bind("productId", itemRequest.getProductId()) .bind("quantity", itemRequest.getQuantity()) .execute(); // Update product stock productDao.reduceStock(itemRequest.getProductId(), itemRequest.getQuantity()); } return order; }); } // Statistics using fluent API public UserStatistics getUserStatistics(Long userId) { return jdbi.withHandle(handle -> { Integer totalOrders = handle.createQuery(""" SELECT COUNT(*) FROM orders WHERE user_id = :userId """) .bind("userId", userId) .mapTo(Integer.class) .one(); BigDecimal totalSpent = handle.createQuery(""" SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE user_id = :userId AND status = 'COMPLETED' """) .bind("userId", userId) .mapTo(BigDecimal.class) .one(); LocalDateTime lastOrderDate = handle.createQuery(""" SELECT MAX(created_at) FROM orders WHERE user_id = :userId """) .bind("userId", userId) .mapTo(LocalDateTime.class) .one(); return new UserStatistics(totalOrders, totalSpent, lastOrderDate); }); } } // Request/Response DTOs class CreateUserRequest { private String username; private String email; // Getters and setters public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } } class OrderRequest { private Long userId; private List<OrderItemRequest> items; // Getters and setters public Long getUserId() { return userId; } public void setUserId(Long userId) { this.userId = userId; } public List<OrderItemRequest> getItems() { return items; } public void setItems(List<OrderItemRequest> items) { this.items = items; } } class OrderItemRequest { private Long productId; private int quantity; // Getters and setters public Long getProductId() { return productId; } public void setProductId(Long productId) { this.productId = productId; } public int getQuantity() { return quantity; } public void setQuantity(int quantity) { this.quantity = quantity; } } class UserStatistics { private final int totalOrders; private final BigDecimal totalSpent; private final LocalDateTime lastOrderDate; public UserStatistics(int totalOrders, BigDecimal totalSpent, LocalDateTime lastOrderDate) { this.totalOrders = totalOrders; this.totalSpent = totalSpent; this.lastOrderDate = lastOrderDate; } // Getters public int getTotalOrders() { return totalOrders; } public BigDecimal getTotalSpent() { return totalSpent; } public LocalDateTime getLastOrderDate() { return lastOrderDate; } }

Testing JDBI Implementations

9. JDBI Testing

@ExtendWith(MockitoExtension.class) class UserRepositoryTest { private Jdbi jdbi; private UserRepository userRepository; @BeforeEach void setUp() { jdbi = Jdbi.create("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1", "sa", ""); jdbi.installPlugins(); // Create schema jdbi.useHandle(handle -> { handle.execute(""" CREATE TABLE users ( id BIGINT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL, created_at TIMESTAMP NOT NULL, points INTEGER DEFAULT 0 ) """); }); userRepository = new UserRepository(jdbi); } @AfterEach void tearDown() { jdbi.useHandle(handle -> { handle.execute("DROP TABLE users"); }); } @Test void createUser_ThenFind_ShouldReturnUser() { // Arrange User user = new User("testuser", "[email protected]"); // Act userRepository.createUser(user); Optional<User> foundUser = userRepository.findUserById(1L); // Assert assertTrue(foundUser.isPresent()); assertEquals("testuser", foundUser.get().getUsername()); assertEquals("[email protected]", foundUser.get().getEmail()); } @Test void updateUserEmail_WithExistingUser_ShouldUpdateEmail() { // Arrange User user = new User("testuser", "[email protected]"); userRepository.createUser(user); // Act int updated = userRepository.updateUserEmail(1L, "[email protected]"); Optional<User> foundUser = userRepository.findUserById(1L); // Assert assertEquals(1, updated); assertTrue(foundUser.isPresent()); assertEquals("[email protected]", foundUser.get().getEmail()); } } @SpringBootTest class UserManagementServiceIntegrationTest { @Autowired private UserManagementService userService; @Autowired private Jdbi jdbi; @BeforeEach void setUp() { // Initialize test data jdbi.useHandle(handle -> { handle.execute("DELETE FROM orders"); handle.execute("DELETE FROM users"); handle.execute("DELETE FROM products"); handle.execute(""" INSERT INTO users (id, username, email, created_at) VALUES (1, 'testuser', '[email protected]', NOW()) """); handle.execute(""" INSERT INTO products (id, name, price, stock) VALUES (1, 'Test Product', 99.99, 10) """); }); } @Test void placeOrder_WithValidRequest_ShouldCreateOrder() { // Arrange OrderRequest request = new OrderRequest(); request.setUserId(1L); request.setItems(List.of( new OrderItemRequest(1L, 2) // productId, quantity )); // Act Order order = userService.placeOrder(request); // Assert assertNotNull(order.getId()); assertEquals(new BigDecimal("199.98"), order.getTotalAmount()); } @Test void getUserStatistics_WithOrders_ShouldReturnStatistics() { // Arrange - create test orders jdbi.useHandle(handle -> { handle.execute(""" INSERT INTO orders (user_id, total_amount, status, created_at) VALUES (1, 100.00, 'COMPLETED', NOW()), (1, 50.00, 'COMPLETED', NOW()) """); }); // Act UserStatistics stats = userService.getUserStatistics(1L); // Assert assertEquals(2, stats.getTotalOrders()); assertEquals(new BigDecimal("150.00"), stats.getTotalSpent()); } }

Key Benefits of JDBI

1. Lightweight: Minimal overhead compared to full ORMs
2. SQL-Centric: Work with SQL directly while reducing boilerplate
3. Flexible: Choose between fluent API and declarative SQL Object API
4. Type Safety: Compile-time checking with SQL Object API
5. Extensible: Custom mappers, arguments, and plugins
6. Transaction Management: Built-in transaction support
7. Spring Integration: Seamless integration with Spring Framework
8. Testing Friendly: Easy to test with in-memory databases

JDBI strikes an excellent balance between raw JDBC's control and full ORM's convenience, making it ideal for applications that need SQL transparency with reduced boilerplate code.