Getting Started with PojoQuery

This guide walks you through setting up PojoQuery and executing your first queries.

Prerequisites

  • Java Development Kit (JDK) 17 or later

  • A build tool like Maven or Gradle

  • A JDBC-compliant database (e.g., MySQL, PostgreSQL, HSQLDB)

Installation

Maven

Add the PojoQuery dependency to your pom.xml:

<dependency>
  <groupId>org.pojoquery</groupId>
  <artifactId>pojoquery</artifactId>
  <version>4.0.0-BETA</version>
</dependency>

<!-- Add your JDBC driver, e.g., for MySQL -->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.0.33</version>
    <scope>runtime</scope>
</dependency>

Gradle

dependencies {
    implementation 'org.pojoquery:pojoquery:4.0.0-BETA'
    runtimeOnly 'com.mysql:mysql-connector-j:8.0.33'
}

Define Your Entities

Create Java classes representing your database tables. Note how relationships are modeled without cyclic references—the DepartmentWithEmployees subclass adds the collection:

    @Table("department")
    public static class Department {
        @Id
        public Long id;
        
        public String name;
        public String location;
    }
    
    // Subclass that includes the employee collection (avoids cyclic references)
    public static class DepartmentWithEmployees extends Department {
        public List<Employee> employees;
    }
    
    @Table("employee")
    public static class Employee {
        @Id
        public Long id;
        
        public String firstName;
        public String lastName;
        public String email;
        
        // Links to department via department_id column (auto-inferred from field name)
        public Department department;
        
        // Helper method (not mapped to database)
        public String getFullName() {
            return firstName + " " + lastName;
        }
    }

Key points:

  • @Table maps the class to a database table

  • @Id marks the primary key field

  • Entity references (like department in Employee) automatically create foreign key relationships

  • Use subclasses to add collections without creating cycles in your entity model

Setup

Configure the database dialect and create your schema:

        // 1. Configure the database dialect (do this once at startup)
        DbContext.setDefault(DbContext.forDialect(DbContext.Dialect.HSQLDB));
        
        // 2. Create a DataSource (use your connection pool in production)
        DataSource dataSource = createDataSource();
        
        // 3. Create the database schema from entity classes
        SchemaGenerator.createTables(dataSource, Department.class, Employee.class);

The SchemaGenerator creates tables based on your entity classes—no manual SQL required.

Insert Records

Insert entities using PojoQuery.insert(). The generated ID is automatically populated:

        // Run all operations within a connection
        DB.withConnection(dataSource, (Connection c) -> {
            // Insert a department
            Department engineering = new Department();
            engineering.name = "Engineering";
            engineering.location = "Building A";
            PojoQuery.insert(c, engineering);
            System.out.println("Inserted department with ID: " + engineering.id);
            
            // Insert employees linked to the department
            Employee alice = new Employee();
            alice.firstName = "Alice";
            alice.lastName = "Anderson";
            alice.email = "alice@example.com";
            alice.department = engineering;
            PojoQuery.insert(c, alice);
            
            Employee bob = new Employee();
            bob.firstName = "Bob";
            bob.lastName = "Brown";
            bob.email = "bob@example.com";
            bob.department = engineering;
            PojoQuery.insert(c, bob);
            
            Employee carol = new Employee();
            carol.firstName = "Carol";
            carol.lastName = "Clark";
            carol.email = "carol@example.com";
            carol.department = engineering;
            PojoQuery.insert(c, carol);

Query Records

Query All with Ordering

            // Query all employees with their department, sorted by last name
            List<Employee> allEmployees = PojoQuery.build(Employee.class)
                .addOrderBy("{employee}.lastName ASC")
                .execute(c);
            
            System.out.println("\nAll employees:");
            for (Employee emp : allEmployees) {
                System.out.println("  " + emp.getFullName() + " - " + emp.department.name);
            }
Use {employee} syntax in WHERE and ORDER BY clauses—PojoQuery automatically quotes identifiers for your database.

Query with Filters

            // Query with a filter
            List<Employee> filtered = PojoQuery.build(Employee.class)
                .addWhere("{employee}.lastName LIKE ?", "A%")
                .execute(c);
            
            System.out.println("\nEmployees with last name starting with 'A':");
            for (Employee emp : filtered) {
                System.out.println("  " + emp.getFullName());
            }

Find by ID

            // Find by ID
            Employee found = PojoQuery.build(Employee.class)
                .findById(c, alice.id);
            
            System.out.println("\nFound by ID: " + found.getFullName());

Update Records

            // Update an employee
            found.email = "alice.anderson@example.com";
            PojoQuery.update(c, found);
            System.out.println("Updated email for: " + found.getFullName());

Delete Records

            // Delete an employee
            PojoQuery.delete(c, bob);
            System.out.println("Deleted: Bob Brown");
            
            // Verify deletion
            List<Employee> remaining = PojoQuery.build(Employee.class).execute(c);
            System.out.println("\nRemaining employees: " + remaining.size());

Query with Collections

Use a subclass to fetch an entity with its related collection. PojoQuery automatically performs the JOIN:

            // Query department with all its employees using the subclass
            DepartmentWithEmployees dept = PojoQuery.build(DepartmentWithEmployees.class)
                .findById(c, engineering.id);
            
            System.out.println("\nDepartment: " + dept.name + " (" + dept.location + ")");
            System.out.println("Employees:");
            for (Employee emp : dept.employees) {
                System.out.println("  - " + emp.getFullName());
            }

DataSource Configuration

For this example, we use HSQLDB in-memory. In production, use a connection pool:

    private static DataSource createDataSource() {
        // Using HSQLDB in-memory database for this example
        // In production, use a connection pool like HikariCP
        JDBCDataSource ds = new JDBCDataSource();
        ds.setUrl("jdbc:hsqldb:mem:getting_started");
        ds.setUser("SA");
        ds.setPassword("");
        return ds;
    }

For MySQL or PostgreSQL, configure the appropriate dialect:

// For MySQL
DbContext.setDefault(DbContext.forDialect(DbContext.Dialect.MYSQL));

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

Next Steps