MyBatis简单动态SQL的使用

最新推荐文章于 2024-08-21 23:39:15 发布

码农小刘_大力水手

最新推荐文章于 2024-08-21 23:39:15 发布

阅读量82

点赞数

分类专栏： MyBatis 文章标签： mybatis

本文链接：https://blog.csdn.net/qq_48472773/article/details/132799592

版权

MyBatis 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

where+if（多重查询）

<select id="findByCondition" resultType="com.xiaoliu.pojo.User">
        select * from 表名
        <where>
            <if test="字符串 != null and 字符串 != ''">
                <!--模糊查询:and username like concat('%',#{username},'%')-->
                and 条件 = 值
            </if>
            <if test="字符串 != null and 字符串 != ''">
                and 条件 = 值
            </if>
            <if test="字符串 != null and 字符串 != ''">
                and 条件 = 值
            </if>
        </where>
    </select>
<!-- 具体例子 -->
<select id="findByCondition" resultType="com.xiaoliu.pojo.User">
        select * from tb_user
        <where>
            <if test="username != null and username != ''">
                and username like concat('%',#{username},'%')
            </if>
            <if test="gender != null and gender != ''">
                and gender = #{gender}
            </if>
            <if test="addr != null and addr != ''">
                and addr = #{addr}
            </if>
        </where>
    </select>

set+if（修改数据）

<update id="modifyUser">
        update tb_user
        <set>
            <if test="username != null and username != ''">
                username = #{username},
            </if>
            <if test="password != null and password != ''">
                password = #{password},
            </if>
            <if test="gender != null and gender != ''">
                gender = #{gender},
            </if>
            <if test="addr != null and addr != ''">
                addr = #{addr},
            </if>
        </set>
            where id=#{id}
</update>

foreach（批量操作）

按照id查找多条数据

//接口
List<User> findByIds(@Param("ids") List<Integer> ids);
//映射文件
<select id="findByIds" resultType="com.itheima.pojo.User">
        select * from tb_user where id in 
        <foreach collection="ids" open="(" close=")" separator="," item="id">
            #{id}
        </foreach>
</select>

按照id删除多条数据

//接口
void removeByIds(@Param("ids") List<Integer> ids);
//映射文件
<delete id="removeByIds">
        delete from tb_user where id in 
        <foreach collection="ids" open="(" close=")" separator="," item="id">
            #{id}
        </foreach>
</delete>

插入多条数据

//接口
void saveUsers(List<User> users);
//映射文件
<insert id="saveUsers" useGeneratedKeys="true" keyProperty="id">
        insert into tb_user values
        <foreach collection="collection" separator="," item="itemUsers">
         (null,#{itemUsers.username},#{itemUsers.password},#{itemUsers.gender},#{itemUsers.addr})
        </foreach>
</insert>

sql+include（属性调用）

<!--sql标签 作用:定义一些属性可以直接用-->
<sql id="baseColumn">
        id,
        username,
        password,
        gender,
        addr
    </sql>

<!--include标签 作用:替换相应的* -->
<include refid="baseColumn"/>