JSqlParser-based heterogeneous database statement conversion system and method

Abstract

The invention provides a heterogeneous database statement conversion system and a heterogeneous database statement conversion method based on JSqlParser. The method comprises the following steps: S1, in a data source configuration scene, defining a connection parameter of a target database for a conversion test; and S2, in the conversion rule configuration scene, defining a conversion rule from the source database to the target database. And S3, in the SQL mode recognition scene, recognizing the mode of the SQL statement filled by the user and containing the code format, and converting the SQL statement into a native SQL statement. And S4, in the SQL analysis scene, according to the configured conversion rule, converting the SQL statement into the SQL statement of the target database. Step S5, in the SQL test scene, performing a database connection test on the converted SQL statement, and outputting a test result; the method can support built-in or self-defined storage processes and functions, configuration rules are flexible, the development period is short, and maintenance is convenient.

Description

Technical Field

[0001] The present invention relates to the field of application technology, and in particular to a heterogeneous database statement conversion system and method based on JSqlParser. Background Art

[0002] Due to various complex factors at home and abroad, more and more state-owned enterprises and institutions are currently requiring their information systems to replace the closed-source databases such as Oracle that they rely on with open-source or domestic databases such as Postgresql, MySQL, and DAMO. The data layer framework of the current information system is generally Mybatis, which encapsulates the process of JDBC operating the database. Some earlier systems were even data layers developed directly based on jdbc, such as Spring's JdbcTemplate or self-encapsulated jdbc data layers. The characteristics of these data layer frameworks are to access the database based on the SQL programming language. Although different databases are all based on the SQL specification, there are various dialects, that is, there are a lot of differences in SQL syntax between them, and the built-in functions provided by different databases are also significantly different in number, function and syntax. Therefore, if you want to migrate the database that the original information system relies on from one to another, the development workload is large and difficult.

[0003] The existing heterogeneous database statement conversion method is to perform lexical analysis on the SQL statement to be converted from the source database to obtain the attribute information of each word in the SQL statement to be converted; then perform syntax analysis on the above information to obtain the syntax parse tree of the SQL statement to be converted, and then convert it according to the conversion rules between the source database and the target database to obtain the converted syntax parse tree; finally, the converted syntax parse tree is used to generate a result SQL statement corresponding to the SQL statement to be converted and used for the target database.

[0004] However, the above implementation method has the following two problems:

[0005] 1. Unable to process database built-in functions, database custom stored procedures and functions.

[0006] Database built-in functions are a series of processing methods developed by the database itself based on query requirements. Custom stored procedures and functions are an advanced feature of the database that allows developers to define complex database operations that can be executed on the database server side, thereby improving the performance and security of the application. However, none of the above are within the scope of the SQL language specification, so the corresponding syntax parsing tree is powerless. These built-in functions exist in large numbers in SQL statements used in business system development, and there are also many custom stored procedures and functions defined due to business needs. If these rely on manual search and conversion, the workload is huge and error-prone.

[0007] 2. Unable to configure conversion rules.

[0008] Since the conversion rules are based on the syntax parse tree, they cannot be configured. Every time a new conversion is needed, developers need to familiarize themselves with the rules between different databases before development, and then perform encoding conversion. This method has a long development cycle, is prone to errors, and is difficult to maintain. Summary of the invention

[0009] The present invention proposes a heterogeneous database statement conversion system and method based on JSqlParser, which can support built-in or custom stored procedures and functions, has flexible configuration rules, short development cycle and convenient maintenance.

[0010] The present invention adopts the following technical solutions.

[0011] A heterogeneous database statement conversion method based on JSqlParser, comprising the following steps:

[0012] Step S1: In the data source configuration scenario, define the connection parameters of the target database for conversion testing.

[0013] Step S2: In the conversion rule configuration scenario, define the conversion rules from the source database to the target database.

[0014] Step S3: In the SQL pattern recognition scenario, for the SQL statement filled in by the user and containing the code format, its pattern is recognized and converted into a native SQL statement.

[0015] Step S4: In the SQL parsing scenario, convert it into an SQL statement of the target database according to its configured conversion rules.

[0016] Step S5: In the SQL test scenario, perform a database connection test on the converted SQL statement and output the test result.

[0017] In step S1, the connection parameters of the target database include definable data source type, driver class name, connection URL, database account and password.

[0018] In step S2, the conversion rules include built-in conversion rules and regular expression conversion rules; the built-in conversion rules are implemented based on JSqlParser; the regular expression conversion rules are configurable conversion rules. The specifically defined configuration rule fields are: source statement regular expression, remarks, replacement string, and replacement strategy.

[0019] The built-in conversion rules can be used for complex recursive queries. In the regular expression conversion rules, the replacement string supports group replacement, and the replacement strategies include direct replacement strategy, specific built-in function replacement strategy and reminder strategy.

[0020] The specific built-in function replacement strategy is to replace the commonly used built-in functions of the target database. When the target database is Oracle, the specific built-in function replacement strategy includes converting Oracle's decode function into a case when statement.

[0021] In step S3, the SQL statement in code format includes a Java string or XML format, and step S3 converts it into an original SQL statement by identifying the statement format.

[0022] When the conversion method is used in the Java language system, step S4 converts it into the original SQL statement by identifying the format, specifically: in the Java language system of the target database, the SQL statement containing the code format has two formats: string and XML, where the string is divided into statements connected by "+" and statements connected by the StringBuilder.append() method; the XML format refers to the statement format defined by Mybatis, and by identifying these formats, the native SQL is extracted for conversion in the next stage.

[0023] In step S4, the conversion rules configured by the preset application source and target database are used to perform conversion one by one. The target database corresponding to the SQL statement is a relational database, including MySQL, PosgresSQL, Sql Server, Oracle, and SQLite.

[0024] In step S5, for the converted SQL statement, the corresponding question mark placeholder is converted to a default value according to the field type. During the conversion, the string type field is converted to an empty string, or the conversion value is set by the user.

[0025] A heterogeneous database statement conversion system based on JSqlParser, the system itself has a built-in driver package of the target database; it also supports uploading a specific database driver package;

[0026] The hardware of the system includes a memory and a processor. The memory stores computer instructions for implementing the above-mentioned heterogeneous database statement conversion system and method based on JSqlParser. The processor executes the computer instructions stored in the memory during operation.

[0027] The system uses JSqlParser as a SQL statement parser, which is written in Java and supports SQL statements including SELECT, INSERT, UPDATE, DELETE, and statements specific to the target database. It is used to parse complex SQL statements into a hierarchical structure to facilitate analysis, modification, and reconstruction of SQL statements.

[0028] The system configures rules according to the conversion between various heterogeneous databases. When it is working, if it is a built-in rule of the system, it can be directly selected and used. If it is a regular expression rule, it is configured in the regular expression way. The system has a built-in regular expression tester for writing regular expressions for SQL statements. The written regular expressions are configured into the system only after passing the test.

[0029] When the system executes the conversion process, the SQL statement is color-coded and formatted for display at the front-end interface of the system.

[0030] When the user clicks the test button, the system connects to the target database to test the correctness of the statement;

[0031] When the user clicks the Convert Code button, the system converts the statement into a corresponding string code format or Mybatis's XML format so that it can be pasted into the code of other application sources.

[0032] Compared with the prior art, the present invention has the following beneficial effects: the present invention can support built-in or customized stored procedures and functions, has flexible configuration rules, and has a short development cycle and is easy to maintain.

[0033] The present invention also has the following advantages:

[0034] 1. Flexible configuration rules: The system can configure rules according to the conversion between various heterogeneous databases. If it is a built-in rule, just select it. If it is a regular expression rule, you can configure it in the regular expression way. The system has a built-in regular expression tester, you can first write a regular expression for the SQL statement, and then configure it into the system after the test passes.

[0035] 2. Short development cycle: The system implements a large number of built-in conversion rules, covering most scenarios. If there is a need for secondary development of the solution described in the present invention, it only needs to implement the corresponding replacement strategy interface, then inject it into the system through reflection, and then configure it.

[0036] 3. Easy to maintain: Since most rules are configurable and support hot deployment, there is no need for frequent upgrades and maintenance is simple. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] The present invention is further described in detail below with reference to the accompanying drawings and specific embodiments:

[0038] Attached Figure 1 is a schematic diagram of a method flow of an embodiment of the present invention;

[0039] Attached Figure 2 It is a schematic diagram of a principle block diagram of an embodiment of the present invention. DETAILED DESCRIPTION

[0040] The present invention will be further described below in conjunction with the accompanying drawings and embodiments.

[0041] It should be noted that the following detailed descriptions are exemplary and are intended to provide further explanation of the present application. Unless otherwise specified, all technical and scientific terms used herein have the same meanings as those commonly understood by those skilled in the art to which the present application belongs.

[0042] It should be noted that the terms used herein are only for describing specific embodiments and are not intended to limit the exemplary embodiments according to the present application. As used herein, unless the context clearly indicates otherwise, the singular form is also intended to include the plural form. In addition, it should be understood that when the terms "comprise" and / or "include" are used in this specification, it indicates the presence of features, steps, operations, devices, components and / or combinations thereof.

[0043] As shown in the figure, a heterogeneous database statement conversion method based on JSqlParser includes the following steps:

[0044] Step S1: In the data source configuration scenario, define the connection parameters of the target database for conversion testing.

[0045] Step S2: In the conversion rule configuration scenario, define the conversion rules from the source database to the target database.

[0046] Step S3: In the SQL pattern recognition scenario, for the SQL statement filled in by the user and containing the code format, its pattern is recognized and converted into a native SQL statement.

[0047] Step S4: In the SQL parsing scenario, convert it into an SQL statement of the target database according to its configured conversion rules.

[0048] Step S5: In the SQL test scenario, perform a database connection test on the converted SQL statement and output the test result.

[0049] In step S1, the connection parameters of the target database include definable data source type, driver class name, connection URL, database account and password.

[0050] In step S2, the conversion rules include built-in conversion rules and regular expression conversion rules; the built-in conversion rules are implemented based on JSqlParser; the regular expression conversion rules are configurable conversion rules. The specifically defined configuration rule fields are: source statement regular expression, remarks, replacement string, and replacement strategy.

[0051] The built-in conversion rules can be used for complex recursive queries. In the regular expression conversion rules, the replacement string supports group replacement, and the replacement strategies include direct replacement strategy, specific built-in function replacement strategy and reminder strategy.

[0052] The specific built-in function replacement strategy is to replace the commonly used built-in functions of the target database. When the target database is Oracle, the specific built-in function replacement strategy includes converting Oracle's decode function into a case when statement.

[0053] In step S3, the SQL statement in code format includes a Java string or XML format, and step S3 converts it into an original SQL statement by identifying the statement format.

[0054] When the conversion method is used in the Java language system, step S4 converts it into the original SQL statement by identifying the format, specifically: in the Java language system of the target database, the SQL statement containing the code format has two formats: string and XML, where the string is divided into statements connected by "+" and statements connected by the StringBuilder.append() method; the XML format refers to the statement format defined by Mybatis, and by identifying these formats, the native SQL is extracted for conversion in the next stage.

[0055] In step S4, the conversion rules configured by the preset application source and target database are used to perform conversion one by one. The target database corresponding to the SQL statement is a relational database, including MySQL, PosgresSQL, Sql Server, Oracle, and SQLite.

[0056] In step S5, for the converted SQL statement, the corresponding question mark placeholder is converted to a default value according to the field type. During the conversion, the string type field is converted to an empty string, or the conversion value is set by the user.

[0057] A heterogeneous database statement conversion system based on JSqlParser, the system itself has a built-in driver package of the target database; it also supports uploading a specific database driver package;

[0058] The hardware of the system includes a memory and a processor. The memory stores computer instructions for implementing the above-mentioned heterogeneous database statement conversion system and method based on JSqlParser. The processor executes the computer instructions stored in the memory during operation.

[0059] The system uses JSqlParser as a SQL statement parser, which is written in Java and supports SQL statements including SELECT, INSERT, UPDATE, DELETE, and statements specific to the target database. It is used to parse complex SQL statements into a hierarchical structure to facilitate analysis, modification, and reconstruction of SQL statements.

[0060] The system configures rules according to the conversion between various heterogeneous databases. When it is working, if it is a built-in rule of the system, it can be directly selected and used. If it is a regular expression rule, it is configured in the regular expression way. The system has a built-in regular expression tester for writing regular expressions for SQL statements. The written regular expressions are configured into the system only after passing the test.

[0061] When the system executes the conversion process, the SQL statement is color-coded and formatted for display at the front-end interface of the system.

[0062] When the user clicks the test button, the system connects to the target database to test the correctness of the statement;

[0063] When the user clicks the Convert Code button, the system converts the statement into a corresponding string code format or the XML format of Mybatis so that it can be pasted (copied) into the code of other application sources.

[0064] Example:

[0065] like Figure 1 as well as Figure 2 As shown, this embodiment provides a heterogeneous database statement conversion system and method based on JSqlParser, including the following steps:

[0066] In the data source configuration scenario, define the connection parameters of the target database for conversion testing.

[0067] In the transformation rule configuration scenario, the transformation rules from the source database to the target database are defined.

[0068] In the SQL pattern recognition scenario, the pattern of the SQL statement filled in by the user and containing the code format is identified and converted into a native SQL statement.

[0069] In the SQL parsing scenario, it is converted into the SQL statement of the target database according to the configured conversion rules.

[0070] In the SQL test scenario, the converted SQL statement is tested for database connection and the test results are output.

[0071] In this embodiment, the connection parameters of the target database are defined in the data source configuration scenario for conversion testing. Specifically:

[0072] You can define the data source type, driver class name, connection URL, database account and password. The system itself has some common database driver packages built in (such as Postgresql, MySql, etc.), and also supports uploading specific database driver packages. After definition, you can click the button to test whether the connection is normal.

[0073] In this embodiment, the conversion rules from the source database to the target database are defined in the conversion rule configuration scenario. Specifically:

[0074] Conversion rules are divided into two types: built-in conversion rules and regular expression conversion rules. Built-in conversion rules are implemented based on JSqlParser, such as complex recursive queries. The other is regular expression conversion rules, which are configurable. The specific configuration rule fields defined are: source statement regular expression, remarks, replacement string (supports group replacement), and replacement strategy. The replacement strategy includes direct replacement strategy, specific built-in function replacement strategy, and reminder strategy.

[0075] Specific built-in function replacement strategies are some common built-in function replacements implemented by the system, such as the conversion of the oracledecode function to a case when statement.

[0076] The reminder strategy is to find certain scene modes in the sentence, which require manual intervention. After the sentence is converted, the system will highlight the scene mode keywords found to remind the user.

[0077] In this embodiment, in the SQL pattern recognition scenario, the pattern of the SQL statement filled in by the user and containing the code format is recognized and converted into a native SQL statement. Specifically:

[0078] In the Java language system, SQL statements in code format have two formats: string and XML. Strings are divided into statements connected by "+" and statements connected by StringBuilder.append() method. XML format generally refers to the statement format defined by Mybatis. By identifying these formats, the native SQL is extracted for conversion in the next stage. The database corresponding to the SQL statement here refers to a relational database, such as MySQL, PosgresSQL, SqlServer, Oracle, SQLite, etc.

[0079] In this embodiment, the SQL parsing scenario is converted into the SQL statement of the target database according to the conversion rules configured therein. Specifically:

[0080] According to the conversion rules configured in the source database and the target database, the conversion is performed in the order of configuration. If the built-in conversion rules are applied, the statements are converted into objects based on JSqlParser for conversion. If the regular expression conversion rules are applied, the regular expression method is used to convert the statements. For example, in the scenario of merging multiple rows of data from Oracle to PostgreSQL, the corresponding regular expression mapping relationship is:

[0081] ·PostgreSQL: listagg\\((\\S+),\\s+(\\S+)\\)\\s+within\\s+GROUP\\"+"(order\\s+by\\s+(\\S+)\\)

[0082] ·Oracle: STRING_AGG($1,$2"+"ORDER BY$3)

[0083] In this embodiment, the SQL test scenario performs a database connection test on the converted SQL statement and outputs the test result.

[0084] Specifically:

[0085] For the converted SQL statement, the corresponding question mark placeholder is converted to the default value according to the field type (for example, a string type field is converted to an empty string). It can also be set by the user. The SQL statement will be color-coded and formatted on the front end. The user can click the Test button, and the system will connect to the target database to test the correctness of the statement. The user can also click the Convert Code button to convert the statement into the corresponding string code format or Mybatis's XML format so that it can be pasted into the application source code.

[0086] In summary, in this example, the connection parameters of the target database are first defined in the data source configuration scenario for conversion testing. In the conversion rule configuration scenario, the conversion rules from the source database to the target database are defined. In the SQL pattern recognition scenario, the pattern of the SQL statement filled in by the user and containing the code format is identified and converted into a native SQL statement. In the SQL parsing scenario, according to the configured conversion rules, it is converted into an SQL statement of the target database. In the SQL test scenario, the converted SQL statement is tested for database connection and the test results are output. The present invention can support built-in or custom stored procedures and functions, with flexible configuration rules, short development cycle and easy maintenance.

[0087] This embodiment also provides a computer storage medium, including computer instructions. When the computer instructions are executed on an electronic device, the electronic device executes the heterogeneous database statement conversion system and method based on JSqlParser as described above.

[0088] This embodiment also provides a heterogeneous database statement conversion system and method based on JSqlParser, including a memory and a processor, wherein the memory stores computer instructions for implementing the heterogeneous database statement conversion system and method based on JSqlParser described above, and the processor executes the computer instructions stored in the memory at runtime.

[0089] In particular, in this embodiment, JSqlParser technology is used. jsqlparser is a SQL statement parser written in Java. It can parse complex SQL statements into a hierarchical structure, which is convenient for developers to analyze, modify and reconstruct SQL statements. jsqlparser supports multiple types of SQL statements, including SELECT, INSERT, UPDATE, DELETE and some database-specific statements. JSqlParser and regular expressions are used together to define conversion rules.

[0090] 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 adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application may adopt the form of a computer program product implemented in one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) that include computer-usable program code.

[0091] 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 box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes 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.

[0092] 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.

[0093] 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 The steps for the functions specified in one or more boxes.

[0094] The above is only a preferred embodiment of the present invention, and does not limit the present invention in other forms. Any technician familiar with the profession may use the above disclosed technical content to change or modify it into an equivalent embodiment with equivalent changes. However, any simple modification, equivalent change and modification made to the above embodiment according to the technical essence of the present invention without departing from the technical solution of the present invention still belongs to the protection scope of the technical solution of the present invention.

Claims

1. A heterogeneous database statement conversion method based on JSqlParser, characterized by: The steps include: Step S1: In the data source configuration scenario, define the connection parameters of the target database for conversion testing; Step S2: In the conversion rule configuration scenario, define the conversion rules from the source database to the target database; Step S3: In the SQL pattern recognition scenario, for the SQL statement filled in by the user and containing the code format, its pattern is recognized and converted into a native SQL statement; Step S4: in the SQL parsing scenario, convert it into the SQL statement of the target database according to the configured conversion rules; Step S5: In the SQL test scenario, perform a database connection test on the converted SQL statement and output the test result.

2. According to a JSqlParser-based heterogeneous database statement conversion method according to claim 1, it is characterized in that: In step S1, the connection parameters of the target database include definable data source type, driver class name, connection URL, database account and password.

3. According to a JSqlParser-based heterogeneous database statement conversion method according to claim 1, it is characterized in that: In step S2, the conversion rules include built-in conversion rules and regular expression conversion rules; the built-in conversion rules are implemented based on JSqlParser; the regular expression conversion rules are configurable conversion rules; The specifically defined configuration rule fields are: source statement regular expression, remarks, replacement string, and replacement strategy.

4. According to a JSqlParser-based heterogeneous database statement conversion method according to claim 3, it is characterized in that: The built-in conversion rules can be used for complex recursive queries. In the regular expression conversion rules, the replacement string supports group replacement, and the replacement strategies include direct replacement strategy, specific built-in function replacement strategy and reminder strategy.

5. The method for converting heterogeneous database statements based on JSqlParser according to claim 3 is characterized in that: The specific built-in function replacement strategy is to replace the commonly used built-in functions of the target database. When the target database is Oracle, the specific built-in function replacement strategy includes converting Oracle's decode function into a case when statement.

6. A heterogeneous database statement conversion method based on JSqlParser according to claim 5, characterized in that: In step S3, the SQL statement in code format includes a Java string or XML format, and step S3 converts it into an original SQL statement by identifying the statement format.

7. The method for converting heterogeneous database statements based on JSqlParser according to claim 6, characterized in that: When the conversion method is used for the Java language system, step S4 converts the original SQL statement by identifying the format, specifically: in the Java language system of the target database, the SQL statement containing the code format has two formats: string and XML, where the string is divided into statements connected by "+" and statements connected by the StringBuilder.append() method; the XML format refers to the statement format defined by Mybatis, and by identifying these formats, the native SQL is extracted for conversion in the next stage.

8. A heterogeneous database statement conversion method based on JSqlParser according to claim 7, characterized in that: In step S4, the conversion rules configured by the preset application source and target database are used to perform conversion one by one. The target database corresponding to the SQL statement is a relational database, including MySQL, PosgresSQL, Sql Server, Oracle, and SQLite.

9. A heterogeneous database statement conversion method based on JSqlParser according to claim 8, characterized in that: In step S5, for the converted SQL statement, the corresponding question mark placeholder is converted to a default value according to the field type. During the conversion, the string type field is converted to an empty string, or the conversion value is set by the user.

10. A heterogeneous database statement conversion system based on JSqlParser, characterized by: The system itself has a built-in driver package for the target database; it also supports uploading a driver package for a specific database; The hardware of the system includes a memory and a processor, the memory stores computer instructions for implementing the heterogeneous database statement conversion system and method based on JSqlParser according to any one of claims 1 to 9, and the processor executes the computer instructions stored in the memory at runtime; The system uses JSqlParser as a SQL statement parser, which is written in Java and supports SQL statements including SELECT, INSERT, UPDATE, DELETE, and statements specific to the target database. It is used to parse complex SQL statements into a hierarchical structure to facilitate analysis, modification, and reconstruction of SQL statements. The system configures rules according to the conversion between various heterogeneous databases. When it is working, if it is a built-in rule of the system, it can be directly selected and used. If it is a regular expression rule, it is configured in the regular expression way. The system has a built-in regular expression tester for writing regular expressions for SQL statements. The written regular expressions are configured into the system only after passing the test. When the system executes the conversion process, the SQL statement is color-coded and formatted for display at the front-end interface of the system. When the user clicks the test button, the system connects to the target database to test the correctness of the statement; When the user clicks the Convert Code button, the system converts the statement into a corresponding string code format or Mybatis's XML format so that it can be pasted into the code of other application sources.

Images