Query Building API

PojoQuery provides a fluent API via the PojoQuery<T> class to build and customize your database queries programmatically.

Creating a Query

Start by obtaining a PojoQuery instance for your target POJO class using PojoQuery.build():

import org.pojoquery.PojoQuery;
import javax.sql.DataSource;

// Assuming User is an annotated POJO and dataSource is configured

// Build a query for the User class
PojoQuery<User> userQuery = PojoQuery.build(User.class);

// Build a query for a more complex view/DTO
PojoQuery<ArticleDetail> articleQuery = PojoQuery.build(ArticleDetail.class);

Adding Conditions (WHERE)

Use addWhere() to add conditions to the WHERE clause. Conditions are combined with AND.

// Find user by email
userQuery.addWhere("user.email = ?", "test@example.com");

// Find articles by author ID and published status
articleQuery.addWhere("article.author_id = ?", authorId);
articleQuery.addWhere("article.published = ?", true);

// Using curly braces for alias resolution (useful in complex scenarios)
articleQuery.addWhere("{author.lastName} = ?", "Smith");

  • Placeholders (?) are used for parameters to prevent SQL injection.

  • Refer to columns using the alias defined by PojoQuery (usually {table_name}.{column_name} or {field_name}.{column_name} for linked entities).

Alias Resolution with Curly Braces

One of PojoQuery’s handy features is alias resolution using curly braces {}. This lets you reference fields and table aliases in a clean, readable way — and PojoQuery takes care of proper SQL quoting and schema prefixes for you.

Why Use Curly Braces?

When you write {author.lastName}, PojoQuery will:

  1. Recognize author as a table/join alias

  2. Quote everything properly for your database dialect (e.g., backticks for MySQL)

  3. Handle schema prefixes automatically

This means you can write readable conditions without worrying about SQL syntax details!

Using Curly Braces in WHERE Conditions

// Simple field reference
query.addWhere("{article.published} = ?", true);
// Generates: WHERE `article`.`published` = ?

// Reference fields from joined tables
query.addWhere("{author.lastName} = ?", "Smith");
// Generates: WHERE `author`.`lastName` = ?

// Works with nested relationships too!
query.addWhere("{articles.author.fullName} = ?", "Jane Doe");
// Generates: WHERE `articles.author`.`fullName` = ?

Using Curly Braces in ORDER BY

// Sort by a joined table's column
query.addOrderBy("{author.lastName} ASC");
query.addOrderBy("{article.publishDate} DESC");

Using Curly Braces in GROUP BY

// Group by columns from different tables
query.addGroupBy("{article.author_id}");
query.addGroupBy("{category.name}");

Resolving Field Expressions

The real magic happens when you have fields with custom @Select expressions. Curly braces will resolve to the actual SQL expression:

@Table("person")
public class Person {
    @Id Long id;
    String firstName;
    String lastName;

    @Select("CONCAT({this}.firstName, ' ', {this}.lastName)")
    String fullName;
}

// Now you can filter by the computed field!
query.addWhere("{author.fullName} = ?", "Jane Doe");
// Generates: WHERE CONCAT(`author`.firstName, ' ', `author`.lastName) = ?
Use {this} inside @Select annotations to reference the current table alias. This keeps your expressions portable.

Sorting Results (ORDER BY)

Use addOrderBy() to specify sorting order. Multiple calls append sorting criteria.

// Order users by last name, then first name
userQuery.addOrderBy("user.lastName ASC");
userQuery.addOrderBy("user.firstName ASC");

// Order articles by publish date descending
articleQuery.addOrderBy("article.publishDate DESC");

Limiting Results (LIMIT/OFFSET)

Use setLimit() to control the number of rows returned and the starting offset.

// Get the first 10 users
userQuery.setLimit(10);

// Get 20 users, skipping the first 40 (for pagination)
userQuery.setLimit(40, 20); // offset, rowCount

Customizing Joins (addJoin)

While convention-based joins handle most relationships automatically, you can add explicit joins using addJoin().

import org.pojoquery.SqlExpression;
import org.pojoquery.pipeline.SqlQuery.JoinType;

// Add an INNER JOIN manually
articleQuery.addJoin(
    JoinType.INNER,                 // Join type
    "category",                     // Table name
    "cat",                          // Alias for the joined table
    SqlExpression.sql("article.category_id = cat.id") // ON condition
);

// Add a WHERE clause referencing the joined table alias
articleQuery.addWhere("cat.name = ?", "Technology");

Customizing Selections (addField)

Add custom fields or expressions to the SELECT clause.

// Add a calculated field for full name
userQuery.addField(
    SqlExpression.sql("CONCAT(user.firstName, ' ', user.lastName)"), // SQL Expression
    "user.fullName" // Alias for the result column (must match a field if mapping)
);

// Note: Often easier to use @Select annotation on the POJO field instead.

Grouping Results (addGroupBy)

Add GROUP BY clauses, typically used with aggregate functions defined via @Select or addField.

// Assuming ArticleStats POJO has @Select fields for COUNT(*) etc.
PojoQuery<ArticleStats> statsQuery = PojoQuery.build(ArticleStats.class);
statsQuery.addGroupBy("article.author_id"); // Group by author

Executing the Query

Once the query is built, execute it against a DataSource or Connection.

Standard Execution (Loads all results)

List<User> users = userQuery.execute(dataSource);
for (User user : users) {
    System.out.println(user.getFullName());
}

Streaming Execution (Processes row by row)

Use executeStreaming() for large result sets to avoid loading everything into memory.

// Using DataSource
PojoQuery.build(VeryLargeTable.class)
    .executeStreaming(dataSource)
    .forEach(record -> {
        // Process each record as it's fetched
        System.out.println("Processing record ID: " + record.id);
    });

// Using Connection (less common, requires manual connection management)
Connection conn = null;
try {
    conn = dataSource.getConnection();
    PojoQuery.build(VeryLargeTable.class)
        .executeStreaming(conn) // Pass the connection
        .forEach(record -> {
            // Process record
        });
} catch (SQLException e) {
    // Handle exception
} finally {
    if (conn != null) try { conn.close(); } catch (SQLException ignore) {}
}

Other Useful Methods

  • toSql(): Get the generated SQL string (primarily for debugging).

  • toStatement(): Get the SqlExpression containing the SQL and parameters.

  • findById(DataSource ds, Object id): Convenience method to fetch a single entity by its primary key.

  • countTotal(DataSource ds): Executes a COUNT(DISTINCT primary_key) query based on the current filters.

  • listIds(DataSource ds): Executes a query selecting only the primary key(s) based on the current filters.

Refer to the PojoQuery class JavaDoc for a complete list of methods.