[JAVA] How to use MyBatis Mapper annotation

MyBatis allows you to define Mapper with annotations instead of Mapper XML. This time, I will summarize how to use the annotation.

environment

• MyBatis 3.4.6

@Insert, @Update, @Select, @Delete It can be specified for each method, and each corresponds to the SQL to be executed. Each annotation can take a string or an array of strings as arguments and pass the SQL you want to execute. If an array is specified, the character strings will be combined separated by a single-byte space.

As with XML, you can embed values with # {hoge}, and if you want to take advantage of dynamic SQL, you'll need to enclose the whole thing in <script>.

@Select("SELECT id, title FROM table WHERE id=#{id}")
Todo findById(Integer id);

@Select({
  "<script>",
  "SELECT id, title FROM table",
  "<where>",
  "<if test=\"id != null\">AND id=#{id}</if>",
  "<if test=\"title != null\">AND title=#{title}</if>",
  "</where>",
  "</script>"
})
List<Hoge> find(@Param("id") Integer id, @Param("title") String title);

By the way, in the case of XML, the attribute resultType was specified, but it is not necessary to specify it because the return type of the method is automatically applied.

@InsertProvider, @UpdateProvider, @SelectProvider, @DeleteProvider It can be specified for the method, and each corresponds to the SQL to be executed. For each annotation, specify the class object in the type attribute and the method name as a character string in the method attribute. Executes the string returned by the methods specified by these as SQL.

The methods for creating SQL can be achieved by simply constructing a string, but it can also be constructed using the SQL builder provided by MyBatis. You can also use Java control syntax to create dynamic SQL. http://www.mybatis.org/mybatis-3/ja/statement-builders.html

You can set the value directly in SQL, but there is a risk of injection, so it is better to use the format # {hoge}.

@SelectProvider(type = TodoSQLProvider.class, method = "select")
List<Todo> find2(@Param("id") Integer id, @Param("title") String title);

public class TodoSQLProvider {
  public String select(@Param("id") Integer id, @Param("title") String title) {
    return new SQL() {
      {
        SELECT("*");
        FROM("todo");
        if (id != null) {
          WHERE("id = #{id}");
          // WHERE("id = " + id);This is a risk of injection
        }
        if (title != null) {
          WHERE("title = #{title}");
        }
      }
    }.toString();
  }
}

@Option You can set the following options that can be specified for the method, such as <select> and <insert> in XML.

• useCache
• flushCache
• resultSetType
• statementType
• fetchSize
• timeout
• useGeneratedKeys
• keyProperty
• keyColumn
• resultSets

@Results, @Result @Reuslts corresponds to the XML<resultMap>, and @Result corresponds to the <result>. @Results can be specified for the method, and the ʻid attribute specifies a string and the valueattribute specifies an array of@Result`.

@Result can specify ʻid, column, property, javaType, jdbcType, typeHandler, ʻone, many as attributes. Since it is almost the same as the attribute that can be specified by the <result> tag of XML, only the difference will be explained.

@Select("SELECT id, title FROM todo")
@Results(id = "hoge", value = {
  @Result(id = true, column = "id", property = "i"),
  @Result(id = true, column = "title", property = "t"),
})
List<Hoge> findAll();

@Data
public class Hoge {
  private Integer i;
  private String t;
}

@One, @Many As mentioned above, it corresponds to <association> and <collection>. In both cases, fetchType and select can be specified as attributes.

For fetchType, specify one of FetchType.DEFAULT, FetchType.EAGER, or FetchType.LAZY. The operation when specified is the same as the XML fetchType, so it is omitted here.

For select, specify the fully qualified name of the Mapper method. (Methods with @Select and @SelectProvider) If you specify only the method name instead of the fully qualified name including the package etc., you are specifying the method in the same Mapper interface. The behavior is the same as when the select attribute is specified in XML.

public interface TodoMapper {
  @Select("SELECT * FROM todo WHERE id=#{id}")
  @Results(id = "todo", value = {
      @Result(column = "author", property = "author", one = @One(select = "com.example.mybatis.UsersMapper.findById", fetchType = FetchType.EAGER)) })
  Todo findById(Integer id);
}

public interface UsersMapper {
  @Select("SELECT * FROM users WHERE id=#{id}")
  User findById(Integer id);
}

In XML, instead of using select, it was possible to join and get the tables as shown below and use the result for mapping.

<resultMap id="TodoMap" type="Todo">
  <id column="todoId" property="id" />
  <result column="title" property="title" />
  <association property="author" javaType="User">
    <id column="userId" property="id" />
    <result column="name" property="name" />
  </association>
</resultMap>
<select id="findById" resultMap="TodoMap">
  SELECT t.id as todoId, t.title, u.id as userId, u.name FROM todo t, users u WHERE t.author = u.id
</select>

However, @ One and @ Many cannot achieve the same thing as above, and you have to always execute another select. I don't like the N + 1 problem with the select method, and I personally didn't use it much, so I thought this was a bit of a disadvantage.

@ConstructorArgs, @Arg @ConstructorArgs corresponds to the XML<constructor>, and @Arg corresponds to the <arg>.

@ConstructorArgs can specify an array of @Arg in the value attribute. @ Arg can specify ʻid, column, javaType, jdbcType, typeHandler, select, resultMap as attributes. ʻId is similar to @Result, and you must specify true for the primary key. Other than that, it is the same as the attributes that can be specified in the XML <arg> tag.

@Select("SELECT id, title FROM todo")
@ConstructorArgs({
  @Arg(id = true, column = "id", name = "id"),
  @Arg(id = true, column = "title", name = "title")
})
List<Todo> constructor();

@ResultMap By specifying ʻid of @ Resultsthat you want to use in thevalue attribute, you can use @ Results` defined elsewhere.

@Select("SELECT id, title FROM todo")
@Results(id = "hoge", value = {
  @Result(id = true, column = "id", property = "i"),
  @Result(id = true, column = "title", property = "t")
})
List<Hoge> findAll();

@Select("SELECT id, title FROM todo WHERE id=#{id}")
@ResultMap("hoge")
Hoge findBy(Integer id);

If you specify in the format of "package name + interface name + id attribute value", you can also use @Results defined in other interfaces.

@MapKey Can only be specified for methods that return Map. In the value attribute, specify the key property of Map.

@Select("SELECT id, title FROM todo")
@MapKey("id")
Map<Integer, Todo> map();

For example, if the data with id 1 and title 1 and id 2 and title 2 can be obtained, the map will be as follows.

{1=Todo(id=1, title=test1), 2=Todo(id=2, title=test2)}

@ResultType The class to be mapped is obtained from the return type. However, in the case of the method that uses ResultHandler, the return value is void, so specify the class to be mapped by @ ResultType.

@Select("SELECT id, title FROM todo")
@ResultType(Todo.class)
void handle(ResultHandler<Todo> handler);

When using ResultHandler, you can also use @ResultMap.

@SelectKey You can implement the function equivalent to the XML <selectKey>. You can specify statement, keyProperty, keyColumn, before, resultType, statementType as attributes.

Specify the SQL statement in the statement attribute, before corresponds to ʻorder` in XML. Other than that, it is the same as for XML.

Summary

You can do almost the same thing as XML, but I think that is the bottleneck because there are functional differences between @ One and @ Many. If you just want to execute simple SQL quickly, you can easily define it directly in the interface.

There are some annotations that have not been summarized yet, so I would like to add them soon.