Mybatis學習之路3

@Param()註解

• 引用類型不需要加
• 如果只有一個基本類型的，可以忽略
• 在 sql 中引用的就是在註解中設定的欄位名

高級結果映射

public class Student {
    private int id;
    private String name;
    private Teacher teacherr;
}
public class Teacher {
    private int id;
    private String name;
}

多對一：

<select id="getStudentsWithTeacher2" resultMap="studentTeac2">
    select s.id sid,s.name sname,t.id tid,t.name tname from student s join teacher t on s.tid = t.id;
</select>
<resultMap id="studentTeac2" type="student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacherr" javaType="teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
    </association>
</resultMap>

一對多

<select id="getStudentsWithTeacher2" resultMap="studentTeac2">
    select s.id sid,s.name sname,t.id tid,t.name tname from student s join teacher t on s.tid = t.id;
</select>
<resultMap id="studentTeac2" type="student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacherr" javaType="teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
    </association>
</resultMap>

動態 sql

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

實體類

public class Blog {
    private String id;
    private String name;
    private String author;
    private Date create_time;
    private int views;
}

if

<select id="findBlog" parameterType="map" resultType="blog">
    <!--此處為了sql語句能夠執行只能加上條件-->
    select * from blog where views>1
    <if test="name !=null">
        and name = #{name}
    </if>
    <if test="author != null">
        and author like "%"#{author}"%"
    </if>
</select>

@Test
public void findBlog() {
    ...
    Map map = new HashMap();
    map.put("author", "w");
    map.put("name", "Maven配置");
    mapper.findBlog(map);
    ...
}

如果上面的 views>1 也變成動態條件那麼語句就會變成這樣

<select id="findBlog" parameterType="map" resultType="blog">
    <!--此處為了sql語句能夠執行只能加上條件-->
    select * from blog where
    <if test="views !=null">
        views = #{views}
    </if>
    <if test="name !=null">
        and name = #{name}
    </if>
    <if test="author != null">
        and author like "%"#{author}"%"
    </if>
</select>

如果此時 views 為空，那麼 sql 語句就出現了錯誤 select * from blog where and... select * from blog where 此時就需要用 where 來解決

where

where 會根據條件去掉 and 或者 or 來使 sql 能夠正確執行

<select id="findBlog" parameterType="blog" resultType="blog">
    select * from blog
    <where>
        <if test="name !=null">
            name = #{name}
        </if>
        <if test="author != null">
            and author like "%"#{author}"%"
        </if>
    </where>
</select>

choose (when, otherwise)

相當於 Java 中的 switch case 語句匹配條件中的一個若條件都不符合就匹配 otherwise 中的條件

<select id="findBlogByRequirement" parameterType="map" resultType="blog">
    select * from blog where
    <choose>
        <when test="id != null">
            id = #{id}
        </when>
        <when test="name != null">
            name = #{name}
        </when>
        <otherwise>
            views > 1
        </otherwise>
    </choose>
</select>

set

根據條件判斷要影響的列

<update id="updateBlog" parameterType="blog">
    update blog
    <set>
        <if test="name != null">
            name = #{name},
        </if>
        <if test="author">
            author = #{author},
        </if>
        <if test="views != 0">
            views = #{views}
        </if>
    </set>
    where id = #{id}
</update>

sql 語句塊

<sql id="ifn_name">
    <if test="name != null">
        name = #{name}
    </if>
</sql>

使用：

<include refid="ifn_name"/>

foreach

<select id="findBlogByRequirement2" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <foreach collection="names" item="na" open="name in (" separator="," close=")">
            #{na}
        </foreach>
    </where>
</select>

Map map = new HashMap();
List<String> names = new ArrayList<>();
names.add("mybatis配置");
//names.add("Maven配置");
map.put("names", names);
List<Blog> blogs = mapper.findBlogByRequirement2(map);
for (Blog blog : blogs) {
    System.out.println(blog);
}