Database Support

PojoQuery is designed to work with relational databases that support standard SQL and provide a JDBC driver. Database-specific behavior is handled through the DbContext system.

Supported Databases

PojoQuery provides built-in dialect support for:

Database Dialect Identifier Quote Style

MySQL / MariaDB

Dialect.MYSQL

Backticks ( ` )

PostgreSQL

Dialect.POSTGRES

Double quotes (")

HSQLDB

Dialect.HSQLDB

Double quotes (")
"Quote style" in this context refers to identifier quoting — how table and column names are escaped in SQL (e.g., ` users ` or "users"). This is separate from string literal quoting, which always uses single quotes ('value') across all databases.

Other databases using standard ANSI SQL should work with the appropriate dialect configuration.

Configuring the Database Dialect

Set the default DbContext for your database at application startup:

import org.pojoquery.DbContext;
import org.pojoquery.DbContext.Dialect;

// For MySQL/MariaDB (this is the default)
DbContext.setDefault(DbContext.forDialect(Dialect.MYSQL));

// For PostgreSQL
DbContext.setDefault(DbContext.forDialect(Dialect.POSTGRES));

// For HSQLDB (typically used in tests)
DbContext.setDefault(DbContext.forDialect(Dialect.HSQLDB));

The dialect affects:

  • Identifier quoting - How table and column names are quoted

  • SQL type mapping - How Java types map to database column types

  • Auto-increment syntax - AUTO_INCREMENT vs SERIAL/BIGSERIAL

  • Streaming fetch size - Database-specific settings for result set streaming

  • CREATE TABLE syntax - Engine settings and charset configuration

Database-Specific Considerations

  • SQL Functions (@Select): When using the @Select annotation with database functions (e.g., CONCAT, YEAR, MAX), ensure you use syntax compatible with your target database.

  • Generated Keys: The mechanism for retrieving auto-generated keys after an INSERT relies on standard JDBC Statement.RETURN_GENERATED_KEYS. This is widely supported.

  • Data Types: Ensure that the Java types used in your POJOs map correctly to the corresponding data types in your database. PojoQuery handles common types including LocalDate, LocalDateTime, BigDecimal, and Enum types.

DataSource Configuration

PojoQuery interacts with the database solely through a standard javax.sql.DataSource. Configure your DataSource using your preferred connection pool (like HikariCP, c3p0, Tomcat JDBC Pool) or framework (like Spring Boot) and pass it to PojoQuery’s execution methods (execute, executeStreaming, findById, etc.) or the static DB helper methods.

Custom DbContext

For advanced use cases, you can create a custom DbContext using the builder:

DbContext customContext = DbContext.builder()
    .dialect(Dialect.POSTGRES)
    .quoteObjectNames(false)  // disable identifier quoting
    .build();

// Use with a specific query
PojoQuery<User> query = PojoQuery.build(customContext, User.class);

// Or set as default
DbContext.setDefault(customContext);

Custom Type Mapping

To support custom Java types or override default type mappings, create a custom DbContext that overrides mapJavaTypeToSql(). This is useful for:

  • Supporting custom Java types (like UUID)

  • Using database-specific types (like PostgreSQL’s JSONB, INET, etc.)

  • Overriding default type mappings for your application’s needs

Here’s an example that adds native UUID support by extending an existing DbContext:

/**
 * Custom DbContext that extends HsqldbDbContext to add UUID support.
 * Override mapJavaTypeToSql() to map java.util.UUID to the database's UUID type.
 */
public static class HsqldbWithUuidContext extends HsqldbDbContext {

    @Override
    public String mapJavaTypeToSql(Field field) {
        Class<?> type = field.getType();
        
        // Add support for UUID type
        if (type == UUID.class) {
            return "UUID";
        }
        
        // Fall back to parent implementation for all other types
        return super.mapJavaTypeToSql(field);
    }
}

With an entity that uses the UUID type:

@Table("document")
public static class Document {
    @Id
    public Long id;
    
    public UUID documentId;  // Uses native UUID type
    
    public String title;
    public String content;
}

You can then insert and query records with UUID fields:

// Use runInTransaction for atomic operations
DB.withConnection(db, (Connection c) -> {
    // Insert a document with a UUID
    Document doc = new Document();
    doc.documentId = UUID.randomUUID();
    doc.title = "Architecture Overview";
    doc.content = "This document describes the system architecture...";
    
    PojoQuery.insert(c, doc);
    
    // Query back by UUID using {alias} syntax for proper quoting
    List<Document> found = PojoQuery.build(Document.class)
        .addWhere("{document}.documentId = ?", doc.documentId)
        .execute(c);
    
    System.out.println("Found " + found.size() + " document(s)");
    for (Document fetched : found) {
        System.out.println("Fetched: " + fetched.title + " (UUID: " + fetched.documentId + ")");
    }
});

The SchemaGenerator will use your custom mapJavaTypeToSql() when creating tables:

private static void createTable(DataSource db) {
    // SchemaGenerator uses mapJavaTypeToSql() from the DbContext,
    // so our UUID field will be mapped to the database's UUID type
    SchemaGenerator.createTables(db, Document.class);
}