sql

<sql> 是 MyBatis 里用来定义可复用 SQL 片段的标签，通常配合 <include> 使用。

基本语法

定义SQL片段

<sql id="baseColumn">
    id, name, age, create_time
</sql>

引用 SQL 片段

<select id="queryUser" resultType="User">
    SELECT 
    <include refid="baseColumn"/>
    FROM user
</select>

SELECT id, name, age, create_time FROM user

传参

因为是字符串拼接，因此只能使用${}

<sql id="orderBy">
    ORDER BY ${column} ${order}
</sql>

<select id="queryUser">
    SELECT * FROM user
    <include refid="orderBy">
        <property name="column" value="create_time"/>
        <property name="order" value="DESC"/>
    </include>
</select>

使用场景

复用查询字段

<sql id="userColumns">
    u.id, u.name, u.age, u.create_time
</sql>

<select id="listUser" resultType="User">
    SELECT <include refid="userColumns"/>
    FROM user u
</select>

复用 JOIN

<sql id="userJoinDept">
    LEFT JOIN dept d ON u.dept_id = d.id
</sql>

<select id="query">
    SELECT u.*, d.name
    FROM user u
    <include refid="userJoinDept"/>
</select>

复用WHERE

<sql id="userWhere">
    <where>
        <if test="name != null and name != ''">
            AND name LIKE CONCAT('%', #{name}, '%')
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
    </where>
</sql>

<select id="queryUser" resultType="User">
    SELECT * FROM user
    <include refid="userWhere"/>
</select>