MyBatis Interceptor--Get execution SQL for multi-client data synchronization

A recent project is to package the Java EE Environment on the client (using NWJS + NSIS to make the installation package) and all the business operations are done on the client and the data is stored in the client database. The server-side database summarizes the data for each client for analysis. Where the client ORM uses MyBatis. Get all the SQL statements executed by the MyBatis interceptor and synchronize to the server regularly.

This article describes how to use the MyBatis interceptor by blocking SQL on the client.

1. Project Requirements

Customer branch More and more scattered, some in-store network is not stable, customers require each branch in the case of no network can also normal use of the system, and all store data needs to be aggregated analysis. Integrated customer needs, the project structure is as follows:

The Web project and its running environment are installed in each branch through the NSIs production installation package, each branch is a separate Web service, so that the store can use the system without network (LAN, Internet access). At this point, each store's database maintains its own in-store operational data, and the data between stores is isolated from each other.

However, the operator is unable to analyze all the store's aggregated data (such as the overall sales of goods), so it is necessary to synchronize each store's data to the server's database regularly.

The real-time synchronization scheme is excluded because there may be no network in the store (it is not affected by data synchronization and the system needs to run properly).
In order to ensure the security of the database, the server database can not be exposed, and the synchronization mechanism scheme using the database is excluded.
Some businesses need to record data change logs (data from 1 to 0 to 1 to record the process), and the incremental synchronization scheme is excluded.
Finally, the use of all client updates (add, delete, change) of SQL in the order of execution to the database, and periodically synchronize and in the server's database sequentially execute SQL, so as to ensure that the server database data is a summary of the client data.

2. Solution

The project uses MyBatis, Mapper in the definition of SQL can use MyBatis tags and parameter identifiers, MyBatis will resolve the label substitution parameters to generate the final SQL in the database execution, and we need to eventually execute in the database SQL.

MyBatis SQL in the syntax:

<insert id= "Insert" >
INSERT into Atd681_mybatis_test (DV) VALUES (#{dv})
</insert>
Copy Code
SQL that needs to be synchronized to the server execution:

INSERT into Atd681_mybatis_test (DV) VALUES (' AAA ')
Copy Code
3. Interceptors

3.1 What is an interceptor?
To think of such a scenario, you might need the following steps when cooking:

Buy Vegetables >> wash Vegetables >> cut vegetables >> make dishes >> serve >> wash dishes

Before starting to wash the vegetables, the purchase of the operation has been completed, you can know what to buy food.
The dish is not cooked yet, so I don't know what it tastes.
You can know the taste of the dish before serving (the dish has been finished at this time).
I don't know if there's any leftovers when serving.
We can know if there is any leftovers when we wash the dishes.
The cooking process above is done step by step, and we can either get the results of the previous steps in one of these procedures or do something extra before a step begins, such as weighing the dish before cutting it.

MyBatis provides a component that allows a custom action to be performed before a step is executed. This component is called an interceptor. The so-called Interceptor, as the name implies: need to define which steps to intercept and what to do after interception.

3.2 Defining interceptors

Interceptors need to implement the Org.apache.ibatis.plugin.Interceptor interface and specify the interception method.

Interception device
@Intercepts (@Signature (type = Statementhandler.class,
method = "Update",
args = Statement.class)
)
public class Sqlinterceptor implements interceptor {

//Logic executed after intercepting method
@Override
public Object intercept(Invocation invocation) throws Throwable {
//Continue to execute the original logic of mybatis
//The intercepted method is executed by reflection in proceed
return invocation.proceed();
}
//Returns the dynamic proxy of the currently intercepted object (statementhandler)
//When the method of intercepting object is executed, interceptor intercept method is executed in dynamic agent
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
//Set properties
@Override
public void setProperties(Properties properties) {
}

}

Copy Code
@Intercepts The Interceptor annotations provided for MyBatis, @Signature specify the interception method.
If an interceptor intercepts multiple methods, configure multiple @Signature (arrays) in the @Intercepts.
Because Java methods can be overloaded, determining the unique method requires specifying the class (type), method, and parameter (args).
Interceptors can intercept Executor, Parameterhandler, Resultsethandler, Statementhandler methods.
3.3 Configuring interceptors

In the spring configuration file, declare the interceptor and configure it to the plugins attribute in the Sqlsessionfactorybean

MyBatis Interceptor
Sqlinterceptor (Sqlinterceptor)

MyBatis Configuration
Sqlsessionfactory (Sqlsessionfactorybean) {
DataSource = ref ("DataSource")
Mapperlocations = "Classpath:/com/atd681/mybatis/interceptor/_mapper.xml"

//Configure mybatis interceptor plugins =[

}
Copy Code
4. Get and save the SQL

The approximate process for mybatis processing SQL is as follows:

Load sql>> parse SQL >> Replace SQL parameters >> Execute SQL >> get returned results

intercepts the [Execute SQL] operation, at which time MyBatis has completed the SQL parsing and substitution parameters, and the resulting SQL is the SQL executed by the sending database. We just need to get the SQL and save it to the database.

MyBatis Interceptors: Block all additions and deletions to SQL, keeping SQL to the database
Intercept Statementhandler.update method
@Intercepts (@Signature (type = Statementhandler.class,
method = "Update",
args = Statement.class)
)
public class Sqlinterceptor implements interceptor {

@Override
public Object intercept(Invocation invocation) throws Throwable {
//Invocation. Getargs () can get the parameters of the intercepted method
//The parameter of statementhandler.update (statements) is statement
Statement s = (Statement) invocation.getArgs()[0];
//Statement with data source of druid and statement of Druid
Statement stmt = ((DruidPooledPreparedStatement) s).getStatement();
//Use filters: stat configuration when configuring Druid connections
if (stmt instanceof PreparedStatementProxyImpl) {
stmt = ((PreparedStatementProxyImpl) stmt).getRawObject();
}
//The statement provided by the database can get the SQL after parameter replacement (JDBC and Druid get the SQL with "?)
//The database is mysql, which can be directly converted to Preparedstatement of Mysql to obtain SQL
//When SQL is written, there will be line breaks (multiple spaces) for format container reading
//Remove line breaks and multiple spaces in SQL for saving and viewing
String sql = ((com.mysql.jdbc.PreparedStatement) stmt).asSql().replaceAll("\\s+", " ");
//The operation to save SQL must be in the same transaction as the currently executed SQL
//Use the database connection of the current SQL to save
//When the target SQL succeeds, the method to save the SQL is also synchronized successfully
Connection conn = stmt.getConnection();
//Save SQL to database
PreparedStatement ps = null;
Try {
ps = conn.prepareStatement("INSERT INTO atd681_mybatis_sql (v_sql) VALUES (?)");
ps.setString(1, sql);
//Because it is in the same transaction as the operation of mybatis
//If this operation fails, all operations are rolled back
ps.execute();
}
Finally {
if (ps != null) {
Ps.close ();
}
}
//Continue with statementhandler.update method
return invocation.proceed();
}

}

Copy Code
Only the PreparedStatement object provided by MySQL can get the final SQL.
Saving SQL operations requires and MYBATIS operations in the same transaction and must succeed or fail at the same time.

1. Test

Create two tables in the database:

Atd681_mybatis_test
Atd681_mybatis_sql
Create DAO and Mapper, create add, delete, modify methods and SQL

Data daobr/> @Repository
< p="">

//Add data
void insert(String dv);
//Update data
void update(String dv);
//Delete data
void delete();

}
Copy Code
<mapper namespace= "Com.atd681.mybatis.interceptor.DataDAO" >

<! -- add data, the content is the value of parameter I -- >
<insert id="insert">
INSERT INTO atd681_mybatis_test ( dv ) VALUES ( #{dv} )
</insert>
<! -- update the data to the value of parameter U -- >
<update id="update">
UPDATE atd681_mybatis_test1 SET dv = #{dv}
</update>
<! -- delete data -- >
<delete id="delete">
DELETE FROM atd681_mybatis_test
</delete>

</mapper>
Copy Code
Add a method to the controller, call Delete, add, and update in turn. Ensure that three operations are in the same transaction.

@RestController
public class Datacontroller {

/ / injection of DAO
@Autowired
private DataDAO dao;
//Delete, insert and update respectively
//Parameter I: string at insert time
//Parameter U: string at update time
@GetMapping("/mybatis/test")
@Transactional
public String excuteSql(String i, String u) {
//After deleting the data, plug-in the content database of parameter I, and update the data to the content of parameter U
//This method adds a transaction, and three database operations will be performed in the same transaction
//Mybatis interceptor will capture the database SQL three times and insert it into the database (see interceptor for details)
dao.delete();
dao.insert(i);
dao.update(u);
return "success";
} 

}
Copy Code
Start Service, Access Http://localhost:3456/mybatis/test?i=insert&u=update

The program performs delete, add (content "Insert"), Update (content is "update") three operations, after execution completes the database has a record (content is "Update"). Because the interceptor is configured, the SQL is persisted to the database before each operation executes, so three SQL is also saved to the database.

In the above process, in addition to 3 business operations, there are 3 times to maintain SQL operations, so the database will execute a total of 6 SQL.

Perform a delete operation
Save SQL for delete operation in 1
Execute Insert SQL
Save SQL for insert operation in 3
Execute Update SQL
Save SQL for update operation in 5
The above 6 database operations must be in the same transaction, otherwise if the business operation succeeds but the SQL failure is saved. The server-side synchronization data will be inconsistent with the client locally.

MyBatis Interceptor--Get execution SQL for multi-client data synchronization