Advanced Topics

This section covers more advanced features and usage patterns in PojoQuery.

Inheritance Mapping

PojoQuery supports table-per-subclass inheritance using the @SubClasses annotation. Each subclass has its own table that extends the base table through a shared primary key.

Annotate the base class with @Table pointing to the base table.
Annotate the base class with @SubClasses, listing all subclass types.
Each subclass extends the base class and has its own @Table annotation.
The subclass table uses the same primary key as the base table (foreign key relationship).

Database Schema:

POJOs:

// Base class mapped to the 'person' table
@Table("person")
@SubClasses({Employee.class, Customer.class})
public static class Person {
    @Id Long id;
    String name;

    public Person() {}

    public Person(String name) {
        this.name = name;
    }

    public Long getId() { return id; }
    public String getName() { return name; }
}

// Employee subclass - has its own 'employee' table
@Table("employee")
public static class Employee extends Person {
    String department;
    BigDecimal salary;

    public Employee() {}

    public Employee(String name, String department, BigDecimal salary) {
        super(name);
        this.department = department;
        this.salary = salary;
    }

    public String getDepartment() { return department; }
    public BigDecimal getSalary() { return salary; }
}

// Customer subclass - has its own 'customer' table
@Table("customer")
public static class Customer extends Person {
    Integer loyaltyPoints;

    public Customer() {}

    public Customer(String name, Integer loyaltyPoints) {
        super(name);
        this.loyaltyPoints = loyaltyPoints;
    }

    public Integer getLoyaltyPoints() { return loyaltyPoints; }
}

Querying:

// Querying for the base class fetches instances of the correct subclass
List<Person> persons = PojoQuery.build(Person.class).execute(c);

for (Person p : persons) {
    if (p instanceof Employee emp) {
        System.out.println("Employee: " + emp.getName() + ", Dept: " + emp.getDepartment());
    } else if (p instanceof Customer cust) {
        System.out.println("Customer: " + cust.getName() + ", Points: " + cust.getLoyaltyPoints());
    }
}

You can also query directly for a subclass:

// You can also query directly for a subclass
List<Employee> employees = PojoQuery.build(Employee.class)
    .addWhere("{employee}.department = ?", "Sales")
    .execute(c);

for (Employee emp : employees) {
    System.out.println("Sales employee: " + emp.getName());
}

Streaming Large Result Sets

For queries returning many rows, use executeStreaming() to process results row-by-row, avoiding OutOfMemoryError.

PojoQuery<LogEntry> query = PojoQuery.build(LogEntry.class)
    .addWhere("log.timestamp > ?", oneHourAgo);

// Process stream using DataSource
try (Stream<LogEntry> stream = query.executeStreaming(dataSource).stream()) {
    long errorCount = stream
        .filter(entry -> "ERROR".equals(entry.level))
        .count();
    System.out.println("Found " + errorCount + " errors.");
}

// Alternative using DB helper for raw SQL streaming
DB.queryRowsStreaming(dataSource,
    SqlExpression.sql("SELECT id, message FROM huge_log WHERE level = ?", "WARN"),
    rowMap -> {
        // Process each row as a Map<String, Object>
        System.out.println("Warning ID: " + rowMap.get("id"));
    });

Custom Joins and Conditions

Use @Join or addJoin() for joins not covered by convention-based relationship mapping. Use curly braces {} in addWhere() for alias resolution when referencing explicitly joined tables.

@Table("article")
@Join(type = JoinType.INNER, tableName = "category", alias = "cat", joinCondition = "article.category_id = cat.id")
public class ArticleWithCategory {
    @Id Long id;
    String title;
    // Map category fields explicitly since we're using @Join
    @Select("cat.name") String categoryName;
}

// Querying with condition on joined table
List<ArticleWithCategory> techArticles = PojoQuery.build(ArticleWithCategory.class)
    .addWhere("{cat.name} = ?", "Technology") // Use alias from @Join
    .execute(dataSource);

CRUD Operations

PojoQuery provides static helpers for basic Insert, Update, Delete. Use DB.runInTransaction() to ensure all write operations are atomic:

Insert: PojoQuery.insert(connection, entity) or DB.insert(…)
Handles single-table and multi-table inheritance inserts.
Populates the @Id field if it’s auto-generated.
Update: PojoQuery.update(connection, entity) or DB.update(…)
Updates based on the @Id field(s).
Handles multi-table inheritance updates.
Respects @NoUpdate.
Can optionally handle optimistic locking if the entity implements HasVersion.
Delete: PojoQuery.delete(connection, entity) or PojoQuery.deleteById(connection, Class, id)
Deletes based on the @Id field(s).
Handles multi-table inheritance deletes (deletes from all mapped tables).

Example:

// Use runInTransaction to ensure all operations are atomic
DB.withConnection(dataSource, (Connection c) -> {
    // --- Insert ---
    User newUser = new User();
    newUser.setFirstName("Jane");
    newUser.setLastName("Doe");
    newUser.setEmail("jane.doe@example.com");
    PojoQuery.insert(c, newUser);
    // newUser.getId() is now populated if auto-generated
    System.out.println("Inserted user with ID: " + newUser.getId());

    // --- Query ---
    User existingUser = PojoQuery.build(User.class)
        .addWhere("{app_user}.id = ?", newUser.getId())
        .execute(c)
        .stream().findFirst().orElse(null);

    // --- Update ---
    if (existingUser != null) {
        existingUser.setEmail("jane.d@example.com");
        int updatedRows = PojoQuery.update(c, existingUser);
        System.out.println("Updated rows: " + updatedRows);
    }

    // --- Delete ---
    // Delete by entity instance
    PojoQuery.delete(c, existingUser);
    
    // Or delete by ID directly
    // PojoQuery.deleteById(c, User.class, existingUser.getId());
});

Transactions

PojoQuery provides transaction support through DB.runInTransaction(), which handles commit/rollback automatically.

Basic Usage

import java.sql.Connection;
import org.pojoquery.DB;

DB.runInTransaction(dataSource, (Connection connection) -> {
    // All operations use the same connection = same transaction
    User user = new User();
    user.setFirstName("Alice");
    PojoQuery.insert(connection, user);

    Account account = new Account();
    account.setUserId(user.getId());
    account.setBalance(BigDecimal.ZERO);
    PojoQuery.insert(connection, account);

    return true; // Return value from the transaction
});

How It Works

Auto-commit disabled: The connection’s auto-commit is set to false
Commit on success: If the lambda completes without exception, the transaction commits
Rollback on failure: If any exception is thrown (checked or unchecked), the transaction rolls back
Return values: The lambda can return a value of any type

Method Signatures

There are two overloads available:

// Using a DataSource - connection is obtained and closed automatically
public static <T> T runInTransaction(DataSource dataSource, Transaction<T> transaction);

// Using an existing Connection - you manage the connection lifecycle
public static <T> T runInTransaction(Connection connection, Transaction<T> transaction);

Returning Values

Use the return value to get results from your transaction:

Order createdOrder = DB.runInTransaction(dataSource, connection -> {
    Order order = new Order();
    order.setCustomerId(customerId);
    order.setOrderDate(LocalDate.now());
    PojoQuery.insert(connection, order);

    for (CartItem item : cartItems) {
        OrderLine line = new OrderLine();
        line.setOrderId(order.getId());
        line.setProductId(item.getProductId());
        line.setQuantity(item.getQuantity());
        PojoQuery.insert(connection, line);
    }

    return order; // Return the created order with its generated ID
});

Exception Handling

When an exception occurs, the transaction rolls back and the exception propagates:

try {
    DB.runInTransaction(dataSource, connection -> {
        PojoQuery.insert(connection, order);
        PojoQuery.insert(connection, invalidOrderLine); // This fails
        return null;
    });
} catch (DB.DatabaseException e) {
    // Transaction was rolled back
    // The cause contains the original SQLException
    System.err.println("Transaction failed: " + e.getCause().getMessage());
}

Both SQLException and RuntimeException trigger a rollback. SQLExceptions are wrapped in DB.DatabaseException.

Void Transactions

If you don’t need a return value, simply return null or any placeholder:

DB.runInTransaction(dataSource, connection -> {
    PojoQuery.delete(connection, oldRecord);
    PojoQuery.insert(connection, newRecord);
    return null; // No meaningful return value
});

Nested Transactions

PojoQuery does not support true nested transactions. If you call runInTransaction() with an existing connection that’s already in a transaction, both blocks share the same transaction:

DB.runInTransaction(dataSource, outerConn -> {
    PojoQuery.insert(outerConn, record1);

    // This uses the same transaction - NOT a nested transaction
    DB.runInTransaction(outerConn, innerConn -> {
        PojoQuery.insert(innerConn, record2);
        return null;
    });

    return null;
});
// Both inserts commit or rollback together

For complex transaction scenarios requiring savepoints or isolation level control, obtain a Connection directly from your DataSource and use standard JDBC transaction APIs.

Optimistic Locking

PojoQuery supports optimistic locking to prevent lost updates in concurrent environments. This is implemented through the HasVersion interface.

How It Works

Your entity implements HasVersion, providing a version field
On each update, PojoQuery checks that the version in the database matches the version in your entity
If versions match, the update proceeds and the version is incremented
If versions don’t match (another transaction modified the row), a StaleObjectException is thrown

Implementation

import org.pojoquery.HasVersion;

@Table("product")
public class Product implements HasVersion {
    @Id Long id;
    String name;
    BigDecimal price;
    Long version; // Version column in database

    @Override
    public Long getVersion() {
        return version;
    }

    @Override
    public void setVersion(Long version) {
        this.version = version;
    }

    // Other getters and setters omitted for brevity
}

Database Schema

Your table needs a version column (or whatever name you use):

CREATE TABLE product (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    price DECIMAL(10,2),
    version BIGINT DEFAULT 0
);

Usage Example

import org.pojoquery.StaleObjectException;

// User A fetches the product
Product productA = PojoQuery.build(Product.class)
    .addWhere("product.id = ?", 123L)
    .execute(dataSource)
    .get(0);
// productA.getVersion() = 1

// User B fetches the same product
Product productB = PojoQuery.build(Product.class)
    .addWhere("product.id = ?", 123L)
    .execute(dataSource)
    .get(0);
// productB.getVersion() = 1

// User A updates the product within a transaction
DB.runInTransaction(dataSource, (Connection c) -> {
    productA.setPrice(new BigDecimal("29.99"));
    PojoQuery.update(c, productA); // Success! version becomes 2
});

// User B tries to update the same product
DB.runInTransaction(dataSource, (Connection c) -> {
    productB.setPrice(new BigDecimal("24.99"));
    try {
        PojoQuery.update(c, productB); // Fails! version mismatch
    } catch (StaleObjectException e) {
        // Handle the conflict - typically refresh and retry, or notify user
        System.out.println("Product was modified by another user. Please refresh.");
    }
});

Handling Stale Object Exceptions

public void updateProductPrice(Long productId, BigDecimal newPrice) {
    int maxRetries = 3;
    for (int attempt = 0; attempt < maxRetries; attempt++) {
        try {
            DB.runInTransaction(dataSource, (Connection c) -> {
                Product product = PojoQuery.build(Product.class)
                    .addWhere("product.id = ?", productId)
                    .execute(c)
                    .stream().findFirst()
                    .orElseThrow(() -> new RuntimeException("Product not found"));

                product.setPrice(newPrice);
                PojoQuery.update(c, product);
            });
            return; // Success

        } catch (StaleObjectException e) {
            if (attempt == maxRetries - 1) {
                throw new RuntimeException("Failed to update after " + maxRetries + " attempts", e);
            }
            // Retry with fresh data
        }
    }
}

Future Enhancements

This section documents potential features that may be added in future versions.

Java Record Support (Planned)

Java records provide a concise way to declare immutable data classes. While runtime PojoQuery requires mutable POJOs with no-arg constructors, the generated typed query API could support records in a future version.

Why records don’t work with runtime PojoQuery:

Records have no no-arg constructor — PojoQuery creates instances with new Entity() then sets fields
Record fields are final — PojoQuery sets fields via reflection after construction
Records can’t use inheritance — patterns like EmployeeWithProjects extends Employee won’t work

Why records could work with generated queries:

The annotation processor has full knowledge of field names, types, and column mappings at compile time. Instead of generating field-by-field assignment:

// Current generated code (for mutable classes)
employee = new Employee();
fmId.apply(employee, row.get("employee.id"));
fmFirstName.apply(employee, row.get("employee.firstName"));

It could generate constructor calls for records:

// Possible future generated code (for records)
employee = new EmployeeRecord(
    (Long) row.get("employee.id"),
    (String) row.get("employee.firstName")
);

Potential detection strategy:

Has no-arg constructor → use field injection (current behavior)
Is a record or only has all-args constructor → use constructor injection

This would keep runtime PojoQuery simple while enabling immutable data classes for generated type-safe queries.