Database Keywords as Columns in Hibernate Entities

1. Overview

In this tutorial, we’ll learn how to avoid cryptic Hibernate exceptions by properly escaping database keywords in column names.

2. Setup

Before we dive in, let’s define a simple BrokenPhoneOrder entity with reserved keywords in column mappings:

@Entity
public class BrokenPhoneOrder implements Serializable {
    @Id
    @Column(name = "order")
    String order;
    @Column(name = "where")
    String where;

    // getters and setters
}

Once we understand the cause of a syntax exception this entity causes, we’ll turn it into a PhoneOrder without the flawed database keyword usage.

2. Hibernate Exception – Hidden Root Cause

Let’s imagine receiving the following exception from our ORM-based application:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax

It’s always thrown when we attempt to persist  BrokenPhoneOrder to the database:

@Test
void givenBrokenPhoneOrderWithReservedKeywords_whenNewObjectIsPersisted_thenItFails() {
    BrokenPhoneOrder order = new BrokenPhoneOrder(randomUUID().toString(), "My House");

    assertThatExceptionOfType(PersistenceException.class).isThrownBy(() -> {
        session.persist(order);
        session.flush();
    });
}

The exception itself is of no use, so let’s enable additional SQL statements logging to access the actual query.

We’ll need to modify hibernate.cfg.xml to include the show_sql property:

<property name="show_sql">true</property>

With hibernate.properties, the syntax slightly differs:

show_sql = true

Now we’re able to see the following SQL statement being logged right before the exception log:

Hibernate: insert into broken_phone_order (where, order) values (?, ?)

The question marks are simply the values of order and where of the BrokenPhoneOrder entity we attempted to create.

3. The Actual SQL Error

We’re possibly already able to tell the root cause of the exception. But even if not, let’s pick our favorite SQL editor and paste the captured SQL with example values:

INSERT INTO broken_phone_order (order, where) VALUES ('some-1', 'somewhere');

In most cases, we’ll see where and order marked in red already. If not, once we execute it, the DB engine (MySQL in our case) returns an error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order, where) values ('a', 'b')' at line 1

4. Database Keywords

Both ORDER and WHERE are reserved words according to the SQL standard. Therefore, they have a special meaning in the language grammar. Each database engine has the same base list of words but can also extend it.

We can simply remove the special meaning by wrapping them with the “ characters, e.g., `where`.

Let’s apply it to the INSERT statement and confirm that this time it passes:

INSERT INTO broken_phone_order (`order`, `where`) VALUES ('some-1', 'somewhere');

5. Reserved Words in Hibernate

The question is: how are we going to change the Hibernate query if it’s auto-generated?

In Hibernate, like in SQL, we can simply use the “ escaping in the @Column annotation.

Let’s try it with the PhoneOrder entity:

@Entity
@Table(name = "phone_order")
public class PhoneOrder implements Serializable {
    @Id
    @Column(name = "`order`")
    String order;
    @Column(name = "`where`")
    String where;

    // getters and setters
}

With proper keyword escaping, MySQL no longer complains about the syntax, and the entity is successfully persisted:

@Test
void givenPhoneOrderWithEscapedKeywords_whenNewObjectIsPersisted_thenItSucceeds() {
    PhoneOrder order = new PhoneOrder(randomUUID().toString(), "here");

    session.persist(order);
    session.flush();
}

6. Conclusion

In this article, we’ve learned how to properly escape reserved words in @Column annotations. By adding additional escape characters around order and where terms, we’ve influenced Hibernate-generated queries and resolved the exception.