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

Data query method, and system, device and computer-readable storage medium Download PDF

Info

Publication number
WO2024113740A1
WO2024113740A1 PCT/CN2023/097722 CN2023097722W WO2024113740A1 WO 2024113740 A1 WO2024113740 A1 WO 2024113740A1 CN 2023097722 W CN2023097722 W CN 2023097722W WO 2024113740 A1 WO2024113740 A1 WO 2024113740A1
Authority
WO
WIPO (PCT)
Prior art keywords
database
key information
partition
query
information
Prior art date
Application number
PCT/CN2023/097722
Other languages
French (fr)
Chinese (zh)
Inventor
施瑜
蔡纯钢
王嘉杰
吴秀群
王广贵
莫元武
Original Assignee
易保网络技术(上海)有限公司
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by 易保网络技术(上海)有限公司 filed Critical 易保网络技术(上海)有限公司
Publication of WO2024113740A1 publication Critical patent/WO2024113740A1/en

Links

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/242—Query formulation
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/245—Query processing
    • G06F16/2455—Query execution

Definitions

  • 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.
  • Oracle provides table partitioning technology. This technology can enable the database to support larger data volumes and improve system performance.
  • 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.
  • SQL Structured Query Language
  • JPA Java Persistence API
  • Hibernate object-relational mapping framework
  • 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.
  • 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.
  • the first database table may be a main table in an Oracle database, such as an employee table.
  • the first entity may be a JPA entity corresponding to the employee table definition, i.e., an "employee class" entity
  • 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).
  • 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.
  • a primary key query statement such as an SQL statement
  • 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.
  • 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
  • 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.
  • 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).
  • 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.
  • a foreign key query statement such as an SQL statement
  • 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.
  • 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.
  • 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.
  • the second related information also includes second primary key information of the second database table
  • 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.
  • the server can also respond to the data query request for the attendance information table and other sub-tables sent by the client.
  • 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.
  • 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.
  • 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.
  • the query condition is a Where condition in an SQL statement
  • the first query statement, the second query statement, and the third query statement are SQL statements.
  • 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.
  • 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.
  • 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.
  • the database is an Oracle database
  • the server-side application is an application developed based on a JPA framework or a Hibernate framework.
  • 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
  • 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.
  • 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
  • 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.
  • 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
  • the client is installed on a terminal device used by a user
  • the server application is installed on an application server
  • the database is deployed on a database server.
  • 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.
  • an embodiment of the present application provides a computer-readable storage medium having instructions stored thereon.
  • the instructions When the instructions are executed on a computer, the computer executes the data query method provided in the first aspect and various possible implementations.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • frameworks such as JPA or Hibernate
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • the operation corresponding to the response of the SQL statement may include any of the above-mentioned CRUD operations.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • AR augmented reality
  • VR virtual reality
  • smart TVs smart watches and other wearable devices
  • mobile email devices car equipment
  • portable game consoles portable
  • 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.
  • 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.
  • 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.
  • AR augmented reality
  • VR virtual reality
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • FIG3 is a schematic diagram showing an implementation flow of a data query method according to an embodiment of the present application.
  • 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.
  • the execution subject of each step in the process shown in FIG3 may also be the server.
  • the interaction process may include the following steps:
  • 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.
  • 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.
  • 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.
  • FIG4 shows a database table for partition management in a database according to an embodiment of the present application.
  • the partition includes database tables such as an employee table and an attendance information table.
  • 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.
  • 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.
  • the employee table is used as the master table
  • 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.
  • the employee table and the attendance information table also have the same field, namely, the organization ID.
  • 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.
  • the primary key field of each database table may not be adjusted.
  • 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”
  • the primary key of each database table is still the single primary key field initially designed.
  • the database 20b feeds back the selected partition key field to the server application 20a.
  • 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.
  • 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.
  • the partition key field selected in the above step 301 can be used as one of the primary keys of the defined entity.
  • 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.
  • 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.
  • 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”.
  • 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.
  • the primary key of the employee class is the employee ID
  • the primary key of the attendance information class is the attendance information ID
  • 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.
  • 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”.
  • the primary key information of the entity can include "employee ID” and "institution ID”.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • the server application 20a receives the data query request sent by the client.
  • the server application 20a can receive the data query request sent by the client.
  • 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.
  • 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.
  • 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.
  • FIG. 6 shows some SQL statement examples with different Where conditions according to an embodiment of the present application.
  • 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”.
  • 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.
  • 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”.
  • 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.
  • the server application 20a sends the generated SQL statement to the database 20b.
  • 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.
  • the database 20b feeds back the data query result to the server application 20a.
  • 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.
  • 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.
  • 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.
  • 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.
  • FIG. 6 shows some SQL statement examples with different Where conditions according to an embodiment of the present application.
  • 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”.
  • 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”.
  • the corresponding generated SQL statement that uses the foreign key as the Where condition can only include "employee ID”.
  • the server application 20a may not skip this step 309 and no longer execute the following steps 310 to 311.
  • the server application 20a sends the generated SQL statement to the database 20b.
  • 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.
  • the database 20b feeds back the data query result to the server application 20a.
  • 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.
  • 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.
  • 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.
  • 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.
  • partition design such as Oracle databases
  • SQL statements such as data update and data deletion
  • 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.
  • 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.
  • 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.
  • 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.
  • processors 204 a system control logic 208 connected to at least one of the processors 204
  • system memory 212 connected to the system control logic 208
  • NVM non-volatile memory
  • network interface 220 connected to the system control logic 208.
  • 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.
  • eNB evolved Node B
  • RAN Radio Access Network
  • 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 .
  • 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.
  • the system memory 212 of the server 200 may include any suitable volatile memory, such as a suitable dynamic random access memory (DRAM).
  • DRAM dynamic random access memory
  • NVM/memory 216 may include one or more tangible, non-transitory computer-readable media for storing data and/or instructions.
  • 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.
  • HDD Hard Disk Drive
  • CD Compact Disc
  • DVD Digital Versatile Disc
  • 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.
  • 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.
  • 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.
  • the network interface 220 may be integrated with other components of the server 200.
  • 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.
  • the network interface 220 may be a network adapter, a wireless network adapter, a telephone modem and/or a wireless modem.
  • 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).
  • SiP system in package
  • SoC system on chip
  • 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.
  • the server 200 further includes a sensor for determining at least one of an environmental condition and location information related to the server 200.
  • 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.
  • a display e.g., an LCD display, a touch screen display, etc.
  • a speaker e.g., a speaker
  • a microphone e.g., a microphone
  • one or more cameras e.g., a still image camera and/or a video camera
  • a flashlight e.g., an LED flash
  • the peripheral component interface may include, but is not limited to, a non-volatile memory port, an audio jack, and a power interface.
  • 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).
  • GPS global positioning system
  • 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.
  • 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.
  • 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.

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Physics & Mathematics (AREA)
  • Computational Linguistics (AREA)
  • Data Mining & Analysis (AREA)
  • Databases & Information Systems (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Mathematical Physics (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

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
本申请要求于2022年11月28日提交中国专利局、申请号为202211503327.1、申请名称为“数据查询方法、***、设备及计算机可读存储介质”的中国专利申请的优先权，其全部内容通过引用结合在本申请中。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
针对具有海量数据记录的数据库表，Oracle提供了表分区(Table Partition)技术。该技术可以使数据库支持更大的数据量，并能提升***性能。在表分区技术中有一个很重要的功能性概念叫分区剪裁，即在结构化查询语言(Structured Query Language，SQL)的Where条件中包括分区键字段的情况下，基于Oracle管理的数据库(简称Oracle数据库)可以仅在相应数据库表中该分区键字段对应的分区下进行数据查询。如此，可以极大的提升数据库的查询/检索性能。因此，在开发业务服务***时，开发人员为***的数据查询服务编写的数据查询语句，例如采用上述SQL编写的SQL语句，通常都会遵循上述分区剪裁功能对应的编程规范，在相应语句中加入分区键字段。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.
然而，当数据库采用Java持久层API(Java Persistence API，JPA)框架或者对象关系映射框架(Hibernate)等框架时，所有的语句都是由工具程序自动生成的，CRUD操作对应的SQL语句都是使用主键字段或者外键字段作为Where条件的，不会包含数据库表的分区键，这样就不能充分使用数据库的分区剪裁能力，数据库会遍历所有分区进行查询操作，有较大的性能损耗。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
本申请提供了一种数据查询方法、***、设备及计算机可读存储介质，其中的方法能够利用具有分区剪裁能力的Oracle数据库等的数据库表的字段关联特点，确定各数据库表中的分区键字段，并将该分区键字段自动添加到数据查询SQL语句中，作为Where条件进行数据查询，从而提高数据查询效率，并且能充分发挥Oracle数据库等的分区功能，有利于保障数据查询效率稳定，服务端也因此可以提高对客户端的响应速率，因而有利于提高用户体验。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.
例如，上述第一数据库表例如可以是Oracle数据库中的一个主表，例如可以是员工表等。相应地，第一实体例如可以是对应于员工表定义的JPA实体，即“员工类”实体，该“员工类”实体的主键信息例如可以是员工表的主键信息(即上述第一主键信息)与分区键信息(例如机构ID)的复合信息。如此，服务端在响应客户端发来的数据查询请求时，服务端应用可以调用已定义的第一实体，并利用该第一实体的主键信息自动生成具有数据库表主键信息和分区键信息的主键查询语句，例如SQL语句，在相应数据库的分区中查询相关的目标数据。如此，服务端则可以自主利用Oracle数据库等的分区剪裁能力，无需借助于开发人员编写的具有分区键作为Where条件的SQL语句进行分区查询，有利于提高服务端对客户端的响应速率，提高用户体验。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.
例如，上述第二数据库表例如可以是Oracle数据库中的一个从表，例如可以是员工表的从表：考勤信息表等。相应地，第二实体例如可以是对应于考勤信息表定义的JPA实体，即“考勤信息类”实体，该“考勤信息类”实体的主键信息例如可以是考勤信息表的外键信息与分区键信息(例如机构ID)的复合信息。如此，服务端在响应客户端发来的数据查询请求时，服务端应用可以在调用上述第一实体，进而在员工表中查询到相应分区下的相关数据记录时，基于已定义的第二实体的主键信息自动生成能够关联到考勤信息表的外键查询语句，例如SQL语句，从而在相应数据库的分区中可以进一步查询到外键关联的数据库表中的相关目标数据。相应地，服务端向客户端反馈的数据查询结果例如可以是上述查询到的第一目标数据和第二目标数据的组合。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.
如此，服务端则可以在查询客户端请求的数据时，自动生成带有分区键的Where条件，无需借助于开发人员在SQL语句中编写带有分区键的Where条件来进行分区查询，有利于减少开发人员的工作量。并且，服务端还可以在查询外键关联数据时，所生成的查询条件也能自动带上分区键作为Where条件，有利于提高数据查询效率，进而提高服 务端对客户端的响应速率，提高用户体验。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.
在上述第一方面的一种可能的实现中，查询条件为SQL语句中的Where条件，第一查询语句、第二查询语句以及第三查询语句为SQL语句。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.
在上述第一方面的一种可能的实现中，数据库为Oracle数据库，服务端应用为基于JPA框架或Hibernate框架开发的应用程序。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.
在上述第一方面的一种可能的实现中，第一实体由服务端应用采用对象关系映射(O/R Mapping)工具定义，O/R Mapping工具由JPA框架或Hibernate框架提供，并且，服务端应用定义第一实体的过程，包括：获取数据库确定的作为分区键的标识字段；将第一数据库表的第一主键与分区键组合成复合主键，并将复合主键定义为映射至第一数据库表的第一实体的主键。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
图1所示为本申请实施例提供的一种数据查询方法所应用的场景示意图。FIG1 is a schematic diagram of a scenario in which a data query method provided in an embodiment of the present application is applied.
图2所示为本申请实施例提供的一种服务端所具有的软件结构示意框图。FIG. 2 is a schematic block diagram of a software structure of a server provided in an embodiment of the present application.
图3所示为本申请实施例提供的一种数据查询方法的实施流程示意图。FIG3 is a schematic diagram showing an implementation flow of a data query method provided in an embodiment of the present application.
图4所示为本申请实施例提供的一种数据库中分区管理的数据库表。FIG. 4 shows a database table for partition management in a database provided in an embodiment of the present application.
图5所示为本申请实施例提供的采用复合主键前后对应定义的实体的数据结构变化示意图。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.
图6所示为本申请实施例提供的一些具有不同Where条件的SQL语句示例。FIG. 6 shows some examples of SQL statements with different Where conditions provided in an embodiment of the present application.
图7所示为本申请实施例提供的一种服务器200的硬件结构示意图。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)数据库，用于存储大量的数据实体。数据库设计则是将数据库中的数据实体及这些数据实体之间的关系,进行规划和结构化的过程。(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)数据库表，是由表名、表中的字段和表的记录三个部分组成的。设计数据库表结构(以下简称表结构)就是定义数据库表文件名，确定数据库表包含哪些字段，各字段的字段名、字段类型、及宽度，并将这些数据输入到计算机当中。可以理解，数据库表是数据库端的数据载体。(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)主键，指的是数据库表中一个列或多列的组合，主键的值能唯一地标识表中的每一行。一个数据库表的主键可以与其他表的外键关联，以关联其他数据库表中的字段文本等的新增、删除、变更/修改等。(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)，是将数据库中的数据库表进行分区的表字段。数据库中各数据库表完成分区后，各分区可以具有唯一标识的分区键。(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)是将大表的数据分成称为分区的许多小的子集。分区的种类划分主要包括范围(Range)、列表(List)、散列(Hash)以及复合(Range+List或Range+Hash)分区等。可以理解，分区的划分依据主要是根据上述大表以及分区表的内部属性。同时，分区表可以创建其独特的分区索引。(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是一种关系数据库管理***。采用Oracle管理的数据库通常称为Oracle数据库。安装在应用程序服务器上的服务端应用可以基于JPA或Hibernate等框架提供的工具程序，借助于服务器底层的JDBC(Java Database Connectivity)驱动程序等与Oracle数据库之间进行数据交互，实现对数据库以及各数据库表中不同分区记录的数据查询功能。(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，是指在做计算处理时的增加(Create)、读取查询(Retrieve)、更新(Update)和删除(Delete)几个单词的首字母简写。主要被用在描述软件***中数据库或者持久层(JPA)的基本操作功能。(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)实体，是应用端的数据载体，例如安装在应用程序服务器上的服务端应用所使用的数据载体，实体通常会包括一些属性和方法。而应用程序服务器上应用的JPA或Hibernate等框架实现了应用端实体与数据库表之间的映射关系，以将应用端对数据实体的操作转换为对数据库表操作的SQL语句，并通过JDBC驱动发送到数据库服务器端，数据库服务器响应这些SQL语句，从而实现对数据库表的操作。通常，映射至相应数据库表的实体，可以与该数据库表具有相同或相似的表结构，例如该实体可以与相应数据库表具有相同的主键或外键。(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.
图1根据本申请实施例示出了一种数据查询方法所应用的场景示意图。FIG1 is a schematic diagram showing a scenario in which a data query method is applied according to an embodiment of the present application.
如图1所示，该场景包括终端设备100和服务器(或服务器集群)200。用户可以通过终端设备100进行一些数据查询操作，例如搜索保单、查看保单、导出保单数据等操作，向服务器200发起数据查询以及数据获取等请求。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.
服务器200在接收到终端设备100响应于用户操作发来的数据查询请求后，可以匹配或生成对应的数据查询语句，在数据库中查找用户所请求的数据，并将查询到的数据(即数据查询结果)反馈给终端设备100。其中，服务器200可以包括应用程序服务器200a以及数据库服务器200b，在另一些实施例中，服务器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.
可以理解，应用程序服务器200a上可以安装应用端，例如一个或多个服务端应用，用于接收终端设备100上安装的客户端发来的上述数据查询请求，并调用预设框架提供的工具程序，例如上述JPA框架或Hibernate框架提供的对象关系映射(Object Relational Mapping，O/R Mapping)工具程序等，自动生成能够映射到相应数据库或者分区进行数据查询的数据查询语句，并发送给数据库服务器200b上部署的数据库端查询相应数据。该数据查询语句例如可以是SQL语句。在另一些实施例中，该SQL语句对应响应的操作可以包括上述CRUD操作中的任一项。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.
进而，数据库服务器200b可以将基于接收到的数据查询语句查询到的数据，即数据查询结果通过相应的服务端应用反馈给客户端。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.
然而，如前所述，这类自动生成的SQL语句通常使用数据库表的主键字段或外键字段作为Where条件，而不包括相应数据所属分区的分区键字段。因此，该SQL语句无法利用Oracle数据库的分区剪裁能力进行分区查询，导致数据查询效率降低，影响服务器200侧的服务端应用和数据库端对相应数据查询请求的响应速度，进而导致用户体验变差。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.
如此，在应用了JPA或Hibernate等自动生成SQL语句的框架工具的服务端，也能够有效利用Oracle数据库等的分区剪裁能力，自动进行分区查询，提高数据查询效率，或者说有利于保障数据查询效率稳定。服务端也因此可以提高对客户端的响应速率，用户体验也可以因此得以提高。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.
可以理解，在一些场景中，如果用户在客户端操作对应请求查询的数据无其他关联数据库表，则基于本申请实施例提供的数据查询方法可以根据自动生成的SQL语句中的复合主键，精准定位到相应分区下查询具有相应主键的数据记录。在另一些场景中，如果用户在客户端操作对应请求查询的数据有其他通过外键关联的数据库表，则基于本申请实施例提供的数据查询方法不仅可以根据SQL语句中复合主键中的分区键精准定位到相应分区下查询具有相应主键的数据记录、还可以根据复合外键定位到关联数据表中相应分区下快速查询到相应的数据记录。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.
可以理解，本申请实施例所提供的数据查询方法，所适用的电子设备包括但不限于上述服务器200，以及终端设备100例如手机、平板电脑、桌面型、膝上型、手持计算机、上网本，以及增强现实(Augmentedreality，AR)\虚拟现实(Virtual Reality，VR)设备、智能电视、智能手表等可穿戴设备、移动电子邮件设备、车机设备、便携式游戏机、便携式音乐播放器、阅读器设备、其中嵌入或耦接有一个或多个处理器的电视机、或能够 访问网络的其他设备。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.
可以理解，上述服务端是指与终端设备100上安装的客户端之间进行交互的业务服务***服务端。该服务端所提供的应用服务可以通过上述安装在应用程序服务器200a上的服务端应用来实现，该服务端所提供的数据库相关服务可以通过部署在数据库服务器200b上的数据库来实现，该数据库例如可以是Oracle数据库。因此，本申请实施例提供的配置数据管理方法所适用的服务器/服务器集群，可以包括应用程序服务器(The Application Server)等具有较强计算能力或者说应用程序运行能力的服务器或者其他电子设备、以及数据库服务器等具有较强存储能力以及能够运行Oracle等数据库管理***实现分区剪裁的服务器及其他电子设备。在此不做限制。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.
可以理解，上述安装有客户端的终端设备100，可以包括但不限于手机、平板电脑、桌面型、膝上型、手持计算机、上网本，以及增强现实(Augmentedreality，AR)\虚拟现实(Virtual Reality，VR)设备、智能电视、智能手表等可穿戴设备、移动电子邮件设备、车机设备、便携式游戏机、便携式音乐播放器、阅读器设备、其中嵌入或耦接有一个或多个处理器的电视机、或能够访问网络的其他电子设备。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.
图2根据本申请实施例示出了一种服务端所具有的软件结构示意框图。FIG2 is a schematic block diagram showing a software structure of a server according to an embodiment of the present application.
如图2所示，服务端可以包括服务端应用20a以及数据库20b。其中，服务端应用20a可以安装于应用程序服务器200a上，数据库20b可以部署于数据库服务器200b上。相应地，图2所示的服务端可以部署在包括应用程序服务器200a和数据库服务器200b的服务器集群或者集成的服务器200上。可以理解，该服务端例如可以是一些大型业务服务***的组成部分，例如保险业务服务***中的服务端等。部署上述服务端的服务器集群也可以包括其他类型的服务器，例如文件服务器、内容管理服务器、代理服务器、电子邮件服务器等，在此不做限制。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.
可以理解，图2所示的服务端可以基于服务端应用20a获取客户端发来的数据查询请求，该数据查询请求例如可以是用户在相应终端设备100显示的客户端界面上进行的查看、下载、修改、删除或者说CRUD等操作对应发起的查询相应数据的请求，在此不做限制。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.
基于图2所示的服务端结构，下面结合具体的实施流程图，详细说明本申请实施例提供的数据查询方法的具体实现过程。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.
图3根据本申请实施例示出了一种数据查询方法的实施流程示意图。FIG3 is a schematic diagram showing an implementation flow of a data query method according to an embodiment of the present application.
可以理解，图3所示流程可以涉及服务端中的服务端应用20a与数据库20b之间的交互过程，其中服务端应用20a与数据库20b可以安装/部署在不同的服务器上，例如服务端应用20a可以安装在应用程序服务器上，数据库20b可以部署在数据库服务器上，在此不做限制。在另一些实施例中，图3所示流程中各步骤的执行主体也可以均为服务端。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.
如图3所示，该交互流程可以包括以下步骤：As shown in FIG3 , the interaction process may include the following steps:
301：数据库20b在数据库的主表上选择合适字段作为分区键字段，并在从表上使用相同的字段作为分区键字段。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.
示例性地，上述数据库20b例如可以是Oracle数据库。Oracle数据库中各数据库表中的数据记录通常会按照预设的分区规则被划分至相应的分区下，例如按照各数据记录 所属的机构或单位信息、被应用到的软件项目信息、被调用的端口信息或者被调用的主机信息、服务信息等划分到相应的分区下。相应地，各数据库表中不同分区的数据记录可以具有一些共同字段信息，这些共同字段则可以作为标识该分区的分区键字段。该分区键字段也可以理解为该分区内能在各数据库表之间建立关联的字段。因此，数据库20b可以在管理各数据库表的主键信息、外键信息等，还可以在数据库的主表中选择一个分区键字段作为该主表以及外键关联的从表等各数据库表中的分区键信息。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.
作为示例，图4根据本申请实施例示出了一种数据库中分区管理的数据库表。As an example, FIG4 shows a database table for partition management in a database according to an embodiment of the present application.
如图4所示，例如该分区包括员工表、考勤信息表等数据库表。其中，员工表中的字段包括员工ID、机构ID、姓名等，其中员工ID为该员工表的主键。考勤信息表中的字段包括考勤信息ID、员工ID、机构ID、考勤日期、考勤状态等，其中考勤信息ID例如可以是考勤卡的***等信息，可以设计为该考勤信息表的主键。并且，图3所示的员工表与考勤信息表之间可以构成主从表，例如二者可以通过员工ID建立主从关系。如此，参考图3所示，员工表作为主表，考勤信息表则作为员工表的从表，其中考勤信息表中的员工ID为关联员工表的外键。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.
继续如图4所示，员工表与考勤信息表之间，除了通过员工ID关联之外，二者还具有相同的字段，即机构ID。并且，该分区的其他数据库表也可能包含有该机构ID字段。因此，数据库20b可以在员工表中选择该机构ID作为分区键字段，并将图4所示考勤信息表中的机构ID也作为分区键字段。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.
可以理解，数据库20b在选择作为分区键的字段时，对于各数据库表的主键字段可以不做调整。例如，上述图4所示员工表的主键仍为“员工ID”、考勤信息表的主键仍为“考勤信息ID”等，即保持各数据库表的主键仍为初始设计的单个主键字段。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：数据库20b向服务端应用20a反馈选择的分区键字段。302: The database 20b feeds back the selected partition key field to the server application 20a.
示例性地，数据库20b在选定各数据库表中的分区键字段后，可以将该分区键字段反馈给服务端应用20a，以便于服务端应用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：服务端应用20a将分区键字段与相应的数据库表的主键进行复合得到复合主键，并定义包括该复合主键的实体。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.
示例性地，在对JPA实体进行定义时，可以将上述步骤301中选择出的分区键字段，作为所定义的实体的主键之一，例如可以将该分区键字段与相应数据库表中原始设计的主键进行复合，得到的复合主键则可以作为所定义的实体的主键。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.
作为示例，基于上述图4所示的数据库表结构，服务端应用20a对应定义的实体的数据结构可以参考图5所示的示例。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 .
图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.
如图5所示，在采用复合主键之前，基于上述图4所示的“员工表”可以对应定义包含该“员工表”主键的“员工类”实体，即该实体的主键信息为“员工ID”。同样地，基于上述图4所示的“考勤信息表”可以对应定义包含该“考勤信息表”主键的“考勤信息类”实体，即该实体的主键信息为“考勤信息ID”。可以理解，在采用复合主键之前，对各数据库表关联定义的实体的主键通常会默认与数据库中相应数据库表的主键一致， 例如员工类的主键为员工ID、考勤信息类的主键为考勤信息ID等。另外，如果上述图4所示的“员工表”通过外键关联多个“考勤信息表”，此时服务端应用20a则可以定义一个“员工类”实体，并基于外键关联的多个“考勤信息表”定义多个“考勤信息类”实体，即二者的数量对应关系可以是1对n的对应关系。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.
继续参考图5所示，在采用复合主键之后，基于上述图4所示的“员工表”可以对应定义包含复合主键的“员工类”实体，即该实体的主键信息可以包括“员工ID”和“机构ID”，同样地，基于上述图4所示的“考勤信息表”可以对应定义包含复合主键的“考勤信息类”实体，即该实体的主键信息为“考勤信息ID”和“机构ID”。可以理解，在采用复合主键定义的实体中，“机构ID”既是分区键也是主键。另外，如果上述图4所示的“员工表”通过外键关联多个“考勤信息表”，此时服务端应用20a则可以定义一个具有复合主键的“员工类”实体，并基于外键关联的多个“考勤信息表”定义多个具有复合主键的“考勤信息类”实体，即二者的数量对应关系也可以是1对n的对应关系。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：服务端应用20a将分区键字段与相应的数据库表的外键进行复合得到复合外键，并将该复合外键添加到定义的实体中。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.
示例性地，在一些数据查询场景中，不仅需要查询数据库表中相应分区下包含相应主键信息的数据记录，还需要查询外键关联的一些数据库表中相应分区下的相关数据记录，以实现数据查全的目的。此种场景下，服务端应用20a可以基于数据库中各数据库表的主键字段和分区键字段，复合得到复合外键，并将该复合外键添加到已定义的包括复合主键的实体中，形成具有复合外键的实体。其中，该复合外键例如可以包括各数据库中原始设计的外键信息以及上述步骤302数据库20b反馈的分区键信息，即使用分区键信息作为主从表之间的外键关联条件之一。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.
作为示例，继续参考上述图5所示，在采用复合外键之前，基于上述图4所示的“考勤信息表”可以对应定义包含该“考勤信息表”外键的“考勤信息类”实体，即该实体的外键信息为“员工ID”，该外键信息则可以对应关联到作为“员工类”实体。可以理解，在采用复合外键之前，对各数据库表关联定义的实体的外键通常会默认与数据库中相应数据库表的外键一致，例如考勤信息类的外键为员工ID等。另外，如果上述图4所示的“员工表”通过外键关联多个“考勤信息表”，此时服务端应用20a则可以定义一个“员工类”实体，并基于外键关联的多个“考勤信息表”定义多个“考勤信息类”实体，即二者的数量对应关系可以是1对n的对应关系。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.
继续参考图5所示，基于上述图4所示的“考勤信息表”可以对应定义包含复合外键的“考勤信息类”实体，即该实体的外键信息为“员工ID”和“机构ID”。可以理解，在采用复合外键定义的实体中，“机构ID”既是分区键也是外键。另外，如果上述图4所示的“员工表”通过外键关联多个“考勤信息表”，此时服务端应用20a则可以定义一个具有复合外键的“员工类”实体，并基于外键关联的多个“考勤信息表”定义多个具有复合外键的“考勤信息类”实体，即二者的数量对应关系也可以是1对n的对应关系。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.
可以理解，在一些数据查询场景中，服务端应用20a可以仅执行上述步骤303定义具有复合主键的实体，该实体对应映射到相应的数据库表。在另一些场景中，服务端应用20a也可以执行上述步骤303至304定义具有复合主键和复合外键的实体，该实体可以映射到对应的数据库表。在此不做限制。 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：服务端应用20a接收到客户端发来的数据查询请求。305: The server application 20a receives the data query request sent by the client.
示例性地，参考上述图1所示的场景，当用户操作终端设备100，通过终端设备100上安装的客户端向服务端请求查询或获取相关数据时，相应的客户端可以向服务端应用20a发送数据查询请求。该数据查询请求可以包括所请求数据的主键信息等作为相关数据的识别信息。相应地，服务端应用20a可以接收到客户端发来的数据查询请求。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：服务端应用20a基于实体中的复合主键，生成采用复合主键字段作为Where条件的SQL语句。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.
示例性地，服务端应用20a可以根据接收到的数据查询请求中的主键信息等，匹配相应的实体并调用相应实体生成数据查询语句。例如，应用了JPA框架或Hibernate框架的服务端应用20a，可以基于数据查询请求中的主键信息匹配到包括该主键信息的实体。该实体例如可以是服务端应用20a执行上述步骤303时定义的具有复合主键的实体，该复合主键的信息可以包括数据查询请求中的主键信息。服务端应用20a利用JPA框架或Hibernate框架提供的O/R Mapping工具例如可以自动生成SQL语句作为数据查询语句，上述匹配到的实体中的复合主键即可以作为所生成SQL语句中的Where条件或者Where条件之一，在此不做限制。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.
作为示例，图6根据本申请实施例示出了一些具有不同Where条件的SQL语句示例。As an example, FIG. 6 shows some SQL statement examples with different Where conditions according to an embodiment of the present application.
如图6所示，表1为基于一般实体自动生成的SQL语句。该一般实体的主键例如只是关联数据库表的主键，例如“员工ID”或“考勤信息ID”等。则服务端应用20a利用O/R Mapping工具对应生成的以主键作为Where条件的SQL语句中则只能包括“员工ID”或者“考勤信息ID”，例如表1中的主键查询语句“Select*from员工表where员工ID＝？”或“Select*from考勤信息表where考勤信息ID＝？”等，在此不做限制。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.
在另一些实施例中，利用O/R Mapping工具生成的数据更新语句、数据删除语句或者数据***语句等也可以仅采用主键作为Where条件。例如，表1中的数据更新语句“Update员工表set姓名＝？where员工ID＝？”或“Update考勤信息表set状态＝？where考勤信息ID＝？”、以及表1中的数据删除语句“Delete from员工表where员工ID＝？”或“Delete from考勤信息表where考勤信息ID＝？”等，在此不做枚举。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.
相比之下，继续如图6所示，表2为基于具有复合主键的实体自动生成的语句。该实体所具有的复合主键可以包括主键信息“员工ID”和“机构ID”、或者包括“考勤信息ID”和“机构ID”。例如表2中的主键查询语句“Select*from员工表where员工ID＝？and机构ID＝？”或“Select*from考勤信息表where考勤信息ID＝？and机构ID＝？”等，在此不做限制。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.
在另一些实施例中，利用O/R Mapping工具生成的数据更新语句、数据删除语句或者数据***语句等也可以采用上述复合主键作为Where条件。例如，表2中的数据更新语句“Update员工表set姓名＝？where员工ID＝？and机构ID＝？”或“Update考勤信息表set状态＝？where考勤信息ID＝？and机构ID＝？”、以及表2中的数据删除语句“Delete from员工表where员工ID＝？and机构ID＝？”或“Delete from考勤信息表where考勤信息ID＝？and机构ID＝？”等，在此不做枚举。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：服务端应用20a向数据库20b发送所生成的SQL语句。307: The server application 20a sends the generated SQL statement to the database 20b.
示例性地，服务端应用20a可以将基于具有复合主键的实体生成的SQL语句发送给 数据库20b，以通过该SQL语句向数据库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：数据库20b向服务端应用20a反馈数据查询结果。308: The database 20b feeds back the data query result to the server application 20a.
示例性地，数据库20b可以基于接收到的SQL语句中的复合主键信息，在相应分区下查询包含相应主键信息的数据记录，并将查询到的数据作为数据查询结果反馈给服务端应用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：服务端应用20a基于实体中的复合外键，生成采用复合外键字段作为Where条件中关联外键字段的SQL语句。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.
示例性地，在一些数据查询场景中，服务端应用20a可以根据接收到的数据查询请求中的外键信息等，匹配相应的实体并调用相应实体生成数据查询语句。例如，应用了JPA框架或Hibernate框架的服务端应用20a，可以基于数据查询请求中的外键信息匹配到包括该外键信息的实体。该实体例如可以是服务端应用20a执行上述步骤303时定义的具有复合外键的实体，该复合外键的信息可以包括数据查询请求中的外键信息。服务端应用20a利用JPA框架或Hibernate框架提供的O/R Mapping工具例如可以自动生成SQL语句作为数据查询语句，上述匹配到的实体中的复合外键即可以作为所生成SQL语句中的Where条件或者Where条件之一，在此不做限制。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.
作为示例，图6根据本申请实施例示出了一些具有不同Where条件的SQL语句示例。As an example, FIG. 6 shows some SQL statement examples with different Where conditions according to an embodiment of the present application.
如图6所示，表1为基于一般实体自动生成的SQL语句。该一般实体的外键例如只是关联数据库表的外键，例如“员工ID”或“考勤信息ID”等。则服务端应用20a利用O/R Mapping工具对应生成的以外键作为Where条件的SQL语句中则只能包括“员工ID”或者“考勤信息ID”，例如表1中的外键关联查询语句“Select*from考勤信息表where员工ID＝？”等。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.
相比之下，继续如图6所示，表2为基于具有复合外键的实体自动生成的语句。该实体所具有的复合外键可以包括外键信息“员工ID”和“机构ID”、或者包括“考勤信息ID”和“机构ID”。例如表2中的外键关联查询语句“Select*from考勤信息表where员工ID＝？and机构ID＝？”等。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.
对应生成的以外键作为Where条件的SQL语句中则只能包括“员工ID”。The corresponding generated SQL statement that uses the foreign key as the Where condition can only include "employee ID".
在另一些实施例中，如果请求查询的目标数据所在的数据库表没有从表，即其他数据库表中没有关联的外键字段与目标数据所在的数据库表，则服务端应用20a也可以不跳过本步骤309，并且不再执行下述步骤310至311。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：服务端应用20a向数据库20b发送所生成的SQL语句。310: The server application 20a sends the generated SQL statement to the database 20b.
示例性地，服务端应用20a可以将基于具有复合外键的实体生成的SQL语句发送给数据库20b，以通过该SQL语句向数据库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：数据库20b向服务端应用20a反馈数据查询结果。311: The database 20b feeds back the data query result to the server application 20a.
示例性地，数据库20b可以基于接收到的SQL语句中的复合外键信息，在相应分区下查询相应外键信息关联的数据库表中的数据记录，并将查询到的数据作为数据查询结果反馈给服务端应用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.
可以理解，在一些数据查询场景中，本步骤311所反馈的数据查询结果与上述步骤308中对应反馈的数据查询结果可以合并向服务端应用20a反馈。例如，服务端应用20a 可以执行上述步骤306至307、以及步骤309至310之后，向数据库20b发送包括复合主键以及复合外键的SQL语句，进而数据库20b可以根据该SQL语句在相应数据库表中相应分区下查询具有相应主键信息的数据记录、以及相应外键信息关联的数据库表中相应分区下的数据记录，最终精准且快速的查询到所有目标数据，并作为数据查询结果反馈给服务端应用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.
可以理解，基于上述步骤301至311的执行过程，本申请实施例提供的数据查询方法能够充分利用Oracle数据库等具有分区设计的数据库的分区剪裁能力，自动生成具有分区键的数据查询语句以及数据更新、数据删除等SQL语句。如此，为业务服务***提供数据管理服务的数据库端能够自动进行分区剪裁操作，充分发挥分区功能的作用，提高数据查询效率。另外，基于本申请实施例提供的数据查询方法，能够自动生成具有分区键信息的SQL语句，性能上可以替代开发人员编写的SQL语句所具有的性能，有利于节约开发人员的开发量，也有利于提高服务端的整体性能，例如提高响应速率等。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.
图7根据本申请实施例示出了一种服务器200的硬件结构示意图。FIG. 7 shows a schematic diagram of the hardware structure of a server 200 according to an embodiment of the present application.
可以理解，该服务器200既可以是上述用于安装服务端应用20a的应用程序服务器200a，也可以是上述用于部署数据库20b的数据库服务器200b，等等，在此不做限制。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.
如图7所示，在一些实施例中，服务器200可以包括一个或多个处理器204，与处理器204中的至少一在个连接的***控制逻辑208，与***控制逻辑208连接的***内存212，与***控制逻辑208连接的非易失性存储器(NVM)216，以及与***控制逻辑208连接的网络接口220。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.
在一些实施例中，处理器204可以包括一个或多个单核或多核处理器。在一些实施例中，处理器204可以包括通用处理器和专用处理器(例如，图形处理器，应用处理器，基带处理器等)的任意组合。在服务器200采用eNB(Evolved Node B，增强型基站)或RAN(Radio Access Network，无线接入网)控制器的实施例中，处理器204可以被配置为执行各种符合的实施例，例如，如图2至图5所示的多个实施例中的一个或多个。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.
在一些实施例中，***控制逻辑208可以包括任意合适的接口控制器，以向处理器204中的至少一个和/或与***控制逻辑208通信的任意合适的设备或组件提供任意合适的接口。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 .
在一些实施例中，***控制逻辑208可以包括一个或多个存储器控制器，以提供连接到***内存212的接口。***内存212可以用于加载以及存储数据和/或指令。在一些实施例中服务器200的***内存212可以包括任意合适的易失性存储器，例如合适的动态随机存取存储器(DRAM)。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/存储器216可以包括用于存储数据和/或指令的一个或多个有形的、非暂时性的计算机可读介质。在一些实施例中，NVM/存储器216可以包括闪存等任意合适的非易失性存储器和/或任意合适的非易失性存储设备，例如HDD(Hard Disk Drive，硬盘驱动器)，CD(Compact Disc，光盘)驱动器，DVD(Digital Versatile Disc，数字通用光盘)驱动器中的至少一个。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/存储器216可以包括安装服务器200的装置上的一部分存储资源，或者它可以由设备访问，但不一定是设备的一部分。例如，可以经由网络接口220通过网络访问NVM/存储216。 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.
特别地，***内存212和NVM/存储器216可以分别包括：指令224的暂时副本和永久副本。指令224可以包括：由处理器204中的至少一个执行时导致服务器200实施如图3所示的方法的指令。在一些实施例中，指令224、硬件、固件和/或其软件组件可另外地/替代地置于***控制逻辑208，网络接口220和/或处理器204中。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.
网络接口220可以包括收发器，用于为服务器200提供无线电接口，进而通过一个或多个网络与任意其他合适的设备(如前端模块，天线等)进行通信。在一些实施例中，网络接口220可以集成于服务器200的其他组件。例如，网络接口220可以集成于处理器204的，***内存212，NVM/存储器216，和具有指令的固件设备(未示出)中的至少一种，当处理器204中的至少一个执行所述指令时，服务器200实现上述图3所示的方法。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.
网络接口220可以进一步包括任意合适的硬件和/或固件，以提供多输入多输出无线电接口。例如，网络接口220可以是网络适配器，无线网络适配器，电话调制解调器和/或无线调制解调器。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.
在一个实施例中，处理器204中的至少一个可以与用于***控制逻辑208的一个或多个控制器的逻辑封装在一起，以形成***封装(SiP)。在一个实施例中，处理器204中的至少一个可以与用于***控制逻辑208的一个或多个控制器的逻辑集成在同一管芯上，以形成片上***(SoC)。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).
服务器200可以进一步包括：输入/输出(I/O)设备232。I/O设备232可以包括用户界面，使得用户能够与服务器200进行交互；***组件接口的设计使得***组件也能够与服务器200交互。在一些实施例中，服务器200还包括传感器，用于确定与服务器200相关的环境条件和位置信息的至少一种。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.
在一些实施例中，传感器可包括但不限于陀螺仪传感器，加速度计，近程传感器，环境光线传感器和定位单元。定位单元还可以是网络接口220的一部分或与网络接口220交互，以与定位网络的组件(例如，全球定位***(GPS)卫星)进行通信。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.
本申请实施例的公开还涉及用于执行文本中的操作装置。该装置可以专门处于所要求的目的而构造或者其可以包括被存储在计算机中的计算机程序选择性地激活或者重新配置的通用计算机。这样的计算机程序可以被存储在计算机可读介质中，诸如，但不限于任何类型的盘，包括软盘、光盘、CD-ROM、磁光盘、只读存储器(ROM)、随机存取存储器(RAM)、EPROM、EEPROM、磁或光卡、专用集成电路(ASIC)或者适于存储电子指令的任何类型的介质，并且每个可以被耦合到计算机***总线。此外，说明书中所提到的计算机可以包括单个处理器或者可以是采用针对增加的计算能力的多个处理器涉及的架 构。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. 根据权利要求1所述的方法，其特征在于，进一步包括，所述服务端应用响应于客户端发来的数据查询请求，确定所述数据查询请求对应请求的第二实体，其中，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. 根据权利要求2所述的方法，其特征在于，所述第二相关信息还包括所述第二数据库表的第二主键信息，并且，所述方法包括：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. 根据权利要求3所述的方法，其特征在于，所述查询条件为SQL语句中的Where条件，所述第一查询语句、所述第二查询语句以及所述第三查询语句为SQL语句。 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. 根据权利要求1所述的方法，其特征在于，所述服务端应用生成向所述数据库发送的第一查询语句，包括：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. 根据权利要求2所述的方法，其特征在于，所述服务端应用生成向所述数据库发送的第二查询语句，包括：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. 根据权利要求1至6中任一项所述的方法，其特征在于，所述数据库基于接收到的所述第一查询语句，在所述第一数据库表中对应于所述分区键信息的目标分区下查询包括所述主键信息的第一目标数据，包括：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. 根据权利要求1至7中任一项所述的方法，其特征在于，所述数据库为Oracle数据库，所述服务端应用为基于JPA框架或Hibernate框架开发的应用程序。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. 根据权利要求8所述的方法，其特征在于，所述第一实体由所述服务端应用采用对象关系映射(O/R Mapping)工具定义，所述O/R Mapping工具由所述JPA框架或Hibernate框架提供，并且，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. 根据权利要求10所述的***，其特征在于，所述服务端应用进一步用于确定所述数据查询请求对应请求的第二实体，以及生成向所述数据库发送的第二查询语句，其中所述第二实体用于存储所述数据库中的第二数据库表的第二相关信息，所述第二数据库表为所述第一数据库表的外键关联表，所述第二相关信息至少包括所述第二数据库表的外键信息和所述分区键信息，所述第一数据库表包括所述第二数据库表的外键信息对应的字段，所述第二查询语句中的查询条件包括所述外键信息和所述分区键信息；并且，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. 根据权利要求10或11所述的***，其特征在于，所述客户端安装在用户使用的终端设备上，所述服务端应用安装在应用服务器上，所述数据库部署于数据库服务器上。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. 一种设备，其特征在于，包括：一个或多个处理器；一个或多个存储器；所述一个或多个存储器存储有一个或多个程序，当一个或者多个程序被所述一个或多个处理器执行时，使得所述设备执行权利要求1至9中任一项所述的数据查询方法。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. 一种计算机可读存储介质，其特征在于，所述存储介质上存储有指令，所述指令在计算机上执行时，使所述计算机执行权利要求1至9中任一项所述的数据查询方法。 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.
PCT/CN2023/097722 2022-11-28 2023-06-01 Data query method, and system, device and computer-readable storage medium WO2024113740A1 (en)

Applications Claiming Priority (2)

Application Number Priority Date Filing Date Title
CN202211503327.1 2022-11-28
CN202211503327.1A CN115729951B (en) 2022-11-28 2022-11-28 Data query method, system, device and computer readable storage medium

Publications (1)

Publication Number Publication Date
WO2024113740A1 true WO2024113740A1 (en) 2024-06-06

Family

ID=85298714

Family Applications (1)

Application Number Title Priority Date Filing Date
PCT/CN2023/097722 WO2024113740A1 (en) 2022-11-28 2023-06-01 Data query method, and system, device and computer-readable storage medium

Country Status (2)

Country Link
CN (1) CN115729951B (en)
WO (1) WO2024113740A1 (en)

Families Citing this family (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN115729951B (en) * 2022-11-28 2024-02-09 易保网络技术(上海)有限公司 Data query method, system, device and computer readable storage medium
CN117076547B (en) * 2023-10-16 2024-01-02 江苏量界数据科技有限公司 Data storage method based on virtual distributed server

Citations (4)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN105512200A (en) * 2015-11-26 2016-04-20 华为技术有限公司 Distributed database processing method and device
US20200233861A1 (en) * 2019-01-18 2020-07-23 Salesforce.Com, Inc. Elastic data partitioning of a database
CN113515487A (en) * 2021-09-07 2021-10-19 联想凌拓科技有限公司 Directory query method, computing device and distributed file system
CN115729951A (en) * 2022-11-28 2023-03-03 易保网络技术(上海)有限公司 Data query method, system, device and computer readable storage medium

Family Cites Families (15)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US20070027860A1 (en) * 2005-07-28 2007-02-01 International Business Machines Corporation Method and apparatus for eliminating partitions of a database table from a join query using implicit limitations on a partition key value
US9607042B2 (en) * 2013-09-16 2017-03-28 Mastercard International Incorporated Systems and methods for optimizing database queries
US10803062B2 (en) * 2017-01-31 2020-10-13 Salesforce.Com, Inc. Systems, methods, and apparatuses for implementing a by partition command term within a multi-tenant aware structured query language
CN110019112B (en) * 2017-08-25 2023-10-31 阿里巴巴集团控股有限公司 Data transaction processing method and device and electronic equipment
CN110069523B (en) * 2017-11-23 2023-08-18 阿里巴巴集团控股有限公司 Data query method, device and query system
CN110008232A (en) * 2019-04-11 2019-07-12 北京启迪区块链科技发展有限公司 Generation method, device, server and the medium of structured query sentence
CN110134683A (en) * 2019-04-23 2019-08-16 中国地质大学（武汉） The partition zone optimizing research method and system that magnanimity element stores in relational database
US11068461B1 (en) * 2019-06-25 2021-07-20 Amazon Technologies, Inc. Monitoring key access patterns for nonrelational databases
CN111209296A (en) * 2019-12-31 2020-05-29 航天信息股份有限公司企业服务分公司 Database access method and device, electronic equipment and storage medium
CN111198886A (en) * 2019-12-31 2020-05-26 浙江华云信息科技有限公司 Method for constructing Hbase secondary index table
CN113779120A (en) * 2021-01-29 2021-12-10 北京京东拓先科技有限公司 Data query method, device, equipment and storage medium
CN113760948A (en) * 2021-01-29 2021-12-07 北京沃东天骏信息技术有限公司 Data query method and device
CN113297269A (en) * 2021-02-10 2021-08-24 阿里巴巴集团控股有限公司 Data query method and device
CN113094387A (en) * 2021-04-08 2021-07-09 杭州数梦工场科技有限公司 Data query method and device, electronic equipment and machine-readable storage medium
CN115237918A (en) * 2022-07-27 2022-10-25 杭州湖西云百生科技有限公司 Method for designing heterogeneous data index of joint replacement surgery

Patent Citations (4)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN105512200A (en) * 2015-11-26 2016-04-20 华为技术有限公司 Distributed database processing method and device
US20200233861A1 (en) * 2019-01-18 2020-07-23 Salesforce.Com, Inc. Elastic data partitioning of a database
CN113515487A (en) * 2021-09-07 2021-10-19 联想凌拓科技有限公司 Directory query method, computing device and distributed file system
CN115729951A (en) * 2022-11-28 2023-03-03 易保网络技术(上海)有限公司 Data query method, system, device and computer readable storage medium

Also Published As

Publication number Publication date
CN115729951A (en) 2023-03-03
CN115729951B (en) 2024-02-09

Similar Documents

Publication Publication Date Title
CN107402988B (en) Distributed NewSQL database system and semi-structured data query method
US11106816B2 (en) Database public interface
WO2024113740A1 (en) Data query method, and system, device and computer-readable storage medium
CN109906448B (en) Method, apparatus, and medium for facilitating operations on pluggable databases
JP6188732B2 (en) Computer-implemented method, computer program product, and system for managing tenant-specific data sets in a multi-tenant environment
US7325007B2 (en) System and method for supporting non-native data types in a database API
RU2458399C2 (en) In-memory caching of shared customisable multi-tenant data
US9762670B1 (en) Manipulating objects in hosted storage
US20140172775A1 (en) Field extensibility in a multi-tenant environment with columnar database support
US20130086322A1 (en) Systems and methods for multitenancy data
JP2006012155A (en) System and method for delayed fetching of designated members of user defined type
US20170161511A1 (en) Database public interface
US11216516B2 (en) Method and system for scalable search using microservice and cloud based search with records indexes
US8819050B2 (en) Context-and-version-aware facade for database queries
JP2012123790A (en) Database redistribution utilizing virtual partitions
US11086819B2 (en) Efficiently deleting data from objects in a multi-tenant database system
WO2023231665A1 (en) Distributed transaction processing method, system and device, and readable storage medium
US11354332B2 (en) Enabling data access by external cloud-based analytics system
US10855637B2 (en) Architecture for large data management in communication applications through multiple mailboxes
US11409742B2 (en) Efficient database searching for queries using wildcards
US10860606B2 (en) Efficiently deleting data from objects in a multi tenant database system
US20200042609A1 (en) Methods and systems for searching directory access groups
US7139690B2 (en) Object-level conflict detection in an object-relational database system
CN115113989B (en) Transaction execution method, device, computing equipment and storage medium
US10114864B1 (en) List element query support and processing

Legal Events

Date Code Title Description
121 Ep: the epo has been informed by wipo that ep was designated in this application

Ref document number: 23895906

Country of ref document: EP

Kind code of ref document: A1