A visual code generation method and system based on SQL statements

Abstract

The present invention discloses a visual code generation method based on SQL statements, comprising the following steps: Step 1: performing SQL parsing to translate the original SQL and dynamic parameters into Mybatis's DSL; Step 2: generating a DTO object corresponding to the SQL result set using the result set metadata of the SQL execution; Step 3: inserting the pre-generated code into a designated source file in a development engineering environment or generating a new source file based on Java's AST syntax tree analysis and environment perception capabilities. The visual code generation method based on SQL statements of the present invention utilizes the mature Mybatis syntax to automatically parse the dynamic parameters defined in the SQL and convert them into Mybatis's DSL syntax, thereby achieving dynamic conditional control of the generated code.

Description

Technical Field

[0001] The present invention relates to a code generation method, and more particularly to a visual SQL statement-based code generation method. Background Art

[0002] Modern web application development typically relies on stable, proven, and universal frameworks to improve development efficiency. For example, Java-based web applications often choose the Spring Boot + Mybatis framework. Application functional interfaces developed with these universal frameworks often contain a large amount of boilerplate code. Often, two interfaces are almost identical, except for different query conditions or parameters. To overcome the extra repetitive work caused by boilerplate code, developers often use code generation tools to generate repetitive boilerplate code based on configuration, improving efficiency. However, traditional code generation tools or frameworks typically generate logic interactions based on hardcoding or command lines, lacking a visual interface. Furthermore, the generated code lacks flexibility and is typically limited to generating standard CRUD (Create, Delete, Modify, and Query) logic for a single database table, such as the official Mybatis Generator. This approach is inadequate for complex query interfaces that require multi-table joins, forcing developers to customize SQL queries and then tailor the relevant business implementation code. These types of query interfaces are typically much more common in typical projects than simple single-table queries.

[0003] As mentioned above, existing code generation technologies can generally only generate simple CRUD functional code based on data tables. They cannot meet the code generation requirements for complex and flexible business queries (based on SQL). Furthermore, they lack a visual interaction mode, which reduces the usability of code generation. Another common technology for implementing business interfaces based on SQL simulation generally encapsulates SQL at the framework level and implements unified interception without generating additional static, editable source code files. However, this makes it difficult to expand business logic. For example, for some logic that requires additional SQL and code processing to implement, the only option is to write the implementation logic from scratch, without being able to reuse the framework's capabilities. Summary of the Invention

[0004] In response to the shortcomings of the existing technology, the purpose of the present invention is to provide a visual web console that allows developers to generate a series of complete business implementation codes based on the interactive mode of the web page, simply by configuring relevant SQL statements and dynamic parameters, and directly provide them to the upper-level client for use.

[0005] To achieve the above object, the present invention provides the following technical solution: a visual code generation method based on SQL statements, comprising the following steps:

[0006] Step 1: Parse SQL and translate the original SQL and dynamic parameters into Mybatis DSL;

[0007] Step 2: Generate the DTO object corresponding to the result set of SQL through the result set metadata of SQL execution;

[0008] Step 3: Based on Java's AST syntax tree analysis and environment awareness capabilities, the pre-generated code is inserted into the specified source file in the development project environment or a new source file is generated.

[0009] As a further improvement of the present invention, the specific steps of performing SQL parsing in step 1 are as follows:

[0010] Step 1: Enter the SQL and parameters related to the project configuration;

[0011] In steps 1 and 2, select the SQL mapping file class in the project, parse and enhance the SQL, output the DSL, replace dynamic parameters, execute, and obtain SQL metadata.

[0012] As a further improvement of the present invention, the specific steps of inserting the pre-generated code into the specified source file in the development engineering environment or generating a new source file based on Java's AST syntax tree analysis and environment perception capabilities are as follows:

[0013] Step 31: Analyze the AST of the SQL mapping file class selected in steps 1 and 2;

[0014] Step 32: Insert a new method into the mapping class source code and determine the input and output parameters of the method;

[0015] Step 33: Based on the environment perception capability, determine the location of the upper-level code file;

[0016] Step 3 and 4: Determine whether the upper-level code file exists. If so, create the relevant dependency file. If not, insert a new method based on AST parsing.

[0017] Step 35: Complete code generation.

[0018] Another aspect of the present invention provides a system comprising:

[0019] Interaction layer, used for human-computer interaction with users;

[0020] Code generation engine, used to parse SQL and generate code;

[0021] Configure the persistence layer, which is used to configure templates and environment information.

[0022] As a further improvement of the system, the interaction layer includes:

[0023] The visual UI module is used to display the UI on an external display for users to view.

[0024] As a further improvement of the system, the code generation engine includes:

[0025] SQL parser, used to parse SQL;

[0026] Code environment perception module, used to perceive the code environment;

[0027] Code syntax parser, used to parse code syntax.

[0028] As a further improvement of the system, the configuration persistence layer includes:

[0029] Configuration template module, used to configure templates;

[0030] Environmental information module, used to collect engineering environmental information.

[0031] The beneficial effect of the present invention is that, through the settings of steps one to three, the directory location, hierarchical structure, database configuration and other information of the host project can be automatically sensed. Developers only need to configure some key information to generate the necessary code, thus avoiding a lot of repetitive configuration work. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] Figure 1 A flowchart of a visual SQL statement-based code generation method of the present invention;

[0033] Figure 2 Figure 2 is a system module block diagram of the application method. DETAILED DESCRIPTION

[0034] The present invention will be further described below with reference to the embodiments shown in the accompanying drawings.

[0035] Reference Figures 1 to 2 As shown, a visual code generation method based on SQL statements in this embodiment includes the following steps:

[0036] Step 1: Parse SQL and translate the original SQL and dynamic parameters into Mybatis DSL;

[0037] Step 2: Generate the DTO object corresponding to the result set of SQL through the result set metadata of SQL execution;

[0038] In step three, based on Java's AST syntax tree analysis and environment awareness capabilities, the pre-generated code is inserted into the specified source file in the development project environment or a new source file is generated. By setting steps one to three, SQL parsing can be effectively implemented, and then code is automatically generated based on the results of SQL parsing and execution, avoiding a lot of repetitive configuration work.

[0039] As a specific implementation of the improvement, the specific steps of performing SQL parsing in step 1 are as follows:

[0040] Step 1: Enter the SQL and parameters related to the project configuration;

[0041] In steps one and two, select the SQL mapping file class in the project, parse and enhance the SQL, output DSL, replace dynamic parameters and execute, and obtain SQL metadata. By setting the above two steps, you can effectively implement SQL parsing and generate DTO objects after parsing.

[0042] As a specific implementation of the improvement, the specific steps of inserting the pre-generated code into the specified source file in the development project environment or generating a new source file based on Java's AST syntax tree analysis and environment perception capabilities are as follows:

[0043] Step 31: Analyze the AST of the SQL mapping file class selected in steps 1 and 2;

[0044] Step 32: Insert a new method into the mapping class source code and determine the input and output parameters of the method;

[0045] Step 33: Based on the environment perception capability, determine the location of the upper-level code file;

[0046] Step 3 and 4: Determine whether the upper-level code file exists. If so, create the relevant dependency file. If not, insert a new method based on AST parsing.

[0047] Steps 3 and 5 complete code generation. By setting the above steps, you can effectively generate new code and then insert it into the file. If the relevant code file already exists, the code will be automatically merged based on syntax analysis, otherwise a new code file will be created.

[0048] Another aspect of this embodiment provides a system equipped with the above method, including:

[0049] The interactive layer is used for human-computer interaction with the user, and the interactive layer includes: a visual UI module, which is used to display the UI on an external display for the user to view;

[0050] A code generation engine is used to parse SQL and generate code. The code generation engine includes: an SQL parser for parsing SQL; a code environment perception module for perceiving the code environment; and a code syntax parser for parsing code syntax.

[0051] The configuration persistence layer is used to configure templates and environment information. The configuration persistence layer includes: a configuration template module for configuring templates; an environment information module for collecting project environment information; the entire system is embedded in the development project in the form of a plug-in and can be started independently. The system has the corresponding environment perception capabilities and can automatically perceive the directory location, hierarchical structure, database configuration and other information of the host project. Developers only need to configure some key information to generate the necessary code, avoiding a lot of repetitive configuration work. For example, if an interface needs to be generated based on SQL, only the SQL information and the path of the SQL mapping file need to be provided to automatically deduce the relevant information and path of the Service and Controller files, and automatically generate the corresponding interface and method according to the corresponding specifications. If the relevant code file already exists, the code is automatically merged based on syntax analysis, otherwise a new code file is created.

[0052] The dynamic parameter control of SQL in the present invention is based on the mature Mybatis syntax. The system automatically parses the dynamic parameters defined in SQL and converts them into Mybatis DSL syntax, thereby realizing dynamic conditional control of generated code. For example: sql statement

[0053]

[0054] After being analyzed and enhanced

[0055]

[0056] In summary, the method and system of this embodiment generate ready-to-use multi-layer business logic code based on standard SQL statements in a visual interactive mode, and support incremental modification of existing source code files or creation of new source code files.

[0057] The above description is merely a preferred embodiment of the present invention. The scope of protection of the present invention is not limited to the above embodiment. All technical solutions based on the concept of the present invention are within the scope of protection of the present invention. It should be noted that for those skilled in the art, various improvements and modifications that do not depart from the principles of the present invention should also be considered within the scope of protection of the present invention.

Claims

1. A visual SQL statement-based code generation method that allows developers to perform SQL parsing based on the interactive mode of the web page by configuring relevant SQL statements and dynamic parameters. Based on the SQL parsing and execution results, a series of complete business implementation codes are automatically generated and directly provided to the upper-level client for use. Features: The steps include: Step 1: Parse SQL and translate the original SQL and dynamic parameters into Mybatis DSL. The specific steps are as follows: Step 1: Enter the SQL and parameters related to the project configuration; Step 1: Select the mapping file class of the SQL in the project, parse and enhance the SQL, output the DSL, replace the dynamic parameters and execute it, obtain the SQL metadata, and thus implement SQL parsing and generate DTO objects; Step 2: Generate the DTO object corresponding to the result set of SQL through the result set metadata of SQL execution; Step 3: Based on Java's AST syntax tree analysis and environment perception capabilities, insert the pre-generated code into the specified source file in the development project environment or generate a new source file. The specific steps are as follows: Step 31: Analyze the AST of the SQL mapping file class selected in Steps 1 and 2; Step 32: Insert a new method into the mapping class source code and determine the input and output parameters of the method; Step 33: Based on environment perception capabilities, determine the location of the upper-level code file; Step 34: Determine whether the upper-level code file exists. If so, create related dependency files and automatically merge the code based on syntax analysis. If not, insert the new method based on AST parsing; Step 35: Complete code generation.

2. A system using the SQL statement-based code generation method according to claim 1, characterized in that: include: Interaction layer, used for human-computer interaction with users; Code generation engine, used to parse SQL and generate code; Configure the persistence layer, which is used to configure templates and environment information.

3. The system according to claim 2, characterized in that: The interaction layer includes: The visual UI module is used to display the UI on an external display for users to view.

4. The system according to claim 3, wherein: The code generation engine includes: SQL parser, used to parse SQL; Code environment perception module, used to perceive the code environment; Code syntax parser, used to parse code syntax.

5. The system according to claim 4, characterized in that: The configuration persistence layer includes: Configuration template module, used to configure templates; Environmental information module, used to collect engineering environmental information.

Images