Table real-time synchronization method based on spring and myabits frameworks

Abstract

The invention discloses a real-time table synchronization method based on spring and myabits frameworks, which belongs to the technical field of data processing, and comprises the following steps: extracting a table name from an sql statement, and setting a synchronization table list to an sql context manager according to the table name; the mybatis layer interceptor obtains a result of executing the sql statement by using the main data source and returns the result to the Dao layer interceptor, and a synchronization table list is extracted from the sql context manager; the Dao layer interceptor sets a slave library code in an sql context manager; a slave library connection is obtained according to a slave library code set in the sql context manager, the mybatis layer interceptor uses the slave library connection to execute the sql statement, and data synchronization is carried out on a slave library corresponding to the slave library connection; and after all library synchronization operations are completed, returning an execution result to a dao layer method name by the dao layer interceptor. According to the real-time table synchronization method based on spring and myabits frameworks, the problems that in an existing table synchronization method, a programmer needs to write service codes additionally, delay exists, and table synchronization is difficult to conduct between different databases are solved.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a table real-time synchronization method based on Spring and MyAbits framework. Background Art

[0002] In business, the same table will need to be synchronized in multiple databases, that is, the master database will be synchronized in multiple slave databases (synchronization databases). There are currently two main solutions for table synchronization on the market: the first solution is to use database tools to analyze the database log (such as MySQL's binlog) and synchronize the additions, deletions, and modifications to other databases; the second solution is for programmers to add code in the project, such as Figure 1 As shown, the add, delete, and modify operations on table A1 in db1 must be synchronized to table A1 in bd2.

[0003] Before synchronization, the pseudo code added to table A1 is: insertA1(xxxx);

[0004] To synchronize, the pseudo code needs to be changed to:

[0005] db1.insertA1(xxxx);

[0006] db2.insertA1(xxxx);

[0007] In reality, the implementation requires much more code than the pseudocode.

[0008] The first solution has the following issues: 1. Synchronization is delayed. For example, if an operation is performed on table A1 in the database database 1 and then a query is performed on table A1 in database 2, the synchronization delay may prevent the newly added record from being found in database 2. 2. If databases db2 and db1 are different, this solution will not work.

[0009] For the second option, if Figure 1 db3 needs to synchronize the additions, deletions, and modifications of table A3 in db1. In this case, the programmer needs to manually add code, which is extremely inconvenient and prone to errors. Summary of the Invention

[0010] In order to overcome the defects of the prior art, the present invention provides a table real-time synchronization method based on spring and myabits framework to solve the above problems.

[0011] The technical solution adopted by the present invention to solve the technical problem is: a real-time table synchronization method based on Spring and MyAbits framework, comprising the following steps:

[0012] S1: When the call reaches the dao layer interceptor, the dao layer interceptor calls the dao layer method body. For a non-empty synchronization table list, the mybatis layer interceptor obtains the SQL statement to be executed through the parameter invocation in the interface method intercept(Invocation invocation). For the SQL statement to be executed with the content of addition, deletion, and modification, the table name is extracted from the SQL statement, and then the corresponding slave library code is obtained according to the extracted table name to form a synchronization table list and set it to the SQL context manager;

[0013] S2: The mybatis layer interceptor obtains the main data source through the multi-data source manager (MultiDataSource). The mybatis layer interceptor uses the obtained main data source to execute the SQL statement and obtain the execution result of the SQL statement returned from the main database; then, after passing through the mybatis layer interceptor and the dao layer method body in turn, the result is returned to the dao layer interceptor.

[0014] S3: The Dao layer interceptor retrieves the synchronization table list from the SQL context manager;

[0015] S4: Suspend the spring transaction through the Dao layer interceptor; the Dao layer interceptor obtains the slave library code of the slave library that needs data synchronization; sets the slave library code of the slave library that needs data synchronization in the SQL context manager, and then calls the Dao layer method body;

[0016] S5: The Mybatis layer interceptor calls the multi-data source manager. The multi-data source manager obtains the slave connection according to the slave code set in the SQL context manager. The Mybatis layer interceptor uses the slave connection to execute SQL statements and synchronizes data with the slave corresponding to the slave connection.

[0017] S6: After completing all library synchronization operations, the suspended current spring transaction is resumed through the Dao layer interceptor, and then the Dao layer interceptor returns the execution result to the Dao layer method name.

[0018] Optionally, in step S4, if the slave library code can be obtained, the holdOn method of the manager that processes the spring transaction is called to suspend the current spring transaction so that the multi-data source manager is run when the Dao layer interceptor calls the Dao layer method.

[0019] Optionally, in step S6, after using the multi-data source manager to retrieve the corresponding data source according to the slave code, the restore method of the manager that handles the spring transaction is called to restore the suspended current spring transaction; finally, the slave code set in the sql context manager is cleared.

[0020] Specifically, in step S1, the information in the synchronization table list in the mybatis layer interceptor includes the table name and the slave library code of the slave library whose data needs to be synchronized with the table name.

[0021] It is worth noting that, in step S4, the Dao layer interceptor traverses the synchronization table list to obtain the slave library code of the slave library that needs data synchronization.

[0022] Preferably, the synchronization table list is set by the multi-data source manager when the project is started.

[0023] Specifically, for the multi-data source manager, when the project starts, the configuration is read, and the configuration includes the synchronization data source configuration jdbc.writeSyncDbConfig. The synchronization data source configuration jdbc.writeSyncDbConfig is represented by "tableDbList":[{"tableName":"X",dblist:["db1"]} to configure table X to perform data synchronization for addition, deletion, and modification in the slave library code-named db1.

[0024] The beneficial effect of the present invention is that: in the table real-time synchronization method based on the spring and myabits framework, whether there is a table that needs to be synchronized is first determined by whether the synchronization table list is empty. If there is a table that needs to be synchronized (the synchronization table list is not empty), for the first call of the dao layer (the sql context manager has no slave library code), the corresponding synchronization table list containing the slave library code is first set to the sql context manager according to the sql statement; one table may be synchronized in multiple data sources; after analyzing the sql statement, it will return to the dao layer interceptor, and the dao layer interceptor obtains the slave library code of the data source of the table that needs to be synchronized through the sql context manager and then executes the dao layer method multiple times. Each execution will use the sql context manager to set the slave library code to be used. Each time the execution goes to the multi-data source manager, the multi-data source manager then takes out the corresponding data source according to the slave library code set in the current sql context manager, and finally completes the data synchronization of all slave libraries. Compared with traditional table synchronization operations, this solution saves programmers the trouble of setting up additional business code. When calling the insertA1() method, the method will not return until the synchronization operation is completed. Because the synchronization operation is included in the call to the insertA1(xxxx) method, the table is immediately queried after calling the insertA1(xxxx) method, and there will be no delay issues. The principle of this solution is to call different data sources to execute the same SQL statement, so it can achieve table synchronization between databases of different products. BRIEF DESCRIPTION OF THE DRAWINGS

[0025] Figure 1 Schematic diagram of data sources db1, db2 and db3 in the prior art;

[0026] Figure 2 This is a flowchart of a method for real-time synchronization of tables based on the Spring and MyAbits frameworks in one embodiment of the present invention;

[0027] Figure 3 This is a diagram showing the composition of a system corresponding to a method for real-time table synchronization based on the Spring and MyAbits frameworks in one embodiment of the present invention;

[0028] Figure 4 A schematic diagram of the hierarchical relationship when calling the Dao layer in one embodiment of the present invention;

[0029] Figure 5 A diagram showing the relationship between a method name and a method body in an embodiment of the present invention;

[0030] Figure 6 A diagram showing the relationship between a method name and a method body in another embodiment of the present invention;

[0031] Figure 7 A diagram showing the relationship between method names, aspects, and method bodies in one embodiment of the present invention;

[0032] Figure 8 FIG. 1 is a schematic diagram of a multi-data source manager in one embodiment of the present invention. DETAILED DESCRIPTION

[0033] The following is a further description of specific embodiments of the present invention in conjunction with the accompanying drawings. It should be noted that the description of these embodiments is intended to facilitate understanding of the present invention and does not constitute a limitation of the present invention. In addition, the technical features involved in the various embodiments of the present invention described below may be combined with each other as long as they do not conflict with each other.

[0034] like Figure 2-8 As shown in the figure, a table real-time synchronization method based on Spring and MyAbits framework includes the following steps:

[0035] S1: The service layer calls the dao layer method name; the dao layer method name calls the dao layer interceptor; when the call reaches the dao layer interceptor (DaoInterceptor), the dao layer interceptor calls the dao layer method body. For a non-empty synchronization table list, if the slave library code cannot be obtained from the sql context manager (sqlContext) (indicating that it is the first call of the dao layer), the mybatis layer interceptor (StatementHandlerInterceptor) obtains the sql statement to be executed through the parameter invocation in the interface method intercept(Invocationinvocation) to determine whether the sql statement to be executed is an addition, deletion, or modification statement; in this embodiment, intercept(Invocation invocation) is a method in the mybatis layer interceptor, and invocation is a parameter in intercept(Invocation invocation); for the sql statement to be executed whose content is addition, deletion, or modification, the table name is extracted from the sql statement, and then the corresponding slave library code is obtained according to the extracted table name to form a synchronization table list and set it to the sql context manager;

[0036] S2: The mybatis layer interceptor obtains the main data source through the multi-data source manager (MultiDataSource). The mybatis layer interceptor uses the obtained main data source to execute the SQL statement and obtain the execution result of the SQL statement returned from the main database; then, after passing through the mybatis layer interceptor and the dao layer method body in sequence, the result is returned to the dao layer interceptor. Specifically, the multi-data source manager returns the result to the mybatis layer interceptor, the mybatis layer interceptor returns the result to the dao layer method body, and the dao layer method body returns the result to the dao layer interceptor;

[0037] S3: The Dao layer interceptor retrieves the synchronization table list from the SQL context manager;

[0038] S4: Suspend the spring transaction through the Dao layer interceptor;

[0039] The Dao layer interceptor obtains the slave database code of the slave database (synchronous database) that needs data synchronization;

[0040] Call the sql context manager, set the slave database code of the slave database that needs data synchronization in the sql context manager, and then call the Dao layer method body;

[0041] S5: The mybatis layer interceptor calls the multi-data source manager. The multi-data source manager obtains the slave connection according to the slave code set in the sql context manager. The mybatis layer interceptor uses the slave connection to execute the sql statement and synchronizes the data of the slave corresponding to the slave connection. After the slave is executed, it returns to the multi-data source manager. The multi-data source manager returns to the mybatis layer interceptor. The mybatis layer interceptor returns the dao layer method body. The mybatis layer interceptor uses the next slave connection to execute the sql statement, synchronizes the data of the slave corresponding to the slave connection, and then returns to the multi-data source manager.

[0042] S6: After calling the Dao layer method body to complete all library synchronization operations, it returns to the Dao layer interceptor, and resumes the suspended current spring transaction through the Dao layer interceptor. Then the Dao layer interceptor returns the execution result to the Dao layer method name.

[0043] In the table real-time synchronization method based on the spring and myabits framework, it is first determined whether there is a table that needs to be synchronized by checking whether the synchronization table list is empty. If there is a table that needs to be synchronized (the synchronization table list is not empty), for the first call of the dao layer (the sql context manager does not have a slave library code), the corresponding synchronization table list containing the slave library code is first set to the sql context manager according to the sql statement; one of the tables may be synchronized in multiple data sources; after analyzing the sql statement, it will return to the dao layer interceptor, and the dao layer interceptor obtains the slave library code of the data source that the table needs to be synchronized through the sql context manager and then executes the dao layer method multiple times. Each execution will use the sql context manager to set the slave library code to be used. Each time the execution goes to the multi-data source manager, the multi-data source manager then takes out the corresponding data source according to the slave library code set in the current sql context manager, and finally completes the data synchronization of all slave libraries. Compared with traditional table synchronization operations, this solution saves programmers the trouble of setting up additional business code. When calling the insertA1() method, the method will not return until the synchronization operation is completed. Because the synchronization operation is included in the call to the insertA1(xxxx) method, the table is immediately queried after calling the insertA1(xxxx) method, and there will be no delay issues. The principle of this solution is to call different data sources to execute the same SQL statement, so it can achieve table synchronization between databases of different products.

[0044] like Figure 3As shown in the figure, the system of this solution consists of four parts: Dao layer interceptor (DaoInterceptor), mybatis layer interceptor (StatementHandlerInterceptor), multi-data source manager (MultiDataSource) and sql context manager (sqlContext). Figure 4 As shown, the Dao layer is the business code, which is generally called by the service layer. The hierarchical relationship when calling the Dao layer is: Service->DaoInterceptor->Dao->Mybatis->StatementHandlerInterceptor->

[0045] MultiDataSource. Figure 5 As shown, in this embodiment, for a method, it is divided into two parts: one is the method name, and the other is the method body. For example: under the Spring+Mybatis framework, the developer writes a method name such as "public int insertMerchantList(String sessionKey,String appCode)", and then writes the corresponding sql of this method in the xml file according to the requirements of mybatis. The Spring+Mybatis framework will generate a method body for each sql in the xml file for the corresponding method name when the project starts (such as Figure 6 ), these method bodies are Dao layer methods. In this case, the method name is equal to the method body. So when the service layer calls these method names, it is equivalent to calling the Dao layer. When the Dao layer interceptor is added, Figure 7 As shown, the aspect is a Dao-layer interceptor. When the service layer calls a method name, it first calls the aspect, which then calls the method body. The logic of the "Dao-layer interceptor" in this solution is that the aspect first calls the method body, this time using the primary data source and analyzing whether any tables require synchronization. If synchronization is required, the aspect then calls the method body multiple times to access other data sources to be synchronized. The logic in the method body for each method name is different, but the logic in the aspect is the same for all method names.

[0046] It is worth noting that in step S4, if the slave library code can be obtained, the holdOn method of the manager (MasterDataSourceTransactionHolder) that handles the spring transaction is called to suspend the current spring transaction so that the multi-data source manager is run when the Dao layer interceptor calls the Dao layer method.

[0047] Preferably, in step S6, the restore method of the manager (MasterDataSourceTransactionHolder) that processes the spring transaction is called to restore the suspended current spring transaction; finally, the slave library code set in the sql context manager is cleared.

[0048] Regarding the Spring transaction manager, if Spring transactions are enabled at the service layer, calls will not ultimately be made to the multi-data source manager. Instead, the Spring transaction manager will retrieve the database connection. Therefore, if you want to use the multi-data source manager, you must first suspend the Spring transaction. The holdOn method of the Spring transaction manager primarily calls the Spring framework's TransactionSynchronizationManager.unbindResource method to suspend the Spring transaction. The restore method of the Spring transaction manager primarily calls the Spring framework's TransactionSynchronizationManager.bindResource method to resume the transaction.

[0049] Optionally, the information in the synchronization table list includes a table name and a slave library code of a slave library whose data needs to be synchronized with the table name.

[0050] Specifically, in step S4, the Dao layer interceptor traverses the synchronization table list to obtain the slave library code of the slave library that needs data synchronization.

[0051] The data format of the information in the synchronization table list is as follows: [{"tableName":"Table A",dblist:["db1","db2","db3"]},{"tableName":"Table B",dblist:["db2","db3"]}], which means that table A needs to be synchronized in databases db1, db2, and db3, and table B needs to be synchronized in databases db2 and db3.

[0052] It is worth noting that the synchronization table list is set by the multi-data source manager when the project is started.

[0053] Preferably, for the multi-data source manager, when the project is started, the configuration is read, and the configuration includes the synchronization data source configuration jdbc.writeSyncDbConfig, and the synchronization data source configuration jdbc.writeSyncDbConfig is represented by "tableDbList":[{"tableName":"X",dblist:["db1"]} to configure table X to perform data synchronization of additions, deletions, and modifications in the slave library code db1.

[0054] The Multi-Data Source Manager is used to manage multiple data sources. Figure 8 As shown, the MultiDataSource Manager consists of a master data source (master) and a write synchronization data source (writeSyncDbs). There is only one master data source, but multiple write synchronization data sources. When the project starts, the MultiDataSource (MultiDataSource Manager) reads the configuration, which is as follows:

[0055] jdbc.driver = A (driver A of the primary data source)

[0056] jdbc.password = B (the password of the primary data source B)

[0057] jdbc.url = C (address C of the primary data source)

[0058] jdbc.username = D (the primary data source account)

[0059] jdbc.writeSyncDbConfig=xxxx

[0060] jdbc.writeSyncDbConfig is the configuration of the synchronous data source, the format is as follows:

[0061] {

[0062] "dbConfigs":{

[0063] "db1":"${db1config}",

[0064] "db2":"${db2config}",

[0065] "db3":"${db3config}"

[0066] },

[0067] "tableDbList":

[0068] [{"tableName":"X",dblist:["db1"]},{"tableName":"Y",dblist:["db2","db3"]}]

[0069] }

[0070] After the multi-data source manager reads the configuration, it first initializes the master data source master, and then initializes all write synchronization data sources writeSyncDbs according to dbConfigs. Finally, the mybatis layer interceptor is called and tableDbList is set to the synchronization table list of the mybatis layer interceptor. When the call goes to the multi-data source manager, the multi-data source manager first retrieves the slave library code from the sql context manager. If the obtained slave library code is empty, the master data source is returned. If it is not empty, the corresponding synchronization data source is retrieved according to the slave library code and returned. The sql context manager internally maintains a ThreadLocal object provided by jdk, which can save variables in the currently executing thread. The sql context manager maintains two variables: the synchronization table list and the slave library code.

[0071] The embodiments of the present invention are described in detail above with reference to the accompanying drawings, but the present invention is not limited to the described embodiments. It is apparent to those skilled in the art that various changes, modifications, substitutions, and variations to these embodiments may be made without departing from the principles and spirit of the present invention, and these changes and modifications still fall within the scope of protection of the present invention.

Claims

1. A table real-time synchronization method based on spring and myabits framework, characterized in that: The following steps are involved: S1: When the call reaches the dao layer interceptor, the dao layer interceptor calls the dao layer method body. For a non-empty synchronization table list, the mybatis layer interceptor obtains the SQL statement to be executed through the parameter invocation in the interface method intercept(Invocation invocation). For the SQL statement to be executed with the content of addition, deletion, and modification, the table name is extracted from the SQL statement, and then the corresponding slave library code is obtained according to the extracted table name to form a synchronization table list and set it to the SQL context manager; S2: The mybatis layer interceptor obtains the main data source through the multi-data source manager. The mybatis layer interceptor uses the obtained main data source to execute the SQL statement and obtains the execution result of the SQL statement returned from the main database; then, after passing through the mybatis layer interceptor and the dao layer method body in turn, the result is returned to the dao layer interceptor. S3: The Dao layer interceptor retrieves the synchronization table list from the SQL context manager; S4: Suspend the spring transaction through the Dao layer interceptor; the Dao layer interceptor obtains the slave library code of the slave library that needs data synchronization; sets the slave library code of the slave library that needs data synchronization in the SQL context manager, and then calls the Dao layer method body; S5: The Mybatis layer interceptor calls the multi-data source manager. The multi-data source manager obtains the slave connection according to the slave code set in the SQL context manager. The Mybatis layer interceptor uses the slave connection to execute SQL statements and synchronizes data with the slave corresponding to the slave connection. S6: After completing all library synchronization operations, the suspended current spring transaction is resumed through the Dao layer interceptor, and then the Dao layer interceptor returns the execution result to the Dao layer method name.

2. The method for real-time table synchronization based on the spring and myabits framework according to claim 1, characterized in that: In step S4, if the slave library code can be obtained, the holdOn method of the manager that processes the spring transaction is called to suspend the current spring transaction so that the multi-data source manager is run when the Dao layer interceptor calls the Dao layer method.

3. The method for real-time table synchronization based on the spring and myabits framework according to claim 2, characterized in that: In step S6, after using the multi-data source manager to retrieve the corresponding data source according to the slave code, the restore method of the manager that processes the spring transaction is called to restore the suspended current spring transaction; finally, the slave code set in the sql context manager is cleared.

4. The method for real-time table synchronization based on the spring and myabits framework according to claim 1, characterized in that: In the step S1, the information in the synchronization table list in the mybatis layer interceptor includes the table name and the slave library code of the slave library whose data needs to be synchronized with the table name.

5. The method for real-time table synchronization based on the spring and myabits framework according to claim 4, characterized in that: In step S4, the Dao layer interceptor traverses the synchronization table list to obtain the slave library code of the slave library that needs data synchronization.

6. The method for real-time table synchronization based on Spring and MyAbits framework according to claim 1, characterized in that: The synchronization table list is set by the multi-data source manager when the project is started.

7. The method for real-time table synchronization based on the spring and myabits framework according to claim 6, characterized in that: For the multi-data source manager, when the project starts, the configuration is read, and the configuration includes the synchronization data source configuration jdbc.writeSyncDbConfig. The synchronization data source configuration jdbc.writeSyncDbConfig uses "tableDbList":[{"tableName":"X",dblist:["db1"]} to indicate that the configuration table X is synchronized with the addition, deletion and modification of data in the slave library code db1.

Images