CN108563694B - SQL execution method and device for logic deletion, computer equipment and storage medium - Google Patents
SQL execution method and device for logic deletion, computer equipment and storage medium Download PDFInfo
- Publication number
- CN108563694B CN108563694B CN201810225650.4A CN201810225650A CN108563694B CN 108563694 B CN108563694 B CN 108563694B CN 201810225650 A CN201810225650 A CN 201810225650A CN 108563694 B CN108563694 B CN 108563694B
- Authority
- CN
- China
- Prior art keywords
- sql
- executed
- deletion
- sql statement
- entity class
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
- Stored Programmes (AREA)
Abstract
The invention relates to a method and a device for executing SQL (structured query language) for logic deletion, computer equipment and a storage medium, which are applied to the technical field of databases. The method comprises the following steps: acquiring an SQL statement to be executed, and identifying the operation type of the SQL statement to be executed; when the operation type is deletion, update or query, identifying a logic deletion field in a data table for the SQL statement to be executed according to a preset logic deletion mark in the entity class; a mapping relation exists between the entity class and the data table; and generating a corresponding SQL operation condition according to the logic deletion field, updating the SQL statement to be executed according to the SQL operation condition, and executing the updated SQL statement. The embodiment of the invention solves the problem of inaccurate SQL execution result of logic deletion and simplifies user operation.
Description
Technical Field
The present invention relates to the field of database technologies, and in particular, to a method and an apparatus for executing Structured Query Language (SQL) based on logical deletion, a computer device, and a storage medium.
Background
Logical deletion means that a delete operation is not actually performed on the database to delete data, but a field of the data is set as a flag. Meanwhile, a field (for example is _ delete) for indicating logical deletion is set for the data in the database table, if the field is 1, the data is indicated to be logically deleted, and if the field is 0, the data is indicated not to be logically deleted.
In the process of implementing the invention, the inventor finds that the following problems exist in the prior art: after the data is deleted by the user logic, the data is difficult to exclude by using the traditional SQL statement, so that the SQL execution result cannot meet the requirement.
Disclosure of Invention
Based on this, it is necessary to provide a method, an apparatus, a computer device and a storage medium for executing SQL for logical deletion, which solve the problem that the existing method is inaccurate in the execution result of SQL for logical deletion.
The scheme provided by the embodiment of the invention comprises the following steps:
a method of SQL execution for logical deletions, comprising:
acquiring an SQL statement to be executed, and identifying the operation type of the SQL statement to be executed;
when the operation type is deletion, update or query, identifying a logic deletion field in a data table for the SQL statement to be executed according to a preset logic deletion mark in the entity class; a mapping relation exists between the entity class and the data table;
and generating a corresponding SQL operation condition according to the logic deletion field, updating the SQL statement to be executed according to the SQL operation condition, and executing the updated SQL statement.
In one embodiment, the step of identifying a logical deletion field in a data table for which the SQL statement is to be executed according to a preset logical deletion flag in the entity class includes:
determining an entity class corresponding to the SQL sentence to be executed;
and detecting a preset logic deleting mark in the entity class, and identifying a logic deleting field in a data table according to the logic deleting mark.
In one embodiment, the method further comprises the following steps:
if the fact that the entity class does not contain a preset logic deleting mark is detected, executing the SQL sentence to be executed;
and/or the presence of a gas in the gas,
after the step of determining the operation type of the SQL statement to be executed, the method further includes:
and when the operation type of the SQL statement to be executed is the newly added operation type, executing the SQL statement to be executed.
In one embodiment, the step of determining the entity class corresponding to the SQL statement to be executed includes:
analyzing the SQL sentence to be executed according to a preset SQL analyzer to obtain a syntax tree corresponding to the SQL sentence to be executed; traversing the FROM node of the syntax tree and/or leaf nodes of the FROM node, and determining a data table for the SQL statement to be executed;
and obtaining an entity class corresponding to the data table according to the mapping relation between the entity class and the data table, and determining the entity class as the entity class corresponding to the SQL sentence to be executed.
In one embodiment, the step of identifying the logical delete field in the data table based on the logical delete flag comprises:
determining the attribute pointed by the logic deletion mark in the entity class, and obtaining the field corresponding to the attribute as the logic deletion field according to the mapping relation between the attribute in the entity class and the field in the data table.
In one embodiment, generating a corresponding SQL operating condition according to the logical deletion field, and updating the to-be-executed SQL statement according to the SQL operating condition includes:
if the operation type is updating or querying, generating a first operation condition of x.is _ delete ═ K1, and adding the first operation condition to the where attribute of the to-be-executed SQL statement;
if the operation type is deletion, generating a second operation condition of X.is _ delete ═ K2, creating an SQL statement of an update type according to the SQL statement to be executed, and adding the second operation condition to a set attribute of the SQL statement of the update type;
wherein, X represents a data item in a data table pointed by the to-be-executed SQL statement, is _ delete represents a logical deletion field, K1 and K2 represent preset logical values, when x.is _ delete is equal to K1, it represents that data X is not logically deleted, and when x.is _ delete is equal to K2, it represents that data X is logically deleted.
In one embodiment, the method further comprises the following steps:
creating an entity class corresponding to the data table, setting an attribute for representing logic deletion in the entity class, and setting a corresponding logic deletion mark for the attribute.
In one embodiment, setting an attribute for indicating logical deletion in the entity class, and setting a corresponding logical deletion flag for the attribute includes:
and setting Boolean type attributes used for expressing logic deletion in the entity class, and setting a logic deletion mark @ LogicDelete for the attributes.
An SQL execution device for logical deletion, comprising:
the type identification module is used for acquiring the SQL sentence to be executed and identifying the operation type of the SQL sentence to be executed;
the field identification module is used for identifying a logic deletion field in a data table aimed at by the SQL statement to be executed according to a preset logic deletion mark in the entity class; a mapping relation exists between the entity class and the data table;
and the modification execution module is used for generating a corresponding SQL operation condition according to the logic deletion field, updating the SQL statement to be executed according to the SQL operation condition and executing the updated SQL statement.
In one embodiment, the field identification module comprises:
the class identification unit is used for determining an entity class corresponding to the SQL statement to be executed when the operation type is deletion, update or query;
and the field identification unit is used for detecting a preset logic deletion mark in the entity class and identifying a logic deletion field in the data table according to the logic deletion mark.
In one embodiment, the method further comprises the following steps:
the first execution module is used for executing the SQL statement to be executed if the fact that the entity class does not contain a preset logic deletion mark is detected;
and/or the presence of a gas in the gas,
and the second execution module is used for executing the SQL sentence to be executed when the operation type of the SQL sentence to be executed is determined to be the newly added operation type.
In one embodiment, the class identification unit includes:
the data table identification subunit is used for analyzing the SQL statement to be executed according to a preset SQL analyzer to obtain a syntax tree corresponding to the SQL statement to be executed; traversing the FROM node of the syntax tree and/or leaf nodes of the FROM node, and determining a data table for the SQL statement to be executed;
and the class identification subunit is used for obtaining the entity class corresponding to the data table according to the mapping relation between the entity class and the data table, and determining the entity class as the entity class corresponding to the SQL sentence to be executed.
In one embodiment, the field identification unit includes:
and the field determining subunit is used for determining the attribute pointed by the logical deletion mark in the entity class, and obtaining the field corresponding to the attribute as the logical deletion field according to the mapping relation between the attribute in the entity class and the field in the data table.
In one embodiment, the modification execution module includes:
a first modifying unit, configured to generate a first operation condition of x.is _ delete ═ K1 if the operation type is update or query, and add the first operation condition to a where attribute of the to-be-executed SQL statement;
a second modifying unit, configured to generate a second operation condition of x.is _ delete ═ K2 if the operation type is delete, create an SQL statement of an update type according to the to-be-executed SQL statement, and add the second operation condition to a set attribute of the SQL statement of the update type;
wherein, X represents a data item in a data table pointed by the to-be-executed SQL statement, is _ delete represents a logical deletion field, K1 and K2 represent preset logical values, when x.is _ delete is equal to K1, it represents that data X is not logically deleted, and when x.is _ delete is equal to K2, it represents that data X is logically deleted.
According to the SQL execution method and device for logic deletion, after the SQL sentence to be executed is obtained, the operation type of the SQL sentence to be executed is identified; when the operation type is deletion, update or query, identifying a logic deletion field in the data table according to a preset logic deletion mark in the entity class; and generating a corresponding SQL operation condition according to the logic deletion field, updating the SQL statement to be executed according to the generated SQL operation condition, and then executing the updated SQL statement. Therefore, the SQL to be executed is automatically converted, and the method can adapt to all SQL sentences. When the user edits the SQL statement, the SQL operation on the logically deleted data can be realized through the conventional SQL statement without paying attention to whether the data in the database is logically deleted, so that the SQL operation difficulty of the user is simplified, and the accuracy of the SQL operation result is improved.
A computer device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, the processor implementing the SQL execution method for logical deletion as described above when executing the computer program.
According to the computer equipment, through the computer program running on the processor, SQL operation on the logically deleted data can be realized through the conventional SQL statement, the SQL operation difficulty of the user is simplified, and the accuracy of the SQL operation result is improved.
A computer storage medium having stored thereon a computer program which, when executed by a processor, implements the SQL execution method for logical deletion as described above.
The computer storage medium realizes SQL operation on logically deleted data through a conventional SQL statement through a computer program stored in the computer storage medium, simplifies the SQL operation difficulty of a user and improves the accuracy of an SQL operation result.
Drawings
FIG. 1 is a diagram of an application environment for a method for SQL execution for logical deletion in one embodiment;
FIG. 2 is a schematic flow chart diagram of an SQL execution method for logical deletion according to an embodiment;
FIG. 3 is a diagram of a syntax tree corresponding to a query SQL statement according to an embodiment;
FIG. 4 is a diagram of a syntax tree corresponding to a delete SQL statement according to an embodiment;
FIG. 5 is a schematic flow chart diagram of another embodiment SQL execution method for logical deletion
Fig. 6 is a schematic structural diagram of an SQL execution device for logical deletion according to an embodiment.
Detailed Description
In order to make the objects, technical solutions and advantages of the present invention more apparent, the present invention is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention.
Reference herein to "an embodiment" means that a particular feature, structure, or characteristic described in connection with the embodiment can be included in at least one embodiment of the application. The appearances of the phrase in various places in the specification are not necessarily all referring to the same embodiment, nor are separate or alternative embodiments mutually exclusive of other embodiments. It is explicitly and implicitly understood by one skilled in the art that the embodiments described herein can be combined with other embodiments.
Structured query language (SQL for short) is an important relational database operation language for accessing data and querying, updating, and managing relational databases; it allows the user to work on a high-level data structure. The method does not require a user to specify a data storage method and does not require the user to know a specific data storage mode, so that different database systems with completely different underlying structures can use the same structured query language as an interface for data input and management. The structured query language statements can be nested, which allows for great flexibility and powerful functionality.
The SQL execution method for logic deletion provided by the application can be applied to the computer equipment shown in figure 1. The computer device may be a terminal including, but not limited to, various personal computers, laptops, smart phones, tablets, and portable wearable devices.
In one embodiment, the internal structure of the computer device may be as shown in FIG. 1. The computer device includes a processor, a memory, a display screen, and an input device connected by a system bus. Wherein the processor of the computer device is configured to provide computing and control capabilities. The memory of the computer device comprises a nonvolatile storage medium and an internal memory. The non-volatile storage medium stores an operating system and a computer program. The internal memory provides an environment for the operation of an operating system and computer programs in the non-volatile storage medium. The computer program is executed by a processor to implement a method of SQL execution for logical deletion. The display screen of the computer equipment can be a liquid crystal display screen or an electronic ink display screen, and the input device of the computer equipment can be a touch layer covered on the display screen, a key, a track ball or a touch pad arranged on the shell of the computer equipment, an external keyboard, a touch pad or a mouse and the like.
Those skilled in the art will appreciate that the architecture shown in fig. 1 is merely a block diagram of some of the structures associated with the disclosed aspects and is not intended to limit the computing devices to which the disclosed aspects apply, as particular computing devices may include more or less components than those shown, or may combine certain components, or have a different arrangement of components.
In one embodiment, as shown in fig. 2, there is provided a method for SQL execution for logical deletion, comprising the following steps:
s11, obtaining the SQL sentence to be executed, and identifying the operation type of the SQL sentence to be executed.
The types of common SQL statements include: the Query type, the addition type, the modification type and the deletion type, wherein the statements corresponding to the Query type are also called Data Query Language (DQL), and the statements corresponding to the addition type, the modification type and the deletion type are also called Data Manipulation Language (DML).
For query types, its statements are also called "data retrieval statements" to obtain data from the data table, determining how the data is presented at the application. The reserved word SELECT is the most used verb, and other commonly used reserved words include WHERE, ORDER BY, GROUP BY, and HAVING. These reserved words are often used with other types of SQL statements. The statements corresponding to the add, modify, and DELETE types include verbs INSERT, UPDATE, and DELETE, respectively, which are used to add, modify, and DELETE records in the data table, respectively.
S12, when the operation type is deleting, updating or inquiring, according to a preset logic deleting mark in the entity class, identifying a logic deleting field in a data table for the SQL sentence to be executed; and a mapping relation exists between the entity class and the data table.
The entity class corresponds to a data table in a database, and a data table is established in the database based on a framework of a currently mainstream persistent layer (namely, a data access layer), so that a corresponding entity class needs to be established in a code according to a specification, and an attribute in the entity class maps a field of the data table.
Typically, the object targeted by the SQL statement is a data item in a data table, such as: SELECT name, count FROM Websites, the SQL statement refers to selecting "name" and "count" data items FROM the "Websites" table, that is, the SQL statement is directed to the data table "Websites". Because a mapping relation exists between the data table and the entity class, each SQL statement has the corresponding entity class.
In one embodiment, the implementation of this step includes: and when the operation type is deletion, update or query, determining the entity class corresponding to the SQL statement to be executed. And detecting a preset logic deleting mark in the entity class, and identifying a logic deleting field in a data table according to the logic deleting mark.
The logic deletion mark can be identified by a program executing SQL and is equivalent to an annotation of the attribute used for representing the logic deletion state in the entity class. Because the attribute of the entity class has a mapping relation with the field of the data table, the field for recording the logical deletion information in the data table can be identified.
S13, generating a corresponding SQL operation condition according to the logic deleting field, updating the SQL statement to be executed according to the SQL operation condition, and executing the updated SQL statement.
In an embodiment, the value corresponding to the logical deletion field is a logical value, and the SQL operation condition generated according to the logical deletion field includes two types, one type is a condition that the corresponding data item is logically deleted, and the other type is a condition that the corresponding data item is not logically deleted. And modifying the SQL sentence to be executed according to the generated SQL operation condition, namely adding consideration on logic deletion information of related data items in the original SQL sentence to be executed, and executing the updated SQL sentence to obtain a more accurate data result.
It is understood that the value corresponding to the logical delete field may also be other data types.
By the SQL execution method of the embodiment, when the user edits the SQL statement, the user does not need to pay attention to whether the data in the database is logically deleted, and the SQL operation on the logically deleted data can be realized through the conventional SQL statement, so that the SQL operation difficulty of the user is simplified, and the accuracy of the SQL operation result is improved.
In one embodiment, the SQL execution method of the present application further includes: creating an entity class corresponding to the data table, setting an attribute for representing logical deletion in the entity class, and setting a corresponding logical deletion mark for the attribute.
Through the logic deletion mark, the program can effectively identify the logic deletion field for recording the data logic deletion information, and the SQL statement to be executed is modified on the basis of the logic deletion field. It can be understood that, as shown in fig. 5, in an embodiment, if it is detected that the entity class does not include the preset logical deletion flag, the to-be-executed SQL statement is executed. In this case, the SQL statement to be executed is not converted, and the obtained SQL statement to be executed is directly executed without performing the steps S12 and S13.
Because if the entity class does not contain the preset logic deleting mark, the program cannot effectively identify the logic deleting field for recording the data logic deleting information, in this case, the original SQL sentence to be executed is executed, and the deviation of the SQL execution result caused by the error identification of the logic deleting field can be prevented.
In one embodiment, the SQL execution method of the present application further includes: after the step of determining the operation type of the SQL statement to be executed, if the operation type of the SQL statement to be executed is the new operation type, executing the SQL statement to be executed. That is, in this case, the to-be-executed SQL statement is not converted, and the acquired to-be-executed SQL statement is directly executed, and the steps S12 and S13 are not executed.
Because the newly added operation is irrelevant to the logic deletion state of the data, the SQL to be executed does not need to be modified, and the accuracy of the SQL execution result is not influenced.
In one embodiment, an attribute isdeled of a Boolean type is set in the entity class and used for representing the attribute of logic deletion; before the attribute isDeleted, a logical delete flag is set. For example:
the database has a data table "t _ user _ info" as:
correspondingly, a corresponding entity class needs to be created in the program, the attribute of the entity class corresponds to a field of the data table, and the entity class may be:
the logic deleting mark in the entity class is @ LogicDelete, the executed attribute is isDeleted, the attribute is mapped to an is _ deleted field in a data table, the logic deleting needs to be known first, and the @ LogicDelete is used for identifying the logic deleting field.
Based on the above example, in one embodiment, the step of identifying the logical delete field in the data table according to the logical delete flag includes: determining the attribute pointed by the logic deletion mark in the entity class, and obtaining the field corresponding to the attribute as the logic deletion field according to the mapping relation between the attribute in the entity class and the field in the data table. Therefore, the logic deletion field can be rapidly and accurately determined.
In one embodiment, the step of determining the entity class corresponding to the SQL statement to be executed includes: analyzing the SQL sentence to be executed according to a preset SQL analyzer to obtain a syntax tree corresponding to the SQL sentence to be executed; traversing the FROM node of the syntax tree and/or leaf nodes of the FROM node, and determining a data table for the SQL statement to be executed; and obtaining an entity class corresponding to the data table according to the mapping relation between the entity class and the data table, and determining the entity class as the entity class corresponding to the SQL sentence to be executed.
For example: if the to-be-executed SQL statement is an query statement, a delete statement, or an update statement, the to-be-executed SQL statement needs to be analyzed, an SQL parser in the open source tool drive may be used to parse the to-be-executed SQL statement, and the parsing tool may parse the SQL statement into a syntax tree. The result analyzed by the query SQL statement is an object of SelectStatement, the inside of the object mainly comprises SelectList, From and Where attributes, the structure analyzed by the update SQL statement is an object of UpdateStatement, and the inside of the object mainly comprises SetItem, From and Where attributes; the structure analyzed by deleting the SQL statement is an object of DeleteStatement, and the inside of the structure mainly comprises From and Where attributes. The syntax tree corresponding to the query SQL statement is shown in fig. 3, and the syntax tree corresponding to the update SQL statement is shown in fig. 4.
And through the syntax tree corresponding to the SQL statement, the entity class corresponding to the SQL statement to be executed can be quickly determined.
In an embodiment, the data of the corresponding node of the syntax tree can be modified in the process of traversing the syntax tree, so that the purpose of modifying the SQL to be executed is achieved.
In one embodiment, generating a corresponding SQL operating condition according to the logical deletion field, and updating the to-be-executed SQL statement according to the SQL operating condition includes:
if the operation type is updating or querying, generating a first operation condition of x.is _ delete ═ K1, and adding the first operation condition to the where attribute of the to-be-executed SQL statement; if the operation type is deletion, generating a second operation condition of x.is _ delete ═ K2, creating an SQL statement of an update type according to the to-be-executed SQL statement, and adding the second operation condition to a set attribute of the SQL statement of the update type.
Wherein, X represents a data item in a data table pointed by the to-be-executed SQL statement, is _ delete represents a logical deletion field, K1 and K2 represent preset logical values, when x.is _ delete is equal to K1, it represents that data X is not logically deleted, and when x.is _ delete is equal to K2, it represents that data X is logically deleted.
The following describes the update implementation of the SQL statement to be executed, taking query of the SQL statement and deletion of the SQL statement as examples:
suppose the query SQL statement to be executed is:
SELECT u.*,th.*ROM t_user_info u LEFT JOIN t_user_health th ON u.id＝th.user_id WHERE u.name＝‘test’；
analyzing the SQL statement to obtain a syntax tree as shown in FIG. 3, and when traversing to the SQLExpprTableSource node, obtaining the corresponding table name, because the entity class and the data table have a mapping relation, further finding the corresponding entity class; judging whether the entity class has the @ LogicDelete mark, if so, mapping a corresponding field name in the data table according to the attribute corresponding to the @ LogicDelete mark, and then adding a corresponding logic deletion condition to a Where node of the query SQL statement to be executed, for example, adding the is _ delete equal to 0 as the query condition. Based on this, the obtained updated SQL statement corresponding to the query SQL statement to be executed is:
SELECT u.*,th.*ROM t_user_info u LEFT JOIN t_user_health th ON u.id＝th.user_id WHERE u.name＝‘test’AND u.is_delete＝0AND th.is_delete＝0。
it can be understood that, if the to-be-executed SQL statement is an update SQL statement, the to-be-executed SQL statement is modified according to the same principle, so that data deleted logically cannot be modified, and the accuracy of the SQL update result is improved.
Suppose the delete SQL statement to be executed is:
delete from t_user_info u where u.name＝test’；
analyzing the SQL deleting statement to be executed to obtain a syntax tree as shown in FIG. 4; for delete operations, it is not possible to directly modify the contents of the node therein to update the delete SQL statement according to traversing the SQL syntax tree, because the root node is already different. In this regard, the parse tree corresponding to the updated SQL statement is generated in a manner of reconstructing the updatestate statement, because both From and Where are the same if the SQL statement is to be updated, when constructing the updatestate statement node, the value may be assigned according to From and Where of deletestement deleting the SQL statement, and the updatestate is one more SetItem node than the deletestement, which is the syntax tree node for updating the statement is _ deleted equal to 1 in the SQL statement. Based on which the delete statement of the original delete SQL statement may be converted into a delete statement in the logical delete. After a new SQL statement is generated, the original SQL statement is replaced to be executed, and the purpose of logic deletion can be achieved. Based on this, obtaining the updated SQL statement corresponding to the deleted SQL statement to be executed is:
update t_user_info u set u.is_deleted＝1where u.name＝test’。
in one embodiment, the SQL execution method of the present application may be implemented by: the method comprises the steps of writing a Mybatis plug-in advance according to Mybatis specifications, wherein the function of the plug-in is to modify an SQL statement to be executed, so that the aim of modifying the SQL statement to be executed is fulfilled. Mybatis is a relatively standard and flexible framework, provides entity classes, and can realize the purpose of modifying the SQL statement to be executed by providing an interface through the Mybatis plug-in, so that the SQL execution method for logic deletion can be conveniently realized.
It is understood that the above SQL execution method for logical deletion may also be implemented by other frameworks of the data access layer, for example, based on Hibernate or JDBC. Hibernate is packaged more, and an interface is not provided for rewriting SQL statements, so that the realization difficulty is high. The JDBC is written into SQL by itself for execution, and can implement rewriting of SQL statements by a dynamic proxy (a simulation mybatis plug-in) before executing SQL, but the JDBC does not perform any encapsulation, so to implement the SQL execution method of the present application, a user needs to add an entity class and add a logic deletion flag separately, but the dynamic proxy is not provided by the JDBC itself and needs to be implemented separately by the user.
By the embodiment, the SQL to be executed can be automatically converted, the configuration is simple, theoretically, the SQL sentence can be adapted to all SQL sentences, and a user does not need to pay attention to delete data or logic deletion data when writing the SQL.
It should be understood that for the foregoing method embodiments, although the steps in the flowcharts are shown in order indicated by the arrows, the steps are not necessarily performed in order indicated by the arrows. The steps are not performed in the exact order shown and described, and may be performed in other orders, unless explicitly stated otherwise. Moreover, at least a portion of the steps in the flow charts of the method embodiments may include multiple sub-steps or multiple stages that are not necessarily performed at the same time, but may be performed at different times, and the order of performance of the sub-steps or stages is not necessarily sequential, but may be performed in turn or alternating with other steps or at least a portion of the sub-steps or stages of other steps.
Based on the same idea as the SQL execution method for logical deletion in the above embodiments, the present document also provides an SQL execution device for logical deletion.
In one embodiment, as shown in fig. 6, the SQL executing apparatus for logical deletion of the embodiment includes:
the type identification module 501 is configured to acquire an SQL statement to be executed, and identify an operation type of the SQL statement to be executed;
a field identification module 502, configured to identify a logical deletion field in a data table for which an SQL statement to be executed is targeted according to a preset logical deletion flag in an entity class when the operation type is delete, update, or query; a mapping relation exists between the entity class and the data table;
and a modification execution module 503, configured to generate a corresponding SQL operating condition according to the logical deletion field, update the to-be-executed SQL statement according to the SQL operating condition, and execute the updated SQL statement.
In one embodiment, the field identification module 502 includes:
the class identification unit is used for determining an entity class corresponding to the SQL statement to be executed when the operation type is deletion, update or query; and the field identification unit is used for detecting a preset logic deletion mark in the entity class and identifying a logic deletion field in the data table according to the logic deletion mark.
In one embodiment, the SQL executing apparatus further includes: and the first execution module is used for executing the SQL statement to be executed if the fact that the entity class does not contain the preset logic deletion mark is detected.
In one embodiment, the SQL executing apparatus further includes: and the second execution module is used for executing the SQL sentence to be executed when the operation type of the SQL sentence to be executed is determined to be the newly added operation type.
In one embodiment, the class identification unit includes: the data table identifies the sub-unit and the class identifies the sub-unit.
The data table identification subunit is used for analyzing the SQL statement to be executed according to a preset SQL analyzer to obtain a syntax tree corresponding to the SQL statement to be executed; traversing the FROM node of the syntax tree and/or leaf nodes of the FROM node, and determining a data table for the SQL statement to be executed; and the class identification subunit is used for obtaining the entity class corresponding to the data table according to the mapping relation between the entity class and the data table, and determining the entity class as the entity class corresponding to the SQL sentence to be executed.
In one embodiment, the field identification unit includes: and the field determining subunit is used for determining the attribute pointed by the logical deletion mark in the entity class, and obtaining the field corresponding to the attribute as the logical deletion field according to the mapping relation between the attribute in the entity class and the field in the data table.
In one embodiment, the modification execution module 503 includes: a first modification unit and a second modification unit.
The first modifying unit is configured to generate a first operation condition of x.is _ delete ═ K1 if the operation type is update or query, and add the first operation condition to a where attribute of the to-be-executed SQL statement; a second modifying unit, configured to generate a second operation condition of x.is _ delete ═ K2 if the operation type is delete, create an SQL statement of an update type according to the to-be-executed SQL statement, and add the second operation condition to a set attribute of the SQL statement of the update type.
Wherein, X represents a data item in a data table pointed by the to-be-executed SQL statement, is _ delete represents a logical deletion field, K1 and K2 represent preset logical values, when x.is _ delete is equal to K1, it represents that data X is not logically deleted, and when x.is _ delete is equal to K2, it represents that data X is logically deleted.
By the SQL execution device of the embodiment, the SQL to be executed is automatically converted, and the SQL execution device can adapt to all SQL sentences. When the user edits the SQL statement, the SQL operation on the logically deleted data can be realized through the conventional SQL statement without paying attention to whether the data in the database is logically deleted, so that the SQL operation difficulty of the user is simplified, and the accuracy of the SQL operation result is improved.
For specific limitation on the SQL executing apparatus for logical deletion, reference may be made to the above limitation on the SQL executing method for logical deletion, and details are not described here. The modules in the SQL executing apparatus for logic deletion may be implemented in whole or in part by software, hardware, and a combination thereof. The modules can be embedded in a hardware form or independent from a processor in the computer device, and can also be stored in a memory in the computer device in a software form, so that the processor can call and execute operations corresponding to the modules.
In addition, in the above-described exemplary embodiment of the SQL executing apparatus for logic deletion, the logic division of each program module is only an example, and in practical applications, the above-described function distribution may be performed by different program modules according to needs, for example, due to configuration requirements of corresponding hardware or due to convenience of implementation of software, that is, the internal structure of the SQL executing apparatus for logic deletion is divided into different program modules to perform all or part of the above-described functions.
In one embodiment, a computer device is provided, which includes a memory and a processor, the memory stores a computer program, and the processor implements the steps of the SQL execution method for logical deletion of any of the above embodiments when executing the computer program.
In one embodiment, a computer readable storage medium is provided, on which a computer program is stored, which when executed by a processor implements the steps of the SQL execution method for logical deletion of any of the above embodiments.
It will be understood by those skilled in the art that all or part of the processes of the methods of the embodiments described above can be implemented by hardware instructions of a computer program, which can be stored in a non-volatile computer-readable storage medium, and when executed, can include the processes of the embodiments of the methods described above. Any reference to memory, storage, database, or other medium used in the embodiments provided herein may include non-volatile and/or volatile memory, among others. Non-volatile memory can include read-only memory (ROM), Programmable ROM (PROM), Electrically Programmable ROM (EPROM), Electrically Erasable Programmable ROM (EEPROM), or flash memory. Volatile memory can include Random Access Memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in a variety of forms such as Static RAM (SRAM), Dynamic RAM (DRAM), Synchronous DRAM (SDRAM), Double Data Rate SDRAM (DDRSDRAM), Enhanced SDRAM (ESDRAM), Synchronous Link DRAM (SLDRAM), Rambus Direct RAM (RDRAM), direct bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM).
The technical features of the above embodiments can be arbitrarily combined, and for the sake of brevity, all possible combinations of the technical features in the above embodiments are not described, but should be considered as the scope of the present specification as long as there is no contradiction between the combinations of the technical features.
In the above embodiments, the descriptions of the respective embodiments have respective emphasis, and for parts that are not described in detail in a certain embodiment, reference may be made to related descriptions of other embodiments.
The terms "comprises" and "comprising," as well as any variations thereof, of the embodiments herein are intended to cover non-exclusive inclusions. For example, a process, method, system, article, or apparatus that comprises a list of steps or (module) elements is not limited to only those steps or elements but may alternatively include other steps or elements not expressly listed or inherent to such process, method, article, or apparatus.
Reference herein to "a plurality" means two or more. "and/or" describes the association relationship of the associated objects, meaning that there may be three relationships, e.g., a and/or B, which may mean: a exists alone, A and B exist simultaneously, and B exists alone. The character "/" generally indicates that the former and latter associated objects are in an "or" relationship.
References to "first \ second" herein are merely to distinguish between similar objects and do not denote a particular ordering with respect to the objects, it being understood that "first \ second" may, where permissible, be interchanged with a particular order or sequence. It should be understood that "first \ second" distinct objects may be interchanged under appropriate circumstances such that the embodiments described herein may be practiced in sequences other than those illustrated or described herein.
The above-mentioned embodiments only express several embodiments of the present application, and the description thereof is more specific and detailed, but not construed as limiting the scope of the invention. It should be noted that, for a person skilled in the art, several variations and modifications can be made without departing from the concept of the present application, which falls within the scope of protection of the present application. Therefore, the protection scope of the present patent shall be subject to the appended claims.
Claims (10)
1. A method for SQL execution of logic deletion, comprising:
acquiring an SQL statement to be executed, and identifying the operation type of the SQL statement to be executed;
when the operation type is deletion, update or query, identifying a logic deletion field in a data table for the SQL statement to be executed according to a preset logic deletion mark in the entity class; a mapping relation exists between the entity class and the data table;
generating a corresponding SQL operation condition according to the logic deletion field, updating the SQL statement to be executed according to the SQL operation condition, and executing the updated SQL statement;
the step of generating a corresponding SQL operation condition according to the logic deletion field and updating the SQL statement to be executed according to the SQL operation condition comprises the following steps:
if the operation type is updating or querying, generating a first operation condition of x.is _ delete ═ K1, and adding the first operation condition to the where attribute of the to-be-executed SQL statement;
if the operation type is deletion, generating a second operation condition of X.is _ delete ═ K2, creating an SQL statement of an update type according to the SQL statement to be executed, and adding the second operation condition to a set attribute of the SQL statement of the update type;
wherein, X represents a data item in a data table pointed by the to-be-executed SQL statement, is _ delete represents a logical deletion field, K1 and K2 represent preset logical values, when x.is _ delete is equal to K1, it represents that data X is not logically deleted, and when x.is _ delete is equal to K2, it represents that data X is logically deleted.
2. The SQL execution method for logical deletion according to claim 1, wherein the step of identifying a logical deletion field in a data table for which the SQL statement is to be executed according to a logical deletion flag preset in the entity class includes:
determining an entity class corresponding to the SQL sentence to be executed;
and detecting a preset logic deleting mark in the entity class, and identifying a logic deleting field in a data table according to the logic deleting mark.
3. The SQL execution method for logical deletion according to claim 2, further comprising:
if the fact that the entity class does not contain a preset logic deleting mark is detected, executing the SQL sentence to be executed;
and/or the presence of a gas in the gas,
after the step of determining the operation type of the SQL statement to be executed, the method further includes:
and when the operation type of the SQL statement to be executed is the newly added operation type, executing the SQL statement to be executed.
4. The SQL execution method for logical deletion according to claim 2, wherein the step of determining the entity class corresponding to the SQL statement to be executed comprises:
analyzing the SQL sentence to be executed according to a preset SQL analyzer to obtain a syntax tree corresponding to the SQL sentence to be executed; traversing the FROM node of the syntax tree and/or leaf nodes of the FROM node, and determining a data table for the SQL statement to be executed;
and obtaining an entity class corresponding to the data table according to the mapping relation between the entity class and the data table, and determining the entity class as the entity class corresponding to the SQL sentence to be executed.
5. The SQL execution method for logical deletion according to claim 2, wherein the step of identifying a logical deletion field in the data table according to a logical deletion flag comprises:
determining the attribute pointed by the logic deletion mark in the entity class, and obtaining the field corresponding to the attribute as the logic deletion field according to the mapping relation between the attribute in the entity class and the field in the data table.
6. The SQL execution method for logical deletion according to claim 4 or 5, characterized by further comprising:
creating an entity class corresponding to the data table, setting an attribute for representing logic deletion in the entity class, and setting a corresponding logic deletion mark for the attribute.
7. The SQL execution method for logical deletion according to claim 6, wherein the step of setting the attribute for representing logical deletion in the entity class comprises:
and setting Boolean type attribute for representing logic deletion in the entity class, and setting a logic deletion mark @ LogicDelete for the attribute.
8. An SQL execution apparatus for logical deletion, comprising:
the type identification module is used for acquiring the SQL sentence to be executed and identifying the operation type of the SQL sentence to be executed;
the field identification module is used for identifying a logic deletion field in a data table for the SQL sentence to be executed according to a preset logic deletion mark in the entity class when the operation type is deletion, update or query; a mapping relation exists between the entity class and the data table;
the modification execution module is used for generating a corresponding SQL operation condition according to the logic deletion field, updating the SQL statement to be executed according to the SQL operation condition and executing the updated SQL statement;
the modification execution module comprises: a first modification unit and a second modification unit;
the first modifying unit is configured to generate a first operation condition of x.is _ delete ═ K1 if the operation type is update or query, and add the first operation condition to a where attribute of the to-be-executed SQL statement;
the second modifying unit is configured to generate a second operation condition of x.is _ delete ═ K2 if the operation type is delete, create an SQL statement of an update type according to the to-be-executed SQL statement, and add the second operation condition to a set attribute of the SQL statement of the update type;
wherein, X represents a data item in a data table pointed by the to-be-executed SQL statement, is _ delete represents a logical deletion field, K1 and K2 represent preset logical values, when x.is _ delete is equal to K1, it represents that data X is not logically deleted, and when x.is _ delete is equal to K2, it represents that data X is logically deleted.
9. A computer device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, wherein the steps of the method of any of claims 1 to 7 are performed when the program is executed by the processor.
10. A computer-readable storage medium, on which a computer program is stored which, when being executed by a processor, carries out the steps of the method of any one of claims 1 to 7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810225650.4A CN108563694B (en) | 2018-03-19 | 2018-03-19 | SQL execution method and device for logic deletion, computer equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810225650.4A CN108563694B (en) | 2018-03-19 | 2018-03-19 | SQL execution method and device for logic deletion, computer equipment and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN108563694A CN108563694A (en) | 2018-09-21 |
| CN108563694B true CN108563694B (en) | 2021-04-13 |
Family
ID=63531750
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201810225650.4A Active CN108563694B (en) | 2018-03-19 | 2018-03-19 | SQL execution method and device for logic deletion, computer equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN108563694B (en) |
Families Citing this family (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109271404A (en) * | 2018-10-25 | 2019-01-25 | 北京华宇信息技术有限公司 | Database is anti-error to delete method, apparatus, electronic equipment and computer-readable medium |
| CN110309196A (en) * | 2019-05-22 | 2019-10-08 | 深圳壹账通智能科技有限公司 | Block chain data storage and query method, apparatus, equipment and storage medium |
| CN112395302B (en) * | 2019-08-15 | 2025-02-18 | 阿里巴巴集团控股有限公司 | SQL instruction execution method, device, electronic device and computer readable medium |
| CN110515973B (en) * | 2019-08-30 | 2022-02-18 | 上海达梦数据库有限公司 | Data query optimization method, device, equipment and storage medium |
| CN114491673A (en) * | 2022-02-16 | 2022-05-13 | 中通服创立信息科技有限责任公司 | Method, system and storage medium for preventing data tampering |
| CN116010419A (en) * | 2023-02-07 | 2023-04-25 | 江西数字网联信息安全技术有限公司 | Method and device for creating unique index and optimizing logic deletion |
| CN116975032B (en) * | 2023-07-14 | 2024-04-12 | 南京领行科技股份有限公司 | Data alignment method, system, electronic device and storage medium |
Citations (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP0120185B1 (en) * | 1983-02-28 | 1992-01-02 | International Business Machines Corporation | Computing apparatus and method for translating into a linear query a graphic language query |
| CN103473296A (en) * | 2013-09-04 | 2013-12-25 | 国云科技股份有限公司 | A Recycle Bin Mechanism and System Applicable to Cloud Computing |
| CN103678494A (en) * | 2013-11-15 | 2014-03-26 | 北京奇虎科技有限公司 | Method and device for client side and server side data synchronization |
| CN105989195A (en) * | 2015-03-23 | 2016-10-05 | 国际商业机器公司 | Approach and system for processing data in database |
| CN107092685A (en) * | 2017-04-24 | 2017-08-25 | 广州新盛通科技有限公司 | A kind of method that file system and RDBMS store transaction data are used in combination |
| CN107111628A (en) * | 2014-10-31 | 2017-08-29 | 微软技术许可有限责任公司 | Effective maintenance of row repository index on memory optimization table |
| CN107168700A (en) * | 2017-04-30 | 2017-09-15 | 王栋 | Foundational development framework based on the SQL SELECT full-automatic list WEB background management systems driven |
| CN107193910A (en) * | 2017-05-14 | 2017-09-22 | 四川盛世天成信息技术有限公司 | A kind of database tamper resistant method and system applied to data safety class product |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US10083200B2 (en) * | 2013-03-14 | 2018-09-25 | Cavium, Inc. | Batch incremental update |
-
2018
- 2018-03-19 CN CN201810225650.4A patent/CN108563694B/en active Active
Patent Citations (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP0120185B1 (en) * | 1983-02-28 | 1992-01-02 | International Business Machines Corporation | Computing apparatus and method for translating into a linear query a graphic language query |
| CN103473296A (en) * | 2013-09-04 | 2013-12-25 | 国云科技股份有限公司 | A Recycle Bin Mechanism and System Applicable to Cloud Computing |
| CN103678494A (en) * | 2013-11-15 | 2014-03-26 | 北京奇虎科技有限公司 | Method and device for client side and server side data synchronization |
| CN107111628A (en) * | 2014-10-31 | 2017-08-29 | 微软技术许可有限责任公司 | Effective maintenance of row repository index on memory optimization table |
| CN105989195A (en) * | 2015-03-23 | 2016-10-05 | 国际商业机器公司 | Approach and system for processing data in database |
| CN107092685A (en) * | 2017-04-24 | 2017-08-25 | 广州新盛通科技有限公司 | A kind of method that file system and RDBMS store transaction data are used in combination |
| CN107168700A (en) * | 2017-04-30 | 2017-09-15 | 王栋 | Foundational development framework based on the SQL SELECT full-automatic list WEB background management systems driven |
| CN107193910A (en) * | 2017-05-14 | 2017-09-22 | 四川盛世天成信息技术有限公司 | A kind of database tamper resistant method and system applied to data safety class product |
Non-Patent Citations (1)
| Title |
|---|
| Delphi中如何恢复与物理删除Dbase数据库的记录;郑汉垣;《龙岩师专学报》;20020630;第20卷(第03期);第35~36页 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN108563694A (en) | 2018-09-21 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN108563694B (en) | SQL execution method and device for logic deletion, computer equipment and storage medium | |
| US11093490B2 (en) | Recommending data enrichments | |
| US8150882B2 (en) | Mapping from objects to data model | |
| US7743071B2 (en) | Efficient data handling representations | |
| US11334474B2 (en) | Fast change impact analysis tool for large-scale software systems | |
| WO2021217846A1 (en) | Interface data processing method and apparatus, and computer device and storage medium | |
| CN110134705A (en) | A data query method, cache server and terminal | |
| CN110471694A (en) | Annotation information processing method, device, computer equipment and storage medium | |
| AU2012271085A1 (en) | Recommending data enrichments | |
| CN109542966B (en) | Data fusion method and device, electronic equipment and computer readable medium | |
| CN112463792B (en) | Data authority control method and device | |
| CN110727777A (en) | Knowledge graph management method and device, computer equipment and storage medium | |
| US10503743B2 (en) | Integrating search with application analysis | |
| US20070061294A1 (en) | Source code file search | |
| WO2022127557A1 (en) | Code analysis method and related device | |
| WO2021022703A1 (en) | Software project reconstruction method and device, and computer device and storage medium | |
| CN115357590A (en) | Recording method and device for data change, electronic device and storage medium | |
| CN112182637A (en) | Safety control system, method, device and storage medium | |
| WO2021042532A1 (en) | Database information analysis method and apparatus, computer apparatus, and storage medium | |
| CN114371986A (en) | User interface test case update method, device and storage medium | |
| CN114238086B (en) | Memory leak detection method, device, computer equipment and storage medium | |
| CN113867975B (en) | A command line quick response method, device and computer equipment | |
| US11704094B2 (en) | Data integrity analysis tool | |
| US20240111523A1 (en) | Chained pull requests in a source code management system | |
| US11409729B2 (en) | Managing database object schema virtual changes |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |