MyBatis3 provides a new annotation based configuration. Related annotations are defined in MapperAnnotationBuilder:

public MapperAnnotationBuilder(Configuration configuration, Class<?> type) {
    ...
    sqlAnnotationTypes.add(Select.class);
    sqlAnnotationTypes.add(Insert.class);
    sqlAnnotationTypes.add(Update.class);
    sqlAnnotationTypes.add(Delete.class);
    ......
    sqlProviderAnnotationTypes.add(SelectProvider.class);
    sqlProviderAnnotationTypes.add(InsertProvider.class);
    sqlProviderAnnotationTypes.add(UpdateProvider.class);
    sqlProviderAnnotationTypes.add(DeleteProvider.class);
}

Adding, deleting, modifying and querying occupy the vast majority of business operations. It is no longer necessary to configure complicated xml files through annotations. More and more sql interactions are realized through annotations. From MapperAnnotationBuilder, you can see that Mybatis provides the following related annotations:
@Select
@Insert
@Update
@Delete
@SelectProvider
@InsertProvider
@UpdateProvider
@DeleteProvider
For example, the following example uses the @ Select annotation to directly write SQL to complete data query:

@Mapper
public interface UserMapper {    
@Select("select * from t_user")    
List<User> list();
}

Using advanced annotations like @ SelectProvider, you can specify the method of a tool class to dynamically write SQL to meet complex business requirements.
Take @ SelectProvider as an example to view the specific implementation, which mainly includes two annotation attributes, in which type represents the tool class and method represents a method of the tool class, which is used to return specific SQL:

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface InsertProvider {
    // Specifies the specified class used to get the sql statement
    Class<?> type();
    // Specifies the method in the class to execute the get sql statement
    String method();
}

The usage method is as follows: define the relevant sql in the getContentByProjectIds method of ProjectSql class. The sql can be defined through org apache. ibatis. jdbc. sql to quickly implement:
@SelectProvider(type = ProjectSql.class, method = "getContentByProjectIds")
List<Integer> getContentByProjectIds(List<Integer> projectIds);

Common injection scenarios

2.1 general notes
In fact, the syntax of the corresponding tag in the xml configuration is the same (for example, @ select corresponds to the < Select > tag), so the injection scenario is also similar.
In Mybatis, # is mainly used to replace placeholders in the preparestatement$ Is a direct SQL splice. Take like fuzzy query as an example:
For example:
Like the xml configuration, like fuzzy queries annotated directly using # precompiled methods will trigger exceptions, so $is often used directly for annotation:

@Select("SELECT id, name, age, email FROM user where name like '${name}'")List<User> queryUserByName(@Param("name") String name);

If the name front-end user is controllable at this time, it will lead to SQL injection risk.
View the sql log in the picture. The successful execution of 1 / 0 triggers sql error, indicating that the injection is successful:

It is also very simple to deal with this kind of SQL problems. Use the built-in functions of SQL to splice, and then use # precompile to query. For example, the above case is h2 database. Use '|' splicing and precompile:

@Select("SELECT id, name, age, email FROM user where name like '%'||#{name}||'%'")List<User> queryUserByName(@Param("name") String name);

Precompiled SQL query has been used at this time:

In addition, similar to Order by and dynamic table names, if precompiling cannot be adopted, it can be handled by indirect reference at the code layer.
For range query in, if you are familiar with mybatis injection, you need to use the circular instruction foreach provided by mybatis to solve the dynamic splicing of SQL statements. When using annotations, you need to use the < script > tag to introduce foreach.
2.2 dynamic sql
2.2.1 use < script >
To use dynamic SQL in a mapper interface class with common annotations, you can use the script element. Similar to xml, it mainly includes the following elements:

if
choose (when, otherwise)
trim (where, set)
foreach

The related injection scenarios are similar to 2.1. It is also inseparable from $. In addition, when performing multi value queries with the same condition (such as range query in), you can use the circular instruction foreach of MyBatis to solve the problem of dynamic splicing of SQL statements.
2.2.2 using Provider annotation
You can dynamically write SQL by specifying the method of a tool class using the Provider annotation. Take @ SelectProvider as an example:
First, use @ SelectProvider in mapper to define relevant methods, where type represents the tool class and method represents a method of the tool class, which is used to return specific SQL. For example, the following example:
Query relevant user information by passing userIds and name. The specific SQL content is defined in the getUserInfoByids method of UserInfoSql class:

/**
   * @param userIds Required
   * @param name Optional
   * @return
   */
  @SelectProvider(type = UserInfoSql.class, method = "getUserInfoByids")
  List<User> getUserInfoByids(List<Long> userIds, String name);
   
  class UserInfoSql {
    public String getUserInfoByids(List<Long> userIds, String name) {
      SQL sql = new SQL();
      sql.SELECT("id, name, age, email");
      sql.FROM("user");
      sql.WHERE("id in(" + Joiner.on(',').join(userIds) + ")");
      if(StringUtil.isNotBlank(name)){
        sql.WHERE("name like '%" + name + "%'");
      }
      sql.ORDER_BY("id desc");
      return sql.toString();
    }
  }

You can query sql by calling specific methods in the Controller:

 @RequestMapping(value = "/getUserInfoByids")
 public List<User> getUserInfoByids(String name,@RequestParam List<Long> userIds){
         List<User> userList = userMapper.getUserInfoByids(userIds,name);
         return userList;
 }

Normal request returns the corresponding user information:

The previous is the tool class org. Provided through MyBatis 3 apache. ibatis. jdbc. SQL to generate SQL. This class provides functions like select, where and ORDER_BY and other methods to complete the operation of SQL generation. There is a misunderstanding here. Many developers think that tool classes here will be pre compiled.
In fact, the Provider only needs to return an SQL String. The tool class only uses some keywords for formatting. You can even directly use StringBuffer to splice SQL statements. Similarly to the above example, List userIds is of type long, but name is of type String. You can try to inject:

Check the relevant logs and successfully execute the 1 / 0 logic to trigger SQL error, which also confirms that the Provider is actually just SQL splicing without relevant security processing:

Compared with @ Select @, SelectProvider is only different in the way of defining annotations. The former is to directly define sql, and the other is to define sql direct reference externally. There is no essential difference. Therefore, the solution is to use # precompile in the corresponding sql scenario for processing, such as like fuzzy query and in range query here:

@SelectProvider(type = UserInfoSql.class, method = "getUserInfoByids")
  List<User> getUserInfoByids(@Param("userIds")List<Long> userIds,@Param("name")String name);
   
  class UserInfoSql {
    public String getUserInfoByids(@Param("userIds")List<Long> userIds, @Param("name")String name) {
            StringBuilder sql = new StringBuilder(128);
            sql.append("< script>SELECT id, name, age, email FROM user WHERE (id in");
            sql.append("<foreach item='item' collection='userIds' open='(' separator=',' close=')'>#{item}</foreach>");
      if(StringUtil.isNotBlank(name)){
              sql.append("and name like '%'||#{name}||'%')");
      }
      sql.append("ORDER BY id desc</script>");
      return sql.toString();
    }
  }

Check the SQL log. At this time, precompile is used for SQL processing to avoid the risk of SQL injection.

key word: java training