The jOOQ User Manual. Multiple Pages : SQL building : SQL Statements (DDL) : The CREATE statement | previous : next |
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();
// 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();
// 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();
// 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") ...
// 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") ...