Advanced Topics

// Base class mapped to the 'person' table
@Table("person")
@SubClasses(
    discriminator = "person_type", // Discriminator column in 'person' table
    value = {
        @SubClass(value = Employee.class, discriminatorValue = "EMP"), // 'EMP' identifies Employee rows
        @SubClass(value = Customer.class, discriminatorValue = "CUST") // 'CUST' identifies Customer rows
    }
)
public abstract class Person {
    @Id Long id;
    String name;
    @FieldName("person_type") String type; // Field mapping to the discriminator column

    // Getters and setters omitted for brevity
}

// Employee subclass - fields map to columns in 'person' table
public class Employee extends Person {
    String department;
    BigDecimal salary;

    // Getters and setters omitted for brevity
}

// Customer subclass - fields map to columns in 'person' table
public class Customer extends Person {
    Integer loyaltyPoints;

    // Getters and setters omitted for brevity
}

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

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

// You can also query directly for a subclass
List<Employee> employees = PojoQuery.build(Employee.class)
    .addWhere("employee.department = ?", "Sales") // Note: Alias might be 'employee' or 'person' depending on context
    .execute(dataSource);

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"));
    });

@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);

// --- Insert ---
User newUser = new User();
newUser.setFirstName("Jane");
newUser.setLastName("Doe");
newUser.setEmail("jane.doe@example.com");
PojoQuery.insert(dataSource, newUser); // newUser.getId() will be populated if auto-generated
System.out.println("Inserted user with ID: " + newUser.getId());

// --- Update ---
User existingUser = repo.findById(newUser.getId()); // Fetch first
if (existingUser != null) {
    existingUser.setEmail("jane.d@example.com");
    int updatedRows = PojoQuery.update(dataSource, existingUser);
    System.out.println("Updated rows: " + updatedRows);
}

// --- Delete ---
PojoQuery.delete(dataSource, existingUser); // Delete by entity instance
// OR
PojoQuery.deleteById(dataSource, User.class, existingUser.getId()); // Delete by ID

import java.sql.Connection;

DB.runInTransaction(dataSource, (Connection connection) -> {
    // Operations within this lambda run in a single transaction
    User user = new User();
    user.setFirstName("Temp");
    PojoQuery.insert(connection, user); // Use the provided connection

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

    // If no exception is thrown, the transaction commits automatically.
    // If an exception occurs, it rolls back automatically.
    return true; // Return value from the transaction lambda
});