Database migration method, device and system based on MyBatis and storage medium

Abstract

The invention discloses a database migration method, device and system based on MyBatis and a storage medium, and relates to the technical field of data migration. The method comprises the steps that an XML configuration file of MyBatis is obtained and analyzed, and a first SQL statement is obtained; obtaining a source database grammar rule and a target database grammar rule, and converting the first SQL statement according to the source database grammar rule to generate a second SQL statement conforming to the target database grammar rule; and performing optimization processing on the second SQL statement based on the characteristics of the target database to obtain an optimized SQL statement, and rewriting the optimized SQL statement into the XML configuration file of the MyBatis. According to the method disclosed by the embodiment of the invention, the migration workload is reduced, the migration efficiency and accuracy are improved, the error risk in the migration process is reduced, and the portability of the application program and the compatibility of the database are enhanced; and the execution efficiency of the SQL statement is improved.

Description

Technical Field

[0001] The present application belongs to the technical field of data migration, and in particular, relates to a database migration method, device, system and storage medium based on MyBatis. Background Art

[0002] A relational database is a database based on a relational model. It uses mathematical concepts and methods such as set algebra to process data in the database. A relational database system stores data in the form of rows and columns. These data are organized into several tables. The tables are connected to each other through associations (such as foreign keys), forming an association relationship between one or more data tables. This database system uses SQL (Structured Query Language) as the standard language for data access and operation.

[0003] At present, although there are some processing methods in the field of database migration and adaptation, there are still many challenges. Traditional database migration methods often use the method of manually writing conversion scripts, which is not only inefficient but also prone to errors. Especially when dealing with large amounts of data and complex logic, it is difficult to ensure the accuracy and completeness of the conversion. Secondly, existing database adaptation technologies often only focus on syntax-level conversions, while ignoring the optimization of database characteristics and performance, resulting in the performance of data query and processing in the migrated system often failing to achieve the expected results. Summary of the invention

[0004] The purpose of this application is to provide a database migration method, device, system and storage medium based on MyBatis, which realizes data migration between the source database and the target database by parsing the SQL statements in the XML configuration file of MyBatis, converting them according to the grammatical rules of the source database and the target database, optimizing the converted SQL statements, and finally updating them back to the XML configuration file of MyBatis.

[0005] In order to achieve the above objectives, the solution of this application is:

[0006] In a first aspect, an embodiment of the present application provides a database migration method based on MyBatis, comprising: obtaining an XML configuration file of MyBatis and parsing the XML configuration file to obtain a first SQL statement;

[0007] Acquire the source database grammar rules and the target database grammar rules, and convert the first SQL statement according to the source database grammar rules, including converting the data type of the first SQL statement and replacing the function and system variables of the first SQL statement, to generate a second SQL statement that complies with the target database grammar rules;

[0008] The second SQL statement is optimized based on the characteristics of the target database to obtain an optimized SQL statement, and the optimized SQL statement is rewritten into the XML configuration file of MyBatis.

[0009] According to the above method of the embodiment of the present application, the following additional technical features may also be provided:

[0010] Further, the XML configuration file of MyBatis is obtained and parsed to obtain the first SQL statement, including:

[0011] The XML configuration file is read through the XML processing library to obtain the SQL mapping element, and the SQL mapping element is traversed to obtain the first SQL statement.

[0012] Furthermore, converting the data type of the first SQL statement includes, before converting the data type, verifying whether the data in the source database exceeds the data type range of the target database, and performing data type conversion on the data that exceeds the data type range of the target database.

[0013] Furthermore, the functions and system variables of the first SQL statement are replaced, wherein the functions include date and time functions, string processing functions, and aggregate functions; the system variables include database connection, performance optimization, and character set settings, and the functions and system variables in the first SQL statement are replaced with equivalent values ​​corresponding to the target database.

[0014] Further, a second SQL statement that complies with the grammatical rules of the target database is generated, including:

[0015] Identify incompatible grammatical rules between the source database grammar rules and the target database grammar rules, match the strings in the incompatible grammatical rules through regular expressions, the strings include table names, field names and function names, formulate corresponding replacement plans according to the grammatical rules of the target database, replace the strings with compatible forms of the target database, and adjust the structural differences.

[0016] Furthermore, after replacing the string with a compatible form of the target database and adjusting the structural differences, all mapper.XML configuration files in the XML configuration file are recursively scanned and automatically replaced based on the replacement scheme.

[0017] Furthermore, the second SQL statement is optimized based on the characteristics of the target database to obtain an optimized SQL statement, including date type filtering optimization, fuzzy matching performance optimization, index optimization, SQL statement optimization and configuration file optimization.

[0018] In a second aspect, an embodiment of the present application provides a database migration device based on MyBatis, comprising:

[0019] The data acquisition module is configured to obtain the XML configuration file of MyBatis, and obtain the syntax rules of the source database and the syntax rules of the target database;

[0020] The data processing module is configured to parse the XML configuration file to obtain a first SQL statement; convert the first SQL statement according to the grammatical rules of the source database, including converting the data type of the first SQL statement and replacing the function and system variables of the first SQL statement, to generate a second SQL statement that complies with the grammatical rules of the target database;

[0021] A data optimization module is configured to optimize the second SQL statement based on the characteristics of the target database to obtain an optimized SQL statement;

[0022] The data migration module is configured to rewrite the optimized SQL statements into the XML configuration file of MyBatis to migrate data between the source database and the target database.

[0023] In a third aspect, an embodiment of the present application provides a MyBatis-based database migration system, the system including a processor and a memory, the memory storing a computer program, and the computer program being loaded and executed by the processor to implement the MyBatis-based database migration method provided in the first aspect of the embodiment of the present application.

[0024] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, in which a computer program is stored. When the computer program is executed by a processor, it is used to implement the MyBatis-based database migration method as in the first aspect of the embodiment of the present application.

[0025] Compared with the prior art, the MyBatis-based database migration method provided in the embodiment of the present application has the following beneficial technical effects:

[0026] The embodiment of the present application reduces the workload of manual migration of SQL statements and improves the efficiency and accuracy of migration by reading the XML configuration file of MyBatis and parsing the SQL statements therein; converts the SQL statements of the source database into SQL statements that comply with the grammatical rules of the target database, reducing the risk of errors caused by manual modification of SQL statements during the migration process; taking into account the differences between different databases in terms of data types, functions, and system variables, the present application ensures that SQL statements can be correctly executed in the target database by converting these differences, reduces the large number of modifications required for database migration, and enhances the portability of the application and the compatibility of the database; further improves the execution efficiency of SQL statements by optimizing the converted SQL statements based on the characteristics of the target database, and the optimization processing of the present application can give full play to the performance advantages of the new database system and improve the response speed and throughput; improves the stability and reliability of database migration by rewriting the optimized SQL statements into the XML configuration file of MyBatis, and reduces the risk of system failures that may be caused by database migration. BRIEF DESCRIPTION OF THE DRAWINGS

[0027] Figure 1 A schematic diagram of the process of the database migration method based on MyBatis in an embodiment of the present application is shown;

[0028] Figure 2 The structure diagram of the database migration device based on MyBatis in the embodiment of the present application is shown;

[0029] Figure 3 A structural block diagram of a computer device according to an embodiment of the present application is shown. DETAILED DESCRIPTION

[0030] In order to make the above-mentioned purposes, features and advantages of the present application more obvious and easy to understand, the specific implementation methods of the present application are described in detail below in conjunction with the accompanying drawings. It is to be understood that the specific embodiments described herein are only used to explain the present application, rather than to limit the present application. It should also be noted that, for ease of description, only some structures related to the present application are shown in the accompanying drawings, rather than all structures. 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.

[0031] The terms "including" and "having" and any variations thereof in this application are intended to cover non-exclusive inclusions. For example, a process, method, system, product or device comprising a series of steps or units is not limited to the listed steps or units, but may optionally include steps or units not listed, or may optionally include other steps or units inherent to these processes, methods, products or devices.

[0032] Reference to "embodiments" herein means that a particular feature, structure, or characteristic described in conjunction with the embodiments may be included in at least one embodiment of the present application. The appearance of the phrase in various locations in the specification does not necessarily refer to the same embodiment, nor is it an independent or alternative embodiment that is mutually exclusive with other embodiments. It is explicitly and implicitly understood by those skilled in the art that the embodiments described herein may be combined with other embodiments.

[0033] like Figure 1 As shown, the embodiment of the present application provides a database migration method based on MyBatis, comprising the following steps:

[0034] Step 101, obtain the XML configuration file of MyBatis and parse the XML configuration file to obtain the first SQL statement.

[0035] In the embodiment of the present application, an XML configuration file is read through an XML processing library to obtain SQL mapping elements, and the SQL mapping elements are traversed to obtain a first SQL statement.

[0036] Specifically, use a suitable library to read the XML configuration file. Choosing a suitable library to read the XML configuration file depends on the programming language used, the specific requirements of the project, and the processing method of the XML configuration file. The selection of XML processing libraries includes:

[0037] In Python, XML configuration files are processed through ElementTree, lxml and xml.dom.minidom. ElementTree is part of the Python standard library and provides a simple and easy-to-use API to parse and create XML data, which is suitable for most basic needs; lxml is a library based on ElementTree, which provides faster parsing speed and richer functions, such as XPath and XSLT support, and is suitable for processing large or complex XML configuration files; xml.dom.minidom provides an implementation of the DOM API, allowing XML configuration files to be manipulated in an object-oriented manner, which is suitable for scenarios that require highly customized DOM operations.

[0038] In Java, XML configuration files are processed through JAXP, JAXB, JDOM and dom4j. JAXP (Java API for XML Processing) is a Java standard API, including two parsing methods: DOM and SAX. DOM parsing loads XML configuration files into memory for random access and modification. SAX is an event-based parsing that takes up less memory but is not suitable for modifying XML configuration files. JAXB (Java Architecture for XML Binding) allows Java developers to map Java classes to XML configuration files for representation, and vice versa, and is suitable for serialization and deserialization of objects to XML configuration files. JDOM and dom4j are third-party libraries that provide simpler and easier-to-use APIs to operate XML configuration files, especially when processing complex XML configuration files, which are more flexible than JAXP.

[0039] In C#, XML configuration files are processed through .NET Framework and .NET Core. System.Xml is part of .NET Framework and provides XmlDocument and XmlDataDocument to process XML configuration files. XmlDocument provides DOM access to XML configuration files, while XmlDataDocument is specially designed to work with DataSet. LINQ to XML (XElement, XDocument, etc.) is introduced in .NET 3.5 and later versions, which provides LINQ-based methods for querying and manipulating XML configuration files, making processing XML configuration files simpler and more intuitive.

[0040] In the browser or Node.js environment, the way to process XML configuration files is different. In the browser, you can use the DOM API to parse and manipulate XML configuration files, but you usually don't use the DOM API directly to process large XML configuration files because it takes up a lot of memory. In Node.js, you can use third-party libraries such as xml2js and xmlbuilder to parse and build XML configuration files. These libraries provide a simpler API to process XML configuration files.

[0041] Therefore, in this embodiment, based on different programming languages, an XML processing library compatible with the development environment can be selected to process the XML configuration file.

[0042] Further, after reading the XML configuration file, a SQL mapping element is obtained, and the SQL mapping element is traversed to obtain the first SQL statement.

[0043] In SQL mapping, especially when using a persistence layer framework such as MyBatis in the embodiment of the present application, traversing mapping elements usually dynamically includes collection- or array-based data in SQL statements. MyBatis provides a powerful dynamic SQL function that allows SQL statements to be dynamically constructed based on the passed in parameter conditions.

[0044] Specifically, in this embodiment, the <foreach> tag is used to traverse the SQL mapping elements. <foreach>It is the most commonly used tag in MyBatis for traversing a collection or array. It can repeat the SQL fragment according to the size of the collection. You need to ensure that the collection or array passed to the SQL statement is not empty, otherwise it may cause a SQL syntax error. You can avoid this problem by checking the size of the collection before calling SQL. <foreach>The open, separator, and close attributes of the tag allow you to control the format of the generated SQL fragment. MyBatis's dynamic SQL function is very powerful. In addition to <foreach>, there are <if> 、 <choose> ( <when>and <otherwise>), <trim>, <where> and other tags can be used to build more complex dynamic SQL statements.

[0045] Step 102, obtaining the source database grammar rules and the target database grammar rules, converting the first SQL statement according to the source database grammar rules, including converting the data type of the first SQL statement and replacing the functions and system variables of the first SQL statement, to generate a second SQL statement that complies with the target database grammar rules.

[0046] In the embodiment of the present application, the data type of the first SQL statement is converted, including, before converting the data type, verifying whether the data in the source database exceeds the data type range of the target database, and performing data type conversion on the data that exceeds the data type range of the target database.

[0047] Specifically, the source database grammar rules and the target database grammar rules can be obtained by visiting the official website of each database. In this embodiment, MySQL is used as the source database and OpenGauss is used as the target database for illustration. As two popular database management systems, MySQL and OpenGauss each support a set of data types, which differ in name, range, precision and storage requirements. Therefore, when migrating data from MySQL to OpenGauss, it is necessary to correctly convert the data types, especially the conversion of integer types (such as TINYINT, MEDIUMINT, BIGINT).

[0048] In MySQL, TINYINT is a signed 8-bit integer with a range from --128 to 127 (or 0 to 255 if UNSIGNED is used). In OpenGauss, the closest type is SMALLINT, which is a 16-bit integer with a wider range (--32,768 to 32,767, or 0 to 65,535 for UNSIGNED). If the purpose of TINYINT is limited to very small values ​​and UNSIGNED is not required, you can consider using OpenGauss's SMALLINT, but be aware of the possible storage waste. If you need to exactly match the storage and range of TINYINT, you need to constrain the data by applying logic or use other mechanisms.

[0049] In MySQL, MEDIUMINT is a signed 24-bit integer ranging from -8,388,608 to 8,388,607 (or 0 to 16,777,215 if UNSIGNED is used). In OpenGauss, there is no direct corresponding type for MEDIUMINT, but INTEGER (or INT) is a good choice, which is a 32-bit integer with a wider range (-2,147,483,648 to 2,147,483,647, or 0 to 4,294,967,295 for UNSIGNED).

[0050] In MySQL, BIGINT is a signed 64-bit integer ranging from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (or from 0 to 18,446,744,073,709,551,615 if UNSIGNED is used). In OpenGauss, BIGINT is the direct corresponding type, also a 64-bit integer with the same range and precision.

[0051] Although the length of integer types (such as INT(11)) is mainly used to display width in MySQL and does not limit the range of values, you still need to pay attention to how the target database handles the length when converting. Types such as INTEGER or BIGINT in OpenGauss usually do not specify a length because their length and range are defined by the type itself.

[0052] Before converting data types, you should verify that the data in the source database can be correctly represented within the range of the new data type in the target database. If some values ​​are outside the range of the new data type, appropriate conversion or error handling is required.

[0053] Although data type conversion usually does not affect query performance too much, choosing the right data type can optimize storage and indexing efficiency. Therefore, when converting, you should consider the actual usage and storage requirements of the data to ensure that the converted data type is still compatible in the application and maintains data consistency and accuracy.

[0054] When migrating MySQL data to OpenGauss, you can ensure data integrity and smooth application migration by understanding the data type differences between the two database systems and converting according to actual needs.

[0055] Furthermore, the functions and system variables of the first SQL statement are replaced, wherein the functions include date and time functions, string processing functions, and aggregate functions; the system variables include database connection, performance optimization, and character set settings, and the functions and system variables in the first SQL statement are replaced with equivalent values ​​corresponding to the target database.

[0056] Specifically, in the process of database migration, especially when migrating from MySQL to a different database system such as OpenGauss, replacing functions and system variables is a key step. Since different database management systems (DBMS) differ in internal implementation, function naming, parameter lists, and system variables, directly migrating SQL code and database objects may encounter problems.

[0057] Therefore, in the embodiments of the present application, it is first necessary to identify the main differences between MySQL and OpenGauss in functions and system variables, and to understand the list of functions supported by each, the behavior of the functions (such as return value type, parameter processing, etc.), and the definition and purpose of system variables by consulting the official documentation of the two databases.

[0058] Date and time functions are one of the common types that need to be replaced. For example, the NOW() function of MySQL returns the current date and time, while OpenGauss may use CURRENT_TIMESTAMP or LOCALTIMESTAMP. Similarly, date formatting functions such as MySQL's DATE_FORMAT() may correspond to TO_CHAR() combined with date and time types in OpenGauss.

[0059] String processing functions also often need to be replaced between different databases. For example, MySQL's CONCAT() function is used to concatenate strings. OpenGauss also supports this function, but you also need to pay attention to the equivalence of other string functions such as SUBSTRING() (in MySQL) and SUBSTR() (a more common name in OpenGauss and many other SQL databases).

[0060] Aggregate functions (such as SUM(), AVG(), COUNT(), MAX(), MIN(), etc.) are similar in most SQL database systems, but there may be subtle differences, especially when dealing with NULL values ​​or specific data types. Although these functions are generally directly interchangeable in MySQL and OpenGauss, you should still verify that their behavior is as expected during the migration process.

[0061] System variables are used to control the operating environment of the database server. The system variables of MySQL and OpenGauss are different, including their names, scopes, and default values. During the migration process, you need to pay special attention to key system variables that affect database connections, performance optimization, character set settings, etc., and set corresponding equivalent values ​​in OpenGauss.

[0062] For simple function and system variable replacement, it can be done manually directly in the SQL script or application code. For large-scale replacement, scripts can be written (such as using Python, Perl, etc.) to automate the search and replace process.

[0063] After replacing functions and system variables, you can also perform testing and verification, including unit testing, integration testing, and performance testing, to ensure that the migrated database system can correctly perform all necessary operations and that the performance meets expectations.

[0064] Furthermore, a second SQL statement that complies with the grammatical rules of the target database is generated, including identifying incompatible grammatical rules in the grammatical rules of the source database and the grammatical rules of the target database, matching strings in the incompatible grammatical rules through regular expressions, the strings including table names, field names and function names, formulating corresponding replacement plans according to the grammatical rules of the target database, replacing the strings with compatible forms of the target database, and adjusting structural differences.

[0065] Specifically, you first need to understand the differences in SQL syntax between the source database and the target database, including differences in data types, function names, reserved words, reference methods for table names and field names, and SQL statement structures; then use regular expressions to match table names, field names, and function names in SQL statements. Due to the complexity and flexibility of SQL statements, you can also use an SQL parser or abstract syntax tree (AST) to analyze SQL statements.

[0066] According to the syntax rules of the target database, formulate a replacement plan, including replacing double quotes on parameter values ​​with single quotes in batches; setting the auto-increment sequence field in the Insert statement to default; replacing double quotes on constant values ​​in SQL statements with single quotes; replacing BETWEEN AND with a size operation expression; replacing the CONUT (distinct xx, xX) multi-field deduplication syntax; replacing the !ISNULL syntax; and replacing the query statement with a keyword as an alias syntax.

[0067] It should be noted that the above replacement scheme is only a part of the replacement rules in MySQL and OpenGauss, and is only used to formulate specific instructions for the replacement scheme according to the grammatical rules of the target database, and is not used to limit the protection scope of the embodiments of the present application.

[0068] Furthermore, after replacing the string with a compatible form of the target database and adjusting the structural differences, all mapper.XML configuration files in the XML configuration file are recursively scanned and automatically replaced based on the replacement scheme.

[0069] In this embodiment, the XML configuration file of MyBatis is processed by an automated tool, and all mapper.XML configuration files in the specified directory can be recursively scanned, the XML configuration file can be parsed, and SQL statements in tags such as <select>, <insert>, <update>, and <delete> can be extracted. The SQL statements are converted by applying the replacement scheme formulated previously, and the converted SQL statements are written back to the XML configuration file.

[0070] Specifically, in this embodiment, Java is selected as the technology stack for processing, wherein XML parsing can use JAXB (Java Architecture for XML Binding) to map XML to Java objects, or use DOM (Document Object Model) or SAX (Simple API for XML) parsers; file processing uses the Java NIO (New Input / Output) library to provide an efficient file processing API; string processing uses Java's String class and StringBuilder / StringBuffer class to provide string processing functions; Maven or Gradle is used to manage project dependencies and build processes.

[0071] Use Java NIO's Files.walk(Paths.get(startDirectory), Integer.MAX_VALUE) method to recursively traverse all files in the specified directory and check whether the name of each file ends with .xml and contains the word mapper or a custom naming convention.

[0072] For each mapper.XML configuration file found, use JAXB, DOM, or SAX parser to load and parse the XML configuration file. Create a class to map the <mapper> elements in the XML and use annotations (if using JAXB) to indicate how to map the <select>, <insert> 、 <update>and <delete> tags.

[0073] During the parsing process, the text content (i.e., SQL statements) of each <select>, <insert>, <update>, and <delete> tag is extracted, and then the previously formulated replacement rules are applied to modify these SQL statements. This step requires regular expression matching or more complex string processing logic, depending on the complexity of the replacement rules.

[0074] The modified SQL statement needs to be written back to the XML configuration file, which can be done by using JAXB, DOM, or SAX again. If JAXB is used, the state of the Java object can be updated and re-serialized into XML. If DOM or SAX is used, the DOM tree can be modified directly in memory and then written back to the file.

[0075] Before deploying the automation tool to production, test it in the development environment, including various scenarios such as XML configuration files with different structures, complex SQL statements, and potential error conditions.

[0076] Integrate automation tools into your build or deployment process by writing custom Maven plugins, Gradle tasks, or using CI / CD tools such as Jenkins, GitLab CI / CD, or GitHub Actions.

[0077] In another embodiment of the present application, Python is selected as the technology stack to handle tasks such as file processing, XML parsing, string processing, and project management, and is implemented efficiently with the help of Python's powerful ecosystem and rich libraries.

[0078] Specifically, Python has become a popular choice for handling various tasks (including file processing, XML parsing, etc.) due to its concise syntax, readability, and rich standard and third-party libraries. Python has an active community and complete documentation, allowing developers to quickly get started and solve problems.

[0079] There are several libraries in Python that can be used for XML parsing and mapping, the most well-known of which include ElementTree (part of the Python standard library) and lxml. ElementTree provides a simple API to parse and create XML data, which is suitable for most basic needs. lxml is a more powerful and flexible library that provides faster parsing speed and richer functions, such as XPath support.

[0080] Python's file handling capabilities are very powerful and flexible. Python's built-in open function can be used to open files, and provides a variety of methods such as read, write, and readline to read and write file contents. In addition, Python's os and shutil modules provide a wealth of file and directory operations, such as traversing directories and copying files. For scenarios that require efficient processing of large numbers of files or data streams, Python's io module provides buffered IO operations that can significantly improve performance.

[0081] Python's string processing capabilities are equally powerful. Python strings are immutable, but the str class provides a wealth of built-in methods for processing strings, such as finding, replacing, splitting, and merging. For scenarios where strings need to be modified frequently, data structures such as io.StringIO or list can be used to simulate the behavior of mutable strings. In addition, Python also provides a powerful tool called regular expressions (through the re module) to perform complex string search and replacement operations.

[0082] Although Python does not have a build tool specifically for Java projects like Maven or Gradle, the Python community also has powerful package management and build tools. Pip is Python's package management tool, which can be used to install and manage Python packages. For more complex project management needs, you can use tools such as setuptools or poetry to define project dependencies, build processes, and package releases. In addition, for projects that require automated testing, document generation, and other functions, you can also use tools and libraries such as pytest and sphinx.

[0083] Step 103: Optimize the second SQL statement based on the characteristics of the target database to obtain an optimized SQL statement.

[0084] Optimize the second SQL statement, including date type filtering optimization, fuzzy matching performance optimization, index optimization, SQL statement optimization, and configuration file optimization.

[0085] Specifically, taking Gaussian database as an example, using BETWEEN #{Start, jdbcType=TIMESTAMP} AND #{end, jdbcType=TIMESTAMP} in this database may cause the database to perform implicit type conversion, which will affect query performance. Further optimizing the above SQL statement to BETWEEN DATE_FORMAT(#{Start, jdbcType=TIMESTAMP}, 'format') AND DATE_FORMAT(#{end, jdbcType=TIMESTAMP}, 'format') can avoid performance damage caused by implicit conversion. It should be noted that the DATE_FORMAT function is usually used to convert date and time to string format and is not directly used for date range comparison. In Gaussian database or most SQL databases, you can also directly use date or timestamp fields for comparison, or ensure that the data types of both ends of the comparison are consistent. If formatting is required, ensure that the strings compared are the same format. By ensuring that the Start and end fields are consistent with the field types in the target database, use BETWEEN directly.

[0086] In this embodiment, the Gaussian database is still taken as an example. In this database, using LIKE CONCAT("%", #{name, jdbcType=VARCHAR}, "%") may have poor performance when the amount of data is large, and it is optimized to LIKE'%'||#{name, jdbcType=VARCHAR}||'%'. It should be noted that such an optimization method is closer to Oracle's syntax. For the Gaussian database or most SQL databases, the standard SQL connector || or the string connection function supported by the specific database should be used, such as CONCAT() in MySQL. Directly using || for connection in SQL usually does not require an additional CONCAT function unless it is to deal with specific data format issues.

[0087] Specifically, the index in the second SQL statement is optimized. The execution plan of the second SQL statement is analyzed using tools provided by the database, such as EXPLAIN of MySQL and EXPLAIN ANALYZE of PostgreSQL, to check whether the index is used, which indexes are used, and how efficiently the index is used. According to the result of the query plan, the writing of the SQL statement is adjusted or the index is added / modified so that the query can use the index more effectively. For example, it is ensured that the columns in the query condition are covered by the index, or the query condition is adjusted to make the index more effective.

[0088] Be careful to avoid using functions or calculation expressions to operate on index columns in query conditions, as this may cause the index to become invalid. Also, be careful to avoid using operations such as NOT, <>, LIKE '% value%' in the WHERE clause that may cause the index to become invalid.

[0089] Specifically, the second SQL statement itself is optimized, including avoiding full table scans, using aggregate functions and subqueries appropriately, and optimizing the second SQL statement by analyzing query plans, adjusting query logic, and index usage. In addition, the optimization tools provided by the database, such as the EXPLAIN command, can be used to assist in analyzing query performance bottlenecks.

[0090] Specifically, the MyBatis configuration file is optimized, including reasonable configuration of aliases, mappers, etc., to reduce redundant configuration in the XML configuration file, improve the readability and maintainability of the code, simplify the writing of fully qualified class names in the XML configuration file by configuring aliases; specify the location of the SQL mapping file by configuring the mapper; and manage different database environments by configuring environment variables.

[0091] Step 104, rewrite the optimized SQL statement into the XML configuration file of MyBatis.

[0092] In an embodiment of the present application, the optimized SQL statement is rewritten into the XML configuration file of MyBatis. It is necessary to define a <select> (or other corresponding query type tags, such as <insert>, <update>, <delete>) tag inside the <mapper> tag and specify the optimized SQL statement therein.

[0093] Specifically, name the MyBatis Mapper XML configuration file OrderMapper.xml, then define a <mapper> tag in the file and define a <select> tag inside it to contain the optimized SQL statement.

[0094] The namespace attribute of the <mapper> tag matches the fully qualified name of the Mapper interface so that MyBatis can correctly find the mapping relationship between the interface and the XML configuration file.

[0095] <select>The id attribute of the tag matches the corresponding method name in the Mapper interface. In this embodiment, it is assumed that there is a method named selectRecentShippedOrders in the interface.

[0096] Specify the Java type to which the result returned by the SQL query is mapped. It is assumed that there is a Java class named Order to represent the data.

[0097] In the SQL statement, #{parameterName} is used to specify the position of the parameter. The values ​​of these parameters will be provided by MyBatis through the parameters of the Mapper interface method when executing the query. It should be noted that the #{customerId}, #{startDate}, #{endDate} and #{1imit} included therein need to match the parameter names defined in the Mapper interface method.

[0098] Finally, ensure that the MyBatis configuration file (such as mybatis-config.xml) already contains the path to the Mapper XML configuration file so that MyBatis can load and parse it. When the selectRecentShippedOrders method is called through the Mapper interface of MyBatis, MyBatis will execute the optimized SQL statement defined in OrderMapper.xml.

[0099] As shown in Figure 2, an embodiment of the present application provides a database migration device based on MyBatis, including a data acquisition module 201, a data processing module 202, a data optimization module 203 and a data migration module 204, wherein:

[0100] the data acquisition module 201 is configured to obtain the XML configuration file of MyBatis, and obtain the grammatical rules of the source database and the grammatical rules of the target database;

[0101] the data processing module 202 is configured to parse the XML configuration file to obtain a first SQL statement; according to the grammatical rules of the source database, the first SQL statement is converted, including converting the data type of the first SQL statement and replacing the functions and system variables of the first SQL statement, to generate a second SQL statement that complies with the grammatical rules of the target database;

[0102] the data optimization module 203 is configured to optimize the second SQL statement based on the characteristics of the target database to obtain an optimized SQL statement;

[0103] the data migration module 204 is configured to rewrite the optimized SQL statement into the XML configuration file of MyBatis to perform data migration between the source database and the target database. The database migration device based on MyBatis in the embodiment of the present application can be a computer device, or a component in a computer device, such as an integrated circuit or a chip. The computer device can be a terminal, or other devices except the terminal.Exemplarily, the computer device can be a mobile phone, a tablet computer, a notebook computer, a PDA, a vehicle-mounted computer device, a mobile Internet Device (MID), an ultra-mobile personal computer (UMPC), a netbook or a personal digital assistant (PDA), etc., and can also be a server, a network attached storage (NAS), a personal computer (PC), etc., which is not specifically limited in the embodiments of the present application.

[0105] The MyBatis-based database migration device provided in the embodiments of the present application can implement the various processes implemented by the MyBatis-based database migration method embodiment of Figure 1. To avoid repetition, it is not repeated here.

[0106] The embodiments of the present application also provide a computer device, as shown in Figure 3, the computer device includes a processor 301 and a memory 302, and the memory 302 stores a program or instruction that can be run on the processor 301. When the program or instruction is executed by the processor 301, the various steps of the MyBatis-based database migration method are implemented, and the same technical effect can be achieved. To avoid repetition, it is not repeated here.

[0107] It should be noted that the computer device in the embodiment of the present application includes the above-mentioned mobile computer device and non-mobile computer device.

[0108] The memory 302 can be used to store software programs and various data. The memory 302 can mainly include a first storage area for storing programs or instructions and a second storage area for storing data, wherein the first storage area can store an operating system, an application program or instruction required for at least one function (such as a sound playback function, an image playback function, etc.). In addition, the memory 302 can include a volatile memory or a non-volatile memory, or the memory 302 can include both volatile and non-volatile memories. Among them, the non-volatile memory can be a read-only memory (ROM), a programmable read-only memory (PROM), an erasable programmable read-only memory (EPROM), an electrically erasable programmable read-only memory (EEPROM), or a flash memory.The volatile memory may be a random access memory (RAM), a static random access memory (SRAM), a dynamic random access memory (DRAM), a synchronous dynamic random access memory (SDRAM), a double data rate synchronous dynamic random access memory (DDRSDRAM), an enhanced synchronous dynamic random access memory (ESDRAM), a synchronous link dynamic random access memory (SLDRAM) and a direct RAM bus random access memory (DRRAM). The memory 302 in the embodiment of the present application includes but is not limited to these and any other suitable types of memory.

[0109] The processor 301 may include one or more processing units; optionally, the processor 301 integrates an application processor and a modem processor, wherein the application processor mainly processes operations involving an operating system, a user interface and application programs, and the modem processor mainly processes wireless communication signals, such as a baseband processor. It is understandable that the above-mentioned modem processor may not be integrated into the processor 301.

[0110] The embodiment of the present application also provides a readable storage medium, on which a program or instruction is stored. When the program or instruction is executed by a processor, each process of the above-mentioned MyBatis-based database migration method embodiment is implemented, and the same technical effect can be achieved. To avoid repetition, it will not be repeated here.

[0111] The embodiment of the present application also provides a chip, the chip includes a processor and a communication interface, the communication interface is coupled to the processor, and the processor is used to run the program or instruction to implement each process of the above-mentioned MyBatis-based database migration method embodiment, and the same technical effect can be achieved. To avoid repetition, it will not be repeated here.

[0112] It should be understood that the chip mentioned in the embodiment of the present application can also be called a system-level chip, a system chip, a chip system or a system-on-chip chip.

[0113] The embodiment of the present application also provides a computer program product, which is stored in a storage medium, and the program product is executed by at least one processor to implement each process of the above-mentioned MyBatis-based database migration method embodiment, and the same technical effect can be achieved. To avoid repetition, it will not be repeated here.

[0114] It should be noted that, in this article, the terms "comprises", "includes" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also includes other elements not explicitly listed, or also includes elements inherent to such process, method, article or device.In the absence of further restrictions, an element defined by the sentence "comprising a ..." does not exclude the presence of other identical elements in the process, method, article or device comprising the element. In addition, it should be noted that the scope of the method and device in the embodiments of the present application is not limited to performing functions in the order shown or discussed, and may also include performing functions in a substantially simultaneous manner or in a reverse order according to the functions involved. For example, the described method may be performed in an order different from that described, and various steps may also be added, omitted, or combined. In addition, the features described with reference to certain examples may be combined in other examples.

[0115] The embodiments of the present application are described above in conjunction with the accompanying drawings, but the present application is not limited to the above-mentioned specific embodiments. The above-mentioned specific embodiments are merely illustrative and not restrictive. Under the enlightenment of the present application, a person of ordinary skill in the art may also make many forms without departing from the scope protected by the purpose of the present application and the claims, all of which belong to the protection of the present application.< / select> < / update> < / insert> < / when> < / choose> < / if> < / foreach> < / foreach>

Claims

1. A database migration method based on MyBatis, characterized in that: The method comprises: Obtain an XML configuration file of MyBatis and parse the XML configuration file to obtain a first SQL statement; Acquire a source database grammar rule and a target database grammar rule, and convert the first SQL statement according to the source database grammar rule, including converting the data type of the first SQL statement and replacing the function and system variable of the first SQL statement, to generate a second SQL statement that complies with the target database grammar rule; The second SQL statement is optimized based on the characteristics of the target database to obtain an optimized SQL statement, and the optimized SQL statement is rewritten into the XML configuration file of MyBatis.

2. The MyBatis-based database migration method according to claim 1, characterized in that: The obtaining of the XML configuration file of MyBatis and parsing the XML configuration file to obtain the first SQL statement includes reading the XML configuration file through an XML processing library to obtain SQL mapping elements, traversing the SQL mapping elements, and obtaining the first SQL statement.

3. The MyBatis-based database migration method according to claim 1, characterized in that: The converting of the data type of the first SQL statement includes, before converting the data type, verifying whether the data in the source database exceeds the data type range of the target database, and performing data type conversion on the data that exceeds the data type range of the target database.

4. The MyBatis-based database migration method according to claim 1, characterized in that: The functions and system variables of the first SQL statement are replaced, wherein the functions include date and time functions, string processing functions and aggregate functions; the system variables include database connection, performance optimization and character set settings, and the functions and system variables in the first SQL statement are replaced with equivalent values ​​corresponding to the target database.

5. The MyBatis-based database migration method according to claim 1, characterized in that: The generating of the second SQL statement conforming to the grammatical rules of the target database includes identifying incompatible grammatical rules in the grammatical rules of the source database and the grammatical rules of the target database, matching character strings in the incompatible grammatical rules by regular expressions, wherein the character strings include table names, field names, and function names, formulating corresponding replacement schemes according to the grammatical rules of the target database, replacing the character strings with compatible forms of the target database, and adjusting structural differences.

6. The MyBatis-based database migration method according to claim 5, characterized in that: After replacing the character string with a compatible form of the target database and adjusting the structural differences, all mapper.XML configuration files in the XML configuration file are recursively scanned and automatically replaced based on the replacement scheme.

7. The MyBatis-based database migration method according to claim 1, characterized in that: The second SQL statement is optimized based on the characteristics of the target database to obtain an optimized SQL statement, including date type filtering optimization, fuzzy matching performance optimization, index optimization, SQL statement optimization and configuration file optimization.

8. A database migration device based on MyBatis, characterized in that: The device comprises: The data acquisition module is configured to obtain the XML configuration file of MyBatis, and obtain the syntax rules of the source database and the syntax rules of the target database; A data processing module is configured to parse the XML configuration file to obtain a first SQL statement; convert the first SQL statement according to the grammatical rules of the source database, including converting the data type of the first SQL statement and replacing the function and system variables of the first SQL statement, to generate a second SQL statement that complies with the grammatical rules of the target database; A data optimization module is configured to optimize the second SQL statement based on the characteristics of the target database to obtain an optimized SQL statement; The data migration module is configured to rewrite the optimized SQL statement into the XML configuration file of MyBatis to perform data migration between the source database and the target database.

9. A MyBatis-based database migration system, the system comprising a processor and a memory, wherein a computer program is stored in the memory, characterized in that: The computer program is loaded and executed by the processor to implement the MyBatis-based database migration method as described in any one of claims 1 to 7.

10. A computer-readable storage medium, wherein a computer program is stored in the storage medium, characterized in that: When the computer program is executed by a processor, it is used to implement the MyBatis-based database migration method as described in any one of claims 1 to 7.

Images