CN114090613A - Program statement conversion method, device, equipment and storage medium - Google Patents
Program statement conversion method, device, equipment and storage medium Download PDFInfo
- Publication number
- CN114090613A CN114090613A CN202111373357.0A CN202111373357A CN114090613A CN 114090613 A CN114090613 A CN 114090613A CN 202111373357 A CN202111373357 A CN 202111373357A CN 114090613 A CN114090613 A CN 114090613A
- Authority
- CN
- China
- Prior art keywords
- statement
- standard
- access statement
- standard access
- native
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/25—Integrating or interfacing systems involving database management systems
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Mathematical Physics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application provides a program statement conversion method, a device, equipment and a storage medium, wherein the method comprises the following steps: according to the user operation aiming at the target application program, obtaining a standard access statement corresponding to the user operation and a target database to be accessed by the user operation, wherein the standard access statement is an access statement based on a preset language standard, and performing statement conversion on the standard access statement according to the type of the database to obtain a native access statement meeting the language standard of the target database. By providing standard access statements to uniformly access dialects, differentiation among databases can be effectively shielded, and the standard access statements can be seamlessly integrated into codes, namely, the standard access statements can be executed in different types of databases.
Description
Technical Field
The present application relates to the field of computer technologies, and in particular, to a method, an apparatus, a device, and a storage medium for program statement conversion.
Background
Structured Query Language (SQL) is a special programming Language, a database Query and programming Language, used to access data, Query, update, and manage relational database systems. Although the SQL standard is established, as the SQL standard continuously evolves, many language differences are generated between various manufacturers and database products, and thus various SQL languages are generated.
In the prior art, an Object Relational Mapping (ORM) framework is usually adopted to mask differences between different SQL languages, and through specific abstraction of the ORM framework, an SQL statement is converted into a JAVA code operation or an SQL-like statement specific to the ORM framework, so as to implement functions of a unified SQL dialect.
However, the ORM framework itself is not light enough, and cannot support complex SQL queries, and is highly invasive to code.
Disclosure of Invention
The present application aims to provide a program statement conversion method, apparatus, device and storage medium to access different types of databases in a unified manner by accessing statements, in order to overcome the above-mentioned deficiencies in the prior art.
In order to achieve the above purpose, the technical solutions adopted in the embodiments of the present application are as follows:
in a first aspect, an embodiment of the present application provides a program statement conversion method, including:
according to user operation aiming at a target application program, acquiring a standard access statement corresponding to the user operation and a target database to be accessed by the user operation; the standard access statement is based on a preset language standard;
and performing statement conversion on the standard access statement according to the type of the database to obtain a native access statement meeting the language standard of the target database.
Optionally, performing statement conversion on the standard access statement according to the database type to obtain a native access statement meeting the language standard of the target database, including:
determining conversion logic of the standard access statement and the native access statement according to the database type;
analyzing the standard access statement, and determining a field of a preset type in the standard access statement as a field to be converted, wherein the preset type comprises at least one of a function, a grammar and a keyword;
and converting the field to be converted by adopting the conversion logic to obtain the native access statement.
Optionally, the field to be converted includes data of a keyword type, and before the conversion logic is adopted to convert the field to be converted to obtain the native access statement, the method further includes:
analyzing the standard access statement to obtain base table information in the standard access statement;
and checking the fields to be converted according to the base table information.
Optionally, the analyzing the standard access statement, and determining a preset type of field in the standard access statement as a field to be converted includes:
parsing the standard access statement into an abstract syntax tree;
and acquiring the field to be converted from the node data on the abstract syntax tree.
Optionally, the analyzing the standard access statement to obtain information of a base table in the standard access statement includes:
analyzing the standard access statement by starting a timer thread;
and taking the latest analyzed information as the base table information.
Optionally, after analyzing the standard access statement to obtain the library table information in the standard access statement, the method further includes:
and if detecting the base table information updating operation aiming at the target database, re-analyzing the standard access statement to refresh the base table information.
Optionally, the method further comprises:
executing the native access statement and acquiring an execution result corresponding to the native access statement;
and returning the execution result sequentially through the native connection interface corresponding to the target database and a preset standard connection interface.
Optionally, the method further comprises:
if the execution result comprises: judging whether the abnormal data is the abnormal data caused by the access object corresponding to the native access statement or not, wherein the abnormal data is the data generated by executing the native access statement;
and if so, re-analyzing the standard access statement to refresh the base table information.
In a second aspect, another embodiment of the present application provides a program statement conversion apparatus, including:
the system comprises an acquisition module, a processing module and a processing module, wherein the acquisition module is used for acquiring a standard access statement corresponding to a user operation and a target database to be accessed by the user operation according to the user operation aiming at a target application program; the standard access statement is based on a preset language standard;
and the conversion module is used for performing statement conversion on the standard access statement according to the type of the database to obtain a native access statement meeting the language standard of the target database.
Optionally, the conversion module is specifically configured to:
determining conversion logic of the standard access statement and the native access statement according to the database type;
analyzing the standard access statement, and determining a field of a preset type in the standard access statement as a field to be converted, wherein the preset type comprises at least one of a function, a grammar and a keyword;
and converting the field to be converted by adopting the conversion logic to obtain the native access statement.
Optionally, the method further comprises:
the analysis module is used for analyzing the standard access statement to obtain base table information in the standard access statement;
and the checking module is used for checking the field to be converted according to the base table information.
Optionally, the conversion module is specifically configured to:
parsing the standard access statement into an abstract syntax tree;
and acquiring the field to be converted from the node data on the abstract syntax tree.
Optionally, the parsing module is specifically configured to:
analyzing the standard access statement by starting a timer thread;
and taking the latest analyzed information as the base table information.
Optionally, the parsing module is further configured to:
and if detecting the base table information updating operation aiming at the target database, re-analyzing the standard access statement to refresh the base table information.
Optionally, the method further comprises:
the processing module is used for executing the native access statement and acquiring an execution result corresponding to the native access statement; and returning the execution result sequentially through the native connection interface corresponding to the target database and a preset standard connection interface.
Optionally, the processing module is further configured to:
if the execution result comprises: judging whether the abnormal data is the abnormal data caused by the access object corresponding to the native access statement or not, wherein the abnormal data is the data generated by executing the native access statement;
if yes, the analysis module is further configured to re-analyze the standard access statement to refresh the base table information.
In a third aspect, another embodiment of the present application provides a program statement conversion apparatus, including: a processor, a memory and a bus, wherein the memory stores machine-readable instructions executable by the processor, the processor and the memory communicate with each other through the bus when the program statement conversion device runs, and the processor executes the machine-readable instructions to perform the method according to any one of the first aspect.
In a fourth aspect, another embodiment of the present application provides a computer-readable storage medium having a computer program stored thereon, where the computer program is executed by a processor to perform the method of any one of the first aspect.
The beneficial effect of this application is:
the application provides a program statement conversion method, a device, equipment and a storage medium, wherein the method comprises the following steps: according to the user operation aiming at the target application program, standard access statements corresponding to the user operation and a target database to be accessed by the user operation are obtained, the standard access statements are access statements based on preset language standards, and the standard access statements are subjected to statement conversion according to the type of the database to obtain native access statements meeting the language standards of the target database. By providing standard access statements to uniformly access dialects, differentiation among databases can be effectively shielded, the standard access statements can be seamlessly integrated into codes, namely, one set of standard access statements can be used for execution in different types of databases, and compared with an ORM framework, the method is lighter, has higher expansibility, and is free from code framework intrusion.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings that are required to be used in the embodiments will be briefly described below, it should be understood that the following drawings only illustrate some embodiments of the present application and therefore should not be considered as limiting the scope, and for those skilled in the art, other related drawings can be obtained from the drawings without inventive effort.
Fig. 1 illustrates a first flowchart of a program statement conversion method provided in an embodiment of the present application;
FIG. 2 is a flowchart illustrating a second method for program statement conversion according to an embodiment of the present application;
FIG. 3 is a flowchart illustrating a third method for program statement conversion according to an embodiment of the present application;
FIG. 4 is a fourth flowchart illustrating a program statement conversion method according to an embodiment of the present application;
FIG. 5 is a schematic diagram illustrating an SQL statement conversion process provided by an embodiment of the present application;
FIG. 6 is a schematic structural diagram of a program statement conversion device provided in an embodiment of the present application;
fig. 7 shows a schematic structural diagram of a program statement conversion device provided in an embodiment of the present application.
Detailed Description
In order to make the purpose, technical solutions and advantages of the embodiments of the present application clearer, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application, and it should be understood that the drawings in the present application are for illustrative and descriptive purposes only and are not used to limit the scope of protection of the present application. Additionally, it should be understood that the schematic drawings are not necessarily drawn to scale. The flowcharts used in this application illustrate operations implemented according to some embodiments of the present application. It should be understood that the operations of the flow diagrams may be performed out of order, and steps without logical context may be performed in reverse order or simultaneously. One skilled in the art, under the guidance of this application, may add one or more other operations to, or remove one or more operations from, the flowchart.
In addition, the described embodiments are only a part of the embodiments of the present application, and not all of the embodiments. The components of the embodiments of the present application, generally described and illustrated in the figures herein, can be arranged and designed in a wide variety of different configurations. Thus, the following detailed description of the embodiments of the present application, presented in the accompanying drawings, is not intended to limit the scope of the claimed application, but is merely representative of selected embodiments of the application. All other embodiments, which can be derived by a person skilled in the art from the embodiments of the present application without making any creative effort, shall fall within the protection scope of the present application.
It should be noted that in the embodiments of the present application, the term "comprising" is used to indicate the presence of the features stated hereinafter, but does not exclude the addition of further features.
Before describing the technical solutions of the present application, the terms related to the present application will be described first.
Structured Query Language (SQL): is a special purpose programming language, a database query and programming language, used to access data and query, update and manage relational database systems.
Unifying SQL: and the SQL development language is generated by adopting a preset language standard.
SQL dialect: different databases employ SQL development languages generated using different language standards.
Native SQL statements: and SQL sentences generated by an SQL dialect.
Object Relational Mapping (ORM) framework: is a programming technique for implementing conversion between data of different types of systems in an object-oriented programming language.
JAVA Database Connectivity (JDBC): is an application program interface in JAVA language that specifies how a client program accesses a database, and provides methods such as querying and updating data in the database.
JDBC Proxy (JDBC Proxy): unifying proxy driving of data connection interfaces provided by SQL.
Native JDBC: each type of database provides an external data connection interface through which the database can be accessed.
The SQL engine: the JDBC agent provides a parsing engine.
Abstract Syntax Tree (AST) is an Abstract representation of the Syntax structure of the source code, and represents the Syntax structure of the programming language in the form of a Tree, where each node on the Tree represents a structure in the source code.
Database schema Definition Language (DDL), a Language used to describe real-world entities to be stored in a database, operates.
Data Management Language (DML): programming statements for database operations that run access work on objects and data in the database.
Mybatis: an excellent persistence tier framework supports customized SQL, stored procedures, and advanced mapping.
Software Development Kit (SDK): a collection of development tools in building application software for a particular software package, software framework, hardware platform, operating system, etc.
Currently, SQL is executed in JAVA applications, and usually uses an ORM relational mapping framework such as Hibernate, JPA, etc. to mask differences between different SQL dialects, this technology mainly converts SQL statements into JAVA code operations or SQL-like statements specific to the ORM framework, such as Query Language (HQL) and JAVA Persistent Query Language (JPQL), through abstraction of the ORM framework, so as to implement functions of uniform SQL dialects based on comparison.
However, these ORM frameworks are not light-weight enough, the persistence layer needs to be developed completely according to the standard of the ORM framework, the code development is very invasive, and cannot support complex SQL (such as aggregation statistics), if the complex SQL needs to be executed, it can only be realized by developing native SQL, and the native SQL suffers from the problem of non-uniform SQL dialects.
Based on the above problems, the present application provides a program statement conversion method, which can effectively shield the differentiation between databases by providing standard access statements to uniformly access dialects, and can seamlessly integrate into codes, i.e. a set of standard access statements can be executed in different types of databases, and compared with the method using an ORM framework, the method is lighter, more extensible, and has no invasion of the code framework.
The following describes the program statement conversion method provided by the present application in detail with reference to several specific embodiments.
Fig. 1 is a schematic flow diagram illustrating a program statement conversion method according to an embodiment of the present application, where an execution main body of the embodiment may be a program statement conversion device, for example, a device for performing data access, such as a mobile phone, a tablet computer, and a desktop computer.
As shown in fig. 1, the method includes:
s101, according to user operation aiming at a target application program, a standard access statement corresponding to the user operation and a target database to be accessed by the user operation are obtained.
The target application program may be any application program having access to the database, and the user may download and install the target application program and input a user operation for the target application program, where the user operation may be any one of an operation of creating modification table information, a data query operation, and a data add/delete operation.
The program access device can acquire the target database corresponding to the user operation according to the user operation, and can acquire a standard access statement corresponding to the user operation, wherein the standard access statement is an access statement based on a preset language standard, that is, the user operation is described according to the preset language standard to obtain the standard access statement.
And S102, performing statement conversion on the standard access statement according to the type of the database to obtain a native access statement meeting the language standard of the target database.
The native access statement is based on the language standard of the target database, and different conversion logics are arranged between the native access statement of different database types and the standard access statement, so that the conversion logics between the native access statement of the target database and the standard access statement can be determined according to the database type of the target database, and then the statement conversion is carried out on the standard access statement according to the conversion logics so as to obtain the native access statement meeting the language standard of the target database.
In some embodiments, the conversion logic may include at least one of function conversion logic, grammar conversion logic, and keyword conversion logic.
It should be noted that the preset language standard may be a preset unified SQL standard, the standard access language may be unified SQL developed and written by using the preset language standard, the standard access statement is a unified SQL statement, and the native access statement may be a native SQL statement generated by using the language standard of the target database, where the language standard of the target database is a native language standard of the target database, and the unified SQL standard may include a unified SQL standard syntax and a unified standard function.
The program statement conversion method of the embodiment includes: according to the user operation aiming at the target application program, standard access statements corresponding to the user operation and a target database to be accessed by the user operation are obtained, the standard access statements are access statements based on preset language standards, and the standard access statements are subjected to statement conversion according to the type of the database to obtain native access statements meeting the language standards of the target database. By providing standard access statements to uniformly access dialects, differentiation among databases can be effectively shielded, and the standard access statements can be seamlessly integrated into codes, namely, the standard access statements can be executed in different types of databases.
Fig. 2 shows a second flowchart of the program statement conversion method provided in the embodiment of the present application, and as shown in fig. 2, step S102 is to perform statement conversion on a standard access statement according to a database type to obtain a native access statement meeting a language standard of a target database, where the method includes:
s201, determining conversion logic of the standard access statement and the original access statement according to the type of the database.
In this embodiment, conversion logic of the standard access statement and the native access statement may be determined according to a database type of the target database, and the conversion logic may include at least one of function conversion logic, syntax conversion logic, and keyword conversion logic.
S202, analyzing the standard access statement, and determining a field of a preset type in the standard access statement as a field to be converted.
And S203, converting the field to be converted by adopting conversion logic to obtain a native access statement.
The preset type may be a preset field type that needs to be analyzed, and the preset type may include at least one of a function, a syntax, and a keyword.
The method comprises the steps of analyzing a standard access statement to analyze the standard access statement into a plurality of field data, determining a field of a preset type as a field to be converted from the plurality of field data, namely, if the preset type can comprise a function, a grammar and a keyword, the type of the field to be converted comprises the function, the grammar and the keyword, and then converting the field to be converted by adopting conversion logic of the standard access statement and a native access statement to obtain a native access statement.
It should be noted that the keyword refers to a keyword in a target database to be accessed, for example, accessing a data table in the target database, and the keyword may include a library identification field of the target database, a table identification field of the data table, and a field contained in the data table and a field type of the field.
In some embodiments, in step S202, parsing the standard access statement, and determining a preset type of field in the standard access statement as a field to be converted includes:
the standard access statements are parsed into an abstract syntax tree.
And acquiring a field to be converted from node data on the abstract syntax tree.
The abstract syntax tree displays standard access statements in a tree form, analyzes the standard access statements into the abstract syntax tree, and then queries fields to be converted of preset types from node data on the abstract syntax tree. In such a way, the standard access data can be intuitively displayed through the abstract syntax tree, and the field to be converted can be conveniently and accurately acquired.
Taking the standard access statement as the unified SQL statement as an example, before analyzing the unified SQL statement, the target application needs to load the JDBC agent that introduces the unified SQL, the JDBC agent pushes the unified SQL statement to the SQL engine, and the SQL engine analyzes the standard access statement into the abstract syntax tree. Therefore, only the JDBC agent customized for the unified SQL is needed, and compared with the prior art, the method can support the sentences of complex syntax and functions.
According to the program statement conversion method, conversion logic of a standard access statement and a native access statement is determined according to a database type, the standard access statement is analyzed, a field of a preset type in the standard access statement is determined to be a field to be converted, and the field to be converted is converted by adopting the conversion logic to obtain the native access statement. By providing standard access statements to uniformly access dialects, differentiation among databases can be effectively shielded, and the standard access statements can be seamlessly integrated into codes, namely, the standard access statements can be executed in different types of databases.
Fig. 3 shows a third flowchart of the program statement conversion method provided in the embodiment of the present application, as shown in fig. 3, if the field to be converted includes data of a keyword type, step S203, before converting the field to be converted by using a conversion logic to obtain a native access statement, the method further includes:
s301, analyzing the standard access statement to obtain base table information in the standard access statement.
And S302, checking the field to be converted according to the base table information.
And if the field to be converted comprises the data of the keyword type, analyzing the standard access statement to obtain the base table information in the standard access statement, wherein the base table information can comprise the base identification field of the target database to be accessed, the table identification field of the data table to be accessed in the target database, the field contained in the data table and the field type contained in the data table.
The database identification field is used for identifying the target database, the table identification field is used for identifying a data table to be accessed in the target database, fields contained in the data table can comprise age, name and date, and field types contained in the data table can comprise number types, character string types and time types. The field type corresponding to the age is a number type, the field type corresponding to the name is a character creation type, and the field type corresponding to the date is a time type.
And then, checking the field to be converted according to the base table information obtained by analysis, that is, checking the field to be converted by using the base table information obtained by analysis as a check standard, and of course, if the field to be converted is inconsistent with the base table information, re-analyzing the standard access statement to refresh the field to be converted, and then checking the refreshed field to be converted according to the base table information, or directly refreshing the field to be converted according to the base table information. The specific logic may be determined according to the code logic, and this embodiment is not particularly limited thereto.
Of course, the information of the base table (i.e. the metadata) can be cached, and the metadata can be directly obtained from the cache without performing the steps later if the metadata is used.
In some embodiments, step S301, parsing the standard access statement to obtain the library table information in the standard access statement, including:
the standard access statements are parsed by starting a timer thread.
And taking the latest analyzed information as base table information.
In some cases, since the base table information in the delayed target database has been updated, but the base table information obtained by parsing in step S301 has not been updated, for example, the base identifier field of the target database is updated, the base identifier field in the base table information obtained by parsing in step S301 has not been updated, and the target database cannot be found by using the base table information, a base table information update acquisition mechanism is provided, and a timer thread is started to parse a standard access statement, so that the latest parsed information is used as the base table information, that is, the base table information in the cache is refreshed by using the latest parsed information, wherein the interval time of the timer can be set according to actual conditions, and the timer can be disabled.
In some embodiments, in step S301, after parsing the standard access statement to obtain the library table information in the standard access statement, the method further includes:
and if detecting the base table information updating operation aiming at the target database, re-analyzing the standard access statement to refresh the base table information.
In another base table information updating and collecting mechanism, a user may input a base table information updating operation for the target database to adjust the base table information of the target database, that is, if the base table information updating operation for the target database is detected but the base table information obtained by analyzing according to step S301 is not updated, the standard access statement is reanalyzed to refresh the base table information.
The base table information updating operation may be a DDL operation, where the DDL is a language for describing real world entities to be stored in the database, that is, if the user executes a DDL statement, the standard access statement is re-parsed to refresh the base table information.
Taking a standard access statement as a unified SQL statement as an example, analyzing the base table information in the unified SQL by an SQL engine, then connecting a target database by the native JDBC to obtain the base table information, and caching the base table information. Therefore, the fields to be converted can be checked by combining the SQL engine and the base table information without extra management and maintenance work of metadata, and the DDL statement can be executed instead of only the query statement.
In the program statement conversion method of this embodiment, the standard access statement is analyzed to obtain the base table information in the standard access statement, and the to-be-converted field is verified according to the base table information. And the fields to be converted obtained by analysis are verified, so that the accuracy of program statement conversion is improved.
Fig. 4 is a flowchart illustrating a fourth flowchart of the program statement conversion method provided in the embodiment of the present application, and as shown in fig. 4, the method further includes:
s401, executing the native access statement, and acquiring an execution result corresponding to the native access statement.
S402, sequentially passing through the native connection interface corresponding to the target database and the preset standard connection interface, and returning an execution result.
The native connection interface corresponding to the target database may be a connection interface provided for a native JDBC, the preset standard connection interface may be a connection interface provided for a standard JDBC (i.e., a JDBC agent), and after obtaining the native access statement of the target database, the native access statement may be executed, the execution result corresponding to the native access statement is obtained, and the execution result is returned sequentially through the native connection interface and the preset standard connection interface. The execution result may be, for example, data of a certain row and a certain column in a data table in the target database, such as age.
In some embodiments, the method further comprises:
the execution result comprises: and if the abnormal data is the abnormal data caused by the access object corresponding to the native access statement, judging whether the abnormal data is the abnormal data.
If yes, the standard access statement is re-analyzed so as to refresh the base table information.
The access object corresponding to the native access statement may be: if the object indicated by the keyword in the standard access statement, for example, the keyword is a library identification field, the access object corresponding to the native access statement is the library identification field.
The abnormal data is data generated by executing the native access statement, for example, the native access statement is used to search a certain row and a certain column of data of a target data table in the target database, and the access object corresponding to the native access statement includes a library identification field, so that the abnormal data can be an abnormal prompt for finding that the library identification field is inconsistent with the library identification field of the target database when the library identification field is searched, that is, the library table information of the target database is updated, but the library table information obtained by analyzing based on the standard access statement is not updated, and the standard access statement can be re-analyzed to refresh the library table information.
The program statement conversion method of this embodiment executes the native access statement, obtains an execution result corresponding to the native access statement, and returns the execution result after passing through the native connection interface corresponding to the target database and the preset standard connection interface in sequence. Through the operation of the unified SQL dialect, the SQL differentiation among all databases can be effectively shielded, the SQL differentiation can be seamlessly integrated into codes, namely, one set of SQL can be executed in various databases, and compared with an ORM framework, the SQL dialect-based query language is lighter, has higher expansibility and is free from code framework intrusion.
The SQL statement translation process is described below with reference to a specific embodiment, and fig. 5 shows a schematic diagram of the SQL statement translation process provided in the embodiment of the present application, and as shown in fig. 5, the SQL statement translation process includes the following steps:
step one, a target application program introduces a JDBC agent of unified SQL in a Maven or jar loading mode, and develops SQL sentences to submit the unified SQL to the JDBC agent through interface functions (such as execute and execute query) provided by the JDBC agent according to the unified SQL standard.
Step two, the JDBC agent receives the uniform SQL sentences and pushes the uniform SQL sentences to the SQL engine.
And step three, the SQL engine analyzes the base table information in the uniform SQL statement.
And step four, obtaining the base table information obtained by analysis by connecting the native JDBC with the target database, caching the base table information, and then directly obtaining the base table information from the cache without executing the steps again if the base table information is needed.
Step five, the SQL engine sets a change acquisition mechanism of the base table information:
1. and starting a timer thread to refresh all base table information in the cache at intervals.
2. If the DDL is executed under the JDBC agent, step 104 is re-executed 105 and the library table information is cached.
3. Capturing abnormal information when executing DDL and DML statements with errors, judging whether the abnormal information is caused by inconsistency of field names, field types, table names and the like, and then re-executing the step 104 and the step 105 and caching the table information.
Step six, analyzing the unified SQL by combining the cached base table information with an SQL engine (such as an Apache Call engine), and analyzing the unified SQL into a native SQL statement which can be executed by the target database.
The unified SQL statement and the native SQL statement conversion logic of the target database may be extended by configuring a plug-in, for example, the unified SQL is first parsed into an abstract syntax tree, then the syntax, keywords, and functions that need to be replaced in the abstract syntax tree are converted (for example, JAVACC syntax analyzer generator is used, and template parsing SQL is configured), then statement conversion is performed through a customized dialect conversion class, which is, for example, OracleDialect, and in which all syntax, functions, and keyword conversion logic is customized, and finally the converted abstract syntax tree is widely parsed into the native SQL statement of the target database.
For example, the year (or concatenation of multiple functions) function in the unified SQL statement is converted into a year taking function (or concatenation of multiple functions) in the target database oracle: EXTRACT (YEAR FROM COLUMN).
And step seven, the SQL engine sends the native SQL statement to a target database through the native JDBC.
And step eight, the target database executes the native SQL statement and returns an execution result to the target application program sequentially through the native JDBC and the JDBC agents.
In the application, compared with ORM frameworks such as Hibernate, JPA, Mybatis and the like, the concept of the application is based on the SQL conversion of the JDBC layer, that is, the SQL conversion and generation can be performed without using an additional ORM framework, and the JDBC agent mode can be used to integrate with Mybatis seamlessly.
The method specifically comprises the following steps:
step one, introducing JDBC agent into Mybatis configuration file (Maven). An example of code is as follows:
<dependency>
<groupId>com.rdc.bdata</groupId>
<artifactId>hssql-jdbc</artifactId>
<version>1.1.0</version>
</dependency>
step two, developing a database connection code (JDBC agent) according to the JDBC standard, taking the connection database Mysql as an example:
forName ("com.rdc.bdata.hssql.jdbc.driver"); drive party auto registration
String URL＝"hssql:jdbc:mysql:192……:3306/test"；
String USER＝"root"；
String PASSWORD＝"******"；
Connection conn＝DriverManeger.getConnection(URL，USER，PASSWORD)；
It can be seen that the driver is referenced in the code: com, rdc, bdata, hssql, JDBC, driver, which will automatically register in the way of JAVA Service Provider Interface (SPI), add 'hssql' before the Resource Locator (URL) of the JDBC agent of MySQL.
Step three, operating the unified SQL statement through the JDBC agent, for example:
String sql＝"select year(c_time)from user limit 1offset 10"；
PreparedStatement pstm＝conn.prepareStatement(sql)；
ResultSet rs＝pstmt.executeQuery()；
while(rs.next()){
System.out.println(rs.getInt(1))；
}
rs.cloes()；
pstm.close()；
as can be seen, the execution result is the year value of each row of the c _ time field.
And step four, if the database Oracle is required to be connected for execution, the SQL statement is not required to be modified, and only the JDBC URL of which the rear half of the URL of the JDBC agent is Oracle is required to be modified.
It should be noted that, in some scenarios, the original SQL statement of the target database needs to be used, and only the following steps need to be added in front of the original SQL statement: the original keyword may be, for example:
String sql＝"original truncate table mytest"
by using examples, it can be seen that the present patent concept does not refer to an additional ORM framework, and acts directly on the underlying JDBC layer to implement the function of directly converting on the SQL dialect.
Fig. 6 is a schematic structural diagram illustrating a program statement conversion apparatus provided in an embodiment of the present application, where the apparatus may be integrated in a program statement conversion device. As shown in fig. 6, the apparatus may include:
an obtaining module 501, configured to obtain, according to a user operation for a target application program, a standard access statement corresponding to the user operation and a target database to be accessed by the user operation; the standard access statement is based on a preset language standard;
a conversion module 502, configured to perform statement conversion on the standard access statement according to the database type, so as to obtain a native access statement meeting the language standard of the target database.
Optionally, the conversion module 502 is specifically configured to:
determining conversion logic of the standard access statement and the native access statement according to the database type;
analyzing the standard access statement, and determining a field of a preset type in the standard access statement as a field to be converted, wherein the preset type comprises at least one of a function, a grammar and a keyword;
and converting the field to be converted by adopting the conversion logic to obtain the native access statement.
Optionally, the method further comprises:
the analysis module 503 is configured to analyze the standard access statement to obtain the base table information in the standard access statement;
a checking module 504, configured to check the field to be converted according to the base table information.
Optionally, the conversion module 502 is specifically configured to:
parsing the standard access statement into an abstract syntax tree;
and acquiring the field to be converted from the node data on the abstract syntax tree.
Optionally, the parsing module 503 is specifically configured to:
analyzing the standard access statement by starting a timer thread;
and taking the latest analyzed information as the base table information.
Optionally, the parsing module 503 is further configured to:
and if detecting the base table information updating operation aiming at the target database, re-analyzing the standard access statement to refresh the base table information.
Optionally, the method further comprises:
the processing module 505 is configured to execute the native access statement and obtain an execution result corresponding to the native access statement; and returning the execution result sequentially through the native connection interface corresponding to the target database and a preset standard connection interface.
Optionally, the processing module 505 is further configured to:
if the execution result comprises: judging whether the abnormal data is the abnormal data caused by the access object corresponding to the native access statement or not, wherein the abnormal data is the data generated by executing the native access statement;
if so, the parsing module 503 is further configured to parse the standard access statement again to refresh the library table information.
The implementation process and the implementation principle of the program-sentence converting apparatus of this embodiment are similar to those of the method embodiments described above, and are not described herein again.
Fig. 7 is a schematic structural diagram of a program statement conversion device provided in an embodiment of the present application, and as shown in fig. 7, the device includes: a processor 601, a memory 602 and a bus 603, wherein the memory 602 stores machine-readable instructions executable by the processor 601, when the program statement conversion device runs, the processor 601 and the memory 602 communicate through the bus 603, and the processor 601 executes the machine-readable instructions to execute the above method embodiments.
The present application further provides a computer-readable storage medium having stored thereon a computer program which, when executed by a processor, performs the above-described method embodiments.
It can be clearly understood by those skilled in the art that, for convenience and brevity of description, the specific working processes of the system and the apparatus described above may refer to corresponding processes in the method embodiments, and are not described in detail in this application. In the several embodiments provided in the present application, it should be understood that the disclosed system, apparatus and method may be implemented in other ways. The above-described apparatus embodiments are merely illustrative, and for example, the division of the modules is merely a logical division, and there may be other divisions in actual implementation, and for example, a plurality of modules or components may be combined or integrated into another system, or some features may be omitted, or not executed. In addition, the shown or discussed mutual coupling or direct coupling or communication connection may be an indirect coupling or communication connection of devices or modules through some communication interfaces, and may be in an electrical, mechanical or other form.
In addition, functional units in the embodiments of the present application may be integrated into one processing unit, or each unit may exist alone physically, or two or more units are integrated into one unit. The functions, if implemented in the form of software functional units and sold or used as a stand-alone product, may be stored in a computer readable storage medium. Based on such understanding, the technical solution of the present invention may be embodied in the form of a software product, which is stored in a storage medium and includes instructions for causing a computer device (which may be a personal computer, a server, or a network device) to execute all or part of the steps of the method according to the embodiments of the present invention. And the aforementioned storage medium includes: a U-disk, a removable hard disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a magnetic disk or an optical disk, and other various media capable of storing program codes.
The above description is only for the specific embodiments of the present application, but the scope of the present application is not limited thereto, and any person skilled in the art can easily conceive of the changes or substitutions within the technical scope of the present application, and shall be covered by the scope of the present application.
Claims (11)
1. A program statement conversion method, comprising:
according to user operation aiming at a target application program, acquiring a standard access statement corresponding to the user operation and a target database to be accessed by the user operation; the standard access statement is based on a preset language standard;
and performing statement conversion on the standard access statement according to the type of the database to obtain a native access statement meeting the language standard of the target database.
2. The method of claim 1, wherein the performing a statement transformation on the standard access statement according to a database type to obtain a native access statement that satisfies a language standard of the target database comprises:
determining conversion logic of the standard access statement and the native access statement according to the database type;
analyzing the standard access statement, and determining a field of a preset type in the standard access statement as a field to be converted, wherein the preset type comprises at least one of a function, a grammar and a keyword;
and converting the field to be converted by adopting the conversion logic to obtain the native access statement.
3. The method of claim 2, wherein the field to be converted comprises data of a keyword type, and wherein before the converting the field to be converted using the conversion logic to obtain the native access statement, the method further comprises:
analyzing the standard access statement to obtain base table information in the standard access statement;
and checking the field to be converted according to the base table information.
4. The method according to claim 2, wherein the parsing the standard access statement and determining a field of a preset type in the standard access statement as a field to be converted comprises:
parsing the standard access statement into an abstract syntax tree;
and acquiring the field to be converted from the node data on the abstract syntax tree.
5. The method of claim 3, wherein the parsing the standard access statement to obtain the library table information in the standard access statement comprises:
analyzing the standard access statement by starting a timer thread;
and taking the latest analyzed information as the base table information.
6. The method according to claim 3 or 5, wherein after parsing the standard access statement to obtain the library table information in the standard access statement, the method further comprises:
and if detecting the base table information updating operation aiming at the target database, re-analyzing the standard access statement to refresh the base table information.
7. The method of claim 3, further comprising:
executing the native access statement and acquiring an execution result corresponding to the native access statement;
and returning the execution result sequentially through the native connection interface corresponding to the target database and a preset standard connection interface.
8. The method of claim 7, further comprising:
if the execution result comprises: judging whether the abnormal data is the abnormal data caused by the access object corresponding to the native access statement or not, wherein the abnormal data is the data generated by executing the native access statement;
and if so, re-analyzing the standard access statement to refresh the base table information.
9. A program sentence conversion apparatus, comprising:
the system comprises an acquisition module, a processing module and a processing module, wherein the acquisition module is used for acquiring a standard access statement corresponding to a user operation and a target database to be accessed by the user operation according to the user operation aiming at a target application program; the standard access statement is based on a preset language standard;
and the conversion module is used for performing statement conversion on the standard access statement according to the type of the database to obtain a native access statement meeting the language standard of the target database.
10. A program statement conversion apparatus characterized by comprising: a processor, a memory and a bus, the memory storing machine-readable instructions executable by the processor, the processor and the memory communicating via the bus when the program statement conversion device is running, the processor executing the machine-readable instructions to perform the method of any one of claims 1 to 8.
11. A computer-readable storage medium, characterized in that a computer program is stored on the computer-readable storage medium, which computer program, when being executed by a processor, performs the method of any one of claims 1 to 8.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111373357.0A CN114090613A (en) | 2021-11-19 | 2021-11-19 | Program statement conversion method, device, equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111373357.0A CN114090613A (en) | 2021-11-19 | 2021-11-19 | Program statement conversion method, device, equipment and storage medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN114090613A true CN114090613A (en) | 2022-02-25 |
Family
ID=80302034
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202111373357.0A Pending CN114090613A (en) | 2021-11-19 | 2021-11-19 | Program statement conversion method, device, equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN114090613A (en) |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116010445A (en) * | 2023-03-28 | 2023-04-25 | 恒生电子股份有限公司 | Processing method and device of database SQL script |
| CN117033348A (en) * | 2023-08-23 | 2023-11-10 | 中电金信软件有限公司 | SQL conversion method, SQL conversion device, SQL conversion computer equipment and SQL storage medium |
| CN117112538A (en) * | 2023-09-05 | 2023-11-24 | 中电金信软件有限公司 | Database operation method, database operation device, computer equipment and storage medium |
| CN117235107A (en) * | 2023-11-10 | 2023-12-15 | 恒生电子股份有限公司 | Data access processing method and device, electronic equipment and storage medium |
-
2021
- 2021-11-19 CN CN202111373357.0A patent/CN114090613A/en active Pending
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116010445A (en) * | 2023-03-28 | 2023-04-25 | 恒生电子股份有限公司 | Processing method and device of database SQL script |
| CN117033348A (en) * | 2023-08-23 | 2023-11-10 | 中电金信软件有限公司 | SQL conversion method, SQL conversion device, SQL conversion computer equipment and SQL storage medium |
| CN117112538A (en) * | 2023-09-05 | 2023-11-24 | 中电金信软件有限公司 | Database operation method, database operation device, computer equipment and storage medium |
| CN117235107A (en) * | 2023-11-10 | 2023-12-15 | 恒生电子股份有限公司 | Data access processing method and device, electronic equipment and storage medium |
| CN117235107B (en) * | 2023-11-10 | 2024-01-26 | 恒生电子股份有限公司 | Data access processing method and device, electronic equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US8447744B2 (en) | Extensibility platform using data cartridges | |
| US8959106B2 (en) | Class loading using java data cartridges | |
| CN114090613A (en) | Program statement conversion method, device, equipment and storage medium | |
| US9430494B2 (en) | Spatial data cartridge for event processing systems | |
| US9471711B2 (en) | Schema-less access to stored data | |
| CN109614432B (en) | System and method for acquiring data blood relationship based on syntactic analysis | |
| US20100017395A1 (en) | Apparatus and methods for transforming relational queries into multi-dimensional queries | |
| CN111813798B (en) | Mapping method, device, equipment and storage medium based on R2RML standard | |
| US8862563B2 (en) | Getting dependency metadata using statement execution plans | |
| CN109710220B (en) | Relational database query method, relational database query device, relational database query equipment and storage medium | |
| CN113051268A (en) | Data query method, data query device, electronic equipment and storage medium | |
| US20180365294A1 (en) | Artificial intelligence driven declarative analytic platform technology | |
| US7085760B2 (en) | Data query differential analysis | |
| CN113934750A (en) | Data blood relationship analysis method based on compiling mode | |
| CN111078729A (en) | Medical data tracing method, device, system, storage medium and electronic equipment | |
| US20220222253A1 (en) | Sql statement generator | |
| CN108959454B (en) | Prompting clause specifying method, device, equipment and storage medium | |
| CN108008947B (en) | Intelligent prompting method and device for programming statement, server and storage medium | |
| CN114064601B (en) | Storage process conversion method, device, equipment and storage medium | |
| CN110580170B (en) | Method and device for identifying software performance risk | |
| CN117076742A (en) | Data blood edge tracking method and device and electronic equipment | |
| CN116414855A (en) | Information processing method and device, electronic equipment and computer readable storage medium | |
| CN112445867A (en) | Intelligent analysis method and system for data relationship | |
| WO2021259367A1 (en) | Sql unification method, system, and device, and medium | |
| CN112862334A (en) | Index system construction method and device based on syntax analysis tree and computer equipment |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |