Java: автоматически формируем SQL-запросы

    В этой статье я опишу создание фреймворка для автоматической генерации SQL-запросов на основе классов и объектов Java. Я понимаю, что уже существует множество готовых подобных решений, но мне захотелось реализовать это самому.

    Для создания фреймворка будем использовать Java-аннотации и Java Reflection API.

    Итак, начнем.


    Начнем, пожалуй, с примеров использования


    Пример №1


    Допустим, у нас есть некий класс Person:

    public static class Person {
        public String firstName;
        public String lastName;
        public int age;
    }
    

    Следующий вызов выдаст SQL-запрос для создания таблицы на основе этого класса:

    System.out.println(MySQLQueryGenerator.generateCreateTableQuery(Person.class));
    

    Запустив, получим в консоли следующий вывод:

    CREATE TABLE  `Person_table` (
    `firstName` VARCHAR(256),
    `lastName` VARCHAR(256),
    `age` INT);
    

    Пример №2


    Теперь пример посложнее, с использованием аннотаций:

    @IfNotExists // Добавлять в CREATE-запрос IF NOT EXISTS
    @TableName("persons") // Произвольное имя таблицы
    public static class Person {
        @AutoIncrement // Добавить модификатор AUTO_INCREMENT
        @PrimaryKey // Создать на основе этого поля PRIMARY KEY
        public int id;
        @NotNull // Добавить модификатор NOT NULL
        public long createTime;
        @NotNull
        public String firstName;
        @NotNull
        public String lastName;
        @Default("21") // Значение по умолчанию
        public Integer age;
        @Default("")
        @MaxLength(1024) // Длина VARCHAR
        public String address;
        @ColumnName("letter") // Произвольное имя поля
        public Character someLetter;
    }
    

    На основе данного класса получим следующий SQL-запрос:

    CREATE TABLE IF NOT EXISTS `persons` (
    `id` INT AUTO_INCREMENT,
    `createTime` BIGINT NOT NULL,
    `firstName` VARCHAR(256) NOT NULL,
    `lastName` VARCHAR(256) NOT NULL,
    `age` INT DEFAULT '21',
    `address` VARCHAR(1024) DEFAULT '',
    `letter` VARCHAR(1),
    PRIMARY KEY (`id`));
    

    Пример №3


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

    Клиент содержит следующие методы: createTable, alterTable, insert, update, select.

    Используется он примерно так:

    MySQLClient client = new MySQLClient("login", "password", "dbName");
    client.connect(); // Подключаемся к БД
    
    client.createTable(PersonV1.class); // Создаем таблицу
    client.alterTable(PersonV1.class, PersonV2.class); // Изменяем таблицу
    
    PersonV2 person = new PersonV2();
    person.createTime = new Date().getTime();
    person.firstName = "Ivan";
    person.lastName = "Ivanov";
    client.insert(person); // Добавляем запись в таблицу
    
    person.age = 28;
    person.createTime = new Date().getTime();
    person.address = "Zimbabve";
    client.insert(person);
    
    person.createTime = new Date().getTime();
    person.firstName = "John";
    person.lastName = "Johnson";
    person.someLetter = 'i';
    client.insert(person);
    
    List selected = client.select(PersonV2.class); // Извлекаем из таблицы все данные
    System.out.println("Rows: " + selected.size());
    for (Object obj: selected) {
        System.out.println(obj);
    }
    
    client.disconnect(); // Отключаемся от БД
    

    Как это работает


    Сперва алгоритм с помощью Reflection API перебирает все public и не-static поля класса. Если поле при этом имеет поддерживаемый алгоритмом тип (поддерживаются все примитивные типы данных, их объектные аналоги, а так же тип String), то из объекта Field создается объект Column, содержащий данные о поле таблицы базы данных. Конвертация между типами данных Java и типами MySQL происходит автоматически. Так же из аннотаций поля и класса извлекаются все модификаторы таблицы и ее полей. Затем уже из всех Column формируется SQL-запрос:

    public static String generateCreateTableQuery(Class clazz) throws MoreThanOnePrimaryKeyException {
            List<Column> columnList = new ArrayList<>();
            Field[] fields = clazz.getFields(); // получаем массив полей класса
    
            for (Field field: fields) {
                int modifiers = field.getModifiers();
                if (Modifier.isPublic(modifiers) && !Modifier.isStatic(modifiers)) { // если public и не static
                    Column column = Column.fromField(field); // преобразуем Field в Column
                    if (column!=null) columnList.add(column);
                }
            }
    
            /* из полученных Column генерируем запрос */
    }
    
    /***************************/
    
    public static Column fromField(Field field) {
        Class fieldType = field.getType(); // получаем тип поля класса
        ColumnType columnType;
        if (fieldType == boolean.class || fieldType == Boolean.class) {
            columnType = ColumnType.BOOL;
        } /* перебор остальных типов данных */ {
        } else if (fieldType==String.class) {
            columnType = ColumnType.VARCHAR;
        } else { // Если тип данных не поддерживается фреймворком
            return null;
        }
    
        Column column = new Column();
        column.columnType = columnType;
        column.name = field.getName();
        column.isAutoIncrement = field.isAnnotationPresent(AutoIncrement.class);
        /* перебор остальных аннотаций */
        if (field.isAnnotationPresent(ColumnName.class)) { // если установлено произвольное имя таблицы
            ColumnName columnName = (ColumnName)field.getAnnotation(ColumnName.class);
            String name = columnName.value();
            if (!name.trim().isEmpty()) column.name = name;
        }
    
        return column;
    }
    

    Аналогичным образом формируются запросы ALTER TABLE, INSERT и UPDATE. В случае двух последних помимо списка Column из объекта так же извлекаются значения его полей:

    Column column = Column.fromField(field);
    if (column!=null) {
        if (column.isAutoIncrement) continue;
        Object value = field.get(obj);
        if (value==null && column.hasDefaultValue) continue; // есть один нюанс: для корректной работы значений по умолчанию предпочтительно использовать объектные типы вместо примитивных
        if (column.isNotNull && value==null) {
            throw new NotNullColumnHasNullValueException();
        }
        String valueString = value!=null ? "'" + value.toString().replace("'","\\'") + "'" : "NULL";
        String setValueString = "`"+column.name+"`="+valueString;
        valueStringList.add(setValueString);
    }
    

    Так же в фреймворке есть класс ResultSetExtractor, метод которого extractResultSet(ResultSet resultSet, Class clazz) автоматически создает из resultSet список объектов класса clazz. Делается это довольно просто, так что расписывать принцип его действия я здесь не буду.

    На github можно посмотреть полный исходный код фреймворка. На этом у меня все.
    Поделиться публикацией
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама

    Комментарии 19

      +1
      Я в свое время что то аналогичное писал just for fun. И даже реализовал «foreign key».
      А думали как делать выборку сразу из двух таблиц?
        0

        Есть одна идея. Можно, например, сделать как-то так:


        class City {
            int id;
            String name;
        }
        
        class Person {
            String fullName;
            @ObjectLink(columnName="cityId",linkToField="id")
            City city;
        }
          0
          Сейчас порылся в старом коде, я foreign ley делал без аннотаций :)
        +1

        Да когда то давно мой первый тим лид тоже дал таск на саморазвитие написать мини хибернейт. Сразу после этого с любым фреймворком стало проще работать зная что под капотом. Но рефлексия по полям только этого мало. Попробуйте на досуге генерить используя методы. Может как и я споткнетесь с проблемой boolean vs Boolean геттерами))

          0
          кстати да :)
          с методами все становится намного интереснее :)
          • НЛО прилетело и опубликовало эту надпись здесь
          +1

          Как обстоят дела с sql-инъекциями? Исходя из того, что я увидел, Ваш 'фреймворк' их полностью поддерживает)

            0
            String valueString = value!=null ? "'" + value.toString().replace("'","\\'") + "'" : "NULL";

            По идее подобное экранирование должно обезопасить от инъекций. Или я не прав? )

              –1
              Не только ковычки нужно экранировать
                +2

                Не только. Символы комментариев,, точка с запятой. А можно не изобретать велосипед и воспользоваться prepared statement где уже бородатые дядьки в своё время всё сделали))

                  –1

                  Добавил такое вот:


                  private static String escapeString(String str) {
                          return "'" +
                                  str
                                  .replace("\\", "\\\\")
                                  .replace("\0", "\\0")
                                  .replace("'", "\\'")
                                  .replace("\"", "\\\"")
                                  .replace("\b", "\\b")
                                  .replace("\n", "\\n")
                                  .replace("\r", "\\r")
                                  .replace("\t", "\\t")
                                  + "'";
                      }
                  
            +2
            Вообще эти штучки используют лишь 1% возможностей sql запросов. С точки зрения их разработчика — да, это интересно, но с точки зрения практического применения результат близок к 0. Время потраченное на изучение и применения этого — лучше потратить на более полное изучение sql. А насчет инъекций — полная защита — использование хранимых процедур. Они подвержены инъекции если только писавший их специально даст такую возможность.
              +1

              Сорри, случайно поставил минус вместо плюса. Полностью поддерживаю по первой части. А по поводу инъекций — нафиг хранимые процедуры, достаточно PreparedStatement и '?'.

                0
                достаточно PreparedStatement и '?'
                практически да, но возможности хранимки больше.
                0
                Насчет практического применения я бы поспорил, ОРМы очень широко используются.
                Хранимые процедуры, это конечно здорово, но иметь логику в двух местах — в сервере приложений и в БД — не самая лучшая мысль. А всю логику в хранимки не перенесешь. Причем, использовать их возможно только если на сервис уровне только SQL, потому что у ОРМов кеш ломается.
                Для хайлоада, наверняка, будет неплохо. Для всего остального, чистый SQL — долго и дорого.
                  +1
                  ОРМы очень широко используются.
                  то что они используются — не подтверждает тот факт, что возможности sql они используют полностью.
                  Хранимые процедуры, это конечно здорово, но иметь логику в двух местах — в сервере приложений и в БД — не самая лучшая мысль.
                  утвержение спорное. для меня, достаточно хорошо знающего sql, нет сложности, я выбираю компромис, стараюсь чтоб система работала быстрее и была понятнее в сопровождении.
                  Для всего остального, чистый SQL — долго и дорого.
                  я б не сказал что дорого и долго, есть хорошие инструменты (не в качестве рекламы — dbForge for mysql) которые позволяют все делать качественно и быстро. Причём хранимые процедуры даже дебажить. и тут есть огромное ускорение — даже небольшой запрос изменить проще в гуи, отладить и прочее. Мы не трогаем код программы, не компилируем, не запускаем. Изменили хранимку — проверили. а ведь если запрос на 2-3 экрана? чтоб что-то изменить, проверить… и перенести в основной код, вставить как строку, проверить все кавычки… откомпилировать, сделать кучу действий, чтоб дойти до нужного места где производится вызов запроса…
                  К сожалению в последнее время мало уделяют обучению sql. Всё больше используют «хибер», считая его панацеей для решения задач. Но по мне — это «прокладка»… Это первое. Второе — когда смотришь, что делается с помощью хибера и ему подобных, понимаешь, что люди пытаются повторить возможности субд, не имея для этого знания. А это уже сказывается на качестве системы.
                0

                А почему вы решили сделать включение IF NOT EXISTS на уровне сущности, а не на уровне параметра метода createTable()?


                Не смог легко придумать сценарий, когда имеет смысл делать разные настройки для разных сущностей, а вот аннотации развешивать, если что, придётся везде.

                  0

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

                  0
                  Выглядит неплохо, но насколько я понял в текущей версии аннотации являются только разметкой для генерации запроса, но не позволяют контролировать изменение полей. Например @MaxLength(1024) не помешать записать в поле объекта строку большей длины и ошибка возникнет только при сохранении в базу данных. Интересно есть ли реализации которые позволяют генеренировать Java-код который контролирует выполнение ограничений на поля объекта (примерно как в Lombok).

                  Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                  Самое читаемое