Database and software code management method based on annotation and automatic execution mechanism

Abstract

The invention discloses a database and software code management method based on annotation and an automatic execution mechanism. The database and software code management method comprises an annotation definition module and an annotation scanning module. Comprising the following steps: S1, a user assigns a table name to an annotation definition module, and sets corresponding construction information according to an annotation; s2, when a program is started, detecting whether a corresponding table exists in a database or not through an annotation scanning module; s3, if the annotation does not exist, creating a corresponding table through the construction information in the annotation by an annotation definition module; and S4, when the annotation scanning module detects that the information in the annotation is inconsistent with the structure of the table, changing the structure of the table according to the construction information in the annotation.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a database and software code management method based on annotation and automatic execution mechanism. Background Art

[0002] In the process of software development, the interaction between database operations and software code always plays a vital role. With the vigorous development of information technology, the complexity and scale of software projects continue to rise, and the functions of database management systems (DBMS) are becoming increasingly powerful. For example, mainstream database management systems such as MySQL and Oracle provide a wealth of structured query language (SQL) operation functions.

[0003] In the traditional software development model, when it is necessary to map the business logic entities in the software to the database, developers must manually write a large number of SQL statements to build and manage the database table structure. For example, in the Java development scenario, developers need to carefully write table creation statements (such as "CREATE TABLE..." statements) based on the business model, and set key information such as table name, field name, field type, primary key, etc. in detail. This process requires developers to have a deep understanding of database syntax and proficient application capabilities, and frequently switch between code writing and SQL statement writing to ensure that the database structure accurately reflects business needs. At the same time, when the software version is updated, the corresponding adjustment of the database structure also mostly relies on manual operations, which undoubtedly increases the complexity of software development and the risk of errors.

[0004] In the existing database and software code interaction solutions, database connection technologies such as JDBC (Java Database Connectivity) are widely used. Developers connect to the database in Java programs through JDBC and manually write SQL statements in the project's initialization script or version upgrade script. In addition, some code generation tools can reversely generate entity classes in the code based on the database metadata. However, when the business logic changes and the database table structure needs to be positively changed, developers still need to manually write SQL statements.

[0005] In terms of database version management, the existing operation method is usually that developers manually execute relevant SQL files according to the needs of software version updates at specific project deployment stages or version update times. There is a lack of an automated mechanism to ensure the coordinated update of database structure and software versions.

[0006] The prior art has the following disadvantages: Inefficiency: Manually writing SQL statements for table creation and table structure changes is a time-consuming and error-prone task. Developers need to perform complex conversions between business logic and database syntax, which becomes even more cumbersome when dealing with complex business models and frequent demand changes.

[0007] Difficulty in maintenance: When the business model changes, developers need to make modifications in multiple places (code and SQL scripts) and ensure the consistency of the two. This increases the difficulty of maintenance and easily leads to inconsistencies between the code and the database structure, resulting in problems with data storage and query.

[0008] Lack of automation: Existing technologies lack an automated mechanism to directly generate and manage database table structures and related SQL statements based on the business model in the code, and cannot achieve seamless integration from code to database, resulting in a large number of repetitive manual operations during the development process.

[0009] In response to many problems in the prior art such as low efficiency, high maintenance difficulty and lack of automation mechanism in manually writing database-related SQL statements by developers, this application proposes an innovative technical solution, aiming to build an automated management system that can closely connect the database and the business model in the software code, so as to reduce the workload of developers, improve development efficiency, reduce the risk of errors, and ensure that the code and database structure remain consistent throughout the entire life cycle of the software. Summary of the invention

[0010] The purpose of the present invention is to provide a database and software code management method based on annotation and automatic execution mechanism to overcome the deficiencies in the prior art.

[0011] To achieve the above object, the present invention provides the following technical solutions: The present application discloses a database and software code management method based on annotation and automatic execution mechanism, including an annotation definition module and an annotation scanning module; and includes the following steps: S1: The user specifies a table name to the annotation definition module and sets the corresponding construction information according to the annotation; S2: When the program starts, the annotation scanning module detects whether there is a corresponding table in the database; S3: If it is detected that it does not exist, the annotation definition module creates the corresponding table according to the construction information in the annotation; S4: When the annotation scanning module detects that the information in the annotation is inconsistent with the structure of the table, the table structure is changed according to the construction information in the annotation.

[0012] Preferably, the construction information includes field type, comment information, and field options.

[0013] Preferably, the annotations include table name annotations, field annotations, and primary key annotations. The table name annotations are used to specify the name of the table and the corresponding table comments; the field annotations are used to specify the type, options, and corresponding field comments of the field; and the primary key annotations are used to define the specified field as the primary key of the table.

[0014] Preferably, an initialization module is included, and the initialization module reads all initialization structured query language files when the program is newly installed, and executes them in sequence to establish an execution environment.

[0015] Preferably, all initialization structured query language files are stored in an initial folder. When the program is installed, the initialization module reads the initial folder, establishes a database connection based on the initialization structured query language files in the initial folder, and executes all statements in sequence to initialize permission information.

[0016] Preferably, a version upgrade module is included, which executes unexecuted incremental structured query language files and unexecuted framework script files during version upgrade to achieve correct update and correction of database structure and data.

[0017] Preferably, all incremental structured query language files are stored in an incremental folder. When the program version is upgraded, the version upgrade module reads the incremental folder and sequentially executes the incremental structured query language files that have not been executed in the incremental folder to update the table structure and correct the data. All framework scripts are stored in the script folder. When the program version is upgraded, the version upgrade module reads the script folder and executes the framework scripts in the script folder that have not been executed in sequence.

[0018] Preferably, the version upgrade module includes an execution record table. After the version upgrade module executes an incremental structured query language file or framework script, it is marked as executed in the execution record table. During the next version upgrade, this incremental structured query language file or framework script will be skipped.

[0019] Preferably, an automatic annotation generation module is included, which parses comments in the code when compiling a programming project, and automatically generates annotations according to preset rules for constructing and changing tables in the database.

[0020] The present application also discloses a database and software code management device based on annotation and automatic execution mechanism, including a memory and one or more processors, wherein the memory stores executable code, and when the one or more processors execute the executable code, they are used to implement the above-mentioned database and software code management method based on annotation and automatic execution mechanism.

[0021] The present application also discloses a computer-readable storage medium on which a program is stored. When the program is executed by a processor, the above-mentioned database and software code management method based on annotation and automatic execution mechanism is implemented.

[0022] Beneficial effects of the present invention: (1) By automatically generating SQL statements related to the table structure through annotations and automatically executing initialization and incremental SQL files, the workload of developers in manually writing and managing SQL is reduced; developers do not need to frequently switch between code and SQL and can focus on the implementation of business logic, thereby improving overall development efficiency. This directly corresponds to the problem of low efficiency of manually writing SQL in the existing technology; (2) Since the annotations in the code are closely related to the database table structure, when the business model changes, you only need to modify the relevant annotations or business logic in the code, and the annotation scanning module will automatically update the database table structure according to the changes; at the same time, the execution record and version management mechanism of the SQL file also ensures the correct upgrade of the database between different versions, reducing the risk of errors in the maintenance process and solving the maintenance difficulties in the existing technology; (3) This solution realizes an automated process from the business model in the code to the database table structure management and SQL execution. Whether it is the creation and modification of the table structure, or the execution of the SQL file and data correction, it does not require a large amount of manual intervention, which improves the automation level of software development and database interaction and overcomes the shortcomings of the existing technology that lacks an automation mechanism.

[0023] The features and advantages of the present invention will be described in detail through embodiments in conjunction with the accompanying drawings. BRIEF DESCRIPTION OF THE DRAWINGS

[0024] Figure 1 It is a flowchart of the steps of the database and software code management method based on annotation and automatic execution mechanism of the present invention; Figure 2 It is a schematic diagram of the device of the present invention. DETAILED DESCRIPTION

[0025] In order to make the purpose, technical scheme and advantages of the present invention clearer, the present invention is further described in detail below through the accompanying drawings and embodiments. However, it should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the scope of the present invention. In addition, in the following description, the description of known structures and technologies is omitted to avoid unnecessary confusion of the concept of the present invention.

[0026] See also Figure 1The embodiment of the present invention provides a database and software code management method based on annotation and automatic execution mechanism, including the following parts: 1. Database table structure management based on annotations 1. Annotation definition and function This application carefully defines a set of Java annotations to accurately describe the database table structure.

[0027] These include the table name annotation @GenTableName, the field annotation @GenTableField, and the primary key annotation @GenPrimaryKey; Among them, the @GenTableName annotation is designed to be used at the class level; For example, @GenTableName(name="customer_order",comment="Customer Order Table"), its function is to automatically detect whether there is a table with the specified name in the database when the program starts.

[0028] If it does not exist, the system will create the corresponding table based on the information in the annotation and set the corresponding table comment.

[0029] For the fields in the class, we define the @GenTableField annotation; For example, @GenTableField(fieldType="varchar(50)",comment="Order Number"). This annotation can be converted into the field name in the database according to the naming rules of the field in the class, and set the field type and comment information.

[0030] The @GenPrimaryKey annotation acts on a field and is used to define the corresponding field as the primary key of the table.

[0031] 2. Annotation scanning and table structure operations During the program startup process, we designed a special annotation scanning module. This module will traverse the classes and fields in the Java code to find elements with the above annotations. For the @GenTableName annotation, the scanning module will connect to the database to query whether the corresponding table exists. If it does not exist, a "CREATETABLE" statement is constructed based on the table name, comments and other information in the annotation. For example, based on the above @GenTableName(name="customer_order", comment="Customer Order Table") annotation, the following table creation statement may be constructed: CREATE TABLE `customer_order` ( `id` int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`), `order_number` varchar(50) NOT NULL COMMENT 'Order number' ) COMMENT = 'Customer Order Table'; When the information in the annotation is inconsistent with the table structure in the database (such as field type changes, new fields, etc.), the scanning module will construct the corresponding "ALTER TABLE" statement to update the table structure based on the changes.

[0032] 2. SQL execution and version upgrade data correction management 1.SQL file organization and execution logic (1) The SQL file includes an initialization structured query language file (initialization SQL file) and an incremental structured query language file (SQL file); The initialization SQL file is stored in the init initial folder of the project, and the incremental SQL file of each version (including SQL statements for data correction) is stored in the incr incremental folder. In addition, the Shell framework scripts related to version upgrades are also stored in specific folders (such as the shell_scripts folder).

[0033] When the program is newly installed, a special initialization module will read all SQL files in the init folder and execute them one by one. During the execution process, the execution environment is established through the database connection, and each statement is executed according to the SQL syntax rules.

[0034] During version upgrade, the version upgrade module will query a table in the database that is specifically used to record the executed SQL files and data revision status (for example, the "executed_sql_and_revision" table).

[0035] For the SQL files in the incr folder, compare the execution record table and execute the SQL files that have not been executed in order. These SQL files may contain simple table structure changes, data corrections (such as updating data values ​​under specific conditions), and other operations. For example, a SQL file may contain the following statement: --This is a data correction SQL in a version upgrade, which increases the price of products that meet the conditions by 10% UPDATE `product_info` SET `price` = `price` * 1.1 WHERE `category` ='electronics'; (2) For the Shell framework scripts in the shell_scripts folder, the version upgrade module will also check the execution records (a new field can be added to the "executed_sql_and_revision" table to record the execution status of the Shell script). If it is not executed, the Shell script will be executed when the prerequisites are met (such as the database connection is normal, the relevant dependencies are ready, etc.). For example, a Shell script may be used for data migration operations: curl -X GET "http: / / 127.0.0.1:29090 / rest / v2 / unsecure / migrateReportFormItemAttrs"-H "accept: * / *" During the execution of SQL files and Shell scripts, the module will update the execution records in the "executed_sql_and_revision" table according to the execution results, ensuring that each operation is executed only once and the execution status is traceable, thereby ensuring that the database structure and data are correctly updated and corrected during the version upgrade process.

[0036] 3. Automatic Annotation Generation Plug-in How the plugin works A Java Maven plugin has been developed. When compiling a Java project, the plugin will parse the comments in the Java code.

[0037] For example, for the business description annotation / **Data warehouse backup snapshot* / of the class, the plug-in will add the @GenTableName(name = "cube_backup_snapshot", comment = "Cube backup snapshot") annotation to the class according to certain mapping rules. For field annotations, @GenTableField and other annotations are also generated according to the rules, thus reducing the workload of developers in writing annotations.

[0038] The embodiments of the database and software code management device based on the annotation and automatic execution mechanism of the present invention can be applied to any device with data processing capabilities, and the device with data processing capabilities can be a device or apparatus such as a computer. The device embodiment can be implemented through software, or through hardware or a combination of software and hardware. Taking software implementation as an example, as a device in a logical sense, it is formed by the processor of any device with data processing capabilities in which it is located reading the corresponding computer program instructions in the non-volatile memory into the internal memory for execution. From the hardware level, if Figure 2 As shown, it is a hardware structure diagram of any device with data processing capability where the database and software code management device based on annotation and automatic execution mechanism of the present invention is located, except Figure 2 In addition to the processor, memory, network interface, and non-volatile memory shown, any device with data processing capabilities in the embodiment of the device may also include other hardware according to the actual function of the device with data processing capabilities, which will not be described in detail. The implementation process of the functions and effects of each unit in the above device is specifically detailed in the implementation process of the corresponding steps in the above method, which will not be described in detail here.

[0039] For the device embodiment, since it basically corresponds to the method embodiment, the relevant parts can refer to the partial description of the method embodiment. The device embodiment described above is only schematic, wherein the units described as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the scheme of the present invention. Ordinary technicians in this field can understand and implement it without paying creative work.

[0040] An embodiment of the present invention further provides a computer-readable storage medium on which a program is stored. When the program is executed by a processor, the database and software code management device based on annotation and automatic execution mechanism in the above embodiment is implemented.

[0041] The computer-readable storage medium may be an internal storage unit of any device with data processing capability described in any of the aforementioned embodiments, such as a hard disk or a memory. The computer-readable storage medium may also be an external storage device of any device with data processing capability, such as a plug-in hard disk, a smart memory card (Smart Media Card, SMC), an SD card, a flash card (Flash Card), etc. equipped on the device. Furthermore, the computer-readable storage medium may also include both an internal storage unit and an external storage device of any device with data processing capability. The computer-readable storage medium is used to store the computer program and other programs and data required by any device with data processing capability, and may also be used to temporarily store data that has been output or is to be output.

[0042] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modification, equivalent substitution or improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A database and software code management method based on annotation and automatic execution mechanism, characterized in that: It includes annotation definition module and annotation scanning module; and includes the following steps: S1: The user specifies a table name to the annotation definition module and sets the corresponding construction information according to the annotation; S2: When the program starts, the annotation scanning module detects whether there is a corresponding table in the database; S3: If it is detected that it does not exist, the annotation definition module creates the corresponding table according to the construction information in the annotation; S4: When the annotation scanning module detects that the information in the annotation is inconsistent with the structure of the table, the table structure is changed according to the construction information in the annotation.

2. The method for managing database and software code based on annotation and automatic execution mechanism as claimed in claim 1, characterized in that: The construction information includes field type, comment information, and field options.

3. The method for managing database and software code based on annotation and automatic execution mechanism as claimed in claim 1, characterized in that: The annotations include table name annotations, field annotations, and primary key annotations. The table name annotations are used to specify the name of the table and the corresponding table comments; the field annotations are used to specify the type, options, and corresponding field comments of the field; and the primary key annotations are used to define the specified field as the primary key of the table.

4. The method for managing database and software code based on annotation and automatic execution mechanism as claimed in claim 1, characterized in that: It includes an initialization module, which reads all initialization structured query language files when the program is newly installed, and executes them in sequence to establish an execution environment.

5. The method for managing database and software code based on annotation and automatic execution mechanism as claimed in claim 4, characterized in that: All initialization structured query language files are stored in the initial folder. When the program is installed, the initialization module reads the initial folder, establishes a database connection based on the initialization structured query language files in the initial folder, and executes all statements in sequence to initialize the permission information.

6. The method for managing database and software code based on annotation and automatic execution mechanism as claimed in claim 1, characterized in that: It includes a version upgrade module, which executes the incremental structured query language files that have not been executed and the framework script files that have not been executed during the version upgrade to achieve correct update and correction of database structure and data.

7. The method for managing database and software code based on annotation and automatic execution mechanism as claimed in claim 6, characterized in that: All incremental structured query language files are stored in the incremental folder. When the program version is upgraded, the version upgrade module reads the incremental folder and executes the incremental structured query language files that have not been executed in the incremental folder in turn to update the table structure and correct the data. All framework scripts are stored in the script folder. When the program version is upgraded, the version upgrade module reads the script folder and executes the framework scripts in the script folder that have not been executed in sequence.

8. The method for managing database and software code based on annotation and automatic execution mechanism as claimed in claim 6, characterized in that: The version upgrade module includes an execution record table. After the version upgrade module executes an incremental structured query language file or framework script, it is marked as executed in the execution record table. During the next version upgrade, this incremental structured query language file or framework script will be skipped.

9. The method for managing database and software code based on annotation and automatic execution mechanism as claimed in claim 1, characterized in that: It includes an automatic annotation generation module, which parses the comments in the code when compiling a programming project, and automatically generates annotations according to pre-set rules for constructing and changing tables in the database.

Images