Mybatis的使用

大于、小于、等于

大于等于：>=
小于等于：<=

常用标签

1、choose、when、otherwise、if

多重条件判断；

应用场景：一个查询语句存在默认的条件

<choose>
    <when test="sortType != null and sortType != ''">
        <if test='sortType == "1"'>
            t3.sale_num desc
        </if>
        <if test='sortType == "2"'>
            t3.sell_price asc
        </if>
        <if test='sortType == "3"'>
            t3.create_time desc
        </if>
    </when>
    <otherwise>
        t3.score desc ,t3.sale_num desc
    </otherwise>
</choose>

2、foreach

 list

对应的mapper方法入参：List<Object> list

<foreach collection="list" separator="," item="item" open="(" close=")" index="index">
    #{item.property}
</foreach>

array

对应的mapper方法入参：String[] array

<foreach collection="array" separator="," item="item" open="(" close=")" index="index">
    #{item}
</foreach>

map

对应的mapper方法入参：Map map

<foreach collection="map" separator="," item="item" open="(" close=")" index="index">
    #{item.value}
</foreach>

3、《![CDATA[ ]]》

<![CDATA[ 这里写sql语句 ]]> : 标签内原本需要转义的字符可以直接原样输出

<select id="selectByCreatetimeBetween2" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from student
    where
    <![CDATA[  create_time >= #{minCreateTime} and create_time <= #{maxCreateTime}  ]]>
  </select>

 

一对一查询，association

如果查询出来的column相同，会出现覆盖的情况。需要用 as 指定别名

查询每一个学生，以及他们关联的班级。

<resultMap id="selectAllStudentAndClassBaseResultMap" type="com.liyk.mybatis.dto.StudentDto">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <association property="classs" javaType="com.liyk.mybatis.bean.Classs">
      <id column="c_id" property="id"/>
      <result column="class_name" property="className"/>
    </association>
  </resultMap>

  <!--查询语句-->
  <select id="selectAllStudentAndAssociationClass" resultMap="selectAllStudentAndClassBaseResultMap">
    SELECT
        student.id,
        student.`name`,
        classs.id AS c_id,
        classs.class_name
    FROM
        student student,
        classs
    WHERE
        student.class_id = classs.id
  </select>

查询结果：

 

 

一对多查询，collection

如果查询出来的column相同，会出现覆盖的情况。需要用 as 指定别名

查询每一个老师下的所有学生（一个老师有多个学生）

<resultMap id="selectStudentByTeacherIdResultMap" type="com.liyk.mybatis.dto.TeacherDto">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <collection property="students" javaType="java.util.ArrayList" ofType="com.liyk.mybatis.bean.Student">
      <id column="s_id" property="id"/>
      <result column="s_name" property="name"/>
      <result column="s_class_id" property="classId"/>
    </collection>
  </resultMap>

  <!--查询语句-->
  <select id="selectStudentByTeacherId" resultMap="selectStudentByTeacherIdResultMap">
    SELECT
        teacher.id,teacher.name,
        student.id as s_id,student.name as s_name,student.class_id as s_class_id
    FROM
        teacher teacher,
        teacher_and_student teastu,
        student student
    WHERE
        teacher.id = teastu.teacher_id
        AND student.id = teastu.student_id
  </select>

 查询结果：