Несколько лет назад я принялся за разработку одного крупного проекта на 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 использует для этого сокращения типов во время объявления полей, которые будут использоваться при подстановке значений. Используемые сокращения типов данных:
| Тип IQL | Тип при создании таблицы | Тип при вставке в запрос |
| %s | VARCHAR(255) | String, дополнительно обрабатываемый перед вставкой при помощи функционального интерфейса StringFilter, заданного статическим методом setStringFilte() |
| %v | VARCHAR(255) | String без дополнительной обработки (Только экранирование символа одинарной кавычки) |
| %t | TEXT | String, с дополнительной обработкой перед вставкой при помощи функционального интерфейса StringFilter, указанного статическим методом setTextFilter() |
| %i | INTEGER | int, без дополнительных обработок |
| %d | INTEGER | int, Date, либо String. В последнем случае String будет обработан при помощи SimpleDateFormat и форматирующей строки, заданной статическим методом setDateFormat() |
| %f | FLOAT | float, либо String |
| %b | BOOL | boolean, либо String |
Пример использования сокращений типов при вставке данных:
Задача:
Составить запрос для вставки нескольких строк в таблицу 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:
| setStringFilter(StringFilter sf) | предназначен для фильтрации строк типа %s перед вставкой |
| setTextFilter(StringFilter sf) | предназначен для фильтрации строк типа %t перед вставкой |
Предположим, перед нами стоит задача заменить HTML-теги HTML-последовательностями, например, знак < заменить на <.
Для этого мы можем воспользоваться следующей конструкцией:
IQL.setStringFilter( (s) -> s.replace("&","&").replace("<", "<").replace(">",">") ); После чего все вставляемые в запрос строки типа %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
ссылка на оригинал статьи https://habrahabr.ru/post/329280/
Добавить комментарий