Report data screening, filtering and querying method based on SQL (Structured Query Language) parameter transmission
By using placeholders in the SQL defined by the report template and constructing SQL that meets the filtering filtering conditions through dynamic SQL parameters in the back-end service, the problem that traditional report query data cannot be filtered and filtered at the database SQL level is solved, and accurate data filtering and performance improvement is achieved.
Patent Information
- Application Number
- CN202411792191.X
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-12-06
- Publication Date
- 2025-05-06
AI Technical Summary
Traditional report query data cannot be filtered and filtered at the database SQL level, resulting in difficulty in accurately filtering data. In the case of large data volume, query performance is slow, resource overhead is high, and service response speed is slow.
The report data filtering and query method based on SQL parameters is adopted, and the SQL configuration placeholder is configured in the report template definition. The back-end service replaces parameters based on the dynamic SQL parameter transfer method to construct SQL that meets the filtering and filtering conditions, and directly query at the database level.
It realizes accurate data filtering and filtering queries, avoids performance reduction caused by nested subqueries, saves background database and server resource overhead, and improves service response speed of reporting applications.
Smart Images
Figure CN119938706A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the technical field of report data screening, filtering and querying, and in particular to a report data screening, filtering and querying method based on SQL parameter transmission. Background Art
[0002] In recent years, low-code reporting applications have become increasingly popular. In low-code reporting applications, the report template designer provides a simple and easy-to-use drag-and-drop report design solution. By dragging a variety of report components (such as detailed tables, classification tables, cross lines, free tables, statistical charts, bar charts, pie charts, etc.), adjusting the layout and setting style properties, etc., the report can be visualized, quickly and personalized, which can help users quickly create and customize their own statistical analysis reports.
[0003] With the widespread use of low-code reporting applications, there are more and more requirements for real-time interaction in the rendering and display of data in some report components. Users can perform some data filtering operations on the report application to achieve real-time and diversified statistical analysis and display of report data. In response to the filtering and query requirements of report component data, we proposed a report data filtering and query method based on SQL parameter transmission. Summary of the invention
[0004] The present application provides a report data screening and filtering query method based on SQL parameter transmission to solve the above-mentioned problems.
[0005] This application provides a report data screening and filtering query method based on SQL parameter transmission, including the following methods:
[0006] S1. Configure the data source and query SQL in the report template parser definition file;
[0007] S2. Then the dynamic report calls the report background service API. After receiving the query data request, the report background service API first queries the system database to query the report configuration information defined by the report template;
[0008] S3. After the report template parser parses the report template definition information, the data source information and query SQL configured in the report definition are handed over to the SQL construction executor to perform the query database operation, generate the query result set, and save it in the target data database;
[0009] S4, the query result set is handed over to the report data constructor to construct and generate report data according to the data construction requirements defined in the report definition template;
[0010] S5. Finally, the formatted report data is returned to the front-end report display.
[0011] Preferably, the SQL construction executor includes parameter passing construction, SQL construction and SQL query.
[0012] Preferably, the report service based on the system database can also continue to construct SQL based on the query target data database according to the data statistical requirements defined in the report template.
[0013] Preferably, the SQL construction executor includes the following method:
[0014] S31, after the placeholders in the SQL statement are parsed by the construction constructed by parameter passing, security verification and parameter replacement are completed;
[0015] S32, executing the constructed SQL statement and data source information according to the construction execution service of the original report system to perform the query database operation;
[0016] S33. Finally, the report backend server API returns the query result set to the report template constructor, and the report template constructor then constructs the corresponding formatted report data according to the report template definition and returns it.
[0017] Preferably, the parameter construction of the SQL construction executor can refer to the parameter placeholder replacement mode of the Mybatis class, and can also realize the situation where the where filtering conditions are different when passing a null value and a non-null value.
[0018] Preferably, the report data screening and filtering query method constructed based on the SQL parameter transmission includes dynamic reports, report background service API, system database and target data database, and the report background service API data transmission connects the dynamic reports, system database and target data database.
[0019] Preferably, the dynamic report includes a filter screening component and a report display, and the filter screening component is connected to the report display through the filter condition transmission.
[0020] Preferably, the report background service API includes a report template parser, a report template constructor and an SQL construction executor. The report template parser data transmission connects the report template constructor and the SQL construction executor, and the SQL construction executor data transmission connects the report template constructor. The report template parser is also connected to the system database, and the SQL construction executor is also connected to the target data database.
[0021] The above technical solution provided by the embodiment of the present application has the following advantages compared with the prior art:
[0022] The overall structure provided by the embodiment of the present application sets dynamic SQL and placeholders for the data source in the report template definition, passes SQL parameters when querying data, and then replaces the parameters by the back-end service based on the dynamic SQL parameter passing method to construct SQL that meets the filtering conditions, queries the database to obtain data, and can achieve accurate filtering and query of the data, solving the problem that traditional report query data cannot be filtered at the database SQL level.
[0023] The traditional construction method can only construct a nested table subquery statement based on the report template definition SQL, and filter and pass parameters outside the subquery to construct the where condition. However, the present invention can directly replace the parameters on the SQL defined in the report template, reducing the situation where the SQL nested subquery level is too deep.
[0024] The data screening query method of dynamic SQL parameter transmission designed by the present invention can achieve accurate filtering and screening queries when the number of table records in the database is large, avoids the frequent construction of nested subquery SQL during background queries, and reduces the performance degradation in the database memory due to too many nested subquery layers, thereby saving background database and server resource overhead and improving the service response speed of the report application. BRIEF DESCRIPTION OF THE DRAWINGS
[0025] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.
[0026] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.
[0027] Figure 1 It is a schematic diagram of the overall method flow of the present invention;
[0028] Figure 2 This is an example diagram of the screening, filtering and parameter transmission structure of the present invention;
[0029] Figure 3 It is a schematic diagram of the conventional overall operation flow of the present invention;
[0030] Figure 4 This is an example diagram of a conventional screening and filtering structure of the present invention;
[0031] Figure 5 A schematic diagram showing data source, query SQL, and parameter configuration for the report of the present invention;
[0032] Figure 6A schematic diagram of the configuration of the report template definition of the present invention;
[0033] Figure 7 This is a schematic diagram of SQL placeholder parameters of the present invention;
[0034] Figure 8 Binding the linked component graph to the linked component of the present invention;
[0035] Fig. 9 A schematic diagram of generating a query SQL for a report SQL parameter transfer structure (null value transfer) in the back-end structure of the present invention;
[0036] Fig.10 This is a schematic diagram of the SQL parameter passing structure (passing null value) of the present invention;
[0037] Fig.11 A schematic diagram of generating a query SQL for a report SQL parameter transfer structure (transferring non-null values) in the back-end structure of the present invention;
[0038] Fig.12 This is a schematic diagram of the SQL parameter passing structure (passing non-null values) of the present invention. DETAILED DESCRIPTION
[0039] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of this application.
[0040] Various embodiments of the present application may exist in the form of a range. It should be understood that the description in the form of a range is only for convenience and simplicity and should not be understood as a hard limit to the scope of the present application; therefore, it should be considered that the range description has specifically disclosed all possible sub-ranges and single values within the range. For example, it should be considered that the range description from 1 to 6 has specifically disclosed sub-ranges, such as from 1 to 3, from 1 to 4, from 1 to 5, from 2 to 4, from 2 to 6, from 3 to 6, etc., as well as single numbers within the range, such as 1, 2, 3, 4, 5 and 6, which are applicable regardless of the range. In addition, whenever a numerical range is indicated in the present application, it is meant to include any quoted numbers (fractions or integers) within the indicated range. Unless otherwise specified, various raw materials, reagents, instruments and equipment used in the present application, etc., can be purchased from the market or can be prepared by existing equipment.
[0041] In the present application, in the absence of any contrary description, the directional words used, such as "upper" and "lower", are specifically the directions of the drawings in the accompanying drawings. In addition, in the present application, the terms "include", "comprise", etc. refer to "including but not limited to". In the present application, relational terms such as "first" and "second" are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. In the present application, "and / or" describes the association relationship of the associated objects, indicating that three relationships may exist, for example, A and / or B, which can represent: A exists alone, A and B exist at the same time, and B exists alone. Wherein A, B can be singular or plural. In the present application, "at least one" refers to one or more, and "plural" refers to two or more. "At least one", "at least one of the following" or similar expressions refer to any combination of these items, including any combination of singular items or plural items. For example, "at least one of a, b, or c" or "at least one of a, b and c" can both mean: a, b, c, ab, i.e. a and b, ac, bc or abc, where a, b, c can be single or plural, respectively.
[0042] like Figure 1 to Figure 2 As shown: The embodiment of the present application provides 1. a report data screening and filtering query method based on SQL parameter transmission, including the following methods:
[0043] S1. Configure the data source and query SQL in the report template parser definition file;
[0044] S2. Then the dynamic report calls the report background service API. After receiving the query data request, the report background service API first queries the system database to query the report configuration information defined by the report template;
[0045] S3. After the report template parser parses the report template definition information, the data source information and query SQL configured in the report definition are handed over to the SQL construction executor to perform the query database operation, generate the query result set, and save it in the target data database;
[0046] S4, the query result set is handed over to the report data constructor to construct and generate report data according to the data construction requirements defined in the report definition template;
[0047] S5. Finally, the formatted report data is returned to the front-end report display.
[0048] The SQL structure executor includes parameter passing structure, SQL structure and SQL query.
[0049] The report service based on the system database can also continue to construct SQL based on the query target data database according to the data statistical requirements defined in the report template.
[0050] The SQL construction executor includes the following methods:
[0051] S31, after the placeholders in the SQL statement are parsed by the construction constructed by parameter passing, security verification and parameter replacement are completed;
[0052] S32, executing the constructed SQL statement and data source information according to the construction execution service of the original report system to perform the query database operation;
[0053] S33. Finally, the report backend server API returns the query result set to the report template constructor, and the report template constructor then constructs the corresponding formatted report data according to the report template definition and returns it.
[0054] The parameter construction of the SQL construction executor can refer to the parameter placeholder replacement mode of the Mybatis class, and can also realize the situation where the where filtering conditions are different when passing null values and non-null values.
[0055] The report data screening and filtering query method based on the SQL parameter transmission structure includes dynamic reports, report background service API, system database and target data database. The report background service API data transmission connects the dynamic reports, system database and target data database.
[0056] The dynamic report includes a filter screening component and a report display, and the filter screening component is connected to the report display through the filter condition transmission.
[0057] The report backend service API includes a report template parser, a report template constructor and an SQL construction executor. The report template parser data transmission connects the report template constructor and the SQL construction executor, and the SQL construction executor data transmission connects the report template constructor. The report template parser is also connected to the system database, and the SQL construction executor is also connected to the target data database.
[0058] Specifically: the report application supports users to personalize and quickly make reports, such as changing the report type, layout, attributes, data, etc. The data source for the statistical summary display in the report is generally from the background database. The statistical construction method of the report data is often to configure the data source and query SQL in the report template definition file, and then the report component calls the background API. After the background receives the query data request, it first queries the system application database to query the report configuration information defined in the report template. After the report template parser parses the report configuration definition information, it returns the data source information and query SQL configured in the report definition, which are handed over to the SQL construction executor to execute the database query operation, and then returns the query result set, which is handed over to the report data constructor to construct and generate report data according to the data construction requirements defined in the report definition template (of course, this part of the report service based on the database can also continue to construct the report data statistics SQL based on the constructed query database SQL according to the report template data statistical definition requirements), and finally returns the formatted report data to the front-end report component, so that it can be rendered and displayed on the page by the report component; the overall workflow of the conventional report service is as follows Figure 3 As shown;
[0059] With the widespread application of reporting services and the gradual expansion of user needs, dynamic reports in reports also provide some component interaction functions to support users to filter and display statistical data, such as drop-down boxes, residual boxes and other linkage interactive components.
[0060] The data filtering function of the linkage interaction component is implemented by modifying the SQL configured in the report in the background. The specific method is to use the query SQL configured in the report template definition as a subquery statement, perform an external nested table subquery based on the subquery result, and use the filtering parameters as parameters in the where condition query in the external nested query. The following figure shows an example of the general filtering construction: Figure 4 As shown;
[0061] The report service should focus on the data construction and rendering display of the report. Due to the characteristics of nested table subqueries, when the target table in the database has a large amount of data, this method has slow query performance, large resource overhead, and slow service response speed. Therefore, the present invention targets this filtering and screening interaction requirement;
[0062] In the report service, the filtering and querying data method based on SQL parameter transmission is defined in the report template design. After the linkage filtering and screening component establishes a linkage filtering and screening relationship with the linked report display component, the SQL configured in the linked report component can be modified, and the symbols uniformly agreed with the back-end SQL parsing constructor are used to occupy the place in the SQL statement. The present invention refers to the parameter placeholder design of Mybatis, modifies the SQL construction executor, and adds the parsing and replacement construction function of the SQL parameter transmission parameter to the original function. The overall process of querying data in the report background after the transformation is as follows: Figure 1 ;
[0063] When the report queries SQL, the SQL parameter parsing constructor first parses the placeholders in the SQL statement. After completing security verification and parameter replacement, the constructed SQL statement and data source information are executed according to the construction execution service of the original report system to query the database. Finally, the server returns the query result set to the report data constructor, and the report data constructor constructs the corresponding formatted report data according to the report definition template. In addition, the SQL parameter construction can refer to the parameter placeholder replacement mode of the Mybatis class, and it can also realize the different where filter conditions when passing null values and non-null values.
[0064] In the above process, although the report query data result set is the same, the two are implemented in different ways. The SQL builder replaces the parameters of the SQL defined in the report template instead of the previous nested subquery and where condition concatenation query method. The SQL construction example is shown in the figure below. Figure 2 As shown;
[0065] Example
[0066] The steps for completing the SQL parameter query data business of the low-code report application report component are as follows:
[0067] 1. Report template definition, configuration of data source, SQL, SQL parameter placeholder parameters
[0068] The report component in the present invention needs to configure the data source, including the data source, the query data SQL, and the placeholder parameters that need to be dynamically passed in the SQL, such as Figure 5 ;
[0069] This demonstration case configures the data source to configure "grade input parameter" as a SQL statement parameter placeholder. The backend SQL parameter parser can parse and identify the parameter placeholder. The right side is the result set content of the SQL execution query. The SQL configuration and its query result set serve as the basic data of the report service. Subsequent reports can use the data queried by this SQL as the basic data for generating reports.
[0070] 2. The report component configures the report template definition information, including selecting the report statistics type, report style, etc. Figure 6 As shown;
[0071] In this case, the report type is selected as a cross table, the column dimension is selected to count by the fields grade and class, the row dimension is selected to count by the field subject, the indicator is selected as the score field, and its average value is calculated.
[0072] 3. Linkage component binding parameters
[0073] The interactive filtering and linkage requirements of report components require the implementation of SQL dynamic parameter transmission. You need to select the linked component and set the input parameter name. For example, in this case, the drop-down box component selects the grade input parameter and selects the student score statistical classification table as the linked report. When the drop-down box is selected in the interaction, the data value selected in the drop-down box will be passed in as the SQL parameter value and handed over to the back-end SQL parsing structure to implement data filtering and screening input parameters. Figure 7 and Figure 8 shown.
[0074] 4. The data value selected in the drop-down box is passed in as the specific value of the SQL parameter, which is then constructed by the back-end SQL parameter parser to filter and select the input parameters. This case selects empty parameter values and actual parameter values to illustrate the SQL construction of data query parameters of the report component and the formatted data generated and rendered by the report service.
[0075] The SQL structures for backend data query and parameter passing are as follows: Fig. 9 and Fig.11 As shown;
[0076] The formatted data generated and rendered by the report service when an empty parameter value is passed or an actual parameter value is passed are as follows: Fig.10 and Fig.12 As shown;
[0077] The above is only a specific implementation of the present application, so that those skilled in the art can understand or implement the present application. It will be apparent to those skilled in the art that various modifications to these embodiments are possible, and the general principles defined in the present application can be implemented in other embodiments without departing from the spirit or scope of the present application. Therefore, the present application will not be limited to these embodiments shown in the present application, but will conform to the widest scope consistent with the principles and novel features applied for by the present application.
Claims
1. A report data screening and filtering query method based on SQL parameter transmission, characterized in that: The following methods are included: S1. Configure the data source and query SQL in the report template parser definition file; S2. Then the dynamic report calls the report background service API. After receiving the query data request, the report background service API first queries the system database to query the report configuration information defined by the report template; S3. After the report template parser parses the report template definition information, the data source information and query SQL configured in the report definition are handed over to the SQL construction executor to perform the query database operation, generate the query result set, and save it in the target data database; S4, the query result set is handed over to the report data constructor to construct and generate report data according to the data construction requirements defined in the report definition template; S5. Finally, the formatted report data is returned to the front-end report display.
2. According to the method of claim 1, the report data screening and filtering query method based on SQL parameter transmission is characterized in that: The SQL structure executor includes parameter passing structure, SQL structure and SQL query.
3. The method for screening and filtering report data based on SQL parameter transmission according to claim 1, characterized in that: The report service based on the system database can also continue to construct SQL based on the query target data database according to the data statistical requirements defined in the report template.
4. The method for screening and filtering report data based on SQL parameter transmission according to claim 1, characterized in that: The SQL construction executor includes the following methods: S31, after the placeholders in the SQL statement are parsed by the construction constructed by parameter passing, security verification and parameter replacement are completed; S32, executing the constructed SQL statement and data source information according to the construction execution service of the original report system to perform the query database operation; S33. Finally, the report backend server API returns the query result set to the report template constructor, and the report template constructor then constructs the corresponding formatted report data according to the report template definition and returns it.
5. The method for screening and filtering report data based on SQL parameter transmission according to claim 1, characterized in that: The parameter construction of the SQL construction executor can refer to the parameter placeholder replacement mode of the Mybatis class, and can also realize the situation where the where filtering conditions are different when passing null values and non-null values.
6. The method for screening and filtering report data based on SQL parameter transmission according to claim 1, characterized in that: The report data screening and filtering query method based on the SQL parameter transmission structure includes dynamic reports, report background service API, system database and target data database. The report background service API data transmission connects the dynamic reports, system database and target data database.
7. A report data screening and filtering query method based on SQL parameter transmission according to claim 6, characterized in that: The dynamic report includes a filter screening component and a report display, and the filter screening component is connected to the report display through the filter condition transmission.
8. The method for screening, filtering and querying report data based on SQL parameter transmission according to claim 6, characterized in that: The report backend service API includes a report template parser, a report template constructor and an SQL construction executor. The report template parser data transmission connects the report template constructor and the SQL construction executor, and the SQL construction executor data transmission connects the report template constructor. The report template parser is also connected to the system database, and the SQL construction executor is also connected to the target data database.