How to escape SQL reserved keywords with Spring boot, JPA and Hibernate

Introduction

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

Problems

When we use a reserved keyword of SQL in our entities name, Hibernate generates SQL statements containing syntax errors and throws SQLGrammarException, and it’s depend of what database we use we can have this error :

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

Solution

Let’s assume that our entity name is Order, as we know, order is reserved keyword in SQL.

@Entity  
@Table(name = "order")
public class Order {
	
	@Id
    @GeneratedValue
    private Long id;
	
	@Column(name = "orderNumber")
	private Long orderNumber;
 
	@Column(name = "date")
	private LocalDate date;
	
	//Getters and setters	 
}

In our example, we have 2 reserved keyword ( order and date)

1. Manual escaping using the JPA column name attribute The first option you have to escape a database identifier is to enclose the table or column name using the double quote sign (e.g , ”) .

@Entity  
@Table(name = "\"order\"")
public class Order {
	
	@Id
    @GeneratedValue
    private Long id;
	
	@Column(name = "orderNumber")
	private Long orderNumber;
 
	@Column(name = "\"date\"")
	private LocalDate date;
	
	//Getters and setters	 
}

2. Manual escaping using the Hibernate-specific back-tick character You can also escape a database identifier with backtick character (e.g., `).

@Entity  
@Table(name = "`order`")
public class Order {
	
	@Id
    @GeneratedValue
    private Long id;
	
	@Column(name = "orderNumber")
	private Long orderNumber;
 
	@Column(name = "`date`")
	private LocalDate date;
	
	//Getters and setters	 
}

3. Global escaping using Hibernate configuration with hibernate.globallyquotedidentifiers property Another option is to set the hibernate.globally_quoted_identifiers property to true in the persistence.xml configuration file as mentioned in configurations documentation :

<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  
@Table(name = "order")
public class Order {
	
	@Id
    @GeneratedValue
    private Long id;
	
	@Column(name = "orderNumber")
	private Long orderNumber;
 
	@Column(name = "date")
	private LocalDate date;
	
	//Getters and setters	 
}

3. Global escaping using Spring boot configuration

Spring boot allow to use Hibernate native properties, using spring.jpa.properties. as prefix as mentioned here.

So the best option if you use Spring boot is to set spring.jpa.properties.hibernate.globally_quoted_identifiers property to true in your Spring configuration properties file :

spring.jpa.properties.hibernate.globally_quoted_identifiers=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  
@Table(name = "order")
public class Order {
	
	@Id
    @GeneratedValue
    private Long id;
	
	@Column(name = "orderNumber")
	private Long orderNumber;
 
	@Column(name = "date")
	private LocalDate date;
	
	//Getters and setters	 
}

References

• Hibernate configuration
• Spring boot JPA