WO2024113740A1 - Data query method, and system, device and computer-readable storage medium - Google Patents

Abstract

The present application relates to the technical field of databases, and particularly to a data query method, and a system, a device and a computer-readable storage medium. The method comprises: in response to a data query request, which is sent from a client, a server-side application determining a first entity, which is correspondingly requested by means of the data query request, wherein the first entity is used for storing first relevant information of a first database table, and at least comprises first primary key information and partition key information of the first database table; the server-side application generating a first query statement, which comprises the first primary key information and the partition key information; and in a target partition of the first database table, which target partition corresponds to the partition key information in the first query statement, a database querying first target data that includes the primary key information. The present application can effectively utilize the partition clipping capability of a database, so as to automatically generate a data query statement having partition key information for partition querying, thereby facilitating an improvement in the data query efficiency and the response rate of a server side to a request of a client, and facilitating an improvement in the user experience.

Description

Data query method, system, device and computer readable storage medium

This application claims priority to the Chinese patent application filed with the China Patent Office on November 28, 2022, with application number 202211503327.1 and application name “Data Query Method, System, Device and Computer-readable Storage Medium”, all contents of which are incorporated by reference in this application.

Technical Field

The present invention relates to the field of database technology, and in particular to a data query method, system, device and computer-readable storage medium.

Background technique

For database tables with massive data records, Oracle provides table partitioning technology. This technology can enable the database to support larger data volumes and improve system performance. In table partitioning technology, there is a very important functional concept called partition pruning, that is, when the partition key field is included in the Where condition of the Structured Query Language (SQL), the Oracle-managed database (Oracle database for short) can only perform data queries under the partition corresponding to the partition key field in the corresponding database table. In this way, the query/retrieval performance of the database can be greatly improved. Therefore, when developing a business service system, the data query statements written by developers for the system's data query service, such as SQL statements written using the above SQL, usually follow the programming specifications corresponding to the above partition pruning function and add the partition key field to the corresponding statement.

However, when the database uses the Java Persistence API (JPA) framework or the object-relational mapping framework (Hibernate), all statements are automatically generated by the tool program. The SQL statements corresponding to the CRUD operations all use the primary key field or foreign key field as the Where condition, and do not include the partition key of the database table. This makes it impossible to fully utilize the partition trimming capabilities of the database. The database will traverse all partitions for query operations, resulting in a large performance loss.

Summary of the invention

The present application provides a data query method, system, device and computer-readable storage medium, wherein the method can utilize the field association characteristics of database tables of an Oracle database or the like with partition trimming capability to determine the partition key fields in each database table, and automatically add the partition key fields to a data query SQL statement as a Where condition for data query, thereby improving data query efficiency, and can give full play to the partitioning function of the Oracle database or the like, which is conducive to ensuring the stability of data query efficiency, and the server can therefore improve the response rate to the client, thereby helping to improve user experience.

In a first aspect, the present application provides a data query method, which is applied to a server of a business service system, wherein the server includes at least one server application and at least one database, and the method includes:

The server application responds to the data query request sent by the client and determines a first entity corresponding to the data query request, wherein the first entity is used to store first relevant information of a first database table in the database, and the first relevant information is The method comprises at least first primary key information and partition key information of a first database table, wherein the partition key information is information related to an identification field determined by the database in the first database table, and the identification field is a common field possessed by the first database table and a slave table of the first database table; the server application generates a first query statement sent to the database, wherein the query condition in the first query statement includes the first primary key information and the partition key information; the database queries the first target data containing the primary key information under the target partition corresponding to the partition key information in the first database table based on the received first query statement; the server application receives the first target data fed back by the database, and returns the first target data to the client as a data query result.

For example, the first database table may be a main table in an Oracle database, such as an employee table. Accordingly, the first entity may be a JPA entity corresponding to the employee table definition, i.e., an "employee class" entity, and the primary key information of the "employee class" entity may be, for example, a composite of the primary key information of the employee table (i.e., the first primary key information) and the partition key information (e.g., an organization ID). In this way, when the server responds to a data query request sent by the client, the server application may call the defined first entity, and use the primary key information of the first entity to automatically generate a primary key query statement, such as an SQL statement, with the database table primary key information and the partition key information, to query the relevant target data in the partition of the corresponding database. In this way, the server can autonomously utilize the partition tailoring capability of the Oracle database, etc., without the need to perform partition queries with the aid of SQL statements written by developers with the partition key as the Where condition, which is conducive to improving the response rate of the server to the client and improving the user experience.

In a possible implementation of the first aspect above, the method further includes: the server application determines, in response to a data query request sent by a client, a second entity corresponding to the data query request, wherein the second entity is used to store second related information of a second database table in the database, wherein the foreign key information in the second database table is the same as the first primary key information of the first database table, and the primary key information of the second entity is different from the primary key information of the first entity but both include at least partially the same partition key information, and the second related information includes at least foreign key information and partition key information; and the server application generates a second query statement sent to the database, wherein the query condition in the second query statement includes foreign key information and partition key information; the database queries the second target data containing the foreign key information under the target partition corresponding to the partition key information in the second database table based on the received second query statement; the server application receives the second target data fed back by the database, and determines the data query result returned to the client based on the first target data and the second target data.

For example, the second database table may be a slave table in an Oracle database, such as a slave table of an employee table: an attendance information table, etc. Accordingly, the second entity may be, for example, a JPA entity corresponding to the definition of the attendance information table, i.e., an "attendance information class" entity, and the primary key information of the "attendance information class" entity may be, for example, a composite of the foreign key information of the attendance information table and the partition key information (e.g., an organization ID). Thus, when the server responds to a data query request sent by the client, the server application may automatically generate a foreign key query statement, such as an SQL statement, that can be associated with the attendance information table based on the primary key information of the defined second entity when calling the first entity and then querying the relevant data records under the corresponding partition in the employee table, so that the relevant target data in the database table associated with the foreign key can be further queried in the partition of the corresponding database. Accordingly, the data query result fed back by the server to the client may be, for example, a combination of the first target data and the second target data queried above.

In this way, the server can automatically generate a Where condition with the partition key when querying the data requested by the client, without the need for developers to write a Where condition with the partition key in the SQL statement to perform partition query, which helps reduce the workload of developers. In addition, when querying foreign key-related data, the query condition generated by the server can also automatically include the partition key as the Where condition, which helps improve data query efficiency and thus improve server performance. Improve the response rate of the server to the client and improve the user experience.

In a possible implementation of the first aspect above, the second related information also includes second primary key information of the second database table, and the method includes: the server application generates a third query statement sent to the database, wherein the query conditions in the third query statement include the second primary key information and the partition key information; based on the received third query statement, the database queries the third target data including the second primary key information under the target partition corresponding to the partition key information in the second database table, wherein the third target data is the same as or different from the second target data; the server application receives the third target data fed back by the database, and determines the data query result returned to the client based on the first target data, the second target data and the third target data.

That is, the server can also respond to the data query request for the attendance information table and other sub-tables sent by the client. At this time, the server can also respond to the data query request sent by the client and generate a primary key query statement including the primary key of the attendance information table and the partition key field information determined by the database. This primary key query statement can also achieve the purpose of querying the relevant data in the attendance information table in the partition to which the attendance information table belongs. Correspondingly, the data query result fed back by the server to the client can be, for example, a combination of the first target data, the second target data and the third target data queried above.

It can be understood that the server can automatically generate a primary key query statement or a foreign key association query statement with partition key information in response to the data query request sent by the client, and the corresponding target data queried should correspond to the data requested by the data query request. If the query conditions are the same, the target data queried can be the same; if the query conditions are different, the target data queried can be different.

In a possible implementation of the first aspect above, the query condition is a Where condition in an SQL statement, and the first query statement, the second query statement, and the third query statement are SQL statements.

In a possible implementation of the first aspect above, the server application generates a first query statement to be sent to a database, including: obtaining third primary key information of the first entity, wherein the third primary key information is composite information of the first primary key information and the partition key information; and using the third primary key information as a query condition to generate a first query statement.

In a possible implementation of the first aspect above, the server application generates a second query statement to be sent to the database, including: obtaining fourth primary key information of the second entity, wherein the fourth primary key information is composite information of foreign key information and partition key information; and using the fourth primary key information as a query condition to generate a second query statement.

In a possible implementation of the first aspect above, the database queries the first target data including the primary key information under the target partition corresponding to the partition key information in the first database table based on the received first query statement, including: determining the access path corresponding to the target partition in the first database table to be queried based on the partition key information in the query condition of the first query statement; accessing the target partition in the first database table based on the access path; and searching for the first target data of the record corresponding to the primary key value in the primary key information under the target partition in the first database table based on the primary key information in the query condition of the first query statement.

In a possible implementation of the first aspect above, the database is an Oracle database, and the server-side application is an application developed based on a JPA framework or a Hibernate framework.

In a possible implementation of the first aspect above, the first entity is defined by a server-side application using an object-relational mapping (O/R Mapping) tool, the O/R Mapping tool is provided by a JPA framework or a Hibernate framework, and the process of defining the first entity by the server-side application includes: obtaining an identification field determined by the database as a partition key; combining a first primary key of a first database table with the partition key into a composite primary key, and defining the composite primary key as the primary key of the first entity mapped to the first database table.

In a second aspect, the present application embodiment provides a business service system, including a client and a server, the server It includes at least one server application and at least one database, wherein the server application is used to receive and respond to a data query request sent by a client, and is used to determine a first entity corresponding to the data query request according to the data query request, wherein the first entity is used to store first relevant information of a first database table in the database, the first relevant information at least includes first primary key information and partition key information of the first database table, the partition key information is information related to an identification field of the database in the first database table, and the identification field is a common field of the first database table and a subordinate table of the first database table; and the server application is also used to generate a first query statement sent to the database, wherein the query condition in the first query statement includes the first primary key information and the partition key information; the database is used to query first target data containing primary key information under a target partition corresponding to the partition key information in the first database table according to the received first query statement, and return the first target data to the client through the server application.

In a possible implementation of the second aspect above, the server application is further used to determine a second entity of the request corresponding to the data query request, and to generate a second query statement to be sent to the database, wherein the second entity is used to store second related information of a second database table in the database, the second database table is a foreign key association table of the first database table, the second related information includes at least foreign key information and partition key information of the second database table, the first database table includes a field corresponding to the foreign key information of the second database table, and the query condition in the second query statement includes foreign key information and partition key information; and the database is also used to query the second target data containing the foreign key information under the target partition corresponding to the partition key information in the second database table according to the received second query statement, and return the first target data and the second target data to the client through the server application.

In a possible implementation of the second aspect above, the client is installed on a terminal device used by a user, the server application is installed on an application server, and the database is deployed on a database server.

In a third aspect, an embodiment of the present application provides a device, comprising: one or more processors; one or more memories; one or more memories storing one or more programs, and when one or more programs are executed by one or more processors, the device executes the data query method provided in the above-mentioned first aspect and various possible implementations.

In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium having instructions stored thereon. When the instructions are executed on a computer, the computer executes the data query method provided in the first aspect and various possible implementations.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG1 is a schematic diagram of a scenario in which a data query method provided in an embodiment of the present application is applied.

FIG. 2 is a schematic block diagram of a software structure of a server provided in an embodiment of the present application.

FIG3 is a schematic diagram showing an implementation flow of a data query method provided in an embodiment of the present application.

FIG. 4 shows a database table for partition management in a database provided in an embodiment of the present application.

FIG5 is a schematic diagram showing the data structure changes of entities defined before and after using a composite primary key according to an embodiment of the present application.

FIG. 6 shows some examples of SQL statements with different Where conditions provided in an embodiment of the present application.

FIG. 7 is a schematic diagram showing the hardware structure of a server 200 provided in an embodiment of the present application.

Detailed ways

In order to facilitate the understanding of the present application, the concepts of some technical fields involved in the embodiments of the present application are first explained below.

(1) Databases are used to store a large number of data entities. Database design is the process of planning and structuring the data entities in the database and the relationships between these data entities.

(2) A database table consists of three parts: the table name, the fields in the table, and the records in the table. Designing a database table structure (hereinafter referred to as table structure) is to define the database table file name, determine which fields the database table contains, the field name, field type, and width of each field, and input this data into the computer. It can be understood that a database table is the data carrier on the database side.

(3) Primary key refers to a column or combination of columns in a database table. The value of the primary key can uniquely identify each row in the table. The primary key of a database table can be associated with the foreign keys of other tables to associate the addition, deletion, change/modification of field text in other database tables.

(4) Partition Key is a table field that partitions a database table in a database. After each database table in a database is partitioned, each partition can have a uniquely identified partition key.

(5) Partition Table divides the data of a large table into many small subsets called partitions. The types of partitions mainly include range, list, hash, and composite (Range+List or Range+Hash) partitions. It can be understood that the basis for partitioning is mainly based on the internal attributes of the above-mentioned large table and partition table. At the same time, the partition table can create its own unique partition index.

(6) Oracle is a relational database management system. A database managed by Oracle is usually called an Oracle database. The server-side application installed on the application server can interact with the Oracle database based on the tool programs provided by frameworks such as JPA or Hibernate, with the help of the JDBC (Java Database Connectivity) driver at the bottom of the server, to achieve data query functions for the database and different partition records in each database table.

(7) CRUD is the abbreviation of the words Create, Retrieve, Update, and Delete when performing calculations. It is mainly used to describe the basic operation functions of the database or persistence layer (JPA) in the software system.

(8) Entity is the data carrier on the application side, such as the data carrier used by the server-side application installed on the application server. Entity usually includes some properties and methods. The frameworks such as JPA or Hibernate applied on the application server implement the mapping relationship between the application-side entity and the database table, so as to convert the application-side operation on the data entity into SQL statements for operating the database table, and send them to the database server through the JDBC driver. The database server responds to these SQL statements to implement the operation on the database table. Generally, the entity mapped to the corresponding database table can have the same or similar table structure as the database table. For example, the entity can have the same primary key or foreign key as the corresponding database table.

FIG1 is a schematic diagram showing a scenario in which a data query method is applied according to an embodiment of the present application.

As shown in Figure 1, the scenario includes a terminal device 100 and a server (or server cluster) 200. The user can perform some data query operations through the terminal device 100, such as searching for insurance policies, viewing insurance policies, exporting insurance policy data, etc., and initiate data query and data acquisition requests to the server 200.

After receiving the data query request sent by the terminal device 100 in response to the user operation, the server 200 can match or generate a corresponding data query statement, search the database for the data requested by the user, and feed back the queried data (i.e., the data query result) to the terminal device 100. The server 200 may include an application server 200a and a database server 200b. In other embodiments, the server 200 may also include other types of servers.

It is understood that an application end, such as one or more server-side applications, may be installed on the application server 200a to receive the above-mentioned data query request sent by the client installed on the terminal device 100, and call the tool program provided by the preset framework, such as the object relationship mapping (Object Relational Mapping, O/R Mapping) tool program provided by the above-mentioned JPA framework or Hibernate framework, etc., to automatically generate a data query statement that can be mapped to the corresponding database or partition for data query, and send it to the database end deployed on the database server 200b to query the corresponding data. The data query statement may be, for example, an SQL statement. In other embodiments, the operation corresponding to the response of the SQL statement may include any of the above-mentioned CRUD operations.

Furthermore, the database server 200b may feed back the data queried based on the received data query statement, ie, the data query result, to the client through the corresponding server application.

However, as mentioned above, this type of automatically generated SQL statement usually uses the primary key field or foreign key field of the database table as the Where condition, but does not include the partition key field of the partition to which the corresponding data belongs. Therefore, the SQL statement cannot use the partition trimming capability of the Oracle database to perform partition query, resulting in reduced data query efficiency, affecting the response speed of the server application and database end of the server 200 to the corresponding data query request, and further resulting in a poor user experience.

In order to solve the above problems, an embodiment of the present application provides a data query method, which is applied to electronic devices such as servers. The method first determines the partition key field from the database, for example, first selects a suitable field as the partition key field on the main table of the database, and then uses the same field as the partition key field on the slave table. If the corresponding slave table does not have the partition key field selected on the main table, the field can be redundantly added to the slave table when the database table is designed. Furthermore, when defining an entity, the partition key field can be added to the primary key and/or foreign key of the corresponding entity. The adding process can, for example, include compounding the partition key field with the primary key of the corresponding database table to obtain a composite primary key as the primary key of the defined entity. The adding process can, for example, also include compounding the partition primary key field with the foreign key of the corresponding database table to obtain a composite foreign key as the foreign key of the defined entity. Furthermore, when the user needs to obtain data through the client, the server can search for data records with the corresponding primary key under the partition corresponding to the partition key based on the composite primary key and/or composite foreign key in the entity, and the data records under the corresponding partition of the associated foreign key database table.

In this way, the server side that uses framework tools such as JPA or Hibernate that automatically generate SQL statements can also effectively use the partition tailoring capabilities of Oracle databases to automatically perform partition queries, thereby improving data query efficiency, or in other words, helping to ensure the stability of data query efficiency. The server side can therefore increase the response rate to the client, and the user experience can also be improved.

It can be understood that in some scenarios, if the data corresponding to the request query in the user's client operation has no other associated database tables, the data query method provided based on the embodiment of the present application can accurately locate the corresponding partition to query the data records with the corresponding primary key according to the composite primary key in the automatically generated SQL statement. In other scenarios, if the data corresponding to the request query in the user's client operation has other database tables associated by foreign keys, the data query method provided based on the embodiment of the present application can not only accurately locate the corresponding partition to query the data records with the corresponding primary key according to the partition key in the composite primary key in the SQL statement, but also locate the corresponding partition in the associated data table according to the composite foreign key to quickly query the corresponding data records.

It can be understood that the data query method provided in the embodiment of the present application is applicable to electronic devices including but not limited to the above-mentioned server 200, and terminal devices 100 such as mobile phones, tablet computers, desktops, laptops, handheld computers, netbooks, and augmented reality (AR) and virtual reality (VR) devices, smart TVs, smart watches and other wearable devices, mobile email devices, car equipment, portable game consoles, portable music players, reader devices, televisions embedded or coupled with one or more processors, or capable of Access other devices on the network.

It can be understood that the above-mentioned server refers to the business service system server that interacts with the client installed on the terminal device 100. The application service provided by the server can be implemented by the server application installed on the application server 200a, and the database-related services provided by the server can be implemented by the database deployed on the database server 200b, which can be, for example, an Oracle database. Therefore, the server/server cluster to which the configuration data management method provided in the embodiment of the present application is applicable may include an application server (The Application Server) and other servers or other electronic devices with strong computing power or application running capabilities, as well as database servers and other electronic devices with strong storage capabilities and capable of running database management systems such as Oracle to implement partition tailoring. No limitation is made here.

It can be understood that the above-mentioned terminal device 100 installed with a client may include but is not limited to mobile phones, tablet computers, desktops, laptops, handheld computers, netbooks, as well as augmented reality (AR) and virtual reality (VR) devices, smart TVs, smart watches and other wearable devices, mobile email devices, car equipment, portable game consoles, portable music players, reader devices, televisions with one or more processors embedded or coupled thereto, or other electronic devices capable of accessing the Internet.

FIG2 is a schematic block diagram showing a software structure of a server according to an embodiment of the present application.

As shown in Figure 2, the server may include a server application 20a and a database 20b. The server application 20a may be installed on an application server 200a, and the database 20b may be deployed on a database server 200b. Accordingly, the server shown in Figure 2 may be deployed on a server cluster or an integrated server 200 including an application server 200a and a database server 200b. It is understood that the server may be, for example, a component of some large business service systems, such as a server in an insurance business service system. The server cluster that deploys the above-mentioned server may also include other types of servers, such as file servers, content management servers, proxy servers, email servers, etc., which are not limited here.

It can be understood that the server shown in Figure 2 can obtain the data query request sent by the client based on the server application 20a. The data query request can, for example, be a request for querying corresponding data corresponding to the viewing, downloading, modification, deletion or CRUD operations performed by the user on the client interface displayed by the corresponding terminal device 100, and there is no limitation here.

Based on the server structure shown in FIG2 , the specific implementation process of the data query method provided in the embodiment of the present application is described in detail below in conjunction with a specific implementation flowchart.

FIG3 is a schematic diagram showing an implementation flow of a data query method according to an embodiment of the present application.

It can be understood that the process shown in FIG3 may involve the interaction process between the server application 20a and the database 20b in the server, wherein the server application 20a and the database 20b may be installed/deployed on different servers, for example, the server application 20a may be installed on the application server, and the database 20b may be deployed on the database server, without limitation. In other embodiments, the execution subject of each step in the process shown in FIG3 may also be the server.

As shown in FIG3 , the interaction process may include the following steps:

301: The database 20b selects a suitable field on the master table of the database as the partition key field, and uses the same field on the slave table as the partition key field.

For example, the database 20b may be an Oracle database. The data records in each database table in the Oracle database are usually divided into corresponding partitions according to a preset partitioning rule, for example, according to each data record The information of the organization or unit to which it belongs, the information of the software project to which it is applied, the information of the port to which it is called or the information of the host to which it is called, the service information, etc. are divided into corresponding partitions. Accordingly, the data records of different partitions in each database table can have some common field information, and these common fields can be used as the partition key field to identify the partition. The partition key field can also be understood as a field that can establish an association between the database tables in the partition. Therefore, the database 20b can manage the primary key information, foreign key information, etc. of each database table, and can also select a partition key field in the main table of the database as the partition key information in each database table such as the main table and the slave table associated with the foreign key.

As an example, FIG4 shows a database table for partition management in a database according to an embodiment of the present application.

As shown in FIG4 , for example, the partition includes database tables such as an employee table and an attendance information table. Among them, the fields in the employee table include employee ID, organization ID, name, etc., wherein the employee ID is the primary key of the employee table. The fields in the attendance information table include attendance information ID, employee ID, organization ID, attendance date, attendance status, etc., wherein the attendance information ID can be, for example, information such as the card number of the attendance card, which can be designed as the primary key of the attendance information table. Moreover, a master-slave table can be formed between the employee table and the attendance information table shown in FIG3 , for example, the master-slave relationship between the two can be established through the employee ID. Thus, referring to FIG3 , the employee table is used as the master table, and the attendance information table is used as the slave table of the employee table, wherein the employee ID in the attendance information table is the foreign key associated with the employee table.

As shown in FIG. 4 , in addition to being associated through the employee ID, the employee table and the attendance information table also have the same field, namely, the organization ID. Furthermore, other database tables in the partition may also contain the organization ID field. Therefore, the database 20b may select the organization ID as the partition key field in the employee table, and use the organization ID in the attendance information table shown in FIG. 4 as the partition key field.

It is understandable that when the database 20b selects the field as the partition key, the primary key field of each database table may not be adjusted. For example, the primary key of the employee table shown in FIG. 4 is still "employee ID", the primary key of the attendance information table is still "attendance information ID", etc., that is, the primary key of each database table is still the single primary key field initially designed.

302: The database 20b feeds back the selected partition key field to the server application 20a.

Exemplarily, after selecting the partition key field in each database table, the database 20b can feed back the partition key field to the server application 20a, so that the server application 20a can use it when creating entities associated with the corresponding database tables. The above feedback process can also be understood as a data synchronization process on the server side in other embodiments, which is not limited here.

303: The server application 20a composites the partition key field with the primary key of the corresponding database table to obtain a composite primary key, and defines an entity including the composite primary key.

Exemplarily, when defining a JPA entity, the partition key field selected in the above step 301 can be used as one of the primary keys of the defined entity. For example, the partition key field can be composited with the originally designed primary key in the corresponding database table, and the resulting composite primary key can be used as the primary key of the defined entity.

As an example, based on the database table structure shown in FIG. 4 , the data structure of the entity defined by the server application 20 a may refer to the example shown in FIG. 5 .

FIG. 5 is a schematic diagram showing the data structure changes of entities defined before and after using a composite primary key according to an embodiment of the present application.

As shown in FIG5, before the composite primary key is adopted, based on the "employee table" shown in FIG4 above, an "employee class" entity including the primary key of the "employee table" can be defined, that is, the primary key information of the entity is "employee ID". Similarly, based on the "attendance information table" shown in FIG4 above, an "attendance information class" entity including the primary key of the "attendance information table" can be defined, that is, the primary key information of the entity is "attendance information ID". It can be understood that before the composite primary key is adopted, the primary key of the entity associated with each database table will usually be consistent with the primary key of the corresponding database table in the database by default. For example, the primary key of the employee class is the employee ID, the primary key of the attendance information class is the attendance information ID, etc. In addition, if the "employee table" shown in FIG4 is associated with multiple "attendance information tables" through foreign keys, the server application 20a can define an "employee class" entity, and define multiple "attendance information class" entities based on multiple "attendance information tables" associated with foreign keys, that is, the quantitative correspondence between the two can be a 1-to-n correspondence.

Continuing to refer to FIG. 5, after adopting the composite primary key, based on the "employee table" shown in FIG. 4 above, a corresponding "employee class" entity containing a composite primary key can be defined, that is, the primary key information of the entity can include "employee ID" and "institution ID". Similarly, based on the "attendance information table" shown in FIG. 4 above, a corresponding "attendance information class" entity containing a composite primary key can be defined, that is, the primary key information of the entity is "attendance information ID" and "institution ID". It can be understood that in the entity defined by the composite primary key, the "institution ID" is both a partition key and a primary key. In addition, if the "employee table" shown in FIG. 4 above is associated with multiple "attendance information tables" through foreign keys, then the server application 20a can define an "employee class" entity with a composite primary key, and define multiple "attendance information class" entities with composite primary keys based on multiple "attendance information tables" associated with foreign keys, that is, the quantitative correspondence between the two can also be a 1-to-n correspondence.

304: The server application 20a composites the partition key field with the foreign key of the corresponding database table to obtain a composite foreign key, and adds the composite foreign key to the defined entity.

For example, in some data query scenarios, it is necessary not only to query the data records containing the corresponding primary key information under the corresponding partition in the database table, but also to query the related data records under the corresponding partition in some database tables associated with foreign keys, so as to achieve the purpose of data completeness. In this scenario, the server application 20a can compound a composite foreign key based on the primary key field and the partition key field of each database table in the database, and add the composite foreign key to the defined entity including the composite primary key to form an entity with a composite foreign key. Among them, the composite foreign key can, for example, include the foreign key information originally designed in each database and the partition key information fed back by the database 20b in the above step 302, that is, use the partition key information as one of the foreign key association conditions between the master and slave tables.

As an example, continue to refer to FIG. 5 above. Before the composite foreign key is adopted, based on the "Attendance Information Table" shown in FIG. 4 above, a corresponding "Attendance Information Class" entity containing the foreign key of the "Attendance Information Table" can be defined, that is, the foreign key information of the entity is "employee ID", and the foreign key information can be correspondingly associated with the "employee class" entity. It can be understood that before the composite foreign key is adopted, the foreign key of the entity defined in association with each database table will usually be consistent with the foreign key of the corresponding database table in the database by default, for example, the foreign key of the attendance information class is the employee ID, etc. In addition, if the "employee table" shown in FIG. 4 above is associated with multiple "Attendance Information Tables" through foreign keys, then the server application 20a can define an "employee class" entity, and define multiple "Attendance Information Class" entities based on multiple "Attendance Information Tables" associated with foreign keys, that is, the quantitative correspondence between the two can be a 1-to-n correspondence.

Continuing to refer to FIG5 , based on the “Attendance Information Table” shown in FIG4 above, a corresponding “Attendance Information Class” entity containing a composite foreign key can be defined, that is, the foreign key information of the entity is “Employee ID” and “Organization ID”. It can be understood that in the entity defined by the composite foreign key, the “Organization ID” is both a partition key and a foreign key. In addition, if the “Employee Table” shown in FIG4 above is associated with multiple “Attendance Information Tables” through foreign keys, the server application 20a can define an “Employee Class” entity with a composite foreign key, and define multiple “Attendance Information Class” entities with composite foreign keys based on multiple “Attendance Information Tables” associated with foreign keys, that is, the quantitative correspondence between the two can also be a 1-to-n correspondence.

It is understandable that in some data query scenarios, the server application 20a may only execute the above step 303 to define an entity with a composite primary key, and the entity is mapped to a corresponding database table. In other scenarios, the server application 20a may also execute the above steps 303 to 304 to define an entity with a composite primary key and a composite foreign key, and the entity may be mapped to a corresponding database table. This is not limited here.

305: The server application 20a receives the data query request sent by the client.

For example, referring to the scenario shown in FIG. 1 above, when a user operates the terminal device 100 and requests the server to query or obtain relevant data through the client installed on the terminal device 100, the corresponding client can send a data query request to the server application 20a. The data query request can include the primary key information of the requested data as the identification information of the relevant data. Accordingly, the server application 20a can receive the data query request sent by the client.

306: The server application 20a generates an SQL statement using the composite primary key field as a Where condition based on the composite primary key in the entity.

Exemplarily, the server application 20a can match the corresponding entity and call the corresponding entity to generate a data query statement based on the primary key information in the received data query request. For example, the server application 20a that applies the JPA framework or the Hibernate framework can match the entity including the primary key information based on the primary key information in the data query request. The entity can be, for example, an entity with a composite primary key defined when the server application 20a executes the above step 303, and the information of the composite primary key can include the primary key information in the data query request. The server application 20a can use the O/R Mapping tool provided by the JPA framework or the Hibernate framework to automatically generate SQL statements as data query statements, and the composite primary key in the above-mentioned matched entity can be used as the Where condition or one of the Where conditions in the generated SQL statement, without limitation here.

As an example, FIG. 6 shows some SQL statement examples with different Where conditions according to an embodiment of the present application.

As shown in FIG6 , Table 1 is an SQL statement automatically generated based on a general entity. The primary key of the general entity is, for example, only the primary key of the associated database table, such as "employee ID" or "attendance information ID". Then the SQL statement with the primary key as the Where condition generated by the server application 20a using the O/R Mapping tool can only include "employee ID" or "attendance information ID", such as the primary key query statement in Table 1 "Select*from employee table where employee ID=?" or "Select*from attendance information table where attendance information ID=?", etc., which is not limited here.

In other embodiments, data update statements, data deletion statements, or data insertion statements generated by the O/R Mapping tool may also use only the primary key as the Where condition. For example, the data update statements in Table 1 "Update employee table set name =? where employee ID =?" or "Update attendance information table set status =? where attendance information ID =?", and the data deletion statements in Table 1 "Delete from employee table where employee ID =?" or "Delete from attendance information table where attendance information ID =?", etc., are not enumerated here.

In contrast, as shown in Figure 6, Table 2 is a statement automatically generated based on an entity with a composite primary key. The composite primary key of the entity may include primary key information "employee ID" and "institution ID", or include "attendance information ID" and "institution ID". For example, the primary key query statement in Table 2 is "Select * from employee table where employee ID =? and institution ID =?" or "Select * from attendance information table where attendance information ID =? and institution ID =?", etc., which is not limited here.

In other embodiments, the data update statements, data deletion statements or data insertion statements generated by the O/R Mapping tool can also use the above-mentioned composite primary key as the Where condition. For example, the data update statements in Table 2 "Update employee table set name =? where employee ID =? and organization ID =?" or "Update attendance information table set status =? where attendance information ID =? and organization ID =?", and the data deletion statements in Table 2 "Delete from employee table where employee ID =? and organization ID =?" or "Delete from attendance information table where attendance information ID =? and organization ID =?", etc., are not enumerated here.

307: The server application 20a sends the generated SQL statement to the database 20b.

For example, the server application 20a may send a SQL statement generated based on an entity with a composite primary key to Database 20b provides the partition key information and primary key information required for partition query to database 20b through the SQL statement.

308: The database 20b feeds back the data query result to the server application 20a.

Exemplarily, the database 20b may query data records containing corresponding primary key information in the corresponding partition based on the composite primary key information in the received SQL statement, and feed back the queried data as a data query result to the server application 20a.

309: The server application 20a generates a SQL statement using the composite foreign key field as the associated foreign key field in the Where condition based on the composite foreign key in the entity.

Exemplarily, in some data query scenarios, the server application 20a can match the corresponding entity and call the corresponding entity to generate a data query statement based on the foreign key information in the received data query request. For example, a server application 20a that applies the JPA framework or the Hibernate framework can match the entity including the foreign key information based on the foreign key information in the data query request. The entity can be, for example, an entity with a composite foreign key defined when the server application 20a executes the above step 303, and the information of the composite foreign key can include the foreign key information in the data query request. The server application 20a can use the O/R Mapping tool provided by the JPA framework or the Hibernate framework to automatically generate SQL statements as data query statements, and the composite foreign key in the above-mentioned matched entity can be used as a Where condition or one of the Where conditions in the generated SQL statement, without limitation here.

As an example, FIG. 6 shows some SQL statement examples with different Where conditions according to an embodiment of the present application.

As shown in FIG6 , Table 1 is an SQL statement automatically generated based on a general entity. The foreign key of the general entity is, for example, only a foreign key of an associated database table, such as "employee ID" or "attendance information ID". The SQL statement generated by the server application 20a using the O/R Mapping tool with the foreign key as the Where condition can only include "employee ID" or "attendance information ID", such as the foreign key associated query statement "Select * from attendance information table where employee ID =?" in Table 1.

In contrast, as shown in Figure 6, Table 2 is a statement automatically generated based on an entity with a composite foreign key. The composite foreign key of the entity may include foreign key information "employee ID" and "institution ID", or include "attendance information ID" and "institution ID". For example, the foreign key association query statement in Table 2 is "Select * from attendance information table where employee ID =? and institution ID =?", etc.

The corresponding generated SQL statement that uses the foreign key as the Where condition can only include "employee ID".

In other embodiments, if the database table where the target data requested for query is located has no subordinate table, that is, there is no associated foreign key field in other database tables and the database table where the target data is located, the server application 20a may not skip this step 309 and no longer execute the following steps 310 to 311.

310: The server application 20a sends the generated SQL statement to the database 20b.

Exemplarily, the server application 20a may send an SQL statement generated based on an entity with a composite foreign key to the database 20b, so as to provide the database 20b with partition key information and foreign key information required for partition query through the SQL statement, wherein the foreign key information is used to query the target data in the associated database table.

311: The database 20b feeds back the data query result to the server application 20a.

Exemplarily, the database 20b may query the data records in the database table associated with the corresponding foreign key information under the corresponding partition based on the composite foreign key information in the received SQL statement, and feed back the queried data as the data query result to the server application 20a.

It is understood that in some data query scenarios, the data query result fed back in step 311 and the corresponding data query result fed back in step 308 can be combined and fed back to the server application 20a. After executing the above steps 306 to 307 and steps 309 to 310, an SQL statement including a composite primary key and a composite foreign key can be sent to the database 20b, and then the database 20b can query the data records with the corresponding primary key information under the corresponding partition in the corresponding database table and the data records under the corresponding partition in the database table associated with the corresponding foreign key information according to the SQL statement, and finally accurately and quickly query all target data, and feed back to the server application 20a as the data query result.

It can be understood that based on the execution process of the above steps 301 to 311, the data query method provided by the embodiment of the present application can make full use of the partition trimming capabilities of databases with partition design such as Oracle databases, and automatically generate data query statements with partition keys and SQL statements such as data update and data deletion. In this way, the database end that provides data management services for the business service system can automatically perform partition trimming operations, give full play to the role of the partition function, and improve data query efficiency. In addition, based on the data query method provided by the embodiment of the present application, it is possible to automatically generate SQL statements with partition key information, which can replace the performance of SQL statements written by developers, which is beneficial to saving the development workload of developers, and is also beneficial to improving the overall performance of the server, such as improving the response rate.

FIG. 7 shows a schematic diagram of the hardware structure of a server 200 according to an embodiment of the present application.

It is understandable that the server 200 can be the application server 200a for installing the server application 20a, or the database server 200b for deploying the database 20b, etc., and no limitation is made here.

As shown in Figure 7, in some embodiments, the server 200 may include one or more processors 204, a system control logic 208 connected to at least one of the processors 204, a system memory 212 connected to the system control logic 208, a non-volatile memory (NVM) 216 connected to the system control logic 208, and a network interface 220 connected to the system control logic 208.

In some embodiments, the processor 204 may include one or more single-core or multi-core processors. In some embodiments, the processor 204 may include any combination of general-purpose processors and special-purpose processors (e.g., graphics processors, application processors, baseband processors, etc.). In an embodiment where the server 200 uses an eNB (Evolved Node B) or RAN (Radio Access Network) controller, the processor 204 may be configured to execute various compliant embodiments, for example, one or more of the multiple embodiments shown in Figures 2 to 5.

In some embodiments, system control logic 208 may include any suitable interface controller to provide any suitable interface to at least one of processors 204 and/or any suitable device or component in communication with system control logic 208 .

In some embodiments, the system control logic 208 may include one or more memory controllers to provide an interface to the system memory 212. The system memory 212 may be used to load and store data and/or instructions. In some embodiments, the system memory 212 of the server 200 may include any suitable volatile memory, such as a suitable dynamic random access memory (DRAM).

NVM/memory 216 may include one or more tangible, non-transitory computer-readable media for storing data and/or instructions. In some embodiments, NVM/memory 216 may include any suitable non-volatile memory such as flash memory and/or any suitable non-volatile storage device, such as at least one of a HDD (Hard Disk Drive), a CD (Compact Disc) drive, and a DVD (Digital Versatile Disc) drive.

NVM/storage 216 may include a portion of storage resources on the device on which server 200 is installed, or it may be accessible to a device but not necessarily a portion of the device. For example, NVM/storage 216 may be accessed over a network via network interface 220.

In particular, system memory 212 and NVM/storage 216 may include, respectively, a temporary copy and a permanent copy of instructions 224. Instructions 224 may include instructions that, when executed by at least one of processors 204, cause server 200 to implement the method shown in FIG. 3. In some embodiments, instructions 224, hardware, firmware, and/or software components thereof may be additionally/alternatively located in system control logic 208, network interface 220, and/or processor 204.

The network interface 220 may include a transceiver for providing a radio interface for the server 200, thereby communicating with any other suitable device (such as a front-end module, an antenna, etc.) through one or more networks. In some embodiments, the network interface 220 may be integrated with other components of the server 200. For example, the network interface 220 may be integrated with at least one of the processor 204, the system memory 212, the NVM/storage 216, and a firmware device (not shown) having instructions, and when at least one of the processors 204 executes the instructions, the server 200 implements the method shown in FIG. 3 above.

The network interface 220 may further include any suitable hardware and/or firmware to provide a multiple-input multiple-output radio interface. For example, the network interface 220 may be a network adapter, a wireless network adapter, a telephone modem and/or a wireless modem.

In one embodiment, at least one of the processors 204 may be packaged together with logic for one or more controllers of the system control logic 208 to form a system in package (SiP). In one embodiment, at least one of the processors 204 may be integrated on the same die with logic for one or more controllers of the system control logic 208 to form a system on chip (SoC).

The server 200 may further include an input/output (I/O) device 232. The I/O device 232 may include a user interface to enable a user to interact with the server 200; and a peripheral component interface design to enable peripheral components to interact with the server 200. In some embodiments, the server 200 further includes a sensor for determining at least one of an environmental condition and location information related to the server 200.

In some embodiments, the user interface may include, but is not limited to, a display (e.g., an LCD display, a touch screen display, etc.), a speaker, a microphone, one or more cameras (e.g., a still image camera and/or a video camera), a flashlight (e.g., an LED flash), and a keyboard.

In some embodiments, the peripheral component interface may include, but is not limited to, a non-volatile memory port, an audio jack, and a power interface.

In some embodiments, the sensors may include, but are not limited to, gyroscope sensors, accelerometers, proximity sensors, ambient light sensors, and positioning units. The positioning unit may also be part of or interact with the network interface 220 to communicate with components of a positioning network (e.g., global positioning system (GPS) satellites).

References to "one embodiment" or "an embodiment" in the specification mean that the specific features, structures, or characteristics described in conjunction with the embodiment are included in at least one exemplary implementation or technology disclosed according to the embodiment of the present application. The appearance of the phrase "in one embodiment" in various places in the specification does not necessarily all refer to the same embodiment.

The disclosure of the embodiments of the present application also relates to an apparatus for performing the operations in the text. The apparatus may be constructed specifically for the required purpose or it may include a general-purpose computer selectively activated or reconfigured by a computer program stored in the computer. Such a computer program may be stored in a computer-readable medium, such as, but not limited to, any type of disk, including a floppy disk, an optical disk, a CD-ROM, a magneto-optical disk, a read-only memory (ROM), a random access memory (RAM), an EPROM, an EEPROM, a magnetic or optical card, an application-specific integrated circuit (ASIC), or any type of medium suitable for storing electronic instructions, and each may be coupled to a computer system bus. In addition, the computer mentioned in the specification may include a single processor or may be a system architecture involving multiple processors for increased computing power. Structure.

In addition, the language used in this specification has been primarily selected for readability and instructional purposes and may not be selected to describe or limit the disclosed subject matter. Therefore, the present application embodiment disclosure is intended to illustrate rather than limit the scope of the concepts discussed herein.

Claims (14)

1. A data query method, characterized in that it is applied to a server of a business service system, the server includes at least one server application and at least one database, and the method includes:

   The server application determines, in response to a data query request sent by a client, a first entity corresponding to the data query request, wherein the first entity is used to store first relevant information of a first database table in the database, the first relevant information at least including first primary key information and partition key information of the first database table, the partition key information is information related to an identification field determined by the database in the first database table, and the identification field is a common field of the first database table and a subordinate table of the first database table;

   The server application generates a first query statement sent to the database, wherein the query condition in the first query statement includes the first primary key information and the partition key information;

   The database searches, based on the received first query statement, for first target data containing the primary key information in the target partition corresponding to the partition key information in the first database table;

   The server application receives the first target data fed back by the database, and returns the first target data to the client as a data query result.
2. The method according to claim 1, further comprising: the server application responding to the data query request sent by the client to determine the second entity corresponding to the data query request, wherein:

   The second entity is used to store second related information of a second database table in the database, wherein the foreign key information in the second database table is the same as the first primary key information in the first database table, and the primary key information of the second entity is different from the primary key information of the first entity but both include at least partially the same partition key information, and the second related information includes at least the foreign key information and the partition key information; and

   The server application generates a second query statement sent to the database, wherein the query condition in the second query statement includes the foreign key information and the partition key information;

   The database searches, based on the received second query statement, for second target data containing the foreign key information in the target partition corresponding to the partition key information in the second database table;

   The server application receives the second target data fed back by the database, and determines a data query result to be returned to the client based on the first target data and the second target data.
3. The method according to claim 2, characterized in that the second related information further includes second primary key information of the second database table, and the method comprises:

   The server application generates a third query statement sent to the database, wherein the query condition in the third query statement includes the second primary key information and the partition key information;

   The database searches, based on the received third query statement, for third target data including the second primary key information in the target partition corresponding to the partition key information in the second database table, wherein the third target data is the same as or different from the second target data;

   The server application receives the third target data fed back by the database, and determines a data query result to be returned to the client based on the first target data, the second target data, and the third target data.
4. The method according to claim 3 is characterized in that the query condition is a Where condition in a SQL statement, and the first query statement, the second query statement and the third query statement are SQL statements.
5. The method according to claim 1, characterized in that the server application generates a first query statement to be sent to the database, comprising:

   Acquire third primary key information of the first entity, wherein the third primary key information is composite information of the first primary key information and the partition key information;

   The third primary key information is used as a query condition to generate the first query statement.
6. The method according to claim 2, characterized in that the server application generates a second query statement to be sent to the database, comprising:

   Acquire fourth primary key information of the second entity, wherein the fourth primary key information is composite information of the foreign key information and the partition key information;

   The fourth primary key information is used as a query condition to generate the second query statement.
7. The method according to any one of claims 1 to 6, characterized in that the database queries the first target data including the primary key information in the target partition corresponding to the partition key information in the first database table based on the received first query statement, comprising:

   Determine, based on the partition key information in the query condition of the first query statement, an access path corresponding to the target partition in the first database table to be queried;

   Accessing a target partition in the first database table based on the access path;

   Based on the primary key information in the query condition of the first query statement, the first target data of the record corresponding to the primary key value in the primary key information is searched under the target partition in the first database table.
8. The method according to any one of claims 1 to 7 is characterized in that the database is an Oracle database, and the server-side application is an application developed based on a JPA framework or a Hibernate framework.
9. The method according to claim 8 is characterized in that the first entity is defined by the server application using an object-relational mapping (O/R Mapping) tool, the O/R Mapping tool is provided by the JPA framework or the Hibernate framework, and,

   The process of defining the first entity by the server application includes:

   Obtaining the identification field determined by the database as a partition key;

   A first primary key of the first database table and the partition key are combined into a composite primary key, and the composite primary key is defined as a primary key of a first entity mapped to the first database table.
10. A business service system, characterized in that it includes a client and a server, wherein the server includes at least one server application and at least one database, wherein:

    The server application is used to receive and respond to a data query request sent by a client, and to determine a first entity corresponding to the data query request according to the data query request, wherein the first entity is used to store first relevant information of a first database table in the database, the first relevant information at least including first primary key information and partition key information of the first database table, the partition key information is information related to an identification field of the database in the first database table, and the identification field is a common field of the first database table and a slave table of the first database table; and,

    The server application is further used to generate a first query statement sent to the database, wherein the query condition in the first query statement includes the first primary key information and the partition key information;

    The database is used to query the first target data containing the primary key information under the target partition corresponding to the partition key information in the first database table according to the received first query statement, and The label data is returned to the client through the server application.
11. The system according to claim 10 is characterized in that the server application is further used to determine a second entity requested by the data query request, and to generate a second query statement sent to the database, wherein the second entity is used to store second relevant information of a second database table in the database, the second database table is a foreign key association table of the first database table, the second relevant information at least includes foreign key information of the second database table and the partition key information, the first database table includes a field corresponding to the foreign key information of the second database table, and the query condition in the second query statement includes the foreign key information and the partition key information; and,

    The database is also used to query the second target data containing the foreign key information under the target partition corresponding to the partition key information in the second database table according to the received second query statement, and return the first target data and the second target data to the client through the server application.
12. The system according to claim 10 or 11 is characterized in that the client is installed on a terminal device used by a user, the server application is installed on an application server, and the database is deployed on a database server.
13. A device, characterized in that it comprises: one or more processors; one or more memories; the one or more memories store one or more programs, and when the one or more programs are executed by the one or more processors, the device executes the data query method described in any one of claims 1 to 9.
14. A computer-readable storage medium, characterized in that instructions are stored on the storage medium, and when the instructions are executed on a computer, the computer executes the data query method according to any one of claims 1 to 9.