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
- Lightweight: Minimal overhead compared to full ORMs
- SQL-Centric: Work with SQL directly while reducing boilerplate
- Flexible: Choose between fluent API and declarative SQL Object API
- Type Safety: Compile-time checking with SQL Object API
- Extensible: Custom mappers, arguments, and plugins
- Transaction Management: Built-in transaction support
- Spring Integration: Seamless integration with Spring Framework
- 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.