Schema Generation

PojoQuery’s SchemaGenerator creates and updates database tables based on your entity classes.

Creating Tables

import org.pojoquery.schema.SchemaGenerator;

SchemaGenerator.createTables(dataSource, User.class, Article.class, Comment.class);

This generates CREATE TABLE statements based on your entity annotations, creates appropriate column types for Java types, and sets up primary keys from @Id annotations.

Updating Tables

When you add new fields to existing entities, use generateMigrationStatements() to generate ALTER TABLE statements:

import org.pojoquery.schema.SchemaInfo;

SchemaInfo schemaInfo = SchemaInfo.fromDataSource(dataSource);

List<String> statements = SchemaGenerator.generateMigrationStatements(
    schemaInfo, User.class, Article.class);

DB.runInTransaction(dataSource, connection -> {
    for (String ddl : statements) {
        DB.executeDDL(connection, ddl);
    }
});

Type Mapping

Java types are mapped to SQL types. The exact SQL type depends on the database dialect:

Java Type MySQL HSQLDB PostgreSQL

String

VARCHAR(255)

VARCHAR(255)

VARCHAR(255)

Long, long

BIGINT

BIGINT

BIGINT

Integer, int

INT

INT

INTEGER

Double, double

DOUBLE

DOUBLE

DOUBLE PRECISION

Boolean, boolean

TINYINT(1)

BOOLEAN

BOOLEAN

BigDecimal

DECIMAL(19,4)

DECIMAL(19,4)

NUMERIC(19,4)

LocalDate

DATE

DATE

DATE

LocalDateTime

DATETIME

TIMESTAMP

TIMESTAMP

byte[]

BLOB

BLOB

BYTEA

@Column Annotation

Use @Column to customize column definitions:

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

    @Column(length = 100, nullable = false)
    String name;

    @Column(length = 1000)
    String description;

    @Column(precision = 10, scale = 2)
    BigDecimal price;

    @Column(unique = true)
    String sku;
}

@Column Properties

Property Default Description

length

255

Length for VARCHAR columns

precision

19

Precision for DECIMAL columns (total digits)

scale

4

Scale for DECIMAL columns (digits after decimal point)

nullable

true

Whether the column allows NULL values

unique

false

Whether to add a UNIQUE constraint

@Id Annotation

Mark primary key fields with @Id:

@Table("user")
public class User {
    @Id Long id;  // Auto-increment primary key
    String name;
}

For auto-increment, use Long or Integer. The generated SQL uses GENERATED BY DEFAULT AS IDENTITY (HSQLDB/PostgreSQL) or AUTO_INCREMENT (MySQL).

@FieldName Annotation

Map Java field names to different column names:

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

    @FieldName("author_id")
    Long authorId;

    @FieldName("created_at")
    LocalDateTime createdAt;
}

Foreign Keys

Entity references automatically create foreign key columns:

@Table("article")
public class Article {
    @Id Long id;
    String title;
    User author;  // Creates author_id column with FK to user.id
}

Entity Inheritance

The SchemaGenerator supports table-per-subclass inheritance using @SubClasses:

@Table("room")
@SubClasses({BedRoom.class, Kitchen.class})
public class Room {
    @Id Long id;
    String name;
    Double area;
}

@Table("bedroom")
public class BedRoom extends Room {
    Integer numberOfBeds;
}

@Table("kitchen")
public class Kitchen extends Room {
    Boolean hasIsland;
}

This generates separate tables for each class, with the subclass tables containing their specific fields plus a foreign key to the parent table.

Generating DDL Without Executing

To inspect the generated SQL without executing it:

List<String> statements = SchemaGenerator.generateCreateTableStatements(
    User.class, Article.class, Comment.class);

for (String ddl : statements) {
    System.out.println(ddl);
}