IQL — очередной SQL-генератор для Java

Несколько лет назад я принялся за разработку одного крупного проекта на PHP, который только и делал что писал, читал и обновлял тонны данных в SQL. Сразу же было решено написать для этого проекта конструктор SQL-запросов, который тогда сильно облегчил мне жизнь и по сей день помогает мне и мои коллегам в ежедневной работе. Дело в том, что выложить этот конструктор в общественный доступ я не могу, так как проект принадлежит не мне, да и сильно завязан этот конструктор на наш фреймворк.

В связи с чем я решил разработать его аналог на Java, в надежде, что он сослужит кому-то добрую службу, так как не всем нужен ORM, а JOOQ кажется слишком сложным

Преимущества

• Исключение синтаксических ошибок при составлении запроса
• Упрощение многострочной вставки данных — не нужно конкатенировать запросы — достаточно просто вызывать метод insert() для каждой новой строки
• Генерация исключения, если DELETE или UPDATE выполняются без WHERE
• Операция UPSERT, которая вставит, либо обновит запись если указан WHERE
• Возможность дополнительной обработки строк перед вставкой (при помощи специального функционального интерфейса)
• Автоматический парсинг даты из числа int, из строки String, либо из объекта Date
• Порядок команд не имеет значения, например GROUP BY можно указать после LIMIT, WHERE после ORDER BY, а JOIN перед SELECT
• Упрощение работы с входными данными — не нужно использовать методы setString(), setInt() и другие методы ввода данных из PreparedStatment, достаточно просто ввести соотвествующие переменные
• Подстветка синтаксиса при использовании IDE (чего, обычно, лишена прямая запись SQL-запросов)
• Каждый запрос имеет похожую структуру и начинается с ввода таблиц, например для запроса SELECT сначала добавляются необходимые таблицы методом addTable(), тоже самое нужно сделать и для запросов UPDATE, INSERT, DELETE
• Может вернуть сгенерированный SQL-код, в отличие от PreparedStatement
• Запросы вставки и обновления данных имеют схожую структуру, в отличие от SQL, где работа с операторами INSERT и UPDATE полностью отличается

Недостатки

• Подходит преимущественно для CRUD без сложной логики
• Поддерживает не все типы данных
• Нельзя в одном запросе использовать функции и обычный выбор полей (исправлю в будущем)

Немного о том как это работает и чем это лучше PreparedStatement

У PreparedStatement, на мой взгляд, один минус — не совсем удобная вставка данных через setТип(индекс, значение), а класс IQL использует для этого сокращения типов во время объявления полей, которые будут использоваться при подстановке значений. Используемые сокращения типов данных:

Пример использования сокращений типов при вставке данных:

Задача:

Составить запрос для вставки нескольких строк в таблицу users, поля name (VARCHAR), birth_date (INTEGER) и level (INTEGER)

Решение

Connection con = DriverManager.getConnection("url", "login", "pass");

IQL iql = new IQL(con); //Создали экземпляр класса IQL
iql. 
    addTable("users").  //Добавляем таблицу users
    setInsertRows("name %s", "birth_date %d", "level %i"); //Устанавливаем поля для вставки
/* так как все публичные методы (кроме getStatement и getSQL) возвращают this, то можно использовать чейнинг там, где это удобно */
iql.insert("User1", "12.12.2012", 4); //вставка
iql.insert("User2", 1494968400, 3); //вставка
PreparedStatement ps = iql.getStatement(); //получение объекта PreparedStatement

В результате выполнения этого кода ps будет содержать следующий код, ожидающий выполнения:

INSERT INTO `users`(`name`, `birth_date`, `level`) VALUES ('User1', 1355256000, 4), ('User2', 1494968400, 3)

Обновление данных работает аналогично:

IQL iql = new IQL(con);
iql.
 addTable("users"). //добавление таблицы к запросу
 setUpdateRows("name %s", "birth_date %d"). //установка полей для обновления
 update("admin", "11.04.1986"). //ввод данных
 where("level %i", IQL.EQUAL, 3); //условие
String SQL = iql.getSQL(); //получение sql-кода

Что приведет к генерации следующего SQL-кода:

UPDATE `users` SET `name` = 'admin', `birth_date` = 513547200 WHERE `users`.`level` = 3

Следует особо отметить, что если при обновлении не указать условия (вызвав метод where() или whereId()), то будет сгенерировано исключение InsecureOperationException. Та же участь будет ждать и delete без условия.

Не обошлось и без виртуальной операции UPSERT, которая обновит данные, если указано хоть одно условие и добавит строку, если условий нет. Работает аналогично update и insert:

IQL iql = new IQL(con);
        iql.addTable("users").
        setUpsertRows("name %s", "birth_date %d"). //Установка изменяемых полей
        upsert("guest", "12.12.2012"); //Данные для вставки в запрос
        String SQL = iql.getSQL();

Создаст следующий код:

INSERT INTO `users`(`name`, `birth_date`) VALUES ('guest', 1355256000)

А если добавить к этому же коду where() или whereId(), то запрос полностью изменится:

IQL iql = new IQL(con);
        iql.addTable("users").
        setUpsertRows("name %s", "birth_date %d"). //Установка изменяемых полей
        upsert("guest", "12.12.2012"). //Данные для вставки в запрос
        whereId(122); //условие
        String SQL = iql.getSQL();

Запрос будет выглядеть следующим образом:

UPDATE `users` SET `name` = 'guest', `birth_date` = 1355256000 WHERE `users`.`id` = 122

Выборка и объединение таблиц

Простая выборка:

String SQL = new IQL(con).addTable("table1").select().getSQL();

Сгенерирует простейший:

SELECT * FROM `table1`

Мы можем усложнить выборку, добавив выбираемые поля и объединив несколько таблиц:

IQL iql = new IQL(con);
iql.
    //добавим таблицу domains, выберем поля subdomain и domain, где domain не NULL
    addTable("domains").select("subdomain", "domain").where("domain %s", IQL.ISNTNULL).
    //добавим таблицу orgs, выберем поле org_name с алиасом name, org_address с алиасом address
    addTable("orgs").select("org_name name", "org_address address").
    //где org_name таблицы orgs LIKE "%организация%"
    where("org_name %s", IQL.LIKE, "%организация%");
//присоединим к таблице 2 (orgs) таблицу 1 (domains) по полям orgs.id = domains.org_id
iql.join(2, "id", 1, "org_id");
String SQL = iql.getSQL();

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

IQL iql = new IQL(con);
iql.addTable("domains", "orgs").
     join(2, "id", 1, "org_id").
     setTable(1).select("subdomain", "domain").
     setTable(2).select("org_name name", "org_address address").
     setTable(1).where("domain %s", IQL.ISNTNULL).
     setTable(2).where("org_name %s", IQL.LIKE, "%организация%");
String SQL = iql.getSQL();

Однако здесь нужно использовать setTable(), чтобы установить активную таблицу. В первом случае вариант метода addTable() с добавлением только одной таблицы автоматически устанавливает ее активной.

Этот код сгенерирует следующий запрос:

SELECT 
`domains`.`subdomain` AS `domains_subdomain`, 
`domains`.`domain` AS `domains_domain`, 
`orgs`.`org_name` AS `name`, 
`orgs`.`org_address` AS `address` 
FROM `orgs` 
JOIN `domains` ON `orgs`.`id` = `domains`.`org_id` 
WHERE 
`domains`.`domain` IS NOT NULL AND 
`orgs`.`org_name` LIKE '%организация%'

Типы %s, %t, интерфейс StringFilter и дополнительная обработка строк
Для дополнительной обработки строк в классе IQL предусмотрен функциональный интерфейс StringFilter и статические методы класса IQL:

Предположим, перед нами стоит задача заменить HTML-теги HTML-последовательностями, например, знак < заменить на &lt;.

Для этого мы можем воспользоваться следующей конструкцией:

IQL.setStringFilter( (s) -> s.replace("&","&").replace("<", "&lt;").replace(">","&gt;") );

После чего все вставляемые в запрос строки типа %s будут обрабатываться этим способом:

IQL iql = new IQL(con);
iql.addTable("myvals");
iql.setInsertRows("price %f", "name %s");
iql.insert(3.22, "<h1>спички</h1>");
String SQL = iql.getSQL();

Полученный SQL-код:

INSERT INTO `myvals`(`price`, `name`) VALUES (3.22, '<h1>спички</h1>')

В общем это все. Буду рад, если этот класс покажется кому-то полезным, а также буду признателен за конструктивную критику.

Еще больше примеров и более полную документацию, описывающую все методы можно получить в wiki на github.

→ GitHub

Автор: r0ck3r

Источник