Database

Database API

A modern, type-safe database wrapper for Java with step builder pattern and prepared queries support.

Features:

• ✅ Step Builder Pattern - Compile-time safety prevents invalid query construction
• ✅ Prepared Queries - Build once, execute many times (3-5x performance boost)
• ✅ Auto-managed Connections - Implements AutoCloseable for resource safety
• ✅ HikariCP Support - Optional connection pooling for production environments
• ✅ Multiple Databases - SQLite, MySQL, MariaDB, PostgreSQL, H2
• ✅ Fluent API - Clean, readable query construction

Quick Start

Setup

// SQLite (no connection pooling)
Database db = Database.sqlite("app.db");

// MySQL with HikariCP
Database db = Database.builder()
    .jdbcUrl(DatabaseUtils.mysql("localhost", "mydb"))
    .credentials("user", "password")
    .poolSize(4, 16)
    .usePooling()
    .build();

Basic Usage

// SELECT
List<User> users = db.select()
    .from("users")
    .where("age > ?", 18)
    .orderBy("created_at DESC")
    .limit(10)
    .fetch(rs -> new User(
        rs.getInt("id"),
        rs.getString("username"),
        rs.getString("email")
    ));

// INSERT
db.insert("users")
    .value("username", "john")
    .value("email", "john@example.com")
    .execute();

// UPDATE
db.update("users")
    .set("email", "new@example.com")
    .where("id = ?", 1)
    .execute();

// DELETE
db.delete("users")
    .where("inactive = ?", true)
    .execute();

Step Builder Pattern

The step builder pattern ensures type-safe query construction at compile time:

// ❌ This won't compile - missing FROM clause
db.select("*").fetch(mapper);

// ❌ This won't compile - can't use OFFSET without LIMIT
db.select("*").from("users").offset(10).fetch(mapper);

// ✅ Compiler guides you through valid steps
db.select("*")
    .from("users")              // Required
    .where("active = ?", true)  // Optional
    .orderBy("id DESC")         // Optional
    .limit(10)                  // Optional
    .offset(20)                 // Only available after LIMIT
    .fetch(mapper);

Prepared Queries

For queries executed multiple times, use prepared queries for better performance:

// Build query once
SelectQuery findUser = db.select()
    .from("users")
    .where("id = ?")
    .prepare();

// Execute many times
User user1 = findUser.fetchFirst(userMapper, 1);
User user2 = findUser.fetchFirst(userMapper, 2);
User user3 = findUser.fetchFirst(userMapper, 3);

Real-World Example

public class UserRepository {

    private final SelectQuery findById;
    private final InsertQuery createUser;
    private final UpdateQuery updateEmail;
    
    public UserRepository(Database db) {
        // Prepare queries once in constructor
        this.findById = db.select()
            .from("users")
            .where("id = ?")
            .prepare();
        
        this.createUser = db.insert("users")
            .value("username", null)
            .value("email", null)
            .prepare();
        
        this.updateEmail = db.update("users")
            .set("email", null)
            .where("id = ?")
            .prepare();
    }
    
    // Reuse prepared queries
    public User findById(int id) throws SQLException {
        return findById.fetchFirst(this::mapUser, id);
    }
    
    public void create(String username, String email) throws SQLException {
        createUser.execute(username, email);
    }
    
    public void updateEmail(int id, String email) throws SQLException {
        updateEmail.execute(email, id);
    }
}

Advanced Features

Transactions

db.transaction(conn -> {
    db.executeUpdate(conn, "INSERT INTO users (name) VALUES (?)", "john");
    db.executeUpdate(conn, "INSERT INTO profiles (user_id) VALUES (?)", userId);
    // Automatic rollback on exception
});

Complex Queries

// JOINs
List<Post> posts = db.select("posts.*", "users.username")
    .from("posts")
    .innerJoin("users", "users.id = posts.user_id")
    .where("posts.published = ?", true)
    .orderBy("posts.created_at DESC")
    .fetch(postMapper);

// GROUP BY & HAVING
List<Stats> stats = db.select("user_id", "COUNT(*) as count")
    .from("posts")
    .groupBy("user_id")
    .having("COUNT(*) > 10")
    .fetch(statsMapper);

DatabaseUtils

Helper methods for JDBC URL construction:

// SQLite
String url = DatabaseUtils.sqlite("database.db");

// MySQL with parameters
String params = DatabaseUtils.mysqlParams()
    .useSSL(false)
    .serverTimezone("UTC")
    .build();
String url = DatabaseUtils.mysql("localhost", "mydb", params);

// PostgreSQL
String url = DatabaseUtils.postgresql("localhost", 5432, "mydb");

// H2
String url = DatabaseUtils.h2Memory("testdb");

Requirements

• Java 17+
• HikariCP (optional, for connection pooling)
• JDBC driver for your database