CN115964385A - Data query method based on general data access framework - Google Patents

Abstract

The invention relates to a data query method based on a universal data access framework, belongs to the technical field of data access, and solves the problems that query logics of data in different storage forms are not uniform and processing logics are complex in the prior art. The method comprises the following steps: acquiring a data source based on an access mode of data to be queried; acquiring a query condition, calling a query constructor, constructing a query object syntax tree for data to be queried in a chain calling mode, and acquiring 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 a query executor, and executing a query method to obtain a query result. The data of different storage forms can be inquired by using a uniform inquiry method.

Description

Data query method based on general data access framework

Technical Field

The invention relates to the technical field of data access, in particular to a data query method based on a general data access framework.

Background

With the improvement of data storage capacity, various databases are generated, and enterprises can adopt diversified storage forms in the face of increasingly complex requirements. In supporting flexible data query and analysis work, data from different sources need to be processed, and how to rapidly develop and meet query requirements of different data is a problem of paying more attention when developing a system.

For a relational database, an existing open source framework encapsulates a data Query method, so that developers can Query data and encapsulate SQL (Structured Query Language) statements such as Hibernate, JPA (Java Persistence API) and Mybatis, and for complex service data acquisition, SQL conforming to the syntax of the database is often directly compiled for Query. For data directly stored in a memory or data of a memory database, generally, an object corresponding to the memory data is obtained first, a specific value is obtained by a get method, or the memory data is put in a set, and the data is processed by an operation set method.

When an object-oriented query mode in an open source framework is adopted, the method is generally suitable for simple data query, and developers need to construct corresponding business objects according to query business types; when the SQL is packaged or directly written, different types of databases or different versions of some databases have different SQL grammars. Multiple sets of SQL are often packaged to meet different syntax requirements according to the type of the database, and problems such as SQL injection, SQL splicing sequence, matching of SQL dynamic parameters and placeholders and the like need to be noticed; if a plurality of object data in the memory data need to be correlated and compared, or the object data in the memory and the data acquired in the database are interactively matched with the data in the file, developers need to acquire the required memory data according to specific requirements, and then process the memory data and other data through a writing program. Moreover, if the project needs to process both the data of the database and the memory data, developers need to adopt various development modes, so that the development efficiency is influenced, the customized and developed codes cannot be reused, and the code quality and the query mode are not controllable.

Disclosure of Invention

In view of the foregoing analysis, embodiments of the present invention are directed to providing a data query method based on a universal data access framework, so as to solve the problems that query logics of data in different storage forms are not uniform and processing logics are complex.

The embodiment of the invention provides a data query method based on a universal data access framework, which comprises the following steps:

acquiring a data source based on an access mode of data to be queried;

acquiring a query condition, calling a query constructor, constructing a query object syntax tree for data to be queried in a chain calling mode, and acquiring a query object based on the query object syntax tree;

creating a query executor according to a data source;

and transmitting the query object into a query executor, and executing a query method to obtain a query result.

Based on the further improvement of the method, the data source is obtained based on the access mode of the data to be queried, and the method comprises the following steps:

when the data to be queried is stored in the database and the project frame does not adopt Hibernate or JPA, accessing the data to be queried through the table name and the field name of the database to acquire a JDBC data source;

when the data to be queried is stored in the database and the project framework adopts Hibernate or JPA, packaging the data to be queried into an entity object and acquiring a JPA data source;

when the data to be queried is stored in the memory, packaging the data to be queried into a memory table object, and acquiring a memory data source;

and when the data to be queried is stored in the constant data set, packaging the data to be queried into a memory table object or a constant query reference object, and acquiring a memory data source.

Based on the further improvement of the method, the memory table object comprises: table aliases, data lists, and field accessors;

the data list comprises attribute names and attribute values;

the field accessor is used for specifying 1or more attribute names to be extracted from the data list;

the constant query reference object realizes a root reference interface and a data set interface;

the data set interface comprises a method for acquiring a data set header and a data list, and the constant query reference object stores data in a two-dimensional array format in the method for acquiring the data list; and setting the attribute name of the data list as a header in the method for acquiring the header of the data set.

Based on the further improvement of the method, each node on the query object syntax tree is a reference object which realizes the same father reference interface, the father reference interface comprises a method for acquiring element types, and the reference object defines the corresponding SQL syntax element type in the method for acquiring the element types;

the root reference interface is a child reference interface derived from the parent reference interface;

the query condition is packaged as a reference object with the SQL syntax element type being constant, and the reference object is used as a node on a query object syntax tree.

Based on the further improvement of the method, the query constructor comprises a plurality of interfaces corresponding to the SQL components, and the start and the end of the SQL components are represented by a method starting from start and end in each interface implementation class;

constructing a query object syntax tree comprising: calling a plurality of groups of start and end starting methods in a chain calling mode, and hooking 1or more reference objects in each group of start and end starting methods; a query object syntax tree describes a complete SQL statement.

Based on the further improvement of the method, the query object is obtained based on the query object syntax tree, and the method comprises the following steps:

placing the reference objects with the same element type on the syntax tree in the corresponding reference object set;

putting the reference object set into a root reference object of a root node of the syntax tree, and returning the root reference object;

the root reference object is a reference object which realizes a root reference interface and corresponds to a root node of the query object syntax tree.

In a further improvement of the above method, creating a query executor from a data source includes:

if the target database type is the JDBC data source, calling an execution configurator to obtain a JDBC executor, creating a query translator, obtaining the target database type according to the JDBC data source, and registering a writer corresponding to the target database type into the query translator;

if the JPA data source is the JPA data source, calling an execution configurator to acquire a JPA actuator, creating a query translator, and registering a JPA writer in the query translator;

and if the data source is the memory data source, directly acquiring the memory executor.

Based on a further refinement of the above method, the writers have the same parent writer interface for performing the following operations: get writer process type, prepare translation and write translation; wherein the content of the first and second substances,

acquiring writer processing type, which is used for defining element type that can be processed by writer and is corresponding to element type defined in a reference object;

preparing translation, converting reference objects in the query objects by using equivalent syntax according to the type of the target database, and adjusting node hanging relations among the reference objects to obtain the processed query objects;

and the writing translation is used for generating an SQL (structured query language) fragment corresponding to the target database type or an HQL (Java query language) fragment corresponding to the JPA (Java query language) in a context string builder according to the element type corresponding to the reference object in the processed query object, and/or writing a value in a parameter list buffer area.

Based on the further improvement of the method, the query object is transmitted into the query executor, and the query result is obtained after the query method is executed, wherein the method comprises the following steps:

when a JDBC executor or a JPA executor is called to execute a query, a writer corresponding to a root reference object of a query object is used as an entry writer called by a query translator, in preparation of translation and write translation, a query object syntax tree is traversed according to a defined traversal sequence, a character string builder and a parameter list buffer area in the context of each writer are merged for the processed query object, a complete SQL statement or HQL statement and a parameter list are obtained, and a query result is obtained after execution;

when a memory executor is called to execute query, acquiring each reference object set of a query object, acquiring a table reference object set, sequencing and combining the table reference objects to obtain a wide table set, then screening the wide table set according to the Boolean condition reference object set, sequencing the wide table set according to the sequencing reference object set, and finally grouping the wide table set according to the grouped reference object set to obtain a query result.

Based on the further improvement of the method, the query result is packaged into a data set object realizing a data set interface, or is packaged into a constant query reference object for memory data query.

Compared with the prior art, the invention can realize at least one of the following beneficial effects:

1. the query object syntax tree is constructed by using chain method calling according to the unified SQL writing thinking, the SQL splicing sequence is not required to be considered, multi-level sub-query nesting is supported, the construction of complex SQL is supported, and the SQL constructing difficulty is reduced;

2. only one query object is needed to be constructed, and a set of query logic is provided to support databases, memory data and constant data sets of different types and versions, so that the code reuse degree and the database compatibility are improved, and the maintenance cost is reduced;

3. special conversion of different databases is processed in a centralized manner through the query translator, so that a user is prevented from compiling irregular native SQL, SQL syntax errors are avoided, the code quality is improved, and the risk of performance problems is reduced;

4. the personalized use is met by expanding the self-defined function, the reference object, the writer, the actuator and the data set;

in the invention, the technical schemes can be combined with each other to realize more preferable combination schemes. Additional features and advantages of the invention will be set forth in the description which follows, and in part will be obvious from the description, or may be learned by practice of the invention. The objectives and other advantages of the invention will be realized and attained by the structure particularly pointed out in the written description and drawings.

Drawings

The drawings are only for purposes of illustrating particular embodiments and are not to be construed as limiting the invention, wherein like reference numerals are used to designate like parts throughout.

Fig. 1 is a flowchart of a data query method based on a generic data access framework in an embodiment of the present invention.

Detailed Description

The accompanying drawings, which are incorporated in and constitute a part of this application, illustrate preferred embodiments of the invention and together with the description, serve to explain the principles of the invention and not to limit the scope of the invention.

A specific embodiment of the present invention discloses a data query method based on a universal data access framework, as shown in fig. 1, including the following steps:

s11: acquiring a data source based on an access mode of data to be queried;

it should be noted that the generic access framework is integrated into a development project as a tool Jar, wherein different data sources are provided, including JDBC data source, JPA data source and memory data source, for supporting data queries in different storage forms.

The data to be queried refers to all data related to query in the business system, and the access mode of the data to be queried is determined according to the storage form and the project framework of the data to be queried, so that a corresponding data source is obtained.

Specifically, when the data to be queried is stored in the database and the project framework does not adopt Hibernate or JPA, accessing the data to be queried through the table name and the field name of the database to acquire a JDBC data source, namely establishing connection with the database through database driving;

when the data to be queried is stored in the database and the project framework adopts Hibernate or JPA, packaging the data to be queried as an entity object, and acquiring a JPA data source, namely configuring the connection of the database through the JPA;

it should be noted that, in this case, the JDBC data source may also be acquired, but it is suggested to acquire the JPA data source, so that the problem of inconsistency between the hibernate cache and the database caused by directly reading and writing the database may be avoided.

When the data to be queried is stored in the memory, packaging the data to be queried into a memory table object, and acquiring a memory data source;

and when the data to be inquired is stored in the constant data set, packaging the data to be inquired into a memory table object or a constant inquiry reference object, and acquiring a memory data source.

It should be noted that there are various sources of memory data, for example, memory from a business system and a memory database, and the processing of the memory data is not only the memory data, but also data in a file can be associated, a result set obtained by database query can be associated, and the data are packaged as a memory table object, so that the memory table object can be accessed in a virtual physical table form through a memory data source when the data are queried or associated.

Specifically, the memory table objects include: table aliases, data lists, and field accessors, override if the table alias is the same as an existing table alias in the in-memory data source. Wherein, the data list comprises attribute names and attribute values, and the field accessor is used for specifying 1or more attribute names needing to be extracted from the data list. When a plurality of attribute data exist in the data list and only part of the attribute data need to be accessed, the required data can be directly selected and placed into the memory data source through the field accessor without manual processing.

For a part of constant data sets, the data sets are not required to be placed into a memory data source, but are required to be used in data query, the data sets can be packaged into a constant query reference object, and when a subsequent query object is constructed, the object name of the constant query reference object is used as a virtual table name.

S12: acquiring a query condition, calling a query constructor, constructing a query object syntax tree for data to be queried in a chain calling mode, and acquiring a query object based on the query object syntax tree;

it should be noted that the query builder is built according to the idea of writing SQL, and it is considered that the components of an SQL statement include: selecting a column name Select, querying a table name From, filtering a condition Where, grouping a field Group by, ordering a condition orderbby, grouping a condition Having, and table merging an all/union, so that a plurality of interfaces including Select, from, where, group, order, having and Merge are combined in a query constructor, which respectively correspond to the components of SQL, and each interface implementation class represents the beginning and the end of constructing SQL components by a start and end beginning method.

Specifically, the query builder in the generic access framework provides the following methods:

startSelect for setting a selection column name; endSelect, used to indicate that the currently selected column name setting is complete;

startFrom, for setting up a table for query; endForm, used to represent the completion of the setting of the currently queried table;

startWhere for setting the filtering condition; the end wheel is used for indicating that the current filtering condition setting is finished;

startGroup for setting grouping field; endGroup for indicating the completion of setting the current grouping field;

startOrder for setting sort field and sort order; endOrder for indicating the completion of the current sorting field and sorting order setting;

startHaving for setting grouping conditions; endHaving for indicating that the current grouping condition setting is completed;

startMerge for setting table merging; endMerge for indicating that the current merge setting is completed;

a page for setting a paging condition of the query;

limit, which is used for setting the maximum number of the query results;

it should be noted that the page and limit methods are mutually exclusive, the other methods are called without sequence, the methods are called according to the SQL syntax combination, and after the construction is completed, the build method is called to create and generate a query object;

it should be noted that a syntax tree can be constructed by using a statement or multiple statements, and a syntax tree describes a complete SQL statement. When a plurality of methods are called in a combined mode in a statement to construct a plurality of SQL components, the methods at the beginning of each start and end are called in pairs, if each statement constructs only one component of SQL through a plurality of statements, only the method at the beginning of the start can be called, and the code line feed is equivalent to the method at the beginning of the end being called. When the logic is very complicated, the SQL can be freely constructed, so that developers can focus attention on the business without worrying about the sequence relation of splicing various components of the SQL, whether brackets are matched, whether SQL injection attack exists, whether grammar conforms to a target database and the like.

Illustratively, a client name with a client ID greater than 1 needs to be queried, if a SQL statement is used, denoted AS "SELECT c.custom memrane AS client name FROM custom builder C WHERE c.id >1", if a query builder is used, a query builder is created by the hquueryfacede. Createquerybuilder () method, which can be built into a syntax tree with a statement, with the example code AS follows:

HQuery query＝

hquueryfacade. Createquerybuilder ().startfrom (). Tab ("cusomer", "C"). EndFrom (). StartSelect (). Col (0, "cusomername", "CUSTOMER name"). EndSelect (). Startwheel (). Gtr (getCol (0, "ID"), getVal (1)). Endwheel (). Build ();

the SQL statements may also be constructed as a syntax tree using a plurality of statements, and example codes are as follows:

HQueryBuilder querybuilder＝HQueryFacade.createQueryBuilder().startFrom().tab(“CUSTOMER”,“C”).endFrom()；

querybuilder startselect (). Col (0, "CUSTOMERNAME", "customer name");

querybuilder.startWhere().gtr(getCol(0,“ID”),getVal(1))；

HQuery query＝querybuilder.build()；

when the query constructor constructs the SQL components, the data of each component is packaged as a reference object, the reference object is used as a node on the syntax tree and is placed in a reference object set corresponding to each component, and the root node of the syntax tree also has a corresponding reference object HQery and contains the reference object sets of each child node.

Preferably, the set of reference objects is of the ArrayList type.

The reference object describes SQL components by using SQL syntax elements, and comprises a table reference object, a selection column reference object, a grouping reference object, a sorting reference object, a Boolean condition reference object, a field reference object, a constant reference object, a function call reference object and the like, wherein the reference objects realize the same parent reference interface HValRef, and each reference object comprises a method for acquiring an element type and is used for defining the SQL syntax element type corresponding to the reference object.

In the above example code, a table reference object is constructed by a tab ("CUSTOMER", "C") method, where "CUSTOMER" is the table name in the table reference object and "C" is the table alias in the table reference object, which is to be placed in the table reference object set; constructing a selective column reference object by a col (0, CUSTOMERNAME and customer name) method, wherein "0" is a table index to which a column belongs, namely a 1 st table, "CUSTOMERNAME" is a column name and "customer name" is a column name, and the selective column reference object is placed into a selective column reference object set selList; a boolean reference object is constructed by gtr (getCol (0, "ID"), getVal (1)), where gtr is the operator of the boolean condition, indicating ">, getCol (0," ID ") is the left side of the boolean condition, indicating that the compared field is the" ID "field of the 1 st table, getVal (1) is the right side of the boolean condition, indicating that the comparison value is" 1", and the boolean reference object is placed in a boolean reference object set boolean list.

Preferably, the Boolean conditional reference object supports 2 right values, considering that there will be 2 alignment values when using the between operator.

It should be noted that, many child referencing interfaces are derived from a parent referencing interface, and each referencing object directly implements a corresponding child referencing interface, so that a new method can be added while inheriting a method in the parent referencing interface. Taking a Boolean conditional reference object as an example, an example code for constructing the reference object and placing the reference object into a corresponding reference object set is as follows, wherein HBoolExpIml is an implementation class of a Boolean conditional reference interface HBoolRef, and HBoolRef is a child reference interface derived from a parent reference interface HValRef:

v/construct Boolean conditional reference object

HBoolRefbool＝

new HBoolExpImpl(getCol(0,“ID”),GTR,getVal(1),null)；

Putting Boolean conditional reference objects into a Boolean conditional reference object set

ListboolList＝Arrays.asList<HBoolRef>(bool)；

The reference object corresponds to an SQL syntax element, and comprises: the column SEL, the field COL, the table FRM, the grouping GRP, and the constant CST, etc., it should be noted that the element type supports customization, such as defining "CST _ USER" as an extended constant reference, which distinguishes different processing manners for different types or versions of databases from the common CST. For queries of memory data, new element types can be extended if complex function processing is involved.

And for the query conditions transmitted in the query process, the query conditions are directly used in a query object syntax tree in the form of variable names and are packaged into reference objects with SQL syntax element types being constants to serve as nodes on the query object syntax tree.

It should be noted that, in the prior art, when a dynamic incoming query condition is required in an SQL statement, JDBC placeholder "? "represents the dynamic parameters in SQL, but when the query logic is complex, developers are difficult to match placeholders with corresponding parameter values one by one when constructing SQL, and are difficult to troubleshoot when problems occur. Although Hibernate can store variables and parameter values in Map, when the variables are more or the same variable is used in SQL for many times, the variable name and sequence number are used to prevent the variable name from being repeated, and the problem is difficult to be solved. In the embodiment, the query builder constructs the syntax tree by hanging the reference object in each component, the dynamic parameter can be used as a node on the syntax tree and defined by the constant reference object for realizing the HValRef interface, and the problem of placeholders does not need to be considered during construction.

The constant data set which is not placed in the memory data source corresponds to the constant query reference object which can be used as a sub query data set of the sub query reference object, and the constant query reference object simultaneously realizes a root reference interface and a data set interface. The root reference interface is a child reference interface derived from a parent reference interface and is an interface realized by a root reference object, and the constant query reference object can be hung on a query object syntax tree by realizing the interface; the data set interface comprises a method for acquiring a data set header and a method for acquiring a data list, wherein the constant query reference object sets the attribute name of the data list as the header in the method for acquiring the data set header, and the data list is stored in a two-dimensional array format in the method for acquiring the data list.

Specifically, the DataSet object DataSet interface inherits the List interface, and two methods are added for respectively acquiring the query result set and acquiring the metadata of the query result; wherein the query result set is stored in a two-dimensional array format List < Object [ ] >. The structure is convenient for managing the data results, for example, when renaming the columns of the data results, only the metadata of the query results need to be modified, so that the modification efficiency is greatly improved, and the space required by storage can be greatly reduced by using the two-dimensional array to store the query result set.

Any reference object which realizes a father reference interface can be connected to the syntax tree, and each reference object can be connected to other reference objects and can also be connected to a child syntax tree, so that infinite expansion of the syntax tree is realized, and the construction difficulty of SQL can be effectively reduced.

Illustratively, the field to be queried is a field in another table, and example codes are as follows:

querybuilder. StartSelect (). Val (hquueryfactor. Createquerybuilder (). StartSelect (). Col (0, "total"). EndSelect (). Tab ("PRODUCT", "P"). EndFrom (). Build (), "sales");

considering that a query is usually a multi-table association, including leftjoin, rightjoin, innerjoin, and fulljoin, it is necessary to derive the reference object as a sub-reference object. Preferably, the table is referred to the object HFrmRef, and a main table name HFrmNRef is derived, which is used to indicate that the association is directly performed according to the table name; the sub-query reference object HFrmQRef is used for indicating that one sub-query result is used as a table to be associated; the table association refers to an object HFrmJRef for defining the left table name, the association manner and the association condition of the association. When the table is associated, the table name can be directly associated, and the sub-table can be associated and inquired, so that an associated table name reference object HFrmJNRef is derived based on the three derived sub-interfaces and is used for indicating that the associated right table is the direct table name; the associated sub-table references the object HFrmJQRef to indicate that the associated right table is a sub-query.

Illustratively, the a table and the B table are connected with the BID of the B table through the AID of the a table, and example codes when constructing the syntax tree are as follows:

querybuilder startfrom (). Tab ("a", "a table"). JOIN (0, "AID", jointype. Left _ JOIN, "B", "B table", "BID"). EndFrom ();

in the code, a main table name reference object HFrmNRef is constructed by a tab ("A", "A table") method, and an association table name reference object HFrmJNRef is constructed by a JOIN (0, "AID", jointType. LEFT _ JOIN, "B", "B table", "BID") method, wherein the table reference object HFrmRef, the association mode JointType and the Boolean condition reference object HBoolRef are contained, and the table reference object HFrmRef, the association relation and the association condition are respectively used for defining the reference object, the association relation and the association condition of an associated left table.

And acquiring the query object based on the constructed query object syntax tree, namely putting the reference objects with the same element types on the syntax tree into the corresponding reference object set, putting the reference object set into the root reference object of the syntax tree root node, and returning the root reference object.

S13: creating a query executor according to a data source;

specifically, a corresponding query executor is created according to the data source obtained in step S11, where the query executor is also an abstract interface and can be extended according to the object type in the query object, and the query executor in the universal data access framework includes:

if the target database type is the JDBC data source, calling an execution configurator to obtain a JDBC executor, creating a query translator, obtaining the target database type according to the JDBC data source, and registering a writer corresponding to the target database type into the query translator;

if the JPA data source is the JPA data source, calling an execution configurator to acquire a JPA actuator, creating a query translator, and registering a JPA writer in the query translator;

and if the data source is the memory data source, directly acquiring the memory executor.

It should be noted that, when the query constructor constructs the query object, the query object is constructed by the same reference object without considering the difference between different database types and different versions of the same database, so that the convenience of use and the development efficiency are improved. When the query is executed, different translation functions are completed by different writers according to the type and the version of the database, because one reference object corresponds to a plurality of writers due to the difference between the type and the version of the database, so that when the query is executed, the corresponding writer needs to be selected according to the type of the database, and the operation is completed by an execution configurator.

The query translator created by the execution configurator is designed by adopting a strategy mode, a plurality of writers supporting different database SQL grammars are registered in the query translator, and the writers for processing the differentiated grammar corresponding to the database type are registered first during registration, and finally the writers for processing the special service scene are registered, and the writers for processing the previous registration can be covered by the postregistration writers according to the same processable element type defined in the writers.

Preferably, writers corresponding to ANSI SQL full set syntax elements and writers for processing common database differential syntax are provided in the universal data access framework, so that the quality of SQL conversion can be ensured, developers can use the universal data access framework conveniently, and the development efficiency is improved.

Specifically, the writers have the same parent writer interface, HWriter; each writer is used for translating a syntax element in the corresponding database SQL; each reference object corresponds to at least one writer. Writers perform operations including: get writer process type, prepare translation and write translation;

(1) acquiring a writer processing type, which is used for defining element types that can be processed by a writer and corresponds to an SQL syntax element type defined in a reference object;

it should be noted that the element type defined in the writer corresponds to the element type returned by the method for obtaining the element type in one reference object, and the element type of one reference object may correspond to multiple writers, that is, the element type may be translated by using different writers according to different application scenarios.

(2) Preparing translation, converting reference objects in the query objects by using equivalent syntax according to the type of the target database, and adjusting node hanging relations among the reference objects to obtain the processed query objects;

illustratively, mySQL5.7 and MySQL8, different versions, support different functions. If the sales ranking of each product is to be counted, for MySQL8, the RANK function can be used to fulfill this requirement, but if the MySQL version is 5.7, the RANK function is not provided and only the definition variables and the main sub-queries can be used to obtain the ranking. In this case, a function MY _ RANK can be customized, which gets ranked according to the input sort direction and at least one column for sorting, and the function MY _ RANK is called by a function call reference object when constructing the syntax tree, and example code is as follows:

HQueryFacade.createQueryBuilder().startSelect()

col ("P", "PRODUCT _ NAME", "PRODUCT NAME")

.sel(HQueryFacade.getFunRef(“MY_RANK”,HQueryFacade.getCol(0,“TOTALS”),0),“RK”)

.endSelect().startForm.tab(“PRODUCT”,“P”).endFrom().build()；

The function call writer HWriterFun corresponds to the function call reference object, the function call writer HWriter interface is inherited, and a method for acquiring a function name is expanded, so that different writers are realized aiming at different types and versions of databases based on the HWriterFun interface, such as MySQL5MyRankFunWriter and MySQL8MyRankFunWriter, special processing is not needed in a preparation translation method of the MySQL8MyRankFunWriter writer, and a SQL segment converted into a Rank function can be used in a writing translation method, but in the preparation translation method of the MySQL5MyRankFunWriter writer, as a function is completed by main sub-ranking query and adding of ranking fields, one function reference object is converted into a plurality of reference objects to be hung in a main query object again to obtain an adjusted query object syntax tree.

(3) And the writing translation is used for generating a corresponding SQL fragment in the character string builder of the current context according to the element type corresponding to the reference object in the processed query object and/or writing a value in the parameter list buffer.

Notably, if writers are used to support JPA queries, then fragments conforming to HQL (Hibernate Query Language) are generated.

It should be noted that the context includes a string builder StringBuilder and a parameter List buffer List, and the processing is performed according to the processing function of the writer and the database type.

For example, the quotation marks of the table names, table aliases and column aliases in different types of databases are different, for example, the quotation marks in the MySQL database need single quotation marks, and the Oracle database needs double quotation marks, so in the write translation method of the writer corresponding to MySQL, a single quotation mark needs to be added when the corresponding SQL segment is generated in the character string builder, and in the write translation method of the writer corresponding to Oracle, a double quotation mark needs to be added when the corresponding SQL segment is generated in the character string builder.

When inquiring, if the writer in the universal data access frame can not directly meet the service requirement, the new writer can be extended by inheriting the interface of the parent writer.

Illustratively, the writer to which the constant reference object corresponds has hcstwritermipl, which is responsible for writing a placeholder "? Meanwhile, the parameter value corresponding to the placeholder is added into the parameter List buffer List of the context, after translation, the generated SQL is automatically provided with the placeholder, and the parameter values in the parameter List buffer are automatically matched with the placeholder one by one. If we do not want to create SQL with placeholders, we can extend a writer MyCstWriterImpl, which is implemented by writing parameter values directly into string builder StringBuilder of the current context, without adding elements to the parameter list buffer in the context, and we can translate SQL without placeholders using this translator? "is. Finally, only a new writer needs to be registered in the translator to replace the writer HCstWriterImpl corresponding to HCstRef.

S14: and transmitting the query object into a query executor, and executing a query method to obtain a query result.

It should be noted that, by using a query object syntax tree constructed by a uniform query logic, the generated query object is transmitted to the query executor, and the corresponding query executor performs different processing in the query method. The JDBC executor and the JPA executor have similar processing processes, query SQL or HQL generated by writer translation, and the memory executor mainly merges and screens data sets, and thus is described in two categories.

(1) For data stored in a database, when a JDBC executor or a JPA executor is called to execute a query, a writer corresponding to a root reference object of the query object is used as an entry writer called by a query translator, in preparation of translation and write translation, a query object syntax tree is traversed according to a defined traversal sequence, a character string builder and a parameter list buffer area in the context of each writer are merged for the processed query object, a complete SQL statement or HQL statement and a parameter list are obtained, and a query result is obtained after execution.

Specifically, the query translator performs a traversal of the query object syntax tree during a translation preparation period and a translation writing period, respectively, the traversal order being defined in the entry writer. The translation preparation period is mainly used for adjusting a syntax tree structure, and only needs to traverse a reference object set on the syntax tree and each reference object in the set, the traversal order has no strict requirement, but the translation writing period needs to merge SQL fragments generated by translating a plurality of writers into a complete SQL statement and parameter list according to an SQL syntax specification, the translation order of the reference object of the syntax tree is defined in an entry writer, so that the calling order of writers corresponding to the reference object is controlled, the SQL fragment is generated by creating a context, and the merging order of the SQL fragment and the parameter list is controlled by the context merging order.

Preferably, for the MySQL database, the table reference object set corresponding to From is translated, the SQL segment is generated in the context, the selection column reference object set corresponding to Select is translated, the SQL segment is generated in the context, and when the SQL segment is merged, the context written with Select is merged first, and then the context written with From is merged.

It should be noted that, according to the hierarchy of the syntax tree, the management of the context by the query translator involves operations of multiple levels of contexts, including the following operations:

creating a context for generating a subcontext from the current context, the subcontext comprising: variables in the current context, subcontext independent variables, string builders and parameter list buffers;

merging the contexts, namely merging the character string constructor and the parameter list buffer area in the subcontext into the parent context, and finally merging to generate a complete SQL statement and parameter list;

and recovering the context, wherein the context is used for releasing the free context to the context resource pool in the memory.

Illustratively, the context of the first level derives a second level context, the second level context derives a third level context, and when the contexts are merged, the SQL segments and the parameter list in the third level context are merged onto the context of the upper level. If there are also written SQL fragments and parameters in the secondary context before merging the tertiary context, then the contents of the merged tertiary context must be behind the fragments and parameters that the secondary context has written.

When creating a context, the variables in the context are divided into three levels: global variables, query object level variables and local variables; wherein, the first and the second end of the pipe are connected with each other,

global variable, refers to a variable that is shared among all contexts in the translation process;

query object level variables, which refer to variables that are independent but sharable, respectively, in the context of the main sub-query that queries the object during translation;

illustratively, during the translation preparation period, the adjusted syntax tree is written into the query object level variable Map in the context of the sub-query, and the main query may retrieve the sub-query adjusted syntax tree structure from the query object level variable Map.

The local variable refers to a variable which is transferred to the child context step by step and cannot be written back to the parent context by a context merging method.

(2) For data stored in a memory, when a memory executor is called to execute query, each reference object set of a query object is obtained, a table reference object set is obtained first, the table reference objects are sorted and combined to obtain a wide table set, the wide table set is screened according to the Boolean condition reference object set, the wide table set is sorted according to the sorted reference object set, and finally the wide table set is grouped according to the grouped reference object set to obtain a query result.

It should be noted that the memory executor may perform specified logical processing on its data using the memory table object in the memory data source. And acquiring data to be queried according to the memory data source and the transmitted query object, screening the data to be queried, sorting and grouping the data to be queried, and obtaining a final query result.

Specifically, when acquiring data to be queried, various table association scenarios need to be considered, including the following method:

the table sorting is used for traversing the table reference object set in the query object, sorting the table reference object set according to a rule that a left table in the table related reference object is a main table and a right table is a sub table and is sorted, and obtaining the sorted table reference object set;

it should be noted that, when constructing the query object syntax tree, the order is not separated, and when merging the tables, the main table needs to be loaded first, and the sub-tables can be merged based on the main table.

The table merging is used for traversing the sorted table reference object set, identifying whether a table associated reference object is contained or not, if the table associated reference object is not contained, acquiring a memory table object or a constant data set, merging the data objects in a Cartesian product form, and then putting the merged data objects into a wide table set; and if the table association reference object is contained, acquiring the association relationship, the association mode, the associated left table name and the right table data from the sub reference object of the table association reference object, acquiring the data object from the wide table set according to the left table name, and merging the matched right table data object into the wide table set according to the association mode and the association relationship.

Since table sorting is performed first, left table data, which is a main table, is first put into the wide table set when merging, and corresponding data objects can be obtained from the wide table set according to the left table name when performing table-related reference object processing. The wide table set is in a two-dimensional array format, the number of rows of the two-dimensional array represents the number of records of data to be queried, and each column corresponding to each row is an object.

Preferably, the merged wide table set is stored in a list < Object [ ] > format;

and merging the data of the left table and the right table according to the left join, the right join, the inner join and the full join in the association mode according to the query rule of the data association mode in the SQL. Illustratively, taking leftjoin as an example, according to the association relationship, taking out the data object in the right table that matches the left table, and placing the data object in the object array in the same row as the data object in the left table.

After the data to be queried is obtained through combination, the data to be queried is screened, namely a Boolean condition reference object set corresponding to a where condition in a query object is obtained, boolean conditions in the Boolean condition reference object set are connected into a Boolean condition through 'and', the Boolean condition reference object is packaged into a Boolean condition reference object, the packaged Boolean condition reference object is converted into a Java expression, the wide table set is traversed, and data objects which do not conform to the Java expression are directly removed from the wide table set.

It should be noted that, because the data objects have been merged into the wide table set in the table merging, the boolean condition reference object set is processed into one boolean condition reference object, and the wide table set only needs to be traversed once, and every time one row of objects in the wide table set is fetched, all the screening conditions can be determined, so that the processing efficiency is high and the performance consumption is low.

Illustratively, the following conditions exist in the query object syntax tree:

condition 1: startWhere (). Gtr (getCol (0, "ID"), getVal (1)). EndWhere (); // ID >1

Condition 2: startWhere (). StartOr (). Equ (getCol (0, "TYPE"), getVal (1))

.neq(getCol(0,“STATUS”),getVal(1))

.endOr().endWhere()；//TYPE＝1OR STATUS<>1

A boolean condition reference object is constructed for condition 1and condition 2, respectively, and placed into a boolean condition reference object set, with example code as follows:

HBoolRef bool1＝

new HBoolExpImpl(getCol(0,“ID”),GTR,getVal(1),null)；

HBoolRefbool2＝

new HBoolExpImpl(null,OR,Arrays.asList(

new HBoolExpImpl(getCol(0,“TYPE”),EQU,getVal(1),null),

new HBoolExpImpl(getCol(0,“STATUS”),NEQ,getVal(1),null),

),null)；

List boolList＝Arrays.asList<HBoolRef>(bool1,bool2)；

the Boolean conditions in the Boolean condition reference object set are connected into a Boolean condition through 'and', and are packaged into a Boolean condition reference object, and example codes are as follows:

HBoolRef lastbool＝new HBoolExpImpl(null,AND,boolList,null)；

equivalent to combining 2 conditions into: ID >1AND (TYPE =1OR STATUS < > 1).

And then converting the encapsulated Boolean conditional reference object lastwood into a Java expression, traversing the wide table set, and directly removing the data object which does not conform to the Java expression from the wide table set.

Preferably, the Boolean conditional reference object is converted into a Java Expression by adopting an Aviator Expression evaluation engine, then the Java Expression is compiled into an intermediate object of the Expression, and an execution method of the Expression is called during traversal.

Sorting and grouping the screened data to be queried to obtain a final query result, wherein the method comprises the following steps:

obtaining a sequencing reference object set in a query object, and sequencing the broad table set through a sequencing function of JDK based on a responsibility chain design mode;

acquiring a group reference object set in a query object, sequentially taking out data corresponding to group fields from the broad table set according to the group fields defined in each group reference object, calculating a hash value, taking the hash value as a keyword, putting the data with the same hash value into the same set, and obtaining a final query result after traversing.

Preferably, when constructing the query object syntax tree, if the paging condition or the maximum number of the query results is set, the query results obtained after grouping may be further filtered to obtain the final query result.

And encapsulating the query results obtained after the execution of different actuators into a data set object realizing a data set interface, or encapsulating the query results into a constant query reference object for the memory data query.

Preferably, the data sources and executors in the generic data access framework are used in combination. Illustratively, a plurality of query results obtained by the JDBC executor or the JPA executor are packaged as memory table objects and placed in a memory data source, or packaged as constant query reference objects, and then a query object syntax tree is constructed, and the generated query objects are transmitted to the memory executor, thereby implementing integration, screening, grouping and sorting of the plurality of query results.

Compared with the prior art, the embodiment provides a data query method based on a universal data access frame, and aims to solve the problem that the thinking can be written according to uniform SQL for data in different storage forms in development, a chained method is used for calling to construct a query object syntax tree, the SQL splicing sequence does not need to be considered, multi-level sub-query nesting is supported, the construction of complex SQL is supported, and the difficulty in constructing SQL is reduced; only one query object is needed to be constructed, and a set of query logic is provided to support databases, memory data and constant data sets of different types and versions, so that the code reuse degree and the database compatibility are improved, and the maintenance cost is reduced; special conversion of different databases is processed in a centralized manner through the query translator, so that a user is prevented from compiling irregular native SQL, SQL syntax errors are avoided, the code quality is improved, and the risk of performance problems is reduced; and the personalized use is satisfied by extending the self-defined function, the reference object, the writer, the executor and the data set.

Those skilled in the art will appreciate that all or part of the processes for implementing the methods of the embodiments described above can be implemented by controlling the relevant hardware through a computer program, and the program can be stored in a computer-readable storage medium. The computer readable storage medium is a magnetic disk, an optical disk, a read-only memory or a random access memory.

The above description is only for the preferred embodiment of the present invention, but the scope of the present invention is not limited thereto, and any changes or substitutions that can be easily conceived by those skilled in the art within the technical scope of the present invention are included in the scope of the present invention.

Claims (10)

1. A data query method based on a universal data access framework is characterized by comprising the following steps:

acquiring a data source based on an access mode of data to be queried;

acquiring query conditions, calling a query constructor, constructing a query object syntax tree for the data to be queried in a chained calling mode, and acquiring 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 a query executor, and executing a query method to obtain a query result.

2. The data query method based on the universal data access framework as claimed in claim 1, wherein the obtaining of the data source based on the access mode of the data to be queried comprises:

when the data to be queried is stored in the database and the project frame does not adopt Hibernate or JPA, accessing the data to be queried through the table name and the field name of the database to acquire a JDBC data source;

when the data to be queried is stored in the database and the project framework adopts Hibernate or JPA, packaging the data to be queried into an entity object and acquiring a JPA data source;

when the data to be queried is stored in the memory, packaging the data to be queried into a memory table object, and acquiring a memory data source;

and when the data to be inquired is stored in the constant data set, packaging the data to be inquired into a memory table object or a constant inquiry reference object, and acquiring a memory data source.

3. The universal data access framework based data query method according to claim 2, wherein the memory table object comprises: table aliases, data lists, and field accessors;

the data list comprises attribute names and attribute values;

the field accessor is used for specifying 1or more attribute names needing to be extracted from the data list;

the constant query referencing object realizes a root referencing interface and a data set interface;

the data set interface comprises a method for acquiring a data set header and a data list, and the constant query reference object stores data in a two-dimensional array format in the method for acquiring the data list; and setting the attribute name of the data list as a header in the method for acquiring the header of the data set.

4. The method for querying data based on a generic data access framework according to any one of claims 1-3, characterized in that each node in the query object syntax tree is a referencing object implementing the same parent referencing interface, the parent referencing interface comprising a method for obtaining element types, the referencing object defining the corresponding SQL syntax element type in the method for obtaining element types;

the root reference interface is a child reference interface derived from the parent reference interface;

and the query condition is packaged as a reference object with the SQL syntax element type being constant, and the reference object is used as a node on a query object syntax tree.

5. The universal data access framework-based data query method according to claim 4, wherein the query builder comprises a plurality of interfaces corresponding to the SQL component, and the method starting from start and end in each interface implementation class represents the start and end of building the SQL component;

the constructing of the query object syntax tree comprises: calling a plurality of groups of start and end beginning methods in a chain calling mode, and hooking 1or more reference objects in each group of start and end beginning methods; a query object syntax tree describes a complete SQL statement.

6. The universal data access framework based data query method according to claim 5, wherein the obtaining a query object based on the query object syntax tree comprises:

placing the reference objects with the same element types on the syntax tree into the corresponding reference object sets;

putting the reference object set into a root reference object of a root node of a syntax tree, and returning the root reference object;

the root reference object is a reference object which is corresponding to the root node of the query object syntax tree and realizes a root reference interface.

7. The universal data access framework based data query method according to any one of claims 1-6, wherein the creating a query executor according to the data source comprises:

if the target database type is the JDBC data source, calling an execution configurator to obtain a JDBC executor, creating a query translator, obtaining the target database type according to the JDBC data source, and registering a writer corresponding to the target database type into the query translator;

if the JPA data source is the JPA data source, calling an execution configurator to acquire a JPA actuator, creating a query translator, and registering a JPA writer in the query translator;

and if the data source is the memory data source, directly acquiring the memory executor.

8. The universal data access framework based data query method as claimed in claim 7, wherein said writers have the same parent writer interface for performing the following operations: get writer process type, prepare translation and write translation; wherein the content of the first and second substances,

the acquisition writer processing type is used for defining element types which can be processed by a writer and corresponds to the element types defined in a reference object;

the preparation translation is used for converting reference objects in the query objects by using equivalent syntax according to the types of the target database, and adjusting the node hooking relationship among the reference objects to obtain the processed query objects;

and the writing translation is used for generating an SQL (structured query language) fragment corresponding to the target database type or an HQL (extensible markup language) fragment corresponding to the JPA (Java markup language) in a context string builder according to the element type corresponding to the reference object in the processed query object, and/or writing a value in a parameter list buffer area.

9. The universal data access framework-based data query method according to claim 8, wherein the step of transmitting the query object into the query executor and obtaining the query result after executing the query method comprises:

when a JDBC executor or a JPA executor is called to execute a query, a writer corresponding to a root reference object of a query object is used as an entry writer called by a query translator, in preparation of translation and write translation, a query object syntax tree is traversed according to a defined traversal sequence, a character string builder and a parameter list buffer area in the context of each writer are merged for the processed query object, a complete SQL statement or HQL statement and a parameter list are obtained, and a query result is obtained after execution;

when a memory executor is called to execute query, acquiring each reference object set of a query object, acquiring a table reference object set, sequencing and combining the table reference objects to obtain a wide table set, then screening the wide table set according to the Boolean condition reference object set, sequencing the wide table set according to the sequencing reference object set, and finally grouping the wide table set according to the grouped reference object set to obtain a query result.

10. The universal data access framework based data query method according to claim 9, wherein the query result is packaged as a dataset object implementing a dataset interface or as a constant query reference object for use in the in-memory data query.

Images