The real power of MyBatis lies in its statement mapping, which is its magic. Because of its extraordinary power, the XML file of the mapper is relatively simple. If you compare it with JDBC code with the same function, you will immediately find that nearly 95% of the code is saved. MyBatis is committed to reducing the use cost and enabling users to focus more on SQL code.

The SQL mapping file has only a few top-level elements (listed in the order that they should be defined):

• Cache – the cache configuration for this namespace.
• Cache ref – refers to the cache configuration of other namespaces.
• resultMap – describes how to load objects from the database result set. It is the most complex and powerful element.
• sql – a repeating sentence block that can be referenced by other statements.
• Insert – map insert statements.
• Update – map update statements.
• Delete – map delete statements.
• select – map query statements.

You can also see that the dtd file contains these tags:

<!ELEMENT mapper (cache-ref | cache | resultMap* | parameterMap* | sql* | insert* | update* | delete* | select* )+>

In the following example, all our objects use aliases, which can be configured in the global configuration file

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <settings>
        <!-- Convert fields using hump nomenclature -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <typeAliases>
         <!-- Scanned packages, self defined -->
        <package name="com.example.springboot_mybatis.model"></package>
    </typeAliases>

</configuration>

input parameter

Pass simple type

//Query user
User findById(Integer id);

<select id="selectUsers" resultType="User" parameterType="integer">
  select id, username, password
  from users
  where id = #{id}
</select>

Pass pojo

Using #{object attribute}, assuming that a department object attribute Dept is included, you can also use #{dept.xxx} to obtain the attribute value of the included object

//Add user
int save(User user);

<insert id="insertUser" parameterType="User">
  insert into users (id, username, password)
  values (#{id}, #{username}, #{password})
</insert>

HashMap parameters

When HashMap is used as a parameter, we show that javaType is executed to ensure that the correct type handler is used

Prompt JDBC requires that if a column allows null values and will use null parameters, the JDBC type must be specified.

To further customize type handling, you can specify a special type processor class (or alias)

#{age,javaType=int,jdbcType=NUMERIC,typeHandler=MyTypeHandler}

Numeric type: you can also set numericScale to specify the number of digits to be reserved after the decimal point.

#{height,javaType=double,jdbcType=NUMERIC,numericScale=2}

Finally, the mode attribute allows you to specify IN, OUT, or INOUT parameters. If the mode of the parameter is OUT or INOUT, the property value of the parameter object will be modified to return as an output parameter. If the mode is OUT (or INOUT) and the JDBC type is CURSOR (that is, Oracle's REFCURSOR), you must specify a ResultMap reference to map the result set ResultMap to the type of the parameter. Note that the javaType attribute here is optional. If it is left blank and the JDBC type is CURSOR, it will be automatically set to ResultMap.

#{department, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=departmentResultMap}

MyBatis also supports many advanced data types, such as structures, but when using the out parameter, you must explicitly set the name of the type. For example (prompt again, in practice, it should be like this, and line breaks are not allowed):

#{middleInitial, mode=OUT, jdbcType=STRUCT, jdbcTypeName=MY_TYPE, resultMap=departmentResultMap}

Although the above options are powerful, most of the time, you just need to specify the attribute name, specify the JDBC type for the column that may be empty, and leave the rest to MyBatis to infer.

Easy to use: #{key}

List<User> findListByHashMap(HashMap<String, Object> map)

<select id="findListByHashMap" resultType="com.pojo.User">
      select * from user where username = #{username} and status = #{status}
</select>

Collections and arrays

Generally, we use the < foreach > tag to traverse

/**
* Batch insert
* @param userList
* @return
*/
int batchSave(@Param("userList") List userList);

<insert id="batchSave" parameterType="list">
    INSERT INTO t_user(username,email,addr) VALUES
    <foreach item="item" collection="userList" separator=",">
        (#{item.username}, #{item.email}, #{item.addr})
    </foreach>
</insert>

If we want to get the first item in the set: for example, we take the id of the user object in the first item in the user set to query: #{list[0].id}

/**
* Query user
* @param users
* @return
*/
User findById(List<User> users);

<select id="findById" parameterType="integer" resultType="com.pojo.User" resultMap="userMap">
    select id, username, email, addr from t_user where id = #{list[0].id} limit 1
</select>

Similar to this form, there are certain rules:

• Collection - collection [index value]

• LIst - list [index value]

• Array - array [index value]

• ...

public static Object wrapToMapIfCollection(Object object, String actualParamName) {
    if (object instanceof Collection) {
        ParamMap<Object> map = new ParamMap<>();
        map.put("collection", object);
        if (object instanceof List) {
            map.put("list", object);
        }
        Optional.ofNullable(actualParamName).ifPresent(name -> map.put(name, object));
        return map;
    } else if (object != null && object.getClass().isArray()) {
        ParamMap<Object> map = new ParamMap<>();
        map.put("array", object);
        Optional.ofNullable(actualParamName).ifPresent(name -> map.put(name, object));
        return map;
    }
    return object;
}

Output parameters

Output simple type

We get the total number of records in the user table

UserMapper:

/**
 * Get total records
 * @return Total record
 */
int getTotal();

UserMapper.xml:

<select id="getTotal" resultType="java.lang.Integer">
    select count(1) from t_user
</select>

Output pojo

UserMapper:

/**
 * Get total records
 * @return Total record
 */
int getTotal();

UserMapper.xml:

<select id="getTotal" resultType="java.lang.Integer">
    select count(1) from t_user
</select>

Output pojo list

For example, we fuzzy query users according to their user names

UserMapper:

/**
     *  Fuzzy query based on user name
     * @param username user name
     * @return list
     */
List<User> getListByName(@Param("username") String username);

UserMapper.xml:

<select id="getListByName" resultType="User">
    select * from t_user t where instr(t.username, #{username}) > 0
</select>

Output Map collection

It's still the above example, but we only need to query the id and user name fields

UserMapper:

/**
 * Fuzzy query based on user name
 * @param username user name
 * @return list
 */
List<Map<String, Object>> getListByName(@Param("username") String username);

UserMapper.xml:

<select id="getListByName" resultType="map">
    select t.id, t.username from t_user t where instr(t.username, #{username}) > 0
</select>

Test:

@Test
public void getList(){
    List<Map<String, Object>> users = userMapper.getListByName("x");
    users.stream().forEach(System.out::println);
}

//The key of map corresponds to the column name in the database
{id=15, username=xx}
{id=16, username=xx}
{id=17, username=xx}
{id=18, username=xx}
{id=19, username=xx}

Result mapping

resultMap acquaintance

Most of the database column names in the previous query statements are consistent with the entities. However, if they are inconsistent, the entity attributes and data table attributes are often named according to the hump. For example, the entity class is called creTime and the database is named cre_time, then we need to manually establish a one-to-one mapping relationship.

There are two methods:

One is to use as as alias

<select id="getList" resultType="User">
  select
    user_id as id,
    user_name as userName
  from t_user
  where user_id = #{id}
</select>

The second is to use the resultMap: select tag to specify the map we want to use through the resultMap attribute

<resultMap id="userResultMap" type="User">
  <id property="id" column="user_id" />
  <result property="userName" column="user_name"/>
</resultMap>

<select id="findById" parameterType="integer" resultMap="userResultMap">
        select * from t_user where id = #{id}
</select>

• id is used to uniquely identify the resultmap, and type refers to the mapped entity class
• The id tag is generally used to represent the primary key mapping, and the result tag is used to represent other column mappings
• Property: property name of the object, column: database column name