A data query processing method across databases

Abstract

The application provides a cross-database data query processing method, comprising the following steps: S1: writing a cross-database SQL query statement and submitting execution; S2: parsing the cross-database SQL query statement to obtain a use clause parsing result set and an output clause; S3: traversing the use clause parsing result set and executing the query clause to obtain a clause result set and store the clause result set in an in-memory table information set; S4: creating the in-memory table information set into an in-memory table; and S5: executing the output clause and obtaining a return result set. The method provided by the application enables a developer to only write a cross-database SQL query statement in a self-defined format, without writing complex codes, and each query clause can be tested on a corresponding database client tool, facilitating the developer to write, test and troubleshoot; the cross-database SQL query statement in the self-defined format has a simple structure, is similar to a traditional SQL query statement, is easy to read, understand and modify and maintain.

Description

TECHNICAL FIELD

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

[0002] In the field of report development, developers often encounter the situation that the index data of report requirements is located in different databases, which are located in different servers, including homogeneous databases (databases of the same type) or heterogeneous databases (databases of different types); for the report requirements of homogeneous databases, the existing development scheme is: the developer executes SQL query statements on different databases by writing code to obtain index data, and then merges the index data into report data required by the user;

[0003] However, the existing development scheme has the following disadvantages:

[0004] 1. Insufficient support for heterogeneous databases: the existing cross-database query method can only run in a homogeneous database environment and cannot handle joint queries of heterogeneous databases;

[0005] 2. Complex and non-reusable code: the existing cross-database query method can only process index data of the same summary level and cannot process index data of different summary levels, so the developer needs to write a large amount of code for different summary levels, and once the report requirements change, the developer has to modify a large amount of code, which is not universal, the code is complex and cannot be reused, increasing the development complexity and time cost. SUMMARY

[0006] The purpose of the present application is to solve the problems in the prior art and provide a cross-database data query processing method.

[0007] To achieve the above purpose, the present application adopts the following technical scheme:

[0008] A cross-database data query processing method, comprising the following steps:

[0009] S1: writing and submitting a cross-database SQL query statement for execution;

[0010] S11: the user writes a cross-database SQL query statement;

[0011] The cross-database SQL query statement is composed of a use clause and an output clause, and the format is: use clause; output clause.

[0012] The format of the use clause is: use data source name {query clause} as alias; wherein the data source name is used to specify the data source; the query clause is the SQL query statement of the database corresponding to the data source name, and supports with syntax, stored procedures and ordinary select statements;

[0013] The alias is the table name of the memory table in the in-memory database.

[0014] The output clause is the SQL query statement in the in-memory database, and the table name of the output clause is the alias in the use clause, which supports with syntax and ordinary select statements;

[0015] Further, the use clause can be one or more, and each use clause must end with a semicolon (;); each alias in the use clause is unique.

[0016] S12: submit the completed cross-database SQL query statement to the application for execution.

[0017] S2: the application parses the cross-database SQL query statement to obtain a use clause parsing result set and an output clause;

[0018] S21: create a cross-database SQL query statement parser;

[0019] S22: parse the content in the cross-database SQL query statement and save it to the parsing result;

[0020] 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 to the parsing result;

[0021] The parsing result is composed of a use clause parsing result set and a string type output clause;

[0022] The data type of the use clause parsing result set is List collection type, and the element is the use clause parsing result.

[0023] The use clause parsing result is composed of three parts of strings, namely: data source name, query clause, and alias;

[0024] S3: traverse the use clause parsing result set and execute the query clause to obtain a clause result set and store it in the in-memory table information set;

[0025] S31: traverse each element use clause parsing result in the use clause parsing result set;

[0026] S32: Obtain the specified data source from the global data source according to the data source name in the current element; obtain a JDBC connection through the specified data source;

[0027] S33: Create a Statement object using the JDBC connection, execute the query clause in the current element to obtain a result set, and encapsulate it as a clause result set;

[0028] The clause result set is composed of metadata information and data information.

[0029] The encapsulation includes: obtaining metadata from the result set, traversing each row of the metadata to obtain column name and data type information, and storing them in the metadata information; traversing each row in the result set and each column in the current row; obtaining the column name from the metadata and obtaining the column value from the current column based on the column name, to form a key-value pair with the column name as the key and the column value as the value, and storing it in a Map collection; storing the Map collection in a List collection, which is the data information.

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

[0031] S34: Store the alias and the clause result set in the in-memory table information collection.

[0032] The in-memory table information collection is of Map data type. The key is the alias, and the value is the clause result set.

[0033] S4: Create the in-memory table information collection as an in-memory table;

[0034] 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;

[0035] S42: Obtain the root hierarchy RootSchema from the JDBC connection;

[0036] The RootSchema is used to manage all database objects;

[0037] S43: Traverse each key-value pair in the in-memory table information collection, and wrap the value in the key-value pair, i.e. the clause result set, as an in-memory table;

[0038] The specific method of wrapping is: create an in-memory table, determine the in-memory table structure according to the metadata information in the clause result set, use an iterator to wrap the data information into an iterable object, which is used as the data of the in-memory table. Take the key in the key-value pair, i.e. the alias, as the table name, and store the in-memory table in the root hierarchy RootSchema.

[0039] S5: executing the output clause and obtaining a returned result set;

[0040] S51: creating a Statement object using a JDBC driver, executing the output clause, and returning a result set of the output clause;

[0041] S52: traversing each row element in the result set and encapsulating the result set into a List collection, i.e., returning the result set.

[0042] The encapsulation includes: obtaining metadata from the result set; traversing each row and each column of the current row in the result set; obtaining a column name from the metadata and a column value from the current column based on the column name; constructing a key-value pair with the column name as the key and the column value as the value, storing the key-value pair into a Map collection, storing the Map collection into a List collection, and returning the List collection.

[0043] Compared with the prior art, the present application has the following beneficial effects:

[0044] The present application designs a cross-database data query processing method, based on a cross-database SQL query statement in a self-defined format, and applies the information of a data source name, a query clause, an alias, and an output clause in the cross-database SQL query statement in the self-defined format to execute the query clause to obtain a clause result, create an in-memory database, encapsulate the clause result into an in-memory table, execute the output clause, and obtain result data. A developer only needs to write a cross-database SQL query statement in the self-defined format, without the need to write complex codes, and each query clause can be tested on a corresponding database client tool, facilitating the developer to write, test, and troubleshoot. The cross-database SQL query statement in the self-defined format has a simple structure, is similar to a traditional SQL query statement, is easy to read, understand, and modify and maintain. BRIEF DESCRIPTION OF DRAWINGS

[0045] Figure 1 The present application is a cross-database data query processing method. DETAILED DESCRIPTION

[0046] To further understand the purpose, structure, features, and functions of the present application, the following embodiments are described in detail.

[0047] As shown in FIG. 1, a cross-database data query processing method includes the following steps: Figure 1

[0048] S1: writing a cross-database SQL query statement and submitting execution;

[0049] S11: a user writes a cross-database SQL query statement; ​

[0050] The cross-database SQL query statement is composed of a use clause and an output clause, and has the format: use clause; output clause;

[0051] The use clause has the format: use data source name {query clause} as alias;

[0052] The data source name of the use clause is used to specify a data source; the query clause is a SQL query statement of a database corresponding to the data source name, and supports with syntax, a stored procedure, and a normal select statement;

[0053] The alias is a table name of a memory table in a memory database.

[0054] The output clause is a SQL query statement in the memory database, and the table name of the output clause is the alias in the use clause, and the output clause supports with syntax and a normal select statement;

[0055] Further, the use clause can be one or more, and each use clause must end with a semicolon (;); each alias in the use clause is unique;

[0056] S12: submit the completed cross-database SQL query statement to an application for execution.

[0057] By introducing the custom cross-database SQL query syntax, a user can flexibly access multiple databases and perform a joint query between different databases, thereby fully utilizing scattered data resources; the WITH clause, the stored procedure, and the normal SELECT statement are supported, the flexibility and expressiveness of the query are increased, and complex data query scenarios can be processed.

[0058] S2: parse the cross-database SQL query statement to obtain a use clause parsing result set and an output clause;

[0059] S21: create a cross-database SQL query statement parser;

[0060] S22: parse the content in the cross-database SQL query statement and save it to a parsing result;

[0061] The cross-database SQL query statement parser receives and parses the cross-database SQL query statement submitted by a user, extracts a data source name, a query clause, an alias, and an output clause therefrom, and saves the extracted content to a parsing result;

[0062] The parsing result is composed of a use clause parsing result set and a string type output clause;

[0063] The data type of the use clause parsing result set is a List collection type, and the element is a use clause parsing result.

[0064] The use clause parsing result is composed of three parts of strings, namely: data source name, query clause, and alias.

[0065] S3: Traverse the use clause parsing result set and execute the query clause to obtain a clause result set and store it in the in-memory table information set.

[0066] S31: Traverse each element use clause parsing result in the use clause parsing result set.

[0067] S32: Obtain the specified data source from the global data source according to the data source name in the current element; obtain a JDBC connection through the specified data source.

[0068] S33: Create a Statement object using the JDBC connection and execute the query clause in the current element to obtain a result set, and encapsulate it as a clause result set.

[0069] The clause result set is composed of metadata information and data information.

[0070] The encapsulation is: obtain metadata from the result set, traverse each row of metadata to obtain column name and data type information, and store it in the metadata information; traverse each row in the result set and each column in the current row; obtain the column name from the metadata, and obtain the column value from the current column based on the column name, to form a key-value pair with the column name as the key and the column value as the value, and store it in the Map collection, and store the Map collection in the List collection, which is the data information.

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

[0072] S34: Store the alias and clause result set in the in-memory table information set.

[0073] The in-memory table information set is of Map data type. The key is the alias, and the value is the clause result set.

[0074] S4: Create the in-memory table information set as an in-memory table.

[0075] S41: Load the dynamic data management framework apache calcite driver, and create a JDBC connection through the JDBC connection string.

[0076] S42: Obtain a root schema RootSchema from the JDBC connection; the RootSchema is used to manage all database objects;

[0077] S43: Traverse each key-value pair in the in-memory table information set, and wrap the value in the key-value pair, i.e., the clause result set, as an in-memory table.

[0078] The specific method of wrapping is as follows: create an in-memory table, determine the in-memory table structure according to the metadata information in the clause result set, use an iterator to wrap the data information as an iterable object, and the object is used as the data of the in-memory table. Take the key in the key-value pair, i.e., the alias, as the table name, and store the in-memory table in the root schema RootSchema.

[0079] By using the Apache Calcite dynamic management framework, the in-memory table is created, which provides a flexible way to process temporary data sets and reduces the dependence on the underlying database structure. Users can directly operate data in memory, improving query efficiency.

[0080] S5: Execute the output clause and obtain a return result set;

[0081] S51: Create a Statement object using the JDBC connection, execute the output clause, and return the result set of the output clause;

[0082] S52: Traverse each row element in the result set and encapsulate it into a List set, i.e., a return result set.

[0083] The encapsulation is as follows: obtain metadata from the result set; traverse each row and each column of the current row in the result set; obtain the column name from the metadata, and obtain the column value from the current column based on the column name; construct a key-value pair with the column name as the key and the column value as the value, store it in a Map set, store the Map set in a List set, and return the List set.

[0084] The present application has been described by the above-mentioned related embodiments, however, the above-mentioned embodiments are only examples of implementing the present application. It must be pointed out that the disclosed embodiments do not limit the scope of the present application. On the contrary, modifications and improvements made without departing from the spirit and scope of the present application are within the scope of the patent protection of the present application.

Claims

1. A method for processing data query across databases, the method comprising: The method comprises the following steps: ​ S1: writing a cross-database SQL query statement and submitting execution; S11: a user writes a cross-database SQL query statement; The cross-database SQL query statement is composed of a use clause and an output clause, and has the format: use clause; The output clause; The format of the use clause is: use data source name {query clause} as alias; wherein the query clause is an SQL query statement of a database corresponding to the data source name; The output clause is an SQL query statement in a memory database, and the table name of the output clause is the alias in the use clause; The alias is the table name of a memory table in the memory database; Each use clause ends with a semicolon; each alias in the use clause is unique; S12: submitting the written cross-database SQL query statement to an application for execution; S2: the application parses the cross-database SQL query statement to obtain a use clause parsing result set and an output clause; S21: creating a cross-database SQL statement parser; S22: parsing the content in the cross-database SQL query statement and saving it into a parsing result; S3: traversing the use clause parsing result set and executing the query clause to obtain a clause result set and save it into a memory table information set; S4: creating a memory table from the memory table information set; S5: executing the output clause and obtaining a result set of the output clause.

2. 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, the query clause, the alias and the output clause therefrom, and saves the extracted content into a parsing result; The parsing result is composed of a use clause parsing result set and a string type output clause; The data type of the use clause parsing result set is a List set type, and the element is a use clause parsing result; The use clause parsing result is composed of three parts of strings, namely: data source name, query clause and alias.

3. The method for processing data query across databases according to claim 1, wherein: The specific content of step S3 is as follows: S31: traversing each element use clause parsing result in the use clause parsing result set; S32: obtaining a specified data source from a global data source according to the data source name in the current element; obtaining a JDBC connection through the specified data source; S33: using the JDBC connection to create a Statement object, executing the query clause in the current element to obtain a result set, and encapsulating the result set into a clause result set; The clause result set is composed of metadata information and data information; The metadata information is of a Map data type, the key is a column name, and the value is a column data type; The data information is a List set, and the element of the set is of a Map data type; S34: storing the alias and the clause result set into a memory table information set; The memory table information set is of a Map data type; The key is the alias, and the value is the clause result set.

4. The method for processing data query across databases according to claim 1, wherein: The specific content of step S4 is as follows: S41: Load the dynamic data management framework apache calcite driver, create a JDBC connection with the dynamic data management framework apache calcite through a JDBC connection string; S42: Obtain a root hierarchy RootSchema from the JDBC connection; the RootSchema is used to manage all database objects; S43: Traverse each key-value pair in the in-memory table information set, and wrap the value in the key-value pair, that is, the clause result set, as an in-memory table; The specific method of wrapping is: creating an in-memory table, determining the in-memory table structure according to the metadata information in the clause result set, using an iterator to wrap the data information into an iterable object, which is used as the data of the in-memory table; taking the key in the key-value pair, that is, the alias, as the table name, and storing the in-memory table into the root hierarchy RootSchema.

5. The method for processing data query across databases according to claim 1, wherein: The specific content of step S5 is 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 element in the result set to encapsulate it into a List collection, that is, return the result set; Obtain metadata from the result set; traverse each row and each column of the current row in the result set; obtain the column name from the metadata, and obtain the column value from the current column based on the column name; construct a key-value pair with the column name as the key and the column value as the value, store it into a Map collection, store the Map collection into a List collection, and return the List collection.

Images