MyBatis通用的10种写法总结大全

1. 基础查询

• 示例1：

xml
<select id="selectById" resultType="com.example.model.User">
select * from user where id = #{id}
</select>

• 示例2：

xml
<select id="findUserByName" resultType="com.example.model.User">
select * from user where name like concat('%',#{name},'%')
</select>

2. 动态SQL

• 示例1：

xml
<select id="findUserByCondition" resultType="com.example.model.User">
select * from user
<where>
<if test="id != null"> and id = #{id} </if>
<if test="name != null"> and name = #{name} </if>
</where>
</select>

• 示例2：

xml
<update id="updateUser" parameterType="com.example.model.User">
update user
<set>
<if test="name != null"> name = #{name},</if>
<if test="age != null"> age = #{age},</if>
</set>
where id = #{id}
</update>

3. 分页查询

• 示例1：

xml
<select id="getPageData" resultType="com.example.model.User">
select * from user
limit #{startIndex}, #{pageSize}
</select>

• 示例2：

xml
<select id="getPageDataByCondition" resultType="com.example.model.User">
select * from user
<where>
<if test="name != null"> and name like concat('%',#{name},'%') </if>
</where>
limit #{startIndex}, #{pageSize}
</select>

4. 前N条数据查询

• 示例1：

xml
<select id="findTopN" resultType="com.example.model.User">
select * from user
order by id desc
limit 0, #{n}
</select>

• 示例2：

xml
<select id="findTopNByCondition" resultType="com.example.model.User">
select * from user
where gender = #{gender}
order by id desc
limit 0, #{n}
</select>

5. 批量插入

• 示例1：

xml
<insert id="batchInsert" parameterType="java.util.List">
insert into user(name, age, gender)
values
<foreach collection="list" item="item" separator=",">
(#{item.name}, #{item.age}, #{item.gender})
</foreach>
</insert>

• 示例2：

xml
<insert id="batchInsertByCondition" parameterType="java.util.List">
insert into user(name, age, gender)
values
<foreach collection="list" item="item" separator=",">
<if test="item.gender != null">
(#{item.name}, #{item.age}, #{item.gender})
</if>
<if test="item.gender == null">
(#{item.name}, #{item.age}, '未知')
</if>
</foreach>
</insert>

6. 批量更新

• 示例1：

xml
<update id="batchUpdate" parameterType="java.util.List">
<foreach collection="list" item="item">
update user set name = #{item.name}, age = #{item.age} where id = #{item.id};
</foreach>
</update>

• 示例2：

xml
<update id="batchUpdateByCondition" parameterType="java.util.List">
<foreach collection="list" item="item">
update user
<set>
<if test="item.name != null"> name = #{item.name},</if>
<if test="item.age != null"> age = #{item.age},</if>
</set>
where id = #{item.id};
</foreach>
</update>

7. 批量删除

• 示例1：

xml
<delete id="batchDelete" parameterType="java.util.List">
delete from user where id in
<foreach collection="list" open="(" close=")" separator=",">
#{item}
</foreach>
</delete>

• 示例2：

xml
<delete id="batchDeleteByCondition" parameterType="java.util.List">
delete from user where gender = #{gender} and age between #{startAge} and #{endAge}
and id in
<foreach collection="list" open="(" close=")" separator=",">
#{item}
</foreach>
</delete>

8. 多表联查

• 示例1：

```xml

select u., o.
from user u
inner join orders o
on u.id = o.user_id
where u.id = #{id}

...



...


```

• 示例2：

```xml

select u., o., g.*
from user u
inner join orders o
on u.id = o.user_id
inner join order_goods og
on o.id = og.order_id
inner join goods g
on og.goods_id = g.id
where u.id = #{id}

...



...


...


```

9. 存储过程调用

• 示例1：

xml
<select id="callQuery" statementType="CALLABLE" parameterType="com.example.dto.QueryParam">
{call query_users(#{param.name, mode=IN, jdbcType=VARCHAR}, #{param.total, mode=OUT, jdbcType=INTEGER})}
</select>

• 示例2：

xml
<insert id="callInsert" statementType="CALLABLE" parameterType="com.example.model.User">
{call insert_user(#{name, mode=IN, jdbcType=VARCHAR}, #{age, mode=IN, jdbcType=INTEGER},
#{gender, mode=IN, jdbcType=VARCHAR}, #{id, mode=OUT, jdbcType=INTEGER})}
</insert>

10. SQL注入攻击防御

• 示例1：

xml
<select id="findUserByName" resultType="com.example.model.User">
select * from user where name like concat('%',#{name,jdbcType=VARCHAR},'%')
</select>

• 示例2：

xml
<insert id="insertUser" parameterType="com.example.model.User">
insert into user(name, age, gender)
values (#{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}, #{gender,jdbcType=VARCHAR})
select last_insert_id()
</insert>

上述10种MyBatis的写法都可以广泛应用于各种需求场景，帮助开发者快速、高效地完成各种数据库操作。