Cross-type database migration method and device and computer equipment

Abstract

The invention relates to a cross-type database migration method and device and computer equipment, and the method comprises the steps: creating a software project, and configuring the environment dependence of the software project; drivers of a first database and a second database are introduced into the software project, data sources of the first database and the second database are configured, and the types of the first database and the second database are different; performing data type conversion on a first data table in the first database, and creating a second data table corresponding to the first data table in the second database; and migrating the data in the first data table to the second data table. Through the database migration method and device, the problems of data loss, insufficient security and low migration efficiency existing in a database migration method in related technologies are solved, and the effects of improving the database migration efficiency and improving the data security in the database migration process are achieved.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a cross-type database migration method, device and computer equipment. Background Art

[0002] With the rapid development of information technology, various information systems are widely used in various industries. As a core component of information systems, databases are crucial for their security, stability, and efficiency. In practical applications, data migration from one database to another is often necessary due to business needs, system upgrades, and security considerations. However, existing database migration methods often have many limitations, such as slow migration speed, low data security, and inability to adapt to offline environments.

[0003] In particular, when migrating between different database types, such as MySQL to Oracle, traditional migration methods are often difficult to apply directly due to differences in data structure, data types, and SQL syntax. Complex conversion and adaptation are required. Furthermore, data migration in environments without a network connection presents an even greater technical challenge.

[0004] The existing technology has the following defects and deficiencies:

[0005] Defects and shortcomings of existing technology:

[0006] 1. Data integrity: Traditional migration methods are prone to data loss or damage during the data migration process and cannot guarantee data integrity.

[0007] 2. Security: Traditional migration methods lack data encryption and protection measures during the data migration process, resulting in an increased risk of data leakage.

[0008] 3. Applicability: Traditional migration methods are usually only applicable to environments with network connections and cannot effectively migrate data in environments without network connections.

[0009] 4. Efficiency: Traditional migration methods are inefficient during data migration, especially for large-scale data migration, which takes a long time.

[0010] 5. Lack of testing and performance tuning: Traditional migration methods lack testing and performance tuning of migrated data after data migration is completed, resulting in unstable system performance.

[0011] 6. Repeated import: Traditional migration methods are prone to repeated data import during the data migration process, wasting time and resources.

[0012] Currently, database migration methods in related technologies have problems such as data loss, insufficient security, and low migration efficiency, and no effective solutions have been proposed. Summary of the Invention

[0013] The purpose of this application is to address the deficiencies in the prior art and provide a cross-type database migration method, apparatus, computer device, and computer-readable storage medium to at least solve the problems of data loss, insufficient security, and low migration efficiency in the database migration methods in the related art.

[0014] To achieve the above objectives, the technical solutions adopted in this application are:

[0015] In a first aspect, an embodiment of the present application provides a cross-type database migration method, comprising:

[0016] Creating a software project and configuring the environment dependencies of the software project;

[0017] Introducing drivers for a first database and a second database into the software project, and configuring data sources of the first database and the second database, wherein the first database and the second database are of different types;

[0018] Performing data type conversion on a first data table in the first database, and creating a second data table corresponding to the first data table in the second database;

[0019] Migrate the data in the first data table to the second data table.

[0020] In some embodiments, creating a software project and configuring the environment dependencies of the software project include:

[0021] Create software projects using the Java programming language based on the Spring Boot architecture;

[0022] Integrate the MyBatis framework into the software project;

[0023] Add MyBatis dependencies and starter dependencies to the pom.xml file of the software project.

[0024] In some embodiments, introducing drivers of the first database and the second database into the software project and configuring data sources of the first database and the second database includes:

[0025] Introducing drivers for the first database and the second database, and adding database connection configurations for the first database and the second database to the pom.xml file of the software project;

[0026] Define the data source configuration information of the first database and the second database in the application.yml file in the target directory;

[0027] Create data source beans for the data sources of the first database and the second database in the Spring configuration class, and bind the data source configuration information in the application.yml file to the data source beans;

[0028] Configure corresponding EntityManagerFactory and TransactionManager Bean for the data sources of the first database and the second database respectively.

[0029] In some embodiments, converting the data type of the first data table in the first database and creating a second data table corresponding to the first data table in the second database includes:

[0030] Connecting to the first database using JDBC, and obtaining structural information of the first data table by executing a query statement corresponding to the first database, wherein the structural information includes data types;

[0031] converting the data type of the first database into the data type suitable for the second database;

[0032] Perform DDL syntax conversion to generate new DDL statements;

[0033] Use JDBC to connect to the second database, execute the new DDL statement, and create the second data table in the second database.

[0034] In some embodiments, migrating the data in the first data table to the second data table includes:

[0035] Define the entity class corresponding to the first data table;

[0036] Execute data query on the first data table by using Statement;

[0037] The queried data is imported into the second data table through the InsertList method of MyBatis.

[0038] In a second aspect, an embodiment of the present application provides a cross-type database migration device, including:

[0039] A creation unit, used to create a software project and configure the environment dependencies of the software project;

[0040] a configuration unit, configured to introduce drivers of a first database and a second database into the software project, and configure data sources of the first database and the second database, wherein the first database and the second database are of different types;

[0041] a conversion unit, configured to perform data type conversion on the first data table in the first database, and create a second data table corresponding to the first data table in the second database;

[0042] A migration unit is configured to migrate the data in the first data table to the second data table.

[0043] In some embodiments, the creating unit includes:

[0044] The first creation module is used to create software projects based on the Spring Boot architecture using the Java programming language;

[0045] Integration module, used to integrate the MyBatis framework into the software project;

[0046] The first adding module is used to add MyBatis dependency and starter dependency in the pom.xml file of the software project.

[0047] In some embodiments, the configuration unit includes:

[0048] A second adding module is used to introduce drivers of the first database and the second database, and add database connection configurations of the first database and the second database in the pom.xml file of the software project;

[0049] A first definition module is used to define data source configuration information of the first database and the second database in the application.yml file in the target directory;

[0050] A binding module, configured to create data source beans for the data sources of the first database and the second database in the Spring configuration class, respectively, and bind the data source configuration information in the application.yml file to the data source beans;

[0051] The configuration module is used to configure corresponding EntityManagerFactory and TransactionManager Bean for the data sources of the first database and the second database respectively.

[0052] In a third aspect, an embodiment of the present application provides a computer device comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, wherein when the processor executes the computer program, the cross-type database migration method as described in the first aspect above is implemented.

[0053] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the cross-type database migration method as described in the first aspect above.

[0054] The present application adopts the above technical solution. Compared with the existing technology, the cross-type database migration method provided by the embodiment of the present application creates a software project and configures the environment dependency of the software project; introduces the drivers of the first database and the second database in the software project, and configures the data sources of the first database and the second database, wherein the types of the first database and the second database are different; performs data type conversion on the first data table in the first database, and creates a second data table corresponding to the first data table in the second database; and migrates the data in the first data table to the second data table, thereby solving the problems of data loss, insufficient security, and low migration efficiency in the database migration method in the related technology, and achieving the effect of improving the efficiency of database migration and improving the data security during the database migration process.

[0055] The details of one or more embodiments of the present application are set forth in the following drawings and description to make other features, objects, and advantages of the present application more readily apparent. BRIEF DESCRIPTION OF THE DRAWINGS

[0056] The drawings described herein are used to provide a further understanding of the present application and constitute a part of the present application. The illustrative embodiments of the present application and their descriptions are used to explain the present application and do not constitute an improper limitation on the present application. In the drawings:

[0057] Figure 1 is a structural block diagram of a mobile terminal according to an embodiment of the present application;

[0058] Figure 2 is a flowchart of a cross-type database migration method according to an embodiment of the present application;

[0059] Figure 3 is a schematic diagram of the execution process of the cross-type database migration method according to the preferred embodiment of the present application;

[0060] Figure 4 is a structural block diagram of a cross-type database migration device according to an embodiment of the present application;

[0061] Figure 5Schematic diagram of the hardware structure of a computer device according to an embodiment of the present application. DETAILED DESCRIPTION

[0062] In order to make the purpose, technical solutions and advantages of this application more clearly understood, the present application is described and illustrated below in conjunction with the accompanying drawings and examples. It should be understood that the specific embodiments described herein are merely used to explain this application and are not intended to limit this application. Based on the embodiments provided in this application, all other embodiments obtained by those of ordinary skill in the art without making any creative efforts are within the scope of protection of this application.

[0063] Obviously, the drawings described below are merely examples or embodiments of the present application. Those skilled in the art can, without inventive effort, apply the present application to other similar scenarios based on these drawings. Furthermore, it is also understood that, although the effort involved in such a development process may be complex and lengthy, for those skilled in the art related to the content disclosed in this application, changes in design, manufacturing, or production based on the technical content disclosed in this application are merely conventional technical means and should not be construed as an insufficiency of the content disclosed in this application.

[0064] References to "embodiments" in this application mean that a particular feature, structure, or characteristic described in connection with the embodiment may be included in at least one embodiment of the application. The appearance of this phrase in various places in the specification does not necessarily refer to the same embodiment, nor does it refer to independent or alternative embodiments that are mutually exclusive of other embodiments. It is understood, both explicitly and implicitly, by those skilled in the art that the embodiments described in this application may be combined with other embodiments unless there is a conflict.

[0065] Unless otherwise defined, the technical or scientific terms used in this application should have the ordinary meaning understood by a person of ordinary skill in the technical field to which this application belongs. The words "one", "a", "the" and the like used in this application do not indicate a limit on quantity and may indicate the singular or plural. The terms "include", "comprise", "have" and any variations thereof used in this application are intended to cover non-exclusive inclusions; for example, a process, method, system, product or device that includes a series of steps or modules (units) is not limited to the listed steps or units, but may also include steps or units that are not listed, or may also include other steps or units that are inherent to these processes, methods, products or devices. The words "connect", "connected", "coupled" and the like used in this application are not limited to physical or mechanical connections, but may include electrical connections, whether direct or indirect. The word "multiple" used in this application refers to two or more. "And / or" describes the association relationship of associated objects, indicating that three relationships can exist. For example, "A and / or B" can mean: A exists alone, A and B exist at the same time, and B exists alone. The character " / " generally indicates that the objects before and after are in an "or" relationship. The terms "first", "second", "third", etc. involved in this application are only used to distinguish similar objects and do not represent a specific order for the objects.

[0066] This embodiment provides a mobile terminal. Figure 1 : is a structural block diagram of a mobile terminal according to an embodiment of the present application. Figure 1 As shown, the mobile terminal includes components such as a radio frequency (RF) circuit 110, a memory 120, an input unit 130, a display unit 140, a sensor 150, an audio circuit 160, a wireless fidelity (WiFi) module 170, a processor 180, and a power supply 190. Those skilled in the art will understand that Figure 1 The structure of the mobile terminal shown in the figure does not constitute a limitation to the mobile terminal, and may include more or fewer components than shown in the figure, or combine certain components, or arrange the components differently.

[0067] The following combination Figure 1 A detailed introduction to the various components of the mobile terminal is given below:

[0068] The RF circuit 110 can be used to receive and send signals during information transmission or calls. In particular, after receiving downlink information from the base station, it is sent to the processor 180 for processing; in addition, the designed uplink data is sent to the base station. Generally, the RF circuit includes but is not limited to an antenna, at least one amplifier, a transceiver, a coupler, a low noise amplifier (LNA), a duplexer, etc. In addition, the RF circuit 110 can also communicate with the network and other devices through wireless communication. The above-mentioned wireless communication can use any communication standard or protocol, including but not limited to Global System of Mobile Communication (GSM), General Packet Radio Service (GPRS), Code Division Multiple Access (CDMA), Wideband Code Division Multiple Access (WCDMA), Long Term Evolution (LTE), email, Short Messaging Service (SMS), etc.

[0069] The memory 120 can be used to store software programs and modules. The processor 180 executes various functional applications and data processing of the mobile terminal by running the software programs and modules stored in the memory 120. The memory 120 may mainly include a program storage area and a data storage area. The program storage area may store an operating system, at least one application required for a function (such as a sound playback function, an image playback function, etc.), etc.; the data storage area may store data created based on the use of the mobile terminal (such as audio data, a phone book, etc.). In addition, the memory 120 may include a high-speed random access memory and may also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other volatile solid-state storage device.

[0070] The input unit 130 can be used to receive input digital or character information, and to generate key signal input related to the user settings and function control of the mobile terminal. Specifically, the input unit 130 may include a touch panel 131 and other input devices 132. The touch panel 131, also known as a touch screen, can collect user touch operations on or near it (such as operations performed by the user using any suitable object or accessory such as a finger, stylus, etc. on or near the touch panel 131) and drive the corresponding connection device according to a pre-set program. Optionally, the touch panel 131 may include two parts: a touch detection device and a touch controller. Among them, the touch detection device detects the user's touch direction, detects the signal caused by the touch operation, and transmits the signal to the touch controller; the touch controller receives the touch information from the touch detection device and converts it into touch point coordinates, which are then sent to the processor 180, and can receive commands sent by the processor 180 and execute them. In addition, the touch panel 131 can be implemented using various types such as resistive, capacitive, infrared, and surface acoustic wave. In addition to the touch panel 131, the input unit 130 may further include other input devices 132. Specifically, the other input devices 132 may include but are not limited to one or more of a physical keyboard, function keys (such as volume control keys, switch keys, etc.), a trackball, a mouse, and a joystick.

[0071] The display unit 140 can be used to display information input by the user or information provided to the user and various menus of the mobile terminal. The display unit 140 may include a display panel 141. Optionally, the display panel 141 may be configured in the form of a liquid crystal display (LCD), an organic light-emitting diode (OLED), etc. Further, the touch panel 131 may cover the display panel 141. When the touch panel 131 detects a touch operation on or near it, it is transmitted to the processor 180 to determine the type of touch event. Subsequently, the processor 180 provides corresponding visual output on the display panel 141 according to the type of touch event. Although in Figure 1 In the embodiment, the touch panel 131 and the display panel 141 are used as two independent components to implement the input and output functions of the mobile terminal. However, in some embodiments, the touch panel 131 and the display panel 141 can be integrated to implement the input and output functions of the mobile terminal.

[0072] The mobile terminal may also include at least one sensor 150, such as a light sensor, a motion sensor, and other sensors. Specifically, the light sensor may include an ambient light sensor and a proximity sensor, wherein the ambient light sensor may adjust the brightness of the display panel 141 according to the brightness of the ambient light, and the proximity sensor may turn off the display panel 141 and / or the backlight when the mobile terminal is moved to the ear. As a type of motion sensor, the accelerometer sensor can detect the magnitude of acceleration in all directions (generally three axes), and can detect the magnitude and direction of gravity when stationary. It can be used for applications that identify the posture of the mobile terminal (such as horizontal and vertical screen switching, related games, magnetometer posture calibration), vibration recognition related functions (such as pedometer, tapping), etc.; as for other sensors that can be configured in the mobile terminal, such as gyroscopes, barometers, hygrometers, thermometers, infrared sensors, etc., they will not be described here.

[0073] The speaker 161 and microphone 162 in the audio circuit 160 provide an audio interface between the user and the mobile terminal. The audio circuit 160 converts received audio data into electrical signals and transmits them to the speaker 161, which then converts the signals into sound signals for output. Furthermore, the microphone 162 converts the collected sound signals into electrical signals, which are then received by the audio circuit 160 and converted into audio data. The audio data is then processed by the processor 180 and transmitted to, for example, another mobile terminal via the RF circuit 110, or the audio data is output to the memory 120 for further processing.

[0074] WiFi is a short-range wireless transmission technology. Mobile terminals can help users send and receive emails, browse web pages, and access streaming media through the WiFi module 170. It provides users with wireless broadband Internet access. Figure 1 A WiFi module 170 is shown, but it is understandable that it is not an essential component of the mobile terminal and can be omitted as needed without changing the essence of the invention, or replaced with other short-range wireless transmission modules, such as a Zigbee module or a WAPI module.

[0075] The processor 180 is the control center of the mobile terminal, connecting all components of the mobile terminal using various interfaces and circuits. By running or executing software programs and / or modules stored in the memory 120 and accessing data stored in the memory 120, it executes various functions of the mobile terminal and processes data, thereby providing overall monitoring of the mobile terminal. Optionally, the processor 180 may include one or more processing units; preferably, the processor 180 may integrate an application processor and a modem processor. The application processor primarily handles the operating system, user interface, and application programs, while the modem processor primarily handles wireless communications. It is understood that the modem processor may not be integrated into the processor 180.

[0076] The mobile terminal also includes a power supply 190 (such as a battery) for supplying power to various components. Preferably, the power supply can be logically connected to the processor 180 through a power management system, thereby managing charging, discharging, and power consumption through the power management system.

[0077] Although not shown, the mobile terminal may further include a camera, a Bluetooth module, etc., which will not be described in detail here.

[0078] In this embodiment, the processor 180 is configured to:

[0079] Creating a software project and configuring the environment dependencies of the software project;

[0080] Introducing drivers for a first database and a second database into the software project, and configuring data sources of the first database and the second database, wherein the first database and the second database are of different types;

[0081] Performing data type conversion on a first data table in the first database, and creating a second data table corresponding to the first data table in the second database;

[0082] Migrate the data in the first data table to the second data table.

[0083] In some embodiments, the processor 180 is further configured to:

[0084] Create software projects using the Java programming language based on the Spring Boot architecture;

[0085] Integrate the MyBatis framework into the software project;

[0086] Add MyBatis dependencies and starter dependencies to the pom.xml file of the software project.

[0087] In some embodiments, the processor 180 is further configured to:

[0088] Introducing drivers for the first database and the second database, and adding database connection configurations for the first database and the second database to the pom.xml file of the software project;

[0089] Define the data source configuration information of the first database and the second database in the application.yml file in the target directory;

[0090] Create data source beans for the data sources of the first database and the second database in the Spring configuration class, and bind the data source configuration information in the application.yml file to the data source beans;

[0091] Configure corresponding EntityManagerFactory and TransactionManager Bean for the data sources of the first database and the second database respectively.

[0092] In some embodiments, the processor 180 is further configured to:

[0093] Connecting to the first database using JDBC, and obtaining structural information of the first data table by executing a query statement corresponding to the first database, wherein the structural information includes data types;

[0094] converting the data type of the first database into the data type suitable for the second database;

[0095] Perform DDL syntax conversion to generate new DDL statements;

[0096] Use JDBC to connect to the second database, execute the new DDL statement, and create the second data table in the second database.

[0097] In some embodiments, the processor 180 is further configured to:

[0098] Define the entity class corresponding to the first data table;

[0099] Execute data query on the first data table by using Statement;

[0100] The queried data is imported into the second data table through the InsertList method of MyBatis.

[0101] This embodiment provides a cross-type database migration method. Figure 2 is a flowchart of a cross-type database migration method according to an embodiment of the present application. Figure 2 As shown, the process includes the following steps:

[0102] Step S201: Create a software project and configure the environment dependencies of the software project;

[0103] Step S202: introducing drivers of a first database and a second database into the software project, and configuring data sources of the first database and the second database, wherein the first database and the second database are of different types;

[0104] Step S203: performing data type conversion on the first data table in the first database, and creating a second data table corresponding to the first data table in the second database;

[0105] Step S204: Migrate the data in the first data table to the second data table.

[0106] This application aims to solve the data migration problem between multiple database types, improve the security of the data migration process, and is particularly suitable for scenarios without a network environment. Through the embodiments of this application, users can complete database migration conveniently and quickly, reduce the risks during the migration process, and improve the efficiency and security of data migration.

[0107] In some embodiments, step S201 of creating a software project and configuring the environment dependencies of the software project may include:

[0108] Create software projects using the Java programming language based on the Spring Boot architecture;

[0109] Integrate the MyBatis framework into the software project;

[0110] Add MyBatis dependencies and starter dependencies to the pom.xml file of the software project.

[0111] Java is a popular, cross-platform programming language widely used in enterprise application development, mobile application development, game development, and other fields. Spring Boot is an open-source Java framework that simplifies the creation and deployment of Spring applications. Spring Boot helps developers quickly build Spring applications by providing default configurations and a series of convenient starters, reducing the amount of configuration and initialization work. With Spring Boot, developers can focus more on implementing business logic without spending too much time on configuring the underlying framework.

[0112] This application embodiment can integrate the MyBatis framework into an existing Spring Boot project. MyBatis is an excellent persistence layer framework that supports customized SQL, stored procedures, and advanced mapping. MyBatis avoids almost all JDBC code and manual parameter setting and result set acquisition.

[0113] In this embodiment, you can add MyBatis and MyBatis-Spring-Boot-Starter dependencies to the pom.xml file of your software project. In a Maven project, the pom.xml file is the core configuration file for the project, which contains all the dependencies required by the project. To integrate MyBatis into a Spring Boot project, you need to add the dependencies of MyBatis and MyBatis-Spring-Boot-Starter to the pom.xml file.

[0114] MyBatis dependency: It is the dependency of the MyBatis framework itself and provides the core functionality for interacting with the database.

[0115] MyBatis-Spring-Boot-Starter dependency: It is a starter dependency that enables MyBatis to be seamlessly integrated with Spring Boot. It contains MyBatis configuration and classes that are compatible with Spring Boot, thereby simplifying the process of using MyBatis in Spring Boot projects.

[0116] In the embodiment of the present application, by adding these dependencies in the pom.xml file, Maven will automatically download and manage these dependencies, so that the API and functions provided by MyBatis can be directly used in the project.

[0117] In some embodiments, step S202 of introducing drivers of the first database and the second database into the software project and configuring data sources of the first database and the second database may include:

[0118] Introducing drivers for the first database and the second database, and adding database connection configurations for the first database and the second database to the pom.xml file of the software project;

[0119] Define the data source configuration information of the first database and the second database in the application.yml file in the target directory;

[0120] Create data source beans for the data sources of the first database and the second database in the Spring configuration class, and bind the data source configuration information in the application.yml file to the data source beans;

[0121] Configure corresponding EntityManagerFactory and TransactionManager Bean for the data sources of the first database and the second database respectively.

[0122] The embodiment of the present application is described by taking MySQL as the first database and Oracle as the second database as an example.

[0123] The embodiment of the present application configures the database driver and connection, which may specifically include: first, introducing the MySQL database driver and the Oracle database driver, and adding the JDBC configuration of MySQL and Oracle in the pom.xml file. Then, configure application.yml, that is, in the application.yml file under the src / main / resources directory, define the configuration information of the two data sources, including but not limited to the data source URL, user name, password, driver class name, etc. Then, configure the data source Bean, that is, in the Spring configuration class (the class with the @Configuration annotation), create a DataSourceBean for each data source. Use the @ConfigurationProperties annotation to bind the configuration in application.yml to the corresponding DataSourceBean. Then, configure the MyBatis ORM framework, that is, configure the corresponding EntityManagerFactory and TransactionManager Bean for each data source. Through the above configuration, in the DAO, specify which data source to use through the @Qualifier annotation.

[0124] In some embodiments, step S203 converts the data type of the first data table in the first database and creates a second data table corresponding to the first data table in the second database, which may include:

[0125] Connecting to the first database using JDBC, and obtaining structural information of the first data table by executing a query statement corresponding to the first database, wherein the structural information includes data types;

[0126] converting the data type of the first database into the data type suitable for the second database;

[0127] Perform DDL syntax conversion to generate new DDL statements;

[0128] Use JDBC to connect to the second database, execute the new DDL statement, and create the second data table in the second database.

[0129] Step S203 can realize the migration of table structures between different types of databases, which may specifically include the following process:

[0130] 1. Connect to the source database

[0131] Use JDBC to connect to the MySQL source database (also known as the first database). Prepare and execute the following SQL query statement to obtain the structure information of the target table:

[0132] select`column_name`,`column_comment`,`column_type`,`column_default`,`is_nullable`,`column_key`frominformation_schema.columns where table_schema=#{database}and table_name=#{tableName}group by column_name.

[0133] 2. Execute the query

[0134] Execute the above SELECT statement and replace #{database} and #{tableName} with the actual database name and table name.

[0135] The result set will contain all the columns and properties of the table.

[0136] 3. Parsing the result set

[0137] Traverse the result set and extract information such as the name, comment, type, default value, whether it is nullable, and whether it is a primary key or foreign key for each column.

[0138] Identify the data type of the source database: Identify the data type of the column from the result set.

[0139] Map to the target database's data type: Map the source database's data type to the corresponding data type in the target database. MySQL's VARCHAR should be mapped to Oracle's VARCHAR2. Similarly, this type corresponds to SQL Server's NVARCHAR.

[0140] Data type length and precision: Different databases have length and precision limitations depending on the data type. VARCHAR data types cannot exceed 4000 characters in Oracle, but can be up to 8000 characters in SQL Server. Therefore, you need to adjust the parameters based on the limitations of the target database (i.e., the secondary database).

[0141] 4. DDL syntax conversion

[0142] Differences in keywords and syntax: Different database systems use different DDL keywords and syntax. Auto-increment columns use AUTO_INCREMENT in MySQL, IDENTITY in SQL Server, and a combination of sequences and triggers in Oracle to simulate the behavior of auto-increment columns.

[0143] Representation of constraints: Primary keys, foreign keys, unique constraints, non-null constraints, etc. are represented differently in different databases.

[0144] Differences in indexes and stored procedures: The source table has indexes or stored procedures, which need to be converted according to the syntax of the target database.

[0145] 5. Generate DDL statements

[0146] Construct a CREATE TABLE statement: Construct a CREATE TABLE statement based on the converted data type and DDL syntax.

[0147] Add constraints and indexes: Add appropriate constraints (such as primary keys, foreign keys, etc.) and indexes in the DDL statements.

[0148] Consider default values: Add a DEFAULT clause for those columns that have default values.

[0149] 6. Connect to the target database

[0150] Use JDBC to connect to the Oracle target database.

[0151] 7. Execute DDL statements:

[0152] Execute the generated CREATE TABLE statement on the target database to create the table structure.

[0153] In some embodiments, step S204 of migrating the data in the first data table to the second data table may include:

[0154] Define the entity class corresponding to the first data table;

[0155] Execute data query on the first data table by using Statement;

[0156] The queried data is imported into the second data table through the InsertList method of MyBatis.

[0157] Through the above step S204, table data migration between different types of databases can be achieved. The table data migration can specifically include the following process:

[0158] 1. Define the entity class corresponding to the database table, define an entity class for each table, and use JPA annotations to annotate the class and fields.

[0159] 2. Write query SQL statements and use Statement to execute SQL queries on MySQL database tables.

[0160] 3. Parse the result set: traverse the result set and obtain the required data.

[0161] 4. Switch the data source and import the above-mentioned data directly into the Oracle database through the InsertList method of MyBatis.

[0162] Figure 3 FIG. 1 is a schematic diagram of the execution process of the cross-type database migration method according to the preferred embodiment of the present application. Figure 3 As shown in the figure, the execution process of the cross-type database migration method specifically includes:

[0163] 1. Create a software project, including: creating a Java project; configuring environment dependencies.

[0164] 2. Configure database drivers and connections, including: introducing MySQL database drivers and Oracle database drivers; and configuring multiple data sources.

[0165] 3. Table structure migration, specifically including: executing query SQL statements; parsing result sets; performing DDL syntax conversion to generate DDL statements; and executing DDL statements.

[0166] 4. Table data migration, specifically including: defining database table entity classes; executing query SQL statements; parsing result sets; switching data sources and importing.

[0167] By repeating the above query and import operations, you can complete the data migration in all database tables.

[0168] It should be noted that Figure 3 The specific process shown in has been described in detail in the above embodiment and will not be repeated here.

[0169] The embodiments of this application utilize programming techniques to achieve complete migration across database types through multiple steps, technically ensuring data integrity and avoiding data loss or corruption. Furthermore, data migration through programming is highly efficient, significantly reducing migration time, especially for large-scale data migration.

[0170] During the migration process, the local intranet is not connected to the Internet, and only the two database servers are connected. Through the embodiment of the present application, intranet migration can be achieved. Through simple and clear programming ideas and technologies, it embodies high security, practicality, and adaptability, ensuring the security of data during the migration process and preventing data leakage.

[0171] In the embodiment of the present application, the original data is read-only, thereby ensuring that the risk of data loss is reduced during the data migration process. By optimizing the data migration process, repeated data import is avoided, thereby saving a lot of time and resources.

[0172] In summary, the embodiments of the present application not only solve many problems in the prior art, but also bring many obvious advantages, bringing substantial technological progress to the field of database migration.

[0173] It should be noted that the steps shown in the above process or the flowchart in the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions, and although a logical order is shown in the flowchart, in some cases, the steps shown or described can be executed in an order different from that shown here.

[0174] This embodiment provides a cross-type database migration device, which is used to implement the above-mentioned embodiments and preferred implementations. Details already described are omitted. As used below, terms such as "module," "unit," and "subunit" may refer to a combination of software and / or hardware that implements a predetermined function. Although the devices described in the following embodiments are preferably implemented in software, implementation using hardware, or a combination of software and hardware, is also possible and contemplated.

[0175] Figure 4 is a structural block diagram of a cross-type database migration device according to an embodiment of the present application, such as Figure 4 As shown, the device includes:

[0176] A creation unit 41 is used to create a software project and configure the environment dependency of the software project;

[0177] a configuration unit 42, configured to introduce drivers of a first database and a second database into the software project, and configure data sources of the first database and the second database, wherein the first database and the second database are of different types;

[0178] a conversion unit 43, configured to perform data type conversion on the first data table in the first database, and create a second data table corresponding to the first data table in the second database;

[0179] The migration unit 44 is configured to migrate the data in the first data table to the second data table.

[0180] In some embodiments, the creating unit 41 includes:

[0181] The first creation module is used to create software projects based on the Spring Boot architecture using the Java programming language;

[0182] Integration module, used to integrate the MyBatis framework into the software project;

[0183] The first adding module is used to add MyBatis dependency and starter dependency in the pom.xml file of the software project.

[0184] In some embodiments, the configuration unit 42 includes:

[0185] A second adding module is used to introduce drivers of the first database and the second database, and add database connection configurations of the first database and the second database in the pom.xml file of the software project;

[0186] A first definition module is used to define data source configuration information of the first database and the second database in the application.yml file in the target directory;

[0187] A binding module, configured to create data source beans for the data sources of the first database and the second database in the Spring configuration class, respectively, and bind the data source configuration information in the application.yml file to the data source beans;

[0188] The configuration module is used to configure corresponding EntityManagerFactory and TransactionManager Bean for the data sources of the first database and the second database respectively.

[0189] In some embodiments, the conversion unit 43 includes:

[0190] an acquisition module, configured to connect to the first database using JDBC and acquire structural information of the first data table by executing a query statement corresponding to the first database, wherein the structural information includes data types;

[0191] a conversion module, configured to convert the data type of the first database into a data type suitable for the second database;

[0192] The generation module is used to convert DDL syntax and generate new DDL statements;

[0193] The second creation module is used to connect to the second database using JDBC, execute the new DDL statement, and create the second data table in the second database.

[0194] In some embodiments, the migration unit 44 includes:

[0195] A second definition module is used to define the entity class corresponding to the first data table;

[0196] A query module, configured to execute a data query on the first data table by using a Statement;

[0197] The import module is used to import the queried data into the second data table through the InsertList method of MyBatis.

[0198] It should be noted that the above modules can be functional modules or program modules, and can be implemented through software or hardware. For modules implemented through hardware, the above modules can be located in the same processor; or the above modules can be located in different processors in any combination.

[0199] The embodiment provides a computer device. In conjunction with the cross-type database migration method of the embodiment of the present application, the computer device can be implemented. Figure 5 Schematic diagram of the hardware structure of a computer device according to an embodiment of the present application.

[0200] The computer device may include a processor 51 and a memory 52 storing computer program instructions.

[0201] Specifically, the processor 51 may include a central processing unit (CPU), or an application-specific integrated circuit (ASIC), or may be configured to implement one or more integrated circuits of the embodiments of the present application.

[0202] Among them, the memory 52 may include a large-capacity memory for data or instructions. By way of example and not limitation, the memory 52 may include a hard disk drive (HDD), a floppy disk drive, a solid-state drive (SSD), a flash memory, an optical disk, a magneto-optical disk, a magnetic tape, or a universal serial bus (USB) drive, or a combination of two or more of these. Where appropriate, the memory 52 may include a removable or non-removable (or fixed) medium. Where appropriate, the memory 52 may be inside or outside the data processing device. In a specific embodiment, the memory 52 is a non-volatile memory. In a specific embodiment, the memory 52 includes a read-only memory (ROM) and a random access memory (RAM). Where appropriate, the ROM may be a mask-programmed ROM, a programmable ROM (PROM), an erasable PROM (EPROM), an electrically erasable PROM (EEPROM), an electrically alterable ROM (EAROM) or a flash memory (FLASH), or a combination of two or more of these. Under appropriate circumstances, the RAM can be a static random access memory (SRAM) or a dynamic random access memory (DRAM), where the DRAM can be a fast page mode dynamic random access memory (FPMDRAM), an extended data output dynamic random access memory (EDODRAM), a synchronous dynamic random access memory (SDRAM), etc.

[0203] The memory 52 may be used to store or cache various data files that need to be processed and / or used for communication, as well as possible computer program instructions executed by the processor 51 .

[0204] The processor 51 implements any one of the cross-type database migration methods in the above embodiments by reading and executing computer program instructions stored in the memory 52 .

[0205] In some embodiments, the computer device may further include a communication interface 53 and a bus 50. Figure 5 As shown, the processor 51, the memory 52, and the communication interface 53 are connected via a bus 50 and communicate with each other.

[0206] The communication interface 53 is used to implement communication between the various modules, devices, units, and / or devices in the embodiments of the present application. The communication interface 53 can also implement data communication with other components such as: external devices, image / data acquisition equipment, databases, external storage, and image / data processing workstations.

[0207] The bus 50 includes hardware, software, or both, and couples components of a computer device to each other. The bus 50 includes, but is not limited to, at least one of the following: a data bus, an address bus, a control bus, an expansion bus, and a local bus. By way of example and not limitation, bus 50 may include an Accelerated Graphics Port (AGP) or other graphics bus, an Extended Industry Standard Architecture (EISA) bus, a Front Side Bus (FSB), a Hyper Transport (HT) interconnect, an Industry Standard Architecture (ISA) bus, an InfiniBand interconnect, a Low Pin Count (LPC) bus, a memory bus, a Micro Channel Architecture (MCA) bus, a Peripheral Component Interconnect (PCI) bus, a PCI-Express (PCI-X) bus, a Serial Advanced Technology Attachment (SATA) bus, a Video Electronics Standards Association Local Bus (VLB) bus, or other suitable buses, or a combination of two or more of these. Bus 50 may include one or more buses, where appropriate. Although embodiments herein describe and illustrate a particular bus, this application contemplates any suitable bus or interconnect.

[0208] In addition, in conjunction with the cross-type database migration method in the above embodiments, embodiments of the present application may provide a computer-readable storage medium for implementation. The computer-readable storage medium stores computer program instructions; when the computer program instructions are executed by a processor, any of the cross-type database migration methods in the above embodiments is implemented.

[0209] The technical features of the above-mentioned embodiments can be combined arbitrarily. In order to make the description concise, not all possible combinations of the technical features in the above-mentioned embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0210] The above-described embodiments merely represent several implementation methods of the present application. While the descriptions are relatively specific and detailed, they should not be construed as limiting the scope of the present invention. It should be noted that a person skilled in the art could make various modifications and improvements without departing from the spirit of the present application, all of which fall within the scope of protection of the present application. Therefore, the scope of protection of the present patent application shall be determined by the appended claims.

Claims

1. A cross-type database migration method, characterized in that: include: Creating a software project and configuring the environment dependencies of the software project; Introducing drivers for a first database and a second database into the software project, and configuring data sources of the first database and the second database, wherein the first database and the second database are of different types; Performing data type conversion on a first data table in the first database, and creating a second data table corresponding to the first data table in the second database; Migrate the data in the first data table to the second data table.

2. The method according to claim 1, characterized in that The step of creating a software project and configuring the environment dependencies of the software project includes: Create software projects using the Java programming language based on the Spring Boot architecture; Integrate the MyBatis framework into the software project; Add MyBatis dependencies and starter dependencies to the pom.xml file of the software project.

3. The method according to claim 2, characterized in that The step of introducing drivers of the first database and the second database into the software project and configuring data sources of the first database and the second database includes: Introducing drivers for the first database and the second database, and adding database connection configurations for the first database and the second database to the pom.xml file of the software project; Define the data source configuration information of the first database and the second database in the application.yml file in the target directory; Create data source beans for the data sources of the first database and the second database in the Spring configuration class, and bind the data source configuration information in the application.yml file to the data source beans; Configure corresponding EntityManagerFactory and TransactionManager Bean for the data sources of the first database and the second database respectively.

4. The method according to claim 2, characterized in that The converting the data type of the first data table in the first database and creating a second data table corresponding to the first data table in the second database includes: Connecting to the first database using JDBC, and obtaining structural information of the first data table by executing a query statement corresponding to the first database, wherein the structural information includes data types; converting the data type of the first database into the data type suitable for the second database; Perform DDL syntax conversion to generate new DDL statements; Use JDBC to connect to the second database, execute the new DDL statement, and create the second data table in the second database.

5. The method according to claim 2, characterized in that Migrating the data in the first data table to the second data table includes: Define the entity class corresponding to the first data table; Execute data query on the first data table by using Statement; The queried data is imported into the second data table through the InsertList method of MyBatis.

6. A cross-type database migration device, characterized in that: include: A creation unit, used to create a software project and configure the environment dependencies of the software project; a configuration unit, configured to introduce drivers of a first database and a second database into the software project, and configure data sources of the first database and the second database, wherein the first database and the second database are of different types; a conversion unit, configured to perform data type conversion on the first data table in the first database, and create a second data table corresponding to the first data table in the second database; A migration unit is configured to migrate the data in the first data table to the second data table.

7. The device according to claim 6, characterized in that The creation unit includes: The first creation module is used to create software projects based on the Spring Boot architecture using the Java programming language; Integration module, used to integrate the MyBatis framework into the software project; The first adding module is used to add MyBatis dependency and starter dependency in the pom.xml file of the software project.

8. The device according to claim 7, characterized in that The configuration unit includes: A second adding module is used to introduce drivers of the first database and the second database, and add database connection configurations of the first database and the second database in the pom.xml file of the software project; A first definition module is used to define data source configuration information of the first database and the second database in the application.yml file in the target directory; A binding module, configured to create data source beans for the data sources of the first database and the second database in the Spring configuration class, respectively, and bind the data source configuration information in the application.yml file to the data source beans; The configuration module is used to configure corresponding EntityManagerFactory and TransactionManager Bean for the data sources of the first database and the second database respectively.

9. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein: When the processor executes the computer program, the method according to any one of claims 1 to 5 is implemented.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method according to any one of claims 1 to 5 is implemented.

Images