CN113486019B

CN113486019B - Automatic triggering real-time batch synchronization method and device for remote multi-database data

Info

Publication number: CN113486019B
Application number: CN202110849775.6A
Authority: CN
Inventors: 李鹏
Original assignee: Bank of China Ltd
Current assignee: Bank of China Ltd
Priority date: 2021-07-27
Filing date: 2021-07-27
Publication date: 2024-02-23
Anticipated expiration: 2041-07-27
Also published as: CN113486019A

Abstract

The invention provides a method and a device for automatically triggering real-time batch synchronization of remote multi-database data, wherein the method comprises the following steps: loading a Java multi-library synchronous program into an Oracle database for compiling; executing a real-time multi-library synchronous PL/SQL program package in an Oracle database, calling a Java multi-library synchronous program, and determining a remote database connection method; when the DML operation of the database table is monitored through the row trigger, recording information of all fields after the DML operation of the database table, and calling a real-time multi-library synchronous PL/SQL program package to splice to obtain spliced full-field information; the AFTER statement level trigger generates a JDBC binding variable statement according to the type of the DML operation and by combining a system view of an Oracle database; and extracting the spliced full-field information from the cache, transmitting the full-field information to a Java multi-library synchronous program for interaction, and carrying out batch synchronous updating on the remote multi-database.

Description

Automatic triggering real-time batch synchronization method and device for remote multi-database data

Technical Field

The invention relates to the technical field of computer data processing, in particular to a method and a device for automatically triggering real-time batch synchronization of remote multi-database data.

Background

This section is intended to provide a background or context to the embodiments of the invention that are recited in the claims. The description herein is not admitted to be prior art by inclusion in this section.

The prior ORACLE database for a plurality of subsystems is related to synchronous updating of public basic data, and the timing task is mostly adopted for batch updating, on one hand, the triggering time is completely configured by the timing batch task, so that the consistency of a plurality of database data cannot be well ensured, the method has a certain time ductility, in addition, the condition that the data is not changed cannot be processed efficiently and properly, the whole table data can be locked only through a scheme of whole table data synchronization and related to multi-system server file transmission, batch scheduling and the like, the service maintenance is influenced, the consistency of the multi-database data cannot be ensured, the universality is relatively poor, and the expansibility is relatively poor.

Even if a design scheme of automatically triggering and updating a remote database by using a row trigger is adopted, a scene of a large amount of data such as full-table updating cannot be faced only for a small amount of data or a single record.

The use of Oracledtank (DATABASE LINK, a mechanism provided by the Oracle DATABASE for convenient access to other DATABASEs in one DATABASE) scheme updates, requires the use of established remote DATABASE LINKs, often has excessive rights, and many production systems do not allow the use of such a mechanism for safety reasons, and in addition, the use of dbtank schemes requires the handling of remote tables for each table, which is not conducive to automated code generation and expansion.

Based on Oracle bottom log, on one hand, the processing authority is higher, and on the other hand, the timing trigger processing is also needed, and the lightweight database code table is equivalent to the use of a cow knife for killing chickens.

Therefore, how to provide a new solution to the above technical problem is a technical problem to be solved in the art.

Disclosure of Invention

The embodiment of the invention provides an automatic triggering real-time batch synchronization method for remote multi-database data, which can realize the efficient real-time batch synchronization of the multi-database data according to the automatic triggering of the database synchronization, and comprises the following steps:

loading a Java multi-library synchronous program into an Oracle database for compiling;

executing a real-time multi-library synchronous PL/SQL program package in an Oracle database, calling a Java multi-library synchronous program, and determining an initialization method of connecting configuration encryption information of a remote database and a method for testing whether the remote database can be connected;

monitoring a database table through a row trigger, recording information of all fields of the database table after DML operation by using the row trigger when the database table is subjected to DML operation, calling a real-time multi-library synchronous PL/SQL program package, storing a cache provided by the real-time multi-library synchronous PL/SQL program package for splicing, and determining spliced full-field information;

The AFTER statement level trigger generates a JDBC binding variable statement according to the type of the DML operation and by combining a system view of an Oracle database;

extracting spliced full-field information from the cache through an AFTER statement level trigger, and transmitting the full-field information to a Java multi-library synchronous program for interaction;

calling a real-time multi-library synchronous PL/SQL program package, connecting the remote multi-database according to an initialization method for connecting configuration encryption information of the remote database and a method for testing whether the remote database can be connected, calling a Java multi-library synchronous program to establish a thread pool, and concurrently executing spliced full-field information according to JDBC binding variable sentences transmitted by an AFTER sentence level trigger to synchronously update the remote multi-database in batches.

The embodiment of the invention also provides a device for automatically triggering the real-time batch synchronization of the remote multi-database data, which comprises the following steps:

the Java multi-library synchronous program compiling module is used for loading the Java multi-library synchronous program into an Oracle database for compiling;

the remote multi-database connection method configuration module is used for executing a real-time multi-database synchronous PL/SQL program package in an Oracle database, calling a Java multi-database synchronous program, determining an initialization method of the remote database connection configuration encryption information and a method for testing whether the remote database can be connected;

The spliced full-field information determining module is used for monitoring the database table through the row trigger, recording information of all fields after the database table is subjected to DML operation by utilizing the row trigger when the database table is subjected to the DML operation, calling a real-time multi-library synchronous PL/SQL program package, storing a cache provided by the real-time multi-library synchronous PL/SQL program package for splicing, and determining spliced full-field information;

the JDBC binding variable statement generating module is used for generating a JDBC binding variable statement by combining a system view of an Oracle database according to the type of the DML operation by the AFTER statement level trigger;

the spliced full-field information extraction module is used for extracting spliced full-field information from the cache through an AFTER statement level trigger, and transmitting the spliced full-field information to a Java multi-library synchronous program for interaction;

the remote multi-database synchronous updating module is used for calling a real-time multi-database synchronous PL/SQL program package, connecting the remote multi-databases according to an initialization method for connecting configuration encryption information of the remote databases and a method for testing whether the remote databases can be connected, calling a Java multi-database synchronous program method to establish a thread pool, and synchronously updating the remote multi-databases in batches by concurrently executing the spliced full-field information according to JDBC binding variable sentences transmitted by an AFTER sentence level trigger.

The embodiment of the invention also provides computer equipment, which comprises a memory, a processor and a computer program stored on the memory and capable of running on the processor, wherein the processor realizes the method for automatically triggering the real-time batch synchronization of the remote multi-database data when executing the computer program.

The embodiment of the invention also provides a computer readable storage medium which stores a computer program for executing the method for automatically triggering the real-time batch synchronization of the remote multi-database data.

The embodiment of the invention provides a method and a device for automatically triggering real-time batch synchronization of remote multi-database data, which comprise the following steps: firstly, loading a Java multi-library synchronous program into an Oracle database for compiling; then executing a real-time multi-library synchronous PL/SQL program package in an Oracle database, calling a Java multi-library synchronous program, and determining an initialization method of the remote database connection configuration encryption information and a method for testing whether the remote database can be connected; then monitoring a database table through a row trigger, when the database table is subjected to DML operation, recording information of all fields of the database table after the DML operation by utilizing the row trigger, calling a real-time multi-library synchronous PL/SQL program package, storing a cache provided by the real-time multi-library synchronous PL/SQL program package for splicing, and determining spliced full-field information; the next step of an AFTER statement level trigger generates a JDBC binding variable statement according to the type of the DML operation and combining a system view of an Oracle database; then extracting the spliced full-field information from the cache through an AFTER statement level trigger, and transmitting the full-field information to a Java multi-library synchronous program for interaction; and finally, calling a real-time multi-library synchronous PL/SQL program package, connecting the remote multi-database according to an initialization method for connecting configuration encryption information of the remote database and a method for testing whether the remote database can be connected, calling a Java multi-library synchronous program to establish a thread pool, and concurrently executing the spliced full-field information according to JDBC binding variable statements transmitted by an AFTER statement level trigger to synchronously update the remote multi-database in batches. According to the embodiment of the invention, the Java multi-library synchronization program is loaded into the Oracle database, specific synchronization processing logic is encapsulated and hidden, the line trigger realizes the storage of trigger synchronization data and the calling of a related method of the real-time multi-library synchronization PL/SQL program package, automatic triggering and batch real-time synchronization are realized, a small amount of partial table record updating is met, the multi-database full-table data synchronization is supported, relatively complex batch timing scheduling is not needed, and the timeliness is good.

Drawings

In order to more clearly illustrate the embodiments of the invention or the technical solutions in the prior art, the drawings that are required in the embodiments or the description of the prior art will be briefly described, it being obvious that the drawings in the following description are only some embodiments of the invention, and that other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art. In the drawings:

fig. 1 is a schematic diagram of a method for automatically triggering real-time batch synchronization of remote multi-database data according to an embodiment of the present invention.

Fig. 2 is a schematic diagram of a process for automatically triggering generation of JDBC binding variable statements for a remote multi-database data real-time batch synchronization method according to an embodiment of the present invention.

Fig. 3 is a schematic diagram of a process of automatically triggering generation and splicing of full field information of a method for real-time batch synchronization of remote multi-database data according to an embodiment of the present invention.

FIG. 4 is a schematic diagram of a computer device for performing an automatic triggering method for real-time batch synchronization of remote multi-database data implemented in accordance with the present invention.

Fig. 5 is a schematic diagram of an automatic triggering real-time batch synchronization device for remote multi-database data according to an embodiment of the invention.

Detailed Description

For the purpose of making the objects, technical solutions and advantages of the embodiments of the present invention more apparent, the embodiments of the present invention will be described in further detail with reference to the accompanying drawings. The exemplary embodiments of the present invention and their descriptions herein are for the purpose of explaining the present invention, but are not to be construed as limiting the invention.

Fig. 1 is a schematic diagram of an automatic triggering real-time batch synchronization method for remote multi-database data according to an embodiment of the present invention, and as shown in fig. 1, the embodiment of the present invention provides an automatic triggering real-time batch synchronization method for remote multi-database data, which can implement efficient real-time batch synchronization for multi-database data according to automatic triggering database synchronization, and the method includes:

step 101: loading a Java multi-library synchronous program into an Oracle database for compiling;

step 102: executing a real-time multi-library synchronous PL/SQL program package in an Oracle database, calling a Java multi-library synchronous program, and determining an initialization method of connecting configuration encryption information of a remote database and a method for testing whether the remote database can be connected;

step 103: monitoring a database table through a row trigger, recording information of all fields of the database table after DML operation by using the row trigger when the database table is subjected to DML operation, calling a real-time multi-library synchronous PL/SQL program package, storing a cache provided by the real-time multi-library synchronous PL/SQL program package for splicing, and determining spliced full-field information;

Step 104: the AFTER statement level trigger generates a JDBC binding variable statement according to the type of the DML operation and by combining a system view of an Oracle database;

step 105: extracting spliced full-field information from the cache through an AFTER statement level trigger, and transmitting the full-field information to a Java multi-library synchronous program for interaction;

step 106: calling a real-time multi-library synchronous PL/SQL program package, connecting the remote multi-database according to an initialization method for connecting configuration encryption information of the remote database and a method for testing whether the remote database can be connected, calling a Java multi-library synchronous program to establish a thread pool, and concurrently executing spliced full-field information according to JDBC binding variable sentences transmitted by an AFTER sentence level trigger to synchronously update the remote multi-database in batches.

The embodiment of the invention provides a method for automatically triggering real-time batch synchronization of remote multi-database data, which comprises the following steps: firstly, loading a Java multi-library synchronous program into an Oracle database for compiling; then executing a real-time multi-library synchronous PL/SQL program package in an Oracle database, calling a Java multi-library synchronous program, and determining an initialization method of the remote database connection configuration encryption information and a method for testing whether the remote database can be connected; then monitoring a database table through a row trigger, when the database table is subjected to DML operation, recording information of all fields of the database table after the DML operation by utilizing the row trigger, calling a real-time multi-library synchronous PL/SQL program package, storing a cache provided by the real-time multi-library synchronous PL/SQL program package for splicing, and determining spliced full-field information; the next step of an AFTER statement level trigger generates a JDBC binding variable statement according to the type of the DML operation and combining a system view of an Oracle database; then extracting the spliced full-field information from the cache through an AFTER statement level trigger, and transmitting the full-field information to a Java multi-library synchronous program for interaction; and finally, calling a real-time multi-library synchronous PL/SQL program package, connecting the remote multi-database according to an initialization method for connecting configuration encryption information of the remote database and a method for testing whether the remote database can be connected, calling a Java multi-library synchronous program to establish a thread pool, and concurrently executing the spliced full-field information according to JDBC binding variable statements transmitted by an AFTER statement level trigger to synchronously update the remote multi-database in batches. According to the embodiment of the invention, the Java multi-library synchronization program is loaded into the Oracle database, specific synchronization processing logic is encapsulated and hidden, the line trigger realizes the storage of trigger synchronization data and the calling of a related method of the real-time multi-library synchronization PL/SQL program package, automatic triggering and batch real-time synchronization are realized, a small amount of partial table record updating is met, the multi-database full-table data synchronization is supported, relatively complex batch timing scheduling is not needed, and the timeliness is good.

When the method for automatically triggering the real-time batch synchronization of the remote multi-database data provided by the embodiment of the invention is implemented, the method can comprise the following steps:

loading a Java multi-library synchronous program into an Oracle database for compiling; executing a real-time multi-library synchronous PL/SQL program package in an Oracle database, calling a Java multi-library synchronous program, and determining an initialization method of connecting configuration encryption information of a remote database and a method for testing whether the remote database can be connected; monitoring a database table through a row trigger, recording information of all fields of the database table after DML operation by using the row trigger when the database table is subjected to DML operation, calling a real-time multi-library synchronous PL/SQL program package, storing a cache provided by the real-time multi-library synchronous PL/SQL program package for splicing, and determining spliced full-field information; the AFTER statement level trigger generates a JDBC binding variable statement according to the type of the DML operation and by combining a system view of an Oracle database; extracting spliced full-field information from the cache through an AFTER statement level trigger, and transmitting the full-field information to a Java multi-library synchronous program for interaction; calling a real-time multi-library synchronous PL/SQL program package, connecting the remote multi-database according to an initialization method for connecting configuration encryption information of the remote database and a method for testing whether the remote database can be connected, calling a Java multi-library synchronous program to establish a thread pool, and concurrently executing spliced full-field information according to JDBC binding variable sentences transmitted by an AFTER sentence level trigger to synchronously update the remote multi-database in batches.

In an embodiment, the foregoing real-time multi-library synchronous PL/SQL Package refers to a Package related to a storage process of an Oracle database, refers to a Package in the storage process, that is, a Package, also an Oracle storage process term, and is a special program structure. The program structure package is divided into a package head and a package body. The header is used to declare common variables and methods. The packet body is used for declaring the specific implementation of the private variable and the declaration method in the packet head, and an initialization block is arranged in the packet body, and when the program packet is used, the initialization is only executed once.

When the method for automatically triggering real-time batch synchronization of remote multi-database data provided by the embodiment of the invention is implemented, in one embodiment, a Java multi-database synchronization program is loaded into an Oracle database for compiling, and the method comprises the following steps:

and loading the Java multi-library synchronous program into the Oracle database for compiling by using a loadjava command provided by the Oracle database.

In an embodiment, loading the Java multi-library synchronization program into the Oracle database may include: and loading the Java multi-library synchronous program into the Oracle database by using a loadjava command provided by the Oracle database. By loading the Java multi-library synchronization program into the Oracle database, specific synchronization processing logic is encapsulated and hidden, and only synchronous data and related method calls are considered in the process of database updating, so that development difficulty is reduced, and deployment is easy. The loadjava command is a special execution code name (or called command name) provided for Oracle for loading Java programs into an Oracle database, and is a mechanism supported by Oracle for Java, and after Java is loaded into the Oracle database through the loadjava command, a Java program implementation method can be conveniently used in the database through special grammar, like a common storage process.

When the method for automatically triggering real-time batch synchronization of remote multi-database data provided by the embodiment of the invention is implemented, in one embodiment, the method for initializing connection configuration encryption information of a remote database and testing whether the remote database can be connected is implemented by executing a real-time multi-database synchronization PL/SQL program package in an Oracle database, calling a Java multi-database synchronization program, and comprises the following steps:

executing a real-time multi-library synchronous PL/SQL program package in an Oracle database, analyzing encrypted remote connection string information configured by a database table, configuring a remote database connection string, and generating a dynamic password key through an encryption program provided by the real-time multi-library synchronous PL/SQL program package;

and calling a Java multi-library synchronous program, and determining an initialization method of the remote database connection configuration encryption information and a method for testing whether the remote database can be connected by using the dynamic password key.

In an embodiment, before database synchronization, an association relationship is established between a database table to be updated and a remote multi-database, so that the remote multi-database can be directly connected when the remote multi-database is updated, where the association relationship includes: an initialization method for connecting configuration encryption information of a remote database and a method for testing whether the remote database can be connected.

Specifically, executing a real-time multi-library synchronous PL/SQL program package in an Oracle database, calling a Java multi-library synchronous program, and determining an initialization method for connecting configuration encryption information of a remote database and a method for testing whether the remote database can be connected, which may include: firstly, executing a real-time multi-library synchronous PL/SQL program package in an Oracle database for compiling, analyzing encrypted remote connection string information configured by a database table, configuring a remote database connection string, and generating a dynamic password key through an encryption program provided by the real-time multi-library synchronous PL/SQL program package; and next, invoking a Java multi-library synchronous program, and determining an initialization method of the connection configuration encryption information of the remote database and a method for testing whether the remote database can be connected by using the dynamic password key. Wherein, when the PL/SQL program package is compiled, the connection with the remote database is not established; only an initialization method of the remote database connection configuration encryption information and a method for testing whether the remote database can be connected are provided, so that the remote multi-database can be directly connected when the remote multi-database is updated later. By providing the initialization method for the connection configuration encryption information of the remote database and the method for testing whether the remote database can be connected, the connection can be quickly established when the remote multi-database needs to be updated, the connection is not performed when the remote multi-database needs to be updated, the waste of the connection resources between systems caused by unnecessary connection is reduced, the context switching and IO loss are reduced as much as possible, and the reasonable configuration of the system resources is realized.

The real-time multi-library synchronous PL/SQL program package provides a function for calling loadjava to be transmitted to an Oracle database, analyzes encrypted remote connection string information configured by a database table (encryption is carried out by adopting an AES-256 encryption algorithm, a secret key is random and is related to the position of a configuration table, and can be calculated through a certain algorithm), the encryption and decryption process adopts a wrap tool provided by Oracle to encrypt, the analyzed parameters are transmitted to a Java program, an initialization method for establishing remote database connection configuration encryption information and a method for testing whether the remote database can be connected or not is established, and the transmitted appointed sentences are executed in batches. The initialization template program is encrypted, the password key is dynamically generated, and the initialization template program can be analyzed only when the password key is called inside the internal program of the bag body, and the external program cannot be called directly. The aforementioned wrap tool (Wrapping is the process of hiding PL/SQL source code) is an executable code command provided by Oracle for encrypting a stored process source code file, and belongs to an Oracle self-contained tool.

Meanwhile, the real-time multi-library synchronous PL/SQL program package provides a character string connection cache mechanism, the incoming character string is transmitted to an internal Java program, a thread pool can be built according to the number of databases to be synchronized, concurrent execution is performed, data synchronization is performed, execution time is shortened, and execution efficiency is improved.

Meanwhile, the real-time multi-library synchronous PL/SQL program package provides PL/SQL buffer cleaning and the recovery of the Java program application resources. The aforementioned PL/SQL cache cleanup (PL/SQL cache memory cleaning) is a temporary cache cleanup for stored procedures. In order to improve the processing efficiency of the program, reduce the context switching between the storage process and the Java running environment, save the information of the table column field of the database related to adjustment by applying for the storage space with specified length in the program, and exchange data with the Java running environment through a caching mechanism of a specific algorithm. When the program is terminated (including normal termination and abnormal termination), the storage space of the application needs to be recovered, namely the temporary cache is cleaned, and the memory leakage is avoided.

Fig. 2 is a schematic diagram of a process of generating JDBC binding variable statements in an automatic triggering method for real-time batch synchronization of remote multi-database data according to an embodiment of the present invention, as shown in fig. 2, when the method for real-time batch synchronization of remote multi-database data according to an embodiment of the present invention is implemented, in an embodiment, the JDBC binding variable statements include: recording a full field MERGE binding statement in the whole row, and performing DELETE binding statement;

the AFTER statement level trigger generates a JDBC binding variable statement according to the type of the DML operation and combining with a system view of an Oracle database, and the method comprises the following steps:

Step 201: the AFTER statement level trigger judges the type of the DML operation according to the DML operation; the type of DML operation, among others, includes: updating and inserting operation, deleting operation;

step 202: when the type of the DML operation is an updating and inserting operation, generating a corresponding whole-row record full-field MERGE binding statement by combining a system view of an Oracle database;

step 203: when the type of the DML operation is a DELETE operation, a DELETE binding statement is generated in conjunction with the system view of the Oracle database.

In an embodiment, the JDBC binds variable statements, comprising: recording the whole field MERGE binding statement in a more complete line, and recording the DELETE binding statement; java Database Connectivity, JDBC for short, is an application program interface in the Java language that is used to specify how client programs access databases, providing methods such as querying and updating data in databases. JDBC bind variable statements refer to statements supported by JDBC with bound variables. In this embodiment, a mechanism for preparing dynamic sentences in the JDBC interface is used, and the mechanism is a template sentence, and after the sentence is submitted to the database, the sentence is only needed to be parsed and compiled once, the database does not execute the sentence, and the analysis is not needed to be performed every time of submission, thereby improving the sentence execution efficiency. In the statement, the actual execution value is input through the agreed placeholder, and the database replaces the placeholder with the input value and executes the value when the statement is executed. Among them, the MERGE statement is one of the DML (data manipulation language) supported by Oracle, and may be called a MERGE statement. The statement queries another table (target table) according to the connection condition of the table (original data table) or sub-query, and performs UPDATE on the connection condition matching, and cannot perform INSERT in a matching manner. This syntax requires only one full table scan to complete the entire job, with execution efficiency higher than insert+update.

The DML refers to Data Manipulation Language, i.e. a data manipulation language, and includes operations such as commonly used add (INSERT), DELETE (DELETE), UPDATE (UPDATE) (the Merge statement supported by Oracle is also one of the DML statements), where the DML operation in this embodiment refers to a specified data manipulation event monitored by a trigger for a database table for creating a trigger, for example, the trigger may create a corresponding process for adding, deleting, and modifying a data table, and when a specific event such as adding, deleting, and modifying a table is performed, if the trigger is validated, a storage process defined in the trigger is triggered to be executed, so as to perform data processing. There are actually several kinds of flip-flops of Oracle. Here, the trigger operation established when DML operations such as deletion and addition are performed on the database table is specifically referred to.

INSERT and UPDATE statements, INSERT statement or UPDATE statement, represent INSERT and UPDATE statements, both SQL proprietary data manipulation language statements (DML). The INSERT statement is used for adding new data into the data table, and the UPDATE statement is used for updating the data of the database table.

The MERGE statement, represents a DML data manipulation language statement supported by Oracle.

DELETE event, which means a DELETE event, means that when a DELETE operation is performed on a database table in which a trigger is built, the trigger monitors a corresponding DELETE event. (in addition to deletion events, there are insertion events, update events, etc.). The basic structure of the trigger at the time of a DELETE event can be described as follows:

TRIGGER trigger_name

triggering_event

[trigger_restriction]

BEGIN

triggered_action；

END.

The AFTER statement level trigger belongs to a post-event-triggering statement level trigger, is one of Oracle triggers, and is used for triggering the statement level for 1 time only AFTER an event of appointed triggering occurs, and executing the content compiled by the trigger. Oracle's statement level triggers are BEFORE statement level triggers and AFTER statement triggers, where BEFORE and AFTER refer to the occasions when responses are triggered. When the table is subjected to data manipulation processing such as adding and deleting, namely DML operation, the BEFORE trigger is triggered BEFORE the DML operation is executed, and conversely, the AFTER trigger is triggered to respond AFTER the table is executed with the DML operation.

The AFTER statement level trigger generates a JDBC binding variable statement according to the type of the DML operation and combining with a system view of an Oracle database, and the method comprises the following steps: firstly, judging the type of a DML operation by an AFTER statement level trigger according to the DML operation; the type of DML operation, among others, includes: updating and inserting operation, deleting operation; when the type of the DML operation is an updating and inserting operation, generating a corresponding whole-row record full-field MERGE binding statement by combining a system view of an Oracle database; when the type of the DML operation is a DELETE operation, a DELETE binding statement is generated in conjunction with the system view of the Oracle database.

The AFTER statement level trigger dynamically generates a corresponding JDBC binding variable statement according to the triggered database table and Oracle system view and the triggered specific operation:

for updating and inserting operation, generating a corresponding whole-line record full-field MERGE binding statement, wherein INSERT and UPDATE statements are not adopted to avoid the situation that remote databases possibly have inconsistency with local database data, and the whole-line record full-field MERGE statement is used to ensure that the adjusted data can be synchronized to each remote database indiscriminately and the data difference is ignored, so that certain execution efficiency is sacrificed but consistency is replaced; the whole line records the whole field MERGE binding statement Merge statement with bind variable, which refers to the MERGE statement with the binding variable. Among them, the MERGE statement is one of the DML (data manipulation language) supported by Oracle, and may be called a MERGE statement. The statement queries another table (target table) according to the connection condition of the table (original data table) or sub-query, and performs UPDATE on the connection condition matching, and cannot perform INSERT in a matching manner. This syntax requires only one full table scan to complete the entire job, with execution efficiency higher than insert+update. Both INSERT and UPDATE statements are SQL proprietary data manipulation language statements (DML). The INSERT statement is used for adding new data into the data table, and the UPDATE statement is used for updating the data of the database table.

For DELETE operations, a corresponding DELETE binding statement is generated. The association condition of the sentence is automatically determined by the main key and the unique key information of the Oracle system view. Meanwhile, the trigger is responsible for calling a PL/SQL Package program Package, automatically establishing remote database connection, transmitting the data recorded by the line trigger to the part of data of the Java program in time, refreshing the data structure counted in the Java program, and executing corresponding synchronous sentences; and using an AFTER statement level trigger, automatically triggering once AFTER performing DML operation of a trigger event on a trigger table, if the local database executes DML statement exception, executing exception operation in the AFTER statement trigger, releasing and cleaning data resources which are counted into a Java data structure in the early stage, cleaning a PL/SQL cache, and controlling the remote database update.

When the method for automatically triggering real-time batch synchronization of remote multi-database data provided by the embodiment of the invention is implemented, in one embodiment, the database table is monitored through the row trigger, when the database table is subjected to DML operation, the row trigger is utilized to record information of all fields after the database table is subjected to the DML operation, a real-time multi-library synchronous PL/SQL program package is called, the information is stored in a cache provided by the real-time multi-library synchronous PL/SQL program package for splicing, and the information of all the fields after splicing is determined, and the method comprises the following steps:

Detecting and recording information of all fields of the database table after DML operation through a row trigger, calling a real-time multi-library synchronous PL/SQL program package, and storing the information of all fields of the database table after DML operation into a cache provided by the real-time multi-library synchronous PL/SQL program package;

analyzing information of all fields of a database table after DML operation in a cache to obtain change data;

and connecting the change data with low byte character '\0' as a separator to form a character string, storing the character string into a VARCHAR2 variable serving as a character buffer area in a real-time multi-library synchronous PL/SQL program package, and splicing the character buffer area to generate spliced full field information.

In the embodiment, the switching between the storage process and the Java context consumes a lot of time, the performance is low, the more the interaction times are, the worse the performance is, in order to improve the execution efficiency, shorten the running time, reduce the switching between the storage process and the Java context, the program uses the characteristics of a PL/SQL Package program Package, and a VARCHAR2 variable with the maximum length is provided for splicing the record data needing to be synchronized, and the spliced full field information is generated for interacting with a Java multi-library synchronous program; the variable is initialized once in the same session and shared globally, so that when each line of the line trigger is triggered, the data information is recorded and spliced, and the interaction with Java context is effectively reduced. VARCHAR2 is the type of Oracle used to store strings, and is an Oracle self-contained tool. Monitoring a database table through a row trigger, recording information of all fields of the database table after DML operation by using the row trigger when the database table is subjected to DML operation, calling a real-time multi-library synchronous PL/SQL program package, storing a cache provided by the real-time multi-library synchronous PL/SQL program package for splicing, and determining spliced full-field information, wherein the method can comprise the following steps of: detecting and recording information of all fields of the database table after DML operation through a row trigger, calling a real-time multi-library synchronous PL/SQL program package, and storing the information of all fields of the database table after DML operation into a cache provided by the real-time multi-library synchronous PL/SQL program package; analyzing information of all fields of a database table after DML operation in a cache to obtain change data; and connecting the change data with low byte character '\0' as a separator to form a character string, storing the character string into a VARCHAR2 variable serving as a character buffer area in a real-time multi-library synchronous PL/SQL program package, and splicing the character buffer area to generate spliced full field information.

The AFTER statement level trigger is spliced and set JDBC binding variable statements through a system view, and a synchronization method in a java multi-library synchronization program is called, and specific full-field information interaction is carried out in the line trigger.

The line trigger takes the changed data as a separator with low byte characters, namely '\0', all field information after line record adjustment is spliced into a character string, and then the character string is compared with a VARCHAR2 variable for character buffering in a real-time multi-library synchronous PL/SQL program package. The algorithm for comparison and refresh is mentioned above. At this time, the modified data is spliced into a string by using the low byte character '\0' as a separator, or buffered in the variable VARCHAR2, or submitted to the StringBuffer variable buffer provided in the java program. The operations are all interactions that occur at the line trigger. The main purpose of the AFTER trigger is to generate a corresponding JDBC binding variable statement according to the ORACLE system view and the detected specific DML operation type, submit the JDBC binding variable statement to a java program, and finally execute the java multi-library synchronization program. The subsequent processing is realized by the function of the java part, and comprises the steps of establishing a thread pool according to remote database connection configuration, establishing database connection, concurrently analyzing the StringBuffer variable cached in the java program for caching the change data, intercepting each change record according to the field number of the record, and submitting the database for execution in batches.

The StringBuffer variable belongs to the StringBuffer class of java language, and the StringBuffer is adopted in the embodiment, so that the characteristics of the StringBuffer are utilized, the StringBuffer variable can be set, and the StringBuffer is convenient for timely recycling the used resources.

Fig. 3 is a schematic diagram of a process of generating spliced full-field information by automatically triggering a method for real-time batch synchronization of remote multi-database data according to an embodiment of the present invention, as shown in fig. 3, when the method for real-time batch synchronization of remote multi-database data according to the embodiment of the present invention is implemented, in an embodiment, the foregoing splicing is performed on a character buffer, so as to generate spliced full-field information, including:

step 301: calculating the byte length of the character strings to be spliced;

step 302: if the byte length of the character string to be spliced exceeds the maximum length limit of the character buffer area, refreshing and writing the data of the character buffer area into a Java data structure, emptying the character buffer area, and writing the character string to be spliced into a corresponding Java data structure;

step 303: if the byte length of the character string to be spliced plus the specified low byte separator length and the byte length of the character buffer area exceed the maximum length limit of the character buffer area, refreshing and writing the data of the character buffer area into a Java data structure, emptying the character buffer area, and writing the character string to be spliced and the specified low byte separator into the character buffer area;

Step 304: if the byte length of the character string to be spliced plus the specified low byte separator length and the byte length of the character buffer area are equal to the maximum length limit of the character buffer area, after the character string of the character buffer area, the character string to be spliced and the specified low byte separator are spliced, refreshing and writing the character string to a Java data structure, and emptying the character buffer area;

step 305: if the byte length of the character string to be spliced plus the specified low byte separator length and the byte length of the character buffer area are smaller than the maximum length limit of the character buffer area, connecting the character string to be spliced plus the specified low byte separator to the character buffer area;

step 306: and generating the spliced full-field information in the character buffer.

In the embodiment, the line record data and the field data are separated by using the low byte character '\0', the byte is unlikely to appear in the data in the application of a general program, the problem of abnormal data processing caused by the appearance of other visible separators in specific data fields is avoided, the low byte character '\0' is adopted, the length is relatively shortest, the buffer variable is beneficial to storing more record line data in the limited length limit, and the processing loss is relatively low in the analysis process.

When each line is triggered, the line trigger connects the changed data into character strings by taking low byte characters '\0' as separators, and calls a method function provided by the PL/SQL package to splice the character buffers, wherein the specific algorithm for splicing comprises the following steps:

calculating the byte length of the character string to be spliced:

if the length exceeds the maximum length limit of the character buffer area, refreshing and writing the data of the character buffer area into a Java data structure, emptying the character buffer area, and writing the spliced character string into the corresponding Java data structure;

if the byte length of the character string to be spliced plus the specific low byte separator length and the byte length of the character buffer area exceed the maximum length limit of the character buffer area, refreshing and writing the data of the character buffer area into a Java data structure, emptying the character buffer area, and writing the character string to be spliced and the specific low byte separator into the character buffer area;

if the byte length of the character string to be spliced plus the specific low byte separator length and the byte length of the character buffer area are equal to the maximum length limit of the character buffer area, after the character string of the character buffer area, the character string to be spliced and the low byte separator are spliced, refreshing and writing the character string to a Java data structure, and emptying the character buffer area;

If the byte length of the character string to be spliced plus the specific low byte separator length and the byte length of the character buffer area are smaller than the maximum length limit of the character buffer area, the character string to be spliced plus the specific low byte separator is connected to the character buffer area.

When the method for automatically triggering real-time batch synchronization of remote multi-database data provided by the embodiment of the invention is implemented, in one embodiment, the method for calling real-time multi-database synchronization PL/SQL program package, connecting the remote multi-database according to the initialization method for connecting configuration encryption information of the remote database and the method for testing whether the remote database can be connected, calling a Java multi-database synchronization program to establish a thread pool, and concurrently executing spliced full-field information according to JDBC binding variable statements transmitted by an AFTER statement level trigger, and synchronously updating the remote multi-database in batches comprises the following steps:

calling a real-time multi-database synchronous PL/SQL program package, determining the connection string configuration decoding of the remote database and connecting the remote multi-database according to an initialization method of the connection configuration encryption information of the remote database and a method for testing whether the remote database can be connected;

the JDBC binding variable statement is transmitted into a Java multi-library synchronous program, and the Java multi-library synchronous program records the spliced full-field information through the StringBuffer variable;

Based on the configuration available in the remote connection data table, the number of remote multi-databases that need to be synchronized is calculated,

a thread pool is established by calling a Java multi-library synchronous program method, and the field number of a record is determined according to the field number of a synchronous database table;

splitting all field information after the string buffer variable buffer is spliced according to the field number and the low byte character of the record as separators, calling an addBatch method of a JDBC binding variable statement every time a record is split, and adding the method into a batch execution list;

executing exeuteBatch once according to the preset counter in the Java program and the set interval record number, submitting a batch of data recorded by addBatch to a remote database, executing the batch of data concurrently on line Cheng Chizhong, and synchronously updating the remote multi-database in batches.

In an embodiment, when performing multi-database synchronization update, a real-time multi-database synchronization PL/SQL package is specifically called, JDBC binding variable statements are transferred to a Java multi-database synchronization program, and connected to a remote multi-database, and spliced database table synchronization data are submitted to the remote multi-database in batches, which may include:

firstly, calling a real-time multi-database synchronous PL/SQL program package, determining the configuration decoding of a remote database connection string according to an initialization method of the remote database connection configuration encryption information and a method for testing whether the remote database can be connected, and connecting the remote multi-database;

Then, the JDBC binding variable statement is transmitted into a Java multi-library synchronous program, and the Java multi-library synchronous program records the spliced full-field information through the StringBuffer variable;

then, based on the configuration available in the remote connection data table, the number of remote multi-databases that need to be synchronized is calculated,

continuing, a method for calling a Java multi-library synchronous program establishes a thread pool, and determines the field number of a record according to the field number of a synchronous database table;

next, splitting all field information after the string buffer variable cache is spliced according to the field number and the low byte character of the record as separators, calling an addBatch method of a JDBC binding variable statement every time a record is split, and adding the method into a batch execution list;

and finally, executing exeutebatch once according to the preset counter in the Java program and the set interval record number, submitting a batch of data recorded by addBattch to a remote database, concurrently executing on line Cheng Chizhong, and synchronously updating the remote multiple databases in batches.

Java multi-library synchronization program mainly used for: establishing database connection according to the input database connection string; establishing a binding statement to be executed according to the imported binding statement; adopting Stringbuffer to record the incoming data, and adopting low byte character '\0' to separate each record and each field; and establishing a thread pool according to the number of remote databases to be updated, concurrently analyzing the incoming data recorded by the StringBuffer, and executing remote database updating by adopting a batch submission mode. The functions of StringBuffer resource release, database connection, statement cleaning release and the like are encapsulated, and the functions are loaded into a local Oracle database through a loadjava command provided by Oracle. StringBuffer is the StringBuffer class of Java language, and the data that pass in through the record of StringBuffer has utilized its characteristics such as changeable and settable size, the resource of convenient in time recycling. The variable cache of the StringBuffer type relates to dml operation, and the data table separated by low byte character '\0' records full field information.

The number of remote database connections is calculated based on the configuration available in the remote connection data table and not by the spliced full field information. In addition, the spliced full field information is connected by low byte character separation, the java multi-library synchronous program determines the field number of one record according to the field number of the synchronous database table, caches the spliced field information according to the field number and the low byte character separation Fu Cafen StringBuffer variable, calls an addband method of jdbc every time one record is split, adds the method into a batch execution list, sets a counter in the java program, executes exeuteBatch every 1000 records, and submits a batch of data recorded by addband to a remote database for execution. The purpose of this is to reduce interactions and to perform efficiently in batches.

The VARCHAR2 is the type of the Oracle database for storing character strings, and is a special tool in the Oracle database.

Loading a Java multi-library synchronous program into a database by using a loadjava command provided by Oracle, and executing source codes of a real-time multi-library synchronous PL/SQL Package program Package in the database for compiling; and configuring the remote database connection string in a designated configuration table in the database by using a remote database connection string initialization module provided by the program package, and executing encryption processing of the remote database connection string through an encryption program provided by the program package. Furthermore, the remote database which needs to be synchronized in real time can be flexibly configured and flexibly adjusted by configuring the configuration table.

After the initialization of the basic steps is completed, a trigger code program is dynamically generated according to a table which needs to be subjected to real-time data synchronization by utilizing a template function provided by a bag body according to actual requirements, and a developer can execute compiling according to the template code.

According to the embodiment of the invention, the context switching and IO loss are reduced as much as possible through a special cache splicing, refreshing, interaction and splitting mode by combining the line trigger, the Oracle package program characteristic and the AFTER statement level trigger. Furthermore, the view tool of the database system is fully utilized to dynamically generate synchronous sentences, the relatively complex specific update synchronous logic is hidden, automatic triggering is realized, batch real-time synchronization is realized, a small amount of partial table record updating is met, and multi-database full table data synchronization is supported. Relatively complex batch timing scheduling is not needed, and timeliness is good.

According to the embodiment of the invention, the database table of the trigger is established, so that the trigger can be automatically triggered when a user carries out a DML sentence; the line trigger stores the field information of trigger line into session cache according to the triggered line record and specific trigger event, and interacts with Java method loaded into Oracle database by using special cache mechanism. The AFTER statement level trigger invokes specific methods for updating the program package in batch in real time, the specific methods can decrypt remote database connection configuration, establish JDBC database connection, perform multithread concurrent processing on the cached data of the record, split the record, and submit and update the remote database in batch according to the incoming automatically generated binding statement. The Java multi-library synchronous program is loaded into an Oracle database and compiled into a real-time multi-library synchronous Oracle PL/SQL program package, two modules only need to be deployed once, and trigger codes need to be automatically generated according to a code generating tool provided by the program package aiming at a database table which specifically needs to be synchronized in real time in batches, and the generated codes are compiled in the database.

Aiming at a specific DML event, the embodiment of the invention automatically generates a corresponding JDBC binding sentence for compiling a Java function loaded into Oracle according to an Oracle system view, and automatically calculates the number of fields related to each record cached by a spliced character string. The method comprises the steps that an INSERT event and an UPDATE event generate corresponding MERGE sentences to synchronously UPDATE all fields of data table row records, and the number of table column fields is recorded as the number of fields involved in each record of a spliced character string cache; the DELETE event automatically generates a corresponding DELETE statement according to the primary key information of the table, or the configured unique field, and caches the number of fields involved in each record according to the unique field number record concatenation string.

Furthermore, the embodiment of the invention adopts a mode of combining a line trigger, oracle Package program Package characteristics and an AFTER statement level trigger, wherein the line trigger is used for storing trigger data, and a special low byte symbol is adopted for splicing various fields related to line record. The Oracle package provides a long string for caching and builds a mechanism for special cache flushing and interaction with the Java context. The AFTER trigger is used for calling the PL/SQL program package, automatically establishing remote database connection, transmitting the data recorded by the line trigger and not transmitted to the Java program in time, refreshing the data structure which is counted in the Java program, executing corresponding synchronous sentences, simultaneously, using the AFTER sentence level trigger, automatically triggering once AFTER DML operation, if the local database executes DML sentence abnormality, executing abnormal operation in the AFTER sentence, releasing and cleaning the data resources which are counted in the Java data structure in the early stage, cleaning the PL/SQL cache, and controlling not to update the remote database.

Furthermore, in order to adapt to mass data updating, the embodiment of the invention improves the efficiency of data real-time synchronization as much as possible, fully utilizes the characteristics of the PACKAGE program PACKAGE in the Oracle storage process, establishes a cache long field, and splices each field of synchronous data by utilizing invisible low byte with special meaning, thereby reducing the switching between Oracle and Java context and IO as much as possible, reducing the occupation of resources, improving the processing efficiency and adopting a special cache interaction and refreshing mechanism

Furthermore, the embodiment of the invention automatically decodes and connects to establish a connection pool according to the remote database connection string configuration, and avoids triggering database connection and disconnection operations by progressive recording. When the remote databases are synchronously updated, different remote databases are processed in a multithreading way to improve the efficiency, and a batch processing mechanism of JDBC is utilized to greatly improve the efficiency and shorten the processing time.

Furthermore, the embodiment of the invention has the characteristic of flexible expansion, and a module for truly carrying out synchronous call of a remote database is an AFTER statement level trigger, and in the current design, a synchronous call mode is adopted for ensuring the consistency and the transactional property of multi-database data. In fact, it is also possible to explore the task scheduling package using Oracle for asynchronous processing (asynchronous task submission, immediate execution), so that synchronization of local data tables can be performed without any sense of time loss. The user can perform characteristic maintenance and function expansion on the generated trigger code according to the self requirement.

In order to facilitate the expansion and use, the design encapsulates a specific synchronization process, a fixed processing mode is adopted, the line trigger records each field information of synchronous data, for the AFTER line trigger, only a packaged process function is required to be called and characteristic data is transmitted, the trigger code automatic generation module provided by the program package can automatically generate corresponding trigger codes according to the table name of the transmitted database by utilizing the Oracle system view, and the output standard codes only need to be slightly checked and adjusted to be compiled and used, so that the processing difficulty of different data sources is greatly reduced, the real-time synchronization automatic triggering is not required to be performed in batches, the timeliness is higher, the transaction processing is increased, and the consistency of multi-database data is ensured.

Row trigger performance optimization, comprising: the method has the advantages that a large amount of time is consumed in connection and disconnection of a database, the performance is low, the interaction times are more, the performance is worse, the execution efficiency is improved, the running time is shortened, the storage process and the java context are switched, the characteristics of a PL/SQL package program are utilized by a program, a VARCHAR2 variable with the maximum length is provided for splicing recorded data which needs to be synchronized, the variable is initialized once in the same session and is globally shared, when each row of a trigger is triggered, data information is recorded and spliced, and interaction with the java context is effectively reduced.

The line record data and the field data are separated by using the low byte 0, the byte is unlikely to appear in the data in the application of a general program, the problem of abnormal data processing caused by the appearance of other visible separators in specific data fields is avoided, the character is adopted, the length is relatively shortest, the buffer variable is beneficial to storing more record line data within the limit of the limited length, and the processing loss is relatively low in the analysis process.

When each line is triggered, the line trigger takes the low byte character 0 as a separator to connect changed data into character strings, and calls a method function provided by the PL/SQL package to splice character buffers, and the specific algorithm of the splicing is as follows:

calculating the byte length of the character string to be spliced:

The database table for synchronization should meet the requirements of light weight and establishment of a primary key or a unique index so as to ensure real-time synchronization efficiency.

The database table for synchronization should meet the requirements of light weight and establishment of primary key or unique index so as to ensure real-time synchronization efficiency

The embodiment of the invention fully utilizes various characteristics provided by Oracle, encapsulates and conceals specific synchronous processing, and the trigger part only relates to the storage of trigger data and the calling of a program package related method, and the calling of a specified function transmits the data related to change to an internal data structure and the exception handling of a fixed mode, and provides a template program for automatically generating the trigger code, thereby being convenient for automatic expansion and use. After the embodiment of the invention is deployed, a developer only needs to determine the data table which needs to be synchronized in real time in batches, and generates the corresponding trigger code by utilizing a tool provided by the program package and executes the trigger code in the command window, so that the real-time batch synchronization setting of the table can be completed, specific implementation details are not needed to be known, and the special processing is carried out on each table, so that the development workload is greatly reduced, the performance is excellent, and the requirement of on-line maintenance can be basically met.

Similarly, although the embodiment of the invention is realized based on the Oracle database, other databases have similar corresponding characteristics, and the data of the lightweight code table can also be updated automatically and synchronously in real time by referring to the processing thinking and mode.

Fig. 4 is a schematic diagram of a computer device for executing a method for automatically triggering real-time batch synchronization of remote multi-database data according to the embodiment of the present invention, and as shown in fig. 4, an embodiment of the present invention further provides a computer device, including a memory, a processor, and a computer program stored in the memory and capable of running on the processor, where the processor implements the method for automatically triggering real-time batch synchronization of remote multi-database data when executing the computer program.

The embodiment of the invention also provides a computer readable storage medium which stores a computer program for implementing the method for automatically triggering the real-time batch synchronization of the remote multi-database data.

The embodiment of the invention also provides a device for automatically triggering real-time batch synchronization of remote multi-database data, as described in the following embodiment. Because the principle of the device for solving the problem is similar to that of automatic triggering of real-time batch synchronization of the remote multi-database data, the implementation of the device can refer to the implementation of an automatic triggering of real-time batch synchronization method of the remote multi-database data, and the repetition is omitted.

Fig. 5 is a schematic diagram of an automatic triggering real-time batch synchronization device for remote multi-database data according to an embodiment of the present invention, and as shown in fig. 5, the embodiment of the present invention further provides an automatic triggering real-time batch synchronization device for remote multi-database data, including:

a Java multi-library synchronization program compiling module 501, configured to load a Java multi-library synchronization program into an Oracle database for compiling;

the remote multi-database connection method configuration module 502 is configured to execute a real-time multi-database synchronization PL/SQL package in an Oracle database, call a Java multi-database synchronization program, determine an initialization method for connecting configuration encryption information of a remote database and test whether a method for connecting the remote database can be performed;

the spliced full-field information determining module 503 is configured to monitor the database table through the line trigger, record information of all fields after DML operation is performed on the database table by using the line trigger when DML operation is performed on the database table, call the real-time multi-library synchronous PL/SQL package, store a cache provided by the real-time multi-library synchronous PL/SQL package, and splice the information of all the fields after splicing;

the JDBC binding variable statement generation module 504 is configured to generate a JDBC binding variable statement by combining a system view of the Oracle database according to the type of DML operation by using the AFTER statement level trigger;

The spliced full-field information extraction module 505 is configured to extract spliced full-field information from the cache through an AFTER statement level trigger, and transmit the extracted full-field information to a Java multi-library synchronization program for interaction;

the remote multi-database synchronous updating module 506 is configured to call a real-time multi-database synchronous PL/SQL program package, connect the remote multi-databases according to an initialization method for configuring encryption information and a method for testing whether the remote databases can be connected, call a method for Java multi-database synchronous program to establish a thread pool, and execute the spliced full-field information concurrently according to JDBC binding variable statements transmitted by an AFTER statement level trigger, and update the remote multi-databases synchronously in batch.

When the device for automatically triggering real-time batch synchronization of remote multi-database data provided by the embodiment of the invention is implemented, in one embodiment, the foregoing Java multi-database synchronization program compiling module is specifically configured to:

When the device for automatically triggering the real-time batch synchronization of the remote multi-database data provided by the embodiment of the invention is implemented, in one embodiment, the remote multi-database connection method configuration module is specifically used for:

When the device for automatically triggering real-time batch synchronization of remote multi-database data provided by the embodiment of the invention is implemented, in one embodiment, the JDBC binding variable statement includes: recording a full field MERGE binding statement in the whole row, and performing DELETE binding statement;

the JDBC binding variable statement generation module is specifically used for:

the AFTER statement level trigger judges the type of the DML operation according to the DML operation; the type of DML operation, among others, includes: updating and inserting operation, deleting operation;

when the type of the DML operation is an updating and inserting operation, generating a corresponding whole-row record full-field MERGE binding statement by combining a system view of an Oracle database;

when the type of the DML operation is a DELETE operation, a DELETE binding statement is generated in conjunction with the system view of the Oracle database.

When the automatic triggering real-time batch synchronization device for remote multi-database data provided by the embodiment of the invention is implemented, in one embodiment, the spliced full-field information determining module is specifically used for:

When the device for automatically triggering real-time batch synchronization of remote multi-database data provided by the embodiment of the invention is implemented, in one embodiment, the spliced full-field information determining module is further used for:

calculating the byte length of the character strings to be spliced;

if the byte length of the character string to be spliced exceeds the maximum length limit of the character buffer area, refreshing and writing the data of the character buffer area into a Java data structure, emptying the character buffer area, and writing the character string to be spliced into a corresponding Java data structure;

If the byte length of the character string to be spliced plus the specified low byte separator length and the byte length of the character buffer area exceed the maximum length limit of the character buffer area, refreshing and writing the data of the character buffer area into a Java data structure, emptying the character buffer area, and writing the character string to be spliced and the specified low byte separator into the character buffer area;

if the byte length of the character string to be spliced plus the specified low byte separator length and the byte length of the character buffer area are equal to the maximum length limit of the character buffer area, after the character string of the character buffer area, the character string to be spliced and the specified low byte separator are spliced, refreshing and writing the character string to a Java data structure, and emptying the character buffer area;

if the byte length of the character string to be spliced plus the specified low byte separator length and the byte length of the character buffer area are smaller than the maximum length limit of the character buffer area, connecting the character string to be spliced plus the specified low byte separator to the character buffer area;

and generating the spliced full-field information in the character buffer.

When the device for automatically triggering the real-time batch synchronization of the remote multi-database data provided by the embodiment of the invention is implemented, in one embodiment, the remote multi-database synchronization updating module is specifically used for:

In summary, the method and the device for automatically triggering real-time batch synchronization of remote multi-database data provided by the embodiment of the invention comprise the following steps: firstly, loading a Java multi-library synchronous program into an Oracle database for compiling; then executing a real-time multi-library synchronous PL/SQL program package in an Oracle database, calling a Java multi-library synchronous program, and determining an initialization method of the remote database connection configuration encryption information and a method for testing whether the remote database can be connected; then monitoring a database table through a row trigger, when the database table is subjected to DML operation, recording information of all fields of the database table after the DML operation by utilizing the row trigger, calling a real-time multi-library synchronous PL/SQL program package, storing a cache provided by the real-time multi-library synchronous PL/SQL program package for splicing, and determining spliced full-field information; the next step of an AFTER statement level trigger generates a JDBC binding variable statement according to the type of the DML operation and combining a system view of an Oracle database; then extracting the spliced full-field information from the cache through an AFTER statement level trigger, and transmitting the full-field information to a Java multi-library synchronous program for interaction; and finally, calling a real-time multi-library synchronous PL/SQL program package, connecting the remote multi-database according to an initialization method for connecting configuration encryption information of the remote database and a method for testing whether the remote database can be connected, calling a Java multi-library synchronous program to establish a thread pool, and concurrently executing the spliced full-field information according to JDBC binding variable statements transmitted by an AFTER statement level trigger to synchronously update the remote multi-database in batches. According to the embodiment of the invention, the Java multi-library synchronization program is loaded into the Oracle database, specific synchronization processing logic is encapsulated and hidden, the line trigger realizes the storage of trigger synchronization data and the calling of a related method of the real-time multi-library synchronization PL/SQL program package, automatic triggering and batch real-time synchronization are realized, a small amount of partial table record updating is met, the multi-database full-table data synchronization is supported, relatively complex batch timing scheduling is not needed, and the timeliness is good.

It will be appreciated by those skilled in the art that embodiments of the present invention may be provided as a method, system, or computer program product. Accordingly, the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present invention may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.

The present invention is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems) and computer program products according to embodiments of the invention. It will be understood that each flow and/or block of the flowchart illustrations and/or block diagrams, and combinations of flows and/or blocks in the flowchart illustrations and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

The foregoing description of the embodiments has been provided for the purpose of illustrating the general principles of the invention, and is not meant to limit the scope of the invention, but to limit the invention to the particular embodiments, and any modifications, equivalents, improvements, etc. that fall within the spirit and principles of the invention are intended to be included within the scope of the invention.

Claims

1. An automatic triggering real-time batch synchronization method for remote multi-database data is characterized by comprising the following steps:

2. The method of claim 1, wherein loading the Java multi-library synchronization program into the Oracle database for compilation, comprises:

3. The method of claim 1, wherein executing a real-time multi-library synchronization PL/SQL package in an Oracle database, invoking a Java multi-library synchronization program, determining an initialization method for remote database connection configuration encryption information and a method for testing whether a remote database can be connected, comprises:

4. The method of claim 1, wherein JDBC binds variable statements, comprising: recording a full field MERGE binding statement in the whole row, and performing DELETE binding statement;

5. The method of claim 1, wherein monitoring the database table through the row trigger, recording information of all fields of the database table after DML operation by the row trigger when DML operation occurs in the database table, calling a real-time multi-library synchronous PL/SQL package, storing a cache provided by the real-time multi-library synchronous PL/SQL package for splicing, and determining spliced full-field information, comprising:

6. The method of claim 5, wherein splicing the character buffers to generate spliced full field information comprises:

calculating the byte length of the character strings to be spliced;

and generating the spliced full-field information in the character buffer.

7. The method of claim 6, wherein invoking the real-time multi-library synchronization PL/SQL package, connecting the remote multi-databases according to an initialization method for connecting configuration encryption information of the remote databases and a method for testing whether the remote databases can be connected, invoking a method for Java multi-library synchronization program to build a thread pool, and concurrently executing the spliced full field information according to JDBC binding variable statements entered by an AFTER statement level trigger, and batch synchronizing updating the remote multi-databases, comprising:

8. An automatic triggering real-time batch synchronization device for remote multi-database data, which is characterized by comprising:

9. The apparatus of claim 8, wherein the Java multi-library synchronization program compilation module is specifically configured to:

10. The apparatus of claim 8, wherein the remote multi-database connection method configuration module is specifically configured to:

11. The apparatus of claim 8, wherein JDBC binds variable statements, comprises: recording a full field MERGE binding statement in the whole row, and performing DELETE binding statement;

the JDBC binding variable statement generation module is specifically used for:

12. The apparatus of claim 8, wherein the spliced full field information determining module is specifically configured to:

13. The apparatus of claim 12, wherein the spliced full field information determination module is further configured to:

calculating the byte length of the character strings to be spliced;

and generating the spliced full-field information in the character buffer.

14. The apparatus of claim 13, wherein the remote multi-database synchronization update module is configured to:

15. A computer device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, characterized in that the processor implements the method of any of claims 1 to 7 when executing the computer program.

16. A computer-readable storage medium, characterized in that the computer-readable storage medium stores a computer program, which, when executed by a processor, implements the steps of the method according to any one of claims 1 to 7.