How To Auto-Generate DTOs

Background

Usually during database development, few people are willing to use JDBC unless they're learning. As we all know, Java code is verbose, even moreso with JDBC!

So we often use frameworks/libraries to access a database. There are many ORM frameworks available, for example: Hibernate, MyBatis, JdbcTemplate, DbUtils, ActiveRecord, JavaLite, etc.

Many times, the framework can help us avoid writing SQL, but for some complex queries, hand-coded SQL is a must. Although the framework can do the mapping from SQL's ResultSet to a DTO (Data Transfer Object), we have to write those DTOs by hand, one by one.

Problems

In general, we need to write SQL and the corresponding DTOs. Of course, in order to save time, a Map is sometimes used to store data. However, although a Map is a very lightweight object, it will bring a few more important problems:

• The caller needs to remember each of the keys inside the Map, which will bring some of the so-called memory burden.

• If the memory burden is too heavy, it will cause the system to have complex logic, be difficult to understand, be even more difficult to maintain.

• If the SQL is changed, the key may be changed, too. The programmer needs to handle these changes very carefully.

If you want to avoid these problems using a Map, we need to write DTOs for each SQL query. Writing these DTOs is boring; on the other hand, if the SQL query is changed, we must remember to come back to modify the DTOs, too.

Idea

All of those problems can be solved if there is a tool that automatically does the following:

• Use the SQL code to directly generate the corresponding DTO.

• When the SQL code is changed, automatically modify the corresponding DTO.

It saves the trouble of manually programming DTOs and synchronizes between SQL and DTO.

This article attempts to solve the problem of how to automatically generate DTOs with the SQL code and improve development efficiency.

Solution

There is hope, but the reality is cruel!

So, how can we create this tool? Let's first analyze the feasibility about this problem.

To generate these DTOs automatically, the key is to get each name and data type of the SQL result set. If that is done, it will be very easy to generate these DTOs.

As we all know, once the SQL finished, maybe the final run of the SQL statement is different, but the names and data types of the SQL result set are relatively fixed.

In a very few cases, these names and data types of the SQL results set are not fixed. In this case, a Map maybe is more suitable, but there is no need to discuss that here.

So, how do we get these column names and data types?

One solution is to analyze the SQL code and find which columns are between "SELECT" and "FROM". However, there are some important difficulties:

• For parameterized dynamic SQL, this will be difficult to analyze.

• It is difficult to determine the column's data type.

• "SELECT * ..."; "CALL ..."; are very difficult to analyze.

Like Mybatis, SQL queries written in configuration files, the above solution is somewhat feasible. I don't have a specific study, but I think there will be a lot of problems to be solved.

Another solution is to find a way to run the SQL code directly.

By running the SQL code, we can intercept the original SQL code. With the original SQL code, it is easy to get each name and data type, like this:

ResultSet rs=statement.executeQuery("The original SQL code");
ResultSetMetaData rsmd = rs.getMetaData();
int cc = rsmd.getColumnCount();
for (int i = 1; i <= cc; i++) {
    int    type = rsmd.getColumnType(i);
    String name = rsmd.getColumnName(i);
    //...
}
//...

In general, the SQL query interface is a method with some parameters. To run the method directly, it is necessary to automatically initialize these parameters.

To automate the solution to this problem, let's take a look at how this tool will face some of the challenges and their corresponding solutions.

How to Define a Section of SQL Code

First, we need to identify this code so that the code generator can run the code. Normally, our data interface is at the method level, so we can annotate on the method.

How to Define the Class Name of a DTO

A simple way is to use the combined class name and method name to make a new name. Sometimes, to be flexible, you should be allowed to specify a name.

How to Run the SQL Code

To execute code, the key is to construct the appropriate parameters of the method. First, we need to analyze the code of this method to extract the parameter name and type. A code parser can use a tool such as JavaCC or some syntax analyzer.

Let's discuss how to construct these parameters. In order to simplify the problem, we will be building according to the following rules by default:

Numeric parameter, default value: 0,     e.g: int     arg = 0;
String  parameter, default value: "",    e.g: String  arg = "";
Boolean parameter, default value: false, e.g: Boolean arg = false;
Array   parameter, default value: [0],   e.g: int[]   arg = new int[0];
Object  parameter, default value: new(), e.g: User    arg = new User();
...

In the case of some simple parameters, the above structural rules are basically able to work. However, for some special parameters, such as if a parameter is an interface, or some special values to run SQL, etc., the constructed parameters according to the above rules will throw a wrench into the program.

This is a problem, but we can provide a parameter on the annotation — the parameter to help the code generator complete the parameter initialization.

How to Generate the DTO Class

After the previous processing, we can finally run the method. But we haven't got the DTO class that we want yet.

One possible way is to wrap the JDBC, when running the method, to intercept the SQL, but the problem is that if the method has multiple queries, it will cause problems.

Another way depends on the framework's support. We could intercept the method's return statement to get the SQL statement. With the SQL statement, it is not difficult to generate the DTO class.

How to Modify the Called Method

In order to minimize the work of developers, after the DTO class generated, we will also need to modify the method's return value as the corresponding DTO class.

Generally, the returned DTO object has 3 types of manifestation:

Single object
List collection
Collection of pages

Because the method has to be run before generating DTO, the return value of the method should be denoted by common types. Besides, return value can be anyone of the above manifestations. Therefore, for the purpose of realizing automatic modification of return value of method, a simple agreement has to be made:

Object represents that return value is a single DTO object.
List represents that return value is a list collection.
Page represents that return value is a collection of pages.

In this case, tools can automatically make the following modifications according to the agreed return types(supposing that the generated type name of DTO is UserBlogs) :

Object will be modified as UserBlogs.
List will be modified as List<UserBlogs>.
Page will be modified as Page<UserBlogs>.

How to Deal With Changes in SQL

The simple way is: Once the code has changed in that class, all of the DTO classes are regenerated. However, it is clear that when there are a lot of query methods, the DTO code generation process will be very slow.

There's also another suitable way: adding a fingerprint field in DTO class — its value, maybe length of the code and hashcode(or the MD5 value of code). First, calculate the method of the fingerprint and compare to the existing method's fingerprint. If it's the same, move on. Otherwise, the program considers the method to have changed and the DTO class needs an update.

Implementation

Finally, we use a specific implementation as an example. It needs to introduce two projects :

1. Monalisa-orm: This is a simple ORM framework. It introduces the database using an annotation: @DB(jdbc_url, username, password).

2. Monalisa-eclipse: This is an eclipse plugin. It can:

• Interface with annotation @DB, and when the interface is saved, it automatically generates model classes.

• Method with annotation @Select, and when the class is saved, it automatically creates or updates the DTO.

• Easily write multi-line strings.

Here are the instructions for installation and setup. 

Here is an example of how to automatically generated DTOs. The full example is here.

package test.dao;

    public class UserBlogDao {

        // @Select indicating that the method will generate a DTO
        // Optional parameter: name 
        //                     specifies the name of the class generated DTO, 
        //                     if not specified, using the default: "Result" + the method's name
        // Optional parameter: build
        //                     a Java snippet for initializing the method parameters, 
        //                     replace the default initialization rule
        @Select (name = "test.dao.userblogdao.UserBlogs")

        // !!! After saving, the plugin will automatically modify the return value: List -> List <UserBlogs>
        //
        // The first time, DTO class does not exist. In order to compile the code correctly,
        // the return value and the result of the query must be replaced by a generic value. 
        // If saved, the plugin will automatically modify the results to the corresponding values.
        //
        // Here is the corresponding relationship between the return value and the results of the query:
        // 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 ();}
        //
        public List  selectUserBlogs(int 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(); 
        } 
    }

Saved with the above code, the plugin will generate the DTO automatically: test.dao.userblogdao.UserBlogs as follows:

/**
 * Auto generated code by monalisa 1.7.0
 *
 * @see test.dao.UserBlogDao#selectUserBlogs(int)
 */
public class UserBlogs implements java.io.Serializable{  
  /**
  * Some comments from database ...
  */
  @Column(table=User.M.TABLE, jdbcType=4, name=User.M.id$name, ...)
  private Integer id;

  //Other fields and get/set methods ...
}

Method declaration:

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

}

Of course, if the method changes (even if it is just a whitespace), after the file saved, UserBlogs will be updated automatically.

In order to keep debugging easy, the following message will output in an Eclipse console named monalisa:

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

Additionally...

When writing SQL in Java, multi-line strings can be troublesome. A large segment of the SQL code with many new lines and escape symbols looked uncomfortable. Monalisa-eclipse plugin also solves the problem of writing multi-line strings.    

E.g:

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

Output:

SELECT *
FROM user
WHERE name = "zzg"

Of course, in order to write multi-line string quickly, you can set up Java editor templates in Eclipse. For more details about multi-line syntax, please refer here.

So much for that, "How To Auto Generate DTO According SQL Code" is introduced. Welcome to discuss with me, thank you!