How to automatically generate DTOs based on dynamic SQL code

The current situation

Generally do database related development, unless learning, otherwise very few people are willing to use JDBC directly. Originally Java code is more verbose, and the direct use of JDBC to write code of the verbose is a bit crazy! So in the actual development process, we usually use some frameworks/libraries to help us operate the database. And the open source market choice is also more, I personally contact to have: Hibernate,mybatis,jdbctemplate,dbutils,activerecord,javalite and so on. These frameworks can greatly improve the development efficiency, for some basic crud operations, although there are differences, but overall basically enough.

However, for a slightly more complex data query, it is always necessary to manually write SQL code, and even need to dynamically splicing SQL according to parameters. Frameworks basically have their own way of stitching up dynamic SQL, and it's easy to turn the queried data into objects (DTOS).

So far, though, these frameworks can easily help us map the data, but these dtos need to be written by hand.

Problems that exist

Usually after we have written the query code for SQL, we need a corresponding DTO to map the data queried in the database to the DTO so that the calling program can better use the data. Of course, for the sake of convenience, data is sometimes stored directly in a data structure such as map. However, this approach to map is lightweight, but it brings several potential issues that are more important than the following:

• The caller needs to remember the name of each key in the map, which brings some so-called memory burdens to the programmer.
• Heavy memory burdens can lead to complex logic, difficult to understand, and more difficult to maintain.
• It is difficult to find the problem after the SQL changes cause the key to change, requiring the programmer to handle these changes very carefully

If you want to avoid these problems with map, we need to write the DTO separately for each SQL query. Although it is not difficult to write these dtos, it is tedious, especially when there are many fields, and if the fields of the SQL query change, remember to modify the DTO. The separate authoring of DTOs reduces some of the problems associated with map, and adds additional workload.

If there is a way to write SQL code (including dynamic splicing of SQL) after the completion of the automatic to do the following 2 points is perfect:

1. Generate the corresponding DTO directly based on the SQL code
2. Change the SQL code to automatically modify the corresponding DTO

This, on the one hand, solves the trouble of hand writing DTOs, on the other hand, when modifying SQL causes a field to change, because the automatically generated DTO will also be modified synchronously, in those references to this field, the compiler will immediately give the error prompt! So that the problem can be discovered immediately, which avoids a lot of potential problems.

This article is trying to solve the problem of how to automatically generate DTOs based on SQL code, eliminating the hassle of manual writing and improving Programmer's development efficiency.

Solution to the idea

The ideal is always very good, the reality is always very cruel!

So, in the end, we can first analyze the feasibility of automatically generating DTOs:

The core of the automatic generation of DTOs is to get each column name and its data type corresponding to the SQL query. With column names and data types, it's easy to write a method to produce a dto.

We know that, in general, after the SQL query has been written, including calling stored procedures and those that are dynamically stitched according to the invocation parameters, although the resulting SQL may not be the same, the field portion of its query results is relatively fixed.

Of course, there are very few cases where the field is uncertain, but in this extreme case, even if it is impossible to write a dto by hand, it is more appropriate to use a map, and we do not discuss it here.

So, how do you get the column name and type?

One scenario is to parse the fields in the Select section of the SQL code, but the limitations are large:

• For splicing SQL code, the analysis is more difficult
• The type of the field is also difficult to judge
• SELECT * ...; Call statement is also very difficult to analyze with such common query methods.

The above scenario seems to be somewhat feasible in the way that the configuration file (XML) is written in SQL, like MyBatis, which I have not experimented with, but it is unlikely that there will be less difficulty.

Another option is to run the code that contains SQL directly:

We know that JDBC executes an SQL query that returns a ResultSet object, and through the method GetMetaData () in that object, we can get some metadata for this query: column name, column type, and table name of the column, etc. This information is enough for us to produce the class that is needed.

So, how can you run these SQL-containing code?

For those fixed SQL statements, it's a little bit more to say, we get this fixed SQL, call JDBC to get metadata, and then we can easily build the DTO based on this information. However, for those complicated SQL queries that need to be generated dynamically based on a series of parameters, they cannot be run directly before the parameters are set up, they cannot get metadata, and we cannot generate dtos without metadata.

What to do?

As discussed earlier, even with dynamic SQL, regardless of the input parameters, the SQL statements executed may not be the same, but the resulting columns are fixed. The problem that we need to solve now is not to get these column information? In that case, let's construct a series of default parameter values. These parameters are not really useful, just so that we are editing the SQL code to run properly in order to get the required metadata, as to whether the data can be queried is not critical.

Usually we write the SQL code, there are 2 forms of existence: one is directly in the Java code, the other is placed in the configuration file. It's better not to talk about it here, and I'll find another place to talk about it later. The main discussion here is SQL, which is spliced in Java code, and how to implement a code generator to generate these DTOs automatically:

To solve this problem automatically, let's take a look at some of the challenges that this code generator faces and how to deal with it:

• How to identify a section of SQL code that needs to generate a DTO

First, we need to identify this piece of code so that the code generator can run this section to generate the DTO code. In general, our data interfaces are method-level, so we can annotate the method and use annotations to identify the method to return a Dto object is a good choice.

• How to define the class name of a DTO

An easy-to-think approach is to automatically combine a name with the class name + method name where the SQL code resides, and, of course, to allow the programmer to specify a name, sometimes for flexible control.

• How to execute code

The key to executing the code is to construct a batch of appropriate parameters that invoke the annotation method. Of course, first of all, the annotated method of code analysis to extract the method parameter name and type. Code analysis can be done with tools like JAVACC, or some parser, not scrutiny here. The following is a discussion of the construction of default parameters:

To simplify the problem, by default we can construct the following rules:

数字型参数，默认为：0, 例如：public Object find(int arg){...} 构造 int arg=0;  字符串参数，默认为："",     构造 String arg="";  布尔型参数，默认为：false,  构造 boolean arg=false;  数组型参数，默认为：类型[0], 构造 int[] arg=new int[0];  对象型参数，默认为：new 类型()， 例如：public Object find(User arg){...} 构造 User arg=new User();  

Of course, for some simple parameters, the above construction rules will basically work. However, for some parameters: for example, the parameter is an interface, or a table name that needs to be dynamically connected, or the logic of the SQL splicing code requires that the parameter must be some special value, and so on, the default constructed parameter will cause the program to be unable to execute.

But how is it possible for our code generator to continue to execute? It seems that there is really nothing that can be done automatically, so we have to give this problem to the programmer and let the programmer help the code generator to initialize the parameters.

We can provide a parameter on the annotation that mainly completes setting parameters that cannot be initialized under the default rule. Of course, the initialization code in this parameter can also override the default rules so that we can test the execution of different SQL processes during the editing phase.

• how to generate a DTO

After the above series of processing, we can finally automatically put the method containing SQL query code to run up. But now we don't have the metadata we want, and we can't generate DTOs.

One possible way is to wrap a JDBC and intercept the SQL queries that are executed when this method is called, but the problem is that if there are multiple queries in the method it will be more cumbersome.

Another way to rely on framework support, you can intercept the return statement of the method, get its execution of the SQL statement, with SQL statements, the generation of DTOs is no difficulty.

• How to modify the code

To minimize the programmer's work, our code generator will need to automatically modify the return value of the method to the DTO class after it has generated the DTO.

• How to handle changes in SQL

The simple thing to do is that once some SQL code changes, all DTOs are regenerated in the previous way. However, it is clear that the process of generating DTO code is slow to unbearable when there are many query methods.

Another more plausible approach is that we add a fingerprint field to the DTO when it is generated, and its value can be generated using the information contained in the SQL code, for example: code length + Code hashcode. Before deciding whether or not to deal with this method, the code generator calculates the fingerprint of the method compared to the fingerprint present in the DTO, and if the same is skipped, it is considered that the SQL of this method has changed and the DTO needs to be updated.

The specific implementation

So far, basically the main hurdle of the DTO code generator has been the corresponding approach. Finally, we use a concrete implementation to make a simple example.

There are 2 items to be introduced here:

• Monalisa-db:https://github.com/11039850/monalisa-db

This is a very simple ORM framework that introduces a database through @db (Jdbc_url,username,password) annotations, and also implements some basic operations on the database.

• Monalisa-eclipse:https://github.com/11039850/monalisa-eclipse

This is a corresponding eclipse plug-in that can:

1. @DB annotated interface to automatically generate table CRUD operations when files are saved
2. @Select Annotated method to automatically generate a DTO when the file is saved
3. Write multi-line strings with ease

Plug-in installation and setup can be consulted: Https://github.com/11039850/monalisa-db/wiki/Code-Generator

The following is an example of an automatically generated DTO based on dynamic SQL, the complete example project can be consulted: https://github.com/11039850/monalisa-example

     PackageTest.dao; Public  class Userblogdao {        //@Select Note indicates that the method needs to generate a DTO automatically        //Default class Name: Result + method Name, default package name: The package name of the data access Class + "." + Name of the data access Class (lowercase)        //Optional parameter: name specifies the name of the resulting class, or the default class name if the parameter is not specified        //Optional parameter: Build initializes the Java fragment Code of the calling parameter, replacing the default parameter construction rule        @Select(name="Test.result.UserBlogs")//!!! will automatically modify the return value of the function after it is saved: List--list<userblogs>        //When writing for the first time, because the result class does not exist, in order to ensure that the compilation is normal,        The return value of the//function and the result of the query are replaced with a generic value, and the plugin is automatically modified after saving.        the corresponding relationship between the return value of the//function and the generic value of the query result is divided into three categories as follows:        //1. List Query        //public DataTable method_name (...)   {... return query.getlist (); } or        //public List method_name (...)   {... return query.getlist (); }            //        //2. Page Query        //public Page method_name (...)      {... return query.page (); }        //        //3. Single record        //public Object method_name (...) {... return query.getresult ();}        //         PublicListSelectuserblogs(intUSER_ID) {Query q=testdb.db.createquery (); Q.add (""/**~{SELECT a.id,a.name,b.title, b.content,b.create_time from User A, blog b            WHERE a.id=b.user_id and a.id=? }*/, user_id);returnQ.getlist (); }     }

Once the code is saved, the plugin automatically generates a DTO class: Test.result.UserBlogs, and automatically modifies the method to the following declaration:

        public List<UserBlogs>  selectUserBlogs(int user_id){             ...            return q.getList(UserBlogs.class);         }

Of course, if you make any changes to the Selectuserblogs method (including just adding a space), the plugin will automatically update the userblogs when the file is saved.

At the same time, in order to facilitate our debugging, the plugin will also output similar information in the Eclipse console window:

2016-06-27 17:00:31 [I] ****** Starting generate result classes from: test.dao.UserBlogDao ******   2016-06-27 17:00:31 [I] Create class: test.result.UserBlogs, from: [selectUserBlogs(int)]SELECT a.id,a.name,b.title, b.content,b.create_time    FROM user a, blog b            WHERE a.id=b.user_id AND a.id=0

By the way, please add:

One of the most annoying things about writing SQL in Java code is the connection problem of strings in the Java language. So that large sections of the middle of the SQL code to insert a lot of newline/escape symbols, it is cumbersome to write, look uncomfortable. The Monalisa-eclipse plugin also solves the problem of writing multi-line strings.

For example:

    System.out.println(""/**~{        SELECT *             FROM user            WHERE name="zzg"    }*/);

The output will be:

    SELECT *         FROM user        WHERE name="zzg"

Of course, for quick writing, you can set the syntax of a multiline string to a code template in eclipse. For more details on multi-line syntax, refer to: Https://github.com/11039850/monalisa-db/wiki/Multiple-line-syntax

Here, dynamic SQL code automatically generated DTO ideas and implementation examples are basically finished, welcome to all kinds of rational opinions, discussion, progress, thank you!

How to automatically generate DTOs based on dynamic SQL code