Extension refers to adding functions not provided by general Mapper.
Give an example:
An example of using bulk insert as an extension function in the official document of general Mapper is: tk.mybatis.Mapper.additional.insert.insertlistmapper < T > tk.mybatis.mapper.additional.insert.InsertListProvider
Let's copy a batch update. Suppose we want to generate the following SQL statements:
UPDATE tabple_emp SET emp_name=?,emp_age=?,emp_salary=? where emp_id=?; UPDATE tabple_emp SET emp_name=?,emp_age=?,emp_salary=? where emp_id=?; UPDATE tabple_emp SET emp_name=?,emp_age=?,emp_salary=? where emp_id=?;
In order to generate the above SQL statements, we need to use the foreach tag of MyBatis.
<foreach collection="list" item="record" separator=";" > UPDATE tabple_emp <set> emp_name=#{record.empName}, emp_age=#{record.empAge}, emp_salary=#{record.empSalary}, </set> where emp_id=#{record.empId} </foreach>
1. Create the extension interface MyBatchUpdateMapper
public interface MyBatchUpdateMapper<T> { @UpdateProvider(type=MyBatchUpdateProvider.class, method="dynamicSQL") void batchUpdate(List<T> list); }
2. Create MyBatchUpdateProvider assembly SQL
public class MyBatchUpdateProvider extends MapperTemplate { public MyBatchUpdateProvider(Class<?> mapperClass, MapperHelper mapperHelper) { super(mapperClass, mapperHelper); } /** <foreach collection="list" item="record" separator=";" > UPDATE tabple_emp <set> emp_name=#{record.empName}, emp_age=#{record.empAge}, emp_salary=#{record.empSalary}, </set> where emp_id=#{record.empId} </foreach> */ public String batchUpdate(MappedStatement statement) { //1. Create StringBuilder to splice various components of SQL statement StringBuilder builder = new StringBuilder(); //2. Splicing foreach label builder.append("<foreach collection=\"list\" item=\"record\" separator=\";\" >"); //3. Get the Class object corresponding to the entity Class Class<?> entityClass = super.getEntityClass(statement); //4. Get the corresponding table name of entity class in the database String tableName = super.tableName(entityClass); //5. Generate update clause String updateClause = SqlHelper.updateTable(entityClass, tableName); builder.append(updateClause); builder.append("<set>"); //6. Get all field information Set<EntityColumn> columns = EntityHelper.getColumns(entityClass); String idColumn = null; String idHolder = null; for (EntityColumn entityColumn : columns) { boolean isPrimaryKey = entityColumn.isId(); //7. Judge whether the current field is a primary key if(isPrimaryKey) { //8. Cache the field name and value of the primary key idColumn = entityColumn.getColumn(); //The return format is as follows: {record. Age, JDBC type = numeric, typehandler = mytypehandler} idHolder = entityColumn.getColumnHolder("record"); }else { //9. Splicing SET clauses with non primary key fields String column = entityColumn.getColumn(); String columnHolder = entityColumn.getColumnHolder("record"); builder.append(column).append("=").append(columnHolder).append(","); } } builder.append("</set>"); //10. Use the previously cached primary key name and value to splice where clause builder.append("where ").append(idColumn).append("=").append(idHolder); builder.append("</foreach>"); //11. Return the spliced string return builder.toString(); } }
3. MyMapper inherits the extension interface MyBatchUpdateMapper
public interface MyMapper<T> extends SelectAllMapper<T>,SelectByExampleMapper<T>,MyBatchUpdateMapper<T> { }
Test:
Note: JDBC executes semicolon separated SQL statements. Parameter allowMultiQueries=true must be added when connecting to the database
public class MyBatchUpdateTest { public static void main(String[] args) { ClassPathXmlApplicationContext iocContainer = new ClassPathXmlApplicationContext("spring-context.xml"); EmployeeService employeeService = iocContainer.getBean(EmployeeService.class); List<Employee> empList = new ArrayList<Employee>(); empList.add(new Employee(25, "newName01", 111.11, 10)); empList.add(new Employee(26, "newName02", 222.22, 20)); empList.add(new Employee(27, "newName03", 333.33, 30)); employeeService.batchUpdateEmp(empList); iocContainer.close(); } }
Console output:
Preparing: UPDATE tabple_emp SET emp_name=?,emp_salary=?,emp_age=? where emp_id=? ; UPDATE tabple_emp SET emp_name=?,emp_salary=?,emp_age=? where emp_id=? ; UPDATE tabple_emp SET emp_name=?,emp_salary=?,emp_age=? where emp_id=? Parameters: newName01(String), 111.11(Double), 10(Integer), 1(Integer), newName02(String), 222.22(Double), 20(Integer), 2(Integer), newName03(String), 333.33(Double), 30(Integer), 3(Integer) Updates: 1