PojoQuery Examples

This page provides practical examples of using PojoQuery.

Example 1: Basic Select

Fetch all users ordered by last name.

POJO:

@Table("user")
public class User {
    @Id Long id;
    String firstName;
    String lastName;
    String email;

    // Getters and setters omitted for brevity
}

Query:

List<User> users = PojoQuery.build(User.class)
    .addOrderBy("user.lastName ASC")
    .execute(dataSource);

Example 2: Select with Join

Fetch an article including its author details.

POJOs:

@Table("article")
public class Article {
    @Id Long id;
    String title;

    // Getters and setters omitted for brevity
}

public class ArticleWithAuthor extends Article {
    User author; // Automatically joins on 'author_id' column

    // Getters and setters omitted for brevity
}

@Table("user")
public class User {
    @Id Long id;
    String firstName;
    String lastName;

    // Getters and setters omitted for brevity
}

Query:

ArticleWithAuthor article = PojoQuery.build(ArticleWithAuthor.class)
    .addWhere("article.id = ?", 123L)
    .execute(dataSource)
    .stream().findFirst().orElse(null);

if (article != null) {
    System.out.println("Author: " + article.getAuthor().getFirstName());
}

Example 3: One-to-Many Relationship

Fetch an article including a list of its comments.

POJOs:

@Table("article")
public class Article {
    @Id Long id;
    String title;

    // Getters and setters omitted for brevity
}

public class ArticleWithComments extends Article {
    List<Comment> comments; // Automatically joins on 'article_id' in comment table

    // Getters and setters omitted for brevity
}

@Table("comment")
public class Comment {
    @Id Long id;
    String text;
    Date submitDate;

    // Getters and setters omitted for brevity
}

Query:

ArticleWithComments article = PojoQuery.build(ArticleWithComments.class)
    .addWhere("article.id = ?", 456L)
    .addOrderBy("comments.submitDate DESC") // Order comments
    .execute(dataSource)
    .stream().findFirst().orElse(null);

if (article != null) {
    System.out.println("Comments count: " + article.getComments().size());
    article.getComments().forEach(c -> System.out.println("- " + c.getText()));
}

Example 4: Aggregation (`@GroupBy`, `@Select`)

Get the count and latest comment date for each article.

POJO:

@Table("comment") // Query starts from comment table
@GroupBy("comment.article_id") // Group by article
public class ArticleCommentStats {
    @FieldName("article_id") // Map the grouping key
    Long articleId;

    @Select("COUNT(*)")
    Integer commentCount;

    @Select("MAX(submitDate)")
    Date lastCommentDate;

    // Getters and setters omitted for brevity
}

Query:

List<ArticleCommentStats> stats = PojoQuery.build(ArticleCommentStats.class)
    .execute(dataSource);

stats.forEach(s -> System.out.println(
    "Article " + s.articleId + ": " + s.commentCount + " comments, last on " + s.lastCommentDate
));

Example 5: Custom SQL Field (`@Select`)

Fetch user with a calculated full name field.

POJO:

@Table("user")
public class UserWithFullName {
    @Id Long id;
    String firstName;
    String lastName;

    @Select("CONCAT(firstName, ' ', lastName)") // Database-specific function
    String fullName;

    // Getters and setters omitted for brevity
}

Query:

UserWithFullName user = PojoQuery.build(UserWithFullName.class)
    .addWhere("user.id = ?", 789L)
    .execute(dataSource)
    .stream().findFirst().orElse(null);

if (user != null) {
    System.out.println("Full Name: " + user.getFullName());
}

Example 6: Insert Operation

Insert a new user record.

POJO: (Same as Example 1)

Operation:

// Use runInTransaction for all write operations
DB.runInTransaction(dataSource, (Connection c) -> {
    User newUser = new User();
    newUser.setFirstName("Alice");
    newUser.setLastName("Wonder");
    newUser.setEmail("alice@example.com");

    PojoQuery.insert(c, newUser);
    // newUser.getId() is now populated if it was auto-generated
    System.out.println("New user ID: " + newUser.getId());
});

Example 7: Update Operation

Update an existing user’s email.

POJO: (Same as Example 1)

Operation:

// Use runInTransaction for all write operations
DB.runInTransaction(dataSource, (Connection c) -> {
    // Assume 'userToUpdate' was fetched previously
    userToUpdate.setEmail("alice.w@example.com");
    int updatedRows = PojoQuery.update(c, userToUpdate);
    System.out.println("Rows updated: " + updatedRows);
});

Example 8: Many-to-Many Relationship

Fetch articles with their associated tags using a link table.

Database Schema:

-- article table
CREATE TABLE article (id BIGINT PRIMARY KEY, title VARCHAR(255));

-- tag table
CREATE TABLE tag (id BIGINT PRIMARY KEY, name VARCHAR(100));

-- link table (many-to-many)
CREATE TABLE article_tag (
    article_id BIGINT REFERENCES article(id),
    tag_id BIGINT REFERENCES tag(id),
    PRIMARY KEY (article_id, tag_id)
);

POJOs:

@Table("article")
public class ArticleWithTags {
    @Id Long id;
    String title;

    @Link(linktable = "article_tag", linkfield = "article_id", foreignlinkfield = "tag_id")
    List<Tag> tags;

    // Getters and setters omitted for brevity
}

@Table("tag")
public class Tag {
    @Id Long id;
    String name;

    // Getters and setters omitted for brevity
}

Query:

List<ArticleWithTags> articles = PojoQuery.build(ArticleWithTags.class)
    .execute(dataSource);

for (ArticleWithTags article : articles) {
    System.out.println(article.getTitle() + " - Tags: " +
        article.getTags().stream().map(Tag::getName).collect(Collectors.joining(", ")));
}

Example 9: Filtered Relationships with `@JoinCondition`

Fetch events with separate lists for visitors and organizers from the same person table.

Database Schema:

POJOs:

@Table("person")
public static class Person {
    @Id Long id;
    String firstname;
    String lastname;

    public Person() {}

    public Person(String firstname, String lastname) {
        this.firstname = firstname;
        this.lastname = lastname;
    }

    public Long getId() { return id; }
    public String getFirstname() { return firstname; }
    public String getLastname() { return lastname; }
    public String getFullName() { return firstname + " " + lastname; }
}

@Table("event")
public static class EventWithParticipants {
    @Id Long id;
    String title;

    @Link(linktable = "event_person", linkfield = "event_id", foreignlinkfield = "person_id")
    @JoinCondition("{this}.id = {linktable}.event_id AND {linktable}.role = 'visitor'")
    List<Person> visitors;

    @Link(linktable = "event_person", linkfield = "event_id", foreignlinkfield = "person_id")
    @JoinCondition("{this}.id = {linktable}.event_id AND {linktable}.role = 'organizer'")
    List<Person> organizers;

    public Long getId() { return id; }
    public String getTitle() { return title; }
    public List<Person> getVisitors() { return visitors; }
    public List<Person> getOrganizers() { return organizers; }
}

Query:

EventWithParticipants event = PojoQuery.build(EventWithParticipants.class)
    .execute(dataSource)
    .stream().findFirst().orElse(null);

if (event != null) {
    System.out.println("Event: " + event.getTitle());
    System.out.println("Visitors (" + event.getVisitors().size() + "):");
    for (Person visitor : event.getVisitors()) {
        System.out.println("  - " + visitor.getFullName());
    }
    System.out.println("Organizers (" + event.getOrganizers().size() + "):");
    for (Person organizer : event.getOrganizers()) {
        System.out.println("  - " + organizer.getFullName());
    }
}

Example 10: Embedded Objects

Map address fields from the same table into a nested object.

Database Schema:

POJOs:

public static class Address {
    String street;
    String city;
    String zip;

    public Address() {}

    public Address(String street, String city, String zip) {
        this.street = street;
        this.city = city;
        this.zip = zip;
    }

    public String getStreet() { return street; }
    public String getCity() { return city; }
    public String getZip() { return zip; }
}

@Table("customer")
public static class Customer {
    @Id Long id;
    String name;

    @Embedded(prefix = "ship_")
    Address shippingAddress;

    @Embedded(prefix = "bill_")
    Address billingAddress;

    public Customer() {}

    public Customer(String name, Address shippingAddress, Address billingAddress) {
        this.name = name;
        this.shippingAddress = shippingAddress;
        this.billingAddress = billingAddress;
    }

    public Long getId() { return id; }
    public String getName() { return name; }
    public Address getShippingAddress() { return shippingAddress; }
    public Address getBillingAddress() { return billingAddress; }
}

Query:

        Customer customer = PojoQuery.build(Customer.class)
            .addWhere("{customer}.id = ?", 1L)
            .execute(c)
            .stream().findFirst().orElse(null);

        if (customer != null) {
            System.out.println("Customer: " + customer.getName());
            System.out.println("Ship to: " + customer.getShippingAddress().getStreet() 
                + ", " + customer.getShippingAddress().getCity());
            System.out.println("Bill to: " + customer.getBillingAddress().getStreet() 
                + ", " + customer.getBillingAddress().getCity());
        }

Example 11: Streaming Large Result Sets

Process millions of log entries without loading all into memory.

POJO:

@Table("access_log")
public class AccessLog {
    @Id Long id;
    String ipAddress;
    String requestPath;
    Integer statusCode;
    LocalDate accessDate;

    // Getters and setters omitted for brevity
}

Streaming Query:

LocalDate yesterday = LocalDate.now().minusDays(1);

// Using try-with-resources to ensure proper cleanup
try (Stream<AccessLog> stream = PojoQuery.build(AccessLog.class)
        .addWhere("access_log.accessDate = ?", yesterday)
        .executeStreaming(dataSource)
        .stream()) {

    // Count 404 errors without loading all rows
    long notFoundCount = stream
        .filter(log -> log.getStatusCode() == 404)
        .count();

    System.out.println("404 errors yesterday: " + notFoundCount);
}

// Alternative: process each row with a callback
DB.queryRowsStreaming(dataSource,
    SqlExpression.sql("SELECT * FROM access_log WHERE status_code >= ?", 500),
    row -> {
        System.out.println("Error on path: " + row.get("request_path"));
    });

Example 12: Handling Optional Relationships

Gracefully handle nullable foreign keys.

POJOs:

@Table("task")
public class Task {
    @Id Long id;
    String title;

    User assignee; // Will be null if assignee_id is null (LEFT JOIN by default)

    // Getters and setters omitted for brevity
}

@Table("user")
public class User {
    @Id Long id;
    String name;

    // Getters and setters omitted for brevity
}

Query with Null Handling:

List<Task> tasks = PojoQuery.build(Task.class)
    .execute(dataSource);

for (Task task : tasks) {
    String assigneeName = task.getAssignee() != null
        ? task.getAssignee().getName()
        : "Unassigned";
    System.out.println(task.getTitle() + " - " + assigneeName);
}

// Query only unassigned tasks
List<Task> unassigned = PojoQuery.build(Task.class)
    .addWhere("task.assignee_id IS NULL")
    .execute(dataSource);

Example 13: Delete Operation

Delete a record by entity or by ID.

Operations:

// Use runInTransaction for all write operations
DB.runInTransaction(dataSource, (Connection c) -> {
    // Delete by entity instance
    User userToDelete = PojoQuery.build(User.class).findById(c, 123L);
    if (userToDelete != null) {
        PojoQuery.delete(c, userToDelete);
        System.out.println("User deleted");
    }

    // Delete by ID directly (more efficient - no need to fetch first)
    PojoQuery.deleteById(c, User.class, 456L);

    // Delete with raw SQL for bulk operations
    int deleted = DB.update(c,
        SqlExpression.sql("DELETE FROM session WHERE last_activity < ?",
            LocalDate.now().minusDays(30)));
    System.out.println("Cleaned up " + deleted + " old sessions");
});

Example 14: Dynamic Columns with `@Other`

Handle tables with dynamic/schemaless column patterns.

Database Schema:

CREATE TABLE product (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255),
    -- Dynamic attribute columns with prefix
    attr_color VARCHAR(50),
    attr_size VARCHAR(20),
    attr_weight DECIMAL(10,2),
    attr_material VARCHAR(100)
);

POJO:

@Table("product")
public class Product {
    @Id Long id;
    String name;

    // All columns starting with 'attr_' will be collected here
    // Map keys will be 'color', 'size', 'weight', 'material' (without prefix)
    @Other(prefix = "attr_")
    Map<String, Object> attributes;

    // Getters and setters omitted for brevity
}

Query:

Product product = PojoQuery.build(Product.class)
    .addWhere("product.id = ?", 1L)
    .execute(dataSource)
    .stream().findFirst().orElse(null);

if (product != null) {
    System.out.println("Product: " + product.getName());
    System.out.println("Color: " + product.getAttributes().get("color"));
    System.out.println("Size: " + product.getAttributes().get("size"));

    // Iterate all dynamic attributes
    product.getAttributes().forEach((key, value) ->
        System.out.println("  " + key + ": " + value));
}

PojoQuery Examples

Example 1: Basic Select

Example 2: Select with Join

Example 3: One-to-Many Relationship

Example 4: Aggregation (@GroupBy, @Select)

Example 5: Custom SQL Field (@Select)

Example 6: Insert Operation

Example 7: Update Operation

Example 8: Many-to-Many Relationship

Example 9: Filtered Relationships with @JoinCondition

Example 10: Embedded Objects

Example 11: Streaming Large Result Sets

Example 12: Handling Optional Relationships

Example 13: Delete Operation

Example 14: Dynamic Columns with @Other

Example 4: Aggregation (`@GroupBy`, `@Select`)

Example 5: Custom SQL Field (`@Select`)

Example 9: Filtered Relationships with `@JoinCondition`

Example 14: Dynamic Columns with `@Other`