Heterogeneous database self-adaptive data synchronization method, device and equipment and medium

Abstract

The invention provides a heterogeneous database self-adaptive data synchronization method and device, equipment and a medium, and the method comprises the steps: obtaining and recognizing a database connection configuration file of an application system to determine database types of a source database and a target database, and then configuring corresponding data sources for the source database and the target database, the method comprises the steps of configuring a source database data source, selecting a corresponding structured query language from a preset structured query language library, executing query operation in a source database according to the configured source database data source and the corresponding structured query language to retrieve to-be-synchronized data, and inserting the retrieved to-be-synchronized data into a target database, the self-adaptive data synchronization among the heterogeneous databases is realized; according to the method, corresponding data sources and structured query language rules are configured based on different database categories, large-scale modification of application codes is avoided, the flexibility of data processing is enhanced, and the accuracy of data conversion and migration is ensured.

Description

Technical Field

[0001] This application relates to the field of database technology, and particularly to a data synchronization method, device, equipment and medium for self-adapting heterogeneous databases. Background Art

[0002] With the rapid development of information technology and the deepening of enterprise digital transformation, it has become normal for enterprise data to be stored in multiple types of database environments. Such heterogeneous database environments not only include widely used relational databases (such as MySQL, Oracle, SQL Server, DM, KingbaseES, etc.), but may also cover non-relational databases (such as NoSQL databases) and other data storage solutions for specific purposes.

[0003] However, there are significant technical differences between different types of database systems, which pose great challenges to application systems during database migration. Traditional migration methods usually require the development team to make a large number of modifications to the application code to adapt to the specific requirements of the target database. This not only increases the development cost and extends the migration cycle, but may also affect the stability of the system due to the introduction of new errors. Especially for those enterprises that need to support multiple database environments simultaneously (such as the hybrid cloud scenario), maintaining such a system will become extremely complex and costly. Summary of the Invention

[0004] In view of the above-mentioned disadvantages of the prior art, this application provides a data synchronization method, device, equipment and medium for self-adapting heterogeneous databases to solve the above technical problems.

[0005] This application provides a data synchronization method for self-adapting heterogeneous databases, and the method includes: obtaining a database connection configuration file of an application system, where the database connection configuration file configures the connection relationships between the application system and a source database and a target database respectively; identifying the database connection configuration file to determine the database types of the source database and the target database based on the connection relationships; configuring corresponding data sources for the source database and the target database respectively based on the identified database types, and selecting corresponding structured query languages from a preset structured query language library; retrieving data to be synchronized in the source database according to the configured data source of the source database and the corresponding structured query language; inserting the retrieved data to be synchronized into the target database according to the configured data source of the target database and its corresponding structured query language.

[0006] In one embodiment of the present application, selecting the corresponding Structured Query Language (SQL) from a preset SQL library includes: constructing an SQL template library containing various standard SQL statements, and setting corresponding SQL conversion rules for each type of database; according to the specific types of the identified source database and target database, selecting the corresponding SQL conversion rule; based on the selected SQL conversion rule, converting the standard SQL statements in the SQL template library into converted SQL statements that match the types of the source database and target database.

[0007] In one embodiment of the present application, performing a query operation in the source database to retrieve data to be synchronized includes: generating a standard SQL query statement based on the data synchronization requirement; converting the standard SQL statement into a converted SQL statement that matches the source database; based on the converted SQL statement, locating the data range of the data to be synchronized in the source database, and extracting the data within the data range to generate an original data set for representing the data to be synchronized.

[0008] In one embodiment of the present application, after generating the original data set for representing the data to be synchronized, it further includes: identifying the fields in the original data set to obtain the original data types and original values of the data to be synchronized; based on the original data types, querying in a preset data type mapping table to obtain the corresponding target data types in the target database, where the data type mapping table includes the mapping relationships between the data types in different types of databases; converting the original data types into matching target data types, and assigning the original values to the corresponding target data types to generate a target synchronization data table.

[0009] In one embodiment of the present application, inserting the retrieved data to be synchronized into the target database includes: generating a standard structured insert statement based on the target synchronization data table, and converting the standard structured insert statement into a converted structured insert statement that matches the target database; dividing the target synchronization data table into multiple data batches according to the converted structured insert statement, and sequentially inserting them into the target database.

[0010] In one embodiment of the present application, after inserting the retrieved data to be synchronized into the target database, it further includes: real-time monitoring the connection status between the application system and the source database and the target database respectively; when it is detected that any connection status has a connection exception, re-establishing the connection until the connection is successful, where the connection exception includes at least connection failure and connection timeout; updating the data source and database connection parameters based on the new connection information to generate a new connection configuration file.

[0011] In one embodiment of the present application, the data synchronization method for self - adapting heterogeneous databases further includes: monitoring exceptions generated during the data synchronization process, where the exceptions at least include database connection exceptions, data retrieval exceptions, and data insertion exceptions; converting the monitored exceptions into standard exception types, and determining an exception handling solution corresponding to the standard exception type based on a preset exception handling policy, where the exception handling solution at least includes retry, skip, rollback, and alarm.

[0012] The present application provides a data synchronization device for self - adapting heterogeneous databases, and the device includes: a file acquisition module for acquiring a database connection configuration file of an application system, where the database connection configuration file configures the connection relationships between the application system and a source database and a target database respectively; a database category identification module for identifying the database connection configuration file to determine the database categories of the source database and the target database based on the connection relationships; a database information matching module for configuring corresponding data sources for the source database and the target database respectively based on the identified database categories, and selecting corresponding structured query languages from a preset structured query language library; a data to be synchronized retrieval module for retrieving data to be synchronized in the source database according to the configured data source of the source database and the corresponding structured query language; and a data to be synchronized insertion module for inserting the retrieved data to be synchronized into the target database according to the configured data source of the target database and its corresponding structured query language.

[0013] The present application provides an electronic device, including a processor, a memory, and a communication bus; the communication bus is used to connect the processor and the memory; the processor is used to execute a computer program stored in the memory to implement the data synchronization method for self - adapting heterogeneous databases as described above.

[0014] The present application provides a computer - readable storage medium, on which a computer program is stored, and the computer program is used to cause a computer to execute the data synchronization method for self - adapting heterogeneous databases as described above.

[0015] Advantages of the present application: A data synchronization method, apparatus, device, and medium for self - adapting heterogeneous databases are proposed in the present application. By automatically identifying the database type and performing corresponding configurations, the method reduces the need for manual intervention, lowers the operation complexity, and also avoids the possibility of human errors, enhancing the accuracy and efficiency of data migration; selects corresponding SQL statements from a preset structured query language library, and performs a query operation in the source database to retrieve the data to be synchronized according to the configured source database data source and its corresponding SQL. By using predefined and optimized SQL statements, the efficiency and accuracy of the query operation are ensured, enabling the data synchronization method to adapt to the specific requirements of different databases, improving the query performance, and simplifying the workflow of developers; inserts the retrieved data to be synchronized into the target database according to the configured target database data source and its corresponding SQL, achieving seamless data synchronization between heterogeneous databases without the need for large - scale modification of existing application code. This not only shortens the migration cycle but also ensures the stability of the system during the migration process, reducing the risk of downtime and service interruption; in addition, the entire process covers obtaining the database connection configuration file, identifying the database category, configuring the data source, selecting SQL statements, and finally performing the data synchronization operation, which can automatically handle compatibility issues between different databases, greatly simplifying cross - platform data management and maintenance work, and reducing the operation and maintenance costs and complexity.

[0016] It should be understood that the above general description and the following detailed description are merely exemplary and explanatory, and do not limit the present application. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] The accompanying drawings herein are incorporated into the specification and form a part of the specification, showing embodiments consistent with the present application, and are used together with the specification to explain the principles of the present application. Obviously, the drawings in the following description are only some embodiments of the present application, and those of ordinary skill in the art can obtain other drawings based on these drawings without creative efforts. In the drawings:

[0018] Figure 1 is a schematic diagram of the implementation environment of the data synchronization method for self - adapting heterogeneous databases shown in an exemplary embodiment of the present application;

[0019] Figure 2 is a flowchart of the implementation steps of the data synchronization method for self - adapting heterogeneous databases shown in an exemplary embodiment of the present application;

[0020] Figure 3 is a schematic diagram of the application initialization process of the data synchronization method for self - adapting heterogeneous databases shown in an exemplary embodiment of the present application;

[0021] Figure 4 It is a schematic diagram of the SQL execution process of the data synchronization method for heterogeneous database self - adaptation shown in an exemplary embodiment of the present application;

[0022] Figure 5 It is a block diagram of the data synchronization device for heterogeneous database self - adaptation shown in an exemplary embodiment of the present application;

[0023] Figure 6 It shows a schematic diagram of the structure of a computer system of an electronic device suitable for implementing the embodiments of the present application. Detailed implementation manners

[0024] The following will describe the embodiments of the present application with reference to the drawings and preferred embodiments. Those skilled in the art can easily understand other advantages and effects of the present application from the content disclosed in this specification. The present application can also be implemented or applied through other different specific implementation manners. Various details in this specification can also be modified or changed based on different viewpoints and applications without departing from the spirit of the present application. It should be understood that the preferred embodiments are only for explaining the present application, rather than for limiting the protection scope of the present application.

[0025] It should be noted that the diagrams provided in the following embodiments only illustrate the basic concept of the present application in a schematic manner. Therefore, only the components related to the present application are shown in the diagrams, rather than being drawn according to the number, shape, and size of the components in actual implementation. The type, quantity, and ratio of each component in actual implementation can be arbitrarily changed, and the component layout type may also be more complex.

[0026] In the following description, a large number of details are explored to provide a more thorough explanation of the embodiments of the present application. However, it is obvious to those skilled in the art that the embodiments of the present application can be implemented without these specific details. In other embodiments, well - known structures and devices are shown in the form of block diagrams rather than in detail to avoid making the embodiments of the present application difficult to understand.

[0027] First of all, it should be noted that SQL (Structured Query Language) is a standard language for managing and operating relational databases. It allows users to perform various database operations, such as querying data, inserting new records, updating existing records, and deleting records, etc.

[0028] In the embodiments of this application, MySQL is usually used as the source database and PostgreSQL as the target database as an example. However, in the actual application process, the method proposed in this application is applicable to other multi-type databases, including but not limited to MySQL, Oracle, SQL Server, PostgreSQL, DB2, SQLite, DM, and KingbaseES.

[0029] Figure 1 It is a schematic diagram of the implementation environment of the heterogeneous database self-adapting data synchronization method shown in an exemplary embodiment of this application.

[0030] As Figure 1 shown, the implementation environment of the heterogeneous database self-adapting data synchronization method includes a data collection module 101 and a computer device 102.

[0031] Among them, the collection module 101 is responsible for obtaining and identifying the database connection configuration files of the application system. By analyzing these configuration files, the module can automatically determine the types of the source database and the target database, thereby providing the necessary information for subsequent steps. Specifically, this module will first parse the database connection configuration files to identify the source database and the target database that need to perform synchronization operations. Based on the identified database types, appropriate SQL statements are selected from the preset structured query language library to ensure the use of the most appropriate query language for different database systems. In addition, the data collection module 101 is also responsible for performing query operations in the source database to retrieve the data set to be synchronized.

[0032] The computer device 102, as the hardware platform carrying the above functions, not only supports the operation of the data collection module 101 but also is responsible for processing data transmission and conversion. After receiving the data to be synchronized provided by the data collection module 101, the computing device adjusts or converts the format of these data according to the requirements of the target database and then inserts them into the target database. To achieve this process, the computer device 102 needs to have sufficient computing power and storage resources to efficiently complete data processing tasks. At the same time, considering data security and integrity, the device also needs to integrate corresponding protection measures, such as data encryption, backup and recovery mechanisms, etc.

[0033] In summary, through the collaborative work of the data collection module 101 and the computer device 102, the heterogeneous database self-adapting data synchronization method is realized, effectively solving the data synchronization challenges between different types of databases. This method not only improves data processing efficiency but also enhances the flexibility and adaptability of the system.

[0034] Figure 2 It is a flowchart of the implementation steps of the heterogeneous database self-adapting data synchronization method shown in an exemplary embodiment of this application.

[0035] As Figure 2 shown, in an exemplary embodiment, the heterogeneous database self - adapting data synchronization method at least includes steps S210 to S250, which are introduced in detail as follows:

[0036] Step S210, obtain the database connection configuration file of the application system, and the database connection configuration file configures the connection relationships between the application system and the source database and the target database respectively.

[0037] In an embodiment of the present application, take an example that an application system needs to synchronize data from a MySQL database (as the source database) to a PostgreSQL database (as the target database), and the database connection configuration file stores necessary information in JSON format.

[0038] In view of the fact that the configuration file can accurately represent the connection relationships between the application system and the source database and the target database, and each configuration item such as host, port, database, etc. directly corresponds to the specific parameters of the database connection. Therefore, in this embodiment, the application system directly reads and parses the configuration file, and establishes connections with the source database and the target database based on the information in the configuration file. It should be understood that during the process of reading and parsing the connection configuration file, the application system not only successfully obtains the connection configuration information of the source database and the target database, but also establishes actual database connections based on this information.

[0039] It can be understood that the method proposed based on the above - mentioned embodiment ensures that the application system can interact with different databases in the expected manner, improves flexibility and maintainability, and makes it simpler and more efficient to support multiple types of databases.

[0040] Step S220, identify the database connection configuration file to determine the database types of the source database and the target database based on their connection relationships.

[0041] In an embodiment of the present application, combining the above - mentioned embodiment, take an example that there is a configuration file in JSON format, which contains the connection information between the application system and the source database and the target database. In the configuration file, the "source" part represents the connection information of the source database, and the "target" part represents the connection information of the target database, and each part contains a type field used to identify the specific database type.

[0042] First, read and parse the configuration file, including opening the file, reading the content, and converting the content into a processable data structure (such as a dictionary or an object), and extract the relevant configuration information of the source database and the target database during this process. Then, identify the database categories of the source database and the target database according to the type field in the configuration file. Specifically, it includes: reading the information in the source part from the configuration file. Taking the type field value as mysql as an example, it indicates that the source database is a MySQL database. Similarly, reading the information in the target part from the configuration file. Taking the type field value as postgresql as an example, the target database is a PostgreSQL database.

[0043] Step S230, based on the identified database categories, configure corresponding data sources for the source database and the target database respectively, and select the corresponding Structured Query Language from a preset Structured Query Language library.

[0044] In an embodiment of the present application, selecting the corresponding Structured Query Language from a preset Structured Query Language library includes: constructing a Structured Query Language template library containing multiple standard Structured Query Statements, and setting corresponding Structured Query Language conversion rules for each category of database respectively; according to the specific types of the identified source database and target database, select the corresponding Structured Query Language conversion rules; based on the selected Structured Query Language conversion rules, convert the standard Structured Query Statements in the Structured Query Language template library into conversion Structured Query Statements that match the types of the source database and the target database.

[0045] In a specific embodiment of the present application, first, construct a template library containing multiple standard Structured Query Statements (SQL). This template is general and applicable to different database types, but specific conversion rules are required to adapt to specific database systems. To support different types of databases, specific SQL conversion rules are further defined for each database. This conversion rule is used to convert the standard SQL template into an SQL statement suitable for a specific database system. The following are some examples:

[0046] MySQL database:

[0047] Time format: DATE_FORMAT({column}, '%Y-%m-%d')

[0048] Paging query: LIMIT {offset}, {limit}

[0049] PostgreSQL database:

[0050] Time format: TO_CHAR({column}, 'YYYY-MM-DD')

[0051] Pagination query: OFFSET {offset} LIMIT {limit}

[0052] SQL Server database:

[0053] Time format: FORMAT({column}, 'yyyy-MM-dd')

[0054] Pagination query: OFFSET {offset} ROWS FETCH NEXT {limit} ROWS ONLY

[0055] Then, taking the source database as MySQL and the target database as PostgreSQL as an example, further select appropriate conversion rules according to the database types of the source database and the target database. Specifically, the source database (MySQL) uses the time format and pagination query rules of MySQL; the target database (PostgreSQL) uses the time format and pagination query rules of PostgreSQL.

[0056] Finally, convert the specific query statement from the standard template to a query statement suitable for a specific database type. Taking the original standard query statement as: "SELECT * FROM users WHERE created_at > '2023-01-01' ORDER BY id LIMIT 10 OFFSET 20" as an example;

[0057] Convert it to a MySQL query statement as:

[0058] "SELECT * FROM users WHERE DATE_FORMAT(created_at, '%Y-%m-%d') > '2023-01-01' ORDER BY id LIMIT 20, 10";

[0059] Convert it to a PostgreSQL query statement as:

[0060] "SELECT * FROM users WHERE TO_CHAR(created_at, 'YYYY-MM-DD') > '2023-01-01' ORDER BY id OFFSET 20 LIMIT 10".

[0061] It can be understood that, according to the above embodiments, a standard SQL query template is converted into specific query statements suitable for the source database (MySQL) and the target database (PostgreSQL). This process not only ensures the correctness and compatibility of the query statements in different database environments, but also improves the efficiency and reliability of data synchronization operations. This conversion mechanism enables the application system to flexibly handle various database types and support complex cross-database data synchronization requirements; moreover, by dynamically selecting and applying conversion rules, the application system can automatically adjust the query statements at runtime, thereby achieving seamless data synchronization between heterogeneous databases.

[0062] It should be emphasized that this embodiment demonstrates how to construct a structured query language template library and set corresponding conversion rules for different types of databases. By identifying the specific types of the source database and the target database, appropriate conversion rules can be dynamically selected to convert the standard query statement into a query statement adapted to a specific database. This method significantly improves the data management and synchronization capabilities in a multi-database environment and enhances the flexibility and adaptability of the system.

[0063] Step S240, retrieve the data to be synchronized in the source database according to the configured data source of the source database and the corresponding structured query language.

[0064] In an embodiment of the present application, performing a query operation in the source database to retrieve the data to be synchronized includes: generating a standard structured query language query statement based on the data synchronization requirements; converting the standard structured query statement into a converted structured query statement that matches the source database; based on the converted structured query statement, locating the data range of the data to be synchronized in the source database and extracting the data within the data range to generate an original data set for representing the data to be synchronized.

[0065] In a specific embodiment of the present application, taking the example of synchronizing user information from a MySQL database (as the source database) to another PostgreSQL database (as the target database), and the specific requirement is to synchronize all user records created in the past month.

[0066] First, a standard SQL query statement is generated according to the data synchronization requirements. This query statement is a general statement applicable to different database types and needs to be converted through specific conversion rules to adapt to a specific database system. Since the source database in this embodiment is MySQL, the standard query statement needs to be converted into a query statement suitable for MySQL. Therefore, based on the conversion rules defined in the foregoing embodiments, it is converted to obtain the following converted SQL query statement:

[0067] SELECT id,username,email,DATE_FORMAT(created_at,'%Y-%m-%d')AScreated_at

[0068] FROM users

[0069] WHERE created_at>'2025-02-05'

[0070] ORDER BY created_at;

[0071] It should be emphasized that in this converted query statement, the MySQL-specific DATE_FORMAT function is used to format the date field to ensure compliance with MySQL's syntax requirements.

[0072] Then, execute the converted query statement in the source database (MySQL) to locate and extract the data range of the data to be synchronized. The specific steps include using the connection information in the previously parsed configuration file to establish a connection to the source database; after the connection is successful, execute the converted query statement to obtain the data records that meet the conditions; extract the query results to form an original data set. For example, assume the query results are as follows:

[0073] Table 1

[0074] id username email created_at 101 john_doe john@example.com 2025-02-10 102 jane_doe jane@example.com 2025-02-15

[0075] Finally, organize the extracted data into an original data set for subsequent processing and synchronization to the target database.

[0076] It can be understood that through the method proposed in the above embodiments, a standard SQL query statement is generated based on the data synchronization requirements, the standard query statement is converted into a query statement that matches the source database, the data range of the data to be synchronized is located and extracted in the source database, and an original data set is generated, providing a basis for subsequent data synchronization operations. This method not only improves the accuracy and efficiency of data synchronization operations, but also enhances the flexibility and adaptability of the system, enabling it to support various types of database environments. In addition, by dynamically selecting and applying conversion rules, the system can automatically adjust the query statement at runtime, thus achieving seamless data synchronization between heterogeneous databases.

[0077] In one embodiment of the present application, after generating the original data set for characterizing the data to be synchronized, the following steps are further included: identifying the fields in the original data set to obtain the original data type and the original value of the data to be synchronized; based on the original data type, querying in a preset data type mapping table to obtain the corresponding target data type in the target database, where the data type mapping table includes the mapping relationships between the data types in different types of databases; converting the original data type into a matching target data type and assigning the original value to the corresponding target data type to generate a target synchronization data table.

[0078] In a specific embodiment of the present application, after extracting the following original data set from the source database (MySQL), the original data type and value of each field in the original data set are further identified, and some examples are shown as follows:

[0079] id: Integer (INTEGER)

[0080] username: String (VARCHAR)

[0081] email: String (VARCHAR)

[0082] created_at: Date (DATE)

[0083] After that, in order to convert the original data type into the corresponding data type in the target database (PostgreSQL), it is converted according to the preset data type mapping table. The following is an example mapping table for MySQL to PostgreSQL:

[0084] Table II

[0085] MySQL data type PostgreSQL data type INTEGER INTEGER VARCHAR VARCHAR DATE DATE

[0086] Based on the mapping table shown in Table II, the original data type is converted into a target data type that matches the target database, as shown specifically as follows:

[0087] id: The original type is INTEGER and remains unchanged in PostgreSQL, still being INTEGER.

[0088] username: The original type is VARCHAR and remains unchanged in PostgreSQL, still being VARCHAR.

[0089] email: The original type is VARCHAR and remains unchanged in PostgreSQL, still being VARCHAR.

[0090] created_at: The original type is DATE and remains unchanged as DATE in PostgreSQL.

[0091] It should be emphasized that although all data types are consistent in the above embodiments, in actual applications, the data types between different databases may be different, so appropriate conversions are required.

[0092] Finally, the converted data type and the original value are combined to generate the target synchronization data table. Specifically, it includes: creating the corresponding table structure in the target database (PostgreSQL); converting each record in the original dataset to the target data type and inserting it into the target database.

[0093] It can be understood that in the method proposed in this embodiment, each field in the original dataset is identified to determine the original data type and value of each field; using the preset data type mapping table, the corresponding target data type in the target database is found; then, the original data type is converted to the target data type that matches the target database, and finally, the converted data type and the original value are combined and inserted into the target database. The specific steps include identifying each field in the original dataset, querying the data type mapping table, converting the original data type, and generating the target synchronization data table. This method not only improves the accuracy and efficiency of data synchronization operations but also enhances the flexibility and adaptability of the system, enabling it to support multiple types of database environments. By dynamically selecting and applying data type mapping rules, the application system can automatically adjust the data type at runtime, thus achieving seamless data synchronization between heterogeneous databases.

[0094] Step S250, insert the retrieved data to be synchronized into the target database according to the configured target database data source and its corresponding structured query language.

[0095] In an embodiment of the present application, inserting the retrieved data to be synchronized into the target database includes: based on the standard structured insertion statement of the target synchronization data table, and converting the standard structured insertion statement into a converted structured insertion statement that matches the target database; dividing the target synchronization data table into multiple data batches according to the converted structured insertion statement and inserting them into the target database in sequence.

[0096] In a specific embodiment of the present application, after generating the target synchronization data table (original data set), a standard SQL insert statement template is generated according to the data synchronization requirements and converted into an insert statement suitable for PostgreSQL (target database). Considering improving the insertion efficiency and avoiding performance issues caused by inserting a large amount of data at one time, the target synchronization data table is divided into multiple data batches and inserted into the target database sequentially, specifically including: using the connection information in the previously parsed configuration file to establish a connection with the target database, and after the connection is successful, executing the insert statements of each batch in sequence to insert the data into the target database.

[0097] It should be understood that in the method proposed in this embodiment, by creating a general SQL insert statement template, converting its standard insert statement into an insert statement suitable for PostgreSQL, and then dividing the target synchronization data table into multiple data batches and inserting them into the target database sequentially. This method not only improves the accuracy and efficiency of data insertion operations, but also enhances the flexibility and adaptability of the system, enabling it to support various types of database environments. By dynamically selecting and applying conversion rules, the system can automatically adjust the insert statement at runtime, thus achieving seamless data synchronization between heterogeneous databases.

[0098] In an embodiment of the present application, after inserting the retrieved data to be synchronized into the target database, it further includes: real-time monitoring of the connection status between the application system and the source database and the target database respectively; when it is detected that any connection status has a connection anomaly, automatically attempting to re-establish the connection until the connection is successful, and the connection anomaly includes at least connection failure and connection timeout; updating the data source and database connection parameters based on the new connection information to generate a new connection configuration file.

[0099] In a specific embodiment of the present application, in order to ensure high availability and reliability during the data synchronization process, it is necessary to real-time monitor the connection status between the application system and the source database and the target database. Taking the monitoring mechanism as "regularly checking the status of the database connection (for example, once a minute), monitoring changes in the connection status, and recording any abnormal situations" as an example.

[0100] First, detect abnormal situations by listening to the exceptions thrown by the database driver. The abnormal situations include, but are not limited to, connection failures caused by the inability to establish an initial connection to the database, and connection timeouts caused by the connection request not receiving a response within the set time. When a connection exception is detected, the system needs to automatically attempt to re-establish the connection until the connection is successful. For example, after detecting a connection exception, wait for a period of time (such as 5 seconds), and then attempt to reconnect; if the reconnection fails, continue to wait and retry until successful or the maximum number of retries is reached. Once the connection is re-established, the data source and database connection parameters need to be updated based on the new connection information, and a new connection configuration file needs to be generated for subsequent use. The specific process of generating the new configuration file includes: storing the new connection information in a configuration object in memory and writing the new connection information back to the configuration file.

[0101] It should be understood that, based on the method proposed in this embodiment, through real-time monitoring and automatic reconnection mechanisms, the data synchronization interruption time caused by database connection problems is reduced; dynamically updating the configuration file according to the new connection information ensures the flexibility and adaptability of the system. Therefore, the method proposed in this embodiment not only improves the accuracy and efficiency of data synchronization operations, but also enhances the flexibility and adaptability of the system, enabling it to support various types of database environments. In addition, through dynamic selection and application of monitoring and reconnection mechanisms, the application system can automatically adjust the connection status during runtime, thereby achieving seamless data synchronization between heterogeneous databases.

[0102] In an embodiment of the present application, the data synchronization method for heterogeneous database self-adaptation further includes: listening to the exceptions generated during the data synchronization process. The exceptions at least include database connection exceptions, data retrieval exceptions, and data insertion exceptions; converting the listened exceptions into standard exception types, and determining the exception handling solutions corresponding to the standard exception types based on preset exception handling strategies. The exception handling solutions at least include retry, skip, rollback, and alarm.

[0103] In a specific embodiment of the present application, first listen to all the exceptions generated during the data synchronization process and classify them. The following are examples of some common exception types:

[0104] Database connection exception:

[0105] Connection failure: Unable to establish an initial connection to the database.

[0106] Connection timeout: The connection request does not receive a response within the set time.

[0107] Data retrieval exception:

[0108] Query failure: The SQL query statement execution fails.

[0109] Data format error: The data retrieved from the database does not conform to the expected format.

[0110] Data insertion exception:

[0111] Insertion failed: The SQL insertion statement execution failed.

[0112] Data conflict: The inserted data violates the database constraints (such as uniqueness constraints).

[0113] During the specific monitoring process, a unified exception monitoring mechanism is used to monitor these exceptions. After an exception is detected, it is converted into a standard exception type for subsequent processing. Furthermore, different exception handling mechanisms are adopted according to the preset exception handling strategies for different exception types. The exception handling mechanisms include but are not limited to retry, skip, rollback, and alarm.

[0114] Specifically, when encountering temporary problems (such as connection timeouts), a retry mechanism is adopted to attempt to re-execute the operation. When encountering certain non-critical exceptions (such as data format errors for specific records), a skip mechanism is adopted to skip the record and continue processing other records. When a serious error occurs (such as transaction failure), a rollback mechanism is adopted to roll back the transaction to ensure data consistency. For severe exception situations, an alarm mechanism is required to generate an alarm to notify relevant personnel. In addition, to facilitate subsequent problem troubleshooting, detailed error logs need to be recorded, including the database type, SQL statements, and error messages.

[0115] It should be noted that in the embodiments proposed in this application, by adopting different processing mechanisms according to different problem types, the reliability and stability of the system are significantly improved, ensuring that data synchronization operations can be quickly restored in case of abnormal situations. Specifically, through the retry mechanism and the skip mechanism, the data synchronization interruption time caused by temporary problems is reduced. Through the rollback mechanism, data consistency is ensured in case of serious errors. Through the alarm mechanism, relevant personnel can be notified in a timely manner, facilitating quick response and problem handling. Generally speaking, through dynamic selection and application of exception handling mechanisms, the system can automatically adjust the exception handling strategy during operation, thereby achieving seamless data synchronization between heterogeneous databases.

[0116] Figure 3 It is a schematic diagram of the application initialization process of the heterogeneous database self-adapting data synchronization method shown in an exemplary embodiment of this application.

[0117] In an embodiment of this application, the steps before obtaining the data to be synchronized from the application system are regarded as the application initialization process, and its specific steps are as Figure 3As shown below. First, start the application system and read the connection configuration file of the application system; then, parse its connection configuration file to obtain the DBtype in the connection, and further obtain the type, account, and password of the database; then, create a data source connection for it and initialize the provider; finally, load the xml node mapping statement into memory. After the mapping is completed, it indicates that the application startup is completed, that is, the initialization is completed.

[0118] In a specific embodiment of the present application, first, the application system is started by the startup script or service management tool (such as systemd) of the operating system, and after startup, the connection configuration file of the application system is read, and then its connection configuration file is parsed to obtain the database type (DBType) and connection information (such as host address, port, database name, username, and password); then, based on the parsed database type and connection information, a corresponding data source connection is created, and a simple Provider class is initialized to manage the database connection and operations; in addition, in order to implement the dynamic conversion and execution of SQL statements, the XML node mapping statement is further loaded into memory; finally, when all steps are completed, it indicates that the application initialization is completed, that is, the system is ready for data synchronization operations.

[0119] It can be understood that this embodiment shows how to regard the steps before obtaining the data to be synchronized from the application system as the application initialization process. The specific steps include starting the application system, reading and parsing the connection configuration file, creating a data source connection, initializing the Provider, and loading the XML node mapping statement into memory. This method significantly improves the availability and stability of the system, ensuring that all necessary components can be correctly configured and initialized when the application starts, laying a foundation for subsequent data synchronization operations.

[0120] Figure 4 It is a schematic diagram of the SQL execution process of the heterogeneous database self-adaptation data synchronization method shown in an exemplary embodiment of the present application.

[0121] In an embodiment of the present application, the process of "inserting the obtained data to be synchronized into the target database" is regarded as the SQL execution process, and its specific steps are as Figure 4 shown below. First, obtain the execution request of the SQL, and based on this request, obtain the DBtype of the data source in memory; then obtain the databaseld through memory mapping matching, and further obtain the xml node configuration according to the databaseld, match the correct SQL statement node, and execute the statement content; in addition, during its execution process, exception recognition is performed. If an exception exists, an exception rollback is performed. If no exception exists, the entire data synchronization process is returned and ended.

[0122] In a specific embodiment of the present application, first, an SQL execution request is obtained. This request generally includes an operation type (such as insert), a data source ID (DatabaseId), and the data record to be inserted. Then, according to the database_id in the request, the corresponding DBType is obtained from the data source configuration object in the memory. Then, through memory mapping, the DatabaseId is matched, and then the XML node configuration is obtained, and the correct SQL statement is matched. Furthermore, the XML node configuration is obtained according to the DatabaseId, and the correct SQL statement is matched. Finally, the SQL statement is executed, and exception recognition and handling are performed during the execution process. In addition, during the execution process, if an exception occurs, the transaction is rolled back and detailed error logs are recorded.

[0123] It can be understood that this embodiment shows how to regard the process of "inserting the aforementioned obtained data to be synchronized into the target database" as an SQL execution process. The specific steps include obtaining an SQL execution request, obtaining the DBType of the data source in the memory based on the request, matching the DatabaseId through memory mapping, obtaining the XML node configuration and matching the correct SQL statement, executing the statement content, and performing exception recognition and handling. This method significantly improves the accuracy and efficiency of data synchronization operations, enhances the flexibility and adaptability of the system, and enables it to support various types of database environments.

[0124] Figure 5 is a block diagram of a data synchronization device with self-adaptation for heterogeneous databases shown in an exemplary embodiment of the present application. This device can be applied to Figure 1 the implementation environment shown. This device can also be applicable to other exemplary implementation environments and is specifically configured in other devices. This embodiment does not limit the implementation environment applicable to this device.

[0125] As Figure 5 shown, this exemplary data synchronization device with self-adaptation for heterogeneous databases includes: a file acquisition module 510, a database category recognition module 520, a database information matching module 530, a data to be synchronized retrieval module 540, and a data to be synchronized insertion module 550.

[0126] Among them, the file acquisition module 510 is used to acquire the database connection configuration file of the application system, and the database connection configuration file is configured with the connection relationships between the application system and the source database and the target database respectively; the database category identification module 520 is used to identify the database connection configuration file to determine the database categories of the source database and the target database based on their connection relationships; the database information matching module 530 is used to configure corresponding data sources for the source database and the target database respectively based on the identified database categories, and select corresponding structured query languages from a preset structured query language library; the data to be synchronized retrieval module 540 is used to retrieve the data to be synchronized in the source database according to the configured data source of the source database and the corresponding structured query language; the data to be synchronized insertion module 550 is used to insert the retrieved data to be synchronized into the target database according to the configured data source of the target database and its corresponding structured query language.

[0127] It should be noted that the heterogeneous database self-adapting data synchronization device provided in the above embodiment and the heterogeneous database self-adapting data synchronization method provided in the above embodiment belong to the same concept. The specific manners in which each module and unit perform operations have been described in detail in the method embodiment, and will not be elaborated here. In practical applications, the heterogeneous database self-adapting data synchronization device provided in the above embodiment can, according to needs, allocate the above functions to different functional modules, that is, divide the internal structure of the device into different functional modules to complete all or part of the functions described above. This is not limited here either.

[0128] An embodiment of the present application also provides an electronic device, including: one or more processors; a storage device for storing one or more programs, and when the one or more programs are executed by the one or more processors, the electronic device implements the heterogeneous database self-adapting data synchronization method provided in each of the above embodiments.

[0129] Figure 6 The structural diagram of a computer system of an electronic device suitable for implementing the embodiments of the present application is shown. It should be noted that Figure 6 The computer system 600 of the electronic device shown is only an example and should not bring any limitation to the functions and usage scopes of the embodiments of the present application.

[0130] Such as Figure 6As shown, the computer system 600 includes a Central Processing Unit (CPU) 601, which can perform various appropriate actions and processes according to the program stored in the Read-Only Memory (ROM) 602 or the program loaded from the storage section 608 into the Random Access Memory (RAM) 603, such as executing the methods described in the above embodiments. In the RAM 603, various programs and data required for system operation are also stored. The CPU 601, ROM 602, and RAM 603 are connected to each other via a bus 604. An Input / Output (I / O) interface 605 is also connected to the bus 604.

[0131] The following components are connected to the I / O interface 605: an input section 606 including a keyboard, a mouse, etc.; an output section 607 including, for example, a Cathode Ray Tube (CRT), a Liquid Crystal Display (LCD), etc. and a speaker, etc.; a storage section 608 including a hard disk, etc.; and a communication section 609 including a network interface card such as a LAN (Local Area Network) card, a modem, etc. The communication section 609 performs communication processing via a network such as the Internet. A drive 610 is also connected to the I / O interface 605 as needed. A removable medium 611, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 610 as needed so that the computer program read from it can be installed into the storage section 608 as needed.

[0132] Specifically, according to the embodiments of the present application, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, the embodiments of the present application include a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program includes a computer program for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from the network via the communication section 609, and / or installed from the removable medium 611. When the computer program is executed by the Central Processing Unit (CPU) 601, various functions defined in the system of the present application are executed.

[0133] It should be noted that the computer-readable medium shown in the embodiments of the present application can be a computer-readable signal medium, a computer-readable storage medium, or any combination of the two. A computer-readable storage medium can be, for example, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the above. More specific examples of the computer-readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM), a flash memory, an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present application, a computer-readable signal medium may include a data signal propagated in a baseband or as part of a carrier wave, which carries a computer-readable computer program. Such a propagated data signal can take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. A computer-readable signal medium can also be any computer-readable medium other than a computer-readable storage medium, which can send, propagate, or transmit a program for use by or in connection with an instruction execution system, apparatus, or device. The computer program contained on the computer-readable medium can be transmitted using any appropriate medium, including but not limited to: wireless, wired, etc., or any suitable combination of the above.

[0134] The flowcharts and block diagrams in the accompanying drawings illustrate the possible architectures, functions, and operations of systems, methods, and computer program products according to various embodiments of the present application. Among them, each block in the flowchart or block diagram may represent a module, a program segment, or a part of code, and the above-mentioned module, program segment, or part of code contains one or more executable instructions for implementing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the blocks may occur in a different order than that marked in the accompanying drawings. For example, two consecutive blocks shown may actually be executed substantially in parallel, and they may sometimes be executed in the reverse order, depending on the functions involved. It should also be noted that each block in the block diagram or flowchart, and the combination of blocks in the block diagram or flowchart, can be implemented by a dedicated hardware-based system for performing the specified functions or operations, or can be implemented by a combination of dedicated hardware and computer instructions.

[0135] The units involved in the embodiments described in this application can be implemented in software or in hardware, and the described units can also be provided in a processor. Among them, the names of these units do not, in some cases, constitute a limitation on the units themselves.

[0136] On the other hand, this application also provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor of a computer, the computer is made to execute the heterogeneous database self-adapting data synchronization method as described above. The computer-readable storage medium may be included in the electronic device described in the above embodiments, or may exist separately without being assembled into the electronic device.

[0137] On the other hand, this application also provides a computer program product or a computer program. The computer program product or the computer program includes computer instructions, and the computer instructions are stored in a computer-readable storage medium. A processor of a computer device reads the computer instructions from the computer-readable storage medium, and the processor executes the computer instructions, so that the computer device executes the heterogeneous database self-adapting data synchronization method provided in the above various embodiments.

[0138] The above embodiments are only used to exemplarily illustrate the principles and effects of this application, rather than to limit this application. Any person familiar with this technology can modify or change the above embodiments without departing from the spirit and scope of this application. Therefore, all equivalent modifications or changes completed by those with ordinary knowledge in the technical field without departing from the spirit and technical ideas disclosed in this application should still be covered by the claims of this application.

Claims

1. A self-adaptive data synchronization method for heterogeneous databases, characterized in that: The method comprises: Obtaining a database connection configuration file of the application system, wherein the database connection configuration file is configured with connection relationships between the application system and a source database and a target database respectively; identifying the database connection profile to determine database categories of the source database and the target database based on the connection relationship; Based on the identified database category, corresponding data sources are configured for the source database and the target database respectively, and a corresponding structured query language is selected from a preset structured query language library; According to the configured source database data source and the corresponding structured query language, retrieve the data to be synchronized in the source database; According to the configured target database data source and its corresponding structured query language, the retrieved data to be synchronized is inserted into the target database.

2. The self-adaptive data synchronization method for heterogeneous databases according to claim 1, characterized in that: Select the corresponding structured query language from the preset structured query language library, including: Build a structured query language template library containing a variety of standard structured query statements, and set corresponding structured query language conversion rules for each category of database; Select corresponding structured query language conversion rules according to the specific types of the identified source database and target database; Based on the selected structured query language conversion rule, the standard structured query statement in the structured query language template library is converted into a converted structured query statement matching the source database and the target database type.

3. The self-adaptive data synchronization method for heterogeneous databases according to claim 2 is characterized in that: Execute query operations in the source database to retrieve the data to be synchronized, including: Generate standard structured query language query statements based on data synchronization requirements; Converting the standard structured query statement into a converted structured query statement that matches the source database; Based on the converted structured query statement, a data range of the data to be synchronized is located in the source database, and data within the data range is extracted to generate an original data set for representing the data to be synchronized.

4. The self-adaptive data synchronization method for heterogeneous databases according to claim 3 is characterized in that: After generating the original data set used to represent the data to be synchronized, it also includes: Identify the fields in the original data set to obtain the original data type and original value of the data to be synchronized; Based on the original data type, query a preset data type mapping table to obtain a corresponding target data type in the target database, wherein the data type mapping table includes a mapping relationship between data types in different types of databases; The original data type is converted into a matching target data type, and the original value is assigned to the corresponding target data type to generate a target synchronization data table.

5. The self-adaptive data synchronization method for heterogeneous databases according to claim 4 is characterized in that: Insert the retrieved data to be synchronized into the target database, including: Based on the target synchronization data table standard structured insert statement, the standard structured insert statement is converted into a converted structured insert statement matching the target database; The target synchronization data table is divided into a plurality of data batches according to the converted structured insert statement, and the data batches are inserted into the target database in sequence.

6. The self-adaptive data synchronization method for heterogeneous databases according to any one of claims 1 to 5, characterized in that: After inserting the retrieved data to be synchronized into the target database, it also includes: Monitor the connection status between the application system and the source database and target database in real time; When a connection anomaly is detected in any connection state, re-establish the connection until the connection is successful, wherein the connection anomaly includes at least connection failure and connection timeout; Update the data source and database connection parameters based on the new connection information to generate a new connection profile.

7. The self-adaptive data synchronization method for heterogeneous databases according to any one of claims 1 to 5, characterized in that: The method further comprises: Monitor the exceptions generated during data synchronization, which at least include database connection exceptions, data retrieval exceptions, and data insertion exceptions; The monitored exceptions are converted into standard exception types, and the exception handling scheme corresponding to the standard exception type is determined based on a preset exception handling strategy, wherein the exception handling scheme includes at least retry, skip, rollback, and alarm.

8. A self-adaptive data synchronization device for heterogeneous databases, characterized in that: The device comprises: A file acquisition module, used to acquire a database connection configuration file of the application system, wherein the database connection configuration file is configured with connection relationships between the application system and the source database and the target database respectively; a database category identification module, configured to identify the database connection profile to determine the database categories of the source database and the target database based on the connection relationship; A database information matching module is used to configure corresponding data sources for the source database and the target database respectively based on the identified database category, and select a corresponding structured query language from a preset structured query language library; The to-be-synchronized data retrieval module is used to retrieve the to-be-synchronized data in the source database according to the configured source database data source and the corresponding structured query language; The module for inserting data to be synchronized is used to insert the retrieved data to be synchronized into the target database according to the configured target database data source and its corresponding structured query language.

9. An electronic device, characterized in that: It comprises a processor, a memory and a communication bus; the communication bus is used to connect the processor and the memory; the processor is used to execute the computer program stored in the memory to implement the self-adaptive data synchronization method for heterogeneous databases as described in any one of claims 1-7.

10. A computer-readable storage medium, characterized in that: A computer program is stored thereon, and the computer program is used to enable a computer to execute the self-adaptive data synchronization method for heterogeneous databases as described in any one of claims 1-7.

Images