Mybatis中的动态sql

Mybatis中的动态sql

一、查询

1、标签的使用

<sql id="studentInfo">
    st.id,
    st.`name`,
    st.student_num,
    st.gender,
    st.relation_school_id,
    st.relation_department_id,
    st.relation_class_id,
    st.group_id,
    st.group_leader,
    st.telephone,
    st.address,
    st.parent,parent_telephone,
    st.is_group_leader,
    st.student_job,
    st.user_id,
    st.`status`,
    st.idcard_number,
</sql>

把需要查询的常用字段放入标签中，后续使用的标签使用

<include refid="studentInfo"></include>

id与refid对应

2、查询语句

<select id="selectWithOtherInfo" resultType="com.above.practical.dto.StudentDto">
    SELECT
    <include refid="studentInfo"></include>
    sc.`name` AS schoolName,
    `c`.`name` AS className,
    g.`name` AS groupName,
    dep.`name` AS departmentName
    FROM
    student_info AS st
    LEFT JOIN school AS sc ON st.relation_school_id = sc.id
    LEFT JOIN class_info AS c ON st.relation_class_id = c.id
    LEFT JOIN group_info AS g ON st.group_id = g.id
    LEFT JOIN departments AS dep ON st.relation_department_id = dep.id
    Where
    st.`status` = 1
    <if test="schoolId != null">
        AND st.relation_school_id = #{schoolId}
    </if>
    <if test="classId != null">
        AND st.relation_class_id = #{classId}
    </if>
    <if test="departmentId != null">
        AND st.relation_department_id = #{departmentId}
    </if>
    <if test="groupId != null">
        AND st.group_id = #{groupId}
    </if>
    <if test="key != null">
        AND st.`name` LIKE CONCAT("%",#{key},"%")
    </if>
    ORDER BY st.id ASC
    <if test="(page != null) and (size != null)">
        limit #{page,jdbcType=INTEGER},#{size,jdbcType=INTEGER}
    </if>
</select>

别名

AS取别名，额外字段可通过别名来映射到实体类。

动态sql

使用标签实现动态sql

标签中有条件语句时产生WHERE。

中判断条件

赋值

赋值使用#{}来取值

排序

ORDER BY为排序，ASC为升序，DESC为降序

模糊查询

LIKE中，动态sql的条件用java拼接字符串或者使用CONCAT（”%“，#{}，”%“）来拼接使用；

分页

分页字段使用LIMIT page，size；

page传值时需要（-1）运算。

传出时的pages使用查出的数据数count来计算获得

pages = (count + size -1)/size;