Cross-database data query processing method

Abstract

The invention provides a cross-database data query processing method. The cross-database data query processing method comprises the following steps: S1, compiling a cross-database SQL query statement, and submitting and executing the cross-database SQL query statement; s2, analyzing the cross-database SQL query statement to obtain a use word and sentence analysis result set and output clauses; s3, traversing the use word and sentence analysis result set and executing query clauses to obtain a clause result set and storing the clause result set into a memory table information set; s4, creating the memory table information set into a memory table; s5, executing the output clauses and obtaining a return result set; according to the method provided by the invention, developers only need to write cross-database SQL query sentences in a self-defined format without writing complex codes, and each query sentence can run and test on a corresponding database client tool, so that the developers can write, test and debug conveniently; the cross-database SQL query statement in the self-defined format is simple in structure, similar to a traditional SQL query statement, and convenient to read, understand, modify and maintain.

Description

Technical Field

[0001] The present invention relates to the field of report development, and in particular to a cross-database data query processing method. Background Art

[0002] In the field of report development, developers often encounter situations where the indicator data required for reports resides in different databases on different servers, including homogeneous databases (databases of the same type) or heterogeneous databases (databases of different types). For reporting requirements on homogeneous databases, the current development solution is for developers to write code to execute SQL queries on different databases to obtain indicator data, and then merge this indicator data into the report data required by the user. However, the existing development solutions have the following drawbacks: Insufficient support for heterogeneous databases: Existing cross-database query methods can only run in homogeneous database environments and cannot handle joint queries of heterogeneous databases; Complex and non-reusable code: Existing cross-database query methods can only process indicator data at the same aggregation level, and cannot process indicator data at different aggregation levels. Developers need to write a large amount of code for different aggregation levels. Once the reporting requirements change, developers have to modify a large amount of code. The code is not universal, complex, and cannot be reused, which increases the complexity and time cost of development. Summary of the Invention

[0003] The purpose of the present invention is to solve the shortcomings of the prior art and to propose a cross-database data query processing method.

[0004] To achieve the above object, the present invention adopts the following technical solutions: A cross-database data query processing method includes the following steps: S1: Write a cross-database SQL query statement and submit it for execution; S11: The user writes a cross-database SQL query statement; The cross-database SQL query statement consists of a use clause and an output clause, and the format is: use clause; output clause.

[0005] The format of the use clause is: use data source name {query clause} as alias; the data source name is used to specify the data source; the query clause is the SQL query statement for the database corresponding to the data source name, supporting with syntax, stored procedures, and ordinary select statements; The alias is the table name of the memory table in the memory database.

[0006] The output clause is an SQL query statement in the memory database. The table name of the output clause is the alias in the use clause, and supports with syntax and ordinary select statements; Furthermore, there can be one or more use clauses, and each use clause must end with a semicolon (;); each alias in a use clause is unique; S12: Submit the prepared cross-database SQL query statement to the application for execution.

[0007] S2: Apply parsing to the cross-database SQL query statement to obtain the use clause parsing result set and output clause; S21: Create a cross-database SQL query statement parser; S22: Parse the content of the cross-database SQL query statement and save it to the parsing result; The cross-database SQL query statement parser receives and parses the cross-database SQL query statement submitted by the user, extracts the data source name, query clause, alias, and output clause, and saves the extracted content to the parsing result; The parsing result consists of a use clause parsing result set and a string type output clause; The data type of the use statement parsing result set is a List collection type, and the elements are the use statement parsing results.

[0008] The parsed result of the use clause consists of three character strings: data source name, query clause, and alias. S3: Traverse the use clause parsing result set and execute the query clause to obtain the clause result set and store it in the memory table information set; S31: traverse each element of the use sentence parsing result set; S32: Get the specified data source from the global data source according to the data source name in the current element; obtain the JDBC connection through the specified data source; S33: Create a Statement object using the JDBC connection, execute the query clause in the current element to obtain the result set, and encapsulate it into a clause result set; The clause result set consists of two parts: metadata information and data information.

[0009] The encapsulation is as follows: obtaining metadata from the result set, traversing each row of the metadata, obtaining column names and data type information, and storing them in the metadata information; traversing each row in the result set and each column of the current row; obtaining column names from the metadata, and obtaining column values ​​from the current column based on the column names, forming key-value pairs with the column names as keys and the column values ​​as values, storing them in a Map collection, and storing the Map collection in a List collection, where the List collection is the data information.

[0010] The metadata information is of Map data type, the key is the column name, and the value is the data type of the column, such as int, varchar(10), date, etc.

[0011] S34: Store the alias and clause result set into the memory table information set.

[0012] The memory table information set is of Map data type, with the key being the alias and the value being the clause result set.

[0013] S4: Create a memory table from the memory table information set; S41: Load the dynamic data management framework Apache Calcite driver and create a JDBC connection with the dynamic data management framework Apache Calcite through the JDBC connection string; S42: Get the root hierarchy RootSchema from the JDBC connection; The RootSchema is used to manage all database objects; S43: traverse each key-value pair in the memory table information set, and package the value in the key-value pair, i.e., the clause result set, into a memory table; The packaging method is as follows: creating a memory table, determining the memory table structure based on the metadata in the clause result set, using an iterator to package the data into an iterable object, which serves as the memory table data. The memory table is stored in the root schema, using the key (alias) in the key-value pair as the table name.

[0014] S5: Execute the output clause and get the returned result set; S51: Use the JDBC driver to create a Statement object, execute the output clause, and return the result set of the output clause; S52: Traverse each row of elements in the result set and encapsulate it into a List set, that is, return the result set.

[0015] The encapsulation is as follows: obtaining metadata from a result set; traversing each row in the result set and each column of the current row; obtaining column names from the metadata, and obtaining column values ​​from the current column based on the column names, forming key-value pairs with the column names as keys and the column values ​​as values, storing the pairs in a Map collection, storing the Map collection in a List collection, and returning a List collection.

[0016] Compared with the prior art, the present invention has the following beneficial effects: The present invention designs a cross-database data query processing method based on a cross-database SQL query statement in a custom format. By applying and parsing the information of the data source name, query clause, alias, and output clause in the cross-database SQL query statement in the custom format, the query clause is executed to obtain the clause result, an in-memory database is created, the clause result is encapsulated into a memory table, the output clause is executed, and the result data is obtained. Developers only need to write a cross-database SQL query statement in a custom format without writing complex code, and each query clause can be run and tested on the corresponding database client tool, which is convenient for developers to write, test, and debug. The cross-database SQL query statement in the custom format has a simple structure, similar to traditional SQL query statements, and is easy to read, understand, modify, and maintain. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] Figure 1 The present invention is a flowchart of the steps of a cross-database data query processing method. DETAILED DESCRIPTION

[0018] In order to provide a further understanding of the purpose, structure, features, and functions of the present invention, the present invention is described in detail below with reference to the embodiments.

[0019] like Figure 1 As shown, a cross-database data query processing method includes the following steps: S1: Write a cross-database SQL query statement and submit it for execution; S11: The user writes a cross-database SQL query statement;

[0020] The cross-database SQL query statement consists of a use clause and an output clause, and the format is: use clause; output clause; The format of the use clause is: use data source name {query clause} as alias; The data source name of the use clause is used to specify the data source; the query clause is the SQL query statement for the database corresponding to the data source name, supporting with syntax, stored procedures, and ordinary select statements; The alias is the table name of the memory table in the memory database.

[0021] The output clause is an SQL query statement in the memory database. The table name of the output clause is the alias in the use clause, and supports with syntax and ordinary select statements; Furthermore, there can be one or more use clauses, and each use clause must end with a semicolon (;); each alias in a use clause is unique; S12: Submit the prepared cross-database SQL query statement to the application for execution.

[0022] By introducing customized cross-database SQL query syntax, users can flexibly access multiple databases and conduct joint queries between different databases, thereby fully utilizing dispersed data resources. Support for WITH clauses, stored procedures, and ordinary SELECT statements increases query flexibility and expressiveness, enabling the handling of complex data query scenarios.

[0023] S2: Apply parsing to the cross-database SQL query statement to obtain the use clause parsing result set and output clause; S21: Create a cross-database SQL query statement parser; S22: Parse the content of the cross-database SQL query statement and save it to the parsing result; The cross-database SQL query statement parser receives and parses the cross-database SQL query statement submitted by the user, extracts the data source name, query clause, alias, and output clause, and saves the extracted content to the parsing result; The parsing result consists of a use clause parsing result set and a string type output clause; The data type of the use statement parsing result set is a List collection type, and the elements are the use statement parsing results.

[0024] The parsed result of the use clause consists of three character strings: data source name, query clause, and alias. S3: Traverse the use clause parsing result set and execute the query clause to obtain the clause result set and store it in the memory table information set; S31: traverse each element of the use sentence parsing result set; S32: Get the specified data source from the global data source according to the data source name in the current element; obtain the JDBC connection through the specified data source; S33: Create a Statement object using the JDBC connection, execute the query clause in the current element, obtain the result set, and encapsulate it as a clause result set; The clause result set consists of two parts: metadata information and data information.

[0025] The encapsulation is as follows: obtaining metadata from the result set, traversing each row of the metadata, obtaining column names and data type information, and storing them in the metadata information; traversing each row in the result set and each column of the current row; obtaining column names from the metadata, and obtaining column values ​​from the current column based on the column names, forming key-value pairs with the column names as keys and the column values ​​as values, storing them in a Map collection, and storing the Map collection in a List collection, where the List collection is the data information.

[0026] The metadata information is of Map data type, the key is the column name, and the value is the data type of the column, such as int, varchar(10), date, etc.

[0027] S34: Store the alias and clause result set into the memory table information set.

[0028] The memory table information set is of Map data type, with the key being the alias and the value being the clause result set.

[0029] S4: Create a memory table from the memory table information set; S41: Load the Apache Calcite driver for the dynamic data management framework and create a JDBC connection using the JDBC connection string. S42: Obtain a root hierarchy structure RootSchema from the JDBC connection; the RootSchema is used to manage all database objects; S43: traverse each key-value pair in the memory table information set, and package the value in the key-value pair, i.e., the clause result set, into a memory table; The packaging method is as follows: creating a memory table, determining the memory table structure based on the metadata in the clause result set, using an iterator to package the data into an iterable object, which serves as the memory table data. The memory table is stored in the root schema, using the key (alias) in the key-value pair as the table name.

[0030] By using the Apache Calcite dynamic management framework to create memory tables, a flexible way to process temporary data sets is provided, reducing dependence on the underlying database structure; users can operate on data directly in memory, improving query efficiency.

[0031] S5: Execute the output clause and get the returned result set; S51: Create a Statement object using the JDBC connection, execute the output clause, and return the result set of the output clause; S52: Traverse each row of elements in the result set and encapsulate it into a List set, that is, return the result set.

[0032] The encapsulation is as follows: obtaining metadata from a result set; traversing each row in the result set and each column of the current row; obtaining column names from the metadata, and obtaining column values ​​from the current column based on the column names, forming key-value pairs with the column names as keys and the column values ​​as values, storing the pairs in a Map collection, storing the Map collection in a List collection, and returning a List collection.

[0033] The present invention has been described with reference to the above embodiments. However, the above embodiments are merely exemplary embodiments of the present invention. It should be noted that the disclosed embodiments do not limit the scope of the present invention. On the contrary, modifications and improvements that do not depart from the spirit and scope of the present invention are intended to be protected by the present invention.

Claims

1. A cross-database data query processing method, characterized by: The following steps are involved: S1: Write a cross-database SQL query statement and submit it for execution; S11: The user writes a cross-database SQL query statement; S12: Submit the prepared cross-database SQL query statement to the application for execution; S2: Apply parsing to the cross-database SQL query statement to obtain the use clause parsing result set and output clause; S21: Create a cross-database SQL statement parser; S22: Parse the content of the cross-database SQL query statement and save it to the parsing result; S3: Traverse the use clause parsing result set and execute the query clause to obtain the clause result set and store it in the memory table information set; S4: Create a memory table from the memory table information set; S5: Execute the output clause and obtain the result set of the output clause.

2. The cross-database data query processing method according to claim 1, characterized in that: In step S11, the cross-database SQL query statement consists of a use clause and an output clause, and the format is: use clause; output clause; The format of the use clause is: use data source name {query clause} as alias; where the query clause is the SQL query statement for the database corresponding to the data source name; The output clause is an SQL query statement in the memory database, and the table name of the output clause is the alias in the use clause; The alias is the table name of the memory table in the memory database; Each use clause ends with a semicolon; each alias in a use clause is unique.

3. The cross-database data query processing method according to claim 1, wherein: In step S22, the cross-database SQL query statement parser receives and parses the cross-database SQL query statement submitted by the user, extracts the data source name, query clause, alias, and output clause therein, and saves the extracted content into the parsing result; The parsing result consists of a use clause parsing result set and a string type output clause; The data type of the use statement parsing result set is a List set type, and the elements are the use statement parsing results; The use clause parsing result consists of three character strings: data source name, query clause, and alias.

4. The cross-database data query processing method according to claim 1, wherein: The specific content of step S3 is as follows: S31: traverse each element of the use sentence parsing result set; S32: Get the specified data source from the global data source according to the data source name in the current element; obtain the JDBC connection through the specified data source; S33: Create a Statement object using the JDBC connection, execute the query clause in the current element, obtain the result set, and encapsulate it as a clause result set; The clause result set consists of two parts: metadata information and data information; The metadata information is of Map data type, the key is the column name, and the value is the column data type; The data information is a List collection, and the elements of the collection are of Map data type; S34: storing the alias and clause result set into the memory table information set; The memory table information set is of Map data type; The key is the alias and the value is the clause result set.

5. The cross-database data query processing method according to claim 1, characterized in that: The specific contents of step S4 are as follows: S41: Load the dynamic data management framework Apache Calcite driver and create a JDBC connection with the dynamic data management framework Apache Calcite through the JDBC connection string; S42: Obtain the root hierarchy structure RootSchema from the JDBC connection; the RootSchema is used to manage all database objects; S43: traverse each key-value pair in the memory table information set, and package the value in the key-value pair, i.e., the clause result set, into a memory table; The specific packaging method is as follows: creating a memory table, determining the memory table structure according to the metadata information in the clause result set, using an iterator to package the data information into an iterable object, and using the object as the data of the memory table; using the key in the key-value pair, i.e., the alias, as the table name, and storing the memory table in the root hierarchy structure RootSchema.

6. The cross-database data query processing method according to claim 1, characterized in that: The specific contents of step S5 are as follows: S51: Create a Statement object using the JDBC connection, execute the output clause, and return the result set of the output clause; S52: Traverse each row of elements in the result set and encapsulate it into a List set, that is, return the result set; Get metadata from the result set; traverse each row in the result set and each column of the current row; get the column name from the metadata, and get the column value from the current column based on the column name, form a key-value pair with the column name as the key and the column value as the value, store it in a Map collection, store the Map collection in a List collection, and return a List collection.

Images