How to escape SQL reserved keywords with JPA and Hibernate

(Last Updated On: February 2, 2019)
Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

In this article, we are going to see how you can escape SQL reserved keywords with JPA and Hibernate.

I decided to write this article because I keep on seeing this problem on the Hibernate forum or StackOverflow.

Reserved keywords

Because SQL is a declarative language, the keywords that form the grammar of the language are reserved for internal use, and they cannot be employed when defining a database identifier (e.g. catalog, schema, table, column name).

Now, since each relational database provides a custom SQL dialect, the list of reserved keywords may differ from one database to another, as illustrated by the following list:

Domain Model

Let’s assume that we are developing the application we are developing is required to store information about database tables. Therefore, we can use the following Table entity:

Table entity

Now, if we map the Table entity like this:

@Entity(name = "Table")
public class Table {

    @Id
    @GeneratedValue
    private Long id;

    private String catalog;

    private String schema;

    private String name;

    @Column(name = "desc")
    private String description;

    //Getters and setters omitted for brevity
}

And, we try to generate the database schema using the hbm2ddl tool, the schema generation process will fail as follows:

Caused by: org.hibernate.tool.schema.spi.CommandAcceptanceException: 
	Error executing DDL "create table Table (id bigint not null, catalog varchar(255), desc varchar(255), name varchar(255), schema varchar(255), primary key (id))" via JDBC Statement
Caused by: java.sql.SQLSyntaxErrorException: 
	unexpected token: TABLE

Because the TABLE keyword is reserved, we need to escape it. More, we need to escape the catalog, schema, and desc column names since these are also reserved by the database.

Manual escaping using the JPA column name attribute

The first option you have to escape a database identifier is to wrap the table or column name using the double quote sign (e.g. ") as illustrated by the following JPA entity mapping:

@Entity(name = "Table")
@javax.persistence.Table(name = "\"Table\"")
public class Table {

    @Id
    @GeneratedValue
    private Long id;

    @Column(name = "\"catalog\"")
    private String catalog;

    @Column(name = "\"schema\"")
    private String schema;

    private String name;

    @Column(name = "\"desc\"")
    private String description;

    //Getters and setters omitted for brevity
}

Now, when generating the database schema using the hbm2ddl tool, Hibernate is going to generate the following DDL statement:

CREATE TABLE "table" (
    id bigint NOT NULL,
    "catalog" VARCHAR(255),
    "desc" VARCHAR(255),
    name VARCHAR(255),
    "schema" VARCHAR(255),
    PRIMARY KEY (id)
)

Notice that the table name, as well as the columns using SQL reserved keywords, are properly escaped this time.

When persisting a Table entity:

entityManager.persist(
    new Table()
    .setCatalog("library")
    .setSchema("public")
    .setName("book")
    .setDescription(
        "The book table stores book-related info"
    )
);

Hibernate generates the proper SQL INSERT statement:

INSERT INTO "table" (
    "catalog",
    "desc",
    name,
    "schema",
    id
)
VALUES (
    'library',
    'The book table stores book-related info',
    'book',
    'public',
    1
)

Notice that the Table entity creation uses the Fluent-style API pattern. For more details about building JPA entities using the Fluent-style API, check out this article.

When fetching the Table entity:

List<Table> tables = entityManager.createQuery(
    "select t " +
    "from Table t " +
    "where t.description like :description", Table.class)
.setParameter("description", "%book%")
.getResultList();

assertEquals(1, tables.size());

Hibernate escapes all database identifiers we have explicitly escaped in the JPA entity mapping:

SELECT 
    t.id AS id1_0_,
    t."catalog" AS catalog2_0_,
    t."desc" AS desc3_0_,
    t.name AS name4_0_,
    t."schema" AS schema5_0_
FROM 
    "table" t
WHERE 
    t."desc" LIKE '%book%'

Global escaping using the Hibernate globally_quoted_identifiers property

Another option is to set the hibernate.globally_quoted_identifiers property to true in the persistence.xml configuration file:

<property 
    name="hibernate.globally_quoted_identifiers" 
    value=true"
/>

This way, Hibernate is going to escape all database identifiers, meaning that we don’t need to manually escape the table or column names:

@Entity(name = "Table")
public class Table {

    @Id
    @GeneratedValue
    private Long id;

    private String catalog;

    private String schema;

    private String name;

    @Column(name = "desc")
    private String description;

    //Getters and setters omitted for brevity
}

When generating the database schema, Hibernate is going to escape the table name as well as all columns:

CREATE TABLE "Table" (
    "id" bigint NOT NULL,
    "catalog" VARCHAR(255),
    "desc" VARCHAR(255),
    "name" VARCHAR(255),
    "schema" VARCHAR(255),
    PRIMARY KEY ("id")
)

When persisting the Table entity, the SQL INSERT statement will automatically escape the table and the column names:

INSERT INTO "table" (
    "catalog",
    "desc",
    "name",
    "schema",
    "id"
)
VALUES (
    'library',
    'The book table stores book-related info',
    'book',
    'public',
    1
)

Notice that even the id and name column names are escaped this time.

The same applies to any SQL statement generated by Hibernate, so fetching the Table entities matching the provided description generates the following SQL SELECT query:

SELECT 
    t."id" AS id1_0_,
    t."catalog" AS catalog2_0_,
    t."desc" AS desc3_0_,
    t."name" AS name4_0_,
    t."schema" AS schema5_0_
FROM 
    "table" t
WHERE 
    t."desc" LIKE '%book%'

Cool, right?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Escaping SQL reserved keywords is straightforward when using JPA and Hibernate. While the JPA column-level escaping is very useful if you only have a small number of database identifiers to be escaped, when the number of database identifiers using reserved keywords is large, the Hibernate global escaping becomes a very convenient alternative.

Download free ebook sample

Newsletter logo
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence,
  • a 10% discount coupon for my book.

2 Comments on “How to escape SQL reserved keywords with JPA and Hibernate

  1. Great post Vlad. Just some additional points…

    In regards to the manual approach, Hibernate also supports back-ticks rather than JPA’s double-quote to quote an identifier. That predates JPA and is still supported because I believe it is a better “quoting character” since it does not require escaping. E.g. rather than
    name = “\”Table\”” you can write name=”Table“.

    Secondly, the “quote all identifiers” is a JPA feature and it occasionally results in problems. E.g, JPA says that should apply to column definitions too but if the column definition is defined as “NUMBER(10)”, quoting that is obviously wrong. Hibernate offers a setting to indicate that column definitions should not be quoted even if global quoting is enabled : hibernate.globally_quoted_identifiers_skip_column_definitions.

    And lastly, the JPA auto quoting quotes all identifiers regardless of whether it is a reserved word or not. Hibernate has a feature to quite just identifiers it seems are reserved via the Dialect and DatabaseMetaData – hibernate.auto_quote_keyword

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Want to run your data access layer at warp speed?