Method and device for realizing synchronization between informix and MySQL databases based on XXL-JOB, equipment and medium

Abstract

The application discloses a kind of based on XXL-JOB implementation informix and the method, device, equipment and medium of MySQL database synchronization, method includes: after detecting the task of starting XXL-JOB, read task parameter;From informix database, obtain data with given content, and store to specified first HashMap;From MySQL, obtain all data with given content, and store to specified second HashMap;First HashMap is traversed, to query corresponding data in second HashMap with unique value, wherein, if corresponding data can be queried, then MD5 value is generated with all contents of the data and the MD5 value of MySQL is judged, and if value is different, it is marked as update value and is put into update pool;If it is not possible to be queried, then the data is put into new pool;From Druid connection pool, obtain MySQL connection, and the content in update pool and new pool is updated to MySQL in the mode of transaction in batches.The application can solve the problem of low efficiency of data synchronization between heterogeneous data sources, improve the efficiency of data synchronization.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method, apparatus, device, and medium for synchronizing Informix and MySQL databases based on XXL-JOB. Background Technology

[0002] With the development of enterprise informatization, all kinds of systems have emerged, and the data, which is the core of enterprise production and operation, is constantly transmitted in these systems.

[0003] Currently, common data storage methods include relational databases, NoSQL, NewSQL, message queues, and text files. Different data storage methods serve different purposes and each has its own advantages. Therefore, during the operation of enterprise systems, data needs to be exchanged and transmitted between different systems and storage structures to achieve data synchronization.

[0004] The common data synchronization method currently used, such as synchronizing data from an Informix database to a MySQL database, involves reading a single piece of data from Informix and then querying the corresponding data in MySQL with a specified unique value. If the data is not found, it is added to MySQL; if it is found, it is updated in MySQL.

[0005] However, this synchronization method only queries and updates one piece of data at a time, and updating one piece of data requires two queries and comparisons in Informix and MySQL, which is slow and has low synchronization efficiency. Summary of the Invention

[0006] In view of this, the purpose of the present invention is to provide a method, apparatus, device and medium for synchronizing Informix and MySQL databases based on XXL-JOB, so as to improve the above-mentioned problems.

[0007] This invention provides a method for synchronizing Informix and MySQL databases based on XXL-JOB, comprising:

[0008] After detecting a task that has started XXL-JOB, the task parameters of the task are read; wherein, the task parameters include the predetermined content of the data to be synchronized;

[0009] Retrieve data with the given content from the pre-configured Informix database, process part of the column names, parameters and content of each data, and store the processed data in the specified first HashMap with a unique value as the key;

[0010] Retrieve all data containing the given content from MySQL at once, and store them in the specified second HashMap using a unique value as the key;

[0011] Traverse the first HashMap and query the corresponding data in the second HashMap for the unique value. If the corresponding data is found, generate an MD5 value for all the contents of the data and compare it with the MD5 value stored in MySQL. If the values ​​are different, mark the data as an update value and put it into the update pool. If the corresponding data is not found, put the data into the new data pool.

[0012] Obtain the global Druid connection pool, acquire MySQL connections from the Druid connection pool, and sequentially update the contents of the update pool and the new pool to MySQL in batches using transactions.

[0013] Preferably, the task parameters further include at least one of executor, scheduling type, running strategy, routing strategy, and blocking handling strategy.

[0014] Preferably, when processing partial column names, parameters, and content of each data entry:

[0015] Map each column name in the data to the column name in the target table one by one, generate a specified Java object for each data record based on the mapping result and assign a value; where the unique code of the data is used as the ID identifier.

[0016] Preferably, it further includes:

[0017] A global Druid connection pool is created at startup; the Druid connection pool manages multiple database connections. During data synchronization, the database connections in the Druid connection pool are enabled. After synchronization is completed, the database connection resources are released and returned to the Druid connection pool.

[0018] Preferably, the task is a timed task, which starts automatically according to a set time node.

[0019] This invention also provides an apparatus for synchronizing Informix and MySQL databases based on XXL-JOB, comprising:

[0020] The task parameter reading unit is used to read the task parameters of the task after detecting that a task has started XXL-JOB; wherein, the task parameters include the predetermined content of the data to be synchronized;

[0021] The first storage unit is used to retrieve data with the predetermined content from the pre-configured Informix database, process part of the column names, parameters and content of each data, and store the processed data in the specified first HashMap with a unique value as the key.

[0022] The second storage unit is used to retrieve all data with the given content from MySQL at once and store it in the specified second HashMap with a unique value as the key;

[0023] The query unit is used to traverse the first HashMap and query the corresponding data in the second HashMap with the unique value. If the corresponding data can be found, the MD5 value generated from all the contents of the data is compared with the MD5 value stored in MySQL. If the values ​​are different, the data is marked as an update value and put into the update pool. If the corresponding data cannot be found, the data is put into the new data pool.

[0024] The update unit is used to obtain the global Druid connection pool, acquire a MySQL connection from the Druid connection pool, and sequentially update the contents of the update pool and the new pool to MySQL in batches using transactions.

[0025] Preferably, when processing partial column names, parameters, and content of each data entry:

[0026] Map each column name in the data to the column name in the target table one by one, generate a specified Java object for each data record based on the mapping result and assign a value; where the unique code of the data is used as the ID identifier.

[0027] Preferably, it further includes:

[0028] The connection pool creation unit is used to create a global Druid connection pool at startup. The Druid connection pool manages multiple database connections. During data synchronization, the database connections in the Druid connection pool are enabled. After synchronization is completed, the database connection resources are released and returned to the Druid connection pool.

[0029] This invention also provides a device for synchronizing Informix and MySQL databases based on XXL-JOB, which includes a memory and a processor. The memory stores a computer program that can be executed by the processor to implement the method for synchronizing Informix and MySQL databases based on XXL-JOB as described above.

[0030] This invention also provides a computer-readable storage medium storing a computer program that can be executed by a processor of the device where the computer-readable storage medium is located, to implement the method described above for synchronizing Informix and MySQL databases based on XXL-JOB.

[0031] In summary, this embodiment uses a HashMap to pre-collect and categorize all synchronized data before performing batch updates. Compared to the original method of connecting to the database to update data for each piece of data read, this embodiment reduces the number of database connection requests through batch updates, thereby reducing database connection time, significantly shortening data synchronization processing time, and improving data synchronization efficiency.

[0032] Furthermore, this embodiment reduces the database load when large-scale synchronization tasks are performed by introducing the use of a Druid connection pool. Attached Figure Description

[0033] To more clearly illustrate the technical solution of the present invention, the drawings used in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0034] Figure 1 This is a flowchart illustrating the method for synchronizing Informix and MySQL databases based on XXL-JOB, provided in the first embodiment of the present invention.

[0035] Figure 2 This is a screenshot of the interface for setting task parameters in XXL-JOB.

[0036] Figure 3 This is a schematic diagram of the device for synchronizing Informix and MySQL databases based on XXL-JOB, provided in the second embodiment of the present invention. Detailed Implementation

[0037] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0038] To better understand the technical solution of the present invention, the embodiments of the present invention will be described in detail below with reference to the accompanying drawings.

[0039] It should be understood that the described embodiments are merely some, not all, of the embodiments of the present invention. All other embodiments obtained by those skilled in the art based on the embodiments of the present invention without inventive effort are within the scope of protection of the present invention.

[0040] Please see Figure 1 The first embodiment of the present invention provides a method for synchronizing Informix and MySQL databases based on XXL-JOB. This method can be executed by a device (hereinafter referred to as the synchronization device) that synchronizes Informix and MySQL databases based on XXL-JOB. Specifically, it is executed by one or more processors within the synchronization device to achieve the following steps:

[0041] S101, after detecting the task that starts XXL-JOB, read the task parameters of the task; wherein, the task parameters include the predetermined content of the data to be synchronized.

[0042] In this embodiment, the synchronization device may be a smart mobile terminal, tablet computer, desktop computer, laptop computer, workstation or server, or other device with data storage and data processing capabilities. This invention does not impose any specific limitations.

[0043] In this embodiment, before data synchronization, task parameters need to be set in XXL-JOB. These task parameters include at least the predetermined content of the data to be synchronized.

[0044] like Figure 2 As shown, in one embodiment, the predetermined content is 1000010013, which means that all data containing 1000010013 will be synchronized.

[0045] In addition, the task parameters may also include executor, scheduling type, running strategy, routing strategy and blocking handling strategy, etc., which are set according to actual needs, and the present invention does not make specific limitations.

[0046] Furthermore, in some preferred embodiments, the task can be a timed task that starts automatically according to a set time node. This allows for automatic, timed data synchronization without requiring manual initiation of the synchronization task each time, reducing manpower investment and improving work efficiency.

[0047] S102, retrieve data with the predetermined content from the pre-configured Informix database, process the partial column names, parameters, and content of each data entry, and store the processed data in the specified first HashMap with a unique value as the key.

[0048] by Figure 2 For example, first, extract the data containing the given content 1000010013. Map each column name in the extracted data to the column names in the target table. Based on the mapping results, generate a specified Java object for each data entry and assign it a value, using the data's unique code as its ID. Then, store the processed data in a first specified HashMap (e.g., named informixMap) with the unique value as the key. This simultaneously performs deduplication, preventing duplicate data pollution.

[0049] S103: Retrieve all data with the given content from MySQL at once, and store them in the specified second HashMap with a unique value as the key.

[0050] Similarly, retrieve all data containing the predetermined content 1000010013 from MySQL at once, and store it in a second specified HashMap (e.g., named mysqlMap) with a unique value as the key.

[0051] S104: Traverse the first HashMap and query the corresponding data in the second HashMap using the unique value. If the corresponding data can be found, generate an MD5 value using all the contents of the data and compare it with the MD5 value stored in MySQL. If the values ​​are different, mark the data as an update value and put it into the update pool. If the corresponding data cannot be found, put the data into the new data pool.

[0052] In this embodiment, a HashMap is used as a cache pool during the data synchronization process. Compared with the existing single-item comparison and update method, the efficiency of data synchronization is greatly improved by batch update.

[0053] S105: Obtain the global Druid connection pool, acquire a MySQL connection from the Druid connection pool, and sequentially update the contents of the update pool and the new pool to MySQL in batches using transactions.

[0054] Specifically, in this embodiment, a Druid connection pool is created at program startup using the singleton design pattern. This pool manages multiple database connections. During subsequent data synchronization, only the database connections in the pool need to be activated. After synchronization, the database connection resources are released and returned to the Druid connection pool for repeated use by subsequent programs. Compared to the previous method of creating a database connection every time data is updated, this embodiment only requires creating the Druid connection pool and a fixed number of database connection threads once at program startup. This avoids repeatedly creating database connections during program execution, reducing wasted time and resources during connection processes, thereby improving data synchronization efficiency.

[0055] In summary, this embodiment uses a HashMap to pre-collect and categorize all synchronized data before performing batch updates. Compared to the original method of connecting to the database to update data for each piece of data read, this embodiment reduces the number of database connection requests through batch updates, thereby reducing database connection time, significantly shortening data synchronization processing time, and improving data synchronization efficiency.

[0056] Furthermore, this embodiment reduces the database load when large-scale synchronization tasks are performed by introducing the use of a Druid connection pool.

[0057] Please see Figure 3 The second embodiment of the present invention also provides an apparatus for synchronizing Informix and MySQL databases based on XXL-JOB, comprising:

[0058] The task parameter reading unit 210 is used to read the task parameters of the task after detecting that an XXL-JOB has started; wherein, the task parameters include predetermined content of the data to be synchronized;

[0059] The first storage unit 220 is used to retrieve data with the predetermined content from the pre-configured Informix database, process part of the column name, parameters and content of each data, and store the processed data in the specified first HashMap with a unique value as the key.

[0060] The second storage unit 230 is used to retrieve all data with the predetermined content from MySQL at once, and store them in the specified second HashMap with a unique value as the key;

[0061] The query unit 240 is used to traverse the first HashMap and query the corresponding data in the second HashMap with the unique value. If the corresponding data can be found, the MD5 value generated by the contents of the data is compared with the MD5 value stored in MySQL. If the values ​​are different, the data is marked as an update value and put into the update pool. If the corresponding data cannot be found, the data is put into the new data pool.

[0062] Update unit 250 is used to obtain the global Druid connection pool, obtain a MySQL connection from the Druid connection pool, and sequentially update the contents of the update pool and the new pool to MySQL in batches using transactions.

[0063] Preferably, when processing partial column names, parameters, and content of each data entry:

[0064] Map each column name in the data to the column name in the target table one by one, generate a specified Java object for each data record based on the mapping result and assign a value; where the unique code of the data is used as the ID identifier.

[0065] Preferably, it further includes:

[0066] The connection pool creation unit is used to create a global Druid connection pool at startup. The Druid connection pool manages multiple database connections. During data synchronization, the database connections in the Druid connection pool are enabled. After synchronization is completed, the database connection resources are released and returned to the Druid connection pool.

[0067] This invention also provides a device for synchronizing Informix and MySQL databases based on XXL-JOB, which includes a memory and a processor. The memory stores a computer program that can be executed by the processor to implement the method for synchronizing Informix and MySQL databases based on XXL-JOB as described above.

[0068] This invention also provides a computer-readable storage medium storing a computer program that can be executed by a processor of the device where the computer-readable storage medium is located, to implement the method described above for synchronizing Informix and MySQL databases based on XXL-JOB.

[0069] For example, the various devices and process steps described above can be implemented by a computer program, which can be divided into one or more units, which are stored in the memory and executed by the processor to complete the present invention.

[0070] The processor referred to can be a Central Processing Unit (CPU), or other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. A general-purpose processor can be a microprocessor or any conventional processor.

[0071] The memory can be used to store the computer programs and / or modules. The processor implements various functions of the present invention by running or executing the computer programs and / or modules stored in the memory and by calling data stored in the memory. The memory may mainly include a program storage area and a data storage area. The program storage area may store the operating system, at least one application program required for a function (such as sound playback function, image playback function, etc.), etc.; the data storage area may store data created according to the use of the mobile phone (such as audio data, phonebook, etc.). In addition, the memory may include high-speed random access memory, and may also include non-volatile memory, such as hard disk, memory, plug-in hard disk, smart media card (SMC), secure digital (SD) card, flash card, at least one disk storage device, flash memory device, or other volatile solid-state storage device.

[0072] If the unit integrated into the electronic device or printer is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, all or part of the processes in the methods of the above embodiments can also be implemented by a computer program instructing related hardware. The computer program can be stored in a computer-readable storage medium, and when executed by a processor, it can implement the steps of the various method embodiments described above. The computer program includes computer program code, which can be in the form of source code, object code, executable files, or certain intermediate forms. The computer-readable medium can include: any entity or device capable of carrying the computer program code, recording media, USB flash drives, portable hard drives, magnetic disks, optical disks, computer memory, read-only memory (ROM), random access memory (RAM), electrical carrier signals, telecommunication signals, and software distribution media, etc. It should be noted that the content included in the computer-readable medium can be appropriately added or removed according to the requirements of legislation and patent practice in the jurisdiction. For example, in some jurisdictions, according to legislation and patent practice, computer-readable media do not include electrical carrier signals and telecommunication signals.

[0073] It should be noted that the device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. Furthermore, in the accompanying drawings of the device embodiments provided by this invention, the connection relationships between modules indicate that they have communication connections, which can be specifically implemented as one or more communication buses or signal lines. Those skilled in the art can understand and implement this without any creative effort.

[0074] The above description represents the preferred embodiments of the present invention. It should be noted that those skilled in the art can make various improvements and modifications without departing from the principles of the present invention, and these improvements and modifications are also considered to be within the scope of protection of the present invention.

Claims

1. A method for realizing synchronization between informix and MySQL databases based on XXL-JOB, characterized in that, include: After detecting a task that has started XXL-JOB, the task parameters of the task are read; wherein, the task parameters include the predetermined content of the data to be synchronized; Retrieve data with the given content from the pre-configured Informix database, process part of the column names, parameters and content of each data, and store the processed data in the specified first HashMap with a unique value as the key; Retrieve all data containing the given content from MySQL at once, and store them in the specified second HashMap using a unique value as the key; Traverse the first HashMap and query the corresponding data in the second HashMap for the unique value. If the corresponding data is found, generate an MD5 value for all the contents of the data and compare it with the MD5 value stored in MySQL. If the values ​​are different, mark the data as an update value and put it into the update pool. If the corresponding data is not found, put the data into the new data pool. Obtain the global Druid connection pool, acquire MySQL connections from the Druid connection pool, and sequentially update the contents of the update pool and the new pool to MySQL in batches using transactions. 2.The method for realizing synchronization between informix and MySQL databases based on XXL-JOB according to claim 1, wherein, The task parameters also include at least one of the following: executor, scheduling type, running strategy, routing strategy, and blocking handling strategy. 3.The method for realizing synchronization between informix and MySQL databases based on XXL-JOB of claim 1, characterized in that, When processing partial column names, parameters, and content of each data entry: Map each column name in the data to the column name in the target table one by one, generate a specified Java object for each data record based on the mapping result and assign a value; where the unique code of the data is used as the ID identifier.

4. The method for realizing synchronization between informix and MySQL databases based on XXL-JOB according to claim 1, characterized in that, Also includes: A global Druid connection pool is created at startup; the Druid connection pool manages multiple database connections. During data synchronization, the database connections in the Druid connection pool are enabled. After synchronization is completed, the database connection resources are released and returned to the Druid connection pool.

5. The method for synchronizing Informix and MySQL databases based on XXL-JOB according to claim 1, characterized in that, The task is a timed task, which starts automatically according to the set time nodes.

6. A device for synchronizing Informix and MySQL databases based on XXL-JOB, characterized in that, include: The task parameter reading unit is used to read the task parameters of the task after detecting that a task has started XXL-JOB; wherein, the task parameters include the predetermined content of the data to be synchronized; The first storage unit is used to retrieve data with the predetermined content from the pre-configured Informix database, process part of the column names, parameters and content of each data, and store the processed data in the specified first HashMap with a unique value as the key. The second storage unit is used to retrieve all data with the given content from MySQL at once and store it in the specified second HashMap with a unique value as the key; The query unit is used to traverse the first HashMap and query the corresponding data in the second HashMap with the unique value. If the corresponding data can be found, the MD5 value generated from all the contents of the data is compared with the MD5 value stored in MySQL. If the values ​​are different, the data is marked as an update value and put into the update pool. If the corresponding data cannot be found, the data is put into the new data pool. The update unit is used to obtain the global Druid connection pool, acquire a MySQL connection from the Druid connection pool, and sequentially update the contents of the update pool and the new pool to MySQL in batches using transactions.

7. The apparatus for synchronizing Informix and MySQL databases based on XXL-JOB according to claim 6, characterized in that, When processing partial column names, parameters, and content of each data entry: Map each column name in the data to the column name in the target table one by one, generate a specified Java object for each data record based on the mapping result and assign a value; where the unique code of the data is used as the ID identifier.

8. The apparatus for synchronizing Informix and MySQL databases based on XXL-JOB according to claim 6, characterized in that, Also includes: The connection pool creation unit is used to create a global Druid connection pool at startup. The Druid connection pool manages multiple database connections. During data synchronization, the database connections in the Druid connection pool are enabled. After synchronization is completed, the database connection resources are released and returned to the Druid connection pool.

9. A device for synchronizing Informix and MySQL databases based on XXL-JOB, characterized in that, The system includes a memory and a processor. The memory stores a computer program that can be executed by the processor to implement the method for synchronizing Informix and MySQL databases based on XXL-JOB as described in any one of claims 1 to 5.

10. A computer-readable storage medium, characterized in that, The device contains a computer program that can be executed by a processor of the device where the computer-readable storage medium is located, to implement the method for synchronizing Informix and MySQL databases based on XXL-JOB as described in any one of claims 1 to 5.

Images