Java: automatically generate SQL queries

In this article, I will describe the creation of a framework for automatically generating SQL queries based on Java classes and objects. I understand that there are already many ready-made similar solutions, but I wanted to implement this myself.

To create the framework, we will use Java annotations and the Java Reflection API.

So, let's begin.

Let's start with examples of use

Example No. 1

Let's say we have some class Person:

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

The following call will produce an SQL query to create a table based on this class:

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

Running it, we get the following output in the console:

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

Example No. 2

Now the example is more complicated using annotations:

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

Based on this class, we get the following SQL query:

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`));

Example No. 3

I also created the MySQLClient class , which can connect to the database server and send generated SQL queries there.

The client contains the following methods: createTable , alterTable , insert , update , select .

It is used approximately like this:

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(); // Отключаемся от БД

How it works

First, the algorithm uses the Reflection API to iterate over all the public and non-static fields of the class. If the field in this case has a type supported by the algorithm (all primitive data types, their object analogues, as well as String type are supported), then a Column object is created from the Field object , containing data about the database table field. Conversion between Java data types and MySQL types occurs automatically. Also, from the annotations of the field and class, all modifiers of the table and its fields are extracted. Then , an SQL query is formed from all Column :

public static String generateCreateTableQuery(Class clazz) throws MoreThanOnePrimaryKeyException {
        List 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;
}

Similarly, ALTER TABLE, INSERT, and UPDATE queries are generated. In the case of the latter two, in addition to the Column list, the values ​​of its fields are also extracted from the object:

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);
}

Also in the framework there is a class ResultSetExtractor , the method of which extractResultSet (ResultSet resultSet, Class clazz) automatically creates a list of objects of class clazz from resultSet. This is done quite simply, so I will not describe the principle of its action here.

On github you can see the full source code of the framework . That’s all for me.