Mybatis 常用 sql 语句汇总

查询所有用户

<select id="findAll" resultType="entity.User">
        select * from user
    </select>

查询指定用户

<select id="findById" resultType="entity.User" parameterType="java.lang.Integer">
    select * from user where id = #{id};
</select>

模糊查找，这里要用 @

<select id="findMohu" resultType="entity.User" parameterType="java.lang.String">
    select * from user where username like '%${value}%' or sex like '%${value}%';
</select>

判断查找，使用 if 语句，注意

<select id="selectByName" resultType="entity.User" parameterType="java.lang.String">
        select * from user
-- 满足 if 就只走前面，不满足就全都走
        <if test="username != null and username != ''">
            where username = #{username}
        </if>
</select>

删除用户

<delete id="deleteById" parameterType="java.lang.Integer">
    delete from user where id = #{id};
</delete>

更新用户

<update id="UpdateById" parameterType="entity.User">
    update user set sex = #{sex}, birthday = #{birthday}, address = #{address}, username = #{username} where id = #{id};
</update>

插入用户

<insert id="insertUser" parameterType="entity.User">
    insert into user (username, birthday, sex, address) values(#{username}, #{birthday}, #{sex}, #{address});
</insert>

插入用户并返回该用户的 id

<insert id="insertUserRetId" parameterType="entity.User">
        <selectKey keyProperty="id" resultType="int" order="AFTER">
            select LAST_INSERT_ID()
        </selectKey>
        insert into user (username, birthday, sex, address) values(#{username}, #{birthday}, #{sex}, #{address});
</insert>

这是很经典的语句

<select id="selectByMul" resultType="entity.User" parameterType="entity.User">
        select * from user
        -- 进入 where 条件（当 where 条件多个时）
        <where>
            -- 满足 if 就只走前面，不满足就全都走
            -- 如果username 不是空，就进入 if 语句
            <if test="username != null and username != ''">
                username = #{username}
            </if>
            -- 如果地址不是空，就进入 if 语句，选择地址等于的
            -- 如果地址是空，不进入 if 语句，相当于只执行上面的
            <if test="address != null and address != ''">
                and address = #{address}
            </if>
        </where>

    </select>

更新操作，使用 <set>

<update id="updateById" parameterType="entity.User">
        update user
        <set>
            <if test="username != null and username != ''">
                username = #{username}
            </if>
            <if test="address != null and address != ''">
                , address = #{address};
            </if>
            <if test="sex != null and sex != ''">
                , sex = #{sex};
            </if>
            <if test="birthday != null and birthday != ''">
                , birthday = #{birthday};
            </if>
            <where>
                id = #{id};
            </where>
        </set>
    </update>