Data query method based on general data access framework

Abstract

The application relates to a data query method based on a general data access framework and belongs to the technical field of data access, and solves the problem that the query logic of data in different storage forms is not unified and the processing logic is complex in the prior art. The method comprises the following steps: obtaining a data source based on the access mode of data to be queried; obtaining a query condition, calling a query constructor, constructing a query object syntax tree on the data to be queried through a chain calling mode, and obtaining a query object based on the query object syntax tree; creating a query executor according to the data source; and transmitting the query object into the query executor, executing a query method, and obtaining a query result. The method realizes the use of a unified query method to query data in different storage forms.

Description

Technical Field

[0001] This invention relates to the field of data access technology, and in particular to a data query method based on a general data access framework. Background Technology

[0002] With the improvement of data storage capabilities, various types of databases have emerged, and enterprises are adopting diverse storage methods to meet increasingly complex needs. Supporting flexible data querying and analysis requires processing data from different sources. How to quickly develop systems that meet the query requirements of different data types is a key concern during system development.

[0003] For relational databases, existing open-source frameworks encapsulate data query methods, making it easier for developers to query data and encapsulate SQL (Structured Query Language) statements. Examples include Hibernate, JPA (Java Persistence API), and Mybatis. For complex business data retrieval, queries are often performed directly using SQL that conforms to database syntax. For data stored directly in memory, or data in an in-memory database, the typical approach is to first retrieve the corresponding object of the in-memory data and then obtain the specific value using the `get` method, or to place the in-memory data in a collection and process the data using methods that manipulate the collection.

[0004] When using object-oriented query methods in open-source frameworks, it is typically suitable for simple data queries. Developers need to construct corresponding business objects based on the query's business type. When using encapsulated SQL or directly written SQL, different database types, or even different versions of some databases, suffer from inconsistencies in SQL syntax. Often, multiple sets of SQL statements need to be encapsulated to meet different syntax requirements based on the database type, and attention must be paid to issues such as SQL injection, SQL concatenation order, and matching of dynamic SQL parameters and placeholders. If it's necessary to correlate or compare multiple objects in memory, or to interact and match objects in memory with data retrieved from the database and data from files, developers need to obtain the required memory data according to specific needs and then process it with other data through programming. Furthermore, if a project requires processing both database data and memory data, developers need to employ multiple development methods, impacting development efficiency. Custom-developed code cannot be reused, and code quality and query methods become uncontrollable. Summary of the Invention

[0005] Based on the above analysis, the embodiments of the present invention aim to provide a data query method based on a general data access framework to solve the problems of inconsistent query logic and complex processing logic for data in different storage formats.

[0006] This invention provides a data query method based on a general data access framework, comprising the following steps:

[0007] Obtain the data source based on the access method of the data to be queried;

[0008] Get the query conditions, call the query builder, construct the query object syntax tree for the data to be queried through chained calls, and obtain the query object based on the query object syntax tree;

[0009] Create a query executor based on the data source;

[0010] The query object is passed into the query executor, and the query result is obtained after the query method is executed.

[0011] Based on a further improvement to the above method, the data source is obtained based on the access method of the data to be queried, including:

[0012] When the data to be queried is stored in a database and the project framework does not use Hibernate or JPA, the data to be queried can be accessed through the database table name and field name to obtain the JDBC data source;

[0013] When the data to be queried is stored in a database and the project framework uses Hibernate or JPA, the data to be queried is encapsulated as an entity object and the JPA data source is obtained.

[0014] When the data to be queried is stored in memory, the data to be queried is encapsulated into a memory table object, and the memory data source is obtained.

[0015] When the data to be queried is stored in a constant dataset, the data to be queried is encapsulated into an in-memory table object or a constant query reference object to obtain the in-memory data source.

[0016] Based on the above method, a further improvement, the in-memory table object includes: table alias, data list, and field accessor;

[0017] The data list includes attribute names and attribute values;

[0018] Field accessors are used to specify one or more attribute names to be extracted from a list of data;

[0019] The constant query reference object implements the root reference interface and the dataset interface;

[0020] The dataset interface includes methods for retrieving the dataset header and retrieving the data list. In the method for retrieving the data list, the constant query reference object stores the data in a two-dimensional array format; in the method for retrieving the dataset header, the attribute name of the data list is set to the header.

[0021] Based on the further improvement of the above method, each node on the query object syntax tree is a reference object that implements the same parent reference interface. The parent reference interface includes a method to get the element type, and the reference object defines the corresponding SQL syntax element type in the method to get the element type.

[0022] The root reference interface is a child reference interface derived from the parent reference interface;

[0023] The query conditions are encapsulated as constant reference objects of SQL syntax elements, which serve as nodes on the query object syntax tree.

[0024] Based on the above method, the query builder includes multiple interfaces corresponding to SQL components. In each interface implementation class, the start and end methods are used to indicate the start and end of constructing the SQL components.

[0025] Constructing a query object syntax tree includes: calling multiple sets of methods starting with start and end through chained calls, and attaching one or more reference objects to each set of methods starting with start and end; a query object syntax tree describes a complete SQL statement.

[0026] Further improvements to the above method include obtaining the query object based on the query object syntax tree, including:

[0027] Place reference objects with the same element type on the syntax tree into the corresponding reference object collection;

[0028] Place the collection of referenced objects into the root reference object of the syntax tree root node, and return the root reference object;

[0029] The root reference object is the reference object that implements the root reference interface corresponding to the root node of the query object syntax tree.

[0030] Based on a further improvement to the above method, a query executor is created according to the data source, including:

[0031] If it is a JDBC data source, call the execution configurator to obtain the JDBC executor, create a query transpiler, and obtain the target database type according to the JDBC data source, and register the writer corresponding to the target database type to the query transpiler;

[0032] If it is a JPA data source, call the execution configurator to obtain the JPA executor, create a query transpiler, and register the JPA writer to the query transpiler;

[0033] If it is a memory data source, directly obtain the memory executor.

[0034] Based on a further improvement of the above method, the writer has the same parent writer interface, used to perform the following operations: obtain the writer processing type, prepare for translation, and write the translation; wherein,

[0035] Get the writer processing type, which defines the element types that the writer can process, and corresponds to the element types defined in a reference object;

[0036] The translation is prepared to transform the referenced objects in the query object using equivalent syntax according to the target database type, adjust the node connection relationship between the referenced objects, and obtain the processed query object.

[0037] Write translation is used to generate SQL fragments or JPA HQL fragments corresponding to the target database type in the string builder of the context, and / or write values ​​to the parameter list buffer, based on the element type of the referenced object in the processed query object.

[0038] Based on a further improvement to the above method, the query object is passed into the query executor, and the query results are obtained after executing the query method, including:

[0039] When a query is executed by calling the JDBC executor or JPA executor, the writer corresponding to the root reference object of the query object is used as the entry writer for the query transpiler call. In the preparation transpilation and write transpilation, the query object syntax tree is traversed according to the defined traversal order. The string builder and parameter list buffer in each writer context are merged into the processed query object to obtain the complete SQL statement or HQL statement and parameter list. After execution, the query result is obtained.

[0040] When the memory executor is invoked to execute a query, the set of reference objects for the query object is obtained. First, the set of table reference objects is obtained, and the table reference objects are sorted and merged to obtain a wide table set. Then, the wide table set is filtered according to the Boolean condition reference object set. Next, the wide table set is sorted according to the sorted reference object set. Finally, the wide table set is grouped according to the grouped reference object set to obtain the query results.

[0041] Based on the above method, the query results are further improved into a dataset object that implements the dataset interface, or into a constant query reference object for use in memory data queries.

[0042] Compared with the prior art, the present invention can achieve at least one of the following beneficial effects:

[0043] 1. Following a unified SQL writing approach, chained method calls are used to construct the query object syntax tree, eliminating the need to consider the SQL concatenation order. It also supports multi-level nested subqueries and the construction of complex SQL, reducing the difficulty of constructing SQL.

[0044] 2. By constructing only one query object and providing one set of query logic, it can support different types and versions of databases, in-memory data and constant datasets, which improves code reusability, database compatibility and reduces maintenance costs;

[0045] 3. By centrally processing special conversions between different databases through a query translator, users are prevented from writing non-standard native SQL, SQL syntax errors are avoided, code quality is improved, and the risk of performance issues is reduced.

[0046] 4. Customize your needs by extending custom functions, reference objects, writers, executors, and datasets;

[0047] In this invention, the above-described technical solutions can be combined with each other to achieve more preferred combinations. Other features and advantages of this invention will be set forth in the following description, and some advantages may become apparent from the description or be learned by practicing the invention. The objects and other advantages of this invention can be realized and obtained from what is particularly pointed out in the description and drawings. Attached Figure Description

[0048] The accompanying drawings are for illustrative purposes only and are not intended to limit the invention. Throughout the drawings, the same reference numerals denote the same parts.

[0049] Figure 1 This is a flowchart of a data query method based on a general data access framework in an embodiment of the present invention. Detailed Implementation

[0050] Preferred embodiments of the present invention will now be described in detail with reference to the accompanying drawings, which form part of this application and are used together with the embodiments of the present invention to illustrate the principles of the present invention, but are not intended to limit the scope of the present invention.

[0051] A specific embodiment of the present invention discloses a data query method based on a general data access framework, such as... Figure 1 As shown, it includes the following steps:

[0052] S11: Obtain the data source based on the access method of the data to be queried;

[0053] It should be noted that the general access framework is integrated into the development project as a tool JAR, which provides different data sources, including JDBC data sources, JPA data sources, and in-memory data sources, to support data queries of different storage formats.

[0054] The data to be queried refers to all data in the business system that involves the query. Based on the storage format of the data to be queried and the project framework, the access method of the data to be queried is determined, thereby obtaining the corresponding data source.

[0055] Specifically, when the data to be queried is stored in a database and the project framework does not use Hibernate or JPA, the data to be queried is accessed through the database table name and field name to obtain the JDBC data source, that is, a connection to the database is established through the database driver.

[0056] When the data to be queried is stored in a database and the project framework uses Hibernate or JPA, the data to be queried is encapsulated as an entity object and the JPA data source is obtained, that is, the database connection is configured through JPA.

[0057] It should be noted that while JDBC data sources can be used in this scenario, it is recommended to use JPA data sources to avoid inconsistencies between Hibernate cache and the database caused by directly reading and writing to the database.

[0058] When the data to be queried is stored in memory, the data to be queried is encapsulated into a memory table object, and the memory data source is obtained.

[0059] When the data to be queried is stored in a constant dataset, the data to be queried is encapsulated into an in-memory table object or a constant query reference object to obtain the in-memory data source.

[0060] It should be noted that memory data comes from various sources, such as the memory of the business system or the memory database. However, the processing of memory data is not limited to these memory data. It can also be associated with data in files and result sets obtained from database queries. By encapsulating this data into memory table objects, the memory table objects can be accessed in the form of virtual physical tables through the memory data source when querying or performing related queries on this data.

[0061] Specifically, an in-memory table object includes: a table alias, a data list, and field accessors. If the table alias is the same as an existing table alias in the in-memory data source, it will be overwritten. The data list includes attribute names and attribute values, and the field accessors are used to specify one or more attribute names to be extracted from the data list. When the data list contains many attribute data, but only a portion of the attribute data needs to be accessed, the field accessors can directly select the required data and put it into the in-memory data source without manual processing.

[0062] For some constant datasets that do not want to be placed in the memory data source but need to be used in data queries, they can be encapsulated as constant query reference objects. When constructing subsequent query objects, the object name of the constant query reference object is used as the virtual table name.

[0063] S12: Obtain the query conditions, call the query builder, construct the query object syntax tree for the data to be queried through chained calls, and obtain the query object based on the query object syntax tree;

[0064] It should be noted that the query builder is constructed according to the logic of writing SQL. Considering that the components of an SQL statement include: select column name, from table name, where filter condition, group by field, order by condition, having condition, and union all / union, the query builder combines many interfaces, including Select, From, Where, Group, Order, Having, and Merge, which correspond to the various components of SQL. Each interface implementation class uses methods starting with start and end to indicate the start and end of constructing the SQL components.

[0065] Specifically, the query builder in the general access framework provides the following methods:

[0066] `startSelect` is used to set the column names for selection; `endSelect` indicates that the current column names have been set.

[0067] `startFrom` is used to set the table to be queried; `endForm` indicates that the table setting for the current query is complete.

[0068] `startWhere` is used to set the filter conditions; `endWhere` is used to indicate that the current filter conditions have been set.

[0069] `startGroup` is used to set the grouping field; `endGroup` indicates that the current grouping field has been set.

[0070] `startOrder` is used to set the sorting field and sorting order; `endOrder` indicates that the current sorting field and sorting order have been set.

[0071] `startHaving` is used to set grouping conditions; `endHaving` indicates that the current grouping conditions have been set.

[0072] startMerge is used to set table merging; endMerge is used to indicate the completion of the current merging setting;

[0073] page is used to set the pagination condition for the query;

[0074] limit is used to set the maximum number of query results;

[0075] It should be noted that the page and limit methods are mutually exclusive. When calling other methods, there is no order. Call the above methods according to the SQL syntax combination. After construction, call the build method to create and generate a query object;

[0076] It should be noted that a syntax tree can be constructed with one or more statements. A syntax tree describes a complete SQL statement. When combining multiple methods in one statement to construct multiple SQL components, the methods starting with start and end are called in pairs. If multiple statements are used and each statement constructs only one component of the SQL, only the methods starting with start can be called. Code line breaks are equivalent to calling the methods starting with end. When the logic is very complex, this way of freely constructing SQL allows developers to focus on the business without worrying about issues such as the order of splicing various components of the SQL, whether parentheses match, whether there is a SQL injection attack, and whether the syntax conforms to the target database.

[0077] Exemplarily, if you need to query the customer name where the customer ID is greater than 1, in SQL statement, it is expressed as "SELECT C.CUSTOMERNAME AS customer name FROM CUSTOMER C WHERE C.ID>1". If using the query constructor, a query constructor can be created through the HQueryFacade.createQueryBuilder() method. The above SQL statement can be constructed into a syntax tree with one statement. The example code is as follows:

[0078] HQuery query =

[0079] HQueryFacade.createQueryBuilder().startFrom().tab(“CUSTOMER”,“C”).endFrom().startSelect().col(0,“CUSTOMERNAME”,“客户名称”).endSelect().startWhere().gtr(getCol(0,“ID”),getVal(1)).endWhere().build();

[0080] The above SQL statement can also be constructed into a syntax tree using multiple statements, as shown in the example code below:

[0081] HQueryBuilder querybuilder=HQueryFacade.createQueryBuilder().startFrom().tab("CUSTOMER","C").endFrom();

[0082] querybuilder.startSelect().col(0, "CUSTOMERNAME", "Customer Name");

[0083] querybuilder.startWhere().gtr(getCol(0,"ID"),getVal(1));

[0084] HQuery query=querybuilder.build();

[0085] When the query builder constructs the components of the SQL, the data of each component is encapsulated as a reference object, which is placed in the reference object collection corresponding to each component as a node on the syntax tree. The root node of the syntax tree also has a corresponding reference object HQuery, which contains the reference object collection of each child node.

[0086] Preferably, the collection of referenced objects is of type ArrayList.

[0087] Reference objects are SQL syntax elements that describe SQL components, including table reference objects, select column reference objects, grouping reference objects, sorting reference objects, boolean condition reference objects, field reference objects, constant reference objects, and function call reference objects. These reference objects implement the same parent reference interface HValRef and all include a method to get the element type, which is used to define the SQL syntax element type corresponding to the reference object.

[0088] In the example code above, the `tab("CUSTOMER", "C")` method constructs a table reference object, where "CUSTOMER" is the table name in the table reference object, and "C" is the table alias. This table reference object is then placed into a collection of table reference objects. The `col(0, "CUSTOMERNAME", "Customer Name")` method constructs a selection column reference object, where "0" is the table index (i.e., the first table), "CUSTOMERNAME" is the column name, and "Customer Name" is the column alias. The selected column reference object will be placed in the selected column reference object collection selList; a boolean condition reference object is constructed by gtr(getCol(0,“ID”),getVal(1)), where gtr is the boolean condition operator, representing ">", getCol(0,“ID”) is the left side of the boolean condition, indicating that the field being compared is the “ID” field of the first table, and getVal(1) is the right side of the boolean condition, indicating that the comparison value is “1”. This boolean condition reference object will be placed in the boolean condition reference object collection boolList.

[0089] Preferably, considering that there will be two comparison values ​​when using the between operator, the Boolean conditional reference object supports two rvalues.

[0090] It's important to note that a parent reference interface derives into many child reference interfaces. Each reference object directly implements its corresponding child reference interface, and new methods can be added while inheriting methods from the parent reference interface. For example, the code below demonstrates constructing a reference object and placing it into the corresponding collection of reference objects, using a boolean conditional reference object as an example. Here, HBoolExpImpl is the implementation class of the boolean conditional reference interface HBoolRef, and HBoolRef is a child reference interface derived from the parent reference interface HValRef.

[0091] / / Construct a Boolean condition reference object

[0092] HBoolRefbool=

[0093] new HBoolExpImpl(getCol(0,"ID"),GTR,getVal(1),null);

[0094] / / Add the Boolean condition reference object to the Boolean condition reference object collection.

[0095] List<boolList> = Arrays.asList<List<Arrays.as ... <hboolref>(bool);

[0096] The referenced objects correspond to SQL syntax elements, including: column SEL, field COL, table FRM, grouping GRP, and constant CST, etc. It's worth noting that element types can be customized; for example, "CST_USER" can be defined as an extended constant reference, differentiating it from the generic CST for different processing methods on different database types or versions. For queries involving in-memory data, if complex function processing is involved, new element types can be added.

[0097] The query conditions passed in during the query are directly used in the query object syntax tree in the form of variable names. They are encapsulated as reference objects of constant SQL syntax element type and serve as nodes on the query object syntax tree.

[0098] It is worth noting that in existing technologies, when query conditions need to be dynamically passed into SQL statements, JDBC placeholders "?" are used to represent dynamic parameters in the SQL to avoid SQL injection attacks. However, when the query logic is complex, it is difficult for developers to match placeholders with their corresponding parameter values ​​one by one when constructing the SQL, and troubleshooting is also difficult when problems occur. Although Hibernate can store variables and parameter values ​​in a Map, when there are many variables or the same variable needs to be used multiple times in the SQL, the "variable name + ordinal number" method must be used to prevent variable name duplication, which is also difficult to troubleshoot when problems occur. In this embodiment, the query builder constructs the syntax tree by attaching reference objects in each component. Dynamic parameters can be treated as a node on the syntax tree and defined using a constant reference object that implements the HValRef interface, so there is no need to consider the placeholder issue during construction.

[0099] For constant datasets not stored in the memory data source, there is a constant query reference object, which can be used as a subquery dataset of a subquery reference object. The constant query reference object implements both the root reference interface and the dataset interface. The root reference interface is a sub-reference interface derived from the parent reference interface and is implemented by the root reference object. Implementing this interface allows the constant query reference object to be attached to the query object syntax tree. The dataset interface includes methods for retrieving the dataset header and retrieving the data list. In the method for retrieving the dataset header, the constant query reference object sets the attribute name of the data list to the header, and in the method for retrieving the data list, it stores the data list in a two-dimensional array format.

[0100] Specifically, the DataSet interface inherits from the List interface and adds two methods: one for retrieving the query result set and the other for retrieving the metadata of the query results. The query result set is formatted as a two-dimensional array, List.<Object[]> Storage. This structure facilitates the management of data results. For example, when renaming a column in the data results, only the metadata of the query results needs to be modified, which greatly improves the efficiency of modification. Furthermore, using a two-dimensional array to store the query result set can significantly reduce the storage space required.

[0101] Any reference object that implements the parent reference interface can be attached to the syntax tree. Each reference object can then be attached to other reference objects or child syntax trees, thus enabling the syntax tree to expand infinitely and effectively reducing the difficulty of constructing SQL.

[0102] For example, the field to be queried is a field in another table, as shown in the example code below:

[0103] querybuilder.startSelect().val(HQerryFacade.createQueryBuilder().startSelect().col(0,"TOTALS","TOTALS").endSelect().startFrom().tab("PRODUCT","P").endFrom().build(),"Sales");

[0104] Considering that queries typically involve multiple table joins, including left join, right join, inner join, and full join, it's necessary to derive sub-reference objects from the reference object. Preferably, the table reference object HFrmRef is further divided into three sub-reference objects: HFrmNRef (for direct table name joins), HFrmQRef (for subquery joins using a subquery result as a table), and HFrmJRef (for left table joins, join method, and join conditions). Since table joins can involve both direct table name joins and joins with sub-tables, based on the three derived sub-interfaces, HFrmJNRef (for right table joins using the direct table name) and HFrmJQRef (for sub-table joins using a subquery) are also derived.

[0105] For example, tables A and B are left outer joined using AID from table A and BID from table B. The example code for constructing the syntax tree is as follows:

[0106] querybuilder.startFrom().tab("A","A table").join(0,"AID",JoinType.LEFT_JOIN,"B","B table","BID").endFrom();

[0107] The code constructs a primary table name reference object HFrmNRef using the tab("A", "Table A") method, and constructs a related table name reference object HFrmJNRef using the join(0,"AID",JoinType.LEFT_JOIN,"B", "Table B", "BID") method. This object contains a table reference object HFrmRef, a join type JoinType, and a Boolean condition reference object HBoolRef, which are used to define the reference object of the left table to be joined, the join relationship, and the join condition, respectively.

[0108] Retrieving the query object based on the constructed query object syntax tree involves placing reference objects with the same element type on the syntax tree into the corresponding reference object collection, placing the reference object collection into the root reference object of the syntax tree root node, and returning the root reference object.

[0109] S13: Create a query executor based on the data source;

[0110] Specifically, based on the data source obtained in step S11, a corresponding query executor is created. The query executor is also an abstract interface that can be extended according to the object type in the query object. The query executors in the general data access framework include:

[0111] If it is a JDBC data source, call the execution configurator to obtain the JDBC executor, create a query transpiler, and obtain the target database type according to the JDBC data source, and register the writer corresponding to the target database type to the query transpiler;

[0112] If it is a JPA data source, call the execution configurator to obtain the JPA executor, create a query transpiler, and register the JPA writer to the query transpiler;

[0113] If it is a memory data source, directly obtain the memory executor.

[0114] It's important to note that when constructing query objects in the query builder, there's no need to consider differences between different database types or versions of the same database. Query objects are constructed using the same reference object, improving ease of use and development efficiency. During query execution, different writers perform different translation functions based on the database type and version. Because of the different database types and versions, one reference object may correspond to multiple writers. Therefore, when executing a query, it's necessary to select the appropriate writer based on the database type; this task is handled by the execution configurator.

[0115] The query transpiler created by the execution configurator is designed using the strategy pattern. The query transpiler registers multiple writers that support different database SQL syntaxes. It is worth noting that during registration, the writer for standard ANSI SQL is registered first, then the writer for handling differential syntax corresponding to the database type is registered, and finally the writer for handling special business scenarios is registered. Based on the same processable element type defined in the writer, the later registered writer will overwrite the previously registered writer.

[0116] Preferably, the general data access framework provides writers for the full set of ANSI SQL syntax elements, as well as writers for handling common database-differentiated syntax, which can ensure the quality of SQL conversion, facilitate developers' use, and improve development efficiency.

[0117] Specifically, writers share the same parent writer interface HWriter; each writer is used to translate a syntax element in the corresponding database SQL; each reference object corresponds to at least one writer. The operations performed by a writer include: obtaining the writer's processing type, preparing for translation, and writing the translation.

[0118] ① Obtain the writer processing type, which defines the element types that the writer can process, and corresponds to the SQL syntax element types defined in a reference object;

[0119] It should be noted that the element type defined in the writer corresponds to the element type returned by the method of obtaining the element type in a reference object. The element type of a reference object can correspond to multiple writers, that is, different writers can be used for translation according to different application scenarios.

[0120] ②Prepare for translation, which is used to transform the referenced objects in the query object according to the target database type using equivalent syntax, adjust the node connection relationship between the referenced objects, and obtain the processed query object;

[0121] For example, MySQL 5.7 and MySQL 8 are different versions, and therefore support different functions. If you want to rank the sales of each product, MySQL 8 can use the RANK function to achieve this. However, if you are using MySQL version 5.7, there is no RANK function provided; you can only obtain the ranking by defining variables and using a master subquery. In this case, you can define a custom function MY_RANK. This function obtains the ranking based on the input sorting direction and at least one column used for sorting. When constructing the syntax tree, the function MY_RANK is called through a function call reference object. Example code is as follows:

[0122] HQueryFacade.createQueryBuilder().startSelect()

[0123] .col("P", "PRODUCT_NAME", "Product Name")

[0124] .sel(HQueryFacade.getFunRef("MY_RANK",HQueryFacade.getCol(0,"TOTALS"),0),"RK")

[0125] .endSelect().startForm.tab("PRODUCT","P").endFrom().build();

[0126] Corresponding to the function call reference object is the function call writer HWriterFun, which inherits the HWriter interface and extends it with a method to get the function name. Based on the HWriterFun interface, different writers are implemented for different types and versions of databases, such as MySQL5MyRankFunWriter and MySQL8MyRankFunWriter. In the preparation and translation method of the MySQL8MyRankFunWriter writer, no special processing is required. In the write and translation method, it is simply converted into the SQL fragment of the Rank function. However, in the preparation and translation method of the MySQL5MyRankFunWriter writer, since the ranking function needs to be completed through a master-subquery and adding a ranking field, the writer converts a function reference object into multiple reference objects and reattaches them to the main query object to obtain the adjusted query object syntax tree.

[0127] ③ Write translation, used to generate the corresponding SQL fragment in the string builder of the current context based on the element type of the referenced object in the processed query object, and / or write values ​​in the parameter list buffer.

[0128] It is worth noting that if the writer is used to support JPA queries, it will generate HQL (Hibernate Query Language) compliant fragments.

[0129] It should be noted that the context includes a StringBuilder and a List of parameter lists, which are processed according to the writer's processing capabilities and the database type.

[0130] For example, the quotation marks for table names, table aliases, and column aliases differ in different types of databases. For instance, single quotes are used in MySQL databases, while double quotes are used in Oracle databases. Therefore, in the write translation method of the corresponding writer in MySQL, single quotes need to be added when generating the corresponding SQL fragment in the string builder, while double quotes need to be added in the write translation method of the corresponding writer in Oracle databases.

[0131] When performing queries, if the writer in the general data access framework cannot directly meet the business requirements, a new writer can be extended by inheriting the parent writer interface.

[0132] For example, the writer corresponding to a constant reference object is HCstWriterImpl. This writer is responsible for writing placeholders "?" into the context's string builder StringBuilder and simultaneously adding the corresponding parameter value to the context's parameter list buffer List. After translation, the generated SQL automatically includes placeholders, and the parameter values ​​in the parameter list buffer automatically match the placeholders one by one. If you don't want to generate SQL with placeholders and want the parameter values ​​to be directly concatenated into the SQL, you can extend a writer MyCstWriterImpl. In its implementation, the parameter values ​​are directly written to the current context's string builder StringBuilder without adding elements to the context's parameter list buffer. The SQL translated using this transpiler will then be without placeholders "?". Finally, you only need to register a new writer in the transpiler to replace the writer HCstWriterImpl corresponding to HCstRef.

[0133] S14: Pass the query object into the query executor, execute the query method, and obtain the query result.

[0134] It should be noted that the query object syntax tree constructed using a unified query logic is passed to the query executor, where it undergoes different processing within the query method. The JDBC and JPA executors have similar processing procedures, querying the SQL or HQL generated by the writer, while the in-memory executor primarily merges and filters the data set. Therefore, they are explained in two separate categories.

[0135] ① For data stored in the database, when the JDBC executor or JPA executor is called to execute a query, the writer corresponding to the root reference object of the query object is used as the entry writer for the query transpiler call. In the preparation transpilation and write transpilation, the query object syntax tree is traversed according to the defined traversal order. The string builder and parameter list buffer in each writer context are merged into the processed query object to obtain the complete SQL statement or HQL statement and parameter list. After execution, the query result is obtained.

[0136] Specifically, the query transpiler traverses the query object syntax tree during the transpilation preparation phase and the transpilation write phase, respectively. The traversal order is defined in the entry writer. The transpilation preparation phase is mainly used to adjust the syntax tree structure. It only needs to traverse the set of referenced objects on the syntax tree and each referenced object in the set. The traversal order is not strictly required. However, the transpilation write phase needs to merge the SQL fragments transpiled by multiple writers into a complete SQL statement and parameter list according to the SQL syntax specification. In the entry writer, the transpilation order of the syntax tree referenced objects is defined, thereby controlling the calling order of the writers corresponding to the referenced objects. Contexts are created to generate SQL fragments, and the merging order of SQL fragments and parameter lists is controlled by the context merging order.

[0137] Preferably, for MySQL databases, the table reference object set corresponding to FROM is translated first, and an SQL fragment is generated in the context. Then, the selection column reference object set corresponding to Select is translated, and an SQL fragment is generated in the context. When merging SQL fragments, the context written to Select is merged first, and then the context written to FROM is merged.

[0138] It should be noted that, based on the hierarchy of the syntax tree, the query transpiler's management of context involves multi-level context operations, including the following:

[0139] Create a context to generate a sub-context based on the current context. The sub-context includes: variables in the current context, variables independent of the sub-context, string builder, and parameter list buffer.

[0140] Merge context, used to merge the string builder and parameter list buffer in the child context into the parent context, and finally merge to generate a complete SQL statement and parameter list;

[0141] Reclaim contexts, which release idle contexts into the context resource pool in memory.

[0142] For example, a first-level context derives a second-level context, and a second-level context derives a third-level context. When contexts are merged, the SQL fragments and parameter lists in the third-level context are merged into the parent context. If the second-level context also contains written SQL fragments and parameters before merging the third-level context, then the content of the merged third-level context will definitely be after the fragments and parameters already written in the second-level context.

[0143] When creating a context, variables within that context are categorized into three levels: global variables, query object-level variables, and local variables; among them,

[0144] Global variables refer to variables that are shared across all contexts during the translation process;

[0145] Query object-level variables refer to variables that are independent but shareable within the context of the main and subqueries of the query object during the translation process;

[0146] For example, during the translation preparation phase, the adjusted syntax tree is written into the query object-level variable Map in the context of the subquery, and the main query can obtain the adjusted syntax tree structure of the subquery from the query object-level variable Map.

[0147] Local variables are variables that are passed down to child contexts and cannot be written back to the parent context using the context merging method.

[0148] ② For data stored in memory, when the memory executor is called to execute a query, the set of reference objects of the query object is obtained. First, the set of table reference objects is obtained, and the table reference objects are sorted and merged to obtain a wide table set. Then, the wide table set is filtered according to the Boolean condition reference object set. Next, the wide table set is sorted according to the sorted reference object set. Finally, the wide table set is grouped according to the grouped reference object set to obtain the query result.

[0149] It should be noted that the memory executor can use memory table objects in the memory data source to perform specified logical processing on their data. Based on the memory data source and the passed-in query object, it obtains the data to be queried, then filters, sorts, and groups the data to be queried to obtain the final query results.

[0150] Specifically, when retrieving data to be queried, various table join scenarios need to be considered, including executing the following methods:

[0151] Table sorting is used to traverse the set of table reference objects in the query object. According to the rule that the left table is the main table and the right table is the child table in the table association reference objects, the set of table reference objects is sorted to obtain the sorted set of table reference objects.

[0152] It should be noted that the order in which query object syntax trees are constructed is not important. However, when merging tables, the main table must be loaded first before the sub-tables can be merged based on the main table. Therefore, by using sorting methods, we can prevent developers from constructing sub-tables before the main table, which could lead to errors because the main table data cannot be found during subsequent table merging.

[0153] Table merging is used to traverse the sorted set of table reference objects, identify whether it contains table-related reference objects, and if it does not contain table-related reference objects, obtain the in-memory table object or constant dataset, merge the data objects in the form of a Cartesian product and put them into the wide table set. If it contains table-related reference objects, obtain the association relationship, association method, left table name and right table data from the child reference objects of the table-related reference objects, obtain the data objects from the wide table set according to the left table name, and merge the matching right table data objects into the wide table set according to the association method and association relationship.

[0154] It's important to note that because the tables are sorted first, during the merging process, the data from the main table (i.e., the left table) is first placed into a wide table collection. When handling table joins and references, the corresponding data object can be retrieved from the wide table collection based on the left table name. The wide table collection is a two-dimensional array format, where the number of rows represents the number of records to be queried, and each column in each row corresponds to an object. This method avoids directly storing the actual data information, significantly reducing storage space.

[0155] Preferably, a list is used.<Object[]> The format stores the merged wide table collection;

[0156] Based on the join methods (left join, right join, inner join, and full join), the data from the left and right tables is merged according to the query rules for data join methods in SQL. For example, taking left join as an example, based on the join relationship, the data objects in the right table that match the data objects in the left table are retrieved and placed into an object array in the same row as the data objects in the left table.

[0157] After merging the data to be queried, filtering the data involves obtaining a set of Boolean condition reference objects corresponding to the WHERE conditions in the query object, connecting the Boolean conditions in the set of Boolean condition reference objects with "AND" to form a single Boolean condition, encapsulating it into a Boolean condition reference object, converting the encapsulated Boolean condition reference object into a Java expression, traversing the wide table collection, and directly removing data objects that do not conform to the Java expression from the wide table collection.

[0158] It should be noted that since the data objects have already been merged into a wide table collection during table merging, the Boolean condition reference object collection is processed into a single Boolean condition reference object. This only requires traversing the wide table collection once. Moreover, each row of objects retrieved from the wide table collection can be used to determine all the filtering conditions, resulting in high processing efficiency and low performance overhead.

[0159] For example, the following conditions exist in the query object syntax tree:

[0160] Condition 1: startWhere().gtr(getCol(0,"ID"),getVal(1)).endWhere(); / / ID>1

[0161] Condition 2: startWhere().startOr().equ(getCol(0,"TYPE"),getVal(1))

[0162] .neq(getCol(0,"STATUS"),getVal(1))

[0163] .endOr().endWhere(); / / TYPE=1OR STATUS<>1

[0164] Construct Boolean condition reference objects for conditions 1 and 2 respectively, and add them to a collection of Boolean condition reference objects. Example code is as follows:

[0165] HBoolRef bool1=

[0166] new HBoolExpImpl(getCol(0,"ID"),GTR,getVal(1),null);

[0167] HBoolRefbool2=

[0168] new HBoolExpImpl(null,OR,Arrays.asList(

[0169] new HBoolExpImpl(getCol(0,"TYPE”),EQU,getVal(1),null),

[0170] new HBoolExpImpl(getCol(0,"STATUS”),NEQ,getVal(1),null),

[0171] ),null) (

[0172] List boolListOnArrays.asList <hboolref>(bool1,bool2);

[0173] Connect the boolean conditions in a collection of boolean condition reference objects using "and" to form a single boolean condition, and encapsulate it into a boolean condition reference object. Example code is shown below:

[0174] HBoolRef lastbool=new HBoolExpImpl(null,AND,boolList,null);

[0175] This is equivalent to combining the two conditions into: ID>1 AND (TYPE=1 OR STATUS<>1).

[0176] Then, the encapsulated Boolean condition reference object `lastbool` is converted into a Java expression. The wide table collection is then traversed, and data objects that do not conform to the Java expression are directly removed from the wide table collection.

[0177] Preferably, the Aviator expression evaluation engine is used to convert the Boolean condition reference object into a Java expression, which is then compiled into an intermediate object of Expression. During iteration, the execution method of Expression is called.

[0178] The filtered query data is sorted and grouped to obtain the final query results, including executing the following methods:

[0179] Retrieve the sorted collection of referenced objects in the query object, and sort the wide table collection using JDK sorting functions based on the chain of responsibility design pattern;

[0180] Retrieve the collection of grouped reference objects in the query object. Based on the grouping field defined in each grouped reference object, retrieve the data corresponding to the grouping field from the wide table collection in turn, calculate the hash value, and put the data with the same hash value into the same collection using the hash value as the key. After traversing, the final query result is obtained.

[0181] Preferably, when constructing the query object syntax tree, if pagination conditions or a maximum number of query results are set, the query results obtained after the above grouping can be further filtered to obtain the final query results.

[0182] The query results obtained after execution by different executors are encapsulated as dataset objects that implement the dataset interface, or as constant query reference objects for use in memory data queries.

[0183] Preferably, the data source and executor in a general data access framework are used in combination. For example, multiple query results obtained by a JDBC executor or JPA executor are encapsulated as in-memory table objects and placed in an in-memory data source, or encapsulated as constant query reference objects. Then, a query object syntax tree is constructed, the generated query object is passed to the in-memory executor, and the integration, filtering, grouping and sorting of multiple query results are realized.

[0184] Compared with existing technologies, this embodiment provides a data query method based on a general data access framework. It addresses the challenge of writing SQL for data in different storage formats using a unified approach, employing chained method calls to construct the query object syntax tree without considering SQL concatenation order. It also supports multi-level subquery nesting and the construction of complex SQL, reducing the difficulty of SQL construction. Only one query object needs to be constructed, and a single query logic can support different types and versions of databases, in-memory data, and constant datasets, improving code reusability, database compatibility, and reducing maintenance costs. A query translator centrally handles special conversions for different databases, preventing users from writing non-standard native SQL, avoiding SQL syntax errors, improving code quality, and reducing the risk of performance issues. Furthermore, by extending custom functions, reference objects, writers, executors, and datasets, it meets personalized usage needs.

[0185] Those skilled in the art will understand that all or part of the processes of the methods described in the above embodiments can be implemented by a computer program controlling related hardware, and the program can be stored in a computer-readable storage medium. The computer-readable storage medium may be a disk, optical disk, read-only memory, or random access memory, etc.

[0186] The above description is only a preferred embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any changes or substitutions that can be easily conceived by those skilled in the art within the scope of the technology disclosed in the present invention should be included within the scope of protection of the present invention.< / hboolref> < / hboolref>

Claims

1. A data query method based on a general data access framework, characterized in that, Includes the following steps: Based on the access method of the data to be queried, the data source is obtained, including: JDBC data source, JPA data source and in-memory data source; in the in-memory data source, the data to be queried is encapsulated as an in-memory table object and accessed in the form of a virtual physical table; Obtain the query conditions, call the query builder, and construct a query object syntax tree for the data to be queried through chained calls. The data of each SQL component is encapsulated as a reference object, which serves as a node on the object syntax tree; obtain the query object based on the query object syntax tree. Based on the data source, create a query executor, which includes a JDBC executor, a JPA executor, and a memory executor; if it is a JDBC data source and a JPA data source, create a query transpiler and register multiple writers that support different database SQL syntaxes in it; The query object is passed to the query executor, and the query results are obtained after the query method is executed. The query methods include: the JDBC executor and the JPA executor query the SQL or HQL generated by the writer, and the memory executor merges and filters the collection of reference objects of the obtained query object.

2. The data query method based on a general data access framework according to claim 1, characterized in that, The method of obtaining the data source based on the access method of the data to be queried includes: When the data to be queried is stored in a database and the project framework does not use Hibernate or JPA, the data to be queried can be accessed through the database table name and field name to obtain the JDBC data source; When the data to be queried is stored in a database and the project framework uses Hibernate or JPA, the data to be queried is encapsulated as an entity object and the JPA data source is obtained. When the data to be queried is stored in memory, the data to be queried is encapsulated into a memory table object, and the memory data source is obtained. When the data to be queried is stored in a constant dataset, the data to be queried is encapsulated into an in-memory table object or a constant query reference object to obtain the in-memory data source.

3. The data query method based on a general data access framework according to claim 2, characterized in that, The in-memory table object includes: a table alias, a data list, and field accessors; The data list includes attribute names and attribute values; The field accessor is used to specify one or more attribute names to be extracted from the data list; The constant query reference object implements the root reference interface and the dataset interface; The dataset interface includes methods for obtaining the dataset header and obtaining the data list. In the method for obtaining the data list, the constant query reference object stores the data in a two-dimensional array format; in the method for obtaining the dataset header, the attribute name of the data list is set as the header.

4. The data query method based on a general data access framework according to claim 3, characterized in that, Each node on the query object syntax tree is a reference object that implements the same parent reference interface. The parent reference interface includes a method for obtaining the element type, and the reference object defines the corresponding SQL syntax element type in the method for obtaining the element type. The root reference interface is a child reference interface derived from the parent reference interface; The query conditions are encapsulated as constant reference objects of SQL syntax elements, which serve as nodes on the query object syntax tree.

5. The data query method based on a general data access framework according to claim 4, characterized in that, The query builder includes multiple interfaces corresponding to SQL components. In each interface implementation class, methods starting with start and end indicate the start and end of constructing the SQL component. The construction of the query object syntax tree includes: calling multiple sets of methods starting with start and end in a chained manner, and attaching one or more reference objects to each set of methods starting with start and end; a query object syntax tree describes a complete SQL statement.

6. The data query method based on a general data access framework according to claim 5, characterized in that, The process of obtaining the query object based on the query object syntax tree includes: Place reference objects with the same element type on the syntax tree into the corresponding reference object collection; Place the collection of referenced objects into the root reference object of the syntax tree root node, and return the root reference object; The root reference object is the reference object that implements the root reference interface corresponding to the root node of the query object syntax tree.

7. The data query method based on a general data access framework according to any one of claims 1-6, characterized in that, The step of creating a query executor based on the data source includes: If it is a JDBC data source, call the execution configurator to obtain the JDBC executor, create a query transpiler, and obtain the target database type according to the JDBC data source, and register the writer corresponding to the target database type to the query transpiler; If it is a JPA data source, call the execution configurator to obtain the JPA executor, create a query transpiler, and register the JPA writer to the query transpiler; If it is a memory data source, directly obtain the memory executor.

8. The data query method based on a general data access framework according to claim 7, characterized in that, The writers have the same parent writer interface for performing the following operations: obtaining the writer processing type, preparing for translation, and writing the translation; wherein, The method of obtaining the writer processing type is used to define the element types that the writer can process, and corresponds to the element types defined in a reference object; The translation preparation is used to transform the referenced objects in the query object using equivalent syntax according to the target database type, adjust the node connection relationship between the referenced objects, and obtain the processed query object. The write translation is used to generate an SQL fragment corresponding to the target database type or an HQL fragment corresponding to JPA in the string builder of the context, based on the element type of the referenced object in the processed query object, and / or write values ​​in the parameter list buffer.

9. The data query method based on a general data access framework according to claim 8, characterized in that, The step of passing the query object into the query executor, executing the query method, and obtaining the query result includes: When a query is executed by calling the JDBC executor or JPA executor, the writer corresponding to the root reference object of the query object is used as the entry writer for the query transpiler call. In the preparation transpilation and write transpilation, the query object syntax tree is traversed according to the defined traversal order. The string builder and parameter list buffer in each writer context are merged into the processed query object to obtain the complete SQL statement or HQL statement and parameter list. After execution, the query result is obtained. When the memory executor is invoked to execute a query, the set of reference objects for the query object is obtained. First, the set of table reference objects is obtained, and the table reference objects are sorted and merged to obtain a wide table set. Then, the wide table set is filtered according to the Boolean condition reference object set. Next, the wide table set is sorted according to the sorted reference object set. Finally, the wide table set is grouped according to the grouped reference object set to obtain the query results.

10. The data query method based on a general data access framework according to claim 9, characterized in that, The query results are encapsulated as a dataset object that implements the dataset interface, or as a constant query reference object for use in memory data queries.

Images