1. Special operation

1.1 fuzzy query

Requirement: query the data containing "fine" in name, and arrange it in descending order by age

@Test
    public void test9(){
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        DemoUserMapper mapper = sqlSession.getMapper(DemoUserMapper.class);
        Map<String,Object> map=new HashMap<>();
        map.put("name","essence");
        map.put("column","age");
        List<DemoUser> demoUsers=mapper.like(map);
        System.out.println(demoUsers);
    }

① $mode

<select id="like" resultType="com.jt.pojo.DemoUser">
        select * from demo_user where name like '%${name}%' order by ${column} desc ;
    </select>

② % requires quotation marks

<select id="like" resultType="com.jt.pojo.DemoUser">
        select * from demo_user where name like "%"#{name}"%" order by ${column} desc ;
    </select>

There are only two ways to show here  

Note: sql in MyBatis should be lowercase, because different systems are not case sensitive  

Case conversion shortcut: ctrl+shift+u

1.2 batch query

Demand: change the age of Xiao Qiao / Da Qiao / Wang Zhaojun to 25, male

code implementation

  @Test/*11.Change the age of name Xiao Qiao / Da Qiao / Wang Zhaojun to 18, male*/
    public void test11(){
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        DemoUserMapper mapper = sqlSession.getMapper(DemoUserMapper.class);
        List list=new ArrayList();
        list.add("Little Joe");
        list.add("Big Joe");
        list.add("Wang Zhaojun");
        Map<String,Object> map=new HashMap<>();
        map.put("names", list);
        map.put("age", 25);
        map.put("sex", "male");
        mapper.update2(map);

    }

<update id="update2">
        update demo_user set age=#{age} ,sex=#{sex} where name in(
            <foreach collection="names" item="name" separator=",">
                #{name}
            </foreach>
            )
    </update>

void update2(Map<String, Object> map);

2. Optimize configuration

2.1 alias

The above operations are too troublesome. There are three solutions

Method 1: configure the alias label in the core configuration file mybatis-config.xml

Implementation code:

mysql-config.xml file configuration content

<!--Configure alias-->
    <typeAliases >
        <typeAlias type="com.jt.pojo.DemoUser" alias="DemoUser"></typeAlias>
    </typeAliases>

  Contents of sql statements in mapper mapping file

<select id="find1" resultType="DemoUser">
        select * from demo_user where id=#{id}
    </select>

Note: the order of the core configuration files  

The content of element type "configuration" must match "(properties?,settings?,typeAliases?,typeHandlers?,objectFactory?,objectWrapperFactory?,reflectorFactory?,plugins?,environments?,databaseIdProvider?,mappers?)".

Method 2: use alias package

Editing alias labels one by one is also troublesome. Alias labels are only valid for a class, so you can configure package labels and package paths in the configuration file

<!--Configure package path-->
        <package name="com.jt.pojo"/>

The principle is dynamic splicing: package path +. Class name

Method 3: use annotation

@Alias (alias for class)   Not commonly used

2.2 simplified sql Tags

sql tag usage

<!--simplify sql label-->
    <sql id="demo_user_sql">
        select * from demo_user
    </sql>

    <select id="find1" resultType="DemoUser">
        <include refid="demo_user_sql"/>where id=#{id}
    </select>

Advantages and disadvantages of sql Tags

Profit:                                              Disadvantages:

1. Save the size of xml file                   1.sql can only extract public sql statements, which has great limitations

2. The code structure is relatively simple                     2. If sql tags are widely used, the readability is too poor

3.MyBatis dynamic sql

3.1 IF-WHERE label

Rule: where and if tags are usually used together. The where tag can remove redundant and and or, and the test attribute of if is the judgment condition

            Judge the non empty attribute in the object as the where condition

Scenario: the user does not upload all field attribute values. There may only be name and age

<select id="find2" resultType="DemoUser">
        select * from demo_user
        <where>
            <if test="name!=null"> name = #{name}</if>
            <if test="age !=null"> and age = #{age}</if>
            <if test="sex !=null"> and sex = #{sex}</if>
        </where>
    </select>

3.2 SET label

Rule: judge the non empty attribute in the object as a set condition, and the set tag can remove the redundant comma  

Scenario: the user does not modify all field attribute values. There may only be name and age

<update id="update">
        update demo_user
        <set>
            <if test="name !=null">name=#{name},</if>
            <if test="age !=null"> age=#{age}</if>
            <if test="sex !=null">sex=#{sex}</if>
        </set>
        where id=#{id}
    </update>

3.3 Sql-choose when otherwise

Requirement: query by criteria. If there is a name, query by name; if not, query by sex

Branching structure of MyBatis  

<select id="find3" resultType="DemoUser">
        select * from demo_user
        where
        <choose>
            <when test="name!=null">name=#{name}</when>
            <otherwise>sex=#{sex}</otherwise>
        </choose>
    </select>

Explanation:

choose stands for branch structure. Only one condition is valid

when: similar to if

otherwise: if the above conditions are invalid, the conditions here will take effect

3.4 resulttype and ResultMap

Build table→edit Dept pojo class→mapper Interface→Mapping file→Configuring in the core configuration file mapper
 be careful pojo Class to implement the serialization interface

resultType note that automatic data encapsulation can only be realized when the field name in the result set is consistent with the attribute name

resultMap. When the field name in the result set is inconsistent with the attribute name, user-defined data encapsulation can also be realized

The resultMap tag is used to customize the mapping relationship

pojo class

package com.jt.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

import java.io.Serializable;

@Data
@Accessors(chain = true)
@NoArgsConstructor
@AllArgsConstructor
public class Dept implements Serializable {
    private Integer deptId;
    private String deptName;
}

mapper interface

public interface DeptMapper {

    List<Dept> findAll(Dept dept);
}

  Mapping file

 <select id="findAll" resultMap="deptDp">
        select * from dept

    </select>

    <resultMap id="deptDp" type="Dept">
        <id column="dept_id" property="deptId"/>
        <result column="dept_name" property="deptName"/>
    </resultMap>

test class

public class TestMybatis {
    SqlSessionFactory sqlSessionFactory;

    @Test
    @BeforeEach
    public void init() throws IOException {
    //1. Specify resource file
    String resource="mybatis/mybatis-config.xml";
        InputStream inputstream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputstream);
    }
    @Test
    public void test1(){
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        Dept dept= new Dept();
        List<Dept> list=mapper.findAll(dept);
        System.out.println(list);
        sqlSession.close();
    }