Shulou(Shulou.com)06/01 Report--

The current situation

Generally do database-related development, unless you learn, few people are willing to use JDBC directly. Originally, the Java code is quite verbose, but writing code directly in JDBC is a bit maddening! So in the actual development process, we usually use some frameworks / libraries to help us manipulate the database. And there are many choices in the open source market, as far as I am concerned: 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 on the whole is basically enough.

However, for slightly more complex data queries, it is inevitable to write SQL code by hand, and even need to dynamically splice SQL according to parameters. Basically, all kinds of frameworks have their own scheme for stitching dynamic SQL, and they can easily convert the queried data into objects (DTO).

But so far, although these frameworks can easily help us to complete the data mapping, but these DTO still need to be written by hand.

Existing problems

Usually after we write the query code of SQL, we need to have a corresponding DTO to map the data queried in the database to DTO, so that the calling program can make better use of the data. Of course, to save trouble, data is sometimes stored directly in data structures such as Map. However, Map's approach, while lightweight, poses several potential problems that are more important than:

Callers need to remember the name of each key in Map, which brings some so-called memory burden to programmers.

Excessive memory burden will lead to complex logic, difficult understanding and more difficult maintenance of the system.

After SQL changes lead to changes in Key, it is difficult to find problems, and programmers need to deal with these changes very carefully

If we want to avoid these problems caused by Map, we need to write a separate DTO for each SQL query. Although writing these DTO is not difficult, it is very tedious, especially when there are a lot of fields, and remember to come back and modify the DTO if the fields of the SQL query change. Writing DTO alone alleviates some of the problems caused by Map, but also adds new workload.

If there is a way to write SQL code (including dynamic splicing SQL), it is perfect to automatically do the following two points:

Generate the corresponding DTO directly according to the SQL code

Change the SQL code and automatically modify the corresponding DTO

In this way, on the one hand, it solves the trouble of writing DTO by hand; on the other hand, when modifying a SQL causes a field to change, because the automatically generated DTO will also be modified synchronously, the compiler will immediately give an error prompt where the field is referenced! As soon as the problem arises, it can be discovered immediately, which can avoid a lot of potential problems.

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

The way to solve the problem

The ideal is always beautiful, the reality is always cruel!

So, whether this idea can be realized, let's first take a preliminary analysis of the feasibility of automatically generating DTO:

To achieve automatic DTO generation, the core is to get each column name and its data type corresponding to the SQL query. With column names and data types, you can easily write a method to generate DTO.

Of course, there are rare cases where queries with uncertain fields are encountered, but in this extreme case, it is impossible to write DTO by hand. Instead, it is more appropriate to use Map, which we will not discuss here.

So, how can I get the column name and type?

One option is to analyze the fields in the SELECT section of the SQL code, but it is more limited:

It is difficult to analyze the spliced SQL code.

It is also difficult to determine the type of field.

Common query methods such as SELECT *; CALL statement are also difficult to analyze.

The above scheme, such as Mybatis, using configuration files (xml) to write SQL, seems to be feasible. I have not specifically tested it, but I estimate that there will be no less difficulties.

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

We know that JDBC executes a SQL query and returns a ResultSet object. Through the method getMetaData () in this object, we can get some metadata of this query: column name, column type, and the name of the table in which the column is located. This information is enough for us to generate the class we need.

So, how do you run the code that contains SQL?

It's a little easier for those fixed SQL statements, we get the fixed SQL, call JDBC to get the MetaData, and then we can easily generate the DTO based on this information. However, for those complex SQL queries that need to be generated dynamically according to a series of parameters, they cannot be run directly until the parameters are set, so we cannot get MetaData, and we cannot generate DTO without MetaData.

What shall I do?

As discussed earlier, even in a dynamic SQL, although the SQL statements executed may be different, the resulting columns are fixed no matter what parameters are entered. Isn't the problem we need to solve right now to get these columns of information? In that case, let's construct a series of default parameter values. These parameters are of no practical use, just to allow us to edit the SQL code to run properly so that we can get the MetaData we need, and it doesn't matter whether we can query the data or not.

Usually we write SQL code in two forms: one is directly in the Java code, and the other is placed in the configuration file. I will not discuss which form is better here. I will find a place to discuss it later. What is mainly discussed here is how to implement a code generator to automatically generate these DTO when stitching SQL in Java code:

To fully automate this problem, let's first take a look at some of the challenges faced by this code generator and how to deal with it:

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

First, we need to identify this code so that the code generator can run it and generate the DTO code. In general, our data interface is method-level, so we can annotate the method, using annotations to indicate that the method returns a DTO object is a good choice.

How to define the class name of DTO

One easy way to think of is to automatically combine a name from the class name + method name where the SQL code is located, although sometimes programmers should be allowed to specify a name for flexible control.

How to execute code

The key to executing the code is to construct a set of appropriate parameters that can invoke the annotation method. Of course, first of all, we need to analyze the code of the annotated method and extract the parameter name and type of the method. Code analysis can be done with tools like JavaCC, or some parsers, which are not examined here. The following mainly discusses the construction of the following default parameters:

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

Numeric parameter, default is: 0, for example: public Object find (int arg) {...} construct int arg=0

String parameter, default is: ", construct String arg="

Boolean parameter. Default is false. Construct boolean arg=false.

Array parameter. Default is: type [0], construct int [] arg=new int [0]

Object parameter, default is: new type (), for example: public Object find (User arg) {...} construct User arg=new User ()

Of course, for some simple parameters, the above construction rules basically work.

But how can our code generator continue to execute? It seems that there is really no way to deal with it automatically, so we have to leave the problem to the programmer to help the code generator initialize the parameters.

We can provide a parameter on the annotation, which mainly sets the 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 and execute different SQL processes at the editing stage.

How to generate DTO

After the above series of processing, we are finally able to automatically run the method that contains the SQL query code. However, we haven't got the MetaData we want yet, and we haven't been able to generate DTO yet.

One possible way is to wrap a JDBC to intercept the SQL query executed during this method call, but the problem is that it is troublesome if there are multiple queries in the method.

Another way depends on the support of the framework, you can intercept the return statement of the method and get the SQL statement it executes. With the SQL statement, it is not difficult to generate DTO.

How to modify the code

In order to minimize the programmer's work, our code generator needs to automatically modify the return value of the method to this DTO class after generating the DTO.

How to deal with changes to SQL

The simple thing to do is to regenerate all the DTO in the previous method as soon as some SQL code changes. However, it is clear that when there are many query methods, the process of DTO code generation will be unbearably slow.

Another more reasonable approach is to add a fingerprint field when we generate DTO, whose value can be generated by the information contained in the SQL code, such as code length + code hashCode. Before deciding whether to deal with this method, the code generator first calculates the fingerprint of the method and compares it with the fingerprint that exists in the DTO, and skips it if it is the same, otherwise it is considered that the SQL of the method has changed and the DTO needs to be updated.

Concrete implementation

So far, basically, the main obstacles to the DTO code generator have been addressed. Finally, we use a concrete implementation to do a simple example.

Two projects need to be introduced here:

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

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

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

This is a corresponding Eclipse plug-in that can:

@ DB annotation interface, which automatically generates CRUD operations of tables when files are saved

@ Select annotation method to automatically generate DTO when the file is saved

It is easy to write multi-line strings.

For more information on plug-in installation and settings, please see https://github.com/11039850/monalisa-db/wiki/Code-Generator

The following is an example of automatically generating DTO based on dynamic SQL. For a complete example project, please refer to: https://github.com/11039850/monalisa-example

Package test.dao

The public class UserBlogDao {/ / @ Select annotation indicates that this method needs to generate DTO automatically.

/ / default class name: Result + method name, default package name: package name of the data access class + "." + name of the data access class (lowercase)

/ / optional parameter: name specifies the name of the resulting class. If this parameter is not specified, the default class name is used.

/ / optional parameters: build initializes the Java snippet code of the calling parameters, replacing the default parameter construction rules

@ Select (name= "test.result.UserBlogs")

/ /! After saving, the return value of the function will be automatically modified to: List-> List

/ / when writing for the first time, because the result class does not exist yet, in order to ensure that it can be compiled properly

/ / the return value of the function and the query result should be replaced by a generic value, and the plug-in will automatically modify it after saving.

/ / the corresponding relationship between the returned value of the function and the generic value of the query result is divided into three categories:

/ / 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. idling?

} * /, user_id)

Return q.getList ()

}

}

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

Public List 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 plug-in will automatically update the UserBlogs after saving the file.

At the same time, to make it easier for us to debug, the plug-in also outputs information similar to the following in the console window of Eclipse:

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

Incidentally, I would like to add:

One of the most annoying things about writing SQL in Java code is string concatenation in the Java language. It makes a lot of newline / escape symbols inserted in the middle of a large piece of SQL code, which is troublesome to write and uncomfortable to look at. The monalisa-eclipse plug-in also solves the problem of writing multi-line strings by the way.

For example:

System.out.println ("" / * * ~ {

SELECT *

FROM user

WHERE name= "zzg"

} * /)

Will output:

SELECT *

FROM user

WHERE name= "zzg"

Of course, to write quickly, you can set the syntax of a multiline string to a code template in Eclipse. For more details on multiline syntax, please refer to https://github.com/11039850/monalisa-db/wiki/Multiple-line-syntax

At this point, the ideas and implementation examples of automatic generation of DTO in dynamic SQL code are basically introduced. Welcome to put forward all kinds of reasonable and unreasonable suggestions, discuss and make progress together, thank you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.