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

Abstract

The invention provides a multi-table data query method, system and device based on JPA combination and a medium, and belongs to the technical field of data processing.The method comprises the following steps that in response to a front-end display requirement, data tables needing to be queried and the incidence relation between the data tables are determined; a JPA entity is used for defining the structures of the data tables and the incidence relation between the data tables; performing query on each data table, and performing data processing and combination on each query result in a memory by using a Java streaming interface; and integrating the combined query result into a data structure which can be analyzed by the front end and returning the data structure. Through step-by-step query and data processing, the database query frequency is reduced, complex multi-table joint query is avoided, and the query efficiency is improved; a Java streaming interface is used for data processing and combination, so that the data processing efficiency is improved; query logic is disassembled, so that expansion is facilitated; the multi-table data is combined and optimized through the JPA, the database query frequency is reduced, and the query time is shortened.

Description

TECHNICAL FIELD

[0001] The application belongs to the technical field of data processing, and particularly relates to a multi-table data query method, system, device and medium based on JPA combination. BACKGROUND

[0002] With the development of big data and cloud computing, data management and processing have become an important part of application performance optimization. Especially in enterprise-level applications, the database as the core of data storage and access, its query performance is directly related to the response speed of the application and user experience. However, with the expansion of business scale and the rapid growth of data, the traditional database query method is facing great challenges. The current mainstream method uses a complex multi-table query SQL statement to obtain all data at once. This query method has the following defects: first, the original SQL contains multiple nested subqueries and complex multi-table connection operations, and each supplier record needs to execute multiple related subqueries, which leads to long database query time and low efficiency. Secondly, in actual application, when the number of suppliers reaches several thousand, this complex query method will significantly prolong the response time of the business system, seriously affecting the user experience. Thirdly, complex SQL statements are difficult to understand and modify, increasing the maintenance cost of the business system. Finally, when new evaluation indicators need to be added, the complex SQL statement must be modified, which is easy to introduce errors.

[0003] At present, although some database query optimization methods appear, such as establishing index, optimizing JOIN operation, etc., for such complex multi-table association query scene, the effect of these query optimization methods is limited, and the performance bottleneck problem cannot be fundamentally solved. SUMMARY

[0004] In a first aspect, the embodiments of the application provide a multi-table data query method based on JPA combination, comprising the following steps: S1. In response to the front-end display requirement, determine the data tables to be queried and the association relationship between the data tables; S2. Use JPA entity to define the structure of the data tables and the association relationship between the data tables; S3. Execute queries on each data table respectively, and use 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. Through the whole process of multi-table data query based on JPA combination from requirement analysis to final data return, the efficiency and accuracy of the query are ensured; by decomposing the query process, it is convenient for understanding and maintenance.

[0005] Further, step S1 specifically comprises the following steps: S11. Obtain the front-end display requirement and parse the fields to be displayed; S12. Determine the data table to be queried according to the field to be displayed; S13. Determine whether there is an association relationship between the data tables to be queried according to whether there is an association field between the data tables to be queried. By analyzing the front-end display requirements, the data table to be queried and the association relationship are determined to ensure the accuracy and completeness of the requirement analysis. By dynamically analyzing the requirements, different front-end display requirements are flexibly responded to, and the adaptability of the business application is improved.

[0006] Further, the step S2 comprises the following specific steps: S21. Define a JPA entity class for each data table to be queried; S22. Establish the association relationship between the corresponding JPA entity classes of the data tables with the association relationship by using annotations. By defining the JPA entity class and establishing the association relationship, the accurate definition of the data table structure and the association relationship is ensured. By establishing the association relationship through annotations, the code is clearer and easier to understand, which is convenient for maintenance and extension.

[0007] Further, the step S3 of performing the query on each data table respectively comprises the following specific steps: Use JPQL or Java API to build a query statement according to the field to be displayed; Execute the query statement to obtain the data of the field to be displayed from the corresponding data table to be queried. According to the display field, the appropriate query method is selected to build the query statement, which can optimize the query for different scenarios, improve the flexibility and efficiency of the query, and ensure the accuracy of data acquisition.

[0008] Further, the step of using JPQL or Java API to build a query statement according to the field to be displayed comprises the following specific steps: Determine whether the following conditions are met according to the field to be displayed: There is a dynamic query condition; The query statement needs to be dynamically built; If neither of the above conditions is met, use JPQL to build the query statement; If at least one of the above conditions is met, use Java API to build the query statement; The step of executing the query statement to obtain the data of the field to be displayed from the corresponding data table to be queried comprises the following specific steps: Use FETCH JOIN to pre-load the associated data with the lazy loading flag; Filter the redundant fields through the WHERE clause; Optimize the index usage by adding annotations for prompt; When the data volume of query results exceeds a threshold, a paging query is automatically performed. According to dynamic query conditions and requirements, a query statement is constructed using JPQL or a Java API, improving the flexibility and adaptability of the query; by adding annotations to optimize index usage, the query performance is improved.

[0009] Further, the specific steps of using the Java streaming interface to process and combine the query results in memory in step S3 are as follows: threads are created for the query results; the threads are executed in parallel; In each thread, data filtering is performed using a filter operation, data conversion is performed using a map operation, and data aggregation is performed using a reduce operation. By using the Java streaming interface for data processing and combination, the advantages of multi-core processors are fully utilized, improving the efficiency of data processing; by executing the threads in parallel, the speed of data processing is improved.

[0010] Further, the specific steps of step S4 are as follows: S41. The combined query results are integrated into a Map structure in the form of key-value pairs according to a preset manner; S42. The query results of 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 can be parsed by the front end and returned, ensuring the completeness and accuracy of the data; by using pagination and lazy loading techniques, the loading of a large amount of data at once is avoided, reducing the waiting time of the front end and improving the user experience.

[0011] In a second aspect, the embodiments of the present application also provide a multi-table data query system based on JPA combination, comprising: a data table and association relationship determination module, configured to determine the data tables to be queried and the association relationships between the data tables in response to front-end display requirements; an entity definition module, configured to define the structures of the data tables and the association relationships between the data tables using JPA entities; a data query module, configured to execute queries on each data table respectively and process and combine the query results in memory using a Java streaming interface; a data query result return module, configured to integrate the combined query results into a data structure that can be parsed by the front end and return. The whole process of the multi-table data query based on JPA combination from requirement analysis to final data return is realized through the interaction of the data table and association relationship determination module, the entity definition module, the data query module, and the data query result return module.

[0012] In a third aspect, the embodiments of the present application further provide an electronic device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the steps of the JPA combination-based multi-table data query method according to the first aspect when executing the program.

[0013] In a fourth aspect, the embodiments of the present application further provide a storage medium having a computer program stored thereon, wherein the computer program is executable on a processor to implement the steps of the JPA combination-based multi-table data query method according to the first aspect.

[0014] From the above technical solutions, the present application has the following advantages: In the JPA combination-based multi-table data query method, system, device, and medium provided by the present application, the number of database queries is reduced through step-by-step querying and data processing, complex multi-table joint queries are avoided, and the query efficiency is improved; the Java streaming interface is used for data processing and combination, the advantages of multi-core processors are fully utilized, and the data processing efficiency is improved; the complex query logic is decomposed into multiple steps, which facilitates maintenance and extension; the JPA is used for multi-table data combination and optimization, the number of database queries is reduced, the data acquisition efficiency is improved, and the query time is shortened, effectively solving the low efficiency and complex queries in traditional multi-table data queries; through paging and lazy loading, a large amount of data is avoided to be loaded at one time, the waiting time of the front end is reduced, and the user experience is improved; through dynamic construction of query statements and processing logic, different query requirements are flexibly handled, and the rapid changes of business are adapted.

[0015] The present application replaces multiple independent queries with a single JOIN query, reduces the CPU and IO pressure of the database, greatly shortens the front-end waiting time, and supports flexible adaptation to business requirement changes without the need to restructure the code. BRIEF DESCRIPTION OF DRAWINGS

[0016] In order to more clearly illustrate the technical solutions of the present application, the drawings required in the description will be briefly introduced below. Obviously, the drawings in the following description are only some embodiments of the present application, and other drawings can be obtained by those skilled in the art without creative labor.

[0017] Figure 1 The flowchart of the JPA combination-based multi-table data query method of the present application.

[0018] Figure 2 The schematic diagram of the JPA combination-based multi-table data query system of the present application. DETAILED DESCRIPTION

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

[0020] For example, with the development of big data and cloud computing, data management and processing have become critical to optimizing application performance. Especially in enterprise applications, databases, as the core hub for data storage and access, have a direct impact on application responsiveness and user experience. However, as business scale continues to expand and data volumes explode, traditional database query models are facing unprecedented challenges.

[0021] Currently, the mainstream data acquisition method in the industry is to use complex multi-table SQL statements. Figure 1 All required data can be obtained at once. However, this approach has many drawbacks. First, the original SQL statement contains a large number of nested subqueries and complex multi-table join operations. Taking supplier data query as an example, each time a supplier record is queried, multiple related subqueries must be executed, which greatly increases the time consumption of database queries and is extremely inefficient. Second, in actual business scenarios, once the number of suppliers climbs to several thousand, this complex query method will cause the response time of the business system to be significantly lengthened, seriously damaging the user experience. Third, complex SQL statements are obscure and difficult to modify, which undoubtedly increases the maintenance cost of the business system. Fourth, if new evaluation indicators are to be added, the complex SQL statements must be modified, and this process is very likely to introduce new errors.

[0022] Although some database query optimization methods have emerged, such as creating indexes and optimizing JOIN operations, these methods have limited optimization effects in complex multi-table association query scenarios and cannot fundamentally break through performance bottlenecks.

[0023] To address the above issues, this embodiment provides a multi-table data query method based on JPA combination, which improves the efficiency and performance of multi-table query through step-by-step query, data processing and combination.

[0024] The technical solutions in the embodiments of the present application will be clearly and completely described with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only a part of the embodiments of the present application, but not all the embodiments of the present application. Based on the embodiments in the present application, all other embodiments obtained by a person of ordinary skill in the art without creative effort belong to the scope of protection of the present application.

[0025] Please refer to Figure 1 The flow chart of the multi-table data query method based on JPA combination in a specific embodiment is shown, and the method comprises the following steps: S1. In response to the front-end display requirements, determine the data tables and the association relationship between the data tables that need to be queried; In actual application, the front-end display requirements can be very diverse, not only including simple field display, but also possibly involving complex statistical information and associated data; for example, in an e-commerce platform, the front-end can need to display the order information of a user, and also need to display the detailed commodity information, order status, payment information, etc. of each order; in order to meet these requirements, we need to deeply analyze the front-end request and extract all related fields and association relationships; It should be noted that by positioning the query range, unnecessary data query and processing are avoided, the database burden is reduced, and the query efficiency is improved; by determining the association relationship, a basis is provided for subsequent data integration, and the completeness and accuracy of the data are ensured; S2. Use JPA entity to define the structure of the data tables and the association relationship between the data tables; When defining the JPA entity class, in addition to the basic fields and association relationships, JPA annotations can also be used to optimize query performance. For example, using @Fetch(FetchMode.JOIN) can optimize the loading strategy of associated data and reduce the number of database queries. In addition, by using the @Index annotation, an index can be added to the field, further improving the query efficiency; It should be noted that by using the standard specification of JPA, the database table is mapped to the object-oriented entity class, which simplifies the complexity of database operation, makes the code structure clearer, and is convenient for development and maintenance; at the same time, the association relationship definition of JPA can realize consistency maintenance and improve the query; S3. Perform query on each data table respectively, and use Java streaming interface to process and combine the query results in memory; When performing query, the setFirstResult and setMaxResults can be used to set the paging parameters of the query through the TypedQuery of JPA to realize physical paging; in addition, for large data processing, the Stream API of Java 8 can be used for secondary paging to further optimize memory usage and response speed; It should be noted that the required information can be obtained by querying the respective table data, reducing data redundancy; the use of Java streaming interface provides a simple and efficient data processing method, supports parallel processing, can quickly filter, convert and aggregate the query results, improves the data processing efficiency, and improves the concurrent performance of the application; S4. The combined query results are integrated into a data structure that can be parsed by the front end and returned; When integrating the 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, in order to further improve the user experience, paging 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 can be easily parsed by the front end, such as JSON format, it is convenient for the front end to display and use, and the usability of the data and the user experience are improved; at the same time, by pre-integrating and converting the data, the data processing pressure of the front end is reduced, and the overall performance of the business system is improved.

[0026] The embodiment realizes the whole process of multi-table data query based on JPA combination from requirement analysis to final data return, ensuring the efficiency and accuracy of the query; by decomposing the query process, it is easy to understand and maintain.

[0027] Further, as a refinement and extension of the above embodiment, in order to fully describe the specific implementation process in this embodiment, another multi-table data query method based on JPA combination is provided, which includes the following steps: S1. In response to the front-end display requirements, determine the data tables to be queried and the association relationship between the data tables; the specific steps of step S1 are as follows: S11. Obtain the front-end display requirements and parse the fields to be displayed; For example, the front end needs to display all the orders of a user and their associated product information; parse the request and extract the fields to be displayed: orderId, orderDate, productId, and productName; S12. Determine the data tables to be queried according to the fields to be displayed; For example, according to the field information, determine the data tables to be queried: Order table and Product table; S13. Determine whether there is an association relationship between the data tables to be queried according to whether there is an association field between the data tables to be queried; For example, it is determined that there is an association field productId between the Order table and the Product table, and an association relationship is established; It should be noted that by analyzing the front-end display requirements, the data tables and the association relationship to be queried are determined, ensuring the accuracy and completeness of the requirement analysis; by dynamically analyzing the requirements, different front-end display requirements are flexibly responded to, improving the adaptability of the business application; S2. The structure of the data table and the association relationship between the data tables are defined using JPA entities; the specific steps of step S2 are as follows: S21. Defining a JPA entity class for each data table to be queried; Exemplarily, the Order and Product entity classes are defined; S22. The establishment of the association relationship between the corresponding JPA entity classes of the data tables with the association relationship is performed using annotations; Specifically, the association relationship is, for example, @OneToOne, @OneToMany, @ManyToOne, and @ManyToMany; Exemplarily, the @OneToMany and @ManyToOne annotations are used to establish the association relationship; It should be noted that by defining the JPA entity class and establishing the association relationship, the accurate definition of the data table structure and the association relationship is ensured; by establishing the association relationship through annotations, the code is clearer and easier to understand, facilitating maintenance and extension; S3. Querying each data table respectively, and using Java streaming interface to process and combine the query results in memory; the specific steps of querying each data table respectively in step S3 are as follows: Using JPQL or Java API to build a query statement according to the fields to be displayed; Specifically, it is determined whether dynamic query conditions or dynamic query statement construction are needed; Executing the query statement to obtain the data of the fields to be displayed from the corresponding data table to be queried; It should be noted that the query statement is constructed according to the appropriate query method of the display field, which can optimize the query for different scenarios, improve the flexibility and efficiency of the query, and ensure the accuracy of data acquisition; Specifically, the JPQL query statement is constructed to ensure that only necessary fields are queried; the dynamic query is constructed using Criteria API to dynamically generate a query statement according to the query conditions; The specific steps of using JPQL or Java API to build a query statement according to the fields to be displayed are as follows: According to the fields to be displayed, it is determined whether the following conditions are met: There are dynamic query conditions; Dynamic query statement construction is needed; If neither of the above conditions is met, JPQL is used to construct the query statement; If both at least meet one, use Java API to build query statement; It should be noted that the choice of using JPQL and Criteria API to build query statements depends on specific business needs and development scenarios; in practical applications, one of them can be chosen according to the complexity and flexibility requirements of the query, or they can be used separately in different scenarios; Specifically, JPQL is a query language similar to SQL, used to manipulate JPA entities and relationships between JPA entities. JPQL provides a declarative query method, suitable for scenarios where query logic is relatively fixed and simple; JPQL query statements are similar to SQL, easy to understand and maintain, and have high readability, and JPQL checks the syntax of the query statement and the properties of the entity class at compile time; for simple query logic, JPQL can be quickly implemented; the applicable scenarios for JPQL query statements are: first, the query logic is relatively fixed and does not require dynamic generation of query statements; second, the query statement is relatively simple and does not involve complex dynamic conditions; For example, an e-commerce platform needs to query all orders and associated product information of a certain user, which can be implemented using JPQL as follows: 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 is the association relationship between order and product; through JPQL, we can get all the orders and associated product information of a user in one query, avoiding multiple database operations; Criteria API is a query construction method based on Java API, which provides the ability to programmatically build query statements, suitable for scenarios where query logic is complex and query statements need to be dynamically generated; Criteria API can dynamically build query statements and generate different query logic based on different query conditions; check the syntax of the query statement and the properties of the entity class at compile time; for complex query logic, especially in scenarios with many dynamic conditions, Criteria API is more flexible.

[0028] Criteria API scenarios: one is complex query logic, which needs to dynamically generate query statements according to different conditions; there are many query conditions, and there are many combination methods between conditions; Similarly, for an e-commerce platform that needs to dynamically query orders based on different conditions, Criteria API can be used to implement as follows: 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 statement is dynamically built using Criteria API to query orders based on user ID and order date threshold; CriteriaBuilder is used to build query conditions, CriteriaQuery is used to define query logic, and Root represents the root entity class of the query; In actual development, you can choose to use JPQL or Criteria API based on specific business requirements: If the query logic is relatively fixed and simple, it is recommended to use JPQL; JPQL syntax is similar to SQL, easy to understand and maintain, suitable for quickly implementing simple query logic.

[0029] If the query logic is complex and needs to dynamically generate query statements, it is recommended to use Criteria API; Criteria API provides flexibility, allowing you to dynamically build query statements based on different conditions, suitable for handling complex query logic; In actual projects, you can use JPQL and Criteria API in different scenarios based on different query requirements; for example: For some simple queries, such as querying user information based on user ID, you can use JPQL; For some complex queries, such as dynamically querying orders based on multiple conditions, you can use Criteria API; By reasonably selecting and combining JPQL and Criteria API, you can fully leverage their respective advantages, improve development efficiency and query performance; The specific steps for executing the query statement to obtain the data of the fields to be displayed from the corresponding data table to be queried are as follows: Use FETCH JOIN to pre-load associated data with lazy loading flag; In addition to using FETCH JOIN to pre-load associated data, in some special scenarios, you can also use caching mechanisms to further optimize data acquisition processes based on real-time business requirements and data access frequency; for example, for frequently accessed and infrequently changed associated data, you can store the data obtained by FETCH JOIN in local cache (such as using Guava Cache or Caffeine caching framework); in this way, when the same query request comes later, you can first get the data from the cache, if the cache hits, you can directly return the data without querying the database again, greatly reducing the pressure on the database and query time; for cache update strategy, you can set up a timing update or actively update the cache when data changes to ensure data accuracy; It should be noted that the FETCH JOIN is used to preload the associated data with the lazy loading flag, ensuring that the associated information is obtained at the time of query to avoid multiple queries to the database; Filter redundant fields through the WHERE clause; In actual business scenarios, data security is crucial; therefore, when filtering redundant fields using the WHERE clause, data permission control can also be combined for operation; for example, different user roles may have different data viewing permissions, and administrator users can view all fields, while ordinary users may only be able to view part of the fields; by adding permission judgment conditions in the WHERE clause, not only can redundant fields be filtered out, but also the security of data can be ensured; for example, in an enterprise resource management system, the employee table contains the salary information of employees, and ordinary employees cannot obtain the salary field when querying the employee list through the WHERE clause, thus protecting sensitive information; at the same time, this approach also helps to reduce data transmission volume and improve system response speed, as unnecessary sensitive data will not be transmitted over the network; It should be noted that by filtering data through the WHERE clause, unnecessary redundant fields are filtered out, reducing data transmission volume; Optimize index usage by adding comments for hints; It should be noted that adding comments is used to prompt the database to optimize index usage, such as using / *+ INDEX_ASC(table column) * / in JPQL to prompt the use of indexes, improving query speed; When the data volume of query results exceeds the threshold, automatically perform a paging query; Specifically, physical paging is achieved through JPQL's setFirstResult() and setMaxResults(); in JPA entity association relationships, set fetch=FetchType.LAZY to load associated data only when needed; use Java 8 Stream's skip() and limit() methods for secondary paging of in-memory data; use / *+ INDEX_ASC(table column) * / in JPQL to prompt the use of indexes; When performing a paging query, in addition to using JPQL's setFirstResult() and setMaxResults() methods for physical paging and Java 8 Stream's skip() and limit() methods for secondary paging of in-memory data, a database cursor can also be used; a cursor is a mechanism provided by a database system for traversing a query result set; by using a cursor, data can be obtained row by row rather than all at once, thus reducing memory usage; Specifically, it needs to be noted that according to the dynamic query condition and demand, JPQL or Java API is selected to build a query statement, which improves the flexibility and adaptability of the query; by adding annotations to optimize index usage, the query performance is improved; The specific steps of using Java streaming interface to process and combine the query results in memory in step S3 are as follows: Create a thread for each query result; Parallelly execute each thread; In each thread, use filter operation to perform data filtering, map operation to perform data conversion, and reduce operation to perform data aggregation on the query result; Specifically, an independent thread is created for each query result, and multiple threads are executed in parallel to fully utilize the performance advantages of multi-core processors; in each thread, the filter operation of Java streaming interface is used for data filtering, the map operation is used for data conversion, and the reduce operation is used for data aggregation, which efficiently processes and combines the query results; by using Java streaming interface for data processing and combination, the efficiency and speed of data processing are improved; It needs to be noted that by using Java streaming interface for data processing and combination, the advantages of multi-core processors are fully utilized, and the efficiency of data processing is improved; by parallelly executing each thread, the speed of data processing is improved; S4. The combined query results are integrated into a data structure that can be parsed by the front end and returned; The specific steps of step S4 are as follows: S41. The combined query results are integrated into a Map structure in the form of key-value pairs according to the preset method; S42. The query results of the Map structure are grouped according to the front-end display requirements, and then converted into JSON format; It needs to be noted that the combined query results are integrated into a data structure that can be parsed by the front end and returned, which ensures the completeness and accuracy of the data; by using pagination and lazy loading, the loading of a large amount of data at once is avoided, the waiting time of the front end is reduced, and the user experience is improved.

[0030] In a large enterprise's supply chain management system, the comprehensive information of a supplier needs to be queried, including the supplier's basic information, the number of recent transaction records, the average transaction amount, and the transaction evaluation level. The traditional query method and the query method of the present application are compared as follows: Traditional query method: use a complex multi-table query SQL statement to get all the data at once. It involves three tables: supplier table (supplier), transaction record table (transaction), and evaluation table (evaluation).

[0031] The enterprise has 1000 suppliers, with an average of 100 transaction records and 1 evaluation record for each supplier. When the database directly executes this query, it needs to perform multiple subquery operations for each supplier record due to the multiple nested subqueries. According to tests, the query takes up to 5 seconds. As the number of suppliers increases, the query time will further extend, seriously affecting the system response speed. In addition, if a new query field such as the time of the last transaction is added, the complex SQL statement must be modified, which is not only cumbersome to operate but also prone to errors.

[0032] The query method of the present application: multi-table data query method based on JPA combination Analyze the front-end requirements: determine that basic information needs to be obtained from the supplier table, transaction quantity and average transaction amount from the transaction record table, and evaluation level from the evaluation table, and clearly associate the three tables through supplier_id.

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

[0034] Optimize query logic: According to the requirements, the query logic is relatively fixed, and JPQL is used to construct the query statement. For example, query the supplier basic information and transaction record quantity: for the average transaction amount, also use JPQL: For evaluation level: Use FETCH JOIN to preload associated data, filter redundant fields through the WHERE clause, and add annotations to optimize index usage.

[0035] Use Java streaming interface to process and combine the query results in memory. For example, match and integrate the three query results according to supplier_id.

[0036] Convert the combined data into JSON format and return it to the front-end.

[0037] According to tests, using the query method of the present application, the total query and data processing time is only 1 second when processing the same size of data. As the number of suppliers increases, through reasonable paging and lazy loading, the business application response speed is less affected. Moreover, if a new query field needs to be added, only simple modifications are needed in the JPA query and data processing steps, without the need to significantly adjust the overall code structure, reducing maintenance costs.

[0038] As can be seen from this example, the multi-table data query method based on JPA combination of the application has a significant improvement in performance compared to the traditional query method, and also has obvious advantages in maintainability and flexibility.

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

[0040] The application effectively solves the performance bottleneck of the original SQL by breaking it down into multiple simple queries + memory calculation, which is particularly suitable for complex report queries, scenarios requiring multiple calculations and aggregation, and moderate data volume (can be put into memory) conditions, and embodies the "space-time trade-off" and "separate query" methods, which is an optimization mode for Java to handle complex data queries.

[0041] It should be understood that the size of the serial number of each step in the above embodiments does not mean the order of execution, and 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 application.

[0042] As Figure 2 shown below is an embodiment of a multi-table data query system based on JPA combination provided by the embodiments of the present disclosure. The system and the multi-table data query method based on JPA combination of each of the above embodiments belong to the same inventive concept. Details not described in the embodiment of the multi-table data query system based on JPA combination can be referred to the above embodiment of the multi-table data query method based on JPA combination.

[0043] The system comprises: a data table and association relationship determination module for determining the data tables to be queried and the association relationships between the data tables in response to the front-end display requirements; an entity definition module for defining the structures of the data tables and the association relationships between the data tables using JPA entities; a data query module for performing queries on each data table respectively, using Java streaming interfaces to process and combine the query results in memory; a data query result return module for integrating the combined query results into a data structure that can be parsed by the front-end and returning it.

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

[0045] The multi-table data query method based on JPA combination provided by the embodiments of the present application can be applied to an electronic device. Those skilled in the art can understand that the structure of the electronic device involved in the embodiments of the present application does not constitute a limitation on the electronic device, and the electronic device can include more or fewer components than the illustration, or combine certain components, or different component arrangements. In the embodiments of the present application, the electronic device includes but is not limited to a laptop computer, a desktop computer, a workstation, a personal digital assistant, a server, a blade server, a mainframe computer, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as a personal digital processor, a cellular phone, a smart phone, a wearable device, 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 the present application described herein and / or claimed.

[0046] The electronic device can include a processor, an external memory interface, an internal memory, a universal serial bus (USB) interface, a charging management module, a power management module, a battery, a wireless communication module, an audio module, a speaker, a microphone, a sensor module, a key, a camera, a display screen, and a SIM card interface, etc.

[0047] It can be understood that the structure illustrated in the embodiments of the present application does not constitute a specific limitation on the electronic device. In other embodiments of the present application, the electronic device can include more or fewer components than the illustration, or combine certain components, or split certain components, or different component arrangements. The illustrated components can be implemented in hardware, software, or a combination of software and hardware.

[0048] The processor can include one or more processing units, such as: the processor can include a central processing unit (CPU), an application processor (AP), a modem processor, a graphics processing unit (GPU), an image signal processor (ISP), a controller, a memory, a video codec, a digital signal processor (DSP), a baseband processor, and / or a neural-network processing unit (NPU), etc. Among them, different processing units can be independent devices, or can be integrated in one or more processors.

[0049] The processor can be the nerve center and command center of the electronic device. The controller can generate operation control signals according to instruction operation codes and timing signals, and complete the control of fetching and executing instructions.

[0050] The memory in the processor can also be configured to store instructions and data. In some embodiments, the memory in the processor is a cache memory. The memory can store instructions or data that the processor has just used or repeatedly used. If the processor needs to use the instructions or data again, it can directly call from the memory. This avoids repeated access and reduces the waiting time of the processor, thereby improving the efficiency of the system.

[0051] The electronic device realizes the technical scheme of the present application based on the multi-table data query method combined with JPA, which responds to the front-end display requirements, determines the data tables and the association relationship between the data tables that need to be queried, defines the structure of the data tables and the association relationship between the data tables using JPA entities, executes queries on each data table respectively, uses Java streaming interfaces to process and combine the query results in memory, integrates the query results after combination into a data structure that can be parsed by the front end and returns, thereby reducing the number of database queries, avoiding complex multi-table association, improving query efficiency, using Java streaming interfaces for data processing and combination, fully utilizing the advantages of multi-core processors, improving the efficiency of data processing, decomposing complex query logic into multiple steps for easy maintenance and expansion, combining and optimizing multi-table data through JPA, reducing the number of database queries, improving data acquisition efficiency, thereby shortening the query time, effectively solving the low efficiency and complex query problem in traditional multi-table data query, avoiding loading a large amount of data at a time through paging and lazy loading, reducing the waiting time of the front end and improving user experience, and dynamically constructing query statements and processing logic to flexibly respond to different query requirements and adapt to the beneficial effects of rapid changes in business.

[0052] In the storage medium provided by the present application, a program product capable of realizing the multi-table data query method combined with JPA is stored.

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

[0054] In some possible implementation manners, the multi-table data query method based on the JPA combination of the present disclosure can be implemented in the form of a program product, which includes program codes for causing an end device to perform the steps according to various exemplary embodiments of the present disclosure described in the above "Exemplary Method" section of the present specification when the program product is run on the end device.

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

[0056] The above description of disclosed embodiments enables one of ordinary skill in the art to make or use the application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein can be applied to other embodiments without departing from the spirit or scope of the application. Thus, the present application is not intended 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: The steps include: S1. Respond to front-end display requirements and determine the data tables to be queried and the relationships between them; 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. 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 is characterized in that: The specific steps of step S1 are as follows: S11. Obtain the front-end display requirements and parse 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 an associated relationship between the data tables to be queried based on whether there are associated fields between the data tables to be queried.

3. The multi-table data query method based on JPA combination according to claim 1 is 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 association relationships between corresponding JPA entity classes of data tables with association relationships.

4. The multi-table data query method based on JPA combination according to claim 2 is characterized in that: The specific steps for querying 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 obtain the data of the fields that need to be displayed from the corresponding data table.

5. The multi-table data query method based on JPA combination according to claim 4 is characterized in that: The specific steps for constructing a query statement using JPQL or Java API based on the fields to be displayed are as follows: Display fields as needed to determine whether the following conditions are met: There are dynamic query conditions; The query statement needs to be constructed dynamically; If neither of the two conditions are met, use JPQL to construct the query statement; If at least one of the two conditions is met, use the Java API to construct a query statement; The specific steps of executing the query statement to obtain the data of the fields to be displayed from the corresponding data table to be queried are as follows: Use FETCH JOIN to preload related data with the lazy loading flag; Filter redundant fields through the WHERE clause; Optimize index usage by adding annotations for hints; When the amount of query result data exceeds the threshold, paging query is automatically executed.

6. The multi-table data query method based on JPA combination according to claim 2 is characterized in that: The specific steps for using the Java streaming interface in step S3 to process and combine the query results in memory are as follows: Create a thread for each query result; Execute threads 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.

7. The multi-table data query method based on JPA combination according to claim 1 is 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 in a preset manner; S42. Group the query results of the Map structure according to the front-end display requirements and then convert them into JSON format.

8. A multi-table data query system based on JPA combination, 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 data tables; The entity definition module is used to define the structure of the data table and the relationship between data tables using JPA entities; The data query module is used to execute queries on each data table separately, and use the Java streaming interface 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 return it.

9. An electronic device, characterized in that: The invention comprises a memory, a processor and a computer program stored in the memory and executable on the processor, wherein when the processor executes the program, the steps of the multi-table data query method based on JPA combination as claimed in any one of claims 1 to 7 are implemented.

10. A storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the multi-table data query method based on JPA combination as claimed in any one of claims 1 to 7 are implemented.

Images