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:

User newUser = new User();
newUser.setFirstName("Alice");
newUser.setLastName("Wonder");
newUser.setEmail("alice@example.com");

PojoQuery.insert(dataSource, 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:

// Assume 'userToUpdate' was fetched previously
userToUpdate.setEmail("alice.w@example.com");
int updatedRows = PojoQuery.update(dataSource, 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:

CREATE TABLE event (id BIGINT PRIMARY KEY, title VARCHAR(255));
CREATE TABLE person (id BIGINT PRIMARY KEY, firstname VARCHAR(100), lastname VARCHAR(100));
CREATE TABLE event_person (
    event_id BIGINT,
    person_id BIGINT,
    role VARCHAR(20), -- 'visitor' or 'organizer'
    PRIMARY KEY (event_id, person_id, role)
);

POJOs:

@Table("event")
public 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;

    // Getters and setters omitted for brevity
}

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

    // Getters and setters omitted for brevity
}

Query:

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

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

Example 10: Embedded Objects

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

Database Schema:

CREATE TABLE customer (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255),
    -- Shipping address columns with prefix
    ship_street VARCHAR(255),
    ship_city VARCHAR(100),
    ship_zip VARCHAR(20),
    -- Billing address columns with prefix
    bill_street VARCHAR(255),
    bill_city VARCHAR(100),
    bill_zip VARCHAR(20)
);

POJOs:

// Value object (not an entity - no @Table)
public class Address {
    String street;
    String city;
    String zip;

    // Getters and setters omitted for brevity
}

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

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

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

    // Getters and setters omitted for brevity
}

Query:

Customer customer = PojoQuery.build(Customer.class)
    .addWhere("customer.id = ?", 100L)
    .execute(dataSource)
    .stream().findFirst().orElse(null);

if (customer != null) {
    System.out.println("Ship to: " + customer.getShippingAddress().getCity());
    System.out.println("Bill to: " + 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:

// Delete by entity instance
User userToDelete = repo.findById(123L);
if (userToDelete != null) {
    PojoQuery.delete(dataSource, userToDelete);
    System.out.println("User deleted");
}

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

// Delete with raw SQL for bulk operations
int deleted = DB.update(dataSource,
    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`