SQL statement generation method and device, storage medium and electronic equipment

Abstract

The invention discloses an SQL statement generation method and device, a storage medium and electronic equipment. Relates to the technical field of information, and comprises the following steps: receiving a service request for a target service, and determining a service parameter and a target data model class corresponding to the service request, the target data model class being used for interacting with a database, the database being used for storing service data tables corresponding to a plurality of services respectively, the business request is used for requesting to query or change target business; based on the target data model class, generating SQL statement main body information; generating SQL query conditions based on the business parameters; and generating a target SQL statement based on the SQL statement main body information and the SQL query condition. According to the method and the device, the technical problems of poor flexibility and low efficiency of database operation in a multi-service scene are solved.

Description

Technical Field

[0001] The present invention relates to the field of information technology, and in particular, to a method, apparatus, storage medium, and electronic device for generating SQL statements. Background Art

[0002] In the current software development environment, database operations are an indispensable part of almost all business systems. Especially for large-scale applications with multiple business scenarios, the structures of the data tables involved are complex and diverse, and different businesses may need to perform frequent query and change operations on the same or different data tables. In the database operation methods in the related art, such as writing static SQL statements, although they can meet the requirements in some scenarios, their limitations in flexibility and efficiency begin to emerge in the case of rapid changes in business requirements and frequent adjustments of data table structures.

[0003] Specifically, when facing multiple business scenarios, developers often need to write separate SQL statements for each specific business, which not only leads to a large amount of code duplication, increases the workload of development and maintenance, but also when the business logic or the structure of the database table changes, the static SQL statements are difficult to adapt quickly, and manual modification and testing are required, which easily introduces errors and reduces the stability and reliability of the system. In addition, the writing and maintenance of static SQL statements also limit the response speed of the development team to business requirements and cannot meet the requirements for agility and flexibility in modern software development.

[0004] In view of the above problems of poor flexibility and low efficiency in database operations in multiple business scenarios, no effective solution has been proposed yet. Summary of the Invention

[0005] Embodiments of the present invention provide a method, apparatus, storage medium, and electronic device for generating SQL statements, so as to at least solve the technical problems of poor flexibility and low efficiency in database operations in multiple business scenarios.

[0006] According to one aspect of the embodiments of the present invention, a method for generating SQL statements is provided, including: receiving a business request for a target business, and determining business parameters and a target data model class corresponding to the business request, where the target data model class is used to interact with a database, the database is used to store business data tables corresponding to multiple businesses respectively, and the business request is used to request to query or change the target business; generating main body information of an SQL statement based on the target data model class; generating an SQL query condition based on the business parameters; and generating a target SQL statement based on the main body information of the SQL statement and the SQL query condition.

[0007] According to another aspect of the embodiments of the present invention, there is also provided an SQL statement generation device, including: a first determination module, configured to receive a service request for a target service, and determine service parameters and a target data model class corresponding to the service request, wherein the target data model class is used to interact with a database, the database is used to store service data tables corresponding to multiple services respectively, and the service request is used to request querying or changing the target service; a first generation module, configured to generate SQL statement body information based on the target data model class; a second generation module, configured to generate an SQL query condition based on the service parameters; a third generation module, configured to generate a target SQL statement based on the SQL statement body information and the SQL query condition.

[0008] According to another aspect of the embodiments of the present invention, there is also provided a non-volatile storage medium storing multiple instructions, and the instructions are suitable for being loaded and executed by a processor to execute any one of the SQL statement generation methods.

[0009] According to another aspect of the embodiments of the present invention, there is also provided an electronic device including one or more processors and a memory, and the memory is used to store one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors are caused to implement any one of the SQL statement generation methods.

[0010] In the embodiments of the present invention, by receiving a service request for a target service, determining service parameters and a target data model class corresponding to the service request, wherein the target data model class is used to interact with a database, the database is used to store service data tables corresponding to multiple services respectively, and the service request is used to request querying or changing the target service; generating SQL statement body information based on the target data model class; generating an SQL query condition based on the service parameters; and generating a target SQL statement based on the SQL statement body information and the SQL query condition, the purpose of dynamically parsing the received service request and adaptively generating an SQL statement by combining the attributes and annotation information of the target data model class is achieved, thereby realizing the technical effects of improving the automation degree of database operations, reducing duplicate code, enhancing the maintainability and response performance of the system, and further solving the technical problems of poor flexibility and low efficiency in database operations in multi-service scenarios. Description of the Drawings

[0011] The drawings described herein are used to provide a further understanding of the present invention, and constitute a part of this application. The schematic embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute an improper limitation to the present invention. In the drawings:

[0012] Figure 1 It is a flowchart of a method for generating SQL statements according to an embodiment of the present invention;

[0013] Figure 2 It is a flowchart of an alternative method for generating SQL statements according to an embodiment of the present invention;

[0014] Figure 3 It is a schematic diagram of a device for generating SQL statements according to an embodiment of the present invention. Detailed implementation manners

[0015] In order to enable those skilled in the art to better understand the solution of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present invention.

[0016] It should be noted that the terms "first", "second", etc. in the specification and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects, and do not necessarily need to be used to describe a specific order or sequence. It should be understood that such data can be interchanged under appropriate circumstances so that the embodiments of the present invention described here can be implemented in an order other than those illustrated or described here. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusion. For example, a process, method, system, product, or device that includes a series of steps or units does not necessarily need to be limited to those steps or units clearly listed, but may include other steps or units not clearly listed or inherent to these processes, methods, products, or devices.

[0017] First, for the convenience of understanding the embodiments of the present invention, some terms or nouns involved in the present invention will be explained below:

[0018] Structured Query Language (SQL), a database language with various functions such as data manipulation and data definition. This language has the characteristic of interactivity, which can provide great convenience for users. The database management system should make full use of the SQL language to improve the working quality and efficiency of computer application systems. The SQL language can not only be independently applied to terminals, but also be used as a sublanguage to provide effective assistance for other programming. In this program application, SQL can optimize the program function together with other programming languages, and then provide users with more and more comprehensive information.

[0019] A database, also known as a data management system, is a collection of data stored together in a certain way, enabling multiple users to share it and minimizing redundancy as much as possible. In short, it can be regarded as an electronic filing cabinet - a place to store electronic files. A database can be composed of multiple tablespaces, and users can perform operations such as adding, intercepting, updating, and deleting data in the files.

[0020] MyBatis, a persistence layer framework, simplifies the interaction between Java application programs and relational databases. It supports custom SQL queries, result mapping, and provides SQL query caching functions. The most significant feature of MyBatis is its flexibility based on SQL statements. It allows developers to directly write native SQL statements without the need to encapsulate SQL statements. At the same time, MyBatis can provide powerful query generation capabilities based on dynamic SQL, making it very effective in handling complex database queries.

[0021] According to an embodiment of the present invention, a method embodiment for generating SQL statements is provided. It should be noted that the steps shown in the flowchart of the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions. And although the logical order is shown in the flowchart, in some cases, the steps shown or described can be executed in a different order than here.

[0022] Figure 1 is a flowchart of the SQL statement generation method according to an embodiment of the present invention, as Figure 1 shown, the method includes the following steps:

[0023] Step S102, receive a business request for a target business, and determine the business parameters and the target data model class corresponding to the business request. Among them, the target data model class is used to interact with the database, the database is used to store business data tables corresponding to multiple businesses respectively, and the business request is used to request to query or change the target business;

[0024] Optionally, in a software application, a business request can be initiated by a front-end application or an external service and transmitted to the back-end service through the Hypertext Transfer Protocol (HTTP) request or other communication protocols. These requests contain specific business logic requirements, such as querying the detailed information of a certain user, updating the account status of a user, etc. The business request is the trigger point for the entire system to interact with the outside world, and it carries the necessary information required to execute specific business logic.

[0025] Optionally, business parameters are the key information carried in a business request, which directly determine the details of database operations. For example, in a business request for querying user information, the business parameters may include the user ID, the user's name, or the account status, etc. These parameters are the key basis for generating dynamic SQL and will directly affect the structure and content of the generated SQL statement.

[0026] Optionally, a data model class is a class used in software development to encapsulate the database table structure and business logic. It maps the fields of the database table through the attributes of the class, providing an abstraction layer between the data and the business logic. Determining the target data model class means determining the database table structure and field information related to a specific business request, providing metadata support for dynamically generating SQL statements. For example, when querying user information, the target data model class can be the User class, and its fields such as the identifier id, name, account status accountStatus, etc., respectively correspond to the corresponding fields in the database table.

[0027] Optionally, the database may contain multiple data tables, and each data table stores data for a specific business. For example, in the retail finance field, there may be multiple data tables such as a user information table, a transaction record table, an account status table, etc., corresponding to different business scenarios. When a request for a certain business is received, identify and locate the data table related to this business for subsequent data operations.

[0028] The business request in this embodiment is used to request querying or changing the target business, indicating that the business request is not limited to querying data and may also include operations such as updating, inserting, or deleting data. Based on the type of the business request, the system will generate different types of SQL statements, such as a SELECT statement for querying, an UPDATE statement for updating, an INSERT statement for inserting, a DELETE statement for deleting, etc.

[0029] The key in the above step S102 is to accurately extract business parameters and determine the target data model class from the business request, so as to provide sufficient information for the generation of dynamic SQL statements. Through this mechanism, it is possible to intelligently adapt to different business scenarios, effectively reduce code duplication, improve the flexibility and efficiency of database operations, and at the same time reduce the maintenance cost caused by changes in the database table structure or business logic.

[0030] In an optional embodiment, determining the business parameters and the target data model class corresponding to the business request includes: parsing the business request to obtain the business parameters; calling the target interface corresponding to the business request; and calling the target data model class based on the target interface.

[0031] Optionally, parse the received business request. This can be done, but is not limited to, by decoding and converting external request data such as HTTP requests and Remote Procedure Call (RPC) calls, and extracting the business parameters contained therein. Business parameters can be query conditions, operation types, data objects, etc., which directly determine the generation method of the subsequent SQL statement and the data range to be operated on. For example, if the front end sends a request to query user information, then the parsing process will extract parameters such as user ID, name, and account status from the request. Once the business parameters are parsed, further call the corresponding business interface according to the type of the business request. For example, in the scenario of user management, if the request is to query user information, then an interface named UserMapper (or an interface with similar functions) will be called. The UserMapper interface is designed based on the MyBatis framework and is specifically used to handle database operations related to the user data table.

[0032] When calling the interface, further call or instantiate the target data model class that is closely related to the database table structure according to the definition of the interface. The data model class can include metadata of the database table (such as field, type, etc. information) and data objects required for business operations. For example, in the scenario of querying user information, the User class acts as the data model class, and its fields correspond to the fields of the user table in the database. Information such as table name and field name can be obtained from the User class through reflection or other mechanisms and used to generate the static part of the SQL statement. By using the data model class, not only the field information required in the SQL statement is provided, but also in some cases it can be passed as a parameter to the SQL statement for generating dynamic query conditions. For example, the accountStatus field in the User class can be used to construct a query condition such as WHERE accountStatus = 'activated' (i.e., filtering records where the account status is equal to activated).

[0033] Through the above method, after receiving a business request, the business parameters can be quickly and accurately parsed, the correct business interface and data model class can be called, providing the necessary context and data support for the generation of dynamic SQL statements. This can ensure that even if the database table structure or business parameters change, the changes can be adapted by modifying the structure of the data model class or the logic of the business interface, without the need to frequently modify the SQL statement, thereby improving the flexibility and maintainability of SQL statement generation.

[0034] Step S104, generate the main body information of the SQL statement based on the target data model class;

[0035] Optionally, the target data model class is an entity class in the software system used to represent a database table, which contains field attributes corresponding to the database table. In the dynamic SQL generation method, it is necessary to parse this data model class to obtain all field names and types. This information is the basis for constructing the main body of the SQL statement because it defines the structure of the data table and the attributes that can be queried or modified. After parsing the data model class, the main body of the SQL statement is generated based on this field information. For example, if the target data model class represents a user information table (user), then when querying user information, the main body of the SQL statement may be constructed as SELECT id, name, accountStatus FROM user (that is, select id, name, and account status from the user table), where fields such as id, name, and accountStatus are parsed from the data model class, and SELECT and FROM are the basic keywords of the SQL statement.

[0036] Optionally, the fields in the data model class can follow certain naming rules, such as camel case naming or underscore naming, while the fields in the database table may follow different naming rules. Therefore, when generating the main body information of the SQL statement, it is also necessary to implement the conversion of field names to convert the field names in the data model class into the actual field names in the database table to ensure the correctness of the SQL statement.

[0037] It should be noted that although the main body information of the generated SQL statement is fixed according to the target data model class, the dynamic SQL generation method allows flexible adjustment of the SQL statement according to specific business parameters, such as dynamically inserting field values in the query conditions. This means that it is possible to generate SQL statements that adapt to different query requirements without modifying or rewriting the SQL statement every time.

[0038] Optionally, when constructing the main body of the SQL statement, placeholders (such as %s or #{}) can be used to represent the parts that need to be dynamically filled according to business parameters at runtime. For example, the SQL statement may be SELECT %s FROM %s WHERE %s = #{value}, where %s and #{value} will be replaced by specific field names, table names, and parameter values in subsequent steps to generate a complete and dynamic SQL statement.

[0039] In the above step S104, the process of generating the main body information of the SQL statement based on the target data model class not only involves in-depth parsing of the data model class, but also involves the conversion of field names and the use of placeholders to ensure that the generated SQL statement can accurately and dynamically perform database operations and meet business requirements.

[0040] In an alternative embodiment, based on the target data model class, SQL statement body information is generated, including: obtaining the target field information and target annotation information corresponding to the target data model class by using a reflection parsing method; generating the SQL statement body information based on the target field information and target annotation information, wherein the SQL statement body information matches the table structure of the database.

[0041] Optionally, the field information in the target data model class is mapped to the fields in the database through target annotations (for example, @TableName is used to specify the table name, and the annotations on the fields may be used to specify the corresponding fields in the database). This process can ensure that the corresponding relationship between the fields in the data model class and the actual fields in the database can be correctly identified, which is crucial for generating the SQL statement body information. For example, if there is a field accountStatus in the User class and it is specified through an annotation that the corresponding name in the database is account_status, then the mapping process will convert the accountStatus field to account_status to conform to the naming convention of the database.

[0042] Select a template from the predefined set of SQL statement templates that best matches the current business request. These templates usually contain the skeleton of the SQL statement, such as SELECT %s FROM %s WHERE %s, where %s are placeholders corresponding to the fields to be queried, the table name, and the query conditions respectively. Selecting the correct template is the key to dynamic SQL generation, which determines the basic structure and syntax of the SQL statement. The main part of the SQL statement can be generated based on the mapped field list and the selected SQL statement template. This process involves filling the field names in the field list into the corresponding positions in the template and filling the table name according to the annotation information on the data model class. For example, if the field list contains id, name, and account_status, and the template is SELECT %s FROM %s WHERE %s, then the generated main information can be SELECT id,name,account_status FROM user.

[0043] The above method closely combines the data model class, annotations, and SQL statement templates. Through automatic processing by the program, it can avoid the repetitive work of manually writing SQL statements, and at the same time improve the readability and maintainability of the code. Especially in complex business scenarios, the above method can effectively handle changes in the database table structure, reduce code modifications, and improve development efficiency. At the same time, using templates and parameterized queries also helps to enhance the security of SQL statements and prevent attacks such as SQL injection.

[0044] In an alternative embodiment, based on the target field information and the target annotation information, the main body information of the SQL statement is generated, including: based on the target annotation information, mapping the target field information to the field information in the database to determine the list of fields required in the target SQL statement, where the fields included in the field list match the naming of the fields in the database; determining the target SQL statement template that matches the business request from multiple SQL statement templates; generating the main body information of the SQL statement based on the field list; generating the main body information of the SQL statement based on the field list and the target SQL statement template.

[0045] Optionally, when defining a data model class, annotations (such as @TableField) are used to map the attributes of the class to the fields in the database table. This mapping ensures the consistency or transformation rules between the attribute names of the Java class and the database field names, and even in the case where the naming styles do not completely match (for example, the Java class uses camel case naming, while the database uses underscore-separated naming), the corresponding conversion rules specified by the annotation can be used. For example, in the User class, the accountStatus field is mapped to the account_status field in the database through an annotation. During the process of generating a dynamic SQL statement, first, a template that best matches the current business request needs to be selected from the preset SQL statement templates. These templates contain the basic structure of the SQL statement, such as keywords like SELECT, FROM, WHERE, etc., as well as placeholders for dynamic parameters. For example, the template can be SELECT %s FROM %s WHERE %s, where %s are placeholders for dynamic filling, representing the field list, table name, and query conditions respectively.

[0046] After determining all the required fields and templates, use this field information to dynamically generate the main part of the SQL statement. Specifically, you can use the reflection mechanism or a similar mechanism to obtain all the field information of the data model class, convert these fields into database field names according to the mapping rules, and fill these field names into the corresponding placeholder positions in the SQL statement template to generate the main part of the SQL statement. For example, if the User class has fields such as id, name, and accountStatus, then the generated main information will be SELECT id, name, account_status FROM user. Finally, combine the determined field list and the SQL statement template, and insert the field names in the field list into the corresponding positions in the template to generate the complete main part of the SQL statement. This main part contains the details of the query operation, such as which fields to query and from which table to query. For example, combining the template SELECT %s FROM %s and the field list id, name, account_status, the generated main information will be SELECT id, name, account_status FROM user.

[0047] In the above way, without modifying the hard-coded SQL statement, it is possible to dynamically generate SQL statements according to the changes in business requirements and database table structures, improving the flexibility and maintainability of the code, and at the same time reducing the risk of errors caused by changes in the database table structure.

[0048] In an alternative embodiment, based on the target data model class, generate the main information of the SQL statement, including: retrieving whether the account corresponding to the service request has access rights to the database; and if the account corresponding to the service request has access rights, generating the main information of the SQL statement based on the target data model class.

[0049] Optionally, before generating the SQL statement, first verify whether the account initiating the business request has the permission to operate on the target database. This can ensure the security of data and the compliance of access. The access permissions can be stored in the permission management table of the database, or can be managed through system configuration, role assignment, or access control lists, etc. For example, by checking the authentication information in the request, it can be determined whether the user belongs to a specific role or group with read / write database permissions. If the permission check passes, that is, the account of the business request has sufficient permissions to operate on the database, then continue with the process of generating the dynamic SQL. The above method mainly uses the information of the target data model class to construct the main part of the SQL statement, that is, the SQL statement skeleton without specific query conditions. For example, the data model class User is associated with the user table in the database through the @TableName annotation, and the SELECT and FROM clauses can be generated based on the metadata of the User class, such as SELECT id, name, account_status FROM user.

[0050] The above permission check mechanism in the process of generating dynamic SQL can ensure that only authorized users can access the database, thus preventing unauthorized access and potential data leakage. In addition, the permission check can also involve more fine-grained control, such as checking whether the user has the right to access specific fields, or making queries within a specific time period. This fine-grained permission management can further enhance the security of the system and ensure that data access is carried out according to predefined rules and policies.

[0051] Step S106, generate SQL query conditions based on business parameters;

[0052] Optionally, business parameters refer to the data passed in the business request and associated with specific business operations. These parameters can be conditions for query, data for update or insertion, or metrics for filtering and sorting. For example, in a business request for querying user information, the business parameters may include user ID, account status, etc., and these parameters will be used to construct the WHERE clause in the SQL statement to locate specific user records.

[0053] Optionally, after determining the business parameters, generate SQL query conditions based on these parameters. This process may involve converting the business parameters into a recognizable format in the SQL statement, such as converting the user ID into a query condition like WHERE id=#{userId}, where #{userId} is a parameter placeholder in MyBatis, indicating that the actual user ID value will be used to replace it when the SQL is executed.

[0054] Optionally, when generating SQL query conditions, the logical relationships between conditions can also be considered, such as the use of logical operators like AND and OR. For example, if the business request requires querying all users whose status is "activated" and whose last login time is after a specific date, then the query condition can be WHERE accountStatus = 'activated' AND lastLoginDate > #{date} (that is, filtering records where the account status is activated and the last login date is greater than the specified date). This requires the ability to intelligently handle various complex conditional logics to ensure that the generated SQL statement can accurately express the business requirements.

[0055] Optionally, when generating SQL query conditions, special attention should be paid to preventing SQL injection attacks. Therefore, security processing can be performed on business parameters, such as using parameterized queries (such as the #{} placeholder in MyBatis) to avoid directly splicing parameters into the SQL statement, which can effectively prevent malicious users from injecting illegal SQL code through parameter injection.

[0056] Based on the above step S106, accurate SQL query conditions can be generated according to business parameters, so as to filter out data records that meet the business requirements in the database, which can not only improve the efficiency and accuracy of database operations, but also enhance the security and flexibility of the system and can quickly adapt to business changes.

[0057] In an optional embodiment, based on business parameters, generating SQL query conditions includes: parsing the query keywords in the business parameters to determine the type and value of the query conditions; generating a query condition expression that matches the database according to the type and value of the query conditions and the field mappings in the target data model class; and generating an SQL query condition based on the query condition expression.

[0058] Optionally, when the system receives a business request, it first parses the business parameters in the request. These parameters can contain one or more query keywords used to define the query conditions. The query keywords can be field names, operators (such as equals, greater than, less than, etc.), and specific values. For example, the business parameters can include the accountStatus field and the activated value, meaning the query condition is accountStatus = 'activated'. After parsing out the query keywords, the system further analyzes these keywords to determine the type of query condition (such as equality matching, range matching, fuzzy matching, etc.) and the specific values. The determination of the type and value is the basis for generating the SQL query condition. Different types determine the structure and syntax of the query condition. For example, if the value of the accountStatus field is 'activated', then the type of the query condition is equality matching, and the query condition expression will be accountStatus = 'activated'. Using the query condition type and value determined in the previous step, combined with the field mapping information in the target data model class, a query condition expression that matches the database is generated. The field mapping information comes from the annotations on the data model class, such as @TableField, which is used to indicate the mapping relationship between Java fields and database fields. For example, if the annotation of the accountStatus field in the User class is mapped to the account_status field in the database, then the system will replace accountStatus in the query condition expression with account_status to ensure that the query statement conforms to the naming rules of the database. The generated query condition expression is combined into the SQL statement to generate a complete SQL query condition. If a business request contains multiple query keywords, then corresponding query conditions are generated for each keyword, and then these conditions are combined together using logical operators (such as AND, OR) to form the final query condition statement. For example, if the business parameters also include the lastLoginDate field and a date range, then the finally generated SQL query condition can be account_status = 'activated' AND last_login_date BETWEEN #{startDate} AND #{endDate}.

[0059] The above method can effectively convert business parameters into SQL query conditions, enabling dynamic adjustment and generation of query statements according to specific business requirements without manually writing and maintaining a large amount of SQL code. This method not only improves the code reusability and maintainability but also enhances the flexibility of SQL statement generation, being able to better adapt to changes in business logic and data models.

[0060] Step S108: Generate a target SQL statement based on the SQL statement body information and the SQL query conditions.

[0061] Optionally, after obtaining the SQL statement body information, concatenate the SQL body and the query conditions to form a complete SQL statement. For example, if the body information is SELECT id, name, accountStatus FROM user, and the dynamically generated query condition is WHERE accountStatus = 'activated' AND lastLoginDate > #{date}, then the final generated target SQL statement will be the combination of the two as follows:

[0062] SELECT id, name, accountStatus FROM user WHERE accountStatus = 'activated' AND lastLoginDate > #{date}.

[0063] Optionally, during the concatenation process, it is necessary to ensure that the generated SQL statement has correct syntax and a standardized format. For example, necessary spaces and line breaks need to be added between the body and the query conditions to ensure the readability of the SQL statement. For dynamic parameters in the query conditions (such as #{date}), parameterization processing will be performed, that is, these parameters will be replaced with parameter markers in the pre-compiled SQL statement. This processing method can prevent SQL injection attacks and improve the execution efficiency of the SQL statement. Through the above steps, a complete and secure target SQL statement can be finally generated. This statement contains all necessary fields, table names, and dynamic query conditions and can be directly submitted to the database for execution.

[0064] Optionally, the generated SQL statement can be submitted to the database for query or update operations. After the database executes, it returns a result set, and then the result set is processed and returned to the upstream business module or the front-end user.

[0065] The above Step S108 combines the static SQL statement body and the dynamic query conditions, and realizes the personalized customization of the SQL statement through program logic. Thus, it can flexibly respond to various business requests while ensuring the security and efficiency of the SQL statement.

[0066] In an alternative embodiment, a target SQL statement is generated based on the SQL statement body information and the SQL query conditions, including: inserting the SQL query conditions into the corresponding positions in the SQL statement body information to obtain a first SQL statement; performing a syntax check on the first SQL statement to obtain a syntax check result; in the case where the syntax check result indicates that the syntax check of the first SQL statement passes, determining the first SQL statement as the target SQL statement; or in the case where the syntax check result indicates that the syntax check of the first SQL statement fails, performing syntax correction on the first SQL statement to obtain the target SQL statement.

[0067] Optionally, during the generation of dynamic SQL, after the main part of the SQL statement (including basic structures such as SELECT, FROM, etc.) and the SQL query conditions (the WHERE clause generated based on business parameters) are both constructed separately, the query conditions are inserted into the appropriate position in the body information, such as after the WHERE keyword. For example, if the body information is SELECT id, name, account_status FROM user and the query conditions are account_status = 'activated' AND last_login_date > #{date}, then the first SQL statement after insertion is SELECT id, name, account_status FROM user WHERE account_status = 'activated' AND last_login_date > #{date}. The generated first SQL statement needs to be subjected to a syntax check before execution to ensure that it conforms to SQL syntax rules. The syntax check can be completed by the MyBatis framework or other database-related tools, and this step can ensure the correctness and executability of the SQL statement. The check result will indicate whether the statement passes the syntax check, and if not, subsequent corrections will be made.

[0068] If the syntax check passes, it means that the SQL statement is legal and can be correctly parsed and executed by the database. Then this first SQL statement is determined as the final target SQL statement and can be passed to the database for execution. If the syntax check fails, this usually means that there is some syntax error in the SQL statement, such as mismatched parentheses, incorrect keywords, etc. At this time, the system needs to correct the first SQL statement to ensure that it conforms to the SQL syntax rules. The correction can be automatic, adjusting the SQL statement structure through predefined rules, or semi-automatic, with the system prompting error messages for manual correction by developers. After the correction is completed, the syntax check is performed again until the SQL statement passes the check, and the finally determined SQL statement is the target SQL statement.

[0069] In the above manner, it can be ensured that the generated SQL statements not only meet the requirements of business logic but also are syntactically correct. This helps to avoid runtime exceptions caused by SQL syntax errors, improving the stability and security of the system. At the same time, the syntax correction and verification mechanism also enables developers to focus more on the implementation of business logic without having to worry too much about the details of SQL statements.

[0070] Through the above steps S102 to S108, the purpose of dynamically parsing the received business requests and combining the attributes and annotation information of the target data model class to adaptively generate SQL statements can be achieved, thereby realizing the technical effects of improving the automation degree of database operations, reducing duplicate code, enhancing the maintainability and response performance of the system, and further solving the technical problems of poor flexibility and low efficiency in database operations for multiple business scenarios.

[0071] Based on the above embodiments and alternative embodiments, the present invention proposes an alternative implementation manner. Figure 2 It is a flowchart of an alternative SQL statement generation method according to an embodiment of the present invention, as Figure 2 shown. The method includes:

[0072] S1. The front end submits a request for querying user information to the back end, carrying the user identification (userId) parameter.

[0073] S2. The query request calls the method selectOne of the interface corresponding to the database table. For example, for the user table, the corresponding user data mapping interface is userMapper. Specifically:

[0074] The selectOne method is a method for selecting one or querying by primary key, which is used to query and return an instance of a data model from the database according to the given conditions (usually a primary key or other unique identifier).

[0075] userMapper is a custom interface for encapsulating all database operations related to the database user table, such as querying, inserting, updating, and deleting, etc.; the interface userMapper inherits the base interface BaseMapper <t>, BaseMapper <t>It is a general and basic data mapper interface, designed to provide a basic operation template for interacting with the database, where T is a generic parameter representing the data model class associated with the database table.

[0076] The corresponding table name @TableName("user") is marked in the data model class through annotations.

[0077] There is an annotation @SelectProvider(type = OurSqlBuilder.class, method = "buildSelectOneProvider") on the selectOne method in the base class BaseMapper. Specifically:

[0078] Here, the @SelectProvider is the dynamic statement provider annotation. Its function is to generate SQL statements through the SQL factory class and the corresponding method. Specifically, when the method with this annotation is executed, @SelectProvide will call the type and method specified in the annotation to dynamically construct the SQL statement. This enables developers to flexibly generate SQL based on business logic and runtime parameters, thus avoiding the maintenance problems and inefficiencies brought by hard-coded SQL.

[0079] type = OurSqlBuilder.class is the function of specifying the type parameter: The type attribute is used to specify a class that contains the logic for generating dynamic SQL statements. In this example, OurSqlBuilder.class is the specified SQL generator class, and the methods for constructing SQL statements according to the incoming parameters and the metadata of the data model class are defined in this class. When the @SelectProvider annotation is processed, this class is instantiated and its methods are called.

[0080] method = "buildSelectOneProvider" is the function of specifying the method parameter: The method attribute is used to specify a method in the OurSqlBuilder class that is responsible for generating a specific SQL statement. In this example, the "buildSelectOneProvider" method is called, and it will be based on BaseMapper <t>The selectOne call in the interface and the passed business parameters are used to generate an SQL statement for querying a single record. This method can flexibly handle various query conditions and assemble an SQL statement that meets the requirements.

[0081] Here, for querying distance, the corresponding annotation is updated to @UpdateProvider, and for insertion, it is @InsertProvider.

[0082] In summary, the @SelectProvider annotation specifies a dynamic SQL generation strategy through the type and method attributes. Among them, OurSqlBuilder is a custom SQL generator class, and "buildSelectOneProvider" is a method in this class for constructing an SQL statement to query a single data record. This mechanism enables the MyBatis framework to dynamically generate SQL based on real-time business parameters, improving the adaptability and maintainability of the code.

[0083] S3. When the method is called, the annotation in step S2 will be scanned, and the buildSelectOneProvider method of the OurSqlBuilder class in the annotation will be executed.

[0084] S4. In the buildSelectOneProvider method, the SQL statement is assembled according to the dynamic SQL template. Specifically:

[0085] The query in the template is SELECT_ONE("selectOne","Query a single piece of data that meets the conditions"," <script>SELECT％s FROM％s％s％s\n< / script> "); Among them, <script>即脚本标签作用：<script>标签用于包裹动态SQL语句，它允许在其中使用动态SQL语法，如：如果if、选择choose、否则otherwise等，来构造基于条件的SQL语句。<script>标签内的SQL可以包含占位符和动态生成的部分，这些动态部分会根据实际参数和业务逻辑进行替换和组合，生成最终的SQL语句；"SELECT％s FROM％s％s％s\n< / script> " indicates the role of the dynamic SQL template: This string is a template for an SQL statement, where %s is a placeholder to be replaced by the actual SQL statement part. Specifically: In dynamic SQL generation, %s is used as a placeholder, representing the parts that need to be dynamically generated in the SQL statement. These placeholders will be replaced by actual values such as specific field names, table names, and query conditions to form a complete SQL statement; In the SQL statement, the SELECT keyword is used to specify the query operation for retrieving data from the database table; The FROM keyword is used to specify the data source, that is, from which database table to retrieve; Although the WHERE keyword is not directly shown in the template string, the %s placeholder can be used to generate the WHERE clause to filter records that meet specific conditions.

[0086] The role of the entire template is <script>标签的包裹下，提供一个可以被动态填充的SQL语句框架。当使用@SelectProvider或类似注解时，会调用一个指定的动态SQL生成类(如OurSqlBuilder)，这个类中的方法(如buildSelectOneProvider)会根据业务参数，采用反射机制或其他机制来解析数据模型类(如User)的元数据，生成具体的字段列表、表名和查询条件，然后将这些动态生成的部分插入到％s占位符中，替换模板中的占位符，生成最终的SQL语句。这样，即使数据库表结构或查询需求发生变化，也可以通过调整动态SQL生成的逻辑，而无需修改SQL模板或接口方法的签名，保持代码的灵活性和可维护性。

[0087] 其中表名来自于User类中的注解@TableName("user")。

[0088] select中的内容来自User类，通过反射拿到所有字段，然后转换成数据库中的字段，此处约定由于配置，默认model类和数据库字段为驼峰转换关系。

[0089] where条件则来自于入参，根据入参组装对应的查询条件。

[0090] S5、步骤S4中的操作完成即根据模板动态生成SQL完成，将组装好的SQL返回由持有曾框架(如mybatis等)管理并执行。

[0091] S6、拿到步骤S5中执行结果完成后续的业务操作以此完成整个业务流程。

[0092] 通过以上方式，可以避免大量类似的SQL复写，同时避免因表结构和部分业务变更而带来的数据库操作异常。

[0093] 在本实施例中还提供了一种SQL语句生成装置，该装置用于实现上述实施例及优选实施方式，已经进行过说明的不再赘述。如以下所使用的，术语"模块”"装置”可以实现预定功能的软件和 / 或硬件的组合。尽管以下实施例所描述的装置较佳地以软件来实现，但是硬件，或者软件和硬件的组合的实现也是可能并被构想的。

[0094] 根据本发明实施例，还提供了一种用于实施上述SQL语句生成方法的装置实施例，图3是根据本发明实施例的一种SQL语句生成装置的结构示意图，如图3所示，上述SQL语句生成装置，包括：第一确定模块300、第一生成模块302、第二生成模块306、第三生成模块308，其中：

[0095] 第一确定模块300，用于接收到针对目标业务的业务请求，确定业务请求对应的业务参数和目标数据模型类，其中，目标数据模型类用于与数据库进行交互，数据库用于存储多个业务分别对应的业务数据表，业务请求用于请求查询或更改目标业务；

[0096] 第一生成模块302，连接于第一确定模块300，用于基于目标数据模型类，生成SQL语句主体信息；

[0097] 第二生成模块306，连接于第一生成模块302，用于基于业务参数，生成SQL查询条件；

[0098] 第三生成模块308，连接于第二生成模块306，用于基于SQL语句主体信息和SQL查询条件，生成目标SQL语句。

[0099] 在本发明实施例中，通过设置第一确定模块300，用于接收到针对目标业务的业务请求，确定业务请求对应的业务参数和目标数据模型类，其中，目标数据模型类用于与数据库进行交互，数据库用于存储多个业务分别对应的业务数据表，业务请求用于请求查询或更改目标业务；第一生成模块302，连接于第一确定模块300，用于基于目标数据模型类，生成SQL语句主体信息；第二生成模块306，连接于第一生成模块302，用于基于业务参数，生成SQL查询条件；第三生成模块308，连接于第二生成模块306，用于基于SQL语句主体信息和SQL查询条件，生成目标SQL语句，达到了对接收到的业务请求进行动态解析并结合目标数据模型类的属性与注解信息，自适应生成SQL语句的目的，从而实现了提升数据库操作的自动化程度，减少重复代码，增强系统的可维护性和响应性能的技术效果，进而解决了针对多业务场景下数据库操作的灵活性差且效率低的技术问题。

[0100] 需要说明的是，上述各个模块是可以通过软件或硬件来实现的，例如，对于后者，可以通过以下方式实现：上述各个模块可以位于同一处理器中；或者，上述各个模块以任意组合的方式位于不同的处理器中。

[0101] 此处需要说明的是，上述第一确定模块300、第一生成模块302、第二生成模块306、第三生成模块308对应于实施例中的步骤S102至步骤S108，上述模块与对应的步骤所实现的实例和应用场景相同，但不限于上述实施例所公开的内容。需要说明的是，上述模块作为装置的一部分可以运行在计算机终端中。

[0102] 需要说明的是，本实施例的可选或优选实施方式可以参见实施例中的相关描述，此处不再赘述。

[0103] 上述的SQL语句生成装置还可以包括处理器和存储器，上述第一确定模块300、第一生成模块302、第二生成模块306、第三生成模块308等均作为程序模块存储在存储器中，由处理器执行存储在存储器中的上述程序模块来实现相应的功能。

[0104] 处理器中包含内核，由内核去存储器中调取相应的程序模块，上述内核可以设置一个或以上。存储器可能包括计算机可读介质中的非永久性存储器，随机存取存储器(RAM)和 / 或非易失性内存等形式，如只读存储器(ROM)或闪存(flash RAM)，存储器包括至少一个存储芯片。

[0105] 根据本申请实施例，还提供了一种非易失性存储介质的实施例。可选的，在本实施例中，上述非易失性存储介质包括存储的程序，其中，在上述程序运行时控制上述非易失性存储介质所在设备执行上述任意一种SQL语句生成方法。

[0106] 可选的，在本实施例中，上述非易失性存储介质可以位于计算机网络中计算机终端群中的任意一个计算机终端中，或者位于移动终端群中的任意一个移动终端中，上述非易失性存储介质包括存储的程序。

[0107] 可选的，在程序运行时控制非易失性存储介质所在设备执行以下功能：接收到针对目标业务的业务请求，确定业务请求对应的业务参数和目标数据模型类，其中，目标数据模型类用于与数据库进行交互，数据库用于存储多个业务分别对应的业务数据表，业务请求用于请求查询或更改目标业务；基于目标数据模型类，生成SQL语句主体信息；基于业务参数，生成SQL查询条件；基于SQL语句主体信息和SQL查询条件，生成目标SQL语句。

[0108] 根据本申请实施例，还提供了一种处理器的实施例。可选的，在本实施例中，上述处理器用于运行程序，其中，上述程序运行时执行上述任意一种SQL语句生成方法。

[0109] 根据本申请实施例，还提供了一种计算机程序产品的实施例，当在数据处理设备上执行时，适于执行初始化有上述任意一种的SQL语句生成方法步骤的程序。

[0110] 可选的，上述计算机程序产品，当在数据处理设备上执行时，适于执行初始化有如下方法步骤的程序：接收到针对目标业务的业务请求，确定业务请求对应的业务参数和目标数据模型类，其中，目标数据模型类用于与数据库进行交互，数据库用于存储多个业务分别对应的业务数据表，业务请求用于请求查询或更改目标业务；基于目标数据模型类，生成SQL语句主体信息；基于业务参数，生成SQL查询条件；基于SQL语句主体信息和SQL查询条件，生成目标SQL语句。

[0111] 本发明实施例提供了一种电子设备，该电子设备包括处理器、存储器及存储在存储器上并可在处理器上运行的程序，处理器执行程序时实现以下步骤：接收到针对目标业务的业务请求，确定业务请求对应的业务参数和目标数据模型类，其中，目标数据模型类用于与数据库进行交互，数据库用于存储多个业务分别对应的业务数据表，业务请求用于请求查询或更改目标业务；基于目标数据模型类，生成SQL语句主体信息；基于业务参数，生成SQL查询条件；基于SQL语句主体信息和SQL查询条件，生成目标SQL语句。

[0112] 上述本发明实施例顺序仅仅为了描述，不代表实施例的优劣。

[0113] 在本发明的上述实施例中，对各个实施例的描述都各有侧重，某个实施例中没有详述的部分，可以参见其他实施例的相关描述。

[0114] 在本申请所提供的几个实施例中，应该理解到，所揭露的技术内容，可通过其它的方式实现。其中，以上所描述的装置实施例仅仅是示意性的，例如上述模块的划分，可以为一种逻辑功能划分，实际实现时可以有另外的划分方式，例如多个模块或组件可以结合或者可以集成到另一个系统，或一些特征可以忽略，或不执行。另一点，所显示或讨论的相互之间的耦合或直接耦合或通信连接可以是通过一些接口，模块或模块的间接耦合或通信连接，可以是电性或其它的形式。

[0115] 上述作为分离部件说明的模块可以是或者也可以不是物理上分开的，作为模块显示的部件可以是或者也可以不是物理模块，即可以位于一个地方，或者也可以分布到多个模块上。可以根据实际的需要选择其中的部分或者全部模块来实现本实施例方案的目的。

[0116] 另外，在本发明各个实施例中的各功能模块可以集成在一个处理模块中，也可以是各个模块单独物理存在，也可以两个或两个以上模块集成在一个模块中。上述集成的模块既可以采用硬件的形式实现，也可以采用软件功能模块的形式实现。

[0117] 上述集成的模块如果以软件功能模块的形式实现并作为独立的产品销售或使用时，可以存储在一个计算机可读取非易失性存储介质中。基于这样的理解，本发明的技术方案本质上或者说对现有技术做出贡献的部分或者该技术方案的全部或部分可以以软件产品的形式体现出来，该计算机软件产品存储在一个非易失性存储介质中，包括若干指令用以使得一台计算机设备(可为个人计算机、服务器或者网络设备等)执行本发明各个实施例方法的全部或部分步骤。而前述的非易失性存储介质包括：U盘、只读存储器(ROM，Read-Only Memory)、随机存取存储器(RAM，Random Access Memory)、移动硬盘、磁碟或者光盘等各种可以存储程序代码的介质。

[0118] 以上仅是本发明的优选实施方式，应当指出，对于本技术领域的普通技术人员来说，在不脱离本发明原理的前提下，还可以做出若干改进和润饰，这些改进和润饰也应视为本发明的保护范围。< / script> < / t> < / t> < / t>

Claims

1. A method for generating SQL statements, characterized in that, Including: Receiving a service request for a target service, and determining service parameters and a target data model class corresponding to the service request, where the target data model class is used to interact with a database, the database is used to store service data tables corresponding to multiple services respectively, and the service request is used to request querying or changing the target service; Generating main body information of an SQL statement based on the target data model class; Generating an SQL query condition based on the service parameters; Generating a target SQL statement based on the main body information of the SQL statement and the SQL query condition.

2. The method according to claim 1, wherein The determining the service parameters and the target data model class corresponding to the service request includes: Performing parsing processing on the service request to obtain the service parameters; Invoking a target interface corresponding to the service request; Invoking the target data model class based on the target interface.

3. The method according to claim 1, wherein The generating the main body information of the SQL statement based on the target data model class includes: Obtaining target field information and target annotation information corresponding to the target data model class by using a reflection parsing method; Generating the main body information of the SQL statement based on the target field information and the target annotation information, where the main body information of the SQL statement matches the table structure of the database.

4. The method according to claim 3, characterized in that The generating the main body information of the SQL statement based on the target field information and the target annotation information includes: Based on the target annotation information, mapping the target field information to the field information in the database, and determining a field list required in the target SQL statement, where the fields included in the field list match the field naming in the database; Determining a target SQL statement template that matches the service request from multiple SQL statement templates; Generating the main body information of the SQL statement based on the field list; Generating the main body information of the SQL statement based on the field list and the target SQL statement template.

5. The method according to claim 1, characterized in that, The generating the SQL query condition based on the service parameters includes: Parsing query keywords in the service parameters to determine the type and value of the query condition; Generating a query condition expression that matches the database according to the type and value of the query condition and the field mapping in the target data model class; Generating the SQL query condition based on the query condition expression.

6. The method according to claim 1, wherein The generating the target SQL statement based on the main body information of the SQL statement and the SQL query condition includes: Inserting the SQL query condition into a corresponding position in the main body information of the SQL statement to obtain a first SQL statement; Performing syntax verification on the first SQL statement to obtain a syntax verification result; When the syntax verification result indicates that the syntax verification of the first SQL statement passes, determining the first SQL statement as the target SQL statement; or When the syntax verification result indicates that the syntax verification of the first SQL statement fails, performing syntax correction on the first SQL statement to obtain the target SQL statement.

7. The method according to claim 1, characterized in that Generating the main body information of the SQL statement based on the target data model class includes: Retrieving whether the account corresponding to the service request has access rights to the database; When the account corresponding to the service request has the access rights, generating the main body information of the SQL statement based on the target data model class.

8. A SQL statement generation device, characterized in that Including: A first determination module, configured to receive a service request for a target service, and determine the service parameters and the target data model class corresponding to the service request, wherein the target data model class is used to interact with a database, the database is used to store service data tables corresponding to multiple services respectively, and the service request is used to request to query or change the target service; A first generation module, configured to generate the main body information of the SQL statement based on the target data model class; A second generation module, configured to generate an SQL query condition based on the service parameters; A third generation module, configured to generate a target SQL statement based on the main body information of the SQL statement and the SQL query condition.

9. A non-volatile storage medium, characterized in that, The non-volatile storage medium stores multiple instructions, and the instructions are suitable for being loaded and executed by a processor to perform the SQL statement generation method according to any one of claims 1 to 7.

10. An electronic device, characterized in that, Including one or more processors and a memory, the memory is used to store one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors implement the SQL statement generation method according to any one of claims 1 to 7.

Images