Software deployment method, medium and electronic device based on SQL syntax

Abstract

The present application discloses a software deployment method, medium and electronic device based on SQL syntax, and relates to the field of software technology. The method includes: creating a class loading interceptor, and registering a class file converter in the class loading interceptor; determining whether the currently loaded class is a MySQL driver class through the class file converter; if the currently loaded class is a MySQL driver class, intercepting the MySQL driver class through the class loading interceptor, and loading the target driver class; in the static code block of the Oracle database driver class, registering the Oracle database driver class in the JDBC driver manager, and overwriting the connection instance of the Oracle database; packaging the connection instance of the Oracle database to convert the SQL syntax of MySQL into the SQL syntax of Oracle. The present application can effectively reduce development and maintenance costs.

Description

Technical Field

[0001] The embodiments of the present application relate to the field of software technology, and in particular to a software deployment method, medium, and electronic device based on SQL syntax. Background Art

[0002] Currently, the data storage method of Java open source software only supports the MySQL database system. For example, Nacos, as a dynamic service discovery, configuration management and service management software based on Java open source, relies on MySQL for its configuration storage, and the database table structure and operations are designed based on MySQL. In the actual production environment, due to the better performance, reliability and scalability of Oracle database, there is a widespread demand to use Oracle database to store Java open source software when processing large-scale, high-concurrency enterprise-level applications.

[0003] In response to the above problems, when Java open source software only supports MySQL database, due to the differences in SQL syntax between MySQL and Oracle, if Oracle database needs to be used to store Java open source software, it is necessary to customize the Java open source software, rewrite most of the SQL code, and recompile the software before it can be deployed in an environment using Oracle database. As a result, each time a new version of Java open source software is released, the above operations need to be performed before the Java open source software can be deployed in an environment using Oracle database, which greatly increases development and maintenance costs.

[0004] There is currently no effective means to solve the above problems. Summary of the invention

[0005] The embodiments of the present application provide a software deployment method, medium and electronic device based on SQL syntax, which are used to effectively reduce development and maintenance costs.

[0006] To achieve the above objectives, the embodiments of the present application adopt the following technical solutions:

[0007] In a first aspect, a software deployment method based on SQL syntax is provided, the method comprising:

[0008] Create a class loading interceptor and register a class file converter in the class loading interceptor;

[0009] Determining whether the currently loaded class is a MySQL driver class through the class file converter;

[0010] In the case where the currently loaded class is the MySQL driver class, intercepting the MySQL driver class through the class loading interceptor, and loading a target driver class, wherein the target driver class is based on an Oracle driver extension;

[0011] In a static code block of an Oracle database driver class, the Oracle database driver class is registered in a JDBC driver manager, and a connection instance of the Oracle database is overwritten, the connection instance being used to achieve connection with the Oracle database, the Oracle database driver class inherits from the Oracle driver class, and the Oracle database is created by the Oracle driver;

[0012] The connection instance of the Oracle database is packaged to convert the SQL syntax of MySQL into the SQL syntax of Oracle.

[0013] In a possible implementation manner of the first aspect, the overwriting the connection instance of the Oracle database includes:

[0014] Rewrite the connect method in the target driver class;

[0015] Call the connect method of the parent class to obtain the connection instance of the Oracle database;

[0016] A connection decoration class is created, and the connection instance of the Oracle database is encapsulated by the connection decoration class, and the connection decoration class is returned.

[0017] In another possible implementation manner of the first aspect, packaging the connection instance of the Oracle database includes:

[0018] Decorate the createStatement method with the connection decorator class and create a StatementWrapper; and

[0019] The preparedStatement method is decorated with the connection decorator class, and a PreparedStatementWrapper is created, and the SQL conversion tool class is called to perform SQL syntax conversion to convert the SQL syntax created by the preparedStatement into the first SQL syntax of the Oracle database, wherein the createStatement method and the preparedStatement method are used to create SQL syntax to execute SQL queries on the MySQL database;

[0020] Intercepting the execution of non-precompiled SQL grammar through the StatementWrapper, and calling the SQL conversion tool class to perform SQL grammar conversion, so as to convert the non-precompiled SQL grammar into a second SQL grammar of the Oracle database;

[0021] Intercepting the execution of precompiled SQL syntax through the PreparedStatementWrapper and overriding the getGeneratedKeys method, wherein the getGeneratedKeys method is used to obtain the key value automatically generated by the MySQL database after executing the insert operation of the MySQL database;

[0022] Determine whether a fixed mapping relationship exists between the first SQL grammar and the second SQL grammar;

[0023] In the case where the fixed mapping relationship exists, a first target SQL grammar fixedly mapped with the first SQL grammar and a second target SQL grammar fixedly mapped with the second SQL grammar are returned according to the fixed mapping relationship.

[0024] In another possible implementation manner of the first aspect, the method further includes:

[0025] In the case that the fixed mapping relationship does not exist between the first SQL grammar and the second SQL grammar, performing syntax parsing on the first SQL grammar and the second SQL grammar to obtain an SQL syntax tree;

[0026] The special syntax is determined according to the SQL syntax tree and an SQL conversion step is performed to convert the SQL syntax of MySQL into the SQL syntax of Oracle.

[0027] In another possible implementation manner of the first aspect, the SQL conversion step includes:

[0028] Determine whether the SQL syntax of MySQL is a syntax without a table name, and if the SQL syntax of MySQL is a syntax without a table name, set the table name of the SQL syntax of MySQL to dual;

[0029] Determine whether the SQL syntax of MySQL uses the as alias, and if the SQL syntax of MySQL uses the as alias, delete the as alias;

[0030] Determine whether the SQL syntax of MySQL contains a backquote, and if the SQL syntax of MySQL contains the backquote, delete the backquote;

[0031] Determine whether a prohibited symbol is used in a non-equality expression of the SQL grammar of the MySQL, and if the prohibited symbol is used in the non-equality expression of the SQL grammar of the MySQL, replace the prohibited symbol with a corresponding grammar;

[0032] Performing function conversion on the MySQL database function to convert the function corresponding to the MySQL database into a function corresponding to the Oracle database;

[0033] Determine whether the SQL syntax of MySQL is a non-precompiled paging statement, and perform paging conversion if the SQL syntax of MySQL is a non-precompiled paging statement.

[0034] In another possible implementation manner of the first aspect, the method further includes:

[0035] Introducing a cache mechanism into the SQL conversion tool class;

[0036] Calling the SQL conversion tool class to perform SQL syntax conversion and obtain a conversion result;

[0037] The conversion result is cached in Redis through the cache mechanism.

[0038] In another possible implementation of the first aspect, before calling the SQL conversion tool class to perform SQL syntax conversion and obtain a conversion result, the method includes:

[0039] Generate a unique cache key for each of the MySQL SQL grammars, where one or more cache keys are used to represent a historical conversion result;

[0040] The method further comprises:

[0041] Determine in the Redis whether one or more cache keys match the SQL syntax created by the preparedStatement;

[0042] In the case that there are one or more cache keys that hit the SQL grammar created by the preparedStatement, the one or more cache keys that hit the SQL grammar created by the preparedStatement are used as target cache keys, and the historical conversion results corresponding to the target cache keys are obtained, and the historical conversion results are used to characterize the conversion results obtained by calling the SQL conversion tool class to perform SQL grammar conversion.

[0043] In another possible implementation manner of the first aspect, the method further includes:

[0044] In the case where the currently loaded class is the MySQL driver class, the bytecode and byte array of the target driver class are returned;

[0045] In case the currently loaded class is not the MySQL driver class, an empty byte array is returned.

[0046] In a second aspect, the present application provides a machine-readable storage medium having instructions stored thereon, the instructions being used to enable a machine to execute the above-mentioned SQL grammar-based software deployment method.

[0047] In a third aspect, the present application provides an electronic device, including:

[0048] The machine-readable storage medium described above;

[0049] The processor is configured to call instructions from the machine-readable storage medium and implement the above-mentioned software deployment method based on SQL syntax when executing the instructions.

[0050] Through the above technical solution, by creating a class loading interceptor and registering a class file converter in the class loading interceptor to determine whether the currently loaded class is a MySQL driver class, the MySQL driver class can be dynamically detected and processed when the Java open source software is running; when it is detected that the currently loaded class is a MySQL driver class, the class is intercepted by the class loading interceptor, and the target driver class based on the Oracle driver extension is loaded, so that the MySQL driver can be dynamically replaced with the Oracle driver without modifying the Java open source software source code; in the static code block of the Oracle database driver class, the class is registered in the JDBC driver manager and the connection instance is overwritten, which can ensure that the Java open source software uses the Oracle database driver class for database connection at runtime; the connection instance of the Oracle database is packaged to convert the SQL syntax of MySQL into the SQL syntax of Oracle, which can solve the difference in SQL syntax between MySQL and Oracle. In summary, dynamic interception and conversion avoids the need for custom development of Java open source software and rewriting SQL code. In addition, there is no need to recompile and modify the code every time a new version of the software is released, which greatly reduces development and maintenance costs. It also allows Java open source software to be flexibly deployed in an environment using an Oracle database without modifying the software itself, effectively improving deployment flexibility.

[0051] Other features and advantages of the embodiments of the present application will be described in detail in the subsequent specific implementation section. BRIEF DESCRIPTION OF THE DRAWINGS

[0052] Figure 1A flowchart of a software deployment method based on SQL syntax provided in an embodiment of the present application;

[0053] Figure 2 A schematic diagram of the overall process of a software deployment method based on SQL syntax provided in an embodiment of the present application;

[0054] Figure 3 A comparative flowchart of a software deployment method based on SQL syntax provided in an embodiment of the present application. DETAILED DESCRIPTION

[0055] In order to make the purpose, technical scheme and advantages of the embodiments of the present application clearer, the technical scheme in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. It should be understood that the specific implementation methods described herein are only used to illustrate and explain the embodiments of the present application, and are not used to limit the embodiments of the present application. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of this application.

[0056] It should be noted that if the embodiments of the present application involve directional indications (such as up, down, left, right, front, back...), such directional indications are only used to explain the relative position relationship, movement status, etc. between the components under a certain specific posture (as shown in the accompanying drawings). If the specific posture changes, the directional indication will also change accordingly.

[0057] In addition, if there are descriptions involving "first", "second", etc. in the embodiments of the present application, the descriptions of "first", "second", etc. are only used for descriptive purposes and cannot be understood as indicating or suggesting their relative importance or implicitly indicating the number of technical features indicated. Therefore, the features defined as "first" and "second" may explicitly or implicitly include at least one of the features. In addition, the technical solutions between the various embodiments can be combined with each other, but they must be based on the ability of ordinary technicians in the field to implement them. When the combination of technical solutions is contradictory or cannot be implemented, it should be deemed that such combination of technical solutions does not exist and is not within the scope of protection required by this application.

[0058] Figure 1 The following schematically shows a flow chart of a software deployment method based on SQL syntax according to an embodiment of the present application. Figure 1 As shown, an embodiment of the present application provides a software deployment method based on SQL syntax, which may include the following steps.

[0059] S110, creating a class loading interceptor, and registering a class file converter in the class loading interceptor;

[0060] S120, determining whether the currently loaded class is a MySQL driver class through a class file converter;

[0061] S130, when the currently loaded class is a MySQL driver class, intercepting the MySQL driver class through a class loading interceptor, and loading a target driver class, wherein the target driver class is based on an Oracle driver extension;

[0062] S140, in a static code block of an Oracle database driver class, registering the Oracle database driver class in a JDBC driver manager, and overwriting a connection instance of an Oracle database, where the connection instance is used to achieve connection with an Oracle database, the Oracle database driver class inherits from the Oracle driver class, and the Oracle database is created by the Oracle driver;

[0063] S150. Package the connection instance of the Oracle database to convert the SQL syntax of MySQL into the SQL syntax of Oracle.

[0064] The class loading interceptor is used to intercept and process the Java virtual machine when it loads a class. Specifically, the class loading interceptor can monitor and control the class loading process. By registering a class file converter in the class loading interceptor, the identification and conversion of specific classes can be achieved. The class file converter is used to analyze and modify the bytecode for processing before the class is loaded. Specifically, when the Java virtual machine attempts to load a class, the class loading interceptor first captures this request and passes the class file to the class file converter for inspection and conversion to achieve control and customization of class loading behavior without modifying the original code.

[0065] After receiving the class file, the class file converter first parses its bytecode structure to determine the specific type of the currently loaded class. Bytecode is a binary representation of a Java class, which contains all the information of the class, such as the class name, parent class, interface, method, and field. The class file converter checks the fully qualified name of the class (for example, com.mysql.jdbc.Driver) to determine whether it is a MySQL driver class, and accurately identifies the MySQL driver class without relying on hard coding of the class name. After identifying the MySQL driver class, the class file converter sends a signal to the class loading interceptor, indicating that it needs special processing. This approach can flexibly deal with different versions and variants of MySQL driver classes.

[0066] In the case that the currently loaded class is a MySQL driver class, the MySQL driver class is intercepted by a class loading interceptor, and a target driver class is loaded. The target driver class is based on an Oracle driver extension. After receiving a signal from the class file converter, the class loading interceptor blocks further loading of the MySQL driver class. Instead, the class loading interceptor loads a predefined target driver class. This target driver class is based on an Oracle driver extension, which means that the target driver class inherits all the functions of the Oracle driver class and adds additional logic to accommodate specific needs. The process of loading the target driver class can be to find and load the bytecode of the class from the class path and then register it with the Java virtual machine. By adopting the above method, the MySQL driver class can be replaced without modifying the application code so that it uses the Oracle driver class for database connection.

[0067] In the static code block of the Oracle database driver class, register the Oracle database driver class with the JDBC driver manager and overwrite the connection instance of the Oracle database. The connection instance is used to connect to the Oracle database. The Oracle database driver class inherits from the Oracle driver class, and the Oracle database is created through the Oracle driver. The static code block is a piece of code that is automatically executed when the class is loaded, and is used to initialize the static members of the class. In the static code block of the Oracle database driver class, first register the Oracle database driver class with the JDBC driver manager to make it one of the available JDBC drivers.

[0068] Next, customize the connection creation process by overriding the connection instance of the Oracle database. The connection instance is an object used to manage connections to the database. By overriding the connection instance, you can add additional logic, such as connection pool management, connection parameter configuration, etc. In this way, you can ensure that all database connection operations are performed through the customized Oracle driver class, thereby achieving efficient connection and management with the Oracle database.

[0069] Wrap the connection instance of the Oracle database to convert the SQL syntax of MySQL to the SQL syntax of Oracle. The packaging connection instance is used to realize the automatic conversion of SQL syntax without modifying the application code.

[0070] Specifically, the wrapper connection instance can analyze and convert SQL syntax to meet Oracle's requirements before executing SQL statements. For example, the LIMIT statement used by MySQL can be converted to Oracle's ROWNUM statement, and the string concatenation operation can be converted from MySQL's CONCAT function to Oracle's || operator. By parsing and modifying SQL statements, the wrapper class can ensure that all database operations can be executed correctly on the Oracle database, so that the MySQL SQL syntax can be seamlessly adapted to the Oracle database, avoiding the tedious work of manually modifying a large amount of SQL code.

[0071] This embodiment creates a class loading interceptor and registers a class file converter in the class loading interceptor to determine whether the currently loaded class is a MySQL driver class, so that the MySQL driver class can be dynamically detected and processed when the Java open source software is running; when it is detected that the currently loaded class is a MySQL driver class, the class is intercepted by the class loading interceptor, and the target driver class based on the Oracle driver extension is loaded, so that the MySQL driver can be dynamically replaced with the Oracle driver without modifying the Java open source software source code; in the static code block of the Oracle database driver class, the class is registered in the JDBC driver manager, and the connection instance is overwritten, so that the Java open source software can use the Oracle database driver class for database connection at runtime; the connection instance of the Oracle database is packaged to convert the SQL syntax of MySQL to the SQL syntax of Oracle, which can solve the difference between MySQL and Oracle in SQL syntax. In summary, through dynamic interception and conversion, the need for customized development and rewriting of SQL code for Java open source software is avoided. In addition, there is no need to recompile and modify the code every time a new version of the software is released, which greatly reduces the development and maintenance costs, and can enable Java open source software to be flexibly deployed in an environment using an Oracle database without modifying the software itself, effectively improving deployment flexibility.

[0072] In one implementation of this embodiment, overwriting the connection instance of the Oracle database includes the following steps:

[0073] S210, rewrite the connect method in the target driver class;

[0074] S220, calling the connect method of the parent class to obtain a connection instance of the Oracle database;

[0075] S230, creating a connection decoration class, encapsulating the connection instance of the Oracle database through the connection decoration class, and returning the connection decoration class.

[0076] The connect method is the core method used in the JDBC driver class to create a database connection. By overriding the connect method, you can insert custom logic into the connection establishment process.

[0077] It is important to note that when overriding the connect method, you need to ensure that the method signature is consistent with the connect method in the parent class so that the parent class method can be overridden correctly. Specifically, the connect method accepts a database URL and a Properties object as parameters and returns a Connection object. When overriding the connect method, you can add additional logic to the method body, such as logging, adjustment of connection parameters, or other initialization operations. The main purpose of overriding the connect method is to customize the connection creation process to meet specific needs without changing the way the application calls it. For example, you can add code to the overridden method to record the time and source of each connection request to facilitate subsequent performance analysis and troubleshooting.

[0078] The parent class of the target driver class is the Oracle driver class. Specifically, the target driver class inherits from the Oracle driver class, which means that the target driver class inherits all the functions and features of the Oracle driver class, and can add additional functions and behaviors by overriding and extending. The Oracle driver class can be the JDBC driver class provided by Oracle, such as oracle.jdbc.OracleDriver, which is used to communicate with the Oracle database.

[0079] After overriding the connect method, you need to call the parent class's connect method to actually establish a connection with the Oracle database. The parent class's connect method has implemented the underlying communication logic with the database. By calling the parent class's connect method, you can ensure the reliability and correctness of the connection process.

[0080] When calling the parent class method, you can use the super keyword, for example, super.connect(url, info), where url is the URL of the database and info is a Properties object containing the connection parameters. After calling the parent class method, a Connection object is returned, which represents the connection instance to the Oracle database. The connection instance of the Oracle database is a JDBC connection object that contains all the necessary functions and properties. By calling the parent class's connect method, you can avoid duplicating the underlying communication logic.

[0081] Creating a connection decorator class, encapsulating the Oracle database connection instance through the connection decorator class, and returning the connection decorator class is the last step of the overwriting process. The connection decorator class is used to add additional functions or behaviors without modifying the original connection class. When creating a connection decorator class, you can define a new class that implements the Connection interface and holds an actual Connection object as a member variable.

[0082] In the connection decoration class, you can override methods in the Connection interface, such as createStatement, prepareStatement, and close, to add custom logic. For example, you can add SQL syntax conversion logic in the executeQuery method to convert MySQL SQL syntax to Oracle SQL syntax.

[0083] The process of encapsulating the connection instance includes accepting an actual Connection object in the constructor of the connection decoration class and assigning it to the member variable to extend its functionality without changing the original connection object. Finally, in the rewritten connect method, an instance of the connection decoration class is returned instead of the actual Connection object. In this way, all connections created by the driver class will be encapsulated in the connection decoration class, thereby implementing customized connection behavior and logic.

[0084] This implementation achieves overriding and extending the Oracle database connection instance. Overriding the connect method allows custom logic to be inserted into the connection creation process, and calling the connect method of the parent class can ensure the reliability and standardization of the connection process. Create a connection decoration class to add additional functions and behaviors by encapsulating the actual connection object. Finally, return an instance of the connection decoration class so that all connection operations are processed by customized logic, which not only enhances the flexibility of connection management, but also effectively ensures the scalability and maintainability of Java open source software, so that connection management and SQL syntax conversion logic can be implemented without modifying the application code to meet the needs of different database environments.

[0085] In one implementation of this embodiment, packaging the connection instance of the Oracle database includes the following steps:

[0086] S310, decorate the createStatement method using a connection decorator class, and create a StatementWrapper; and

[0087] S320, decorate the preparedStatement method with a connection decorator class, create a PreparedStatementWrapper, call the SQL conversion tool class to perform SQL syntax conversion, so as to convert the SQL syntax created by preparedStatement into the first SQL syntax of the Oracle database, wherein the createStatement method and the preparedStatement method are used to create the SQL syntax to execute the SQL query on the MySQL database;

[0088] S330, intercepting the execution of the non-precompiled SQL syntax through StatementWrapper, and calling the SQL conversion tool class to perform SQL syntax conversion, so as to convert the non-precompiled SQL syntax into the second SQL syntax of the Oracle database;

[0089] S340, intercepting the execution of the precompiled SQL syntax through PreparedStatementWrapper, and overriding the getGeneratedKeys method, where the getGeneratedKeys method is used to obtain the key value automatically generated by the MySQL database after executing the insert operation of the MySQL database;

[0090] S350, determining whether there is a fixed mapping relationship between the first SQL grammar and the second SQL grammar;

[0091] S360: In the case where a fixed mapping relationship exists, return a first target SQL grammar fixedly mapped with the first SQL grammar and a second target SQL grammar fixedly mapped with the second SQL grammar according to the fixed mapping relationship.

[0092] The createStatement method is a method in the JDBC interface used to create a Statement object, which allows static SQL statements to be executed and returns results. By decorating the createStatement method, you can insert custom logic when creating a Statement object.

[0093] Specifically, when decorating the createStatement method, you can override this method in the connection decoration class, call the actual Connection object's createStatement method in the method body, and obtain the original Statement object. Then, use the StatementWrapper class to wrap the original Statement object. The StatementWrapper class implements the Statement interface and holds an actual Statement object as a member variable. In the StatementWrapper class, you can override methods such as executeQuery and executeUpdate to add custom logic, such as SQL syntax conversion, logging, or performance monitoring. In this way, all Statement objects created by the connection decoration class will be wrapped in StatementWrapper to implement custom behavior and logic.

[0094] The preparedStatement method is used to create a PreparedStatement object. The PreparedStatement object allows the execution of precompiled SQL statements. By decorating the preparedStatement method, you can insert custom logic when creating the PreparedStatement object. Specifically, when decorating the preparedStatement method, you can override the method in the connection decoration class, call the preparedStatement method of the actual Connection object in the method body, and obtain the original PreparedStatement object. Then, use the PreparedStatementWrapper class to wrap the original PreparedStatement object. The PreparedStatementWrapper class implements the PreparedStatement interface and holds an actual PreparedStatement object as a member variable.

[0095] In the PreparedStatementWrapper class, you can override methods such as executeQuery and executeUpdate, and call the SQL conversion tool class to convert SQL syntax. The SQL conversion tool class is an independent tool class used to convert MySQL SQL syntax to Oracle SQL syntax. For example, you can call the convert method of the SQL conversion tool class in the executeQuery method to convert the incoming MySQL SQL statement to an Oracle SQL statement, and then execute the converted SQL statement. In this way, all PreparedStatement objects created by the connection decorator class will be wrapped in PreparedStatementWrapper and implement the SQL syntax conversion function.

[0096] Non-precompiled SQL syntax refers to SQL statements executed using the Statement object. Non-precompiled SQL syntax will be parsed and compiled by the database each time it is executed. By intercepting the execution of SQL syntax, it can be converted before execution.

[0097] Specifically, in the StatementWrapper class, you can rewrite methods such as executeQuery and executeUpdate, and call the convert method of the SQL conversion tool class in the method body to convert the incoming MySQL SQL statement into an Oracle SQL statement. The converted SQL statement can be executed through the actual Statement object and the result is returned.

[0098] For example, in the executeQuery method, you can first call the convert method of the SQL conversion tool class to convert the incoming query statement into an Oracle query statement, and then call the executeQuery method of the actual Statement object to execute the converted query statement and return the ResultSet object. This ensures that all non-precompiled SQL syntaxes have undergone SQL syntax conversion before execution to achieve cross-database compatibility.

[0099] Precompiled SQL syntax refers to SQL statements executed using PreparedStatement objects. Precompiled SQL syntax has been compiled by the database when it is created, which can improve execution efficiency. By intercepting the execution of precompiled SQL syntax, you can convert it before execution and obtain the generated key value after execution.

[0100] Specifically, in the PreparedStatementWrapper class, you can override methods such as executeQuery and executeUpdate, and call the convert method of the SQL conversion tool class in the method body to convert the incoming MySQLSQL statement into an Oracle SQL statement. The converted SQL statement can be executed through the actual PreparedStatement object and the result is returned. In addition, you can also override the getGeneratedKeys method, which is used to obtain the key value automatically generated by the database after the insert operation is performed. In the overridden getGeneratedKeys method, you can call the getGeneratedKeys method of the actual PreparedStatement object to obtain the generated key value and return it to the caller, so as to ensure that all precompiled SQL syntax has undergone SQL syntax conversion before execution and the generated key value can be correctly obtained.

[0101] The fixed mapping relationship between the first SQL grammar and the second SQL grammar refers to a one-to-one correspondence between the two SQL grammars. The fixed mapping relationship can simplify the SQL grammar conversion process.

[0102] Specifically, by analyzing the SQL syntax characteristics of MySQL and Oracle, we can determine which syntaxes have fixed mapping relationships. For example, MySQL's LIMIT statement can be converted to Oracle's ROWNUM statement, and MySQL's AUTO_INCREMENT can be converted to Oracle's SEQUENCE. Based on this, we can establish a mapping relationship table to record each MySQL SQL syntax and its corresponding Oracle SQL syntax. In the actual SQL syntax conversion process, we can perform conversion based on the mapping relationship table. If there is a fixed mapping relationship, we can directly use the target SQL syntax in the mapping table to replace it, thereby improving conversion efficiency and accuracy.

[0103] In the case of a fixed mapping relationship, the first target SQL syntax fixedly mapped with the first SQL syntax and the second target SQL syntax fixedly mapped with the second SQL syntax are returned according to the fixed mapping relationship. Specifically, a method can be implemented in the SQL conversion tool class, which accepts a MySQL SQL statement as input and first checks whether it has a fixed mapping relationship. If a fixed mapping relationship exists, the corresponding Oracle SQL syntax can be directly obtained from the mapping relationship table and returned to the caller. For example, a convert method can be implemented, which accepts a MySQL SQL statement, first parses the type of the statement (query, insert, update, etc.), and then searches the mapping relationship table according to the type to obtain the corresponding Oracle SQL syntax. If the corresponding Oracle SQL syntax is found, it can be returned directly; if the corresponding Oracle SQL syntax is not found, other conversion logic can be called for processing, so as to ensure that all SQL syntaxes can be correctly converted to Oracle SQL syntax and achieve cross-database compatibility.

[0104] This implementation adopts a connection decorator class to decorate the createStatement and preparedStatement methods, and intercepts and converts the SQL syntax by creating the StatementWrapper and PreparedStatementWrapper classes. The SQL conversion tool class is called to realize the conversion from MySQL SQL syntax to Oracle SQL syntax. By overriding the getGeneratedKeys method, it is ensured that the generated key value can be correctly obtained after the insert operation is performed. By determining the fixed mapping relationship, the efficiency and accuracy of the SQL syntax conversion are improved. Finally, cross-database compatibility is achieved, so that the SQL query executed on the MySQL database can be correctly executed on the Oracle database and return the expected results, which not only enhances the flexibility and scalability of the system, but also ensures the stability and reliability of the application.

[0105] In one implementation of this embodiment, the following steps are also included:

[0106] S410: When there is no fixed mapping relationship between the first SQL grammar and the second SQL grammar, parse the first SQL grammar and the second SQL grammar to obtain an SQL grammar tree;

[0107] S420, determining the special syntax according to the SQL syntax tree and executing the SQL conversion step to convert the SQL syntax of MySQL into the SQL syntax of Oracle.

[0108] When there is no fixed mapping relationship between the first SQL grammar and the second SQL grammar, the first SQL grammar and the second SQL grammar are parsed to obtain an SQL grammar tree. In other words, when there is no fixed mapping relationship between the SQL grammars, it is necessary to use grammar parsing technology to understand and convert SQL statements.

[0109] In specific implementation, you can use a SQL parser tool (such as ANTLR or SQLAlchemy) to parse the input MySQL SQL statement. The parser can decompose the SQL statement into a syntax tree. The syntax tree is a hierarchical representation of the SQL statement. Each node represents a component of the SQL statement, such as keywords such as SELECT, FROM, WHERE, as well as table names, column names, conditions, etc. Based on this, you can understand the structure of the SQL statement and the relationship between the various components in detail. For example, for a complex SELECT statement, the parser can identify the SELECT part, FROM part, WHERE part, GROUP BY part, etc., and organize them into a syntax tree. The root node of the syntax tree represents the entire SQL statement, and the child nodes represent the various components. In summary, complex SQL statements can be converted into structured data that is easy to process, thus laying the foundation for subsequent SQL conversion steps.

[0110] After obtaining the SQL syntax tree, you can traverse each node of the syntax tree to identify the special syntax that needs to be converted. For example, the LIMIT statement of MySQL has no direct corresponding syntax in Oracle and needs to be converted to a ROWNUM statement. When traversing the syntax tree, you can check whether there is a LIMIT node. If so, convert it to the corresponding ROWNUM statement.

[0111] In specific implementation, a method can be implemented in the SQL conversion tool class, which accepts the SQL syntax tree as input, traverses each node of the syntax tree, and identifies and converts special syntax. For each node that needs to be converted, a new node can be created to represent the converted Oracle syntax and inserted into the syntax tree. For example, for a LIMIT statement, a new ROWNUM node can be created and inserted into the corresponding position. In this way, the SQL syntax of MySQL can be gradually converted to the SQL syntax of Oracle. After completing the conversion of all nodes, the syntax tree can be reassembled into a complete SQL statement and returned to the caller to ensure that all special syntax that needs to be converted is correctly processed, thereby achieving cross-database compatibility.

[0112] In practical applications, the process of parsing and converting SQL syntax trees may involve multiple steps and complex logic. For example, for a complex SQL statement containing subqueries and union queries, the parser needs to identify the structure of each subquery and union query and process them separately. A recursive algorithm can be used to traverse each node of the syntax tree, and for each subquery and union query, the corresponding conversion method is called for processing.

[0113] In addition, you need to consider other special syntax in SQL statements, such as MySQL's AUTO_INCREMENT, IFNULL, etc. These syntaxes have no direct corresponding syntax in Oracle and need special processing. For example, you can convert AUTO_INCREMENT to Oracle's SEQUENCE and convert IFNULL to NVL function. In this way, you can ensure that all special syntax is properly processed, thereby achieving comprehensive conversion of SQL syntax.

[0114] In this implementation mode, when there is no fixed mapping relationship between the first SQL syntax and the second SQL syntax, the SQL statement is decomposed into a syntax tree by the SQL parser, so that the structure of the SQL statement and the relationship between the various components can be understood in detail. By traversing the syntax tree to identify and convert special syntax, it can be ensured that all special syntax that needs to be converted is correctly processed. Finally, the converted syntax tree is reassembled into a complete SQL statement, and a comprehensive conversion from MySQL's SQL syntax to Oracle's SQL syntax is achieved, so that complex SQL queries executed on the MySQL database can be correctly executed on the Oracle database and return the expected results, thereby effectively ensuring cross-database compatibility, and users can seamlessly run their applications in different database environments.

[0115] In one implementation of this embodiment, the SQL conversion step includes the following steps:

[0116] S510, determining whether the SQL syntax of MySQL is a syntax without a table name, and if the SQL syntax of MySQL is a syntax without a table name, setting the table name of the SQL syntax of MySQL to dual;

[0117] S520, determining whether the SQL syntax of MySQL uses the as alias, and if the SQL syntax of MySQL uses the as alias, deleting the as alias;

[0118] S530, determining whether the SQL syntax of MySQL contains a backquote, and if the SQL syntax of MySQL contains a backquote, deleting the backquote;

[0119] S540, determining whether a prohibited symbol is used in a non-equality expression of the SQL grammar of MySQL, and if a prohibited symbol is used in a non-equality expression of the SQL grammar of MySQL, replacing the prohibited symbol with a corresponding grammar;

[0120] S550, performing function conversion on the MySQL database function to convert the function corresponding to the MySQL database into a function corresponding to the Oracle database;

[0121] S560: Determine whether the SQL syntax of MySQL is a non-precompiled paging statement, and perform paging conversion if the SQL syntax of MySQL is a non-precompiled paging statement.

[0122] Determine whether the SQL syntax of MySQL is a syntax without a table name, and if the SQL syntax of MySQL is a syntax without a table name, set the table name of the SQL syntax of MySQL to dual. MySQL allows the table name to be omitted in the query, while Oracle requires that each query must have a table name. In this case, it is necessary to detect whether the SQL syntax of MySQL omits the table name. For example, for a simple SELECT 1 query, MySQL allows this syntax, but a table name must be specified in Oracle. To achieve this, a check step can be added to the SQL parser to identify queries without table names. If the syntax without a table name is detected, the table name can be automatically set to dual, which is represented as a virtual table name in Oracle and is used to obtain data from queries that do not require an actual table.

[0123] In the specific implementation, when parsing the SQL syntax tree, check whether the FROM clause of the SELECT statement is empty. If it is empty, add a dual table name node in the syntax tree, so as to ensure that each query in the converted Oracle SQL syntax contains a valid table name to avoid syntax errors.

[0124] Determine whether the MySQL SQL syntax uses the as alias, and if so, delete the as alias. Both MySQL and Oracle support column and table aliases, but in actual use, MySQL allows the as keyword to be added before the alias, while Oracle usually does not use the as keyword. To ensure that the SQL syntax is correctly executed in Oracle, you need to detect and delete the as alias.

[0125] In specific implementation, when parsing the SQL syntax tree, the alias node of each column and table can be checked to determine whether it contains the as keyword. If the as keyword is detected, it can be deleted and only the alias itself is retained. For example, for SELECTcol1 as c1 FROM table1, the parser recognizes as c1 and converts it to c1, effectively avoiding syntax errors.

[0126] Determines whether the MySQL SQL syntax contains backticks, and removes the backticks if the MySQL SQL syntax contains backticks. MySQL allows backticks to be used to quote table and column names to avoid conflicts with SQL keywords or to contain special characters, while Oracle uses double quotes to quote identifiers. To ensure that the SQL syntax is executed correctly in Oracle, backticks need to be detected and removed.

[0127] In specific implementation, when parsing the SQL syntax tree, each table name and column name node can be checked to determine whether it contains backticks. If backticks are detected, they can be deleted or replaced with double quotes as needed. For example, for SELECT `col1` FROM `table1`, the parser will identify the backticks and delete or replace them with double quotes. In this way, it can be ensured that the converted SQL syntax meets Oracle standards and effectively avoids syntax errors.

[0128] Determine whether prohibited symbols are used in non-equal expressions of MySQL's SQL syntax, and replace the prohibited symbols with corresponding syntax if prohibited symbols are used in non-equal expressions of MySQL's SQL syntax. MySQL and Oracle may use different symbols or syntaxes when processing non-equal expressions. For example, MySQL uses != to indicate inequality, while Oracle uses <> to indicate inequality. In order to ensure that the SQL syntax is correctly executed in Oracle, it is necessary to detect and replace prohibited symbols. In specific implementation, when parsing the SQL syntax tree, each non-equal expression node can be checked to determine whether it contains prohibited symbols. If prohibited symbols are detected, they can be replaced with corresponding Oracle syntax. For example, for SELECT * FROM table1WHERE col1 != 1, the parser will recognize the != symbol and replace it with <>. In this way, it can be ensured that the converted SQL syntax meets Oracle's standards and syntax errors are effectively avoided.

[0129] In addition, the prohibited symbol can be the ! symbol. If the ! symbol is used in the non-equal expression of the MySQL SQL syntax, the ! symbol is replaced with the NOT syntax.

[0130] Perform function conversion on MySQL database functions to convert the functions corresponding to MySQL database to those corresponding to Oracle database. MySQL and Oracle provide many built-in functions, but the names and syntax may be different. For example, the IFNULL function of MySQL corresponds to the NVL function in Oracle. In order to ensure that the SQL syntax is correctly executed in Oracle, it is necessary to detect and convert the database functions.

[0131] In specific implementation, when parsing the SQL syntax tree, each function call node can be checked to determine whether it is a built-in function of MySQL. If a MySQL function is detected, it can be replaced with the corresponding Oracle function. For example, for SELECT IFNULL(col1,0) FROM table1, the parser will identify the IFNULL function and convert it to NVL(col1,0). In this way, it can be ensured that the converted SQL syntax meets the Oracle standard and effectively avoids syntax errors.

[0132] Determine whether the SQL syntax of MySQL is a non-precompiled paging statement, and perform paging conversion if the SQL syntax of MySQL is a non-precompiled paging statement. MySQL and Oracle use different syntaxes when processing paging queries.

[0133] Specifically, MySQL uses the LIMIT and OFFSET keywords, while Oracle uses the ROWNUM pseudo column or the ROW_NUMBER() window function. To ensure that the SQL syntax is executed correctly in Oracle, these paging statements need to be detected and converted.

[0134] In specific implementation, when parsing the SQL syntax tree, the paging query node can be checked to determine whether it contains the LIMIT and OFFSET keywords. If a paging query is detected, it can be converted to the corresponding Oracle syntax. For example, for SELECT * FROM table1 LIMIT 10 OFFSET 20, the parser will identify the LIMIT and OFFSET keywords and convert them to ROWNUM or ROW_NUMBER() syntax, thereby ensuring that the converted SQL syntax meets Oracle standards and effectively avoids syntax errors.

[0135] like Figure 2As shown in the figure, when the SQL syntax of MySQL is a precompiled paging statement, the page number information is stored in ThreadLocal. Specifically, using ThreadLocal to store page number information means storing paging-related information (such as the current page number and the number of records per page) in thread local variables. ThreadLocal is a class in Java that provides an independent copy of a variable for each thread to avoid variable sharing and data competition between threads.

[0136] This implementation achieves a comprehensive conversion of MySQL SQL syntax, ensuring its correct execution in the Oracle database, thereby ensuring cross-database compatibility, so that complex SQL queries executed on the MySQL database can be correctly executed on the Oracle database and return expected results.

[0137] In one implementation of this embodiment, the following steps are also included:

[0138] S610, introducing a cache mechanism into the SQL conversion tool class;

[0139] S620, calling the SQL conversion tool class to perform SQL syntax conversion and obtain the conversion result;

[0140] S630: Cache the conversion result in Redis through a cache mechanism.

[0141] The cache mechanism is introduced in the SQL conversion tool class to improve the efficiency and performance of SQL conversion. The introduction of the cache mechanism can be achieved by adding a cache management module in the tool class.

[0142] Specifically, a distributed data structure can be used to store converted SQL statements and their corresponding conversion results. Whenever the tool class receives a new SQL statement, it first checks whether the statement already exists in the cache. If it does, it directly returns the conversion result in the cache, thus avoiding repeated conversions and saving computing resources and time.

[0143] To ensure the effectiveness of the cache, you can set the cache size and expiration time to prevent excessive cache data or expired data from occupying memory. For example, you can use the LRU (Least Recently Used) algorithm to manage the cache. When the cache reaches the set maximum capacity, the cache items that have not been used for the longest time are automatically deleted. This can not only effectively improve the efficiency of SQL conversion, but also effectively manage memory usage.

[0144] When calling the SQL conversion tool class to convert SQL syntax and obtain the conversion result, the SQL conversion tool class needs to parse the input MySQL SQL statement and convert it into a statement that conforms to Oracle's SQL syntax. The specific implementation method includes parsing the SQL syntax tree, identifying MySQL-specific syntax and functions, and replacing them with corresponding Oracle syntax and functions. For example, for a paging query containing the LIMIT and OFFSET keywords, the tool class will identify these keywords and convert them into Oracle's ROWNUM or ROW_NUMBER() syntax.

[0145] In addition, the tool class also needs to process MySQL's IFNULL function and convert it to Oracle's NVL function. By calling the SQL conversion tool class, complex SQL conversion tasks can be automated, effectively reducing the workload and error rate of manual conversion.

[0146] This embodiment uses a cache mechanism to cache the conversion results in Redis, which can further improve the efficiency and performance of SQL conversion. Redis is a high-performance memory database suitable for storing and quickly retrieving frequently accessed data.

[0147] Specifically, the Redis API is called in the SQL conversion tool class to store the converted SQL statement and its corresponding conversion result in Redis. Whenever the tool class receives a new SQL statement, it first checks whether the conversion result of the statement already exists in Redis. If it does, the conversion result is directly obtained from Redis without the need to convert it again. This not only improves the efficiency of SQL conversion, but also reduces the number of database accesses and reduces the system load.

[0148] To ensure the validity of cached data, you can set an expiration time for each cache item to prevent expired data from occupying memory. For example, you can use the Redis EXPIRE command to set a TTL (Time To Live) for each cache item. When the cache item expires, it is automatically deleted to effectively ensure the real-time and validity of the cached data.

[0149] This implementation method can significantly improve the efficiency and performance of SQL conversion by introducing a cache mechanism, calling an SQL conversion tool class to perform SQL syntax conversion, and caching the conversion results in Redis. Through the cache mechanism, the workload of repeated conversions can be effectively reduced, saving computing resources and time. The introduction of Redis further improves the performance and management efficiency of the cache, ensuring the real-time and effectiveness of cached data. Ultimately, cross-database compatibility can be ensured, so that complex SQL queries executed on a MySQL database can be correctly executed on an Oracle database and return expected results.

[0150] In one implementation of this embodiment, before calling the SQL conversion tool class to perform SQL syntax conversion and obtaining the conversion result, the following steps are included:

[0151] S710, generating a unique cache key for each MySQL SQL syntax, where one or more cache keys are used to represent a historical conversion result;

[0152] The method also includes:

[0153] S720, determining in Redis whether there is a hit between one or more cache keys and the SQL syntax created by preparedStatement;

[0154] S730. When there are one or more cache keys that hit the SQL syntax created by preparedStatement, the one or more cache keys that hit the SQL syntax created by preparedStatement are used as target cache keys, and the historical conversion results corresponding to the target cache keys are obtained. The historical conversion results are used to represent the conversion results obtained by calling the SQL conversion tool class to perform SQL syntax conversion.

[0155] Generating a unique cache key for each MySQL SQL syntax can ensure the effectiveness of the cache mechanism. Specifically, the hash value of the SQL statement can be used as the cache key. Specifically, when a new SQL statement is received, the statement must first be standardized, such as removing extra spaces and comments, to ensure that SQL statements with the same logic generate the same cache key. Then, a hash function (such as SHA-256) is used to calculate the hash value of the standardized SQL statement, and the hash value is used as the cache key. In this way, each SQL statement corresponds to a unique cache key to ensure the uniqueness and accuracy of the cached data. For example, for the SQL statement "SELECT * FROM table1 WHERE col1 = 1", first remove extra spaces and comments, and then calculate its SHA-256 hash value to obtain a unique cache key. Based on this, cached data can be effectively managed and retrieved, improving the efficiency and accuracy of the cache mechanism.

[0156] After receiving a new SQL statement, first generate a cache key for the statement, then query the Redis database to check whether the cache key exists. If it exists, it means that the conversion result of the SQL statement has been cached, and the cached result can be used directly without converting it again. In order to improve query efficiency, you can use the Redis GET command to quickly retrieve the cache item corresponding to the cache key. For example, for the cache key "abc123", you can use the command "GET abc123" to query the Redis database to check whether the key exists. If it exists, it means that the conversion result of the SQL statement has been stored in the cache, and the cached result can be returned directly. Not only does it improve the efficiency of SQL conversion, it also reduces the number of visits to the database and reduces the load on the system.

[0157] When there are one or more cache keys that match the SQL syntax created by preparedStatement, the one or more cache keys that match the SQL syntax created by preparedStatement are used as the target cache keys, and the historical conversion results corresponding to the target cache keys are obtained. The specific implementation method is that after determining that the cache key exists, the Redis GET command is used to obtain the cache item corresponding to the cache key, that is, the historical conversion result. Then, the historical conversion result is returned as the result of SQL syntax conversion. For example, for the cache key "abc123", the command "GET abc123" can be used to obtain the cache item, and the historical conversion result "SELECT * FROM dual WHERE col1 = 1" is obtained. In this way, the conversion result can be quickly obtained without the need to perform SQL syntax conversion again, saving computing resources and time. At the same time, in order to ensure the real-time and validity of cached data, an expiration time can be set for each cache item to prevent expired data from occupying memory. For example, the Redis EXPIRE command can be used to set a TTL (Time To Live) for the cache item, and when the cache item expires, the item is automatically deleted. This method not only improves the efficiency of SQL conversion, but also ensures the real-time and validity of cached data. The historical conversion results are used to represent the conversion results obtained by calling the SQL conversion tool class to perform SQL syntax conversion.

[0158] It should be noted that, for the above SQL syntax, including "SELECT * FROM table1 WHERE col1 != 1", "SELECT * FROM table1 LIMIT 10 OFFSET 20", "SELECT * FROM table1 WHERE col1= 1" and "SELECT * FROM dual WHERE col1 = 1", the "*" means "all columns" wildcard, that is, when used in a SELECT statement, "*" means selecting all columns in the table. For example, in "SELECT *FROM table1 WHERE col1 != 1", "*" means selecting all columns in table1. Specifically, "SELECT *FROM table1 WHERE col1 != 1" means selecting all columns in table1, but only returning rows where col1 is not equal to 1. "SELECT * FROM table1 LIMIT 10 OFFSET 20" means selecting all columns in table1 and returning 10 rows of data starting from row 21. "SELECT * FROM table1 WHERE col1 = 1" selects all columns in table1, but only returns rows where col1 is equal to 1. "SELECT * FROM dual WHERE col1 = 1" selects all columns in the dual table, and returns rows where col1 is equal to 1.

[0159] This implementation can significantly improve the efficiency and performance of SQL conversion by generating a unique cache key for each MySQL SQL syntax, determining in Redis whether one or more cache keys hit the SQL syntax created by preparedStatement, and obtaining the historical conversion results corresponding to the target cache key if there is a hit. Through the caching mechanism, the workload of repeated conversions can be effectively reduced, saving computing resources and time. The introduction of Redis further improves the performance and management efficiency of the cache, ensuring the real-time and effectiveness of cached data. Ultimately, cross-database compatibility can be ensured, so that complex SQL queries executed on the MySQL database can be correctly executed on the Oracle database and return the expected results.

[0160] In one implementation of this embodiment, the following steps are also included:

[0161] S810. When the currently loaded class is a MySQL driver class, return the bytecode and byte array of the target driver class.

[0162] S820. When the currently loaded class is not a MySQL driver class, an empty byte array is returned.

[0163] When the currently loaded class is a MySQL driver class, returning the bytecode and byte array of the target driver class can ensure that the database driver is loaded correctly. When a class loader loads a class, it checks whether the class is a MySQL driver class. If so, it needs to return the bytecode and byte array of the target driver class in order to correctly load and initialize the driver class. The specific implementation method includes the following steps:

[0164] First, get the name of the currently loaded class and compare it with the predefined MySQL driver class name. If the names match, it means that the currently loaded class is the MySQL driver class. Next, use the getResourceAsStream method of ClassLoader to read the bytecode file of the target driver class. Then, convert the read bytecode file into a byte array to return it to the caller. For example, assuming that the name of the target driver class is "com.mysql.jdbc.Driver", the following code can be used to read and convert the bytecode file:

[0165] String className = "com.mysql.jdbc.Driver";

[0166] InputStream inputStream=getClass().getClassLoader().getResourceAsStream (className.replace('.', ' / ') + ".class");

[0167] byte[] byteCode=inputStream.readAllBytes();

[0168] In summary, it can be ensured that when the MySQL driver class is loaded, the bytecode and byte array of the target driver class are correctly returned, thereby ensuring the normal operation of the driver program.

[0169] When the currently loaded class is not a MySQL driver class, an empty byte array is returned to handle the loading of non-driver classes. When the class loader loads a class, if the class is not a MySQL driver class, no bytecode or byte array needs to be returned. The specific implementation method is to directly return an empty byte array after checking whether the currently loaded class is a MySQL driver class if the name does not match. This can avoid unnecessary resource consumption and incorrect loading.

[0170] Based on this, the loading of driver classes and non-driver classes can be effectively distinguished, ensuring that the corresponding bytecode and byte array are returned only when the MySQL driver class is loaded, and an empty byte array is returned when other classes are loaded. This not only improves the efficiency of class loading, but also avoids unnecessary resource waste and potential erroneous loading.

[0171] This embodiment can significantly improve the efficiency and accuracy of class loading by returning the bytecode and byte array of the target driver class when the currently loaded class is the MySQL driver class, and returning an empty byte array when the currently loaded class is not the MySQL driver class. By correctly returning the bytecode and byte array of the target driver class when loading the MySQL driver class, the normal operation of the driver program is ensured. At the same time, by returning an empty byte array, unnecessary resource waste and potential erroneous loading can be effectively avoided.

[0172] In summary, Figure 2 The overall flow chart of a software deployment method based on SQL syntax provided by an embodiment of the present application is shown. Figure 2 As shown, the software deployment method based on SQL syntax may include:

[0173] Step 1: Implement a class loading interceptor through Java Agent, intercept the loading of the Mysql driver class com.mysql.cj.jdbc.Driver and replace it with a driver class based on Oracle driver extension through Javassist;

[0174] Step 2: Based on the driver class extended by the Oracle driver, inherit from the Oracle driver, rewrite the methods for creating JDBC connections (such as the connect method), package the database connection instance created by the Oracle driver, and proxy the createStatement and prepareStatement methods in the packaged connection instance to return the packaged Statement and PreparedStatement instances. In the proxy method, intercept SQL execution, perform SQL syntax conversion, and convert MySQL syntax to Oracle syntax.

[0175] Specifically, Java Agent is a technology that dynamically modifies bytecode when the JVM is started or running. By specifying -javaagent in the startup parameters, you can load a JAR file that contains a custom Agent class. The Agent class can intercept the class loading process through the Instrumentation API. Javassist is a tool library that operates Java bytecode. With Javassist, the bytecode of a class can be modified dynamically. When intercepting the loading of the MySQL driver class, Javassist is used to replace it with a driver class based on the Oracle driver extension.

[0176] In this embodiment, the subquery is recursively processed to ensure that data at all levels can be correctly queried and processed. If it is not a subquery, the decorator process is precompiled, and the setObject\setint\setLong method is rewritten to complete the processing logic of the paging parameters. In other words, by using the connection decorator class to decorate the preparedStatement method, rewriting the setObject, setInt, setLong and other methods, the step of determining whether the SQL syntax of MySQL is a non-precompiled paging statement is executed.

[0177] In a specific implementation, the first step and the second step can be packaged into jdbc-mysql2oracle.jar, and -javaagent:path / to / ojdbc-mysql2oracle-xyxjar can be added to the software startup command to configure it into the system startup parameter -javaagent. Based on this, Figure 3 The difference between the present application and the prior art is shown. Compared with the prior art ( Figure 3 The dotted box shown in the figure above does not require recompiling and modifying the code every time a new version of the software is released, which greatly reduces development and maintenance costs and enables Java open source software to be flexibly deployed in an environment using an Oracle database without modifying the software itself, effectively improving deployment flexibility.

[0178] An embodiment of the present application also provides a machine-readable storage medium, on which instructions are stored, and the instructions are used to enable a machine to execute the above-mentioned SQL grammar-based software deployment method.

[0179] The present application also provides an electronic device, including:

[0180] The machine-readable storage medium described above;

[0181] The processor is configured to call instructions from a machine-readable storage medium and implement the above-mentioned software deployment method based on SQL syntax when executing the instructions.

[0182] Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, systems or computer program products. Therefore, the present application may take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Moreover, the present application may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program codes.

[0183] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of the processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0184] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0185] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 A step that specifies a function in one or more boxes.

[0186] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0187] The memory may include non-permanent memory in a computer-readable medium, random access memory (RAM) and / or non-volatile memory in the form of read-only memory (ROM) or flash RAM. The memory is an example of a computer-readable medium.

[0188] Computer readable media include permanent and non-permanent, removable and non-removable media that can be implemented by any method or technology to store information. Information can be computer readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disk read-only memory (CD-ROM), digital versatile disk (DVD) or other optical storage, magnetic cassettes, magnetic tape disk storage or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer readable media does not include temporary computer readable media (transitory media), such as modulated data signals and carrier waves.

[0189] It should also be noted that the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, commodity or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, commodity or device. In the absence of more restrictions, the elements defined by the sentence "comprises a ..." do not exclude the existence of other identical elements in the process, method, commodity or device including the elements.

[0190] The above are only embodiments of the present application and are not intended to limit the present application. For those skilled in the art, the present application may have various changes and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application should be included within the scope of the claims of the present application.

Claims

1. A software deployment method based on SQL syntax, characterized in that: include: Create a class loading interceptor and register a class file converter in the class loading interceptor; Determining whether the currently loaded class is a MySQL driver class through the class file converter; In the case where the currently loaded class is the MySQL driver class, intercepting the MySQL driver class through the class loading interceptor, and loading a target driver class, wherein the target driver class is based on an Oracle driver extension; In a static code block of an Oracle database driver class, the Oracle database driver class is registered in a JDBC driver manager, and a connection instance of the Oracle database is overwritten, the connection instance being used to achieve connection with the Oracle database, the Oracle database driver class inherits from the Oracle driver class, and the Oracle database is created by the Oracle driver; The connection instance of the Oracle database is packaged to convert the SQL syntax of MySQL into the SQL syntax of Oracle; The overwriting of the connection instance of the Oracle database includes: Rewrite the connect method in the target driver class; Call the connect method of the parent class to obtain the connection instance of the Oracle database; Create a connection decoration class, encapsulate the connection instance of the Oracle database through the connection decoration class, and return the connection decoration class; The packaging of the connection instance of the Oracle database includes: Decorate the createStatement method with the connection decorator class and create a StatementWrapper; and The preparedStatement method is decorated with the connection decorator class, and a PreparedStatementWrapper is created, and the SQL conversion tool class is called to perform SQL syntax conversion to convert the SQL syntax created by the preparedStatement into the first SQL syntax of the Oracle database, wherein the createStatement method and the preparedStatement method are used to create SQL syntax to execute SQL queries on the MySQL database; Intercepting the execution of non-precompiled SQL grammar through the StatementWrapper, and calling the SQL conversion tool class to perform SQL grammar conversion, so as to convert the non-precompiled SQL grammar into a second SQL grammar of the Oracle database; Intercepting the execution of precompiled SQL syntax through the PreparedStatementWrapper and overriding the getGeneratedKeys method, wherein the getGeneratedKeys method is used to obtain the key value automatically generated by the MySQL database after executing the insert operation of the MySQL database; Determine whether a fixed mapping relationship exists between the first SQL grammar and the second SQL grammar; In the case where the fixed mapping relationship exists, a first target SQL grammar fixedly mapped with the first SQL grammar and a second target SQL grammar fixedly mapped with the second SQL grammar are returned according to the fixed mapping relationship.

2. The method according to claim 1, characterized in that The method further comprises: In the case that the fixed mapping relationship does not exist between the first SQL grammar and the second SQL grammar, performing syntax parsing on the first SQL grammar and the second SQL grammar to obtain an SQL syntax tree; The special syntax is determined according to the SQL syntax tree and an SQL conversion step is performed to convert the SQL syntax of MySQL into the SQL syntax of Oracle.

3. The method according to claim 2, characterized in that The SQL conversion step includes: Determine whether the SQL syntax of MySQL is a syntax without a table name, and if the SQL syntax of MySQL is a syntax without a table name, set the table name of the SQL syntax of MySQL to dual; Determine whether the SQL syntax of MySQL uses the as alias, and if the SQL syntax of MySQL uses the as alias, delete the as alias; Determine whether the SQL syntax of MySQL contains a backquote, and if the SQL syntax of MySQL contains the backquote, delete the backquote; Determine whether a prohibited symbol is used in a non-equality expression of the SQL grammar of the MySQL, and if the prohibited symbol is used in the non-equality expression of the SQL grammar of the MySQL, replace the prohibited symbol with a corresponding grammar; Performing function conversion on the MySQL database function to convert the function corresponding to the MySQL database into a function corresponding to the Oracle database; Determine whether the SQL syntax of MySQL is a non-precompiled paging statement, and perform paging conversion if the SQL syntax of MySQL is a non-precompiled paging statement.

4. The method according to claim 1, characterized in that The method further comprises: Introducing a cache mechanism into the SQL conversion tool class; Calling the SQL conversion tool class to perform SQL syntax conversion and obtain a conversion result; The conversion result is cached in Redis through the cache mechanism.

5. The method according to claim 4, characterized in that Before calling the SQL conversion tool class to perform SQL syntax conversion and obtain the conversion result, the following steps are included: Generate a unique cache key for each of the MySQL SQL grammars, where one or more cache keys are used to represent a historical conversion result; The method further comprises: Determine in the Redis whether one or more cache keys match the SQL syntax created by the preparedStatement; In the case that there are one or more cache keys that hit the SQL grammar created by the preparedStatement, the one or more cache keys that hit the SQL grammar created by the preparedStatement are used as target cache keys, and the historical conversion results corresponding to the target cache keys are obtained, and the historical conversion results are used to characterize the conversion results obtained by calling the SQL conversion tool class to perform SQL grammar conversion.

6. The method according to claim 1, characterized in that The method further comprises: In the case where the currently loaded class is the MySQL driver class, the bytecode and byte array of the target driver class are returned; In case the currently loaded class is not the MySQL driver class, an empty byte array is returned.

7. A machine-readable storage medium, characterized in that: The machine-readable storage medium stores instructions, which are used to enable a machine to execute the SQL grammar-based software deployment method according to any one of claims 1 to 6.

8. An electronic device, characterized in that: include: The machine-readable storage medium according to claim 7; A processor is configured to call instructions from the machine-readable storage medium and implement the SQL grammar-based software deployment method according to any one of claims 1 to 6 when executing the instructions.

Images