Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
Names and identifiers
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Various SQL objects columns or tables can be referenced using names (often also called identifiers). SQL dialects differ in the way they understand names, syntactically. The differences include:
- The permitted characters to be used in "unquoted" names
- The permitted characters to be used in "quoted" names
- The name quoting characters (e.g.
"double quotes"
,`backticks`
, or[brackets]
) (e.g."double quotes"
,`backticks`
, or[brackets]
) - The standard case for case-insensitive ("unquoted") names
For the above reasons, and also to prevent an additional SQL injection risk where names might contain SQL code, jOOQ by default quotes all names in generated SQL to be sure they match what is really contained in your database. This means that the following names will be rendered
-- Unquoted name AUTHOR.TITLE -- MariaDB, MySQL `AUTHOR`.`TITLE` -- MS Access, SQL Server, Sybase ASE, Sybase SQL Anywhere [AUTHOR].[TITLE] -- All the others, including the SQL standard "AUTHOR"."TITLE"
Note that you can influence jOOQ's name rendering behaviour through custom settings, if you prefer another name style to be applied.
Creating custom names
Custom, qualified or unqualified names can be created very easily using the DSL.name()
constructor:
// Unqualified name Name name = name("TITLE"); // Qualified name Name name = name("AUTHOR", "TITLE");
Such names can be used as standalone QueryParts, or as DSL entry point for SQL expressions, like
- Common table expressions to be used with the WITH clause
- Window specifications to be used with the WINDOW clause
More details about how to use names / identifiers to construct such expressions can be found in the relevant sections of the manual.
Feedback
Do you have any feedback about this page? We'd love to hear it!