Java: automatically generate SQL queries

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

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

So, let's begin.
')

Let's begin, perhaps, with examples of use

Example №1

Suppose we have a certain class Person:

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

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

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

Having started, we get the following output in the console:

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

Example 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 number 3

I also created the MySQLClient class, which is able to 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 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 using the Reflection API enumerates all public and non-static fields of the class. If the field is of a type supported by the algorithm (all primitive data types are supported, their object analogs, as well as the String type), then a Column object is created from the Field object containing data about the field of the database table. 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 a SQL query is formed from all Column :

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

Similarly, ALTER TABLE, INSERT and UPDATE queries are generated. In the case of the last 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 ResultSetExtractor class, whose method extractResultSet (ResultSet resultSet, Class clazz) automatically creates from the resultSet a list of objects of the class clazz. This is done quite simply, so I will not describe the principle of its operation here.

On github you can see the full source code of the framework . I have it all.