CN113111239B - General database operation method, device and storage medium thereof - Google Patents
General database operation method, device and storage medium thereof Download PDFInfo
- Publication number
- CN113111239B CN113111239B CN202110378400.6A CN202110378400A CN113111239B CN 113111239 B CN113111239 B CN 113111239B CN 202110378400 A CN202110378400 A CN 202110378400A CN 113111239 B CN113111239 B CN 113111239B
- Authority
- CN
- China
- Prior art keywords
- current
- query
- constructor
- condition
- structured
- 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
- 238000000034 method Methods 0.000 title claims abstract description 65
- 239000012634 fragment Substances 0.000 claims description 32
- 238000012545 processing Methods 0.000 claims description 28
- 238000012795 verification Methods 0.000 claims description 20
- 238000010276 construction Methods 0.000 claims description 11
- 238000011017 operating method Methods 0.000 claims description 2
- 230000008569 process Effects 0.000 description 22
- 238000011161 development Methods 0.000 description 15
- 238000010586 diagram Methods 0.000 description 8
- 230000008859 change Effects 0.000 description 4
- 230000009471 action Effects 0.000 description 3
- 238000006243 chemical reaction Methods 0.000 description 3
- 230000008878 coupling Effects 0.000 description 3
- 238000010168 coupling process Methods 0.000 description 3
- 238000005859 coupling reaction Methods 0.000 description 3
- 230000002085 persistent effect Effects 0.000 description 3
- 238000004891 communication Methods 0.000 description 2
- 238000012217 deletion Methods 0.000 description 2
- 230000037430 deletion Effects 0.000 description 2
- 238000013461 design Methods 0.000 description 2
- 230000006870 function Effects 0.000 description 2
- 238000004321 preservation Methods 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000013507 mapping Methods 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/90—Details of database functions independent of the retrieved data types
- G06F16/903—Querying
- G06F16/90335—Query processing
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/90—Details of database functions independent of the retrieved data types
- G06F16/903—Querying
- G06F16/90335—Query processing
- G06F16/90348—Query processing by searching ordered data, e.g. alpha-numerically ordered data
Abstract
The application discloses a general database operation method, a device and a storage medium thereof, belonging to the field of software development. Judging whether the current preset query condition needs to be added with a non-empty field equal operation or not according to all fields of a current product database, whether the current preset query condition needs to be added with a non-equal special query operation or not, and/or whether the current query condition needs to be subjected to a sorting operation or not; and obtaining a current preset query condition according to the judging result and the corresponding field of the current product database, and generating a current query constructor by utilizing the current preset query condition. According to the query constructor dynamically generated according to different product databases, when the product query conditions are changed, only the front-end service is required to adjust the input parameters, the background is not required to be changed, the standardization and the universality of the background query interface are ensured, and the software development efficiency is improved.
Description
Technical Field
The present invention relates to the field of software development, and in particular, to a method and apparatus for operating a general database, and a storage medium thereof.
Background
The software development needs to interact with the database, a normal software system is adopted, the work such as business logic processing is removed, the rest eighty percent of work load is interacted with the database, and if the repeated work load of the part can be reduced to the minimum, the efficiency of the whole software development can be greatly improved. For java, the database operators in common use have the following.
The object relation mapping framework (Hibernate) of the open source code has the disadvantages of simple development process and high development efficiency, but poor execution performance and relatively laborious regulation of the structured query language (sql).
The persistent layer framework (Mybatis) of the open source database has the disadvantages of complex development process, a large number of repeated work and development efficiency and low efficiency, although the development process is flexible, the execution performance is better, and the SQL tuning is convenient.
The persistent layer framework enhancement tool (Mybatis-plus) of the open source database can simplify the operations of adding, deleting, modifying and searching the database, improve the development efficiency, and have good overall performance and development efficiency. However, this enhanced tool knowledge requires re-writing the structured query language according to the query conditions when the query conditions change.
Disclosure of Invention
Aiming at the problems in the prior art, the application mainly provides a general database operation method, a general database operation device and a general database storage medium, and the query constructor dynamically generated according to different product databases can adjust the input parameters only by front-end business when the product query conditions are changed.
In order to achieve the above purpose, a technical scheme adopted in the application is as follows: there is provided a general database operation method, comprising:
judging whether the current preset query condition needs to be added with a non-empty field equal operation or not according to all fields of the current product database, whether the current preset query condition needs to be added with a non-equal special query operation or not, and/or whether the current query condition needs to be subjected to a sorting operation or not; the method comprises the steps of,
and obtaining a current preset query condition according to the judging result and the corresponding field of the current product database, and generating a current query constructor by utilizing the current preset query condition.
The other technical scheme adopted by the application is as follows: there is provided a general database operating apparatus including:
the current preset query condition requirement judging module is used for judging whether the current preset query condition needs to be added with a non-empty field equal to operation according to all fields of the current product database, whether the current preset query condition needs to be added with a non-equal special query operation, and/or whether the current query condition needs to be subjected to a sorting operation;
The dynamic query constructor generating module is used for obtaining the current preset query condition according to the judging result and all the fields of the current product database, and generating the current query constructor by utilizing the current preset query condition.
The other technical scheme adopted by the application is as follows: there is provided a computer readable storage medium storing computer instructions operable to perform the general database operating method of the first aspect.
The beneficial effect that this application's technical scheme can reach is: by the query constructor dynamically generated according to different product databases, when the product query conditions are changed, only front-end business is required to adjust the input parameters, the background is not required to be changed, the standardization and the universality of a background query interface are ensured, and the software development efficiency is improved.
Drawings
In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, a brief description will be given below of the drawings that are needed in the embodiments or the prior art descriptions, it being obvious that the drawings in the following description are some embodiments of the present application, and that other drawings may be obtained according to these drawings without inventive effort to a person skilled in the art.
FIG. 1 is a flow diagram of one embodiment of a method of general database operation of the present application;
FIG. 2 is a flow diagram of one embodiment of a method of universal database operation of the present application;
FIG. 3 is a flow diagram of one embodiment of a method of universal database operation of the present application;
FIG. 4 is a flow diagram of one embodiment of a method of universal database operation of the present application;
FIG. 5 is a flow diagram of one embodiment of a method of universal database operation of the present application;
FIG. 6 is a schematic diagram of a general purpose repeatability verification process according to one embodiment of a general purpose database operation method of the present application;
FIG. 7 is a flowchart illustrating a method of processing a sub-table generic operation in one embodiment of a generic database operation method of the present application;
FIG. 8 is a schematic diagram of one embodiment of a generic database operating apparatus of the present application;
FIG. 9 is a schematic diagram of one embodiment of a generic database operating apparatus of the present application;
specific embodiments thereof have been shown by way of example in the drawings and will herein be described in more detail. These drawings and the written description are not intended to limit the scope of the inventive concepts in any way, but to illustrate the concepts of the present application to those skilled in the art by reference to specific embodiments.
Detailed Description
The preferred embodiments of the present application will be described in detail below with reference to the drawings so that the advantages and features of the present application can be more easily understood by those skilled in the art, thereby making a clearer and more definite definition of the protection scope of the present application.
It is noted that relational terms such as first and second, and the like are used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Moreover, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising … …" does not exclude the presence of other like elements in a process, method, article, or apparatus that comprises an element.
FIG. 1 illustrates one embodiment of a generic database operation method of the present application.
In the specific embodiment shown in fig. 1, the general database operation method disclosed in the present application includes a process S101, according to all fields of a current product database, determining whether a current preset query condition needs to be added with a non-null field equal to an operation, whether a current preset query condition needs to be added with a non-equal special query operation, and/or whether a current query condition needs to be sequenced; and a process S102, obtaining a current preset query condition according to the judging result and the corresponding field of the current product database, and generating a current query constructor by utilizing the current preset query condition.
According to the query constructor dynamically generated by different product databases, the purpose of automatic construction can be realized by only adjusting the input parameters by front-end business according to different query conditions of different products, the background is not required to be changed, the standardization and the universality of a background query interface are ensured, and the software development efficiency is improved.
The process S101 judges the preset query condition requirement of the current product requirement, and can facilitate further generating a query constructor according to the query condition requirement.
In a specific embodiment of the present application, all field attributes of the entity class are obtained according to all fields of the database corresponding to the current product, as shown in fig. 2, parameters transmitted from the front end are divided into common parameters and special parameters, where the common parameters are entity class fields, and obtaining all field attributes of the entity class can facilitate subsequent dynamic joining in query conditions.
In a specific embodiment of the present application, according to all field attributes of the entity class, whether a non-null field needs to be added, i.e. whether a non-null field of the entity class needs to be added, is determined in the current preset query condition, as shown in fig. 2, so as to obtain, according to a determination result, a preset query condition including or not including "adding a non-null field equals to an operation", which is further convenient for generating a query constructor.
In a specific embodiment of the present application, according to all fields of the database corresponding to the current product, whether the non-equal special query exists is determined, as shown in fig. 2, that is, whether there is a series of specified non-equal special operators of interval query, fuzzy match, empty/non-empty query, greater than, less than, etc. is determined, so that if the non-equal special query characters exist in the fields, a preset query condition including or not including "join non-equal special query operation" is obtained, and a query constructor is further generated.
In a specific embodiment of the present application, it is determined whether a sorting condition exists according to all fields of the database corresponding to the current product, as shown in fig. 2, preferably, it is determined whether a sorting operator exists in the fields, so that if the sorting operator exists in the fields, a preset query condition including or not including "add sorting operation" is obtained, and a query constructor is further generated.
And S102, obtaining a current preset query condition according to a judging result and corresponding fields of a current product database, generating a current query constructor by utilizing the current preset query condition, and correspondingly adjusting the preset query condition by only adjusting an incoming parameter by front-end service when the product query condition is changed, further generating the query constructor by utilizing the adjusted preset query condition, realizing the purpose of automatic construction, ensuring the standardization and universality of a background query interface and improving the software development efficiency without changing the background.
In a specific embodiment of the present application, the step S102 of obtaining the current preset query condition according to the determination result and the corresponding field of the current product database and generating the current query builder using the current preset query condition includes generating a structured query language (sql) segment of the current preset query condition according to the obtained current preset query condition and building the current query builder using the structured query language segment of the current preset query condition (the current preset condition sql segment).
In one embodiment of the present application, if a non-null field needs to be added, a structured query language (sql) fragment is added to the query constructor for which the non-null field of the corresponding entity class is equal to the query operation.
In a specific embodiment of the present application, if it is determined that a non-empty field needs to be added according to all field attributes of an entity class, as shown in fig. 2, a structured query language (sql) fragment corresponding to the non-empty field of the entity class and equal to a query operation is added to the query constructor, that is, all non-empty fields (common parameters returned from the front end) of the entity class are added to the constructor one by one according to the equal operation.
In a specific example of the present application, if it is determined that the non-null field is not required to be added according to all field attributes of the entity class, whether the non-equal special query operation is required to be added in the preset query condition is continuously determined, as shown in fig. 2.
In one embodiment of the present application, if a non-uniform special query operation needs to be added, a structured query language (sql) fragment of the corresponding non-uniform special query operation is added to the query builder according to the non-uniform special query character.
In a specific embodiment of the present application, if a series of specified special operators (i.e. non-equal special query characters) such as interval query, fuzzy match, empty/non-empty query, greater than, less than, etc. exist in all fields of a current product, then a corresponding non-equal special operation needs to be added, then the special operators are obtained and the operation type is judged, and a structured query language (sql) fragment corresponding to a different non-equal special operation is added to a query constructor according to a different non-equal special query character, as shown in fig. 2.
In a specific example of the present application, if no special operator exists in all the fields of the current product, no unequal special operation needs to be added, and then whether the ordering condition exists in the preset query condition is continuously determined, as shown in fig. 2.
In one embodiment of the present application, if a ranking operation is required, a structured query language (sql) fragment of the corresponding ranking operation is added to the query builder.
In one embodiment of the present application, if a sorting condition exists in all the fields of the current product, a structured query language (sql) fragment of the corresponding sorting operation is added to the query builder according to the corresponding field.
In a specific embodiment of the present application, if the current product data has a plurality of data tables, adding a table association relationship in the current query constructor to obtain a current table association structured query language (sql) fragment; and obtaining the alias attribute of each data table, and carrying out aliasing processing on the corresponding fields of different data tables corresponding to the current preset query condition.
A persistent layer framework enhancement tool (Mybat i s-pl us) of an open source database only operates on a single table, a large number of multi-table associated queries exist in one software system, however, the enhancement tool does not have the tool, the multi-table associated queries can only write sql queries in a Mybat mode, and the development efficiency is low. In addition, the large software system data volume is often stored by adopting a sub-library sub-table, and the enhancement tool has dynamic table name substitution, but has no method for directly operating the sub-table.
The multi-table association relation is added, so that a developer only needs to create a construction object according to a constructor format, the original mode that multi-table association inquiry can only be written down to the bottommost sql level by level according to the code level is replaced, the multi-table association condition constructor can be used for constructing the inquiry sql for execution at any code level, the code quantity is reduced, and the development efficiency is improved; and the current query constructor is generated by utilizing the current preset query conditions, when the product query conditions are changed, the preset query conditions can be correspondingly adjusted only by adjusting the input parameters by the front-end service, the query constructor is further generated by utilizing the adjusted preset query conditions, the purpose of automatic construction is realized, the background is not required to be changed, the standardization and the universality of a background query interface are ensured, and the software development efficiency is improved.
Fig. 3 shows a specific example of the present application, in which the product data of the present application has a plurality of data tables.
In one embodiment of the present application, if the current product data has multiple data tables, a table association relationship is added to the current query construction, as shown in the embodiment shown in fig. 3 and the embodiment shown in fig. 4, and a current table association sql segment is generated, and the current query constructor is constructed by using the current table association sql segment and the current preset condition sql segment.
In a specific embodiment of the present application, table association relationships may be dynamically added to implement multiple table association, where the table association relationships are: left association, right association, union association, intersection association, as shown in fig. 3.
In a specific example of the present application, as shown in fig. 4, alias attribute of each data table in the plurality of data tables is obtained, and the corresponding fields of the different data tables corresponding to the current preset query condition are aliased.
Preferably, as shown in fig. 3, the corresponding entity class field is aliased before the structured query language (sql) fragment, where the non-empty field of the corresponding entity class is equal to the query operation, is added to the query constructor.
Preferably, as shown in FIG. 3, the corresponding special query fields are aliased prior to adding the structured query language (sql) fragment of the corresponding non-uniform special query operation to the query builder based on the non-uniform special query characters.
Preferably, as shown in FIG. 3, the fields corresponding to the ordering are aliased before the structured query language (sql) fragments of the corresponding ordering operation are added to the query constructor according to the corresponding fields.
By utilizing the multi-table management relationship and the aliasing processing, the generated constructor can generate a query sql to execute according to the construction object, so that the encoding process is reduced, and the development efficiency is improved; when the product query conditions are changed, the query constructor can be automatically constructed and generated only by adjusting the input parameters by the front-end service, the background is not required to be changed, the standardization and the universality of the background query interface are ensured, and the software development efficiency is improved.
Fig. 5 illustrates a specific embodiment of the present application, where the process S503 indicates that the general database operation method of the present application further includes, according to the current query condition input by the user, generating a current structured query language by using the foregoing processing and parsing of the current query builder, and executing the query by using the current structured query language, so that the relevant query condition can be dynamically added according to the service requirement.
In a specific embodiment of the present application, the process of generating the current structured query language by using the current query builder to process and parse according to the current query condition input by the user further includes adding a ranking condition, where a general query generally has a ranking requirement, and dynamically adding the relevant ranking condition according to the service requirement.
In a specific embodiment of the present application, the process of generating the current structured query language by using the current query builder to process and parse according to the current query condition input by the user further includes adding a paging element, where a general query generally has a paging requirement, and the paging element may be added according to a service requirement.
In a specific embodiment of the present application, the process of processing the parsing to generate the current structured query language by using the current query builder according to the current query condition input by the user further includes parsing the sql segment: and analyzing the added query conditions, ordering conditions and paging elements to generate corresponding sql fragments.
In a specific embodiment of the present application, the processing, by the current query builder, the parsing to generate the current structured query language according to the current query condition input by the user further includes (k) generating an sql statement: and combining the table association sql segment, the query result sql segment, the query condition sql segment, the ordering condition sql segment and the paging element sql segment to generate a complete executable sql statement, namely the current structured query language.
In a specific embodiment of the application, the generated current structured query language is executed, and the execution result of the query sql is mapped and converted into an entity class object to be returned.
In one particular embodiment of the present application, the current structured query language is humped automatically prior to executing the query using the current structured query language.
The system adopting standardized design is adopted, the fields of the database table are in an underline format, after the fields in the program codes are converted by adopting humps, the underline is removed, letters behind the underline are changed into uppercase, the block is added with a hump automatic conversion function, the original query constructor can only be transmitted into the database field with the underline for query, the two modes of the fields after the underline and the hump are expanded, the fields are converted into the underline format before the sql is executed, the normal execution of the sql is ensured, and the compatibility of the query constructor is stronger.
In one embodiment of the present application, the general database operation method of the present application includes a general repeatability checking procedure, as shown in fig. 6.
In a specific embodiment of the application, constructing a current entity class query object to be checked according to a field which is required to be repeatedly checked currently; and obtaining a current repeatability verification structured query language (sql) fragment according to the field required to be subjected to the repeatability verification, the current entity class query object to be verified, the current id attribute of the current entity class query object to be verified and the deleted field in the field required to be subjected to the repeatability verification, wherein if the current id attribute of the current entity class query object to be verified is null, the current id attribute is ignored.
When a developer needs to judge the repeatability of the database table data, the developer only needs to simply call the method, and the program is not written independently every time, so that the coding process is simplified, the development efficiency is improved, and meanwhile, the codes are normalized.
In a specific embodiment of the present application, according to a field to be repeatedly checked, a current entity type query object to be currently checked, a current id attribute of the current entity type query object to be currently checked, and a deleted field in the field to be repeatedly checked, a process of obtaining a current repeatedly checked structured query language (sql) fragment includes, as shown in fig. 6, obtaining a current id attribute to be currently checked of the current entity type query object to be checked according to the current entity type query object to be checked, if the current id attribute to be currently checked is empty, ignoring the current id attribute to be checked, and obtaining a current repeatedly checked structured query language (sql) fragment according to the current entity type query object to be checked and the field to be currently checked to be repeatedly checked; and if the current id attribute to be checked is not null, eliminating the current id attribute to be checked, and obtaining a current repeatability checking structured query language (sql) fragment according to the current entity class query object to be checked and the current field needing to be subjected to repeatability checking.
In a specific embodiment of the present application, as shown in fig. 6, according to an incoming field needing to check repeatability, if the verifier includes deleted data, the current repeatability checking structured query language (sql) fragment is obtained by using the deleted field, the field needing to perform repeatability checking currently, the current entity class query object to be checked, and the current id attribute of the current entity class query object to be checked.
In a specific embodiment of the present application, the above-mentioned current repeatability verification structured query language (sql) fragment is utilized to connect with a database, execute a query, and determine whether to repeat according to the number of records of the query result. If the record number of the query result is larger than 0, the database is indicated to have the same record number, and the data is proved to be repeated; otherwise, equal to 0, the database has no identical record, and can be written normally.
In a specific embodiment of the present application, the general database operation method of the present application further includes a sub-table general operation method processing flow, as shown in fig. 7.
In one embodiment of the application, performing sub-table operation on a data table in a current product database, and caching each sub-table name into a field queue; when the current structured query language is utilized to execute the query, the indication in the current structured query language is replaced by the sub-table name, so that the sub-table operation is as simple as a normal table, the implementation of self-writing of sql is not needed, and the development efficiency is improved.
In one embodiment of the present application, incoming sub-table names are cached in the field queue when the sub-table operation method is entered, primarily for later retrieval of sub-table names from the queue for replacement.
In one embodiment of the present application, the general use of mybatis-plus save, bulk save, update, bulk update, delete, bulk delete, query, paging query, count, etc. is inherited, as shown in FIG. 7.
In a specific embodiment of the present application, before executing the sql statement, the sql statement is intercepted, data is fetched from the thread queue, if the data can be fetched, the description is a sub-table operation, and the table name in the sql statement needs to be replaced by the sub-table name, so that the operations of adding, deleting, changing and checking executed are performed on the sub-table.
FIG. 8 illustrates one embodiment of a generic database operating apparatus of the present application.
The general database operating device shown in fig. 8 includes a current preset query condition requirement judging module 801, configured to judge whether a current preset query condition needs to be added with a non-null field equal operation, whether a current preset query condition needs to be added with a non-equal special query operation, and/or whether a current query condition needs to be sequenced according to all fields of a current product database; and a dynamic query constructor generating module 802, configured to obtain a current preset query condition according to the determination result and all the fields of the current product database, and generate a current query constructor according to the current preset query condition.
In a specific embodiment of the present application, the foregoing current preset query condition requirement determining module 801 may obtain all field attributes of the entity class according to all fields of the database corresponding to the current product, and determine whether to add a non-null field, that is, whether to add a non-null field of the entity class, in the current preset query condition according to all field attributes of the entity class, as shown in fig. 2, so as to obtain a preset query condition including or not including "add a non-null field equals to an operation" according to a determination result, which is further convenient for generating a query constructor.
In a specific embodiment of the present application, the foregoing current preset query condition requirement determining module 801 may determine whether an unequal special query exists in the current product according to all fields of the corresponding database, so as to obtain a preset query condition including or not including "join unequal special query operation" if the unequal special query characters exist in the fields, and further generate the query constructor.
In a specific embodiment of the present application, the current preset query condition requirement determining module 801 determines whether a sorting condition exists according to all fields of the database corresponding to the current product, as shown in fig. 2, preferably, determines whether a sorting operator exists in the fields, so as to obtain a preset query condition including or not including "add sorting operation" if the sorting operator exists in the fields, and further generates a query constructor.
In a specific embodiment of the present application, the dynamic query builder generation module 802 may generate a structured query language (sql) segment of the current preset query condition according to the current preset query condition, and build the current query builder using the structured query language segment of the current preset query condition (the current preset condition sql segment).
In a specific embodiment of the present application, the dynamic query builder generation module 802 may add, as required, a non-null field judgment result, and then add a structured query language (sql) fragment with a non-null field of a corresponding entity class equal to the query operation in the query builder.
In a specific embodiment of the present application, the dynamic query builder generation module 802 may add, according to needs, a determination result of the non-equal special query operation, and add, according to non-equal special query characters, a structured query language (sql) fragment of the corresponding non-equal special query operation to the query builder.
In a specific embodiment of the present application, the dynamic query builder generation module 802 may add a structured query language (sql) fragment of the corresponding ranking operation to the query builder according to the determination result of the ranking operation.
The query condition constructor can be initialized according to the parameters transmitted by the front end of the webpage, when the query conditions change due to the service change requirement, the parameters transmitted by the front end of the webpage are only required to be adjusted, the background interface is not required to change, and the universality of the query interface is ensured.
In a specific embodiment of the present application, the dynamic query builder generation module includes a multi-table association relationship construction unit and an aliasing processing unit, as shown in fig. 9, where the multi-table association relationship construction unit is configured to add a multi-table association relationship to a current query builder to obtain a current table association structured query language (sql) fragment if the current product data has multiple data tables; the aliasing processing unit is used for acquiring the aliasing attribute of each data table, and carrying out aliasing processing on corresponding fields of different data tables corresponding to the current preset query condition.
In a specific embodiment of the present application, the multi-table association relationship construction unit may dynamically add a table association relationship to implement a plurality of table associations, where the association relationship between tables is: left association, right association, union association, intersection association, as shown in fig. 4.
In a specific embodiment of the present application, the aliasing processing unit obtains an aliasing attribute of each data table in the plurality of data tables and performs aliasing processing on corresponding fields of different data tables corresponding to a current preset query condition.
In a specific example of the present application, the aliasing processing unit performs, as shown in fig. 4, aliasing processing on a corresponding entity class field before adding a structured query language (sql) fragment in which a non-empty field of the corresponding entity class is equal to a query operation in a query constructor.
In one embodiment of the present application, the aliasing processing unit performs, as shown in fig. 4, aliasing processing on the corresponding special query field before adding a structured query language (sql) fragment of the corresponding non-equal special query operation to the query builder according to the non-equal special query character.
In a specific example of the present application, the above-mentioned aliasing processing unit performs, as shown in fig. 4, aliasing processing on the fields corresponding to the ranks before adding the structured query language (sql) fragment of the corresponding ranking operation in the query constructor according to the corresponding fields.
By utilizing the multi-table management relationship and the aliasing processing, the generated constructor can generate a query sql to execute according to the construction object, so that the encoding process is reduced, and the development efficiency is improved; when the product query conditions are changed, the query constructor can be automatically constructed and generated only by adjusting the input parameters by the front-end service, the background is not required to be changed, the standardization and the universality of the background query interface are ensured, and the software development efficiency is improved.
In a specific embodiment of the present application, as shown in a module 903 in fig. 9, the general operating device for a database of the present application further includes a current query execution module, configured to process and parse to generate a current structured query language according to a current query condition input by a user by using a current query constructor, and execute a query by using the current structured query language, so that relevant query conditions can be dynamically added according to a service requirement.
In a specific embodiment of the present application, the current query execution module 903 can add a ranking condition, and general queries generally have a ranking requirement, and can dynamically add a relevant ranking condition according to a service requirement.
In one embodiment of the present application, the current query execution module 903 may add a paging element, which is generally required for general queries and may be added according to the service requirement.
In a specific embodiment of the present application, the current query execution module 903 analyzes the added query conditions, ordering conditions, and paging elements to generate corresponding sql segments, and combines the table-associated sql segments, the query result sql segments, the query condition sql segments, the ordering condition sql segments, and the paging element sql segments to generate a complete executable sql statement, i.e., the current structured query language.
In one embodiment of the present application, the current query execution module 903 performs hump auto-conversion on the current structured query language before executing the query using the current structured query language.
The system adopting standardized design is adopted, the fields of the database table are in an underline format, after the fields in the program codes are converted by adopting humps, the underline is removed, letters behind the underline are changed into uppercase, the block is added with a hump automatic conversion function, the original query constructor can only be transmitted into the database field with the underline for query, the two modes of the fields after the underline and the hump are expanded, the fields are converted into the underline format before the sql is executed, the normal execution of the sql is ensured, and the compatibility of the query constructor is stronger.
In a specific embodiment of the present application, the general database operation device further includes a general repeatability calibration module, configured to construct a current entity class query object to be verified according to a field that is required to be subjected to repeatability verification; obtaining a current repeatability verification structured query language (sql) fragment according to a field required to be subjected to repeatability verification, a current entity class query object to be verified, a current id attribute of the current entity class query object to be verified and deleted fields in the field required to be subjected to repeatability verification; and if the current id attribute of the current entity class query object to be checked is null, ignoring the current id attribute.
Therefore, when a developer needs to judge the repeatability of the database table data, the developer only needs to simply call the method, and does not write a program independently every time, so that the coding process is simplified, the development efficiency is improved, and meanwhile, the codes are standardized.
In a specific embodiment of the present application, as shown in fig. 6, the general repeatability calibration module may obtain a current id attribute to be checked of a current entity class query object to be checked according to the current entity class query object to be checked, if the current id attribute to be checked is null, ignore the current id attribute to be checked, and obtain a current repeatability check structured query language (sql) fragment according to the current entity class query object to be checked and a field to be subjected to repeatability check currently; and if the current id attribute to be checked is not null, eliminating the current id attribute to be checked, and obtaining a current repeatability checking structured query language (sql) fragment according to the current entity class query object to be checked and the current field needing to be subjected to repeatability checking.
In a specific embodiment of the present application, as shown in fig. 6, the general repeatability verification module can verify whether the field with repeatability is required according to the incoming data, if so, the verifier includes deleted data, and if so, the current repeatability verification structured query language (sql) fragment is obtained by using the deleted field and the field currently required to be subjected to repeatability verification, the current entity class query object to be verified, and the current id attribute of the current entity class query object to be verified.
In a specific embodiment of the present application, the general repeatability verification module may use the current repeatability verification structured query language (sql) segment to connect with a database, execute a query, and determine whether to repeat according to the number of records of the query result. If the record number of the query result is larger than 0, the database is indicated to have the same record number, and the data is proved to be repeated; otherwise, equal to 0, the database has no identical record, and can be written normally.
In a specific embodiment of the present application, the general database operation device further includes a sub-table general operation module, as shown in fig. 7, configured to perform a sub-table operation on a data table in a current product database, and cache each sub-table name into a field queue; when executing a query using the current structured query language, the manifest in the current structured query language is replaced with the sub-table name.
Therefore, the sub-table operation can be as simple as the normal table, the sql is not required to be written by oneself, and the development efficiency is improved.
In a specific embodiment of the present application, the above general purpose operation module for sub-tables can inherit general purpose usage such as mybatis-plus preservation, batch preservation, update, batch update, deletion, batch deletion, inquiry, paging inquiry, counting, etc., as shown in fig. 7, and intercept sql statement before the sql statement, and fetch data from a thread queue, if the data can be fetched, it indicates that the sub-table operation is performed, and if the table name in the sql statement needs to be replaced by the sub-table name, so that the operations of adding, deleting, changing, and checking performed are performed on the sub-table.
In a specific embodiment of the present application, the functional modules in a generic database operating device of the present application may be directly in hardware, in a software module executed by a processor, or in a combination of the two.
A software module may reside in RAM memory, flash memory, ROM memory, EPROM memory, EEPROM memory, registers, hard disk, a removable disk, a CD-ROM, or any other form of storage medium known in the art. An exemplary storage medium is coupled to the processor such the processor can read information from, and write information to, the storage medium.
The processor may be a central processing unit (English: central Processing Unit; CPU; for short), or other general purpose processor, digital signal processor (English: digital Signal Processor; for short DSP), application specific integrated circuit (English: application Specific Integrated Circuit; ASIC; for short), field programmable gate array (English: field Programmable Gate Array; FPGA) or other programmable logic device, discrete gate or transistor logic, discrete hardware components, or any combination thereof, etc. A general purpose processor may be a microprocessor, but in the alternative, the processor may be any conventional processor, controller, microcontroller, or state machine. A processor may also be implemented as a combination of computing devices, e.g., a combination of a DSP and a microprocessor, a plurality of microprocessors, one or more microprocessors in conjunction with a DSP core, or any other such configuration. In the alternative, the storage medium may be integral to the processor. The processor and the storage medium may reside in an ASIC. The ASIC may reside in a user terminal. In the alternative, the processor and the storage medium may reside as discrete components in a user terminal.
In another embodiment of the present application, a computer-readable storage medium stores computer instructions operable to perform a method of general database operations in scheme one.
In the several embodiments provided in this application, it should be understood that the disclosed apparatus and method may be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative, e.g., the division of elements is merely a logical functional division, and there may be additional divisions of actual implementation, e.g., multiple elements or components may be combined or integrated into another system, or some features may be omitted, or not performed. Alternatively, the coupling or direct coupling or communication connection shown or discussed with each other may be an indirect coupling or communication connection via some interfaces, devices or units, which may be in electrical, mechanical or other form.
The units described as separate units may or may not be physically separate, and units shown as units may or may not be physical units, may be located in one place, or may be distributed over a plurality of network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.
The foregoing is only examples of the present application, and is not intended to limit the scope of the patent application, and all equivalent structural changes made by the specification and drawings of the present application, or direct or indirect application in other related technical fields, are included in the scope of the patent protection of the present application.
Claims (10)
1. A general database operation method is characterized by comprising the following steps of,
judging whether a current preset query condition needs to be added with a non-empty field equal operation according to all fields of a current product database, whether the current preset query condition needs to be added with a non-equal special query operation, and/or whether the current preset query condition needs to be subjected to a sorting operation; the method comprises the steps of,
obtaining the current preset query condition according to the judging result and the corresponding field of the current product database, and generating a current query constructor by utilizing the current preset query condition;
the generating the current query constructor by using the current preset query condition includes generating a structured query language segment corresponding to the current preset query condition according to the current preset query condition, and constructing the current query constructor by using the structured query language segment of the current preset query condition.
2. The method of claim 1, further comprising,
and processing and analyzing by utilizing the current query constructor according to the current query condition input by the user to generate a current structured query language, and executing the query by utilizing the current structured query language.
3. The method according to claim 1 or 2, wherein the step of obtaining the current preset query condition from the determination result and the all fields of the current product database comprises,
if the current product data has a plurality of data tables, adding a multi-table association relation into the current query constructor to obtain a current table association structured query language fragment; the method comprises the steps of,
and acquiring the alias attribute of each data table, and carrying out aliasing processing on the corresponding fields of different data tables corresponding to the current preset query condition.
4. The method of claim 2, further comprising,
and before executing the query by using the current structured query language, automatically converting humps by using the current structured query language.
5. The method of operating a generic database according to claim 1 or 2, further comprising,
constructing a current entity class query object to be checked according to a field needing to be repeatedly checked; the method comprises the steps of,
obtaining a current repeatability verification structured query language fragment according to the field required to be subjected to the repeatability verification, the current entity class query object to be verified, the current id attribute of the current entity class query object to be verified and deleted fields in the field required to be subjected to the repeatability verification;
and if the current id attribute of the current entity class query object to be checked is null, ignoring the current id attribute.
6. The method of claim 2, further comprising,
performing sub-table operation on the data table in the current product database, and caching each sub-table name into a field queue; the method comprises the steps of,
and when the current structured query language is utilized to execute the query, replacing the indication in the current structured query language with the sub-table name.
7. A universal database operating device, comprising
The current preset query condition requirement judging module is used for judging whether the current preset query condition needs to be added with non-empty fields to be equal to operation according to all fields of a current product database, whether the current preset query condition needs to be added with non-equal special query operation, and/or whether the current preset query condition needs to be subjected to sorting operation; the method comprises the steps of,
the dynamic query constructor generating module is used for obtaining the current preset query conditions according to the judging result and all the fields of the current product database and generating a current query constructor by utilizing the current preset query conditions;
the generating the current query constructor by using the current preset query condition includes generating a structured query language segment corresponding to the current preset query condition according to the current preset query condition, and constructing the current query constructor by using the structured query language segment of the current preset query condition.
8. The universal database operating apparatus according to claim 7, further comprising,
the current query execution module is used for processing and analyzing by utilizing the current query constructor according to the current query condition input by the user to generate a current structured query language, and executing the query by utilizing the current structured query language; the dynamic query constructor generating module comprises a multi-table association relation constructing unit and an aliasing processing unit;
The multi-table association relation construction unit is used for adding a multi-table association relation into the current query constructor to obtain a current table association structured query language fragment if the current product data has a plurality of data tables;
the aliasing processing unit is configured to obtain an aliasing attribute of each data table, and perform aliasing processing on the corresponding fields of different data tables corresponding to the current preset query condition.
9. The universal database operating apparatus according to claim 8, further comprising,
the universal repeatability calibration module is used for constructing a current entity class query object to be checked according to the field required to be subjected to repeatability check; the field to be subjected to repeatability verification is used for inquiring the object according to the current entity class to be verified, the current id attribute of the current entity class to be verified inquires the object, and deleted fields in the field to be subjected to repeatability verification are used for obtaining a current repeatability verification structured inquiry language fragment; if the current id attribute of the current entity class query object to be checked is null, ignoring the current id attribute; the method comprises the steps of,
The sub-table general operation module is used for performing sub-table operation on the data table in the current product database and caching each sub-table name into a field queue; and when the current structured query language is utilized to execute the query, replacing the indication in the current structured query language with the sub-table name.
10. A computer readable storage medium storing computer instructions operable to perform the general database operating method of any one of claims 1 to 6.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110378400.6A CN113111239B (en) | 2021-04-08 | 2021-04-08 | General database operation method, device and storage medium thereof |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110378400.6A CN113111239B (en) | 2021-04-08 | 2021-04-08 | General database operation method, device and storage medium thereof |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN113111239A CN113111239A (en) | 2021-07-13 |
| CN113111239B true CN113111239B (en) | 2024-03-29 |
Family
ID=76715241
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110378400.6A Active CN113111239B (en) | 2021-04-08 | 2021-04-08 | General database operation method, device and storage medium thereof |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113111239B (en) |
Families Citing this family (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115587095A (en) * | 2022-10-08 | 2023-01-10 | 广州市玄武无线科技股份有限公司 | Database sub-table creating method and device and storage medium |
| CN116521686B (en) * | 2023-05-22 | 2023-10-27 | 美云智数科技有限公司 | Dynamic data table processing method, device, computer equipment and storage medium |
Citations (18)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CA2534334A1 (en) * | 2005-02-28 | 2006-08-28 | Microsoft Corporation | Composable query building api and query language |
| CN1904884A (en) * | 2005-07-29 | 2007-01-31 | 株式会社理光 | Graph inquiring structuring apparatus for isomerization media and method thereof |
| CN102346785A (en) * | 2011-11-15 | 2012-02-08 | 北京创腾科技有限公司 | Method and device for directly self-defining field of database |
| CN102799644A (en) * | 2012-06-28 | 2012-11-28 | 用友软件股份有限公司 | Dynamic database query system and method based on metadata |
| CN103020064A (en) * | 2011-09-20 | 2013-04-03 | 佳都新太科技股份有限公司 | Method and configuration for generating query condition in annotation way |
| CN104317964A (en) * | 2014-11-14 | 2015-01-28 | 中国建设银行股份有限公司 | Object-relational mapping method and system based on iBatis |
| CN105843609A (en) * | 2016-03-18 | 2016-08-10 | 浪潮软件集团有限公司 | MVC frame based on Spring and MyBatis |
| CN106020847A (en) * | 2016-06-06 | 2016-10-12 | 北京京东尚科信息技术有限公司 | Method and device for configuring SQL for persistent layer development framework |
| CN106528674A (en) * | 2016-10-31 | 2017-03-22 | 厦门服云信息科技有限公司 | Method and device for high-performance query based on Hbase row keys |
| CN106844678A (en) * | 2017-01-24 | 2017-06-13 | 山东浪潮商用系统有限公司 | A kind of exchange method of Mybatis data sources and connection pool |
| CN108280082A (en) * | 2017-01-06 | 2018-07-13 | 北京京东尚科信息技术有限公司 | A kind of extemporaneous querying method and system of statistical data |
| CN110858202A (en) * | 2018-08-21 | 2020-03-03 | 北京京东尚科信息技术有限公司 | Method and device for generating where clause in database query statement |
| CN111274263A (en) * | 2020-01-13 | 2020-06-12 | 平安国际智慧城市科技股份有限公司 | Visual database change statement generation method and device and storage medium |
| CN111581231A (en) * | 2020-04-20 | 2020-08-25 | 北京明略软件系统有限公司 | Query method and device based on heterogeneous database |
| CN111611276A (en) * | 2020-06-05 | 2020-09-01 | 腾讯科技（深圳）有限公司 | Data query method, device and storage medium |
| CN111966340A (en) * | 2020-08-20 | 2020-11-20 | 杭州安恒信息技术股份有限公司 | Code generation method, device, equipment and medium based on Mybatis framework |
| CN111984669A (en) * | 2020-07-08 | 2020-11-24 | 福建亿能达信息技术股份有限公司 | Functional SQL query method, device, equipment and medium supporting dynamic variables |
| CN112035510A (en) * | 2020-08-28 | 2020-12-04 | 中电长城(长沙)信息技术有限公司 | Complex data query method and system for information system |
Family Cites Families (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7953722B2 (en) * | 2007-12-21 | 2011-05-31 | Sap Ag | Query response service for business objects |
| US8375014B1 (en) * | 2008-06-19 | 2013-02-12 | BioFortis, Inc. | Database query builder |
| US8655867B2 (en) * | 2010-05-13 | 2014-02-18 | Salesforce.Com, Inc. | Method and system for optimizing queries in a multi-tenant database environment |
| US20170076054A1 (en) * | 2015-09-10 | 2017-03-16 | Iris International, Inc. | Particle analysis systems and methods |
| EP3430541A1 (en) * | 2016-03-14 | 2019-01-23 | TriNetX, Inc. | Querying data using master terminology data model |
| US11409742B2 (en) * | 2018-12-06 | 2022-08-09 | Salesforce, Inc. | Efficient database searching for queries using wildcards |
-
2021
- 2021-04-08 CN CN202110378400.6A patent/CN113111239B/en active Active
Patent Citations (18)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CA2534334A1 (en) * | 2005-02-28 | 2006-08-28 | Microsoft Corporation | Composable query building api and query language |
| CN1904884A (en) * | 2005-07-29 | 2007-01-31 | 株式会社理光 | Graph inquiring structuring apparatus for isomerization media and method thereof |
| CN103020064A (en) * | 2011-09-20 | 2013-04-03 | 佳都新太科技股份有限公司 | Method and configuration for generating query condition in annotation way |
| CN102346785A (en) * | 2011-11-15 | 2012-02-08 | 北京创腾科技有限公司 | Method and device for directly self-defining field of database |
| CN102799644A (en) * | 2012-06-28 | 2012-11-28 | 用友软件股份有限公司 | Dynamic database query system and method based on metadata |
| CN104317964A (en) * | 2014-11-14 | 2015-01-28 | 中国建设银行股份有限公司 | Object-relational mapping method and system based on iBatis |
| CN105843609A (en) * | 2016-03-18 | 2016-08-10 | 浪潮软件集团有限公司 | MVC frame based on Spring and MyBatis |
| CN106020847A (en) * | 2016-06-06 | 2016-10-12 | 北京京东尚科信息技术有限公司 | Method and device for configuring SQL for persistent layer development framework |
| CN106528674A (en) * | 2016-10-31 | 2017-03-22 | 厦门服云信息科技有限公司 | Method and device for high-performance query based on Hbase row keys |
| CN108280082A (en) * | 2017-01-06 | 2018-07-13 | 北京京东尚科信息技术有限公司 | A kind of extemporaneous querying method and system of statistical data |
| CN106844678A (en) * | 2017-01-24 | 2017-06-13 | 山东浪潮商用系统有限公司 | A kind of exchange method of Mybatis data sources and connection pool |
| CN110858202A (en) * | 2018-08-21 | 2020-03-03 | 北京京东尚科信息技术有限公司 | Method and device for generating where clause in database query statement |
| CN111274263A (en) * | 2020-01-13 | 2020-06-12 | 平安国际智慧城市科技股份有限公司 | Visual database change statement generation method and device and storage medium |
| CN111581231A (en) * | 2020-04-20 | 2020-08-25 | 北京明略软件系统有限公司 | Query method and device based on heterogeneous database |
| CN111611276A (en) * | 2020-06-05 | 2020-09-01 | 腾讯科技（深圳）有限公司 | Data query method, device and storage medium |
| CN111984669A (en) * | 2020-07-08 | 2020-11-24 | 福建亿能达信息技术股份有限公司 | Functional SQL query method, device, equipment and medium supporting dynamic variables |
| CN111966340A (en) * | 2020-08-20 | 2020-11-20 | 杭州安恒信息技术股份有限公司 | Code generation method, device, equipment and medium based on Mybatis framework |
| CN112035510A (en) * | 2020-08-28 | 2020-12-04 | 中电长城(长沙)信息技术有限公司 | Complex data query method and system for information system |
Non-Patent Citations (3)
| Title |
|---|
| Multi-field query expansion is effective for biomedical dataset retrieval;Mohamed Reda Bouadjenek;《Database》;20170907;第2017卷;1-20 * |
| 基于Java的Web通用查询组件设计及应用;舒红平;刘魁;;成都信息工程学院学报(第03期);361-362页 * |
| 面向领域模型的数据访问层的应用和关键技术研究;汪晟杰;《中国优秀硕士学位论文全文数据库 信息科技》;20080615;I138-202 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN113111239A (en) | 2021-07-13 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN113111239B (en) | General database operation method, device and storage medium thereof | |
| US6748374B1 (en) | Method for generating a relational database query statement using one or more templates corresponding to search conditions in an expression tree | |
| US7634460B2 (en) | Computer-implemented data replacement graphical user interface system and method | |
| US8417690B2 (en) | Automatically avoiding unconstrained cartesian product joins | |
| US9930113B2 (en) | Data retrieval via a telecommunication network | |
| US20080183684A1 (en) | Caching an Access Plan for a Query | |
| CN110362593B (en) | Data query method, device, equipment and storage medium | |
| CN114357276A (en) | Data query method and device, electronic equipment and storage medium | |
| CN107145549B (en) | Database cache control method and system | |
| CN111026775A (en) | Method and device for determining correlation index, server and storage medium | |
| CN111198898B (en) | Big data query method and big data query device | |
| CN114443691B (en) | Database query optimization method, system and computer equipment | |
| CN112307062B (en) | Database aggregation query method, device and system | |
| KR102172138B1 (en) | Distributed Computing Framework and Distributed Computing Method | |
| CN109783498B (en) | Data processing method and device, electronic equipment and storage medium | |
| CN116502273B (en) | Dynamic data desensitization method, device and equipment based on data blood edges | |
| CN112434037A (en) | Data processing method, processing device, data processing apparatus, and storage medium | |
| CN111831684B (en) | Data query method, device and computer readable storage medium | |
| US20230141190A1 (en) | Late Materialization of Queried Data in Database Cache | |
| CN114036178A (en) | Service interface generation method, device, computer and readable storage medium | |
| CN111143398B (en) | Extra-large set query method and device based on extended SQL function | |
| CN114138821A (en) | Database query method, system and electronic equipment | |
| CN113779362A (en) | Data searching method and device | |
| CN113934430A (en) | Data retrieval analysis method and device, electronic equipment and storage medium | |
| CN113204558A (en) | Method and device for automatically updating data table structure |
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 |