CN115964386A - Database-based data access system - Google Patents

Abstract

The invention relates to a database-based data access system, belongs to the technical field of data access, and solves the problems that in the prior art, the SQL structure is complex and various SQL are needed to solve different data carriers. The method comprises the following steps: the query constructor is used for constructing a query object syntax tree in a chain calling mode and acquiring a query object; the query translator is used for managing the context, calling the registered writer to adjust the syntax tree structure of the query object in the translation preparation operation, and translating the adjusted query object into SQL in the translation writing operation; the data source is used for acquiring connection with the target database; the execution configurator is used for acquiring a query executor corresponding to a data source, creating a query translator and registering a writer corresponding to the type of the target database into the query translator; the query executor is used for transmitting the query object generated by the query constructor into the query translator, executing SQL returned by the query translator and acquiring a query result. A set of query logic is implemented to support a variety of data carriers.

Description

Database-based data access system

Technical Field

The invention relates to the technical field of data access, in particular to a data access system based on a database.

Background

With the increasingly prominent position of big data as strategic resources, people pay more and more attention to the collection and storage of data, and are more and more strongly aware of the importance of data mining and data transformation, and enterprises gradually build data analysis systems to support flexible data query and analysis work. How to support different types of databases and how to quickly develop and meet different query requirements of customers is a relatively concerned problem when developing a system.

The existing open source framework encapsulates a data Query method, which is convenient for developers to Query data and encapsulate SQL (Structured Query Language) statements, such as Hibernate, JPA (Java Persistence API), mybatis, and often directly writes SQL conforming to the syntax of a database for Query for complex service data acquisition.

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. Often, a plurality of sets of SQL are packaged according to the type of the database to meet different syntax requirements, and the problems of SQL injection, SQL splicing sequence, matching of SQL dynamic parameters and placeholders and the like need to be noticed, so that a lot of difficulties are brought to development, the development efficiency is influenced, the SQL quality is difficult to control, and the query performance has risks.

Disclosure of Invention

In view of the foregoing analysis, the embodiments of the present invention are directed to providing a database-based data access system, so as to solve the problem that the existing SQL is complex in structure and cannot adapt to multiple data carriers with a set of query logic.

The embodiment of the invention provides a database-based data access system, which comprises: the system comprises a query constructor, a query translator, a data source, an execution configurator and a query executor; wherein,

the query constructor is used for constructing a query object syntax tree in a chain calling mode and acquiring a query object based on the query object syntax tree;

a query translator for managing the context, for calling the registered writer to adjust a syntax tree structure of the query object in a translation preparation operation, and for translating the adjusted query object into SQL in a translation writing operation;

a data source for obtaining a connection with a target database;

an execution configurator for obtaining a query executor corresponding to the data source, creating a query translator, and registering a writer corresponding to the target database type in the query translator;

and the query executor is used for transmitting the query object generated by the query constructor into the query translator, executing SQL returned by the query translator and acquiring a query result.

Furthermore, nodes on the query object syntax tree are reference objects corresponding to the SQL syntax elements, the reference objects realize the same father reference interface and each comprise a method for acquiring an element type, and the method is used for defining the SQL syntax element type corresponding to the reference object; the reference object may be associated with other reference objects, and may also derive child reference objects.

Furthermore, the query constructor comprises a plurality of interfaces corresponding to the SQL component, and the start and the end of constructing the SQL component are represented by a method of starting and ending 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 1 or more reference objects in each group of start and end starting methods; a query object syntax tree describes a complete SQL statement.

Further, the writers have a same parent writer interface for performing the following: get writer process type, prepare translation and write translation; wherein,

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;

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

and writing translation, namely generating a corresponding SQL fragment 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.

Further, a writer corresponding to the reference object of the root node of the query object syntax tree is used as an entry writer called by the query translator; the traversal order for the query object syntax tree is defined in translation preparation and translation write operations of the entry writer, respectively.

Further, registering a writer corresponding to the target database type in the query translator, the registration order being: firstly, registering a writer of standard ANSI SQL, then registering a writer for processing the differentiated grammar corresponding to the type of the target database, finally registering a writer for processing the special service scene, and according to the same processable element type defined in the writer, later registering the writer which can cover the former registered writer.

Further, managing the context includes 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 constructors and the parameter list buffer areas in the subcontexts into the parent contexts, and finally merging to generate a complete SQL statement and a parameter list;

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

Further, variables exist in context, divided into three levels: global variables, query object level variables and local variables;

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 shareable within the context of the main sub-query of the query object during translation;

and 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.

Further, the query result is packaged as a data set object, including a query result set and metadata of the query result, wherein the query result set is stored in a two-dimensional array format.

Further, the query executor is an abstract interface, which is extended according to the object type in the query object, and includes: JDBC executor and JPA executor.

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 a chain method without considering the SQL splicing sequence, multi-level sub-query nesting is supported, the construction of complex SQL is supported, and the difficulty of constructing the SQL is reduced;

2. only one query object needs to be constructed, and a set of query logic is provided to support databases of different types and versions, so that the code reusability 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. an extensible mechanism is provided, self-defined functions, reference objects, writers, actuators and data sets are supported, and personalized use is met;

5. the query result set adopts the two-dimensional array for data storage, so that the storage space is greatly reduced.

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 diagram of a database-based data access system according to 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 database-based data access system, as shown in fig. 1, including: the system comprises a query constructor, a query translator, a data source, an execution configurator and a query executor;

the query constructor is used for constructing a query object syntax tree in a chain calling mode and acquiring a query object based on the syntax tree;

a query translator for managing the context, for calling the registered writer to adjust a syntax tree structure of the query object in a translation preparation operation, and for translating the adjusted query object into SQL in a translation writing operation;

a data source for obtaining a connection with a target database;

an execution configurator to obtain a query executor corresponding to the data source, and to create a query translator, and to register a writer corresponding to the target database type into the query translator.

And the query executor is used for transmitting the query object generated by the query constructor into the query translator, executing SQL returned by the query translator and acquiring a query result.

When the method is implemented, a unified query object is defined by using a query constructor, a data source is transmitted into an execution configurator, the execution configurator automatically registers a writer into a translator, an adaptive query executor is returned, the query object is transmitted into the query executor, the query executor automatically calls the query translator, the query object is converted into SQL (structured query language) specific to different databases through the registered writer, and a query result is obtained after the SQL is executed.

Preferably, the database-based data access system in this embodiment can be packaged as a Jar package, which is used as a tool class in an enterprise development framework, and is convenient for use and extension of each development project group.

The operation principle and technical scheme of each module in the database-based data access system are described in detail below.

Specifically, consider 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 comprises the following methods:

startSelect for setting the select column name; endSelect, for indicating that the currently selected column name setting is completed;

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 that the setting of the current grouping field is completed;

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 the completion of the current merge setting;

page, used for setting the 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, for SQL statement "SELECT c.customamer CUSTOMER name FROM C ware c.id >1", a query constructor is created using hquueryfacede createquerybuilder () method, and the SQL statement is constructed as a syntax tree using a statement, with the following example codes:

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 are constructed as a syntax tree by using multiple 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. The reference object may be associated with other reference objects, and may also derive child reference objects.

In the above example code, a table reference object is constructed by a tab ("CUSTOMER", "C") method, where "CUSTOMER" is a table name in the table reference object, and "C" is a 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, a parent referencing interface derives many child referencing interfaces, each referencing object directly implements a corresponding child referencing interface, and a new method can be added while inheriting the 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, which includes: the column SEL, the field COL, the table FRM, the group GRP, and the constant CST, etc., it should be noted that the element type supports customization, for example, the "CST _ USER" can be defined as an extended constant reference, which is different from the general CST distinguishing processing manner.

Other reference objects can be associated with the reference object, and the selection column defined after select in SQL is taken as an example for illustration. The select column may be set to: there are many cases where fields, constant fields, associated table fields, query fields of subqueries, function values, etc. of the current table are present. It can be seen from these situations that the selected column name can be associated with a field reference object, a variable reference object, a function call reference object, and the like, and therefore, when a component of the column name is selected in SQL, multiple methods are defined in the Select interface implementation class to add related reference objects, such as field reference, function call reference, and variable reference, that conform to SQL syntax to a set of the selected column reference objects, where the constant reference object and the sub-query reference object are all sub-interface objects of the variable reference interface HValRef, and the constant reference object and the sub-query reference object can be obtained through the variable reference object, and an input parameter in the function call reference can be associated with the column reference, the constant reference, or the sub-query reference object.

Any reference object which realizes a father reference interface can be hooked on the syntax tree, and each reference object can be hooked with other reference objects and can also be hooked with 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 (hquueryfacade. Createquerybuilder (), startSelect (), col (0, "total"). EndSelect (), startFrom (), tab ("process", "P"). EndFrom (), build (), "sales");

for complex queries in SQL, usually multiple table associations are used, including leftjoin, rightjoin, innerjoin, and fulljoin, only one table reference object is difficult to cover a complex scene, and in this embodiment, the reference object is derived as a sub-reference object to implement the complex query. 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; a table association reference object HFrmJRef for defining the table reference object, the association way and the association condition of the associated left table. 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 ();

a main table name reference object HFrmNRef is constructed in the code through a tab (A, A table) method, an associated table name reference object HFrmJNRRef is constructed through a JOIN (0, AID, joinType, LEFT _ JOIN, B, BID) method, the table name reference object HFrmJNRef comprises a table reference object HFrmRef, an association mode JoinType and a Boolean condition reference object HBoolRef, and the table name reference object HFrmNRRef, the association mode JoinType and the Boolean condition reference object HBoolRef are respectively used for defining reference objects, association relations and association conditions of an associated left table.

It should be noted that, in the prior art, when a dynamic incoming query condition is required in an SQL statement, JDBC placeholder "? "represents parameters in SQL, but when query logic is complex, developers are difficult to match placeholders with corresponding parameter values one by one when constructing SQL, and are also difficult to troubleshoot problems. Although the Hibernate can store variables and parameter values in the Map, when the variables are more or the same variable is used in the SQL for a plurality of times, the variable name + sequence number mode is used to prevent the variable name from being repeated, and the problem is difficult to be solved. In the embodiment, the query constructor 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 realizing the HValRef interface, and the problem of placeholder does not need to be considered during construction.

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.

The reference objects in the reference object set are analyzed by a query translator when a query is executed, the query translator is designed by adopting a policy mode, a plurality of writers supporting different database SQL grammars are registered in the query translator, and 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.

It is worth noting that when the query constructor constructs the query object, the query object is constructed through the same reference object without considering the difference between different database types and different versions of the same database, so that the use convenience and the development efficiency are improved. When executed, different translation functions are performed by different writers according to the type and version of the database, and specifically, the operations performed by the writers include: get writer process type, prepare translation and write translation;

(1) acquiring writer processing type, which is used for defining element type that can be processed by writer and is correspondent to 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 the node hooking relationship 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, the function being ranked according to the input sorting 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()；

Corresponding to the function call reference object is a function call writer HWriterFun, which inherits the HWriter interface and extends a method for acquiring function name, based on the HWriterFun interface, different writers are realized for different types and versions of databases, such as MySQL5MyRankFunWriter, mySQL8MyRankFunWriter, no special processing is needed in the preparation translation method of the MySQL8MyRankFunWriter writer, in the writing translation method, the SQL fragment converted into the Rank function is needed, but in the preparation translation method of the MySQL5MyRankFunWriter writer, because the function is completed by main sub-Rank query and adding Rank field, one function reference object is converted into multiple reference objects to be hanged in the main query object again to obtain the 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 type of the database.

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 querying, when the existing writer cannot directly meet the business requirement, the new writer can be extended by inheriting the parent writer interface.

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.

After the query object is constructed, a writer corresponding to a reference object of a root node of a query object syntax tree is used as an entry writer called by a query translator; the query translator performs a traversal of the query object syntax tree in preparation for translation and in write for translation, respectively, the traversal order being defined in the entry writer.

Specifically, the translation preparation period is mainly used for adjusting the syntax tree structure, and only needs to traverse the 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 by a plurality of writers into a complete SQL statement and parameter list according to the SQL syntax specification, the translation order of the reference objects in the syntax tree is defined in an entry writer, so that the calling order of the writers corresponding to the reference objects is controlled, the SQL fragments are created by context, and the merging order of the SQL fragments 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 first, 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.

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 idle 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,

global variable, which refers to a variable shared among all contexts during the translation process;

query object level variables, which refer to variables that are independent but shareable within the context of the main sub-query of the query 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.

Considering that one reference object corresponds to a plurality of writers due to different types and versions of the databases, when the query is executed, the corresponding writer needs to be selected according to the type of the database, and the task is completed by the execution configurator.

After obtaining the data source, the execution configurator is configured to obtain a query executor corresponding to the data source, create a query translator, and register a writer corresponding to the target database type in the query translator according to the target database type obtained by the data source. During registration, a writer of standard ANSI SQL is registered firstly, then a writer which is corresponding to the database type and is used for processing the differentiated grammar is registered, finally a writer for processing the special service scene is registered, and according to the same processable element type defined in the writer, the later registered writer can cover the former registered writer.

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

The query executor is an abstract interface, is expanded according to the object type in the query object, and comprises the following steps: JDBC executor and JPA executor.

Specifically, when a query object tree is constructed, if data is stored in a database and a project frame does not adopt Hibernate or JPA, the data is accessed through a physical table name and a table field, a JDBC executor is used to call a JDBC driver; if the data is stored in the database and the project framework adopts Hibernate or JPA, the database table is encapsulated into an entity object, the data is accessed through the object name and the object attribute, and then a JPA actuator is used for calling a JPA driving class package.

And packaging the query result returned by the query executor into a data set object, wherein the data set object stores the data result by adopting a two-dimensional array, and the data set object comprises a query result set and metadata of the query result.

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.

Compared with the prior art, the embodiment provides a data access system based on a database, solves the problems that multiple sets of processing logics are needed for different data carriers in development, codes need to be reconstructed once the database is changed, the splicing of complex SQL is complex, and the problem that the query object syntax tree is difficult to find is solved, and the query object syntax tree is constructed in a chained calling mode to support infinite expansion; business objects do not need to be created during development, the SQL splicing sequence is not needed to be considered, the problem of matching of placeholders and parameters is solved, the construction of complex SQL is supported, and the difficulty in constructing the SQL is reduced; the database with different types and versions can be supported only by constructing one query object syntax tree, so that the code reuse degree and the database compatibility are improved; 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, the code quality is improved, and the performance risk is reduced; the query result set adopts a two-dimensional array for data storage, so that the storage space is greatly reduced; an extensible mechanism is provided, self-defined functions, reference objects, writers, executors and data sets are supported, and personalized use is met.

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.

While the invention has been described with reference to specific preferred embodiments, it will be understood by those skilled in the art that various changes and modifications may be made without departing from the spirit and scope of the invention as defined in the following claims.

Claims (10)

1. A database-based data access system, comprising: the system comprises a query constructor, a query translator, a data source, an execution configurator and a query executor; wherein,

the query constructor is used for constructing a query object syntax tree in a chain calling mode and acquiring a query object based on the query object syntax tree;

the query translator is used for managing the context, calling the registered writer to adjust the syntax tree structure of the query object in the translation preparation operation, and translating the adjusted query object into SQL in the translation writing operation;

the data source is used for acquiring connection with a target database;

the execution configurator is used for acquiring a query executor corresponding to a data source, creating a query translator and registering a writer corresponding to the type of the target database into the query translator;

the query executor is used for transmitting the query object generated by the query constructor into the query translator, executing SQL returned by the query translator and obtaining a query result.

2. The database-based data access system of claim 1, wherein the nodes of the query object syntax tree are referencing objects corresponding to SQL syntax elements, the referencing objects implementing the same parent referencing interface, each including a means for obtaining an element type for defining the SQL syntax element type corresponding to the referencing object; the reference object may be associated with other reference objects, and may also derive child reference objects.

3. The database-based data access system of claim 2, wherein the query builder comprises a plurality of interfaces corresponding to the SQL components, the start and end of building the SQL components being represented by methods beginning at start and end in each interface implementation class;

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

4. A database-based data access system according to any of claims 1-3, 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 acquisition writer processing type is used for defining element types which can be processed by a writer and corresponds to an SQL syntax element type 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 a corresponding SQL fragment 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.

5. The database-based data access system of claim 4, wherein the writer corresponding to the reference object of the query object syntax tree root node acts as an entry writer for a query translator call; defining a traversal order for the query object syntax tree in translation preparation and translation write operations of the entry writer, respectively.

6. The database-based data access system of claim 5, wherein the writer corresponding to the target database type is registered in the query translator in the order: firstly, registering a writer of standard ANSI SQL, then registering a writer for processing the differentiated grammar corresponding to the type of the target database, finally registering a writer for processing the special service scene, and according to the same processable element type defined in the writer, later registering the writer which can cover the former registered writer.

7. The database-based data access system of claim 6, wherein the management context comprises the operations of:

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.

8. The database-based data access system of claim 7, wherein the variables exist in context, divided into three levels: global variables, query object level variables and local variables;

the global variable refers to a variable shared by all the contexts in the translation process;

the query object level variables refer to variables that are independent but shareable within the context of the main sub-query of the query object during translation;

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.

9. The database-based data access system of any of claims 1-8, wherein the query results are packaged as dataset objects, including a set of query results and metadata for the query results, wherein the set of query results is stored in a two-dimensional array format.

10. The database-based data access system of claim 9, wherein the query executor is an abstract interface that is extended according to an object type in the query object, comprising: JDBC executor and JPA executor.

Images