A multi-table data query method, system, device and medium based on JPA combination

Abstract

The application provides a multi-table data query method, system, device and medium based on JPA combination, belonging to the technical field of data processing. The method comprises the following steps: in response to the front-end display requirement, determining the data tables to be queried and the association relationship between the data tables; using JPA entity to define the structure of the data tables and the association relationship between the data tables; respectively executing queries on each data table, using Java streaming interface to perform data processing and combination on each query result in the memory; and integrating the combined query result into a data structure that can be parsed by the front end and returning it. Through step-by-step query and data processing, the application reduces the number of database queries, avoids complex multi-table association, improves query efficiency, uses Java streaming interface for data processing and combination, improves the efficiency of data processing, disassembles the query logic, facilitates extension, combines and optimizes multi-table data through JPA, reduces the number of database queries, and shortens the query time.

Description

Technical Field

[0001] This application belongs to the field of data processing technology, specifically relating to a multi-table data query method, system, device, and medium based on JPA. Background Technology

[0002] With the development of big data and cloud computing, data management and processing have become crucial aspects of application performance optimization. Especially in enterprise applications, the database, as the core of data storage and access, directly impacts application response speed and user experience through its query performance. However, with the expansion of business scale and the rapid growth of data, traditional database query methods face significant challenges. The current mainstream approach uses complex multi-table join SQL statements to retrieve all data at once. This query method has the following drawbacks: First, the original SQL contains multiple nested subqueries and complex multi-table join operations, requiring multiple related subqueries to be executed for each supplier record, resulting in excessively long database query times and low efficiency. Second, in practical applications, when the number of suppliers reaches several thousand, this complex query method significantly extends the response time of the business system, severely impacting user experience. Third, complex SQL statements are difficult to understand and modify, increasing the maintenance cost of the business system. Finally, when adding new evaluation metrics, the complex SQL statements must be modified, which is prone to introducing errors.

[0003] While some database query optimization methods have emerged, such as creating indexes and optimizing JOIN operations, these methods have limited effectiveness for complex multi-table join query scenarios and cannot fundamentally solve the performance bottleneck problem. Summary of the Invention

[0004] In a first aspect, embodiments of this application provide a multi-table data query method based on JPA, comprising the following steps: S1. Respond to the front-end display requirements and determine the data tables to be queried and the relationships between the data tables; S2. Use JPA entities to define the structure of data tables and the relationships between data tables; S3. Perform queries on each data table separately, and use the Java streaming interface to process and combine the query results in memory; S4. Integrate the combined query results into a data structure that can be parsed by the front end and return it. By implementing the entire process of multi-table data query based on JPA from requirements analysis to final data return, the efficiency and accuracy of the query are ensured; by decomposing the query process, it is easy to understand and maintain.

[0005] Furthermore, the specific steps of step S1 are as follows: S11. Obtain the front-end display requirements and parse out the fields that need to be displayed; S12. Determine the data table to be queried based on the fields to be displayed; S13. Determine whether there are any related fields between the data tables that need to be queried. By parsing the front-end display requirements, the data tables to be queried and their relationships are determined, ensuring the accuracy and completeness of the requirements analysis; by dynamically parsing the requirements, different front-end display needs can be flexibly addressed, improving the adaptability of business applications.

[0006] Furthermore, the specific steps of step S2 are as follows: S21. Define a JPA entity class for each data table that needs to be queried; S22. Use annotations to establish relationships between corresponding JPA entity classes of related data tables. By defining JPA entity classes and establishing relationships, the accurate definition of the data table structure and relationships is ensured; establishing relationships through annotations makes the code clearer, easier to understand, maintainable, and extensible.

[0007] Furthermore, the specific steps for performing queries on each data table in step S3 are as follows: Use JPQL or Java API to construct query statements based on the fields to be displayed; Execute the query statement to retrieve the data for the fields to be displayed from the corresponding data table. Based on the fields to be displayed, select the appropriate query method to construct the query statement. This allows for optimization of the query for different scenarios, improving query flexibility and efficiency, and ensuring accurate data retrieval.

[0008] Furthermore, the specific steps for constructing query statements using JPQL or Java API based on the fields to be displayed as needed are as follows: Display fields to determine if the following conditions are met: Dynamic query conditions exist; Query statements need to be constructed dynamically; If neither condition is met, use JPQL to construct the query statement; If at least one of the two conditions is met, use the Java API to construct the query statement; The specific steps for executing the query statement and retrieving the data of the fields to be displayed from the corresponding data table are as follows: Use FETCH JOIN to preload associated data with the lazy loading flag; Filter redundant fields using the WHERE clause; Optimize index usage by adding comments to provide suggestions; When the amount of query results exceeds a threshold, pagination is automatically executed. Based on dynamic query conditions and requirements, either JPQL or Java API is used to construct the query statement, improving query flexibility and adaptability; index usage is optimized by adding comments, enhancing query performance.

[0009] Furthermore, the specific steps in step S3 for processing and combining the query results in memory using the Java streaming interface are as follows: Create a thread for each query result; Execute each thread in parallel; Within each thread, the query results are filtered using the `filter` operation, transformed using the `map` operation, and aggregated using the `reduce` operation. By utilizing Java streaming interfaces for data processing and combination, the advantages of multi-core processors are fully utilized, improving data processing efficiency; and by executing threads in parallel, data processing speed is increased.

[0010] Furthermore, the specific steps of step S4 are as follows: S41. Integrate the combined query results into a Map structure in the form of key-value pairs according to a preset method; S42. The query results in the Map structure are grouped according to the front-end display requirements and then converted into JSON format. The combined query results are integrated into a data structure that the front-end can parse and returned, ensuring the integrity and accuracy of the data. By using pagination and lazy loading techniques, large amounts of data are avoided from being loaded at once, reducing front-end waiting time and improving the user experience.

[0011] Secondly, embodiments of this application also provide a multi-table data query system based on JPA, including: The data table and relationship determination module is used to respond to front-end display requirements and determine the data tables to be queried and the relationships between the data tables. The entity definition module is used to define the structure of data tables and the relationships between data tables using JPA entities; The data query module is used to execute queries on each data table separately, and uses Java streaming interfaces to process and combine the query results in memory. The data query results return module is used to integrate the combined query results into a data structure that can be parsed by the front end and return it. Through the interaction of the data table and relationship determination module, entity definition module, data query module, and data query results return module, it realizes the entire process of multi-table data query based on JPA, from requirements analysis to final data return.

[0012] Thirdly, embodiments of this application also provide an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of the multi-table data query method based on JPA combination as described in the first aspect.

[0013] Fourthly, embodiments of this application also provide a storage medium storing a computer program thereon, which, when executed by a processor, implements the steps of the multi-table data query method based on JPA combination as described in the first aspect.

[0014] As can be seen from the above technical solutions, this application has the following advantages: The multi-table data query method, system, device, and medium based on JPA provided in this application reduce the number of database queries and avoid complex multi-table joins by using step-by-step querying and data processing, thereby improving query efficiency. It utilizes Java streaming interfaces for data processing and combination, fully leveraging the advantages of multi-core processors to improve data processing efficiency. Complex query logic is decomposed into multiple steps, facilitating maintenance and expansion. By combining and optimizing multi-table data through JPA, the number of database queries is reduced, data retrieval efficiency is improved, and query time is shortened, effectively solving the problems of low efficiency and complex queries in traditional multi-table data queries. Pagination and lazy loading avoid loading large amounts of data at once, reducing front-end waiting time and improving user experience. By dynamically constructing query statements and processing logic, it flexibly responds to different query needs and adapts to rapid business changes.

[0015] This application replaces multiple independent queries with a single JOIN query, reducing database CPU and IO pressure; this application greatly shortens front-end waiting time; the dynamic JOIN strategy supports flexible adaptation to changes in business requirements without the need for code refactoring. Attached Figure Description

[0016] To more clearly illustrate the technical solution of this application, the accompanying drawings used in the description will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0017] Figure 1 This is a flowchart illustrating the multi-table data query method based on JPA combination according to the present invention.

[0018] Figure 2 This is a schematic diagram of the multi-table data query system based on JPA combination according to the present invention. Detailed Implementation

[0019] The various embodiments of this disclosure will be described more fully in the detailed steps of the multi-table data query method based on JPA combination described below. This disclosure may have various embodiments, and adjustments and changes may be made therein. However, it should be understood that there is no intention to limit the various embodiments of this disclosure to the specific embodiments disclosed herein, but rather this disclosure should be understood to cover all adjustments, equivalents, and / or alternatives falling within the spirit and scope of the various embodiments of this disclosure.

[0020] For example, with the development of big data and cloud computing, data management and processing have become crucial for optimizing application performance. Especially in enterprise applications, databases, as the core hub for data storage and access, directly determine application response speed and user experience through their query performance. However, with the continuous expansion of business scale and the explosive growth of data volume, traditional database query models are facing unprecedented challenges.

[0021] Currently, the mainstream data acquisition method in the industry is to use complex multi-table join SQL statements. Figure 1 Retrieving all necessary data at once. However, this approach has several drawbacks. First, the original SQL statements contain numerous nested subqueries and complex multi-table joins. Taking supplier data queries as an example, each supplier record requires executing multiple related subqueries, significantly increasing database query time and resulting in extremely low efficiency. Second, in real-world business scenarios, once the number of suppliers climbs to several thousand, this complex query method will significantly lengthen the response time of the business system, severely damaging the user experience. Third, the complex SQL statements are obscure and difficult to modify, undoubtedly increasing the maintenance cost of the business system. Fourth, adding new evaluation metrics requires modifying the complex SQL statements, a process that is highly susceptible to introducing new errors.

[0022] Although some database query optimization techniques have emerged, such as creating indexes and optimizing JOIN operations, their optimization effects are relatively limited when faced with complex multi-table join query scenarios, and they cannot fundamentally break through performance bottlenecks.

[0023] To address the aforementioned issues, this embodiment provides a multi-table data query method based on JPA combination. By performing step-by-step queries, data processing, and combination, the efficiency and performance of multi-table queries are improved.

[0024] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0025] Please see Figure 1 The diagram shows a flowchart of a multi-table data query method based on JPA in a specific embodiment. The method includes the following steps: S1. Respond to the front-end display requirements and determine the data tables to be queried and the relationships between the data tables; In practical applications, front-end display requirements can be very diverse, including not only simple field displays but also complex statistical information and related data. For example, in an e-commerce platform, the front-end may need to display the user's order information, as well as detailed product information, order status, payment information, etc. for each order. To meet these requirements, we need to perform in-depth analysis of the front-end requests and extract all relevant fields and relationships. It should be noted that by defining the query scope, unnecessary data queries and processing are avoided, reducing the database load and improving query efficiency; by establishing the relationships, a foundation is laid for subsequent data integration, ensuring the integrity and accuracy of the data. S2. Use JPA entities to define the structure of data tables and the relationships between data tables; When defining JPA entity classes, in addition to basic fields and relationships, JPA annotations can be used to optimize query performance. For example, using `@Fetch(FetchMode.JOIN)` can optimize the loading strategy for related data, reducing the number of database queries. Furthermore, the `@Index` annotation can add indexes to fields, further improving query efficiency. It should be noted that by using the JPA standard specification to map database tables to object-oriented entity classes, the complexity of database operations is simplified, the code structure is clearer, and development and maintenance are easier; at the same time, JPA's relationship definition can achieve consistency maintenance and improve query performance. S3. Perform queries on each data table separately, and use the Java streaming interface to process and combine the query results in memory; When executing a query, you can set pagination parameters such as setFirstResult and setMaxResults using JPA's TypedQuery to achieve physical pagination. In addition, for processing large amounts of data, you can use Java 8's Stream API for secondary pagination to further optimize memory usage and response speed. It should be noted that by querying data from each table separately, the required information can be obtained in a targeted manner, reducing data redundancy; the use of Java streaming interfaces provides a simple and efficient way of data processing, supports parallel processing, and can quickly filter, transform and aggregate query results, improve data processing efficiency, and enhance the concurrency performance of applications; S4. Integrate the combined query results into a data structure that can be parsed by the front end and return it; When integrating data into a structure that can be parsed by the front end, a JSON library (such as Gson or Jackson) can be used to convert the data into JSON format. In addition, to further improve the user experience, pagination information, such as the total number of records and the total number of pages, can be added to the returned data. It should be noted that by integrating the data into a structure that is easy for the front end to parse, such as JSON format, it is convenient for the front end to display and use, thereby improving the usability of the data and the user experience. At the same time, by pre-integrating and transforming the data, the data processing pressure on the front end is reduced, and the overall performance of the business system is improved.

[0026] This embodiment demonstrates the entire process of multi-table data query based on JPA, from requirements analysis to final data return, ensuring high efficiency and accuracy. By decomposing the query process, it facilitates understanding and maintenance.

[0027] Furthermore, as a refinement and extension of the specific implementation of the above embodiments, in order to fully illustrate the specific implementation process in this embodiment, another multi-table data query method based on JPA is provided, which includes the following steps: S1. Responding to front-end display requirements, determine the data tables to be queried and the relationships between them; the specific steps of S1 are as follows: S11. Obtain the front-end display requirements and parse out the fields that need to be displayed; For example, the front end needs to display all of the user's orders and their associated product information; parse the request and extract the fields to be displayed: orderId, orderDate, productId, productName; S12. Determine the data table to be queried based on the fields to be displayed; For example, based on the field information, determine the data tables to be queried: the Order table and the Product table; S13. Determine whether there is a relationship between the data tables that need to be queried, based on whether there are related fields between them; For example, determine that there is a related field productId between the Order table and the Product table, and establish the relationship; It should be noted that by analyzing the front-end display requirements, the data tables to be queried and their relationships were determined, ensuring the accuracy and completeness of the requirements analysis; by dynamically analyzing the requirements, different front-end display needs could be flexibly addressed, improving the adaptability of business applications. S2. Use JPA entities to define the structure of the data tables and the relationships between them; the specific steps in step S2 are as follows: S21. Define a JPA entity class for each data table that needs to be queried; For example, define Order and Product entity classes; S22. Use annotations to establish relationships between corresponding JPA entity classes of related data tables; Specifically, relationships include @OneToOne, @OneToMany, @ManyToOne, and @ManyToMany. For example, the @OneToMany and @ManyToOne annotations are used to establish a relationship; It should be noted that by defining JPA entity classes and establishing relationships, the accurate definition of the data table structure and relationships is ensured; by establishing relationships through annotations, the code becomes clearer and easier to understand, and easier to maintain and extend. S3. Execute queries on each data table separately, and use the Java streaming interface to process and combine the query results in memory; the specific steps for executing queries on each data table in step S3 are as follows: Use JPQL or Java API to construct query statements based on the fields to be displayed; Specifically, determine whether dynamic query conditions or dynamic construction of query statements are needed; Execute the query statement to retrieve the data of the fields to be displayed from the corresponding data table; It should be noted that by selecting the appropriate query method based on the displayed fields to construct the query statement, the query can be optimized for different scenarios, improving the flexibility and efficiency of the query and ensuring the accuracy of data acquisition. Specifically, construct JPQL query statements to ensure that only necessary fields are queried; use the Criteria API to construct dynamic queries, dynamically generating query statements based on query conditions; The specific steps for constructing query statements using JPQL or Java API to display the fields as needed are as follows: Display fields to determine if the following conditions are met: Dynamic query conditions exist; Query statements need to be constructed dynamically; If neither condition is met, use JPQL to construct the query statement; If at least one of the two conditions is met, use the Java API to construct the query statement; It should be noted that the choice between using JPQL and the Criteria API to construct query statements depends on the specific business requirements and development scenario. In practical applications, one can choose to use one of them based on the complexity and flexibility requirements of the query, or use them separately in different scenarios. Specifically, JPQL is a SQL-like query language used to manipulate JPA entities and the relationships between them. JPQL provides a declarative query approach, suitable for scenarios with relatively fixed and simple query logic. JPQL query statements are similar to SQL, making them easy to understand and maintain, and highly readable. JPQL checks the syntax of query statements and the properties of entity classes at compile time. For simple query logic, JPQL can be implemented quickly. Suitable scenarios for JPQL query statements include: firstly, query logic that is relatively fixed and does not require dynamic generation of query statements; and secondly, query statements that are relatively simple and do not involve complex dynamic conditions. Taking an e-commerce platform as an example, if you need to query all of a user's orders and their associated product information, you can use JPQL to achieve the following: String jpql = "SELECT o, p FROM Order o JOIN o.products p WHEREo.userId = :userId"; TypedQuery<Object[]> query = entityManager.createQuery(jpql, Object[].class); query.setParameter("userId", userId); List<Object[]> results = query.getResultList(); In this example, Order is the order entity class, and products are the association between orders and products. Through JPQL, we can retrieve all of a user's orders and their associated product information in a single query, avoiding multiple database operations. The Criteria API is a query building method based on the Java API that provides the ability to programmatically construct query statements. It is suitable for scenarios with complex query logic that require dynamic generation of query statements. The Criteria API can dynamically construct query statements and generate different query logic based on different query conditions. It checks the syntax of the query statements and the properties of entity classes at compile time. For complex query logic, especially in scenarios with many dynamic conditions, the Criteria API is more flexible.

[0028] Criteria API is suitable for scenarios where: the query logic is complex and needs to be dynamically generated based on different conditions; there are many query conditions and multiple combinations of these conditions. Similarly, for an e-commerce platform that needs to dynamically query orders based on different conditions, the CriteriaAPI can be used to achieve the following: CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery <order>cq = cb.createQuery(Order.class); Root <order>order = cq.from(Order.class); Predicate[] predicates = new Predicate[2]; predicates[0] = cb.equal(order.get("userId"), userId); predicates[1] = cb.greaterThan(order.get("orderDate"),dateThreshold); cq.where(predicates); TypedQuery <order>query = entityManager.createQuery(cq); List <order>results = query.getResultList(); In this example, a query is dynamically constructed using the Criteria API to retrieve orders based on user ID and order date threshold; CriteriaBuilder is used to construct the query conditions, CriteriaQuery is used to define the query logic, and Root represents the root entity class of the query. In actual development, you can choose to use JPQL or Criteria API depending on your specific business needs: If the query logic is relatively fixed and simple, JPQL is recommended. JPQL's syntax is similar to SQL, making it easy to understand and maintain, and suitable for quickly implementing simple query logic.

[0029] If the query logic is complex and requires dynamic generation of query statements, it is recommended to use the Criteria API; the Criteria API provides flexibility and can dynamically construct query statements based on different conditions, making it suitable for handling complex query logic. In real-world projects, JPQL and the Criteria API can be used separately for different query requirements and scenarios; for example: For simple queries, such as retrieving user information by user ID, JPQL can be used; For complex queries, such as dynamically retrieving orders based on multiple conditions, the Criteria API can be used. By appropriately selecting and combining JPQL and Criteria API, their respective advantages can be fully utilized to improve development efficiency and query performance. The specific steps for executing the query statement and retrieving the data of the fields to be displayed from the corresponding data table are as follows: Use FETCH JOIN to preload associated data with the lazy loading flag; Besides using FETCH JOIN to preload related data, in some special scenarios, caching mechanisms can be used to further optimize the data retrieval process based on the real-time requirements of the business and the frequency of data access. For example, for related data that is frequently accessed but does not change frequently, the data obtained by FETCH JOIN can be stored in a local cache (such as using caching frameworks like Guava Cache or Caffeine). In this way, when subsequent identical query requests arrive, the data is first retrieved from the cache. If the cache is hit, the data is returned directly without querying the database again, greatly reducing the pressure on the database and the query time. For the cache update strategy, you can set up timed updates or actively update the cache when the data changes to ensure the accuracy of the data. It should be noted that FETCH JOIN is used to preload related data with the lazy loading flag, ensuring that the related information is obtained together during the query, thus avoiding multiple database queries; Filter redundant fields using the WHERE clause; In real-world business scenarios, data security is paramount. Therefore, when using the WHERE clause to filter redundant fields, it's essential to combine it with data access control. For instance, different user roles may have different data viewing permissions; administrators might view all fields, while regular users might only be able to view certain fields. By adding permission checks to the WHERE clause, not only can redundant fields be filtered out, but data security can also be ensured. For example, in an enterprise resource management system, the employee table contains employee salary information. When a regular employee queries the employee list, the WHERE clause can restrict their access to the salary field, thus protecting sensitive information. Simultaneously, this approach also helps reduce data transmission volume and improve system response speed, as unnecessary sensitive data is not transmitted over the network. It should be noted that the WHERE clause is used to filter data, removing unnecessary and redundant fields and reducing the amount of data transmitted. Optimize index usage by adding comments to provide suggestions; It should be noted that adding comments can be used to prompt the database to optimize index usage. For example, in JPQL, you can use / *+ INDEX_ASC(table column) * / to force the use of indexes and improve query speed. When the amount of query results exceeds the threshold, pagination will be executed automatically. Specifically, physical pagination is implemented using JPQL's setFirstResult() and setMaxResults() methods; fetch=FetchType.LAZY is set in the JPA entity relationship to load related data only when needed; secondary pagination of in-memory data is performed using Java8Stream's skip() and limit() methods; and the JPQL hint / *+ INDEX_ASC(table column) * / is used to force the use of an index. When performing paginated queries, in addition to using JPQL's setFirstResult() and setMaxResults() methods for physical pagination, and Java 8 Stream's skip() and limit() methods for secondary pagination of in-memory data, database cursors can also be used. Cursors are a mechanism provided by the database system for traversing query result sets. By using cursors, data can be retrieved row by row instead of loading all data at once, thereby reducing memory usage. Specifically, it should be noted that by selecting to use JPQL or Java API to construct query statements based on dynamic query conditions and requirements, the flexibility and adaptability of queries are improved; and by adding comments to optimize index usage, query performance is enhanced. The specific steps in step S3, which use Java streaming interfaces to process and combine the query results in memory, are as follows: Create a thread for each query result; Execute each thread in parallel; In each thread, the query results are filtered using the filter operation, transformed using the map operation, and aggregated using the reduce operation. Specifically, independent threads are created for each query result, enabling multiple threads to execute in parallel and fully leveraging the performance advantages of multi-core processors. Within each thread, the Java streaming interface's filter operation is used for data filtering, map operation for data transformation, and reduce operation for data aggregation, efficiently processing and combining query results. By using the Java streaming interface for data processing and combination, the efficiency and speed of data processing are improved. It should be noted that by using Java streaming interfaces for data processing and combination, the advantages of multi-core processors are fully utilized, thus improving data processing efficiency; and by executing threads in parallel, the speed of data processing is increased. S4. Integrate the combined query results into a data structure that can be parsed by the front end and return it; The specific steps of step S4 are as follows: S41. Integrate the combined query results into a Map structure in the form of key-value pairs according to a preset method; S42. After grouping the query results of the Map structure according to the front-end display requirements, convert them into JSON format; It should be noted that by integrating the combined query results into a data structure that the front-end can parse and returning them, the integrity and accuracy of the data are ensured. By using pagination and lazy loading, large amounts of data are avoided from being loaded at once, reducing the front-end's waiting time and improving the user experience.

[0030] Taking a large enterprise's supply chain management system as an example, this involves querying comprehensive supplier information, including basic supplier information, number of recent transaction records, average transaction amount, and transaction rating. A comparison between the traditional query method and the query method described in this application is as follows: Traditional query method: Uses complex multi-table join SQL statements to retrieve all data at once. This involves three tables: supplier, transaction, and evaluation.

[0031] The company has 1,000 suppliers, with an average of 100 transaction records and 1 review record per supplier. When executing this query directly in the database, due to multiple nested subqueries, the database needs to perform multiple subquery operations for each supplier record. Testing showed that the query took 5 seconds. As the number of suppliers increases, the query time will further increase, severely impacting system response speed. Furthermore, adding a new query field, such as the time of the most recent transaction, requires modifying this complex SQL statement, which is not only cumbersome but also prone to introducing errors.

[0032] The query method in this application is a multi-table data query method based on JPA. Analyze the front-end requirements: It is determined that basic information needs to be obtained from the supplier table, the number of transactions and the average transaction amount from the transaction record table, and the rating from the rating table. It is also determined that the three tables are linked by supplier_id.

[0033] Define JPA entities and relationships: Define three JPA entity classes: Supplier, Transaction, and Evaluation. In the Supplier entity class, establish association relationships with Transaction and Evaluation through annotations such as @OneToMany and @OneToOne.

[0034] Optimize query logic: Based on the requirements, the query logic is relatively fixed, and JPQL is used to construct the query statements. For example, to query basic supplier information and the number of transaction records: JPQL is also used for the average transaction amount. Regarding the rating levels: Use FETCH JOIN to preload related data, filter redundant fields through the WHERE clause, and add comments to optimize index usage.

[0035] Use Java streaming interfaces to process and combine query results in memory. For example, match and combine three query results based on supplier_id.

[0036] The combined data is converted into JSON format and returned to the front end.

[0037] Testing showed that using the query method described in this application, the total time for querying and data processing was only 1 second when handling the same amount of data. As the number of suppliers increases, the response speed of the business application is minimally affected by reasonable pagination and lazy loading. Furthermore, if new query fields need to be added, only simple modifications are required in the JPA query and data processing stages, without significantly altering the overall code structure, thus reducing maintenance costs.

[0038] This example clearly demonstrates that the multi-table data query method based on JPA in this application has a significant performance improvement over traditional query methods, while also having obvious advantages in maintainability and flexibility.

[0039] This application replaces multiple independent queries with a single JOIN query, reducing database CPU and IO pressure; this application greatly shortens front-end waiting time; the dynamic JOIN strategy supports flexible adaptation to changes in business requirements without the need for code refactoring.

[0040] This application effectively solves the performance bottleneck of the original SQL by breaking down complex SQL into multiple simple queries and in-memory computation. It is particularly suitable for complex report-type queries, scenarios requiring multiple calculations and aggregations, and situations with moderate data volume (that can fit into memory). By embodying the approach of "trading space for time" and "separate queries", it is an optimization mode for Java to handle complex data queries.

[0041] It should be understood that the sequence number of each step in the above embodiments does not imply the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present invention.

[0042] like Figure 2 As shown, the following are embodiments of a multi-table data query system based on JPA combination provided in this disclosure. This system and the multi-table data query method based on JPA combination in the above embodiments belong to the same inventive concept. For details not described in detail in the embodiments of the multi-table data query system based on JPA combination, please refer to the embodiments of the multi-table data query method based on JPA combination described above.

[0043] The system includes: The data table and relationship determination module is used to respond to front-end display requirements and determine the data tables to be queried and the relationships between the data tables. The entity definition module is used to define the structure of data tables and the relationships between data tables using JPA entities; The data query module is used to execute queries on each data table separately, and uses Java streaming interfaces to process and combine the query results in memory. The data query result return module is used to integrate the combined query results into a data structure that can be parsed by the front end and then return it.

[0044] This embodiment realizes the entire process of multi-table data query based on JPA combination, from requirements analysis to final data return, through the interaction of the data table and relationship determination module, entity definition module, data query module, and data query result return module.

[0045] The multi-table data query method based on JPA combination provided in this application embodiment can be applied to electronic devices. Those skilled in the art will understand that the electronic device structure involved in the embodiments of this invention does not constitute a limitation on the electronic device. An electronic device may include more or fewer components than illustrated, or combine certain components, or have different component arrangements. In the embodiments of this invention, electronic devices include, but are not limited to, laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. Electronic devices may also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices, and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the embodiments of this application described and / or claimed herein.

[0046] Electronic devices may include processors, external memory interfaces, internal memory, universal serial bus (USB) interfaces, charging management modules, power management modules, batteries, wireless communication modules, audio modules, speakers, microphones, sensor modules, buttons, cameras, displays, and SIM card interfaces, etc.

[0047] It is understood that the structures illustrated in the embodiments of this application do not constitute a specific limitation on the electronic device. In other embodiments of this application, the electronic device may include more or fewer components than illustrated, or combine some components, or split some components, or have different component arrangements. The illustrated components may be implemented in hardware, software, or a combination of software and hardware.

[0048] A processor may include one or more processing units, such as: a central processing unit (CPU), an application processor (AP), a modem processor, a graphics processing unit (GPU), an image signal processor (ISP), a controller, memory, a video codec, a digital signal processor (DSP), a baseband processor, and / or a neural network processing unit (NPU). Different processing units may be independent devices or integrated into one or more processors.

[0049] The processor can serve as the nerve center and command center of an electronic device. The controller can generate operation control signals based on the instruction opcode and timing signals to control the fetching and execution of instructions.

[0050] The processor may also include memory for storing instructions and data. In some embodiments, the memory in the processor is a cache memory. This memory can store instructions or data that the processor has just used or that are used repeatedly. If the processor needs to use the instruction or data again, it can retrieve it directly from this memory. This avoids repeated accesses, reduces processor latency, and thus improves system efficiency.

[0051] The aforementioned electronic device implements the multi-table data query method based on JPA in this application, responding to front-end display requirements by determining the data tables to be queried and the relationships between them; defining the structure of the data tables and the relationships between them using JPA entities; executing queries on each data table separately; processing and combining the query results in memory using Java streaming interfaces; and integrating the combined query results into a data structure that the front-end can parse and return. This achieves the following technical solutions: reducing the number of database queries and avoiding complex multi-table joins through step-by-step querying and data processing; fully utilizing the advantages of multi-core processors to improve data processing efficiency by using Java streaming interfaces for data processing and combination; decomposing complex query logic into multiple steps for easier maintenance and expansion; reducing the number of database queries and improving data acquisition efficiency through JPA combination and optimization of multi-table data, thereby shortening query time and effectively solving the problems of low efficiency and complex queries in traditional multi-table data queries; avoiding loading large amounts of data at once through pagination and lazy loading, reducing front-end waiting time and improving user experience; and flexibly responding to different query requirements and adapting to rapid business changes by dynamically constructing query statements and processing logic.

[0052] The storage medium provided in this application stores a program product capable of implementing a multi-table data query method based on JPA.

[0053] The multi-table data query method based on JPA includes: responding to front-end display requirements, determining the data tables to be queried and the relationships between the data tables; using JPA entities to define the structure of the data tables and the relationships between the data tables; executing queries on each data table separately, using Java streaming interfaces to process and combine the query results in memory; and integrating the combined query results into a data structure that can be parsed by the front-end and returning it.

[0054] In some possible implementations, the JPA-based multi-table data query method of this disclosure can be implemented as a program product including program code that, when the program product is run on a terminal device, causes the terminal device to perform the steps described in the "Exemplary Methods" section of this specification according to various exemplary embodiments of this disclosure.

[0055] The storage medium disclosed herein may be any combination of one or more readable media. A readable medium may be a readable signal medium or a readable storage medium. A readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples (a non-exhaustive list) of readable storage media include: an electrical connection having one or more wires, a portable disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage device, magnetic storage device, or any suitable combination thereof.

[0056] The above description of the disclosed embodiments enables those skilled in the art to make or use the invention. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the invention. Therefore, the invention is not to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.< / order> < / order> < / order> < / order>

Claims

1. A multi-table data query method based on JPA combination, characterized in that, Includes the following steps: S1. Respond to the front-end display requirements and determine the data tables to be queried and the relationships between the data tables; S2. Use JPA entities to define the structure of data tables and the relationships between data tables; S3. Execute queries on each data table separately, and use the Java streaming interface to process and combine the query results in memory. The specific steps in step S3 of using the Java streaming interface to process and combine the query results in memory are as follows: Create a thread for each query result; Execute each thread in parallel; In each thread, the query results are filtered using the filter operation, transformed using the map operation, and aggregated using the reduce operation. S4. Integrate the combined query results into a data structure that can be parsed by the front end and return it.

2. The multi-table data query method based on JPA combination according to claim 1, characterized in that, The specific steps of step S1 are as follows: S11. Obtain the front-end display requirements and parse out the fields that need to be displayed; S12. Determine the data table to be queried based on the fields to be displayed; S13. Determine whether there is a relationship between the data tables that need to be queried, based on whether there are related fields between them.

3. The multi-table data query method based on JPA combination according to claim 1, characterized in that, The specific steps of step S2 are as follows: S21. Define a JPA entity class for each data table that needs to be queried; S22. Use annotations to establish relationships between corresponding JPA entity classes of related data tables.

4. The multi-table data query method based on JPA combination according to claim 2, characterized in that, The specific steps for performing queries on each data table in step S3 are as follows: Use JPQL or Java API to construct query statements based on the fields to be displayed; Execute the query statement to retrieve the data of the fields to be displayed from the corresponding data table.

5. The multi-table data query method based on JPA combination according to claim 4, characterized in that, The specific steps for constructing query statements using JPQL or Java API to display the fields as needed are as follows: Display fields to determine if the following conditions are met: Dynamic query conditions exist; Query statements need to be constructed dynamically; If neither condition is met, use JPQL to construct the query statement; If at least one of the two conditions is met, use the Java API to construct the query statement; The specific steps for executing the query statement and retrieving the data of the fields to be displayed from the corresponding data table are as follows: Use FETCH JOIN to preload associated data with the lazy loading flag; Filter redundant fields using the WHERE clause; Optimize index usage by adding comments to provide suggestions; When the amount of query results exceeds the threshold, pagination will be executed automatically.

6. The multi-table data query method based on JPA combination according to claim 1, characterized in that, The specific steps of step S4 are as follows: S41. Integrate the combined query results into a Map structure in the form of key-value pairs according to a preset method; S42. After grouping the query results of the Map structure according to the front-end display requirements, convert them into JSON format.

7. A multi-table data query system based on JPA, executing the method described in any one of claims 1-6, characterized in that, include: The data table and relationship determination module is used to respond to front-end display requirements and determine the data tables to be queried and the relationships between the data tables. The entity definition module is used to define the structure of data tables and the relationships between data tables using JPA entities; The data query module is used to execute queries on each data table separately, and uses Java streaming interfaces to process and combine the query results in memory. The data query result return module is used to integrate the combined query results into a data structure that can be parsed by the front end and then return it.

8. An electronic device, characterized in that, The method includes 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 steps of the multi-table data query method based on JPA combination as described in any one of claims 1 to 6.

9. A storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the steps of the multi-table data query method based on JPA combination as described in any one of claims 1 to 6.

Images