Mybatis learning road 3

@Param() annotation

  • Reference type does not need to be added
  • If there is only one basic type, it can be ignored
  • What is referenced in sql is the field name set in the annotation

Advanced result mapping

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

Many to one:

<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>

One to many

<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>

Dynamic sql

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

Entity class

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">
    <!--Here for sql Statement can be executed only with conditions-->
    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 To configure");
    mapper.findBlog(map);
    ...
}

If the View > 1 above also becomes a dynamic condition, then the statement will become this way

<select id="findBlog" parameterType="map" resultType="blog">
    <!--Here for sql Statement can be executed only with conditions-->
    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>

If the views is empty at this time, the sql statement has an error select * from blog where and... select * from blog where, which needs to be solved with where

where

where will remove and or according to conditions to make sql execute correctly

<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)

It is equivalent to one of the switch case statement matching conditions in Java. If none of the conditions match, it will match the condition in other wise

<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

Determine the columns to be affected according to the conditions

<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 statement block

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

Use:

<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 To configure");
//names.add("Maven configuration");
map.put("names", names);
List<Blog> blogs = mapper.findBlogByRequirement2(map);
for (Blog blog : blogs) {
    System.out.println(blog);
}

Tags: Java SQL Maven Mybatis

Posted on Sat, 14 Mar 2020 11:30:58 -0400 by kutchbhi