23.05.2017 10:58
r0ck3r 27 3600 Источник
Несколько лет назад я принялся за разработку одного крупного проекта на 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
    setInsertFields("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"). //добавление таблицы к запросу
 setUpdateFields("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").
        setUpsertFields("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").
        setUpsertFields("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-последовательностями, например, знак < заменить на &lt;.

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

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

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

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

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

INSERT INTO `myvals`(`price`, `name`) VALUES (3.22, '&lt;h1&gt;спички&lt;/h1&gt;')

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

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

> GitHub

UPD: Исправил некорректные имена методов
Поделиться с друзьями
-->

Комментарии (27)


  1. GreenStore
    23.05.2017 14:26
    #10229788

    Извиняюсь, а каким образом было выбрано название метода «setInsertRows», как это соответствует его назначению?


    1. r0ck3r
      23.05.2017 16:17
      #10230054

      Вы абсолютно правы. К сожалению, мной была допущена глупая ошибка, в результате которой я называл методы setInsertRows() и setUpdateRows(), вместо положенных setInsertFields() и setUpdateFields(). Сейчас это недоразумение исправлено здесь, в коде и в Wiki, а я приношу Вам глубочайшие извинения


  1. PutPixel
    23.05.2017 16:17
    #10230056

    А вы рассматривали queryDSL? Для создания самой базы может и не пойдет, но вот писать запросы явно удобней


    1. r0ck3r
      23.05.2017 16:25
      #10230082

      Этот проект остался мной незамеченным. Очень Вам благодарен — я обязательно его посмотрю


  1. nikitasius
    23.05.2017 16:29
    #10230096

    Никогда не понимал таких "фишек". Ейбогу, гораздо проще зайди в базу и написать и оттестить запрос на чистом SQL.


    1. r0ck3r
      23.05.2017 16:41
      #10230124

      Как мне кажется, это дело подхода, ведь кому-то нравится держать запросы в отдельном файле, кому-то внутри кода, а кто-то использует ORM. Я, в свою очередь, показал Вам свой подход. Более того, как мне кажется, выбрать что-то одно на все случаи жизни и вовсе невозможно — всегда могут возникнуть задачи, лучше решаемые одним способом, чем другим


      1. nikitasius
        23.05.2017 23:56
        #10231032

        Я не против этого решения :)
        Каждый работает так, как считает эффективным. Если кто-то всю сознательную жизнь работал на чистом SQL, то он и продолжит на нем работать. Новичку же, вероятно будет проще с текущим вариантом.


        Хотя ему так и так придется изучать теорию и нормальные формы, чтобы уметь эффективно проектировать базы.


        1. r0ck3r
          24.05.2017 00:02
          #10231038

          Спасибо за Ваше мнение :)


  1. Regis
    23.05.2017 20:21
    #10230730
    +1

    Обычно либо хватает простого SQL, либо, если уж хочется статической проверки, то полноценно подключается тот же упомянутый JOOQ. А тут довольно странный промежуточный шаг. Впрочем, на вкус и цвет...


  1. aleksandy
    23.05.2017 20:26
    #10230742

    Ввиду

    Сгенерирует простейший:
    SELECT * FROM `table1`

    есть подозрение, что эта штука поддерживает только mysql.


    1. r0ck3r
      24.05.2017 00:02
      #10231036

      Ваша правда. Я сознательно упустил этот момент в изложенном материале, так как понимаю, что такое ограничение не уместно и работаю сейчас над поддержкой PostgreSQL. Спасибо


  1. rraderio
    23.05.2017 21:57
    #10230870

    Что будет если написать
    "name %i"

    где name VARCHAR(80)


    1. r0ck3r
      23.05.2017 23:33
      #10230984

      запрос успешно выполнится, но в качестве значения для name Вы будете должны указать значение типа int


      1. r0ck3r
        23.05.2017 23:45
        #10231002

        либо String, которое, однако, также будет приведено к типу int


  1. gltrinix
    23.05.2017 23:07
    #10230960

    А как обращаться к колонкам, которые имеют в названиях пробелы и спецсимволы:  "`My cRaZy %` $d"?


    1. r0ck3r
      23.05.2017 23:34
      #10230988

      символ ` у меня не экранируется, поэтому нужно подождать, пока я это исправлю, а в остальном все должно работать как надо


      1. gltrinix
        24.05.2017 00:24
        #10231062

        Обычно обратные кавычки это и есть экранирование имен колонок.


        1. r0ck3r
          24.05.2017 10:17
          #10231458

          Да, но дело в том, что они будут добавлены к имени этой колонки. В результате получится так, что обратная кавычка закроет добавленную конструктором открывющую


  1. leorush
    24.05.2017 09:29
    #10231376

    У SQL Builder в чём-то схожий подход. 

    new SelectBuilder()
    .column("name")
    .column("age")
    .from("Employee")
    .where("dept = 'engineering'")
    .where("salary > 100000")
    .toString();

    Получается
    select name, age from Employee where dept = 'engineering' and salary > 100000


    1. nikitasius
      24.05.2017 10:02
      #10231420

      А как там будет выглядеть:


      select e.name, e.surname, b.summ from employe e, bills b where b.employeid=e.id and ((e.age>51 and b.type=2) or b.type=1);

      Выбрать ФИО+суммы для всех, кто старше 51 года и получал платежи типа 2 или всех кто получил платеж типа 1.


      1. leorush
        24.05.2017 10:34
        #10231496
        +1

        Как-то так

        new SelectBuilder()
    .column("e.name")
    .column("e.surname")
    .column("b.summ")
    .from("employe e, bills b")
    .where("b.employeid=e.id and ((e.age>51 and b.type=2) or b.type=1)")
    .toString();


        Прелесть в том что можно строить запрос динамически, вот тут автор показывает как:


        1. nikitasius
          24.05.2017 10:48
          #10231528

          Спасибо! Я просто хотел для себя проветить теорию о том, что на сложных запросам будет таки больше SQL, и что (мое личное мнение) выигрышь получается лишь на простых запросах.


    1. r0ck3r
      24.05.2017 13:50
      #10231902

          .where("dept = 'engineering'")
    .where("salary > 100000")

      Если я правильно понял, то в случае когда значение для dept или salary приходит от пользователя, то его нужно фильтровать вручную, иначе тут будет присутствовать SQL-инъекция.

      В моем случае запись бы имела следующий вид:
      .where("dept %s", IQL.EQUAL, "engineering")
.where("salary %i", IQL.MORE, 10000)

      что привело бы к экранированию escape-последовательностей, а сигнатуры типов (%s и %i) здесь нужны именно для этого


  1. nestor_by
    24.05.2017 11:12
    #10231594

    Порядок команд не имеет значения, например GROUP BY можно указать после LIMIT, WHERE после ORDER BY, а JOIN перед SELECT

    по мне так это скорее минус чем плюс, в лучшем случае у вас получится йода стайл, в худшем будете искать где же кто поставил этот лимит.


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

    В целом jooq очень хорош и по мне разобраться с ним не так сложно.


  1. d_pa
    24.05.2017 23:59
    #10233112

    А как будет выглядеть простой запрос на несколько сотен строк типа такого:

    INSERT INTO NameYourTableResult (
		Field1,
		Field2,
		Field3 
		... 
		FieldN
	)
	WITH --в этот блок помещаются временные таблицы, которые и заменяют View
	TempTable1 as (
		--Сложный SQL запрос, результат которого будет храниться в TempTable1 на протяжении всего INSERT INTO
	)
	TempTable2 as (
		--Сложный SQL запрос, в котором мы можем использовать временную таблицу TempTable1 и другие таблицы базы данных
	)
	TempTable3 as (
		--Сложный SQL запрос, в котором мы можем использовать временные таблицы TempTable1, TempTable2 и другие таблицы базы данных
	)
	--... и так далее, но главное не увлекаться :)
	
	--Основной блок SELECT, из которого результаты пойдут в команду INSERT INTO (количество полей в SELECT равно количеству полей в INSERT)
	--В этом запросе SELECT используются временные таблицы TempTable1, TempTable2, TempTable3 и т.д. и другие источники данных
	SELECT --Перечень полей
	FROM TempTable3, sourceTable1, sourceTable2, sourceTable3
	JOIN ...
	WHERE ...;


    1. r0ck3r
      25.05.2017 00:02
      #10233114

      Такого рода запросы должны писаться вручную в любом случае


    1. leorush
      25.05.2017 19:47
      #10234834

      Когда сталкивался с таким, разбивал на несколько простых запросов, а потом конкатенацией делал один общий.