Data query method, system and equipment and storage medium

Abstract

The invention provides a data query method, system and device and a storage medium, and the method comprises the steps: defining access connection information of a new database, and adding the new database into a connection pool; defining corresponding relations between tables and fields among different databases in the connection pool, and storing the corresponding relations between the tables and the fields among the different databases in a centralized warehouse; according to a preset business requirement, a preset query statement corresponding to each table in each database is disassembled, and the preset query statement is packaged in a storage process corresponding to the table, and the storage process is a pre-compiled SQL statement; searching a data mapping rule corresponding to the query entity data in the warehouse according to the user query request, and matching a target table of at least one target database in the connection pool according to the data mapping rule; and returning a query result based on a preset query statement corresponding to the target table. Data in different databases can be integrated, and the data management efficiency is effectively improved.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a data query method, system, device and storage medium. Background Art

[0002] With the continuous development of information technology, more and more data query systems have been developed and applied to information management in various fields. Common data query systems need to rely on relational databases, from simple SQLite to complex large databases such as Oracle and Sybase.

[0003] In software engineering, clear and specific requirements allow software developers to analyze precise data structures, thereby realizing the development and implementation of data query systems as quickly as possible in a low-cost and efficient manner. However, in the actual application development process, users often encounter unclear user requirements, which leads to frequent changes in requirements and further to continuous modifications to completed configuration items in the software development process, increasing the cost investment and R&D cycle of software development, as well as the risk of software problems and defects. Summary of the invention

[0004] The present invention provides a data query method, system, device and storage medium, the main purpose of which is to simplify data query operations across databases, improve software development efficiency and reduce the workload of developers.

[0005] In a first aspect, an embodiment of the present invention provides a data query method, comprising:

[0006] Define access connection information of a new database and add the new database to a connection pool, wherein the access connection information includes a data source name, an access connection mode, a user name, a password and a data source driver type, and the connection pool includes multiple databases of different types;

[0007] Define the correspondence between tables and fields between different databases in the connection pool, so as to realize data mapping during cross-database query, and the correspondence between tables and fields between different databases is stored in a centralized warehouse;

[0008] According to the preset business requirements, the preset query statements corresponding to each table in each database are disassembled, and the preset query statements are encapsulated in the stored procedure corresponding to the table. The stored procedure is a pre-compiled SQL statement, and the stored procedure includes encapsulated table access and encapsulated data processing logic;

[0009] Searching the warehouse for a data mapping rule corresponding to the query entity data according to a user query request, and matching at least one target table of a target database in the connection pool according to the data mapping rule;

[0010] Return query results based on the preset query statement corresponding to the target table.

[0011] Furthermore, it also includes:

[0012] Whenever a user performs an update operation on any table in any database, a preset query statement corresponding to the table is called and updated, and fields of the table are updated.

[0013] Furthermore, the step of encapsulating the preset query statement in a stored procedure corresponding to the table includes:

[0014] The preset query statement is constructed in a parameterized manner, using placeholders to replace actual values ​​in the pre-compiled SQL statement.

[0015] Furthermore, it also includes:

[0016] If any database has not been used for more than a preset time threshold, the connection relationship of any database in the connection pool is destroyed.

[0017] Furthermore, it also includes:

[0018] Regularly detect the connection status of each database in the connection pool;

[0019] If the connection status of any database is abnormal, the abnormality is reported.

[0020] Furthermore, it also includes:

[0021] Batch processing is performed on multiple user query requests submitted in batches by the user.

[0022] Furthermore, the step of returning the query result based on the preset query statement corresponding to the target table includes:

[0023] Receiving initial query results corresponding to each of the target tables;

[0024] The formats of the initial query results are unified, and the final query results are output.

[0025] In a second aspect, an embodiment of the present invention provides a data query system, including:

[0026] A connection module is used to define access connection information of a new database and add the new database to a connection pool. The access connection information includes a data source name, an access connection mode, a user name, a password and a data source driver type. The connection pool includes multiple databases of different types.

[0027] A mapping module is used to define the correspondence between tables and fields between different databases in the connection pool, so as to realize data mapping during cross-database query, and the correspondence between tables and fields between different databases is stored in a centralized warehouse;

[0028] A pre-stored module is used to disassemble the preset query statements corresponding to each table in each database according to preset business requirements, and encapsulate the preset query statements in the stored procedures corresponding to the tables. The stored procedures are pre-compiled SQL statements, and the stored procedures include encapsulated table access and encapsulated data processing logic;

[0029] A matching module, configured to search the warehouse for a data mapping rule corresponding to the query entity data according to a user query request, and match the data mapping rule to a target table of at least one target database in the connection pool;

[0030] The query module is used to return the query result based on the preset query statement corresponding to the target table.

[0031] In a third aspect, an embodiment of the present invention provides a computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the steps of the above-mentioned data query method when executing the computer program.

[0032] In a fourth aspect, an embodiment of the present invention provides a computer storage medium, wherein the computer storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the above-mentioned data query method are implemented.

[0033] The advantages of a data query method, system, device and storage medium proposed by the present invention are described as follows:

[0034] 1. Economical, it simplifies the operation of different types of databases and different databases, improves software development efficiency, and reduces the need for developers to write complex SQL statements.

[0035] 2. Simplicity: It provides a graphical user interface for data source definition and data set definition, allowing non-technical personnel to easily configure and manage database connection information and query logic. It also provides dynamic access API functions, allowing subsequent developers to implement the addition, deletion, modification, query, analysis and display operations of relational databases by only writing a small number of scripts.

[0036] 3. Reliability: Using precompiled stored procedures and functions to execute queries reduces the amount of communication between the client and the server and improves the query speed. At the same time, parameterized queries prevent SQL injection attacks and enhance the security of the system.

[0037] 4. Easy to maintain, providing a version control system to record the history of each modification for easy backtracking and tracking. It has the function of automatically detecting the database connection status. BRIEF DESCRIPTION OF THE DRAWINGS

[0038] Figure 1 A flowchart of a data query method provided by an embodiment of the present invention;

[0039] Figure 2 A structural diagram of a data query system provided by an embodiment of the present invention;

[0040] Figure 3 A structural schematic diagram of a computer device is provided for an embodiment of the present invention.

[0041] The implementation, functional features and advantages of the technical solution of the present invention will be further explained in conjunction with embodiments and with reference to the accompanying drawings. DETAILED DESCRIPTION

[0042] The embodiments of the present application are described in detail below, and examples of the embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals throughout represent the same or similar elements or elements having the same or similar functions. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain the present application, and cannot be understood as limiting the present application.

[0043] In order to enable those skilled in the art to better understand the solutions of the present application, the technical solutions 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 only part of the embodiments of the present application, rather than all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those skilled in the art without making creative work are within the scope of protection of the present application.

[0044] In the embodiments of the present application, at least one refers to one or more; multiple refers to two or more. In the description of the present application, words such as "first", "second", "third", etc. are only used to distinguish the purpose of description, and cannot be understood as indicating or implying relative importance, nor can they be understood as indicating or implying order. In addition, the terms "first" and "second" are only used for descriptive purposes, and cannot be understood as indicating or implying relative importance or implicitly indicating the number of indicated technical features. Thus, the features defined as "first" and "second" may explicitly or implicitly include one or more of the features. In the description of the present application, the meaning of "multiple" is two or more, unless otherwise clearly and specifically defined.

[0045] References to "one embodiment" or "some embodiments" etc. described in this specification mean that one or more embodiments of the present application include a particular feature, structure or characteristic described in conjunction with the embodiment. Thus, in this specification, the terms "include", "comprises", "has" and their variations all mean "including but not limited to", unless otherwise specifically emphasized.

[0046] The present invention only provides a concise, reliable and efficient framework for database access, data analysis and data display in software development. A stored procedure is a set of pre-compiled SQL statements stored on a database server. When it needs to be executed, the client sends a command to the server to call the stored procedure, and the server will execute the corresponding stored procedure.

[0047] The server can be implemented by an independent server or a server cluster composed of multiple servers. The client can be a smart phone, a tablet computer, a laptop computer, a desktop computer, etc., but is not limited thereto. The client and the server can be connected via Bluetooth, USB (Universal Serial Bus) or other communication connection methods, and the embodiment of the present invention does not limit this. In the embodiment of the present invention, the execution subject is the client, and the database can be stored in the server. When the client needs to query data, the relevant data will be called in the database for search.

[0048] In the embodiment of the present invention, the data source definition sets the connection information of the database according to the provided database, including the data source name, access connection, user name, password and data source driver type. The data source name is the source of data in the database, the access connection refers to the address and port for accessing the database, the user name refers to the user name that can access the database, the password refers to the user password that can access the database, and the data source driver type refers to the interface or component for connecting to the database, etc.

[0049] Figure 1A flowchart of a data query method provided by an embodiment of the present invention is shown in FIG. Figure 1 As shown, the method includes:

[0050] The core of the present invention is to use stored procedures to encapsulate complex SQL logic, so that the application program does not need to care about the internal implementation details, but only needs to call these pre-compiled objects to complete the required functions.

[0051] S110, defining access connection information of a new database, and adding the new database to a connection pool, wherein the access connection information includes a data source name, an access connection mode, a user name, a password, and a data source driver type, and the connection pool includes multiple databases of different types;

[0052] The embodiment of the present invention provides a graphical user interface, which enables users to easily configure and manage access connection information of all databases that need to be connected. The access connection information includes but is not limited to the database server address, port number, user name, password and the driver type used.

[0053] The user can set a new database to be added to the connection pool on the graphical interface, and configure the relevant database connection parameter information according to the type of the new database; after the configuration is completed, use the test connection tool on the graphical interface to test whether the database connection is successful. If the database connection is unsuccessful, make corresponding configuration changes according to the interface prompts or check whether the database service is normal.

[0054] The embodiment of the present invention adopts a connection pool mechanism. Database connection pool is a technology for managing database connections. It creates a certain number of database connections in advance and saves these connections in a "pool". When an application needs to access a database, it can obtain a connection from the pool and return the connection to the pool after use, instead of re-creating and destroying the connection every time.

[0055] This can optimize the use efficiency of database connection resources and reduce the overhead caused by frequent creation and destruction of connections.

[0056] In addition, the embodiment of the present invention also sets a time limit for database connection. If the database is not used for more than a preset time threshold, it indicates that the database connection has not been used for a long time, and the relevant connection is destroyed to reduce the resources of the connection pool. Among them, the preset time threshold can be determined according to actual conditions, and the embodiment of the present invention does not specifically limit this.

[0057] Furthermore, the embodiment of the present invention also introduces a function of automatically detecting the database connection status. If a connection abnormality is detected, an attempt is made to re-establish the connection to ensure the continuity and stability of the system.

[0058] Furthermore, in the embodiment of the present invention, the database connection supports encrypted connection to ensure that sensitive data will not be stolen or tampered with during transmission; at the same time, access permission control can be set to limit the database access scope of specific users.

[0059] In the actual implementation process, after the user logs in to the system's graphical user interface, he selects the "Data Source Definition" operation to display the data source definition configuration interface. The user clicks the "Add Database" button and selects the type of database to connect to in the pop-up window. Fill in the database connection parameters, such as the server address, port number, user name, password, and the type of driver used.

[0060] If needed, you can set advanced options such as automatic detection of database connection, database connection time limit, encrypted connection, access permission control, etc. After completing all necessary fields, click the "Test Connection" button.

[0061] The system will try to establish a connection with the specified database and return a connection message. Users can change configuration information or check database services based on the returned connection message.

[0062] S120, defining the correspondence between tables and fields between different databases in the connection pool, thereby realizing data mapping during cross-database query, and the correspondence between tables and fields between different databases is stored in a centralized warehouse;

[0063] In order to automatically detect and read the structural information of the configured database in the embodiment of the present invention, by connecting to each database and using the built-in metadata query statement, detailed information such as the table name, field name, data type, primary key, foreign key, etc. of each database can be obtained.

[0064] All collected metadata are stored in a centralized warehouse, such as a dedicated database or local file system. This warehouse not only needs to save the static structure information of each database table, but also can be dynamically updated to reflect the latest database changes.

[0065] In the warehouse management process, add a version control system to record the historical versions of the data and clearly record each update operation. In this way, users can browse and query metadata and historical records in an intuitive way in the graphical user interface.

[0066] Users can customize the correspondence between tables and fields in different databases in the interface, thereby realizing data mapping during cross-database queries. For example, you can select tables in two different databases and specify the corresponding fields between them, even if these fields have different names or types in different databases.

[0067] At the same time, it simplifies user operations and can automatically provide possible field correspondences based on metadata analysis. When a user queries the data of an entity, the data of the same entity in different databases can be found based on the user-defined data mapping settings.

[0068] In addition, all mapping rules are saved in a centralized repository to ensure consistent mapping relationships even after restarts. At the same time, these rules can be exported and imported to facilitate the backup and migration of mapping rules.

[0069] S130, according to preset business requirements, disassemble the preset query statements corresponding to each table in each database, and encapsulate the preset query statements in the stored procedure corresponding to the table, wherein the stored procedure is a pre-compiled SQL statement, and the stored procedure includes encapsulated table access and encapsulated data processing logic;

[0070] For preset business needs, it supports encapsulating the preset query statements corresponding to each table in the database decomposed from these business needs, encapsulating these logics into stored procedures, reducing the communication between the client and the server, and improving the query speed.

[0071] A stored procedure is a set of predefined compiled SQL code blocks that can receive input parameters, perform specific operations, and return result sets or output parameters. Once a stored procedure is created, it can be included in a data set definition as an independent data object.

[0072] This means that developers can directly reference these objects to perform data operations without understanding the underlying SQL logic. In addition, stored procedures can prevent SQL injection attacks and enhance system security. Users can manage and maintain stored procedures in a graphical user interface, including creation, modification, and deletion to ensure that they meet current business needs. All stored procedures are stored in a centralized warehouse, and the application version control system is used to manage historical version records of data.

[0073] In the actual implementation process, after the user logs in to the system and completes the "data source definition" operation, he performs the "data set definition" operation and displays the data set definition configuration interface; the user selects the "metadata management" operation and displays the metadata management interface; the user clicks the "metadata initialization" button, the system automatically connects to the selected database, and uses the built-in metadata query statement to obtain detailed information such as table name, field name, data type, primary key, foreign key, etc.; the static result information of each table is displayed on the interface.

[0074] When the user clicks the "Save Metadata" button, the system will store all the collected metadata in a centralized warehouse; when the user clicks the "Export Metadata" button, the system will export the metadata for backup. The user selects the "Field Mapping" operation to display the field mapping interface; the user clicks the "Add Field Mapping" button to select a field in different database tables for mapping association, and can rename the mapped fields to implement joint queries of different database tables; the user clicks the "Update Field Mapping" button to check whether the database table structure has changed based on the defined field mapping, and a pop-up interface will prompt the user of the changed field mapping relationship; the user clicks the "Delete Field Mapping" button to delete the unnecessary field mapping; the user clicks the "Save Field Mapping" button to save the user-defined and updated field mapping relationship; the user selects the "Stored Procedure" operation to display the stored procedure interface.

[0075] The user clicks the "Create Stored Procedure" button, and creates a new stored procedure in the interface, defines input parameters, execution logic, and output results. The user clicks the "Execute" button to test whether the SQL of the currently edited stored procedure can run normally.

[0076] The user clicks the "Run" button, enters the parameters of the stored procedure, and views the results of the stored procedure.

[0077] The user clicks the "Edit" button and selects a stored procedure to edit in the stored procedure list. The user clicks the "Delete" button and selects a stored procedure to delete in the stored procedure list.

[0078] This module lays the foundation for subsequent data retrieval, allowing queries to obtain required information across multiple tables while using stored procedures to encapsulate complex business logic.

[0079] S140, searching the warehouse for a data mapping rule corresponding to the query entity data according to the user query request, and matching a target table of at least one target database in the connection pool according to the data mapping rule;

[0080] S150: Return query results based on the preset query statement corresponding to the target table.

[0081] When performing a query, a user query request is input, and the corresponding data mapping rules are searched in the warehouse according to the user query request. The query entity data is the data contained in the data query request and needs to be searched, which can be determined based on actual conditions, and the embodiment of the present invention does not make specific limitations on this.

[0082] Finally, according to the data mapping rules, a match is made in the connection pool to obtain the matched target database and target table, and a search is performed in the target table to obtain the search result.

[0083] In some embodiments, it also includes:

[0084] Whenever a user performs an update operation on any table in any database, a preset query statement corresponding to the table is called and updated, and fields of the table are updated.

[0085] In the embodiment of the present invention, an additional status field is introduced into the data table to identify the current status of each row record in the data table. The current status includes the status of adding, modifying, deleting, and history.

[0086] Whenever a user operates a record, the corresponding update stored procedure statement of each table is called to update the processing status field. Through the trigger or event monitoring mechanism, the system can capture all insert, update and delete operations on the database table in real time.

[0087] Once a change is detected, the corresponding status information is updated immediately. For some high-concurrency scenarios, the system adopts a delayed execution strategy to temporarily store the change request in the queue and process it in batches when the time is right. This not only reduces the pressure on the database, but also ensures the orderliness and consistency of the change operation. If an error occurs during the processing, the system can roll back some of the completed operations based on the saved status information to ensure the consistency and integrity of the data.

[0088] All update operations are encapsulated in a transaction to ensure that the entire operation is either completely successful or completely failed. Even if multiple tables or cross-database operations are involved, data consistency can be maintained. When a link fails, the transaction can be terminated in time and the compensation mechanism can be activated to roll back some of the executed operations to prevent data inconsistency problems.

[0089] The embodiment of the present invention also provides a special batch operation API, allowing users to submit multiple insert, update or delete requests at one time. Compared with executing one by one, batch processing significantly reduces the number of network round trips and database connection overhead, and improves overall efficiency.

[0090] In some embodiments, it also includes:

[0091] Batch processing is performed on multiple user query requests submitted in batches by the user.

[0092] For very large data sets, it is automatically divided into several small batches for processing according to the actual situation. This method can ensure the response speed of a single operation without exhausting system resources due to excessive data volume. When performing batch operations, the number of concurrent queries will be dynamically adjusted to ensure the security and stability of multi-threaded processing. After each batch operation is completed, a detailed execution report will be returned, including the number of successful and failed records and their reasons, to help users quickly locate and solve problems.

[0093] Every update operation will be fully recorded for easy query and analysis. The log content includes but is not limited to the operation field status, the tables and fields involved, the values ​​before and after the change, the execution time and user information, etc., and the log information will be included in the version control management system.

[0094] In the actual implementation process, after the user logs in to the system, he selects the "Data Update" operation, and the system displays the data update interface. Before starting this process, the user needs to complete two key steps: "Data Source Definition" and "Data Set Definition". The user enters the record to be updated in the edit box, clicks the "Confirm" button to submit the update request, and the system introduces an additional status field in the data table to identify the current status of each row of records in the data table, and calls the corresponding update stored procedure statement of each table to update the status field. The data update progress is displayed on the interface. When the user selects batch operation, enter the statement to be batch processed in the edit box, click the "Batch Processing" button and submit the statement.

[0095] After each batch operation is completed, the system will return a detailed execution report, including the number of successful and failed records and their reasons, to help users quickly locate and solve problems. The system will perform batch processing and dynamically adjust the number of concurrent queries to ensure the security and stability of multi-threaded processing. Users can select the "Log View" operation to view historical data update records and understand the details of each operation.

[0096] As an implementation mode, the steps of encapsulating the preset query statement in a stored procedure corresponding to the table include:

[0097] The preset query statement is constructed in a parameterized manner, using placeholders to replace actual values ​​in the pre-compiled SQL statement.

[0098] Use stored procedures to precompile complex SQL statements and save them on the database server. Precompiled queries can be optimized on the database server side, for example, by analyzing the query plan to select the optimal index path, so that each time the same query is executed, it can be completed in the fastest way. This method not only improves the speed of query execution, but also reduces the amount of communication between the client and the server. The client only needs to send a short call command instead of a complete SQL statement. Because of this, SQL statements are not directly exposed to the network, reducing security risks such as SQL injection. Even if an attacker obtains some information, it is difficult to easily construct harmful SQL instructions.

[0099] All queries in stored procedures are constructed in a parameterized manner, that is, placeholders are used in SQL statements instead of actual values. When the query is executed, the system binds specific parameter values ​​to these placeholders to generate the final query statement.

[0100] When the same query needs to be executed multiple times, parameterized queries can avoid recompiling the execution plan each time.

[0101] The parameterization method performs type checking before binding parameters, reducing hard-coded string errors and ensuring that the parameters passed to the query conform to the expected data type. If the type does not match, an exception is thrown to avoid potential errors.

[0102] The parameterization method ensures that the input data will not be misunderstood as part of the SQL code, effectively preventing SQL injection tools. The parameterization method can be used as a template for multiple query instances, reducing the amount of code that needs to be written and maintained.

[0103] For frequently executed but insignificant query results, the system applies an intelligent caching strategy, which dynamically decides whether to cache a query result based on factors such as query frequency, data update cycle, and memory resources.

[0104] To ensure the consistency of cached data, the system monitors changes in related tables. Once data updates are detected, the affected cache entries are immediately cleared to ensure that subsequent queries can obtain the latest data. The system uses the LRU (Least Recently Used) cache invalidation algorithm to automatically manage cache space, giving priority to retaining recently used or long-lived data to improve cache hit rates.

[0105] The result set returned by the query can be automatically converted to a data format suitable for processing, such as JSON, XML, CSV, etc., according to the needs of the application. This flexibility simplifies the subsequent data processing process, and developers do not need to manually parse the original tabular data. The system has a series of built-in templates for defining the conversion rules from relational data to the target format.

[0106] Users can easily adjust these templates through configuration files or interface tools to meet different business needs. For large data sets, the system gradually reads and converts the result set to avoid loading too much data at one time and occupying a large amount of memory resources. During the conversion process, the system will perform necessary optimization on the result set, such as removing redundant fields, merging similar records, etc., to ensure that the output data is both accurate and compact, reducing transmission bandwidth and storage overhead.

[0107] After logging into the system, users can use parameterized queries and stored procedures to retrieve business data efficiently. Before starting this process, users need to complete two key steps: "data source definition" and "data set definition". Data source definition requires users to define the database used by the project. Data set definition requires users to set the association information between multiple database table entities and specify the stored procedures used to perform complex queries. When users initiate specific business data retrieval, the system uses parameterized queries.

[0108] This means using placeholders instead of actual values ​​in SQL statements and calling stored procedures to query data. The system automatically precompiles and optimizes query plans, selecting the best index path to increase data query speed. For query results that are frequently executed but do not change much, the system intelligently caches the query results.

[0109] Dynamically decide whether to cache the results of a query based on factors such as query frequency, data update cycle, and memory resources to reduce database pressure, speed up response time, and improve user experience.

[0110] The system processes the query result set, converts it into appropriate data, and returns the result to the query statement caller. The user selects the "Data Query" operation, and the system displays the data query interface. The interface list displays the data query records previously executed in the project.

[0111] In some embodiments, the step of returning the query result based on the preset query statement corresponding to the target table includes:

[0112] Receiving initial query results corresponding to each of the target tables;

[0113] The formats of the initial query results are unified, and the final query results are output.

[0114] When each database returns the initial query results, these initial query results are collected and merged. Through intelligent algorithms, the engine can process result sets in different formats to generate the final query results. For example, it can aggregate or sort the same fields from different databases.

[0115] When the data formats obtained from different sources are inconsistent, the system automatically identifies these differences and applies the necessary format conversion rules. For example, standardize the date and time formats, or unify the string encoding to UTF-8. The system has a series of built-in templates to define the conversion rules from the source format to the target format. Users can easily adjust these templates through configuration files or interface tools to meet different business needs. For large-capacity data sets, read and convert the result sets step by step to avoid loading too much data at one time and occupying a lot of memory resources. During the conversion process, the system verifies and corrects the data to ensure that the converted data is both accurate and in the expected format. For example, check the value range, remove redundant characters, etc. to ensure the uniformity and correctness of the final result set.

[0116] In the actual implementation process, after the user logs in to the system, he selects the "multi-database union" operation, and the system displays the multi-database union interface. Before starting this process, the user needs to complete two key steps: "data source definition" and "data set definition". The system queries the qualified data from the data source selected by the user, and the interface displays the integrated unified result set. The user can format the display result data by configuring the module of the result. After receiving the query request, the system assigns the query to the correct database according to the mapping rules of the "data set definition" and automatically adjusts the query syntax to meet the requirements of different databases. Call the stored procedure in the target database to query the data, collect the results returned by each database, and aggregate the results for output. The user can select the "log view" operation to view the historical multi-database union query records and understand the details of each operation.

[0117] The advantages of a data query method proposed by the present invention are described as follows:

[0118] 1. Economical, it simplifies the operation of different types of databases and different databases, improves software development efficiency, and reduces the need for developers to write complex SQL statements.

[0119] 2. Simplicity: It provides a graphical user interface for data source definition and data set definition, allowing non-technical personnel to easily configure and manage database connection information and query logic. It also provides dynamic access API functions, allowing subsequent developers to implement the addition, deletion, modification, query, analysis and display operations of relational databases by only writing a small number of scripts.

[0120] 3. Reliability: Using precompiled stored procedures and functions to execute queries reduces the amount of communication between the client and the server and improves the query speed. At the same time, parameterized queries prevent SQL injection attacks and enhance the security of the system.

[0121] 4. Easy to maintain, providing a version control system to record the history of each modification for easy backtracking and tracking. It has the function of automatically detecting the database connection status.

[0122] Figure 2 A structural diagram of a data query system provided by an embodiment of the present invention is shown in FIG. Figure 2 As shown, the system includes:

[0123] The connection module 210 is used to define the access connection information of a new database and add the new database to a connection pool. The access connection information includes a data source name, an access connection mode, a user name, a password and a data source driver type. The connection pool includes multiple databases of different types.

[0124] A mapping module 220, which is used to define the correspondence between tables and fields between different databases in the connection pool, so as to realize data mapping during cross-database query, and the correspondence between tables and fields between different databases is stored in a centralized warehouse;

[0125] The pre-stored module 230 is used to disassemble the preset query statements corresponding to each table in each database according to the preset business requirements, and encapsulate the preset query statements in the stored procedures corresponding to the tables. The stored procedures are pre-compiled SQL statements, and the stored procedures include encapsulated table access and encapsulated data processing logic;

[0126] A matching module 240, configured to search the warehouse for a data mapping rule corresponding to the query entity data according to a user query request, and match a target table of at least one target database in the connection pool according to the data mapping rule;

[0127] The query module 250 is used to return a query result based on a preset query statement corresponding to the target table.

[0128] This embodiment is a system embodiment corresponding to the above method embodiment, and its specific implementation process is the same as that of the above method embodiment. For details, please refer to the above embodiment, and this system embodiment will not be repeated here.

[0129] Each module in the above data query system can be implemented in whole or in part by software, hardware, or a combination thereof. Each module can be embedded in or independent of a processor in a computer device in the form of hardware, or can be stored in a memory in a computer device in the form of software, so that the processor can call and execute operations corresponding to each module.

[0130] Figure 3 A schematic diagram of the structure of a computer device is provided for an embodiment of the present invention. Figure 3As shown, the computer device can be a server. The computer device includes a processor, a memory, a network interface and a database connected via a system bus. Among them, the processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a computer storage medium and an internal memory. The computer storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the computer storage medium. The database of the computer device is used to store data generated or obtained during the execution of a data query method, such as access connection information and joint operations. The network interface of the computer device is used to communicate with an external terminal via a network connection. When the computer program is executed by the processor, a data query method is implemented.

[0131] In one embodiment, a computer device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the steps of a data query method in the above embodiment when executing the computer program. Alternatively, the processor implements the functions of each module / unit in the embodiment of a data query system when executing the computer program.

[0132] In one embodiment, a computer storage medium is provided, on which a computer program is stored, and when the computer program is executed by a processor, the steps of a data query method in the above embodiment are implemented. Alternatively, when the computer program is executed by a processor, the functions of each module / unit in the above embodiment of a data query system are implemented.

[0133] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided in the present application can include non-volatile and / or volatile memory. Non-volatile memory may include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory may include random access memory (RAM) or external cache memory. As an illustration and not limitation, RAM is available in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM, direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM.

[0134] Those skilled in the art can clearly understand that for the convenience and simplicity of description, only the division of the above-mentioned functional units and modules is used as an example. In actual applications, the above-mentioned functions can be distributed and completed by different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above.

[0135] The embodiments described above are only used to illustrate the technical solutions of the present invention, rather than to limit the same. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that the technical solutions described in the aforementioned embodiments may still be modified, or some of the technical features may be replaced by equivalents. Such modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be included in the protection scope of the present invention.

Claims

1. A data query method, characterized in that: include: Define access connection information of a new database, and add the new database to a connection pool, wherein the access connection information includes a data source name, an access connection mode, a user name, a password, and a data source driver type, and the connection pool includes multiple databases of different types; Define the correspondence between tables and fields between different databases in the connection pool, so as to realize data mapping during cross-database query, and the correspondence between tables and fields between different databases is stored in a centralized warehouse; According to the preset business requirements, the preset query statements corresponding to each table in each database are disassembled, and the preset query statements are encapsulated in the stored procedure corresponding to the table. The stored procedure is a pre-compiled SQL statement, and the stored procedure includes encapsulated table access and encapsulated data processing logic; Searching the warehouse for a data mapping rule corresponding to the query entity data according to a user query request, and matching at least one target table of a target database in the connection pool according to the data mapping rule; Based on the preset query statement corresponding to the target table, the query result is returned.

2. The data query method according to claim 1, characterized in that: Also includes: Whenever a user performs an update operation on any table in any database, a preset query statement corresponding to the table is called and updated, and fields of the table are updated.

3. The data query method according to claim 1, characterized in that: The step of encapsulating the preset query statement in a stored procedure corresponding to the table includes: The preset query statement is constructed in a parameterized manner, using placeholders to replace actual values ​​in the pre-compiled SQL statement.

4. The data query method according to claim 1, characterized in that: Also includes: If any database has not been used for more than a preset time threshold, the connection relationship of any database in the connection pool is destroyed.

5. The data query method according to claim 4, characterized in that: Also includes: Regularly detect the connection status of each database in the connection pool; If the connection status of any database is abnormal, the abnormality is reported.

6. The data query method according to claim 1, characterized in that: Also includes: Batch processing is performed on multiple user query requests submitted in batches by the user.

7. The data query method according to claim 1, characterized in that: The step of returning the query result based on the preset query statement corresponding to the target table includes: Receiving initial query results corresponding to each of the target tables; The formats of the initial query results are unified, and the final query results are output.

8. A data query system, characterized in that: include: A connection module is used to define access connection information of a new database and add the new database to a connection pool. The access connection information includes a data source name, an access connection mode, a user name, a password and a data source driver type. The connection pool includes multiple databases of different types. A mapping module is used to define the correspondence between tables and fields between different databases in the connection pool, so as to realize data mapping during cross-database query, and the correspondence between tables and fields between different databases is stored in a centralized warehouse; A pre-stored module is used to disassemble the preset query statements corresponding to each table in each database according to preset business requirements, and encapsulate the preset query statements in the stored procedures corresponding to the tables. The stored procedures are pre-compiled SQL statements, and the stored procedures include encapsulated table access and encapsulated data processing logic; A matching module, configured to search the warehouse for a data mapping rule corresponding to the query entity data according to a user query request, and match the data mapping rule to a target table of at least one target database in the connection pool; The query module is used to return the query result based on the preset query statement corresponding to the target table.

9. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the steps of the data query method according to any one of claims 1 to 7 are implemented.

10. A computer storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the steps of the data query method according to any one of claims 1 to 7 are implemented.

Images