An ORM design method and device

Abstract

The application provides an ORM design method and device. The method supports query of multiple-layer associated entities and accurate paging of one-to-many associated query. The steps are as follows: S1, calling a SUID interface and inputting a main entity, in a complex query scenario, filter conditions, query fields, grouping, sorting, and page number parameters can be input; S2, parsing the entity and parameters, generating a sql statement preventing sql injection and corresponding placeholder parameter values; S3, based on the sql statement and the placeholder parameter values, initiating a query to a database and obtaining original query results; S4, automatically assembling the original query results into target entity objects and returning; in particular, S2 can parse multiple-layer associated entities; S4 converts each row of results into an intermediate object and then assembles; in order to realize accurate paging of one-to-many associated query, the sql generated by S2 is rewritten. The above functions support use under a database and table separation mode, and are realized based on an object-oriented structured parsing mode, without the need of parsing sql.

Description

Technical Field

[0001] This invention relates to the fields of computer technology and software technology, and in particular to an ORM design method and apparatus for multi-layered associated entities and one-to-many association with accurate paging. Background Technology

[0002] Explanation of terms or abbreviations:

[0003] ORM: Object Relational Mapping. ORM maps the concepts of objects in object-oriented programming to the concepts of tables in relational databases. This transforms direct database operations into object-oriented operations, allowing indirect manipulation of database tables through object manipulation. A device implementing an ORM should have the capability to automatically convert data formats or data structures between object-oriented and relational databases.

[0004] DB: database.

[0005] SUID: An abbreviation for the four SQL operations of database query, update, insert and delete (select, update, insert and delete).

[0006] Coding complexity C(n): In MVC programming, actions, services, DAOs, and models (entities) are typically involved. When operating on a single database table using object-oriented methods, one DAO is required; when operating on two tables, two DAOs are required; when operating on n tables, n DAOs are required. Therefore, the coding complexity increases to n as the problem size grows. The coding complexity of problem n is denoted by C(n). Thus, the coding complexity of the problem described above is: C(n) = O(n).

[0007] Existing ORM tools have incomplete multi-level related query functions, with limited support for the number of related levels and the number of related entities, and cannot automatically handle multi-level related queries.

[0008] Furthermore, in ORM frameworks, developers typically describe the relationships between database tables using object models and use pagination parameters to output paginated query results. However, in multi-table join scenarios, especially one-to-many joins, directly paginating based on the join results can lead to duplicate primary table rows in the join result set, since one primary table record may correspond to multiple secondary table records. This results in the following problems:

[0009] 1) The number of records per page is inaccurate; the number of records per page does not match the actual number of entities.

[0010] 2) Page number boundaries are disrupted by repeated lines;

[0011] 3) The same business entity may be distributed across multiple pages;

[0012] Therefore, existing 0RM tools often fail to simultaneously satisfy both pagination query accuracy and object reorganization convenience in multi-table pagination scenarios.

[0013] Furthermore, existing database sharding and table partitioning components parse the SQL before sharding and table partitioning is implemented, and then regenerate the SQL based on the sharding and partitioning settings. The drawbacks of this approach are that it requires parsing already generated SQL statements, has limited support for the types of SQL statements, and the accuracy of the regenerated SQL is not guaranteed. Summary of the Invention

[0014] The present invention aims to solve the above-mentioned technical problems and provides an ORM design method and apparatus for accurate pagination of multi-layered related entities and one-to-many relationships, which can also be used in database sharding and table partitioning mode.

[0015] Therefore, one object of the present invention is to provide a method for solving the above-mentioned problems; another object is to provide an apparatus for solving the above-mentioned problems.

[0016] To achieve the above objectives, the present invention adopts the following technical solution:

[0017] The basic steps for ORM to query results using an object-oriented approach are as follows: Step S1: Call the SUID interface, passing in the main entity. For complex queries, a Condition parameter can also be passed. Condition is mainly used to specify complex filtering conditions, query fields, grouping, sorting, page numbers, etc. Step S2: Parse the entity and Condition to obtain the SQL to prevent injection and the corresponding value parameters. Other information needed will also be stored in the context. Step S3: Use the SQL and the values ​​of the placeholder parameters to send a query request to the database and obtain the raw query results. Step S4: Process and assemble the obtained query results, convert them into entity objects, and return them.

[0018] The main entity contains table association information; in step S2, parsing the main entity object yields metadata information about the multi-level table association structure; then, by using a design method that supports processing multi-level association structures, the goal of processing multi-level associated entities is achieved. In step S4, each row of the original query result is first converted into an object of the main entity and objects of each sub-entity; then, they are assembled into the desired entity structure.

[0019] By rewriting the SQL in step 2, accurate pagination can be achieved. This mainly involves adding an extra section of SQL, which can be obtained using the following method: Modify the original SQL to only query the primary key of the main table and use `DISTINCT` to remove duplicates, creating a temporary table; then, join the primary key retrieved from the temporary table with the corresponding primary key in the main table.

[0020] Existing database sharding and table partitioning components rewrite SQL by working backwards from existing SQL statements, which leads to performance and accuracy issues, as well as a limited range of supported SQL statement types. Object-oriented programming, on the other hand, parses structured objects and then generates statements suitable for database sharding and table partitioning, thus eliminating the problems associated with reverse-engineering SQL.

[0021] Beneficial effects

[0022] Compared with the prior art, the present invention has at least the following beneficial effects:

[0023] 1) It can automatically parse multi-level relational queries of object-oriented entities and automatically assemble the results of multi-level relational queries;

[0024] 2) It can achieve accurate pagination.

[0025] 3) Avoid pagination inaccuracies caused by one-to-many relationships;

[0026] 4) Pagination results can be accurately counted by business entity;

[0027] 5) Pagination supports multiple databases; it can be adapted to different databases.

[0028] 6) Database sharding and table partitioning support accurate pagination for one-to-many relationships;

[0029] 7) The ORM layer automatically encapsulates multi-level join queries and can automatically execute accurate pagination without the need to manually write additional SQL. It can also be used in database sharding and table partitioning mode, reducing development complexity.

[0030] 8) Reflection is not required when assembling the query result into the target structure;

[0031] 9) No N+1 problem. Attached Figure Description

[0032] Figure 1 Basic module structure diagram;

[0033] Figure 2 The improved module structure diagram;

[0034] Figure 3 The improved flowchart for parsing entities and conditions;

[0035] Figure 4 A flowchart that assembles intermediate objects, especially multiple sub-entity objects, into the desired entity structure;

[0036] Figure 5 A module structure diagram with interceptor functionality;

[0037] Figure 6 Flowchart for initiating a query. Detailed Implementation

[0038] The present invention provides an ORM design method and apparatus for multi-layered associated entities and one-to-many association with accurate pagination.

[0039] The embodiments described below with reference to the accompanying drawings (primarily using Java when specific computer languages ​​are involved) are merely one implementation of the present invention and should not be construed as limiting the present invention.

[0040] Entities correspond to tables, main entities correspond to main tables, and subordinate tables (child tables) correspond to subordinate entities (child entities). They can be used as synonyms without causing ambiguity; in code implementation, appropriate conversions can be made when necessary.

[0041] First, the device described in this invention is introduced, and its basic modular structure diagram is shown below. Figure 1 As shown. This includes the object-oriented MoreTable interface module (module 11), a module for converting multi-level related entity objects to SQL statements, a module for initiating query requests and obtaining raw query results, and a module for processing and assembling query results. In module 11, the MoreTable interface for multi-level related entity queries has the following query methods (SUID four operations, only query operations are listed):

[0042] public <t>List <t>select(T entity);

[0043] public <t>List <t>select(T entity,int start,int size);

[0044] public <t>List <t>select(T entity,Condition condition);

[0045] Parameter description. `Condition` allows you to specify the fields to query, filter conditions, grouping, sorting, pagination, aggregation queries, etc. In Java, multi-level related entities can define metadata information using annotations (`JoinTable3`). In Python, you can define `_joins_` as shown in the following example, listing the desired information within it.

[0046]

[0047] The `@JoinTable3` annotation is used to represent attributes of child entities. The attributes of `@JoinTable3` include: `JoinType` (representing the join type), `mainField` and `subField` (arrays of the association fields of the parent and child tables), `subAlias` and `mainAlias` (aliases of the parent and child tables), and `subClass` (the type of the child table). Depending on the language, attributes can be added or removed. For example, in Python, it's difficult to determine whether an attribute is a list using language features; an `isList` attribute can be added for the user to fill in. `MoreTableStruct3` is used to store metadata information obtained from parsing entities and `@JoinTable3` annotations. Therefore, it has more attributes and functions than `@JoinTable3`. The specific attributes, descriptions, and functions of `MoreTableStruct3` are shown in Appendix Source Code 1.

[0048] Secondly, the basic steps of the device in completing the object-oriented query results in the ORM are introduced. Step S1: Call the SUID interface, passing in the main entity; complex queries can also pass in the Condition parameter. Step S2: Parse the entity and Condition to obtain the SQL to prevent SQL injection and the corresponding value parameters; other necessary information is also stored in the context. Step S3: Primarily use the SQL and placeholder parameter values ​​to initiate a query request to the database and obtain the original query results. Step S4: Process and assemble the obtained query results, convert them into entity objects, and return them. To prevent SQL injection, placeholders are used when generating the SQL. In Java, using JDBC, the placeholder is generally a question mark (?); other statements may differ, such as Python, which uses "?", "%s", ":", etc.; adjustments can be made according to the specific situation.

[0049] Next, based on this, specific implementation examples will be introduced.

[0050] The source code used is shown in Appendix 1 through Appendix 6. A brief description of the function of each source code is as follows:

[0051] Source code 1: MoreTableStruct3; a data structure for storing metadata information of multi-level related entities;

[0052] Source code 2: MoreObjectToSQL._toSelectSQL; parses multi-level related entities and conditions, and assembles them into SQL;

[0053] Source code 3: ParseSqlHelper.parseJoins; parses multi-level related entities;

[0054] Source code 4: MoreTableResultWrapper; the structure used to transform query results into intermediate objects;

[0055] Source code 5: SqlLib._moreTableSelect3; Initiates a query request, assembling intermediate objects into the target entity structure;

[0056] Source code 6: MoreTableSelect_list_filter5_1_n11_1_n_1; Example source code for multi-level related entity query.

[0057] Example 1: Automatically complete object-oriented entity and multi-level related entity queries

[0058] In order to automatically process queries of multi-level related entities in object-oriented programming, the device of the present invention has improved its module design, and its module diagram is as follows. Figure 2 As shown, the included modules are: an object-oriented MoreTable interface module (module 21), a module for converting multi-level related entity objects to SQL statements (module 22), a module for parsing multi-level related entity objects (module 23), a module for initiating a query request and obtaining the original query results (module 24), a module for converting the original query results to intermediate objects (module 25), and a module for assembling the intermediate objects into the desired entity structure (module 26). Module 21 mainly consists of the MoreTable interface; module 22 mainly consists of source code 2; module 23 mainly consists of source code 3; module 24 mainly consists of the part in source code 5 that queries the database and obtains the results; module 25 mainly consists of the function for generating intermediate results and source code 4; and module 26 mainly consists of source code 5. Module 24 initiates a query request to the database, then module 25 converts the initial query results into intermediate objects, and module 26 then assembles the intermediate objects into the target entity structure and returns it. As can be seen from the MoreTable interface of module 1, the query method of the MoreTable interface can be called for 1 entity, 10 entities, up to n entities. It does not require the user to write a DAO for each entity. Therefore, it has the characteristic of O(1) coding complexity.

[0059] The device described in this invention supports multiple connection types, including: inner connection, left connection, right connection, full connection, and connection where the connection conditions are written in the where part.

[0060] The improvements are further explained in comparison to the basic steps. The main function of step S2 is to parse the entity and condition to obtain the SQL to prevent injection and the corresponding value parameters. To support automatic processing of multi-level related entities, improvements are needed. The flowchart is as follows: Figure 3 As shown. Detailed source code is shown in source code 2. In the basic steps, step S2 specifically involves:

[0061] Step S21: Based on the input entity, in module 23, parse the multi-level related entities to obtain a mapping object composed of sub-table attribute names and corresponding related structures: Map<String,MoreTableStruct3> `moreTableStructMap`. During parsing, it iterates through the hierarchical relationships between entities, allowing multiple related entities at the same level. `moreTableStructMap` stores metadata map information about the relationships between the current child table and its parent table. The source code for parsing multi-level related entities is shown in Appendix Source Code 3.

[0062] Step S22: Based on the input entity, parse and collect the fields of the main entity object (main table), filter conditions and their values;

[0063] Step S23 involves iteratively processing the moreTableStructMap object: 1) parsing and collecting the fields, filtering conditions, and their values ​​of the sub-entity objects (sub-tables); 2) obtaining the association conditions of the current sub-entity (sub-table), and collecting them separately according to the connection type. This iterative processing ensures the algorithm is unaffected by the number of sub-entities or the number of levels.

[0064] Step S24: Determine the fields to be queried. Parse the Condition to obtain the specified fields to be queried. If specific fields are specified, all fields will not be queried by default.

[0065] Step S25 begins assembling the SQL statement. After assembling parts such as `select column_name from main_table join subtable on condition where condition`, the specified conditions are parsed from the `condition` statement, and grouping and sorting are performed. Finally, pagination is added. Since pagination syntax may differ between databases, a corresponding pagination statement is generated based on the current database type. Pagination implementations for MySQL, MariaDB, Oracle, H2, PostgreSQL, SQL Server, and SQLite are provided in advance, and interfaces are reserved to allow users to implement their own database pagination. Therefore, the device described in this invention supports multiple databases and supports database type expansion.

[0066] Additionally, the number of associated entity layers is determined using the following algorithm (the specific implementation is shown in the layer-related code in source code 3):

[0067] Read the associated configuration information corresponding to the main entity;

[0068] The associated configuration information is traversed to identify the child entities directly associated with the main entity;

[0069] After identifying a sub-entity, an initial hierarchy value is set for that sub-entity;

[0070] Perform recursive parsing on the sub-entity to find the next level sub-entity that the sub-entity is still associated with;

[0071] When the next level sub-entity is found, the current level value is incremented and the recursive parsing continues;

[0072] Output the hierarchical relationship information between the main entity and each level of sub-entities until no further sub-entities exist.

[0073] The number of associated entity layers can be multi-layered. In source code 3, the code limits it to a maximum of 10 layers, which can be adjusted according to the situation. Alternatively, the number of layers can be determined by obtaining the configuration value.

[0074] Additionally, the following algorithm prevents circular dependencies between entities (the specific implementation is shown in the typeTree related code in source code 3):

[0075] The data structure used is a list named typeTree to store the types of entities.

[0076] When parsing the main entity, if it is found that an attribute of the main entity (level 1) is a child entity (level 2), then the types of level 1 and level 2 are added to typeTree;

[0077] When parsing the entities obtained from the second level, if a child entity is encountered, it is first checked whether it exists in the typeTree. If it exists, it is returned; if it does not exist, its type is added to the typeTree and the child entity is parsed. Starting from the second level, the child entities obtained will be parsed in a loop.

[0078] It should be noted that when obtaining the second-level sub-entity, it is not first determined whether its type is related to the first-level entity. This is to support cases where the first two entities are the same. For example: select x.sname from Stu x, Stu y where y.sname='WANG'and x.sno>y.sno and x.age<y.age;

[0079] In the basic steps, step S4 specifically includes:

[0080] Step S41, in module 25, each row of the original query result is first converted into intermediate objects: objects of the main entity and objects of each child entity (stored in a Map, associated through sub-table aliases). The specific data structure and description of the intermediate objects are shown in Appendix Source Code 4. How to obtain the values ​​of corresponding fields from the initial query results and set them to the attributes of the corresponding entities, ultimately obtaining the objects of the main entity and child entities, varies depending on the language. For example, in Java, it's more appropriate to assemble the objects by iterating through all the attributes of the main entity and then iterating through the attributes of the child entities in that order. However, in Python, you can first obtain the query list (the order corresponds to each column of the query result), and then directly iterate through a row of data to obtain the value of each column; the entity can be resolved by using the format "sub-table name.field name" for field names, where the dot before the dot is the table name; if there is no dot, it is conventionally a field of the main table. Java cannot use this method because many databases lose the table name portion when obtaining the field name. The table name can only be confirmed by the field name queried. If different entities have the same field name, a field name alias needs to be used. When retrieving the alias, the entity name is included, and the mapping between the alias and the original field name is cached. After solving the problems of obtaining field values ​​and determining the corresponding entities, the assembly of intermediate objects can begin. The processing steps are as follows: 1) Obtain the original value of a field; collect the character type value of a row of data of a certain entity (its purpose is to identify a certain row of data when assembling data later; if they are the same, the entities are considered equal; in addition, it can be converted to MD5 before returning after collection); 2) Convert the original value of a field to a value of a specified type; 3) Set the value to the corresponding entity; 4) Assemble into the structure of the intermediate object and return it. In step S42, in module 26, the intermediate object obtained in step S41 is assembled into the desired entity structure. Because there are multiple relationships and the entities may have multiple levels, the logic here becomes complex. Two-layer relationships include 1:1 and 1:n (one-to-many); multi-layer relationships, such as three-layer relationships, include 1:n:1, 1:1:n, 1:1n1:1, 1:1n1:n, etc. 1:1n1:1 indicates that there are three child entities in the second layer, where the first and third have a 1:1 relationship with the parent entity, and the second has a 1:n relationship. Other relationships follow the same pattern. The specific assembly steps are as follows: Related entities are divided into two categories based on the number of layers: two layers and three or more layers. The second layer must be associated with the main entity object. For layers three or more, the logic may involve looping and indiscriminately processing the objects of the current and previous layers to handle multi-layer related entities. During processing, it is also necessary to determine whether the current child entity is a List type, because in 1:n, the entity n is represented by a List. The original query result is converted into an intermediate object collection, which is then assembled into the desired entity structure. Detailed source code is shown in Appendix Source Code 5.The specific assembly method of step S42 is as follows: receive an intermediate object set generated based on the transformation of a row of original query results, the intermediate object set containing at least a main entity object; first obtain the main entity object, then determine whether there are child entity objects; if not, put the main entity object into a list variable used to return the results, and return it; if there are child entity objects, traverse the child entity objects in the intermediate object set, obtain the association level identifier of the current child entity object, and process it according to the following steps (detailed processing flowchart, as shown). Figure 4 As shown):

[0081] S42-1, Detect whether it is the second layer;

[0082] Y jumps to S42-2, N jumps to S42-7;

[0083] S42-2, Check if the current sub-entity is a List

[0084] Y jumps to S42-3, N jumps to S42-6;

[0085] S42-3, Check if the current child object is already in the cache.

[0086] Y jumps to S42-4, N jumps to S42-5;

[0087] S42-4, the second-level list exists, but the list has not yet been added with second-level objects. Therefore, it needs to be added to the list type property of the first-level object and placed into two caches: the single object cache and the list type object cache.

[0088] S42-5: First, create a new list and put it into the cache; then set the list object to the corresponding property of the main object. If the main object has not yet put the list variable used to return the result into the list, then put it into the cache.

[0089] S42-6, Set the child object to the main object; put the main object into the cache;

[0090] If the main object has not yet been placed into the list variable used to return the result, then it will be placed there;

[0091] S42-7 uses key1 to check if the child object exists in singleCache (single object cache);

[0092] If Y, then continue to check whether the current sub-entity is a List;

[0093] Y jumps to S42-8, N jumps to S42-11;

[0094] S42-8, Check if the list sub-objects exist;

[0095] Y jumps to S42-9, N jumps to S42-10;

[0096] S42-9: If the list has child objects but the current level objects have not yet been added, then they need to be added to the list of the parent level objects.

[0097] S42-10: First, create a new list, then store the current child object into it; set the list object to its parent object; store the current child object and the list object into the corresponding cache;

[0098] S42-11, find the parent object of the current object and set the current object to its parent object.

[0099] If you need to process multiple rows of records, you can put the intermediate results into a list; during the conversion, iterate through each element of the list and call the above steps to process each element.

[0100] Step S43: Return the processed result.

[0101] In paginated queries, when retrieving records starting from `start` and specifying `size` records, performance issues arise due to deep pagination when `start` is a very large value (e.g., one million). A better approach is to pass `start` as either 0 or 1, and use the boundary ID value from the previous query (0 for the first query, and the largest ID from the previous result for ascending ID queries) as a filter. This allows you to retrieve `size` records only if the ID value is greater than `id`, avoiding deep pagination. For descending order, adjustments can be made accordingly. However, this optimization method does not support skipping pages.

[0102] Example 2: One-to-many query, accurate pagination

[0103] Because one-to-many queries with pagination may result in inaccurate pagination, it is necessary to be able to identify the problematic scenarios and rewrite the SQL accordingly.

[0104] First, we'll use a specific example (province and city related query) to illustrate how to rewrite the SQL (using MySQL syntax for pagination).

[0105] The original SQL was as follows:

[0106]

[0107] The rewritten SQL is as follows:

[0108]

[0109] The above example uses the `select*` method to represent the fields to be queried, but in reality, it retrieves the specific fields.

[0110] The specific rewrite steps are as follows: Step S2a, modify the original SQL, changing the select part to only query the primary key of the main table and use DISTINCT to remove duplicates to create a temporary table (named bee_paging in this example); Step S2b, join the primary key retrieved from the temporary table with the corresponding primary key in the main table; the SQL obtained from steps S2a and S2b is called the SQL related to the accurate pagination temporary table; Step S2c, insert the SQL obtained from the above two steps into the main table of the original SQL; the pagination part of the original SQL is no longer needed. When using SQL to prevent injection, the positional value parameters must be in the correct order. In the example above, the temporary table is placed second in the related tables; actually, placing it first would be better, but it would be more complicated when parsing and generating the SQL. In addition, modern databases generally optimize the table join order.

[0111] Based on Example 1, steps S21 to S25 are appropriately improved: the common SQL portion is stored, and if pagination rewriting is required, the relevant SQL for the accurate pagination temporary table is assembled and inserted into the corresponding position. If using SQL injection prevention, for ease of implementation, placeholders containing special characters can be written first, and then the replacement method can be used. Because of the SQL injection prevention method, the SQL statement does not contain static characters, and characters identical to the user-defined placeholders will not appear, leading to incorrect SQL replacement. Detailed source code is shown in Appendix Source Code 2 and Source Code 3.

[0112] Step 4 in this embodiment is the same as steps S41 to S43 in embodiment 1, and no special improvement is needed, because the fields retrieved are the same as before the rewriting.

[0113] The device can intelligently and automatically determine whether accurate paging rewriting is needed. To ensure paging accuracy, paging rewriting is implemented by default. To minimize the impact of paging rewriting on query performance, the device intelligently identifies use cases that do not require rewriting and directs them to the branch that does not use paging rewriting. Alternatively, users can also declare that paging rewriting is not used, such as by adding a corresponding setting method `doNotRewritePagingSql` to the Condition class. The logic for identifying use cases that do not require rewriting is as follows: if any necessary condition is not met, no rewriting is needed; the necessary conditions are:

[0114] 1) Pagination is required;

[0115] 2) There is a one-to-many relationship.

[0116] If any of the following conditions are met, then no rewrite is needed:

[0117] 1) Only query data from the main table;

[0118] 2) There are aggregate queries;

[0119] 3) If all primary key settings in the main table have values ​​or a unique constraint column in the main table has a value, it can be determined that at most one record in the main table can be found.

[0120] If the conditions are met, the rewrite flag is set to false, indicating that pagination rewrite is not required.

[0121] Pagination modification has another advantage: when querying a single page, using the previous page's ID as a filter condition for subsequent pagination queries can avoid slow query speeds when dealing with a large number of pages. For the temporary table, pagination uses `limit number of records per page`, and filtering uses `where id>maximum ID found in the previous query`.

[0122] The problem of unstable sorting due to the absence of a sorting field can be solved by automatically adding a primary key as the sorting field.

[0123] Example 3: Multi-level join queries and accurate pagination rewriting under a sharded database and table model

[0124] The device described in this invention provides a solution for multi-level relational queries and accurate pagination rewriting in a sharded database and table mode. It implements query functionality in a sharded database and table mode based on an object-oriented structured parsing method, eliminating the need to parse and rewrite SQL statements to achieve sharding functionality. Building upon embodiment 2, further improvements have been made to the modules and functions. A pre-interceptor module has been added before module 22, and a post-interceptor module has been added at the end. The module diagram is shown below. Figure 5 As shown, the modules included are: an object-oriented MoreTable interface module (module 51), a pre-interceptor module (module 52), a module for converting multi-level related entity objects to SQL statements (module 53), a module for parsing multi-level related entity objects (module 54), a module for initiating sharded query requests and obtaining raw query results (module 55), a module for converting raw query results to intermediate objects (module 56), a module for assembling intermediate objects into the desired entity structure (module 57), and a post-interceptor module (module 58). Module 51 receives query requests. The main function of module 52 is to execute interceptors in a certain order. Module 53 converts multi-level related entity objects to SQL statements and calls module 54 to parse multi-level related entity objects. Module 55, the sharding engine, initiates sharded query requests to the database, merges the results of multiple shards, and returns them. Then, module 56 converts the initial query results into intermediate objects, and module 57 assembles the intermediate objects into the target entity structure and returns them. The device supports queries of multi-level related entities and accurate pagination for one-to-many related queries. The basic steps are as follows: Step S1, in module 51, the SUID interface is called and the main entity is passed in. In complex query scenarios, filter conditions, query fields, grouping, sorting, and page number parameters can be passed in. Step S2, in module 53, the entity and parameters are parsed to generate an SQL statement to prevent SQL injection and corresponding placeholder parameter values. During this process, module 54 is called to parse multi-level related entities. Step S3, in module 55, a query is initiated to the database based on the SQL statement and placeholder parameter values ​​to obtain the original query results. In module 56, the original query results are first converted into intermediate results. Step S4, in module 57, the intermediate results are automatically assembled into the target entity object and returned. The post-interceptor in module 58 can perform some transformations on the obtained query results before returning them to the user, and can also perform tasks such as clearing the cache.

[0125] The main function of module 52 is to execute interceptors in a specific order. Interceptors are executed before basic step S2. Various types of interceptors can be used, such as interceptors for automatically setting primary key values, interceptors for parsing database sharding and table partitioning, or no interceptors at all. Here, the primary purpose is to implement database sharding and table partitioning. Before parsing and generating SQL, the database sharding and table partitioning parsing interceptor parses and collects the entity and defined sharding information, and uses the obtained information or caches it for use in subsequent steps.

[0126] Sharding information should include at least: the base table name (baseTableName), the sharding key (tabField), and the distribution of all table nodes (fullNodes). For example, as defined:

[0127] bee.db.sharding[0].baseTableName=orders

[0128] bee.db.sharding[0].tabField=userid

[0129] bee.db.sharding[0].fullNodes=ds[0..1].orders[0..5]

[0130] The above configuration defines the sharding of the base table 'orders', and the sharding key 'userid' is used to calculate the mapping between data and tables. There are two data sources: ds0 and ds1. The relationship between tables and data sources uses the default average distribution algorithm. In the example, the mapping is as follows:

[0131] {orders0=ds0, orders1=ds0, orders2=ds0, orders3=ds1, orders4=ds1, orders5=ds1}

[0132] The main function of the database sharding and table partitioning parsing interceptor is to parse the sharding information of entities and definitions to determine whether a certain entity needs to be sharded. Only entities with sharding definitions will trigger the execution of the database sharding and table partitioning parsing interceptor. Additionally, for simplicity, the slave table and the master table can be assumed to be on the same node. In module 53, when parsing the table name, it is necessary to determine whether sharding is required. If so, the table name used for sharding is used. Specifically, in steps S21 to S25 described in Example 1 or Example 2, the table name adopts the format: base table name 'table name subscript special placeholder' base table name. Using the base table as a table alias is closer to the usage habits in single-database scenarios; when listing specific fields after `SELECT`, using the base table name is more general (for brevity, the following examples use `SELECT *`). Specific examples include:

[0133]

[0134] During actual execution, the index placeholders will be replaced with the actual index values. By replacing the placeholders with different index values, SQL can be executed for different tables after selecting a data source. For example, replacing the index of the placeholder in "orders'[$#(index)#$]'orders" with 1 will result in "ordersl orders".

[0135] After executing the interceptor, the information obtained from executing the interceptor can be used in basic step 2. In steps S21 to S25, if there is sharding and the SQL needs to be rewritten or adjusted, this can be done at this point. No further adjustments are needed in the sharding engine. In sharding cases, it's necessary to determine if the current query involves grouping or aggregation, which may require SQL adjustments. Additionally, if there is pagination in sharding cases, the page numbers also need to be adjusted. Originally, the query started from `start` and retrieved `size` records; it needs to be changed to start from 0 or 1 and retrieve `start + size` records. The actual number of records returned will be adjusted in the sharding engine.

[0136] In module 55, when a query request is initiated, it is necessary to determine whether there is sharding. If there is sharding, the branch of sharding mode is followed.

[0137] Detailed flowchart, such as Figure 6 As shown; the detailed process for initiating a query request through database sharding and table partitioning is as follows:

[0138] S1, determine if there is a fragment;

[0139] Y jumps to S2, N jumps to S5;

[0140] S2 has fragments (S1 is Y).

[0141] Determine if it is a sharded main thread;

[0142] Y jumps to S3, N jumps to S4;

[0143] S3 is the main thread of the shard (S2 is Y).

[0144] Execute steps S3a-S3h:

[0145] S3a checks if there is a cache; if so, it returns; otherwise, it continues execution.

[0146] S3b is sent to the sharding engine for processing, and the sharding engine will execute S3c-S3h.

[0147] S3c retrieves the data source and table index involved;

[0148] In S3d, each database and table index corresponds to one query, which is executed by creating a sub-thread.

[0149] S3e, each sub-thread executes the query operation;

[0150] S3f collects the query results from each sub-thread;

[0151] S3g, adjust the sorting;

[0152] S3h retrieves a specified number of records and returns them.

[0153] S4 is not the main sharding thread (S2 is N), but rather a sharding sub-thread (corresponding to S3e), which initiates database query operations for specific shards.

[0154] S5, without sharding (S1 is N), initiates a query operation to the database and returns the query results.

[0155] The sharding main thread calls the sharding engine. Within the engine, based on the data source and table index (each data source and table index corresponds to one sharding query), a corresponding number of child threads are created to initiate the query operation. The data source name is used to obtain the specific data source to be operated on by the current SQL statement, and the table index replaces the placeholders in the SQL statement's table index (making the initiated current query operation correspond to the specific table name), thus obtaining the SQL to be executed. The query results from multiple child threads are collected and merged, sorted, and a specified number of records (starting from a specified start value) are retrieved as the original query results. The query requests of each child thread are similar to those in a single-database scenario. Many industry tools re-parse and rewrite the SQL within the sharding engine; if pagination is involved, page numbers are typically adjusted at this point. For complex SQL statements, the correctness of the rewritten SQL is difficult to guarantee, and parsing the SQL also degrades performance. Due to the complexity of the parsing process, the types of SQL statements supported are also limited. However, based on the object-oriented structured parsing approach, page numbers can be adjusted when assembling the basic SQL. Therefore, when rewriting SQL for database sharding and table partitioning, the SQL doesn't need to be rewritten for page numbers, improving both accuracy and efficiency. Furthermore, because the designed representation of database sharding and table partitioning SQL is quite reasonable, in most cases, the SQL doesn't need to be rewritten within the sharding engine.

[0156] Example 4: When assembling the query result into the target structure, reflection is not required.

[0157] In the basic steps of object-oriented query results, step S4 typically involves using reflection to obtain the entity's structure and then dynamically setting values ​​for the entity. One problem with this approach is its impact on performance. This embodiment proposes a solution to this problem.

[0158] Reflection is not needed when assembling intermediate results. Assembly code and registration code are automatically generated. The code for assembling query results can be generated during the compilation phase or directly generated to a specified location using another tool. The file location of the code can be set by the user; otherwise, a default location is used. The generated assembly code is then registered to the specified assembler factory class. Users do not need to worry about generating the code used to assemble query results during coding. Before initiating the query in basic step S3, the assembler factory class is checked to see if there is an assembler corresponding to the currently queried entity. If so, another branch is used to initiate the query request. In basic step S4, when assembling the query results, the assembler is directly used to assemble the query results into the target structure and then returned. The key source code is shown in the part of example 4 marked in source code 5. If not found, dynamic assembly is used. When generating the assembler code, pay attention to numeric types. Values ​​stored in the database are null, and values ​​retrieved via JDBC may become 0. Therefore, primitive types like int are not recommended for attributes; wrapper types like Integer should be used instead. When parsing the results, if the value stored in the database is null, then numeric attributes can be set to null. If a query does not retrieve a certain field, then there is no need to set the corresponding attribute for the entity. The automatically generated code should have this compatibility logic. Additionally, there could be an option to override the previous code; if code has already been generated, subsequent generation can process the results based on the settings.

[0159] Example 5: Multi-level related entity query sample code

[0160] Example code for multi-level associated entity queries is shown in source code 6. This example uses a query of five levels of associated entities (province, city, town / district, village, road) as an example (the number of entity levels is 5). The second level has three entities with relationships of 1:n, 1:1, and 1:1 with the entities in the first level, respectively. By adjustment, instances of 2 to 4 levels of associated entities can be obtained. From this embodiment, it can be concluded that the device of the present invention supports 2 to 5 levels of associated entities. Furthermore, starting from the third level, it uses loop logic parsing, so it can be deduced that associated entities with more than 2 levels are supported. However, generally, the more levels of associated entities, the lower the performance; therefore, in practice, for more than 4 levels of associated entities, the design will be readjusted. In the given source code example, a limit of 10 levels has been added.

[0161] The log is as follows:

[0162] The results obtained from the analysis are as follows:

[0163]

[0164] The following are the example results (test data):

[0165] [INFO]|<--(select raw record rows:4)

[0166] [INFO]|<--select rows:3

[0167] [INFO]{\"id\":1002,\"name\":\"Guangdong Province

[0168] \",\"level\":1,\"remark\":null,\"listCity\":[{\"id\":2005,\"name\":\"Shenzhen City

[0169] \",\"level\":2,\"remark\":null,\"provinceId\":1002,\"town\":{\"id\":3003,\"name\":\"Nanshan District

[0170] \",\"level\":3,\"remark\":null,\"cityId\":2005,\"listVillage\":[{\"id\":4001,\"name\":\"Nanyuan Village\",\"level\":4,\"remark\":null,\"townId\":3003}]}},{\"id\":2001,\"name\":\"Guangzhou City

[0171] \",\"level\":2,\"remark\":null,\"provinceId\":1002,\"town\":null}],\"provinceHistory\":{\"id\":1002,\"name\":\"History of Guangdong Province\",\"provinceId\":1002,\"descStr\":\"Guangdong Province has a long history

[0172] \"},\"news\":null}

[0173] [INFO]{\"id\":1004,\"name\":\"Shanghai Municipality\",\"level\":1,\"remark\":\"Municipality directly under the Central Government

[0174] \",\"listCity\":[{\"id\":2004,\"name\":\"Shanghai Municipality\",\"level\":2,\"remark\":\"Municipality directly under the Central Government

[0175] \",\"provinceId\":1004,\"town\":null}],\"provinceHistory\":null,\"news\":null}

[0176] [INFO]{"id": 1003, "name": "Jiangsu Province"}

[0177] ", "level": 1, "remark": null, "listCity": [{"id": 2002, "name": "Nanjing City"]

[0178] ", "level": 2, "remark": null, "provinceId": 1003, "town": null}], "provinceHistory": null, "news": null}

[0179] The device involved in this invention has a buffering function. For example... Figure 5 As shown in module 55, when a query operation is executed, the cache is accessed first. If the cache contains the desired result, the corresponding result is returned directly. If the cache does not contain the desired result, the database is queried and the result is returned. At the same time, the obtained result is stored in the cache.

[0180] Due to the design features of the device involved in this invention, the SQL generated by the table join query can retrieve data from both the master and slave tables, thus avoiding the n+1 query problem.

[0181] Although embodiments of the present invention have been shown and described, they are not intended to limit the scope of this application. Various modifications and variations can be made to this application by those skilled in the art, including different computer languages ​​used in its implementation. Any changes, modifications, substitutions, and alterations to these embodiments without departing from the principles and design concept of the present invention should be included within the scope of the claims of this application.

[0182] Appendix: Source code

[0183] The following is the reference implementation code (Java) provided in the embodiments of the present invention.

[0184] Source code 1: MoreTableStruct3; A data structure for storing metadata information of multi-level related entities.

[0185]

[0186] Source code 2: MoreObjectToSQL._toSelectSQL; parses multi-level related entities and conditions, and assembles them into SQL.

[0187]

[0188]

[0189]

[0190]

[0191]

[0192] Source code 3: ParseSqlHelper.parsejoins; parses multi-level related entities.

[0193]

[0194]

[0195] Source code 4: MoreTableResultWrapper; the structure used to transform query results into intermediate objects.

[0196] Source code 5: SqlLib._moreTableSelect3; initiates a query request, assembling intermediate objects into the target entity structure.

[0197]

[0198]

[0199]

[0200]

[0201]

[0202] Source Code 6: Example Source Code for Multi-Level Related Entity Queries

[0203] < / t> < / t> < / t> < / t> < / t> < / t>

Claims

1. An ORM design method, characterized in that, include: It supports querying multiple levels of related entities and provides accurate pagination for one-to-many related queries; The basic steps are as follows: Step S1, call the SUID interface and pass in the main entity. In complex query scenarios, filter conditions, query fields, grouping, sorting, and page number parameters can be passed in; Step S2, parse the entity and parameters to generate an SQL statement to prevent SQL injection and corresponding placeholder parameter values; Step S3, initiate a query to the database based on the SQL statement and placeholder parameter values ​​and obtain the original query results; Step S4, automatically assemble the original query results into the target entity object and return it. Step S2 is as follows: Step S21: Parse multi-level related entities and obtain metadata information; Step S22: Parse and collect information about the main entity object; Step S23: Process metadata information in a loop, parse and collect sub-entity object information, and associate condition information; Step S24: Determine the fields to be queried; Step S25: Assemble the SQL; Step S4 is as follows: Step S41: First, convert each row of the original query result into intermediate objects: the object of the main entity and the objects of each sub-entity; Step S42: Assemble the intermediate objects obtained in step S41 into the desired entity structure; Step S43: Return the result obtained after processing; The specific assembly method of step S42 is as follows: Receive an intermediate object set generated based on a row of original query results, wherein the intermediate object set contains at least a main entity object; first, obtain the main entity object, then determine whether there are child entity objects; if not, place the main entity object into a list variable used to return the results, and return it; if there are child entity objects, iterate through the child entity objects in the intermediate object set, obtain the association level identifier of the current child entity object, and process it according to the following steps: S42-1, Detect whether it is the second layer; Y jumps to S42-2, N jumps to S42-7; S42-2, Check if the current sub-entity is a List Y jumps to S42-3, N jumps to S42-6; S42-3, Check if the current child object is already in the cache. Y jumps to S42-4, N jumps to S42-5; S42-4, the second-level list exists, but the list has not yet been added with second-level objects. Therefore, it needs to be added to the list type property of the first-level object and placed into two caches: the single object cache and the list type object cache. S42-5, First create a new list and put it into the cache; Then set the list object to the corresponding property of the main object. If the main object has not yet added a list variable for returning results, then add it. S42-6, Set the child object to the main object; put the main object into the cache; If the main object has not yet been placed into the list variable used to return the result, then it will be placed there; S42-7 uses key1 to check if a child object exists in a single object cache; If Y, then continue to check whether the current sub-entity is a List; Y jumps to S42-8, N jumps to S42-11; S42-8, Check if the list sub-objects exist; Y jumps to S42-9, N jumps to S42-10; S42-9: If the list has child objects but the current level objects have not yet been added, then they need to be added to the list of the parent level objects. S42-10: First, create a new list, and then store the current child object into it; Then set the list object to its parent object; store the current child object and the list object in the corresponding cache; S42-11, Find the parent object of the current object and set the current object to its parent object; To achieve accurate pagination in one-to-many join queries, the SQL generated in step S2 can be rewritten if necessary. The specific steps are as follows: Step S2a: Change the select part of the original SQL to only query the primary key of the main table and use DISTINCT to remove duplicates and then create a temporary table. Step S2b: Associate the primary key retrieved from the temporary table with the corresponding primary key in the main table; In step S2c, the SQL obtained from the above two steps is inserted into the main table of the original SQL; the pagination part of the original SQL is no longer retained. The ORM design method automatically identifies multi-level relationships between entities, automatically parses and assembles the results of multi-level relationship queries, and the one-to-many relationship query supports accurate pagination, with pagination results accurately counted by business entity to ensure that the number of paginated objects is consistent with the number of business entities. The ORM layer automatically encapsulates multi-level relationship queries, and accurate pagination can be executed automatically without manually writing SQL. The method supports pagination implementations adapted to different databases.

2. The method according to claim 1, characterized in that, The query function in the database sharding and table partitioning mode is implemented based on the object-oriented structured parsing method, without the need to parse the SQL statement and then rewrite it into SQL with database sharding and table partitioning function. Based on the method described in claim 1, the method is further extended as follows: Between step S1 and step S2, a database sharding and table partitioning parsing interceptor is added; the database sharding and table partitioning parsing interceptor parses the entity and the defined sharding information to determine whether a certain entity should be sharded; Furthermore, only entities with sharding definitions will trigger the execution of the database sharding and table partitioning parsing interceptor; In step S2, when parsing the table name, it is necessary to determine whether there is sharding. If so, the table name used for sharding is used. Specifically, the table name adopts the format: base table name 'table name subscript special placeholder' base table name. When generating SQL, if there is sharding and pagination query, the page number needs to be adjusted. Originally, the query started from start and retrieved size records, but it needs to be changed to start from the first record and retrieve start + size records. If the current query involves grouping or aggregation query, the SQL may also need to be adjusted. In step S3, a query request is sent to the database, which should be adjusted as follows: When initiating a query request, it is necessary to determine whether sharding exists. If sharding exists, the sharding mode branch is executed. The detailed process for initiating a query request based on database sharding and table partitioning is as follows: S1, determine if there is a fragment; Y jumps to S2, N jumps to S5; S2 has fragments (S1 is Y). Determine if it is a sharded main thread; Y jumps to S3, N jumps to S4; S3 is the main thread of the shard (S2 is Y). Execute steps S3a-S3h: S3a checks if there is a cache; if so, it returns; otherwise, it continues execution. S3b is sent to the sharding engine for processing, and the sharding engine will execute S3c-S3h. S3c retrieves the data source and table index involved; In S3d, each database and table index corresponds to one query, which is executed by creating a sub-thread. S3e, each sub-thread executes the query operation; S3f collects the query results from each sub-thread; S3g, adjust the sorting; S3h retrieves a specified number of records and returns them. S4 is not the main thread of the sharding (S2 is N), but a sub-thread of the sharding, which initiates database query operations for specific shards; S5, without sharding (S1 is N), initiates a query operation to the database and returns the query results; In step S3, the sharding main thread calls the sharding engine. In the engine, based on the data source and table index, each data source and table index corresponds to one sharding query. A corresponding number of child threads are created to initiate the query operation. The data source name is used to obtain the specific data source to be operated on by the current SQL. The table index will replace the placeholder of the table index in the SQL statement, thereby obtaining the SQL to be executed. Collect and merge the query results from multiple sub-threads, adjust the sorting, and retrieve a specified number of records (starting from the specified start and retrieving size records) as the original query results; Accurate pagination for multi-level and one-to-many relational queries, supporting use in database sharding and table partitioning modes; If the database sharding and table partitioning mode involves sharding and requires rewriting or adjusting the SQL, it can be adjusted before the first SQL1 is generated. In sharded database and table partitioning mode, if pagination numbers need to be rewritten, they can be adjusted before the first SQL statement is generated. The SQL for generating specific table partitioning functions in sharded database and table partitioning mode is completed during entity parsing. It is based on object-oriented programming, rather than parsing SQL and then rewriting SQL statement, thus saving the performance overhead of parsing SQL statement.

3. The method according to claim 1, characterized in that, The number of associated entity layers supports 2 to 5. The number of related entity layers is determined using the following algorithm: Read the associated configuration information corresponding to the main entity; The associated configuration information is traversed to identify the child entities directly associated with the main entity; After identifying a sub-entity, an initial hierarchy value is set for that sub-entity; Perform recursive parsing on the sub-entity to find the next level sub-entity that the sub-entity is still associated with; When the next level sub-entity is found, the current level value is incremented and the recursive parsing continues; Output the hierarchical relationship information between the main entity and its sub-entities until no further sub-entities exist. The following algorithm can be used to prevent circular dependencies between entities: The data structure used is a list to store the types of entities; When parsing the main entity, if it is found that an attribute of the main entity (level 1) is a child entity (level 2), then the types of both level 1 and level 2 are added to the list; Parse the entities obtained from the second level. When encountering a child entity, first check if it exists in the list. If it does, return it. If it does not exist, its type is added to the list and the child entity is parsed; starting from the second level, the child entities obtained will be parsed in a loop. In addition, when there are two levels of related entities, they are allowed to be of the same type and can be distinguished by table aliases.

4. The method according to claim 1, characterized in that, Accurate pagination for one-to-many join queries is only rewritten when needed, and the rewriting algorithm is as follows: 1) Define and modify the sq1 variable, setting it to true by default; 2) If the user has set not to rewrite, then set the rewrite SQL variable to false; 3) If the modified SQL variable is true, then check... If the following conditions are not met: there is pagination or one-to-many relationship, then set the rewrite SQL variable to false; 4) If the rewrite SQL variable is still true, then determine if one of the following conditions is met, and set the rewrite SQL variable to false. 4.1) Only query data from the main table; 4.2) There are aggregate queries; 4.3) If all primary key settings in the main table have values ​​or a unique constraint column in the main table has a value, it can be determined that at most one record in the main table can be found.

5. The method according to claim 1, characterized in that, The query results can be assembled without relying on reflection. When performing paginated queries, passing 0 as the start parameter and using the boundary ID of the previous query as a filter condition can optimize pagination performance. This can avoid the N+1 query problem; The coding complexity is O(1).

6. An ORM device, characterized in that, The included modules are: object-oriented interface module (module 51), pre-interceptor module (module 52), multi-level related entity object to SQL statement module (module 53), multi-level related entity object parsing module (module 54), query request initiation and raw query result acquisition module (module 55), raw query result to intermediate object module (module 56), intermediate object assembly into the desired entity structure module (module 57), and post-interceptor module; Module 51 is used to receive query requests. The main function of module 52 is to execute interceptors in a certain order. Module 53 converts multi-level related entity objects into SQL statements. Module 54 parses multi-level related entity objects. Module 55 sends a query request to the database. Then, module 56 converts the initial query results into intermediate objects. Module 57 then assembles the intermediate objects into objects of the target entity structure and returns them. The device supports querying multi-level related entities and supports accurate pagination for one-to-many related queries; The basic steps are as follows: Step S1, in module 51, call the SUID interface and pass in the main entity. In complex query scenarios, filter conditions, query fields, grouping, sorting, and page number parameters can be passed in. Step S2: In module 53, the entity and parameters are parsed to generate an SQL statement to prevent SQL injection and corresponding placeholder parameter values; Step S3: In module 55, a query is initiated to the database based on the SQL statement and placeholder parameter values ​​and the original query results are obtained; Step S4: In module 57, the original query results are automatically assembled into a target entity object and returned. Step S2 is as follows: Step S21: Module 53 calls module 54 to parse multi-level related entities and obtain metadata information; Step S22: Parse and collect information about the main entity object; Step S23: Process metadata information in a loop, parse and collect sub-entity object information, and associate condition information; Step S24: Determine the fields to be queried; Step S25: Assemble the SQL; Step S4 is as follows: Step S41: Using module 56, each row of the original query result obtained in module 55 is processed... First, convert them into intermediate objects: the main entity object and the objects of each child entity; Step S42: In module 57, the intermediate objects obtained in step S41 are assembled into the desired entity structure. Step S43: Return the result obtained after processing; The specific assembly method of step S42 is as follows: Receive an intermediate object set generated based on a row of original query results, wherein the intermediate object set contains at least a main entity object; first, obtain the main entity object, then determine whether there are child entity objects; if not, place the main entity object into a list variable used to return the results, and return it; if there are child entity objects, iterate through the child entity objects in the intermediate object set, obtain the association level identifier of the current child entity object, and process it according to the following steps: S42-1, Detect whether it is the second layer; Y jumps to S42-2, N jumps to S42-7; S42-2, Check if the current sub-entity is a List Y jumps to S42-3, N jumps to S42-6; S42-3, Check if the current child object is already in the cache. Y jumps to S42-4, N jumps to S42-5; S42-4, the second-level list exists, but the list has not yet been added with second-level objects. Therefore, it needs to be added to the list type property of the first-level object and placed into two caches: the single object cache and the list type object cache. S42-5, First create a new list and put it into the cache; Then set the list object to the corresponding property of the main object. If the main object has not yet added a list variable for returning results, then add it. S42-6, Set the child object to the main object; put the main object into the cache; If the main object has not yet been placed into the list variable used to return the result, then it will be placed there; S42-7 uses key1 to check if a child object exists in a single object cache; If Y, then continue to check whether the current sub-entity is a List; Y jumps to S42-8, N jumps to S42-11; S42-8, Check if the list sub-objects exist; Y jumps to S42-9, N jumps to S42-10; S42-9: If the list has child objects but the current level objects have not yet been added, then they need to be added to the list of the parent level objects. S42-10: First, create a new list, and then store the current child object into it; Then set the list object to its parent object; store the current child object and the list object in the corresponding cache; S42-11, Find the parent object of the current object and set the current object to its parent object; To achieve accurate pagination in one-to-many join queries, the SQL generated in step S2 can be rewritten if necessary. The specific steps are as follows: Step S2a: Change the select part of the original SQL to only query the primary key of the main table and use DISTINCT to remove duplicates and then create a temporary table. Step S2b: Associate the primary key retrieved from the temporary table with the corresponding primary key in the main table; In step S2c, the SQL obtained from the above two steps is inserted into the main table of the original SQL; the pagination part of the original SQL is no longer retained. The ORM design method automatically identifies multi-level relationships between entities, automatically parses and assembles the results of multi-level relationship queries, and the one-to-many relationship query supports accurate pagination, with pagination results accurately counted by business entity to ensure that the number of paginated objects is consistent with the number of business entities. The ORM layer automatically encapsulates multi-level relationship queries, and accurate pagination can be executed automatically without manually writing SQL. The method supports pagination implementations adapted to different databases.

7. The apparatus according to claim 6, characterized in that, The query function in the database sharding and table partitioning mode is implemented based on the object-oriented structured parsing method, without the need to parse the SQL statement and then rewrite it into SQL with database sharding and table partitioning function. The device described in claim 6 is further extended as follows: In module 52, the database sharding and table parsing interceptor parses the entity and the defined sharding information to determine whether a certain entity should be sharded; and only entities with sharding definitions will trigger the execution of the database sharding and table parsing interceptor. In module 53, when parsing table names, it is necessary to determine whether sharding is required. If so, the table name used for sharding is used. Specifically, the table name adopts the format: base table name 'table name subscript special placeholder' base table name. When generating SQL, if there is sharding and pagination query, the page number needs to be adjusted. Originally, the query started from start and retrieved size records, but it needs to be changed to start from the first record and retrieve start + size records. If the current query involves grouping or aggregation query, the SQL may also need to be adjusted. Module 55 sends a query request to the database; this needs to be adjusted as follows: When initiating a query request, it is necessary to determine whether sharding exists. If sharding exists, the sharding mode branch is executed. The detailed process for initiating a query request based on database sharding and table partitioning is as follows: S1, determine if there is a fragment; Y jumps to S2, N jumps to S5; S2 has fragments (S1 is Y). Determine if it is a sharded main thread; Y jumps to S3, N jumps to S4; S3 is the main thread of the shard (S2 is Y). Execute steps S3a-S3h: S3a checks if there is a cache; if so, it returns; otherwise, it continues execution. S3b is sent to the sharding engine for processing, and the sharding engine will execute S3c-S3h. S3c retrieves the data source and table index involved; In S3d, each database and table index corresponds to one query, which is executed by creating a sub-thread. S3e, each sub-thread executes the query operation; S3f collects the query results from each sub-thread; S3g, adjust the sorting; S3h retrieves a specified number of records and returns them. S4 is not the main thread of the sharding (S2 is N), but a sub-thread of the sharding, which initiates database query operations for specific shards; S5, without sharding (S1 is N), initiates a query operation to the database and returns the query results; In step S3, the sharding main thread calls the sharding engine. In the engine, based on the data source and table index, each data source and table index corresponds to one sharding query. A corresponding number of child threads are created to initiate the query operation. The data source name is used to obtain the specific data source to be operated on by the current SQL. The table index will replace the placeholder of the table index in the SQL statement, thereby obtaining the SQL to be executed. Collect and merge the query results from multiple sub-threads, adjust the sorting, and retrieve a specified number of records (starting from the specified start and retrieving size records) as the original query results; Accurate pagination for multi-level and one-to-many relational queries, supporting use in database sharding and table partitioning modes; If you need to rewrite or adjust SQL in a sharded database and table partitioning mode, you can adjust it before generating SQL for the first time. In the sharding and partitioning mode, if the pagination number needs to be rewritten, it can be adjusted before the first SQL statement is generated. The generation of SQL statements for specific sharding functions in the sharding and partitioning mode is completed during the parsing of entities. It is based on object-oriented programming, rather than parsing and rewriting SQL statements, thus saving the performance overhead of parsing SQL statements.

8. The apparatus according to claim 6, characterized in that, The number of associated entity layers supports 2 to 5. It can prevent circular dependencies between entities; One-to-many relational queries with accurate pagination are only rewritten when needed. The query results can be assembled without relying on reflection. When performing paginated queries, passing 0 as the start parameter and using the boundary ID of the previous query as a filter condition can optimize pagination performance. This can avoid the N+1 query problem; The coding complexity is O(1).

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the method according to any one of claims 1 to 5.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the method described in any one of claims 1 to 5.

Images