The jOOQ User Manual. Multiple Pages : SQL building : SQL Statements (DDL) : The CREATE statement

All versions: 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | 3.7 | Development versions: 3.13 | Unsupported versions: 3.6 | 3.5

The CREATE statement

Available in ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition

jOOQ currently supports the following CREATE statements (SQL examples in PostgreSQL syntax):

Indexes

// Create a non-unique index
create.createIndex("I_AUTHOR_LAST_NAME").on(AUTHOR, AUTHOR.LAST_NAME).execute();

// Create an index only if it doesn't exist (not all databases support this)
create.createIndexIfNotExists("I_AUTHOR_LAST_NAME").on(AUTHOR, AUTHOR.LAST_NAME).execute();

// Create a partial index (not all databases support this)
create.createIndex("I_AUTHOR_LAST_NAME").on(AUTHOR, AUTHOR.LAST_NAME).where(AUTHOR.LAST_NAME.like("A%")).execute();

// Create a unique index
create.createUniqueIndex("I_AUTHOR_LAST_NAME").on(AUTHOR, AUTHOR.LAST_NAME).execute();

Schemas

// Create a schema
create.createSchema("new_schema").execute();

// Create a schema only if it doesn't exists (not all databases support this)
create.createSchemaIfNotExists("new_schema").execute();

Sequences

// Create a sequence
create.createSequence(S_AUTHOR_ID).execute();

// Create a sequence only if it doesn't exists (not all databases support this)
create.createSequence(S_AUTHOR_ID).execute();

Tables

// Creating a table with columns and inline constraints
create.createTable(AUTHOR)
      .column(AUTHOR.ID, SQLDataType.INTEGER)
      .column(AUTHOR.FIRST_NAME, SQLDataType.VARCHAR.length(50).nullable(false))
      .column(AUTHOR.LAST_NAME, SQLDataType.VARCHAR.length(50))
      .constraints(
          constraint("PK_AUTHOR").primaryKey(AUTHOR.ID),
          constraint("UK_AUTHOR").unique(FIRST_NAME, LAST_NAME))
      .execute();

// Creating a table from a SELECT statement
create.createTable("TOP_AUTHORS").as(
       select(
         AUTHOR.ID,
         AUTHOR.FIRST_NAME,
         AUTHOR.LAST_NAME)
      .from(AUTHOR)
      .where(val(50).lt(
         selectCount().from(BOOK)
        .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
      ))).execute();
      
// Create a table only if it doesn't exists (not all databases support this)
create.createTableIfNotExists("TOP_AUTHORS")
      ...

Views

// Create a view
create.createView("V_TOP_AUTHORS").as(
       select(
         AUTHOR.ID,
         AUTHOR.FIRST_NAME,
         AUTHOR.LAST_NAME)
      .from(AUTHOR)
      .where(val(50).lt(
         selectCount().from(BOOK)
        .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
      ))).execute();
      
// Create a view only if it doesn't exists (not all databases support this)
create.createTableIfNotExists("TOP_AUTHORS")
      ...

The jOOQ User Manual. Multiple Pages : SQL building : SQL Statements (DDL) : The CREATE statement

previous : next