CN111666293A - Database access method and device - Google Patents
Database access method and device Download PDFInfo
- Publication number
- CN111666293A CN111666293A CN201910163475.5A CN201910163475A CN111666293A CN 111666293 A CN111666293 A CN 111666293A CN 201910163475 A CN201910163475 A CN 201910163475A CN 111666293 A CN111666293 A CN 111666293A
- Authority
- CN
- China
- Prior art keywords
- field
- entity object
- sql
- request
- condition
- 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.)
- Pending
Links
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a database access method and a database access device, and relates to the technical field of computers. The method is used for a database access device adopting a front-end and back-end separated micro-service architecture, and comprises the following steps: after receiving a database access request of a front-end page, encapsulating page data carried by the request into a first entity object, and transmitting the first entity object to a service layer; analyzing the first entity object, and then dynamically generating an SQL statement according to an analysis result; packaging the SQL statement into a second entity object, and transmitting the second entity object to a data persistence layer; and analyzing the SQL statement from the second entity object and executing the SQL statement so as to realize the access to the database. Through the steps, the SQL can be dynamically generated in the memory based on the page request, the SQL statements are completely decoupled from the back-end configuration file, and the number of times of restarting the server caused by the change of the demand is effectively reduced.
Description
Technical Field
The invention relates to the technical field of computers, in particular to a database access method and a database access device.
Background
At present, development service systems in various fields rarely access databases. A more common scenario is where a user accesses a database through a front-end page. For example, the user performs query, update, deletion or addition operation on the data in the database based on the data items of the front-end page.
The prior technical scheme for accessing the database comprises the following steps: a developer writes SQL sentences in advance according to data items of a front-end page, and stores the SQL sentences in a configuration file of a persistent layer frame (such as Mybatis); and after receiving a database access request of a front-end page, realizing access operation on a database table by means of SQL statements in the persistent layer framework configuration file.
In the process of implementing the invention, the inventor finds that at least the following problems exist in the prior art: in the existing technical scheme for accessing the database, the data items of the front-end page and the SQL in the persistent layer framework configuration file are not decoupled, if a business or product worker puts forward a new requirement and needs to newly add a query condition or a query result, a developer needs to modify the SQL sentences in the front-end page and the configuration file at the same time, so that the development workload is huge, and the development efficiency is low; moreover, in the existing technical solution for accessing the database, after the SQL statement in the backend configuration file is modified each time, the backend server needs to be restarted to deploy the backend service, which not only brings inconvenience to system deployment and maintenance, but also easily causes online modification errors, and increases project development, test and maintenance costs.
Disclosure of Invention
In view of this, the present invention provides a database access method and apparatus, which can dynamically generate SQL in a memory based on a page request, completely decouple SQL statements from a backend configuration file, and need not to restart a server by modifying the SQL statements in the configuration file, thereby effectively reducing the number of times of server restart caused by a change in demand.
To achieve the above object, according to one aspect of the present invention, there is provided a database access method.
The database access method of the invention is used in a database access device adopting a front-end and back-end separated micro-service architecture, and comprises the following steps: after receiving a database access request of a front-end page, encapsulating page data carried by the request into a first entity object, and transmitting the first entity object to a service layer; analyzing the first entity object, and then dynamically generating an SQL statement according to an analysis result; packaging the SQL statement into a second entity object, and transmitting the second entity object to a data persistence layer; and analyzing the SQL statement from the second entity object and executing the SQL statement so as to realize the access to the database.
Optionally, the database access request includes: a data query request; the page data carried by the request comprises: at least one condition field with a prefix, a value of the condition field and at least one query result field; the prefix includes: and the first identification part is used for indicating the connection relationship between the field and the value thereof.
Optionally, the step of parsing the first entity object and then dynamically generating an SQL statement according to a parsing result includes: acquiring the condition field with the prefix and the value of the condition field from the first entity object; inquiring a preset corresponding relation according to the condition field with the prefix to determine a database table field matched with the condition field and a connection operator; splicing SQL condition clauses according to the matched database table fields, the connection operators and the values of the condition fields; acquiring a query result field from the first entity object, and querying the pre-configured corresponding relation according to the query result field to determine a database table field matched with the query result field; splicing SQL result clauses according to the matched database table fields; and generating an SQL sentence according to the SQL condition clause and the SQL result clause obtained by splicing.
Optionally, the step of encapsulating the page data carried by the request into a first entity object includes: filtering the condition field with the prefix in the request according to a preset legal prefix enumeration set; and encapsulating the filtered condition field, the value of the filtered condition field and the query result field into a first entity object.
To achieve the above object, according to another aspect of the present invention, there is provided a database access device.
The database access device of the invention adopts a micro-service architecture with separated front and back ends, and comprises: the system comprises a control layer and a service layer, wherein the control layer is used for packaging page data carried by a request into a first entity object after receiving a database access request of a front-end page and transmitting the first entity object to the service layer; the business layer is used for analyzing the first entity object and then dynamically generating an SQL statement according to an analysis result; the SQL statement is packaged into a second entity object, and the second entity object is transmitted to a data persistence layer; and the data persistence layer is used for analyzing the SQL statement from the second entity object and executing the SQL statement so as to realize the access to the database.
Optionally, the database access request includes: a data query request; the page data carried by the request comprises: at least one condition field with a prefix, a value of the condition field and at least one query result field; the prefix includes: and the first identification part is used for indicating the connection relationship between the field and the value thereof.
Optionally, the business layer parses the first entity object, and then dynamically generates an SQL statement according to a parsing result; the service layer acquires the condition field with the prefix and the value of the condition field from the first entity object; inquiring a preset corresponding relation according to the condition field with the prefix to determine a database table field matched with the condition field and a connection operator; then, splicing SQL conditional clauses according to the matched database table fields, the connection operators and the values of the conditional fields; the business layer acquires a query result field from the first entity object, and queries the pre-configured corresponding relation according to the query result field to determine a database table field matched with the query result field; then, splicing SQL result clauses according to the matched database table fields; and the business layer generates an SQL statement according to the SQL condition clause and the SQL result clause which are obtained by splicing.
Optionally, encapsulating, by the control layer, the page data carried by the request into a first entity object includes: the control layer filters the condition field with the prefix in the request according to a preset legal prefix enumeration set; and then, encapsulating the filtered condition field, the value of the filtered condition field and the query result field into a first entity object.
To achieve the above object, according to still another aspect of the present invention, there is provided an electronic apparatus.
The electronic device of the present invention includes: one or more processors; and storage means for storing one or more programs; when the one or more programs are executed by the one or more processors, the one or more processors are caused to implement the database access method of the present invention.
To achieve the above object, according to still another aspect of the present invention, there is provided a computer-readable medium.
The computer-readable medium of the present invention has stored thereon a computer program which, when executed by a processor, implements the database access method of the present invention.
One embodiment of the above invention has the following advantages or benefits: after receiving a database access request of a front-end page, encapsulating page data carried by the request into a first entity object, and transmitting the first entity object to a service layer; analyzing the first entity object, and then dynamically generating an SQL statement according to an analysis result; and packaging the SQL statement into a second entity object, transmitting the second entity object to a data persistence layer and the like, so that the database access is more flexible, and the SQL statement is completely decoupled from a back-end configuration file. Moreover, when the front-end data access requirement is changed, the invention adopts the micro-service architecture with the front-end and the back-end separated and the SQL is dynamically generated by the back-end, so that developers only need to redeploy the front-end page application, and the back-end can dynamically generate the SQL in the memory according to the first entity class obtained by real-time encapsulation, thereby not needing to modify the SQL manually, and not needing to restart the server due to the modification of SQL sentences in the configuration file, not only improving the development efficiency, but also effectively reducing the restart times of the server caused by the requirement change, reducing the occurrence probability of online modification errors, and reducing the development, test and maintenance costs of projects.
Further effects of the above-mentioned non-conventional alternatives will be described below in connection with the embodiments.
Drawings
The drawings are included to provide a better understanding of the invention and are not to be construed as unduly limiting the invention. Wherein:
fig. 1 is a schematic diagram of the main steps of a database access method according to a first embodiment of the present invention;
FIG. 2 is a diagram illustrating the main steps of a database access method according to a second embodiment of the present invention;
fig. 3 is a schematic diagram of the main blocks of a database access device according to a first embodiment of the present invention;
FIG. 4 is a schematic diagram of the main modules of a database access device according to a second embodiment of the present invention;
FIG. 5 is an exemplary system architecture diagram in which embodiments of the present invention may be employed;
FIG. 6 is a schematic block diagram of a computer system suitable for use with the electronic device to implement an embodiment of the invention.
Detailed Description
Exemplary embodiments of the present invention are described below with reference to the accompanying drawings, in which various details of embodiments of the invention are included to assist understanding, and which are to be considered as merely exemplary. Accordingly, those of ordinary skill in the art will recognize that various changes and modifications of the embodiments described herein can be made without departing from the scope and spirit of the invention. Also, descriptions of well-known functions and constructions are omitted in the following description for clarity and conciseness.
It should be noted that the embodiments and features of the embodiments may be combined with each other without conflict.
Before describing embodiments of the present invention in detail, some technical terms related to the embodiments of the present invention will be described.
Micro-service architecture: can be understood as an architectural concept aimed at reducing the coupling of the system by breaking down the functionality into discrete services.
And Map: is a data structure that is stored in the form of key-value pairs.
Entity object: can be understood as an "instance of an entity class". The entity class is mainly used as a class existing on the data management and business logic processing level, is mainly used for storing and managing information in the system, and can also have behaviors and even very complex behaviors, but the behaviors are closely related to entity objects represented by the behaviors.
RPC interface: an RPC based interface. Where RPC refers to a remote procedure call protocol, which is a protocol that requests services from a remote computer program over a network without knowledge of the underlying network technology.
Fig. 1 is a schematic diagram of the main steps of a database access method according to a first embodiment of the present invention. As shown in fig. 1, the database access method according to the embodiment of the present invention is applied to a database access device adopting a front-end and back-end separated micro-service architecture, and includes:
step S101, after receiving a database access request of a front-end page, encapsulating page data carried by the request into a first entity object, and transmitting the first entity object to a Service layer (or called as a Service layer).
In an optional implementation manner of this step, the page data carried by the data access request may be first added to the Map-type data structure, then the Map-type data structure is encapsulated into the first entity object, and then the first entity object is passed to the service layer through the call interface (e.g., RPC interface). The database access request may be a data query request, a data update request, a data deletion request, a data addition request, or the like.
For example, when the request is a data query request, the page data carried by the request may include: at least one condition field with a prefix, a value of the condition field, and at least one query result field (also referred to as a "field to be queried" or a "query target field"). Wherein the prefix comprises: and the first identification part is used for indicating the connection relationship between the field and the value thereof. The connection relation can be a connection relation of 'greater than, less than, equal to, not equal to, within … …, outside … …, null or non-null'. For example, the data query request of the user is "query number 010012 of employee's mobile phone number", and the page data carried by the request may include: EQ _ emereecode, 010012, emeryeephone. Wherein, EQ _ employeeCode represents a condition field with prefix "EQ", specifically an employee number; the employee phone represents a query result field, specifically an employee phone number; EQ indicates that the connection of the condition field and its value is "equal".
For example, when the request is a data update request, the page data carried by the request may include: at least one condition field with a prefix, a value of the condition field, at least one field to be updated with the prefix, and a value of the field to be updated. Wherein the prefix comprises: the first identification part is used for indicating the connection relation between the field and the value; and the second identification part is used for indicating the type of the field (the type of the field comprises a condition field and a field to be updated). For example, the value of the second identifier may be "1" or "2", where "1" indicates that the type of the field is a condition field and "2" indicates that the type of the field is a field to be updated.
In addition, in specific implementation, when the request is a data deletion request or a data addition request, a field with a prefix may also be carried in the request according to a similar manner, so as to indicate a connection relationship between the field and a value thereof and a type of the field, and further prepare for subsequent dynamic generation of SQL.
Step S102, analyzing the first entity object, and then dynamically generating an SQL statement according to an analysis result; and packaging the SQL statement into a second entity object, and transmitting the second entity object to a data persistence layer.
In an optional implementation manner of this step, the page data encapsulated in step S101 may be obtained from the first entity object, then an SQL statement required for accessing the database at this time is generated according to the obtained page data and a preset splicing rule, and then the SQL statement is encapsulated into the second entity object in a string format, and the second entity object is transferred to the data persistence layer by a parameter transfer manner.
It should be noted that the first entity object and the second entity object may be the same entity object or different entity objects. For Example, assuming that the entity object is an Example object, in step S101, the page data carried by the request may be encapsulated into the Example object, and the Example object is delivered to the service layer; in step S102, the Example object is analyzed, an SQL statement is dynamically generated according to the analysis result, the SQL statement is encapsulated into the Example object, and the Example object is transferred to the data persistence layer.
Step S103, analyzing the SQL statement from the second entity object and executing the SQL statement to realize the access to the database.
In the embodiment of the invention, the SQL can be dynamically generated based on the page request through the steps, and the SQL statement is completely decoupled from the back-end configuration file, so that the database access is more flexible. Moreover, when the front-end data access requirement is changed, the invention only needs to redeploy the front-end page application by adopting the front-end and back-end separated micro-service architecture and dynamically generates SQL by the back-end, and the back-end can dynamically generate SQL according to the first entity class obtained by real-time encapsulation, so that the SQL is not needed to be manually modified, and the back-end server is not needed to be restarted by modifying SQL sentences in the configuration file, thereby not only improving the development efficiency, but also effectively reducing the restarting times of the server caused by the requirement change, reducing the occurrence probability of online modification errors, and reducing the cost of project development, test and maintenance.
Fig. 2 is a schematic diagram of the main steps of a database access method according to a second embodiment of the present invention. In the embodiment of the present invention, a database access method is described in detail by taking a data query request as an example. As shown in fig. 2, the database access method according to the embodiment of the present invention includes:
step S201, receiving a data query request of a front-end page.
The page data carried by the data query request comprises: at least one condition field with a prefix, a value of the condition field, and at least one query result field. The prefix includes: and the first identification part is used for indicating the connection relationship between the field and the value thereof. The connection relation can be a connection relation of 'greater than, less than, equal to, not equal to, within … …, outside … …, null or non-null'.
For example, the data query request of the user is "query number 010012 of employee's mobile phone number", and the page data carried by the request may include: EQ _ emereecode, 010012, emeryeephone. Wherein, EQ _ employeeCode represents a condition field with prefix "EQ", specifically an employee number; the employee phone represents a query result field, specifically an employee phone number; EQ indicates that the connection of the condition field and its value is "equal".
Further, the prefix may include, in addition to the first identification portion: and a second identification part for indicating the field type. For example, the value of the second identification portion may be "filter" or null value, where the second identification portion is "filter" and indicates that the type of the field is a condition field, and the second identification portion is null value and indicates that the type of the field is a query result field.
Step S202, filtering the condition field with the prefix in the request according to a preset legal prefix enumeration set.
Illustratively, the legal prefix enumeration set may be defined in the global configuration file in advance, or may be defined in an enumeration class. In the embodiment of the invention, the condition field with the prefix in the data query request is filtered according to the legal prefix enumeration set, so that developers or other personnel can be effectively prevented from defining the illegal prefix field on the page for transmission, and the safety of database access is improved.
Step S203, packaging the filtered condition field, the value of the filtered condition field and the query result field into a first entity object, and transmitting the first entity object to a service layer.
In an optional embodiment, the filtered condition field, the value of the filtered condition field, and the query result field may be added to a Map-type data structure, the Map-type data structure is encapsulated into a first entity object, and then the first entity object may be transferred to a Service layer (or referred to as a "Service layer") through a call interface (e.g., an RPC interface).
And step S204, analyzing the first entity object, and dynamically generating an SQL statement according to an analysis result.
In an alternative embodiment, this step may comprise: step S2041 to step S2043.
Step S2041, obtaining the condition field with the prefix and the value of the condition field from the first entity object; inquiring a preset corresponding relation according to the condition field with the prefix to determine a database table field matched with the condition field and a connection operator; and splicing SQL condition clauses according to the matched database table fields, the connection operators and the values of the condition fields.
For example, assuming that the data query request of the user is "query number 110010 and mobile phone number of employee with last name", the page data packaged in the first entity object includes: filter _ EQ _ employeeCode, 110010, filter _ LIKE _ employenename, sheet, employenephone. In this step, the condition field "filter _ EQ _ employee" with a prefix and its value "110010", and the condition field "filter _ LIKE _ employee" with a prefix and its value "sheet" may be obtained from the first entity object; then, finding a database table field 'employee _ code' and a connection operator 'matched with' filter _ EQ _ employee 'and finding a database table field' employee _ name 'and a connection operator' LIKE matched with 'filter _ LIKE _ employee'; then, according to the database table field, the join operator and the value of the condition field, the following SQL condition clauses can be obtained by splicing: where tempemploye _ code ═ 110010 'and employee _ name'% sheet. Where, where and are splicing keywords, and can be obtained from the constant pool.
Step S2042, obtaining a query result field from the first entity object, and querying the pre-configured corresponding relation according to the query result field to determine a database table field matched with the query result field; and splicing SQL result clauses according to the matched database table fields.
For example, assuming that the data query request of the user is "query number 110010 and mobile phone number of employee with last name", the page data packaged in the first entity object includes: filter _ EQ _ employeeCode, 110010, filter _ LIKE _ employenename, sheet, employenephone. In this step, the query result field "employee phone" may be obtained from the first entity object, the database table field "employee _ phone" matching therewith may be found, and then the following SQL result clause may be generated according to the matching database table field: a select employee _ phone from table. Wherein, select and from are splicing keywords, and table is the name of the database table to be queried and can be obtained from the constant pool.
And S2043, generating an SQL statement according to the SQL condition clause and the SQL result clause obtained by splicing.
For example, from the SQL condition clause "where" person employee _ code "% of" 110010 "and employee _ namelike" and the SQL result clause "select employee _ phone from table", the following SQL statement may be generated: the selected expression _ phone from table where expression _ code is ' 110010 ' and expression _ name ' of ' sheet '.
Step S205, packaging the SQL statement into a second entity object, and transmitting the second entity object to a data persistence layer.
In an alternative embodiment, the SQL statement may be packaged into a second entity object in a string format, and the second entity object may be passed to the data persistence layer by parameter passing. In specific implementation, in order to further improve the splicing efficiency of the SQL, a StringBuffer class (a class that can store and operate a character string) may be used to execute the splicing logic of the SQL.
Further, after the SQL statement is dynamically generated, the method according to the embodiment of the present invention may further include the following steps: the generated SQL sentences are cached to adapt to a data access scene with the requirement not changing for a long time, the SQL splicing times are reduced, and the data access efficiency is improved.
Step S206, the SQL statement is analyzed from the second entity object and executed, so as to realize the query of the database.
In this step, the SQL statement in the string variable may be parsed from the second entity object based on a persistent layer framework such as Mabtis framework and executed.
In the embodiment of the invention, the SQL can be dynamically generated based on the page request through the steps, and the SQL statement is completely decoupled from the back-end configuration file, so that the database access is more flexible. Moreover, when the front-end data access requirement is changed, the invention only needs to redeploy the front-end page application by adopting the front-end and back-end separated micro-service architecture and dynamically generates SQL by the back-end, and the back-end can dynamically generate SQL according to the first entity class obtained by real-time encapsulation, so that the SQL is not needed to be modified manually, and the back-end server is not needed to be restarted by modifying SQL sentences in the configuration file, thereby not only improving the development efficiency, but also effectively reducing the restarting times of the server caused by the requirement change, reducing the occurrence probability of on-line modification errors, and reducing the cost of project development, test and maintenance.
Fig. 3 is a schematic diagram of main blocks of a database access device according to a first embodiment of the present invention. As shown in fig. 3, the database access device 300 according to the embodiment of the present invention includes: control layer 301, business layer 302, data persistence layer 303.
The control layer 301 is configured to, after receiving a database access request of a front-end page, encapsulate page data carried by the request into a first entity object, and transmit the first entity object to a service layer.
In an optional embodiment, the control layer 301 may add the page data carried by the data access request to a Map-type data structure, encapsulate the Map-type data structure into the first entity object, and then may transfer the first entity object to the service layer through a call interface (e.g., an RPC interface). The database access request may be a data query request, a data update request, a data deletion request, a data addition request, or the like.
For example, when the request is a data query request, the page data carried by the request may include: at least one condition field with a prefix, a value of the condition field, and at least one query result field (also referred to as a "field to be queried" or a "query target field"). Wherein the prefix comprises: and the first identification part is used for indicating the connection relationship between the field and the value thereof. The connection relation can be a connection relation of 'greater than, less than, equal to, not equal to, within … …, outside … …, null or non-null'. For example, the data query request of the user is "query number 010012 of employee's mobile phone number", and the page data carried by the request may include: EQ _ emereecode, 010012, emeryeephone. Wherein, EQ _ employeeCode represents a condition field with prefix "EQ", specifically an employee number; the employee phone represents a query result field, specifically an employee phone number; EQ indicates that the connection of the condition field and its value is "equal".
For example, when the request is a data update request, the page data carried by the request may include: at least one condition field with a prefix, a value of the condition field, at least one field to be updated with the prefix, and a value of the field to be updated. Wherein the prefix comprises: the first identification part is used for indicating the connection relation between the field and the value; and the second identification part is used for indicating the type of the field (the type of the field comprises a condition field and a field to be updated). For example, the value of the second identifier may be "1" or "2", where "1" indicates that the type of the field is a condition field and "2" indicates that the type of the field is a field to be updated.
In addition, in specific implementation, when the request is a data deletion request or a data addition request, a field with a prefix may also be carried in the request according to a similar manner, so as to indicate a connection relationship between the field and a value thereof and a type of the field, and further prepare for subsequent dynamic generation of SQL.
The business layer 302 is used for analyzing the first entity object and then dynamically generating an SQL statement according to an analysis result; and packaging the SQL statement into a second entity object, and transmitting the second entity object to a data persistence layer.
In an optional implementation manner, the business layer 302 may first obtain the encapsulated page data from the first entity object, then generate the SQL statement required for accessing the database according to the obtained page data and the preset splicing rule, then encapsulate the SQL statement into the second entity object in the format of a character string, and may transmit the second entity object to the data persistence layer in a parameter transmission manner. It should be noted that the first entity object and the second entity object may be the same entity object or different entity objects.
And the data persistence layer 303 is configured to parse the SQL statement from the second entity object and execute the SQL statement, so as to implement access to the database.
In the embodiment of the invention, the SQL can be dynamically generated based on the page request through the device, and the SQL statement is completely decoupled from the back-end configuration file, so that the database access is more flexible. Moreover, when the front-end data access requirement is changed, the device of the invention adopts the micro-service architecture with the front-end and the back-end separated and dynamically generates the SQL by the back-end, so that developers only need to redeploy the front-end page application, and the back-end can dynamically generate the SQL according to the first entity class obtained by real-time encapsulation, thereby not needing to manually modify the SQL and restarting a back-end server because of modifying SQL sentences in the configuration file, not only improving the development efficiency, but also effectively reducing the restarting times of the server caused by the requirement change, reducing the occurrence probability of online modification errors and reducing the development, test and maintenance costs of projects.
Fig. 4 is a schematic diagram of main blocks of a database access device according to a second embodiment of the present invention. As shown in fig. 4, the database access apparatus 400 according to the embodiment of the present invention adopts a front-end and back-end separated micro-service architecture, which includes: a control layer 401, a Service layer 402, a Manager layer 403, and a DAO layer 404. Wherein the DAO layer (data persistence layer) accesses the database using the Mabatis framework 405. Wherein, the Manager layer is an optional module.
The control layer 401 is configured to, after receiving a database access request of a front-end page, encapsulate page data carried by the request into a first entity object, and transmit the first entity object to the Service layer 402.
The database access request may be a data query request, a data update request, a data deletion request, a data addition request, or the like.
For example, when the request is a data query request, the page data carried by the request may include: at least one condition field with a prefix, a value of the condition field, and at least one query result field (also referred to as a "field to be queried" or a "query target field"). Wherein the prefix comprises: and the first identification part is used for indicating the connection relationship between the field and the value thereof. The connection relation can be a connection relation of 'greater than, less than, equal to, not equal to, within … …, outside … …, null or non-null'.
Further, in this example, encapsulating, by the control layer 401, the page data carried by the data query request into the first entity object may include: the control layer 401 filters the condition field with prefix in the request according to a preset legal prefix enumeration set; and then, encapsulating the filtered condition field, the value of the filtered condition field and the query result field into a first entity object.
And the Service layer 402 is used for analyzing the first entity object so as to dynamically generate an SQL statement according to an analysis result.
Illustratively, when the request is a data query request, the Service layer 402 parses the first entity object to dynamically generate an SQL statement according to a parsing result, which mainly includes:
step 1, the Service layer 402 acquires the condition field with the prefix and the value of the condition field from the first entity object; the Service layer 402 inquires the pre-configured corresponding relation according to the condition field with the prefix to determine the database table field and the connection operator matched with the condition field; and the Service layer 402 splices the SQL conditional clauses according to the matched database table fields, the connection operators and the values of the conditional fields.
Step 2, the Service layer 402 acquires the query result field from the first entity object, and queries the pre-configured corresponding relation according to the query result field to determine the database table field matched with the query result field; and the Service layer 402 splices the SQL result clauses according to the matched database table fields.
And 3, the Service layer 402 generates an SQL statement according to the SQL condition clause and the SQL result clause obtained by splicing.
The Service layer 402 is further configured to encapsulate the SQL statement into a second entity object, and transfer the second entity object to the Manager layer 403.
In an alternative embodiment, the Service layer 402 may encapsulate the SQL statement into a second entity object in a string format, and pass the second entity object to the Manager layer 403 by parameter passing. In specific implementation, in order to further improve the splicing efficiency of the SQL, a StringBuffer class (a class that can store and operate a character string) may be used to execute the splicing logic of the SQL.
Further, after the SQL statements are dynamically generated, the Service layer 402 may also be configured to cache the generated SQL statements to adapt to a data access scenario in which a demand does not change for a long time, reduce the number of SQL splices, and help to improve the data access efficiency.
And the Manager layer 403 is used for transferring the second entity object to the DAO layer. In particular implementations, the Manager layer may also be used to define transactions.
And the DAO layer 404 is configured to parse the SQL statement from the second entity object and execute the SQL statement, so as to implement access to a database.
In a specific implementation, the DAO layer 404 may parse and execute the SQL statement from the second entity object based on a persistent layer framework such as a Mabtis framework.
In the embodiment of the invention, the SQL can be dynamically generated in the memory based on the page request through the device, the SQL sentences are completely decoupled from the back-end configuration file, the server is not required to be restarted by modifying the SQL sentences in the configuration file, and the number of times of restarting the server caused by the requirement change is effectively reduced.
Fig. 5 illustrates an exemplary system architecture 500 to which the database access method or database access apparatus of an embodiment of the present invention may be applied.
As shown in fig. 5, the system architecture 500 may include terminal devices 501, 502, 503, a network 504, and a server 505. The network 504 serves to provide a medium for communication links between the terminal devices 501, 502, 503 and the server 505. Network 504 may include various connection types, such as wired, wireless communication links, or fiber optic cables, to name a few.
The user may use the terminal devices 501, 502, 503 to interact with a server 505 over a network 504 to receive or send messages or the like. The terminal devices 501, 502, 503 may have installed thereon various client applications, for example front-end page applications (such as VUE, a front-end page framework).
The terminal devices 501, 502, 503 may be various electronic devices having a display screen and supporting web browsing, including but not limited to smart phones, tablet computers, laptop portable computers, desktop computers, and the like.
The server 505 may be a server providing various services, such as a background management server providing support for database access requests issued by users using the terminal devices 501, 502, 503. The background management server may analyze and perform other processing on the received data such as the database access request, and feed back a processing result (e.g., a query result) to the terminal device.
It should be noted that the database access method provided by the embodiment of the present invention is generally executed by the server 505, and accordingly, the database access apparatus is generally disposed in the server 505.
It should be understood that the number of terminal devices, networks, and servers in fig. 5 is merely illustrative. There may be any number of terminal devices, networks, and servers, as desired for implementation.
Referring now to FIG. 6, shown is a block diagram of a computer system 600 suitable for use with the electronic device implementing an embodiment of the present invention. The electronic device shown in fig. 6 is only an example, and should not bring any limitation to the functions and the scope of use of the embodiments of the present invention.
As shown in fig. 6, the computer system 600 includes a Central Processing Unit (CPU)601 that can perform various appropriate actions and processes according to a program stored in a Read Only Memory (ROM)602 or a program loaded from a storage section 608 into a Random Access Memory (RAM) 603. In the RAM 603, various programs and data necessary for the operation of the system 600 are also stored. The CPU 601, ROM 602, and RAM 603 are connected to each other via a bus 604. An input/output (I/O) interface 605 is also connected to bus 604.
The following components are connected to the I/O interface 605: an input portion 606 including a keyboard, a mouse, and the like; an output portion 607 including a display such as a Cathode Ray Tube (CRT), a Liquid Crystal Display (LCD), and the like, and a speaker; a storage section 608 including a hard disk and the like; and a communication section 609 including a network interface card such as a LAN card, a modem, or the like. The communication section 609 performs communication processing via a network such as the internet. The driver 610 is also connected to the I/O interface 605 as needed. A removable medium 611 such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, or the like is mounted on the drive 610 as necessary, so that a computer program read out therefrom is mounted in the storage section 608 as necessary.
In particular, according to the embodiments of the present disclosure, the processes described above with reference to the flowcharts may be implemented as computer software programs. For example, embodiments of the present disclosure include a computer program product comprising a computer program embodied on a computer readable medium, the computer program comprising program code for performing the method illustrated in the flow chart. In such an embodiment, the computer program may be downloaded and installed from a network through the communication section 609, and/or installed from the removable medium 611. The computer program performs the above-described functions defined in the system of the present invention when executed by the Central Processing Unit (CPU) 601.
It should be noted that the computer readable medium shown in the present invention can be a computer readable signal medium or a computer readable storage medium or any combination of the two. A computer readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the foregoing. More specific examples of the computer readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer diskette, a hard disk, a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In the present invention, a computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device. In the present invention, however, a computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated data signal may take many forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A computer readable signal medium may also be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device. Program code embodied on a computer readable medium may be transmitted using any appropriate medium, including but not limited to: wireless, wire, fiber optic cable, RF, etc., or any suitable combination of the foregoing.
The flowchart and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods and computer program products according to various embodiments of the present invention. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams or flowchart illustration, and combinations of blocks in the block diagrams or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.
The modules (or "layers" or "units") mentioned in the embodiments of the present invention may be implemented by software, and may also be implemented by hardware. The described modules (or "layers" or "units") may also be provided in a processor, and may be described as, for example: a processor includes a control layer, a business layer, and a data persistence layer. The names of these modules (or "layers" or "units") do not in some cases constitute a definition of the modules themselves, for example, the control layer may also be described as a "module encapsulating page data carried by a database access request".
As another aspect, the present invention also provides a computer-readable medium that may be contained in the apparatus described in the above embodiments; or may be separate and not incorporated into the device. The computer readable medium carries one or more programs which, when executed by a device, cause the device to perform the following: after receiving a database access request of a front-end page, encapsulating page data carried by the request into a first entity object, and transmitting the first entity object to a service layer; analyzing the first entity object, and then dynamically generating an SQL statement according to an analysis result; packaging the SQL statement into a second entity object, and transmitting the second entity object to a data persistence layer; and analyzing the SQL statement from the second entity object and executing the SQL statement so as to realize the access to the database.
The above-described embodiments should not be construed as limiting the scope of the invention. Those skilled in the art will appreciate that various modifications, combinations, sub-combinations, and substitutions can occur, depending on design requirements and other factors. Any modification, equivalent replacement, and improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.
Claims (10)
1. A database access method for use in a database access device employing a front-end separated microservice architecture, the method comprising:
after receiving a database access request of a front-end page, encapsulating page data carried by the request into a first entity object, and transmitting the first entity object to a service layer;
analyzing the first entity object, and then dynamically generating an SQL statement according to an analysis result; packaging the SQL statement into a second entity object, and transmitting the second entity object to a data persistence layer;
and analyzing the SQL statement from the second entity object and executing the SQL statement so as to realize the access to the database.
2. The method of claim 1, wherein the database access request comprises: a data query request; the page data carried by the request comprises: at least one condition field with a prefix, a value of the condition field and at least one query result field; the prefix includes: and the first identification part is used for indicating the connection relationship between the field and the value thereof.
3. The method according to claim 2, wherein the step of parsing the first entity object and then dynamically generating SQL statements according to the parsing result comprises;
acquiring the condition field with the prefix and the value of the condition field from the first entity object; inquiring a preset corresponding relation according to the condition field with the prefix to determine a database table field matched with the condition field and a connection operator; splicing SQL condition clauses according to the matched database table fields, the connection operators and the values of the condition fields;
acquiring a query result field from the first entity object, and querying the pre-configured corresponding relation according to the query result field to determine a database table field matched with the query result field; splicing SQL result clauses according to the matched database table fields;
and generating an SQL sentence according to the SQL condition clause and the SQL result clause obtained by splicing.
4. The method according to claim 2, wherein the step of encapsulating the page data carried by the request into a first entity object comprises:
filtering the condition field with the prefix in the request according to a preset legal prefix enumeration set; and encapsulating the filtered condition field, the value of the filtered condition field and the query result field into a first entity object.
5. A database access apparatus employing a front-end separated microservice architecture, the apparatus comprising:
the system comprises a control layer and a service layer, wherein the control layer is used for packaging page data carried by a request into a first entity object after receiving a database access request of a front-end page and transmitting the first entity object to the service layer;
the business layer is used for analyzing the first entity object and then dynamically generating an SQL statement according to an analysis result; the SQL statement is packaged into a second entity object, and the second entity object is transmitted to a data persistence layer;
and the data persistence layer is used for analyzing the SQL statement from the second entity object and executing the SQL statement so as to realize the access to the database.
6. The apparatus of claim 5, wherein the database access request comprises: a data query request; the page data carried by the request comprises: at least one condition field with a prefix, a value of the condition field and at least one query result field; the prefix includes: and the first identification part is used for indicating the connection relationship between the field and the value thereof.
7. The apparatus according to claim 6, wherein the business layer parses the first entity object, and then dynamically generates SQL statements according to the parsing result;
the service layer acquires the condition field with the prefix and the value of the condition field from the first entity object; inquiring a preset corresponding relation according to the condition field with the prefix to determine a database table field matched with the condition field and a connection operator; then, splicing SQL conditional clauses according to the matched database table fields, the connection operators and the values of the conditional fields;
the business layer acquires a query result field from the first entity object, and queries the pre-configured corresponding relation according to the query result field to determine a database table field matched with the query result field; then, splicing SQL result clauses according to the matched database table fields;
and the business layer generates an SQL statement according to the SQL condition clause and the SQL result clause which are obtained by splicing.
8. The apparatus of claim 6, wherein the control layer encapsulates the page data carried by the request into a first entity object, comprising:
the control layer filters the condition field with the prefix in the request according to a preset legal prefix enumeration set; and then, encapsulating the filtered condition field, the value of the filtered condition field and the query result field into a first entity object.
9. An electronic device, comprising:
one or more processors;
a storage device for storing one or more programs,
when executed by the one or more processors, cause the one or more processors to implement the method of any one of claims 1-4.
10. A computer-readable medium, on which a computer program is stored, which program, when being executed by a processor, carries out the method of any one of claims 1 to 4.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910163475.5A CN111666293A (en) | 2019-03-05 | 2019-03-05 | Database access method and device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910163475.5A CN111666293A (en) | 2019-03-05 | 2019-03-05 | Database access method and device |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN111666293A true CN111666293A (en) | 2020-09-15 |
Family
ID=72381492
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910163475.5A Pending CN111666293A (en) | 2019-03-05 | 2019-03-05 | Database access method and device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111666293A (en) |
Cited By (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112115125A (en) * | 2020-09-27 | 2020-12-22 | 北京人大金仓信息技术股份有限公司 | Database access object name resolution method and device and electronic equipment |
| CN112364378A (en) * | 2020-11-17 | 2021-02-12 | 江苏银承网络科技股份有限公司 | Information processing method and device for order transaction operation management system |
| CN112799643A (en) * | 2021-01-26 | 2021-05-14 | 中国工商银行股份有限公司 | Front-end page application development method and device based on database mapping dynamic interface |
| CN114237712A (en) * | 2021-12-15 | 2022-03-25 | 北京百度网讯科技有限公司 | Instruction execution method and device, electronic equipment and computer storage medium |
| CN115481448A (en) * | 2022-09-16 | 2022-12-16 | 昆仑数智科技有限责任公司 | Data acquisition method, device, server and storage medium |
| CN117149888A (en) * | 2023-11-01 | 2023-12-01 | 建信金融科技有限责任公司 | Method, apparatus, device and computer readable medium for data processing |
-
2019
- 2019-03-05 CN CN201910163475.5A patent/CN111666293A/en active Pending
Cited By (10)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112115125A (en) * | 2020-09-27 | 2020-12-22 | 北京人大金仓信息技术股份有限公司 | Database access object name resolution method and device and electronic equipment |
| CN112115125B (en) * | 2020-09-27 | 2024-04-26 | 北京人大金仓信息技术股份有限公司 | Database access object name resolution method and device and electronic equipment |
| CN112364378A (en) * | 2020-11-17 | 2021-02-12 | 江苏银承网络科技股份有限公司 | Information processing method and device for order transaction operation management system |
| CN112799643A (en) * | 2021-01-26 | 2021-05-14 | 中国工商银行股份有限公司 | Front-end page application development method and device based on database mapping dynamic interface |
| CN112799643B (en) * | 2021-01-26 | 2024-02-20 | 中国工商银行股份有限公司 | Front-end page application development method and device based on database mapping dynamic interface |
| CN114237712A (en) * | 2021-12-15 | 2022-03-25 | 北京百度网讯科技有限公司 | Instruction execution method and device, electronic equipment and computer storage medium |
| CN114237712B (en) * | 2021-12-15 | 2022-12-27 | 北京百度网讯科技有限公司 | Instruction execution method and device, electronic equipment and computer storage medium |
| CN115481448A (en) * | 2022-09-16 | 2022-12-16 | 昆仑数智科技有限责任公司 | Data acquisition method, device, server and storage medium |
| CN117149888A (en) * | 2023-11-01 | 2023-12-01 | 建信金融科技有限责任公司 | Method, apparatus, device and computer readable medium for data processing |
| CN117149888B (en) * | 2023-11-01 | 2024-02-13 | 建信金融科技有限责任公司 | Method, apparatus, device and computer readable medium for data processing |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111666293A (en) | Database access method and device | |
| EP3822777A1 (en) | Methods for processing mini program, and related devices | |
| CN107491382B (en) | Log output method and device | |
| CN110555030A (en) | SQL statement processing method and device | |
| CN110858202A (en) | Method and device for generating where clause in database query statement | |
| CN111427701A (en) | Workflow engine system and business processing method | |
| CN113760948A (en) | Data query method and device | |
| CN111338944B (en) | Remote Procedure Call (RPC) interface testing method, device, medium and equipment | |
| CN111125064A (en) | Method and device for generating database mode definition statement | |
| CN110109983B (en) | Method and device for operating Redis database | |
| CN109981546B (en) | Method and device for acquiring remote call relation between application modules | |
| CN111241189A (en) | Method and device for synchronizing data | |
| CN113419740A (en) | Program data stream analysis method and device, electronic device and readable storage medium | |
| CN110764769B (en) | Method and device for processing user request | |
| CN116226189A (en) | Cache data query method, device, electronic equipment and computer readable medium | |
| CN113625998B (en) | Request processing method and device | |
| CN115145652A (en) | Method, device, equipment and medium for creating data processing task | |
| CN113111079A (en) | Database execution statement generation method and device | |
| CN113760240A (en) | Method and device for generating data model | |
| CN113495747B (en) | Gray scale release method and device | |
| CN113779018A (en) | Data processing method and device | |
| CN113448602A (en) | Version updating method and device | |
| CN112559001A (en) | Method and device for updating application | |
| CN111008202A (en) | Distributed transaction processing method and framework | |
| CN114285743B (en) | Method, device, electronic equipment and storage medium for updating configuration information |
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 |