An efficient data query processing method, system and device

Abstract

The present invention discloses an efficient data query processing method, system, and device, belonging to the field of Web application technology. The method, system, and device combine the SQL query language and the Freemaker template engine to decouple data query from business code. By configuring a page with an SQL query statement that conforms to the Freemaker template engine, business data queries are performed on the corresponding page according to the unified data query interface input specification. When user needs change rapidly, the query results are changed by modifying the SQL query statement that conforms to the Freemaker template engine in the page configuration, thereby achieving rapid response to customer needs. The present invention can decouple data query from business code and realize the rapid development and modification of page data query lists.

Description

Technical Field

[0001] The present invention relates to the field of Web application technology, and in particular to an efficient data query processing method, system and device. Background Art

[0002] Querying business data stored in a database and displaying it in a list format on a page is a common data display method in the government service field. For this scenario, common coding schemes include the following:

[0003] 1. Combine Java and SQL and write in Java code file:

[0004] In Java code files, StringBuffer is often used as a container for SQL query statements. The corresponding data query statements are hard-coded into the Java code files. The relevant query conditions must be individually determined based on the parameters passed in by the front-end, and the query condition statements must be written to achieve the final query results. The advantage of this solution is that after the Java code files are compiled, they are generated into bytecode class files, which ordinary personnel cannot parse to obtain the relevant query statements. The disadvantage is that the relevant code is hard-coded in the Java code files. If the query requirements are subsequently changed, the relevant files must be modified and replaced and redeployed on the server. This requires the joint participation of R&D and implementation personnel, and it is not possible to respond quickly to needs.

[0005] 2. Combine the relevant framework to write the SQL query statement into the XML file:

[0006] Data query SQL statements are written into XML files and configured or mapped using common industry frameworks (such as MyBatis and Hibernate). Query conditions are configured in the XML file using relevant conditional statements in the XML language. This allows for the display of relevant data results when the relevant query statement is called. The advantage of this solution is that SQL query statements are written into XML files, decoupling them from Java program code and facilitating unified management. XML also provides tags that support the writing of dynamic SQL statements, allowing for the reuse of related statements. The disadvantage is that the related statements are dependent on the database, resulting in poor code portability, and related code changes require code modifications and redeployment. Summary of the Invention

[0007] The technical task of the present invention is to address the above shortcomings and provide an efficient data query processing method, system and device, which can decouple data query from business code and realize the rapid development and change of page data query lists.

[0008] The technical solution adopted by the present invention to solve its technical problem is:

[0009] An efficient data query processing method that combines SQL query language and Freemaker template engine to decouple data query from business code;

[0010] By configuring the SQL query statement that complies with the freemaker template engine on the page, assemble parameters according to the unified data query interface input specification on the corresponding page to query business data. When user needs change rapidly, change the SQL query statement that complies with the freemaker template engine in the page configuration to change the query results and quickly respond to customer needs.

[0011] This method decouples data query from business code by combining SQL query language and Freemaker template engine, and realizes the rapid development and modification of page data query list.

[0012] Preferably, the method is implemented as follows:

[0013] 1) Data query sql configuration,

[0014] Based on the SSM framework, combined with JavaScript and HTML languages, develop front-end pages to configure SQL query statements and store them in the database for use when performing data queries;

[0015] 2) Unified data query interface,

[0016] Write a unified data query interface to decouple the front-end page from the back-end query interface logic; reduce the workload of R&D personnel in writing repetitive code, allowing them to focus on other logical rules;

[0017] 3) Interface call,

[0018] After unifying the data query interface, calls to the corresponding interface must assemble relevant data according to the calling specifications, otherwise a call error prompt will be returned.

[0019] When querying a list of data, call the unified data query interface, pass in the corresponding parameters according to the specifications, and verify the effect based on the output data.

[0020] Preferably, the data query sql configuration, the configuration page includes a description, parser name and sql text,

[0021] Description is used to explain the specific purpose of the SQL statement;

[0022] The parser name is used as a unique identifier and is passed to the backend for parsing when the frontend calls it;

[0023] The sql text is used to fill in the data query SQL that complies with the freemaker template engine rules and is used for parsing when calling the interface;

[0024] After saving, the relevant data is stored in the corresponding database table for subsequent interface calls.

[0025] Preferably, the unified data query interface adopts the adapter mode when writing the backend code to meet the requirements of the unified data query interface. In addition to the unified background query logic, a personalized processing interface is reserved, and R&D personnel can implement it according to business needs.

[0026] Furthermore, the backend provides a unified external interface. R&D personnel only need to request this address when writing code. The backend code queries the corresponding SQL parser based on the passed handlerName parameter.

[0027] Preferably, the corresponding SQL parser,

[0028] After obtaining the corresponding parser according to the passed-in handlerName, the SQL query statement that conforms to the freemaker template engine configured in step 1) is obtained. After obtaining the corresponding statement, the freemaker tool class is called according to the passed-in parameters to perform query SQL parsing and finally obtain the final query statement;

[0029] According to the parameters assembled using the freemaker engine, data queries can be performed and corresponding results can be returned.

[0030] Preferably, when calling the interface, the calling parameters must be passed in json format, and the parameters must include the handlerName parameter, which is used to parse and query the corresponding parser in the background to query data. Other query parameters can be added in json according to the query requirements.

[0031] Preferably, the method implements the query process through the unified data query interface as follows:

[0032] 1) Assemble parameters according to specifications and call the unified data query interface;

[0033] 2) Unify the data query interface and obtain the freemaker template engine query SQL according to handlerName;

[0034] 3) Call the freemaker tool class to dynamically generate data query SQL according to the query conditions;

[0035] 4) Execute the query SQL, obtain the query results and return them.

[0036] The present invention also claims protection for an efficient data query processing system, including a page configuration module, a unified data query interface module and an interface calling module,

[0037] The page configuration module is used to configure SQL query statements that conform to the freemaker template engine through the page;

[0038] The unified data query interface module is used to implement a unified external interface provided by the backend. The backend code queries the corresponding SQL parser based on the passed handlerName parameter;

[0039] The interface calling module is used to implement calling by assembling relevant data according to the calling specification;

[0040] The system implements business data query through the above-mentioned data query processing method.

[0041] The present invention also claims protection for an efficient data query processing device, comprising at least one memory and at least one processor;

[0042] The at least one memory is configured to store a machine-readable program;

[0043] The at least one processor is configured to call the machine-readable program to implement the above-mentioned data query processing method.

[0044] Compared with the prior art, the present invention provides an efficient data query processing method, system and device with the following advantages:

[0045] During the development process, there is no need to write the corresponding parameter acquisition and transfer processing. You only need to write the query logic required by the business and adjust the SQL query statement according to the freemaker template based on the query conditions, which reduces the workload of R&D personnel.

[0046] By configuring SQL queries on the page, when user needs change rapidly, there is no need for R&D personnel to be involved. Project implementation personnel can simply adjust SQL based on user needs, thereby achieving the effect of quickly responding to user needs. BRIEF DESCRIPTION OF THE DRAWINGS

[0047] Figure 1 This is a flowchart of implementing data query through a unified data query interface provided by an embodiment of the present invention;

[0048] Figure 2 is a diagram of a query SQL configuration page provided by an embodiment of the present invention;

[0049] Figure 3 1 is a diagram of a storage table of an SQL query SQL statement parser provided in an embodiment of the present invention;

[0050] Figure 4 This is an example diagram of input parameters for calling a unified data query interface provided by an embodiment of the present invention;

[0051] Figure 5 This is an example diagram of output parameters of the unified data query interface call provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0052] The embodiment of the present invention provides an efficient data query processing method, which combines the SQL query language and the Freemaker template engine to decouple data query from business code and realize the rapid development and modification of page data query lists;

[0053] By configuring the SQL query statement that complies with the freemaker template engine on the page, assemble parameters according to the unified data query interface input specification on the corresponding page to query business data. When user needs change rapidly, change the SQL query statement that complies with the freemaker template engine in the page configuration to change the query results and quickly respond to customer needs.

[0054] The implementation of this method is:

[0055] 1. Data query sql configuration,

[0056] Based on the SSM framework, combined with JavaScript and HTML languages, the front-end page is developed to configure SQL query statements and store them in the database, which is used to call the configuration page when performing data query. Figure 2 As shown;

[0057] The configuration page includes description, parser name and sql text,

[0058] Description is used to explain the specific purpose of the SQL statement;

[0059] The parser name is used as a unique identifier and is passed to the backend for parsing when the frontend calls it;

[0060] The sql text is used to fill in the data query SQL that complies with the freemaker template engine rules and is used for parsing when calling the interface;

[0061] After saving, the relevant data is stored in the corresponding database table for subsequent interface calls.

[0062] 2. Unified data query interface,

[0063] Based on step 1, a unified data query interface is written to decouple the front-end page from the back-end query interface logic, reducing the workload of R&D personnel in writing duplicate code and allowing them to focus on other logical rules. To meet the requirements of the unified data query interface, the back-end code is written in the adapter mode. In addition to the unified back-end query logic, a personalized processing interface is reserved, which R&D personnel can implement according to their business needs. The key code of the unified data query interface is as follows:

[0064]

[0065] The above code is the unified query interface access interface URL and part of the processing process. The backend provides a unified external interface. R&D personnel only need to request this address when writing code. The backend code queries the corresponding SQL parser based on the passed handlerName parameter. The following code shows the corresponding process:

[0066]

[0067] The above is the processing of querying the corresponding parser according to the handlerName. After obtaining the corresponding parser according to the passed-in handlerName, the SQL query statement that conforms to the freemaker template engine configured in step 1 is obtained. After obtaining the corresponding statement, the freemaker tool class is called according to the passed-in parameters to perform query SQL parsing and finally obtain the final query statement. The relevant processing code is as follows:

[0068]

[0069] The above is the implementation of the freemaker template engine parsing and obtaining SQL. According to the parameters assembled using the freemaker engine, data queries can be performed and corresponding results can be returned.

[0070] 3. Interface call,

[0071] After unifying the data query interface, calls to the corresponding interface must be made according to the call specifications and assembled with relevant data. Otherwise, a call error message will be returned. When calling this interface, the call parameters must be passed in JSON format. The parameters must include the handlerName parameter, which is used for background parsing and querying the corresponding parser to query data. Other query parameters can be added in JSON according to the query requirements. The specific specifications are as follows:

[0072]

[0073] The above code is the unified query interface parameter specification provided by this embodiment.

[0074] 4. Effect verification,

[0075] When querying a list of data, call the unified data query interface and pass in the corresponding parameters according to the specifications. The interface parameters are as follows: Figure 4 As shown, the parameters output by the unified data query interface call are as follows Figure 5 shown.

[0076] like Figure 1 As shown, the method implements the query process through the unified data query interface as follows:

[0077] 1) Assemble parameters according to specifications and call the unified data query interface;

[0078] 2) Unify the data query interface and obtain the freemaker template engine query SQL according to handlerName;

[0079] 3) Call the freemaker tool class to dynamically generate data query SQL according to the query conditions;

[0080] 4) Execute the query SQL, obtain the query results and return them.

[0081] Structured Query Language (SQL), abbreviated as Structured Query Language, is a special-purpose programming language and database query and programming language used to access data as well as query, update, and manage relational database systems. SQL is a high-level, non-procedural programming language that allows users to work with high-level data structures. It does not require users to specify or understand the specific storage method for data, so different database systems with completely different underlying structures can use the same SQL as the data input and management interface. SQL statements can be nested, which makes it extremely flexible and powerful.

[0082] SQL has the functions of data definition, data manipulation, and data control.

[0083] SQL data definition function: It can define the three-level database schema structure: external schema, global schema, and internal schema. In SQL, the external schema is also called a view, the global schema is simply called a schema, and the internal schema is automatically implemented by the system based on the database schema, generally without user intervention.

[0084] SQL data manipulation functions: including inserting, deleting and modifying data in basic tables and views, especially with powerful data query functions.

[0085] SQL's data control function: mainly controls user access rights to ensure system security.

[0086] FreeMarker is a template engine: a general-purpose tool for generating output text (HTML pages, emails, configuration files, source code, etc.) based on templates and data to be changed. It is not intended for end users, but is a Java class library, a component that programmers can embed into the products they develop. FreeMarker is free and released under the Apache License version 2.0. Its templates are written in the FreeMarker Template Language (FTL), which is a simple, dedicated language. Data needs to be prepared for display in a real programming language, such as database queries and business operations, and then the template displays the prepared data. In the template, the main focus is on how to display the data, while outside the template, attention is paid to what data to display.

[0087] In the field of government service products, most business data displays are implemented using SQL query execution results combined with related components. Data list display requirements change frequently, often requiring rapid response. Therefore, this method proposes a new, easily maintainable data query processing method. To reduce the pressure on R&D personnel to respond to related requirements, and considering that implementation personnel generally have a certain level of SQL language proficiency, and to quickly respond to user needs and avoid wasted resources, a data query processing method is proposed that facilitates the modification of SQL query statements and can be quickly deployed and implemented.

[0088] When developing a Javaweb project, you can introduce related components and create related query SQL statement parser storage tables in the database. Write SQL query statements that conform to the freemaker template engine and configure them on the query SQL statement configuration page. On the page where data needs to be queried, assemble the corresponding parameters according to the result call specification.

[0089] The embodiment of the present invention also provides an efficient data query processing system, including a page configuration module, a unified data query interface module and an interface calling module.

[0090] The page configuration module is used to configure SQL query statements that conform to the freemaker template engine through the page;

[0091] The unified data query interface module is used to implement a unified external interface provided by the backend. The backend code queries the corresponding SQL parser based on the passed handlerName parameter;

[0092] The interface calling module is used to implement calling by assembling relevant data according to the calling specification;

[0093] The system implements business data query through the data query processing method described in the above embodiment.

[0094] An embodiment of the present invention further provides an efficient data query processing device, comprising at least one memory and at least one processor;

[0095] The at least one memory is configured to store a machine-readable program;

[0096] The at least one processor is configured to call the machine-readable program to implement the efficient data query processing method described in the above embodiment.

[0097] The above specific embodiments will allow those skilled in the art to easily implement the present invention. However, it should be understood that the present invention is not limited to the above specific embodiments. Based on the disclosed embodiments, those skilled in the art can arbitrarily combine different technical features to implement different technical solutions.

[0098] Except for the technical features described in the specification, all other technical features are known technologies to those skilled in the art.

Claims

1. An efficient data query processing method, characterized in that: Combine SQL query language and Freemaker template engine to decouple data query from business code; By configuring SQL query statements that conform to the freemaker template engine on the page, assemble parameters according to the unified data query interface input specification on the corresponding page to query business data. When user needs change rapidly, modify the SQL query statements that conform to the freemaker template engine in the page configuration to change the query results and quickly respond to customer needs. The implementation of this method is: 1) Data query sql configuration, Based on the SSM framework, combined with JavaScript and HTML languages, develop front-end pages to configure SQL query statements and store them in the database for use when performing data queries; 2) Unified data query interface, Write a unified data query interface to decouple the front-end page from the back-end query interface logic; 3) Interface call, After unifying the data query interface, when calling the corresponding interface, you need to assemble the relevant data according to the calling specification, otherwise an error message will be returned; The data query sql configuration, the configuration page includes description, parser name and sql text, Description is used to explain the specific purpose of the SQL statement; The parser name is used as a unique identifier and is passed to the backend for parsing when the frontend calls it; The sql text is used to fill in the data query SQL that complies with the freemaker template engine rules and is used for parsing when calling the interface; After saving, the relevant data is stored in the corresponding database table for subsequent interface calls.

2. An efficient data query processing method according to claim 1, characterized in that: The unified data query interface adopts the adapter mode when writing the back-end code. In addition to the unified background query logic, it also reserves a personalized processing interface, which R&D personnel can implement according to business needs.

3. An efficient data query processing method according to claim 2, characterized in that: The backend provides a unified external interface. R&D personnel only need to request this address when writing code. The backend code queries the corresponding SQL parser based on the passed handlerName parameter.

4. The efficient data query processing method according to claim 3, characterized in that: The corresponding SQL parser, After obtaining the corresponding parser according to the passed-in handlerName, the SQL query statement that conforms to the freemaker template engine configured in step 1) is obtained. After obtaining the corresponding statement, the freemaker tool class is called according to the passed-in parameters to perform query SQL parsing and finally obtain the final query statement; According to the parameters assembled using the freemaker engine, data queries can be performed and corresponding results can be returned.

5. The efficient data query processing method according to claim 3, characterized in that: The interface call must pass the call parameters in json format when calling the interface. The parameters must include the handlerName parameter, which is used to parse the data in the background and query the corresponding parser to query the data. Other query parameters can be added in json according to the query requirements.

6. An efficient data query processing method according to claim 5, characterized in that: The method implements the query process through the unified data query interface as follows: 1) Assemble parameters according to specifications and call the unified data query interface; 2) Unify the data query interface and obtain the freemaker template engine query SQL according to the handlerName; 3) Call the freemaker tool class to dynamically generate data query SQL according to the query conditions; 4) Execute the query SQL, obtain the query results and return them.

7. An efficient data query processing system, characterized in that: Including page configuration module, unified data query interface module and interface call module, The page configuration module is used to configure SQL query statements that conform to the freemaker template engine through the page; The unified data query interface module is used to implement a unified external interface provided by the backend. The backend code queries the corresponding SQL parser based on the passed handlerName parameter; The interface calling module is used to implement calling by assembling relevant data according to the calling specification; The system realizes business data query through the data query processing method described in any one of claims 1 to 6.

8. An efficient data query processing device, characterized in that: comprising at least one memory and at least one processor; The at least one memory is configured to store a machine-readable program; The at least one processor is configured to call the machine-readable program to implement the data query processing method according to any one of claims 1 to 6.

Images