A database query method, device and equipment for splitting databases and tables

Abstract

The present application belongs to the technical field of database, and particularly relates to a database query method, device and equipment, the database query method comprises the following steps: obtaining an SQL statement and a data source configuration ID input by a terminal; when the SQL statement and the data source configuration ID are verified to be legal, obtaining a database and table splitting data source object from a cache according to the data source configuration ID; obtaining a result set of the database and table splitting database responding to the SQL statement execution through the database and table splitting data source object; and encapsulating the read result into response data and displaying the response data, wherein the response data is aggregated data, which helps to solve the problem that when ShardingJDBC is used as a database and table splitting scheme and a database and table splitting algorithm is used to accurately query data, the operation of the query data is difficult and the result cannot be aggregated and displayed.

Description

Technical Field

[0001] This invention belongs to the field of database technology, specifically relating to a method, apparatus, and device for querying sharded databases. Background Technology

[0002] Due to the massive amounts of data in today's IT industry, with single tables containing tens or hundreds of millions of records, traditional single-table solutions cannot guarantee query efficiency. Therefore, database sharding and table partitioning solutions have emerged. Currently, common database sharding and table partitioning solutions in the industry include MyCat and ShardingJDBC. MyCat is commercial software, a server-side database middleware that can directly connect to the database via code and distribute data by rewriting SQL to ensure data security. It is based on a proxy, overriding the MyCat protocol to disguise the MyCat server as a MyCat database. This operation results in lower efficiency and slightly higher overhead.

[0003] ShardingJDBC is an open-source framework, a native database middleware framework. It's a JAR file that rewrites native JDBC methods at the local application layer to implement database sharding. Primarily based on AOP principles, it performs SQL interception, parsing, rewriting, routing, and result aggregation locally within the web application, resulting in high efficiency. While ShardingJDBC is more efficient than MyCat, it requires code modification. In daily development, self-testing, and troubleshooting, accurately retrieving data often requires traditional connection tools like Navicat to connect to the database, then using sharding algorithms to determine the shard node containing the target value. This process is complex, and the query results for each table are displayed in a separate window, making aggregated results impossible. Summary of the Invention

[0004] In view of this, the purpose of the present invention is to provide a database query method, apparatus and device for sharded databases, which helps to solve the problem that when using ShardingJDBC as a database sharding scheme and the sharding algorithm to calculate accurate query data, the query data operation is difficult and the results cannot be aggregated and displayed.

[0005] According to a first aspect of the present invention, a method for querying a sharded database is provided, comprising:

[0006] Obtain the SQL statement and data source configuration ID input from the terminal, wherein the terminal and the data source configuration ID correspond one-to-one, the data source configuration ID is used to identify the data source configuration information stored in the configuration information database, and the SQL statement is used to operate the sharded database;

[0007] When the SQL statement and the data source configuration ID are valid, the sharded database and sharded table data source object is retrieved from memory according to the data source configuration ID. The sharded database and sharded table data source object is created according to the data source information and sharded database and sharded table information corresponding to the data source configuration ID.

[0008] The result set of the SQL statement execution response from the sharded database is obtained through the sharded database data source object.

[0009] The data in the result set that has been read is encapsulated into response data and the response data is displayed, wherein the response data is the aggregated data.

[0010] Preferably, obtaining the result set of the SQL statement execution response from the sharded database through the sharded database data source object includes:

[0011] Parse the statement type of the SQL statement;

[0012] The connection object of the sharded database is obtained through the sharded database data source object;

[0013] The preprocessing object corresponding to the statement type is obtained through the connection object;

[0014] The SQL statement is executed through the preprocessed object to obtain the result set of the SQL statement execution in response to the sharded database.

[0015] Preferably, the method further includes:

[0016] Verify the validity of the SQL statement and the data source configuration ID; and verify the validity of the SQL statement and the data source configuration ID, including:

[0017] Verify that the data source configuration ID contains data source information;

[0018] Verify whether the SQL statement is a valid SQL statement;

[0019] If the data source configuration ID has data source information and the SQL statement is a valid SQL statement, then the SQL statement and the data source configuration ID are valid.

[0020] Preferably, before obtaining the SQL statement and data source configuration ID input from the terminal, the method further includes:

[0021] Configure the data source information and the database sharding and table partitioning information;

[0022] In addition, configuring the data source information and the database sharding and table partitioning information includes:

[0023] A configuration information table is created in the configuration information database, wherein the configuration information table is used to store the data source information and the database sharding and table partitioning information;

[0024] Obtain configuration information, wherein the configuration information is used to configure the data source information and the database sharding and table partitioning information;

[0025] Verify the validity of the configuration information;

[0026] The valid configuration information is stored in the corresponding configuration information table;

[0027] Return the save result, which indicates whether the save was successful. The save result includes: save successful and save failed. A save successful result indicates that the data source information and the database sharding information were configured successfully.

[0028] Preferably, the configuration information table includes:

[0029] Data source configuration information table, data source information table, and database sharding information table;

[0030] The data source configuration information table is used to store the configured data source configuration name and data source configuration ID;

[0031] The data source information table is used to store data source information;

[0032] The database sharding and table partitioning information table is used to store sharding strategies.

[0033] Preferably, the configuration information includes:

[0034] Data source configuration information, data source information, and database sharding information;

[0035] The data source configuration information includes: data source configuration name;

[0036] The data source information includes: data source name, fully qualified driver class name, database connection URL address, database connection username and password;

[0037] The sharding and partitioning information includes: a set of data source names, logical table names, sharding columns, sharding and partitioning strategies, partition size, and partition suffix.

[0038] Preferably, the method further includes:

[0039] When the sharded database and sharded table data source object cannot be retrieved from memory, the sharded database and sharded table data source object is created and stored in memory according to the data source information corresponding to the data source configuration ID and the sharded database and sharded table information;

[0040] And, based on the data source information corresponding to the data source configuration ID and the database sharding information, create the database sharding data source object and store it in memory, including:

[0041] From the configuration information database, obtain the corresponding data source information and database sharding information according to the data source configuration ID;

[0042] Create the aforementioned database sharding and table sharding data source object;

[0043] Based on the data source information and the database sharding and table partitioning information, instantiate the database sharding and table partitioning data source object;

[0044] The instantiated database sharding and table sharding data source object is stored in memory.

[0045] According to a second aspect of the present invention, a database query apparatus for sharded databases and tables is provided, comprising:

[0046] The acquisition module is used to acquire the SQL statement and data source configuration ID input by the terminal. The terminal and the data source configuration ID correspond one-to-one. The data source configuration ID is used to identify the data source configuration information stored in the configuration information database. The SQL statement is used to operate the sharded database.

[0047] The execution module is used to retrieve a sharded database data source object from the cache based on the data source configuration ID when the SQL statement and the data source configuration ID are verified to be valid. The sharded database data source object is created based on the data source information and sharded database information corresponding to the data source configuration ID. The module also retrieves the result set of the sharded database's response to the SQL statement execution through the sharded database data source object.

[0048] The display module is used to encapsulate the read result set data into response data and display the response data, wherein the response data is the aggregated data.

[0049] Preferably, it further includes:

[0050] The verification module is used to verify whether the SQL statement and the data source configuration ID are valid.

[0051] According to a third aspect of the present invention, a database query device with sharded tables is provided, comprising: the apparatus described above.

[0052] The technical solutions provided by the embodiments of the present invention may include the following beneficial effects:

[0053] This application obtains the SQL statement and data source configuration ID input from the terminal. When the SQL statement and data source configuration ID are valid, the application retrieves the sharded database data source object from the cache based on the data source configuration ID. The application then obtains the result set of the sharded database's response to the SQL statement through the sharded database data source object, making data querying convenient. The retrieved results are encapsulated into response data and displayed. This response data is aggregated data, which helps solve the problem of difficult data querying and inability to aggregate and display results when using ShardingJDBC as a sharding solution and employing sharding algorithms to calculate accurate query data.

[0054] It should be understood that the above general description and the following detailed description are exemplary and explanatory only, and do not limit this application. Attached Figure Description

[0055] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with the invention and, together with the description, serve to explain the principles of the invention.

[0056] Figure 1 This is a flowchart illustrating a database query method with sharded tables according to an exemplary embodiment;

[0057] Figure 2 This is a schematic block diagram illustrating a database query apparatus with sharded tables according to an exemplary embodiment. Detailed Implementation

[0058] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numerals in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the present invention. Rather, they are merely examples of apparatuses and methods consistent with some aspects of the invention as detailed in the appended claims.

[0059] Please see Figure 1 , Figure 1 This is a flowchart illustrating a database sharding and table-sharding query method according to an exemplary embodiment, such as... Figure 1 As shown, this database sharding and table partitioning query method includes the following steps:

[0060] Step S11: Obtain the SQL statement and data source configuration ID input by the terminal, wherein the terminal and the data source configuration ID correspond one-to-one, the data source configuration ID is used to identify the data source configuration information stored in the configuration information database, and the SQL statement is used to operate the sharded database.

[0061] Step S12: When the SQL statement and the data source configuration ID are valid, the sharded database and sharded table data source object is obtained from memory according to the data source configuration ID. The sharded database and sharded table data source object is created according to the data source information and sharded database and sharded table information corresponding to the data source configuration ID.

[0062] Step S13: Obtain the result set of the SQL statement execution response from the sharded database through the sharded database data source object;

[0063] Step S14: Encapsulate the read result set data into response data and display the response data, wherein the response data is the aggregated data.

[0064] It should be noted that, in practice, the database sharding and table-sharding query method provided in this embodiment needs to be loaded and run in the controller of the electronic device.

[0065] The electronic devices include, but are not limited to, desktop computers, laptops, etc.

[0066] It is understood that the technical solution provided in this embodiment obtains the SQL statement and data source configuration ID input from the terminal. When the SQL statement and data source configuration ID are valid, the sharded database data source object is retrieved from the cache according to the data source configuration ID. The result set of the sharded database responding to the SQL statement is obtained through the sharded database data source object, making data query operations convenient. The retrieved results are encapsulated into response data and displayed. The response data is aggregated data, which helps to solve the problem that when ShardingJDBC is used as a sharding solution and the sharding algorithm is used to calculate accurate query data, the data query operation is difficult and the results cannot be aggregated and displayed.

[0067] In practice, the "SQL statements" mentioned in step S11 are used to operate on sharded databases. Specifically, users enter SQL statements related to operating on sharded databases in the terminal, such as DDL, DML, etc.

[0068] In practice, the "data source configuration ID" mentioned in step S11 is used to identify the data source configuration information stored in the configuration information database. In this embodiment, adding a data source configuration information to the configuration information database corresponds to generating a data source configuration ID.

[0069] In practice, the "terminal" mentioned in step S11 is used for user input of SQL statements. Specifically, this terminal is opened by clicking on the configured data source configuration, therefore there is a one-to-one correspondence between the terminal and the data source configuration ID. It can be understood that when the corresponding execution terminal is opened by clicking on the configured data source configuration and the corresponding SQL statement is executed, the corresponding data source configuration is used, and the query result is unique.

[0070] It's important to note that existing database terminals can only perform single-table queries, not multi-table queries. For example, if the user information table is split into two tables (user_info_1, user_info_2), the existing database terminal must first determine which table the user's information belongs to before it can perform the query. The advantage of this new terminal is that you only need to enter `select * from user_info` to retrieve the user data.

[0071] In practice, the "sharded database and sharded table data source object" mentioned in step S12 is used to execute SQL statements, obtain the results of the SQL statements, and encapsulate the data of the read results into the response data. In this embodiment, ShardingSphere's ShardingDataSource is used as the instantiation object of the sharded database and sharded table data source object. After the sharded database and sharded table data source object is instantiated, it is stored in memory. After subsequent modifications to the data source and sharding configuration, the memory cache needs to be cleared.

[0072] In practice, the method described in step S12 further includes: verifying the validity of the SQL statement and the data source configuration ID. Specifically, verifying the validity of the SQL statement and the data source configuration ID includes:

[0073] Verify that the data source configuration ID exists and that data source information is present; and

[0074] Verify whether the SQL statement is a valid SQL statement;

[0075] If the data source configuration ID has data source information and the SQL statement is a valid SQL statement, then the SQL statement and the data source configuration ID are valid.

[0076] Understandably, validating the SQL statement and the data source configuration ID ensures the legality and validity of the operation.

[0077] In practice, the method described in step S12 further includes:

[0078] When the sharded database and sharded table data source object cannot be retrieved from memory, the sharded database and sharded table data source object is created and stored in memory based on the data source information corresponding to the data source configuration ID and the sharded database and sharded table information.

[0079] Specifically, based on the data source information corresponding to the data source configuration ID and the database sharding information, the database sharding data source object is created and stored in memory, including:

[0080] 1. From the configuration information database, obtain the corresponding data source information and database sharding information based on the data source configuration ID.

[0081] It should be noted that the configuration information database stores the user-configured data source information and database sharding information. Once the data source information and database sharding information are configured, there is a mapping relationship with the data source configuration ID. Therefore, the corresponding data source information and database sharding information can be obtained through the data source configuration ID.

[0082] 2. Create the data source object for the sharded database and sharded tables.

[0083] In practice, this embodiment uses ShardingSphere's ShardingDataSource as the instantiation object of the sharding data source object. Creating the sharding data source object is equivalent to creating the ShardingDataSource object.

[0084] 3. Instantiate the sharded database and sharded table data source object based on the data source information and sharding information.

[0085] It is understandable that the ShardingDataSource object that has already been created is instantiated based on the data source information and the database sharding and table partitioning information.

[0086] In practice, this embodiment uses ShardingSphere's TableRule as the routing mapping configuration, which converts the obtained data source information and database sharding information into routing rules to realize the instantiation of ShardingDataSource objects. Specifically, the data source information is converted into data source routing (which data source to which SQL statement is executed), and the database sharding information is converted into table routing (which sharding table to which SQL statement is executed).

[0087] 4. Store the instantiated database sharding and table sharding data source object in memory.

[0088] It is understandable that the instantiated ShardingDataSource object is the same as the instantiated sharded database and table data source object, which is stored in memory. Since creating a sharded database and table data source object is time-consuming, storing the created object in memory effectively saves time and returns results faster when the terminal executes SQL statements.

[0089] In practice, step S13, "obtaining the result set of the SQL statement execution response from the sharded database through the sharded database data source object," specifically means:

[0090] 1. Parse the statement type of the SQL statement.

[0091] In practice, parsing the statement type of an SQL statement specifically involves determining whether the SQL statement is DDL or DML. This can be understood as obtaining different prepared statements (Statement objects) by parsing the statement type of the SQL statement.

[0092] 2. Obtain the connection object of the sharded database through the sharded database data source object.

[0093] 3. Obtain the preprocessing object corresponding to the statement type through the connection object.

[0094] It is understandable that the connection object (Connection) and the preprocessing object (Statement) of the sharded database are obtained sequentially through the sharded database data source object (DataSource). The preprocessing object (Statement) is determined by the statement type of the SQL statement.

[0095] 4. Execute the SQL statement through the preprocessed object to obtain the result set of the SQL statement execution response from the sharded database.

[0096] It should be noted that the result set in response to the SQL statement specifically refers to a data stream. The result set contains a lot of information, and the information used in response to the SQL statement is the data / row count in the result set ResultSet.

[0097] In practice, step S14, "encapsulating the read result set data into response data and displaying the response data," specifically means:

[0098] In practice, the response data is aggregated data. This means that by reading data from the result set to obtain valid information, then aggregating and encapsulating the read data into response data, and finally returning and displaying the response data, the aggregated data display is achieved.

[0099] In practice, before obtaining the SQL statement and data source configuration ID input from the terminal, the method further includes configuring the data source information and the database sharding and table partitioning information.

[0100] Specifically, configuring the data source information and the database sharding and table partitioning information includes:

[0101] 1. Create a configuration information table in the configuration information database.

[0102] In practice, the configuration information table is used to store the data source information and the database sharding and table partitioning information. Specifically, the configuration information table includes: a data source configuration information table, a data source information table, and a database sharding and table partitioning information table; the data source configuration information table stores the configured data source configuration name and data source configuration ID; the data source information table stores data source information; and the database sharding and table partitioning information table stores the table partitioning strategy.

[0103] In practice, three tables are created in the configuration information database: `t_data_source_config` for data source configuration information, `t_data_source` for data source information, and `t_table_config` for sharding and partitioning information. `t_data_source_config` records data source configuration information; `t_data_source` records data source information; and `t_table_config` records sharding and partitioning information. It should be noted that one data source configuration record corresponds to one or more data source records; one data source configuration record corresponds to zero or more sharding and partitioning records (a data source can have a single table, or multiple shards, and can query both a single table and multiple shards simultaneously).

[0104] In practice, the specific table design of the data source configuration information table t_data_source_config is shown in Table 1 below:

[0105] Column names Field type and length Remark data_source_config_id bigint(20) Auto-incrementing primary key, data source configured with ID data_source_config_name varchar(128) Data source configuration name

[0106] Table 1

[0107] As shown in Table 1, the data source configuration information table includes the fields for data source configuration ID and data source configuration name.

[0108] The specific table design of the data source information table t_data_source is shown in Table 2 below:

[0109]

[0110]

[0111] Table 2

[0112] As shown in Table 2, the data source information table includes fields such as: data source name, full name of driver class, URL address of database connection, username and password of database connection.

[0113] The specific table design of the sharding and partitioning information table t_table_config is shown in Table 3 below:

[0114]

[0115] Table 3

[0116] As shown in Table 3, the data source information table includes fields such as: data source name set, logical table name, sharding column, database sharding and table partitioning strategy, table size and table suffix.

[0117] 2. Obtain configuration information.

[0118] In practice, configuration information is used to configure the data source information and the database sharding and table partitioning information. Specifically, the configuration information includes: data source configuration information, data source information, and database sharding and table partitioning information; the data source configuration information includes: data source configuration name; the data source information includes: data source name, fully qualified driver name, database connection URL address, database connection username and password; the database sharding and table partitioning information includes: data source name set, logical table name, sharding column, database sharding and table partitioning strategy, table size, and table sharding suffix.

[0119] It should be noted that the configuration information is the information entered by the user through the configuration page.

[0120] 3. Verify the validity of the configuration information.

[0121] It should be noted that the specific steps for verifying the validity of configuration information are as follows:

[0122] Verify whether the configuration name, data source name, full driver name, URL, username, and password are required.

[0123] Verify that the Class corresponding to the full name of the driver exists;

[0124] Verify that the URL conforms to the JDBC protocol;

[0125] Verify whether the data source list, table name, sharding columns, sharding strategy, sharding size, and suffix format are required for adding logical tables.

[0126] Determine if the data source for adding the logical table matches the data source name;

[0127] Determine if the logical table configuration and the generated physical table exist in the data source;

[0128] 4. Store the valid configuration information in the corresponding configuration information table.

[0129] 5. Return the save result, wherein the save result is used to indicate whether the save was successful. The save result includes: save successful and save failed. The save success result indicates that the data source information and the database sharding information were configured successfully.

[0130] It is understandable that valid configuration information is stored in the configuration information database, and successful storage means successful configuration.

[0131] It is understood that this application provides a database sharding and partitioning query method. By obtaining the SQL statement and data source configuration ID input from the terminal, when the SQL statement and data source configuration ID are valid, the sharding and partitioning data source object is retrieved from the cache according to the data source configuration ID. The result set of the sharding and partitioning database responding to the SQL statement is obtained through the sharding and partitioning data source object, making data query operations convenient. The retrieved results are encapsulated into response data and displayed. The response data is aggregated data, which helps to solve the problem that when ShardingJDBC is used as a database sharding and partitioning solution and the sharding and partitioning algorithm is used to calculate accurate query data, the data query operation is difficult and the results cannot be aggregated and displayed.

[0132] It is understood that this application provides a method for querying sharded databases. Users configure the data source connection (driver full name, URL, username, password) and sharding strategy (logical table name, sharding columns, sharding strategy [mode, range, enum], number of shards, etc.) via a webpage. After configuration, the execution window of this data source is opened, SQL statements are entered and executed, and the execution results are displayed. When using the technical solution provided in this application for data querying, users do not need to concern themselves with the specific sharding algorithm or calculate which sharding nodes the data is distributed across to retrieve data matching the target value. The operation is simple and convenient.

[0133] Please see Figure 2 , Figure 2 This is a schematic block diagram illustrating a database query apparatus with sharded tables according to an exemplary embodiment, such as... Figure 2 As shown, the database query device 100 for sharding and partitioning includes:

[0134] The acquisition module 101 is used to acquire the SQL statement and data source configuration ID input by the terminal, wherein the terminal and the data source configuration ID correspond one-to-one, the data source configuration ID is used to identify the data source configuration information stored in the configuration information database, and the SQL statement is used to operate the sharded database.

[0135] The execution module 102 is used to retrieve a sharded database data source object from the cache based on the data source configuration ID when the SQL statement and the data source configuration ID are verified to be valid. The sharded database data source object is created based on the data source information and sharded database information corresponding to the data source configuration ID. The module also retrieves the result set of the sharded database response to the SQL statement execution through the sharded database data source object.

[0136] Display module 103 is used to encapsulate the read results into response data and display the response data, wherein the response data is aggregated data.

[0137] In practice, preferably, the sharded database query device 100 further includes a verification module 104, used to verify whether the SQL statement and the data source configuration ID are valid. This verification module 104 does not... Figure 2 As shown in the image.

[0138] Regarding the database query device 100 with sharded tables in this embodiment, the specific ways in which its acquisition module 101, execution module 102, display module 103, and verification module 104 execute the above method have been described in detail in the embodiments related to the method, and will not be elaborated here.

[0139] It is understood that the technical solution provided in this embodiment involves a sharded database query device that obtains the SQL statement and data source configuration ID input from the terminal. When the SQL statement and data source configuration ID are valid, the device retrieves the sharded database data source object from the cache based on the data source configuration ID. The device then obtains the result set of the sharded database response to the SQL statement through the sharded database data source object, making data query operations convenient. The retrieved results are encapsulated into response data and displayed. This response data is aggregated data, which helps solve the problem of high difficulty in data query operations and the inability to aggregate and display results when using ShardingJDBC as a sharded database solution and employing sharding algorithms to calculate accurate query data.

[0140] An exemplary embodiment of a database sharding and table-sharding query device is shown, the database sharding and table-sharding query device comprising: the aforementioned database sharding and table-sharding query apparatus 100.

[0141] Regarding the device including the database sharding and table-sharding query device in the above embodiments, the specific manner in which the database sharding and table-sharding query device executes the above method has been described in detail in the embodiments related to the method, and will not be elaborated here.

[0142] In practice, the electronic device can be a desktop computer, laptop computer, or other device equipped with the database sharding and table querying device.

[0143] It is understood that the technical solution provided in this embodiment includes the aforementioned sharded database query device. This device obtains the SQL statement and data source configuration ID input from the terminal. When the SQL statement and data source configuration ID are valid, it retrieves the sharded database data source object from the cache based on the data source configuration ID. It then obtains the result set of the sharded database response to the SQL statement through the sharded database data source object, making data query operations convenient. The retrieved results are encapsulated into response data and displayed. The response data is aggregated data, which helps to solve the problem that when ShardingJDBC is used as a sharded database solution and the sharded database algorithm is used to calculate accurate query data, the data query operation is difficult and the results cannot be aggregated and displayed.

[0144] Furthermore, this application provides a computer-readable storage medium storing computer instructions for causing a computer to perform the steps of any of the methods described above. The storage medium may be a magnetic disk, optical disk, read-only memory (ROM), random access memory (RAM), flash memory, hard disk drive (HDD), or solid-state drive (SSD), etc.; the storage medium may also include combinations of the above types of memory.

[0145] It is understood that the same or similar parts in the above embodiments can be referred to each other, and the contents not described in detail in some embodiments can be referred to the same or similar contents in other embodiments.

[0146] It should be noted that in the description of this application, the terms "first," "second," etc., are used for descriptive purposes only and should not be construed as indicating or implying relative importance. Furthermore, in the description of this application, unless otherwise stated, "multiple" or "more" means at least two.

[0147] It should be understood that when an element is referred to as "fixed to" or "set on" another element, it may be directly on the other element or may have an intervening element present at the same time; when an element is referred to as "connected to" another element, it may be directly connected to the other element or may have an intervening element present at the same time. In addition, the term "connected" as used herein may include wireless connections; the word "and / or" as used includes any unit and all combinations of one or more of the associated listed items.

[0148] Any process or method description in the flowchart or otherwise herein can be understood as: representing a module, segment, or portion of code comprising one or more executable instructions for implementing a particular logical function or process, and the scope of the preferred embodiments of this application includes additional implementations in which functions may be performed not in the order shown or discussed, including substantially simultaneously or in reverse order depending on the function involved, as should be understood by those skilled in the art to which embodiments of this application pertain.

[0149] It should be understood that various parts of this application can be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented using software or firmware stored in memory and executed by a suitable instruction execution system. For example, if implemented in hardware, as in another embodiment, it can be implemented using any one or a combination of the following techniques known in the art: discrete logic circuits having logic gates for implementing logical functions on data signals, application-specific integrated circuits (ASICs) having suitable combinational logic gates, programmable gate arrays (PGAs), field-programmable gate arrays (FPGAs), etc.

[0150] Those skilled in the art will understand that all or part of the steps of the methods described in the above embodiments can be implemented by a program instructing related hardware. The program can be stored in a computer-readable storage medium, and when executed, it includes one or a combination of the steps of the method embodiments.

[0151] Furthermore, the functional units in the various embodiments of this application can be integrated into a processing module, or each unit can exist physically separately, or two or more units can be integrated into a module. The integrated module can be implemented in hardware or as a software functional module. If the integrated module is implemented as a software functional module and sold or used as an independent product, it can also be stored in a computer-readable storage medium.

[0152] The storage media mentioned above can be read-only memory, disk, or optical disk, etc.

[0153] In the description of this specification, the references to terms such as "one embodiment," "some embodiments," "example," "specific example," or "some examples," etc., indicate that a specific feature, structure, material, or characteristic described in connection with that embodiment or example is included in at least one embodiment or example of this application. In this specification, the illustrative expressions of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics described may be combined in any suitable manner in one or more embodiments or examples.

[0154] Although embodiments of this application have been shown and described above, it is understood that the above embodiments are exemplary and should not be construed as limiting this application. Those skilled in the art can make changes, modifications, substitutions and variations to the above embodiments within the scope of this application.

Claims

1. A method for querying a sharded database, characterized in that, The method comprises the following steps: obtaining a terminal input SQL statement and a data source configuration ID, wherein the terminal and the data source configuration ID are in one-to-one correspondence, the data source configuration ID is used to identify data source configuration information stored in a configuration information database, and the SQL statement is used to operate a database table splitting database; the terminal is an execution terminal opened after clicking on a configured data source configuration, and the terminal is used for user input of the SQL statement; the terminal is in one-to-one correspondence with the data source configuration ID; when the SQL statement and the data source configuration ID are verified to be legal, obtaining a database table splitting data source object from the memory according to the data source configuration ID, wherein the database table splitting data source object is created according to data source information and database table splitting information corresponding to the data source configuration ID; obtaining a result set of the database table splitting database responding to execution of the SQL statement through the database table splitting data source object; encapsulating data in the read result set into response data, and displaying the response data, wherein the response data is aggregated data.

2. The method of claim 1, wherein, The method further comprises: verifying whether the SQL statement and the data source configuration ID are legal; and verifying whether the SQL statement and the data source configuration ID are legal, comprising: verifying whether the data source configuration ID exists data source information; verifying whether the SQL statement is a legal SQL statement; when the data source configuration ID exists data source information and the SQL statement is a legal SQL statement, the SQL statement and the data source configuration ID are verified to be legal.

3. The method of claim 2, wherein, Before obtaining the terminal input SQL statement and the data source configuration ID, the method further comprises: configuring the data source information and the database table splitting information; and configuring the data source information and the database table splitting information, comprising: creating a configuration information table in the configuration information database, wherein the configuration information table is used to store the data source information and the database table splitting information; obtaining configuration information, wherein the configuration information is used to configure the data source information and the database table splitting information; verifying the legality of the configuration information; 4. The method of claim 3, wherein, saving the configuration information verified to be legal in the corresponding configuration information table; returning a saving result, wherein the saving result is used to indicate whether saving is successful, the saving result comprises: saving success and saving failure, and saving success of the saving result indicates that the data source information and the database table splitting information are configured successfully. The configuration information table comprises: a data source configuration information table, a data source information table and a database table splitting information table. ​ ​ ​ ​ 5. The method of claim 4, wherein, ​ ​ The data source configuration information table is configured to store the configured data source configuration name and data source configuration ID; The data source information table is configured to store data source information; The database and table splitting information table is configured to store table splitting strategies.

6. The method of claim 5, wherein, The configuration information includes: Data source configuration information, data source information, and database and table splitting information. The data source configuration information includes: Data source configuration name; The data source information includes:

7. The method of claim 6, wherein, Data source name, full name of a driver class, URL address of a database connection, username and password of a database connection; The database and table splitting information includes: Data source name set, logical table name, sharding column, database and table splitting strategy, table size, and table suffix. The method further includes: When the database and table splitting data source object cannot be obtained from the memory, creating the database and table splitting data source object according to the data source information and the database and table splitting information corresponding to the data source configuration ID and saving the database and table splitting data source object in the memory; According to the data source information and the database and table splitting information corresponding to the data source configuration ID, creating the database and table splitting data source object and saving the database and table splitting data source object in the memory includes: Obtaining the corresponding data source information and database and table splitting information from the configuration information database according to the data source configuration ID; 8. A database query apparatus for a split database and split table, characterized by, Creating the database and table splitting data source object; Instantiating the database and table splitting data source object according to the data source information and the database and table splitting information; Saving the instantiated database and table splitting data source object in the memory. It includes:

9. The apparatus of claim 8, wherein, An obtaining module configured to obtain a SQL statement and a data source configuration ID input by a terminal, wherein the terminal and the data source configuration ID are in one-to-one correspondence, the data source configuration ID is configured to identify data source configuration information stored in a configuration information database, and the SQL statement is configured to operate a database and table splitting database; the terminal is an execution terminal opened after a configured data source configuration is clicked, and the terminal is configured to input a SQL statement by a user; the terminal and the data source configuration ID are in one-to-one correspondence; An execution module configured to, when the SQL statement and the data source configuration ID are verified to be legal, obtain a database and table splitting data source object from a cache according to the data source configuration ID, wherein the database and table splitting data source object is created according to data source information and database and table splitting information corresponding to the data source configuration ID; and obtain a result set of the database and table splitting database in response to execution of the SQL statement through the database and table splitting data source object; 10. A database query apparatus for a split database and split table, characterized by, A display module configured to encapsulate data in the result set into response data and display the response data, wherein the response data is aggregated data. It further includes: A verification module configured to verify whether the SQL statement and the data source configuration ID are legal. It includes: The apparatus of claim 9.

Images