Current status

Do database-related development, and very few people want to use JDBC directly unless they learn it. Java code is already verbose, and writing code directly in JDBC is maddeningly verbose! So in the actual development process, we usually use some framework/library to help us manipulate the database. And there are many choices in the open source market. I have come into contact with Hibernate, MyBatis, JdbcTemplate, DbUtils, ActiveRecord, JavaLite and so on. Each of these frameworks can greatly improve development efficiency and, while varying, generally suffice for some basic CRUD operations.

However, for slightly more complex data queries, there is always the need to manually write SQL code, or even dynamically concatenate SQL based on parameters. Each framework has its own way of concatenating dynamic SQL, and can easily convert the queried data into objects (Dtos).

So far, though these frameworks can easily help us to complete data mapping, these Dtos still need to be written manually.

Existing problems

Usually, after we write SQL query code, we need to have a corresponding DTO map the data queried in the database to THE DTO, so that the program can better use these data. Of course, sometimes data is stored directly in a data structure like a Map for convenience. However, the Map approach, while lightweight, presents several potential problems that are more important than that:

  • The caller needs to remember the name of every key in the Map, which imposes some so-called memory burden on the programmer

  • Excessive memory burden will lead to complex logic of the system, difficult to understand, and more difficult to maintain

  • SQL changes cause Key changes that are difficult to detect and require programmers to handle these changes very carefully

If we want to avoid these problems with Map, we need to write a SEPARATE DTO for each SQL query. While these Dtos are easy to write, they can be tedious, especially if there are many fields; Also, if the SQL query fields change, remember to come back and modify the DTO. Writing a separate DTO alleviates some of the problems associated with Map, but it also adds a new workload.

It would be great if there was a way to automatically do two things when SQL code (including dynamically concatenated SQL) is written:

  1. According to the SQL code, the corresponding DTOS are generated directly

  2. The DTO is automatically modified when the SQL code is changed

In this way, on the one hand, the trouble of manual DTO writing is solved; On the other hand, when changing SQL causes a field to change, the compiler will immediately give an error message where the field is referenced, because the automatically generated DTOS are also synchronized! Problems can be found as soon as they arise, which can avoid many potential problems.

This paper is trying to solve the problem of how to automatically generate DTO according to SQL code, save the trouble of manual writing and improve the efficiency of programmer development.

Solution idea

Ideal is always very beautiful, reality is always very cruel!

So, whether this idea can be realized or not, let’s first preliminarily analyze the feasibility of automatic GENERATION of DTO:

To automate the generation of Dtos, the key is to get each column name and data type corresponding to the SQL query. With column names and data types, it’s easy to write a method to generate a DTO.

As we know, in general, after SQL queries are written, including those that call stored procedures and those that are dynamically concatenated according to the call parameters, the final SQL run may be different, but the field part of the query result is relatively fixed.

Of course, there are rare cases where you will encounter queries with indeterminate fields, but in such extreme cases, it is not possible to write dtos manually. Instead, it is more appropriate to use a Map, which we will not discuss here.

So, how to get the column name and type?

One solution is to analyze the SELECT part of the SQL code, but there are some limitations:

  • For the spliced SQL code, it is difficult to analyze

  • The type of the field is also difficult to determine

  • SELECT * … ; Common queries such as CALL Statements can also be difficult to analyze

The above scheme seems to be feasible to write SQL using configuration file (XML) like Mybatis. I have not tested it, but I expect to face difficulties.

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

We know that JDBC executes an SQL query and returns a ResultSet object. Using the getMetaData() method in this object, we can get some metadata about the query: the column name, column type, and table name of the column. This information is enough to generate the required class.

So, how can I run the code that contains SQL?

For fixed SQL statements, we take the fixed SQL, call JDBC and get MetaData, and we can easily generate Dtos from that information. However, for those complex SQL queries that need to be generated dynamically according to a series of parameters, they cannot run directly before the parameters are set, and they cannot get MetaData. Without MetaData, we cannot generate Dtos.

As discussed earlier, even with dynamic SQL, no matter what parameters are entered, the SQL statement may be executed differently, but the resulting result column is fixed. Isn’t that the problem we need to solve right now? In this case, let’s construct a set of default parameter values. These parameters are not really useful, just to keep the SQL code we are editing running to retrieve the MetaData we need, regardless of whether the data can be queried.

Typically, we write SQL code that exists in two forms: directly in Java code, or in configuration files. I won’t discuss which is better here, but I’ll find a separate place to discuss it later. The focus here is on concatenating SQL in Java code, and how to implement a code generator to generate these Dtos automatically:

To fully automate this problem, let’s take a look at some of the challenges this code generator faces and how it works:

  • How do I identify a piece of SQL code that needs to generate a DTO

First, we need to identify the code so that the code generator can run the DTO code that needs to be generated. Typically, our data interfaces are at the method level, so we can annotate a method. It’s a good idea to annotate a method that returns a DTO object.

  • How do I define the class name of a DTO

An easy way to do this is to automatically combine the name of the class in which the SQL code resides with the name of the method, although sometimes the programmer should be allowed to specify a name for flexibility.

  • How to execute code

The key to executing the code is to construct a set of appropriate parameters to call the annotated method. Of course, the first step is to do code analysis of the annotated method and extract the method parameter names and types. Code analysis can be done with a tool like JavaCC, or some parser, which I won’t go into here. The following focuses on the construction of default parameters:

To simplify things, by default we can construct as follows:

The default value is:, for example, public Object (arg){... } construct arg =; String arguments, default:, construct String arg=; Boolean arguments, default: false, constructor Boolean arg=false; Array arguments, default: type [], construct [] arg= []; Object type parameters. The default value is type (), for example, public Object (User arg){... } construct User arg= User();Copy the code

Of course, for some simple parameters, the above construction rules generally work. However, for some parameters, such as an interface, or a table name that needs to be dynamically joined, or SQL concatenation code logic that requires the parameters to be special values, the default constructed parameters will cause the program to fail to execute.

But how do we keep our code generator going? There really doesn’t seem to be any way to automate this, so let’s leave it to the programmer to help the code generator initialize the parameters.

We can provide a parameter on the annotation that does the work of setting parameters that cannot be initialized by default. Of course, the initialization code in this parameter can also override the default rules so that we can test executing different SQL processes at the edit stage.

  • How do I generate dtos

After all of the above processing, we are finally able to automatically run the method containing the SQL query code. However, we do not yet have the desired MetaData and cannot generate dtos.

One possible approach is to wrap a JDBC that intercepts the SQL query executed on this method call, but the problem is that it can be troublesome if there are multiple queries in the method.

The other approach relies on the framework’s support. You can intercept a method’s return statement and get the SQL statement that it executes. With SQL statements, generating dtos is easy.

  • How to modify code

To minimize programmer effort, our code generator also needs to automatically change the return value of the method to the DTO class after generating the DTO.

  • How do I handle SQL changes

The simple way to do this is to regenerate all dtos in the same way as before whenever any SQL code changes. However, it is clear that when there are many query methods, the DTO code generation process can be excruciatingly slow.

A more sensible alternative would be to add a fingerprint field to generate the DTO, whose value can be generated from the information contained in the SQL code, for example: code length + hashCode of the code. Before deciding whether to process the method, the code generator calculates the method’s fingerprint and compares it with the fingerprint in the DTO. If they are the same, it skips them. Otherwise, the method’s SQL has changed and the DTO needs to be updated.

Concrete implementation

At this point, basically all the major obstacles to DTO code generators have been addressed. Finally, let’s do a simple example with a concrete implementation.

Two projects need to be introduced here:

  • Monalisa – db: github.com/11039850/mo… [1]

This is a very simple ORM framework, through @db (jdbC_URL,username,password) annotation to introduce the database, but also to achieve some basic operations on the database.

  • Monalisa – eclipse: github.com/11039850/mo… [2]

This is a corresponding Eclipse plug-in that can:

  1. @db annotated interface that automatically generates CRUD operations for tables when files are saved

  2. The @select annotation method automatically generates dtos when the file is saved

  3. Easy to write multi-line strings

For plug-in installation and configuration, see github.com/11039850/mo…

The following is an example of automatic DTO generation based on dynamic SQL. The complete example project can be found at: github.com/11039850/mo… [3]

package test.dao; Public class UserBlogDao {// @select annotation indicates that the method should automatically generate DTO // default class name: Result + method name, default package name: package name of the data access class +"."+ Name of the data access class (lowercase) // Optional: name Specifies the name of the generated Result class. If this parameter is not specified, the default class name is used. Build initializes the Java snippet code for call-arguments, replacing the default parameter construction rule @select (name=" test.result.userblogs ") //!!! This function automatically modifies its return value to: List -> List -> List -> List -> List -> List List query //public DataTable method_name(...) {... return Query.getList(); } //public List method_name(...) {... return Query.getList(); } //public Page method_name(...) {... return Query.Page(); } //public Object method_name(...) {... return Query.getResult(); } public List selectUserBlogs( user_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); return q.getList(); }}Copy the code

Once the above code is saved, the plug-in automatically generates a DTO class, test.result.UserBlogs, and automatically modifies the methods to the following declaration:

        public List  selectUserBlogs( user_id){ 
            ...
            return q.getList(UserBlogs.class); 
        }Copy the code

Of course, if you make any changes to the selectUserBlogs method (including just adding a space), the plug-in will also automatically update UserBlogs after you save the file.

At the same time, for our debugging purposes, the plug-in will also output something like the following 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
Copy the code

By the way:

One of the most annoying things about writing SQL in Java code is the concatenation of strings in the Java language. This causes large chunks of SQL code to have too many line breaks/escapes inserted in the middle, which is cumbersome to write and uncomfortable to watch. The Monalisa-Eclipse plug-in incidentally also solves the problem of writing multi-line strings.

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

Will print:

  SELECT * 
        FROM user
        WHERE name="zzg"Copy the code

Of course, for quick writing, you can set the syntax for multi-line strings to a code template in Eclipse. More details on multi-line syntax can be found at github.com/11039850/mo… [4]

Here, dynamic SQL code automatic generation of DTO ideas and implementation examples are basically introduced, welcome to put forward all kinds of rational and unreasonable opinions, together to discuss, progress, thank you!