Рубрики
Без рубрики

Как избежать зарезервированных ключевых слов SQL с помощью JPA и гибернации

Узнайте, как избежать зарезервированных ключевых слов SQL при использовании JPA и Hibernate. Зарезервированные ключевые слова могут быть экранированы в именах таблиц или столбцов.

Автор оригинала: Vlad Mihalcea.

Вступление

В этой статье мы рассмотрим, как вы можете избежать зарезервированных ключевых слов SQL с помощью JPA и гибернации.

Я решил написать эту статью, потому что я продолжаю видеть эту проблему на форуме Hibernate или StackOverflow .

Как избежать зарезервированных ключевых слов SQL с помощью JPA и #Hibernate @vlad_mihalcea https://t.co/Pyi6u9pR3k pic.twitter.com/d1eLcCeMe3

Зарезервированные ключевые слова

Поскольку SQL является декларативным языком, ключевые слова, формирующие грамматику языка, зарезервированы для внутреннего использования, и их нельзя использовать при определении идентификатора базы данных (например, каталог, схема, таблица, имя столбца).

Теперь, поскольку каждая реляционная база данных предоставляет пользовательский диалект SQL, список зарезервированных ключевых слов может отличаться от одной базы данных к другой, как показано в следующем списке:

Модель предметной области

Давайте предположим, что мы разрабатываем приложение, которое мы разрабатываем, требуется для хранения информации о таблицах базы данных. Поэтому мы можем использовать следующую Таблицу сущность:

Теперь, если мы сопоставим сущность Таблица следующим образом:

@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
}

И, если мы попытаемся сгенерировать схему базы данных с помощью инструмента hbm2ddl , процесс генерации схемы завершится следующим образом:

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

Поскольку ключевое слово ТАБЛИЦА зарезервировано, нам нужно его экранировать. Более того, нам нужно избежать имен столбцов catalog , schema и desc , поскольку они также зарезервированы базой данных.

Экранирование вручную с использованием атрибута имени столбца JPA

Первый вариант, который вам нужно избежать идентификатора базы данных, – это обернуть имя таблицы или столбца, используя знак двойной кавычки (например,”), как показано на следующем сопоставлении объектов JPA:

@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
}

Теперь при создании схемы базы данных с помощью инструмента hbm2ddl Hibernate создаст следующую инструкцию DDL:

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

Обратите внимание, что имя таблицы, а также столбцы, использующие зарезервированные ключевые слова SQL, на этот раз правильно экранированы.

При сохранении Таблицы сущности:

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

Hibernate генерирует соответствующую инструкцию SQL INSERT:

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

Обратите внимание, что при создании Таблицы сущности используется шаблон API в стиле Fluent. Для получения более подробной информации о создании объектов JPA с использованием API в стиле Fluent ознакомьтесь с этой статьей .

При извлечении объекта Таблица :

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

assertEquals(1, tables.size());

Hibernate экранирует все идентификаторы базы данных, которые мы явно экранировали в сопоставлении объектов JPA:

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%'

Ручное экранирование с использованием специфичного для режима гибернации символа обратного отсчета

Вы также можете избежать данного квалификатора объекта базы данных, используя символ обратного отсчета (например,`).

@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;

    @Column(name = "`name`")
    private String name;

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

    //Getters and setters omitted for brevity
}

Экранирование обратного отсчета для гибернации эквивалентно экранирующему символу JPA в двойных кавычках, поэтому все сгенерированные инструкции DDL или DML точно такие же, как в предыдущем разделе.

Глобальное экранирование с использованием свойства Hibernate globally_quoted_identifiers

Другой вариант-установить для свойства hibernate.globally_quoted_identifiers значение true в persistence.xml файл конфигурации:


Таким образом, Hibernate будет экранировать все идентификаторы базы данных, что означает, что нам не нужно вручную экранировать имена таблиц или столбцов:

@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
}

При создании схемы базы данных Hibernate будет экранировать имя таблицы, а также все столбцы:

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

При сохранении сущности Таблица инструкция SQL INSERT автоматически экранирует таблицу и имена столбцов:

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

Обратите внимание, что на этот раз даже имена столбцов id и name экранированы.

То же самое относится к любому оператору SQL, сгенерированному Hibernate, поэтому при извлечении Таблицы сущностей, соответствующих предоставленному описанию , создается следующий запрос SQL SELECT:

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%'

Включение свойства Hibernate globally_quoted_identifiers_skip_column_definitions

Теперь давайте предположим, что нам нужно предоставить пользовательское определение DDL для данного столбца таблицы, как показано в следующем фрагменте кода:

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

	@Id
	@GeneratedValue
	@Column(columnDefinition = "smallint")
	private Integer id;

	private String catalog;

	private String schema;

	private String name;

	private String description;

    //Getters and setters omitted for brevity
}

Если свойство hibernate.globally_quoted_identifiers включено и мы пытаемся создать схему базы данных с помощью hbm2ddl, Hibernate выдаст следующее исключение:

CREATE TABLE "Table" (
    "id" "smallint" NOT NULL,
    "catalog" VARCHAR(255),
    "desc" VARCHAR(255),
    "name" VARCHAR(255),
    "schema" VARCHAR(255),
    PRIMARY KEY ("id")
)
    
-- GenerationTarget encountered exception accepting command : 
    Error executing DDL via JDBC Statement

Проблема вызвана явным определением столбца первичного ключа таблицы, который также был экранирован. Обратите внимание на тип столбца "smallint" в двойных кавычках, связанный со столбцом id .

Чтобы устранить эту проблему, нам также необходимо включить свойство hibernate.globally_quoted_identifiers_skip_column_definitions конфигурации:


Теперь Hibernate пропускает цитирование явного определения столбца, и все будет работать просто отлично:

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

Список зарезервированных слов, которые пропускаются Hibernate при установке свойства hibernate.globally_quoted_identifiers_skip_column_definitions , взят из следующих источников:

  • java.sql.DatabaseMetaData.getSQLKeywords() предоставляется текущим драйвером JDBC,
  • ключевые слова SQL ANSI, определенные в Hibernate org.hibernate.engine.jdbc.env.spi.Ключевые слова Sql Ansi класс,
  • ключевые слова для конкретного диалекта, определенные Ключевыми словами sql |/Заданными в экземпляре объекта Hibernate Диалект .

Хотя вы можете автоматически указывать все идентификаторы, на самом деле гораздо лучше, если вы удаляете только те объекты базы данных, которые содержат зарезервированное ключевое слово. Это обеспечит лучший контроль, чем стратегии автоматического цитирования.

Вывод

Экранирование зарезервированных ключевых слов SQL просто при использовании JPA и Hibernate. Хотя экранирование на уровне столбцов JPA очень полезно, если у вас есть только небольшое количество идентификаторов базы данных, которые необходимо экранировать, когда количество идентификаторов базы данных, использующих зарезервированные ключевые слова, велико, глобальное экранирование Hibernate становится очень удобной альтернативой.