MyBatis几种SQL写法

目录

1. 批量操作:通过标签支持批量插入

2. 批量操作:通过标签支持批量更新

3. 批量操作：通过标签支持批量删除

4. 动态SQL

3. 多条件分支查询

4. SQL语句优化：使用标签避免多余的AND或OR关键字。

5. 注解方式使用MyBatis

6. 一对多

7. 多对一：每个评论（Comment）都属于一篇文章（Article），并且每篇文章可以有多个评论。

8. MyBatis-Plus集成

---

1. 批量操作:通过<foreach>标签支持批量插入

<insert id="batchInsert" parameterType="java.util.List">INSERT INTO user (username, email,phone, create_time) VALUES<foreach collection="list" item="item" separator=",">(#{item.username}, #{item.email},#{item.phone}, #{item.createTime})</foreach>
</insert>

2. 批量操作:通过<foreach>标签支持批量更新

<update id="batchUpdate" parameterType="java.util.List"><foreach collection="list" item="item" separator=";">UPDATE userSET username = #{item.username}, email = #{item.email}WHERE id = #{item.id}</foreach>
</update>

3. 批量操作：通过<foreach>标签支持批量删除

<delete id="batchDelete" parameterType="java.util.List">DELETE FROM user WHERE id IN<foreach collection="list" item="id" open="(" separator="," close=")">#{id}</foreach>
</delete>

4. 动态SQL

<select id="findUsers" resultType="User">SELECT * FROM userWHERE 1=1<if test="username != null and username != ''">AND username LIKE CONCAT('%', #{username}, '%')</if><if test="email != null and email != ''">AND email = #{email}</if><if test="status != null">AND status = #{status}</if>
</select>

3. 多条件分支查询

<select id="findUsersByCondition" resultType="User">SELECT * FROM user<where><choose><when test="searchType == 'username'">username LIKE CONCAT('%', #{keyword}, '%')</when><when test="searchType == 'email'">email LIKE CONCAT('%', #{keyword}, '%')</when><otherwise>(username LIKE CONCAT('%', #{keyword}, '%') OR email LIKE CONCAT('%', #{keyword}, '%'))</otherwise></choose></where>
</select>

4. SQL语句优化：使用<trim>标签避免多余的AND或OR关键字。

<select id="findUsers" resultType="User">SELECT * FROM user<trim prefix="WHERE" prefixOverrides="AND |OR "><if test="username != null and username != ''">AND username LIKE CONCAT('%', #{username}, '%')</if><if test="email != null and email != ''">AND email = #{email}</if><if test="status != null">AND status = #{status}</if></trim>
</select>

5. 注解方式使用MyBatis

public interface UserMapper {@Select("SELECT * FROM user WHERE id = #{id}")User getUserById(Long id);@Insert("INSERT INTO user (username, email, create_time) VALUES (#{username}, #{email}, #{createTime})")@Options(useGeneratedKeys = true, keyProperty = "id")int insertUser(User user);@Update("UPDATE user SET username = #{username}, email = #{email} WHERE id = #{id}")int updateUser(User user);@Delete("DELETE FROM user WHERE id = #{id}")int deleteUser(Long id);
}

6. 一对多

<resultMap id="userWithOrdersMap" type="User"><id property="id" column="user_id"/><result property="username" column="username"/><collection property="orders" ofType="Order"><id property="id" column="order_id"/><result property="orderNumber" column="order_number"/><result property="createTime" column="order_create_time"/></collection>
</resultMap><select id="getUserWithOrders" resultMap="userWithOrdersMap">SELECT u.id as user_id, u.username, o.id as order_id, o.order_number, o.create_time as order_create_timeFROM user uLEFT JOIN orders o ON u.id = o.user_idWHERE u.id = #{userId}
</select>

7. 多对一：每个评论（Comment）都属于一篇文章（Article），并且每篇文章可以有多个评论。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.example.mapper.ArticleMapper"><!-- 定义 Comment 的 resultMap --><resultMap id="commentWithArticleMap" type="Comment"><id property="id" column="comment_id"/><result property="content" column="comment_content"/><result property="createTime" column="comment_create_time"/><association property="article" javaType="Article"><id property="id" column="article_id"/><result property="title" column="article_title"/><result property="content" column="article_content"/></association></resultMap><!-- 定义 Article 的 resultMap --><resultMap id="articleWithCommentsMap" type="Article"><id property="id" column="article_id"/><result property="title" column="article_title"/><result property="content" column="article_content"/><collection property="comments" ofType="Comment" resultMap="commentWithArticleMap"/></resultMap><!-- 查询文章及其评论列表 --><select id="getArticleWithComments" resultMap="articleWithCommentsMap">SELECT a.id as article_id,a.title as article_title,a.content as article_content,c.id as comment_id,c.content as comment_content,c.create_time as comment_create_timeFROM article aLEFT JOIN comment c ON a.id = c.article_idWHERE a.id = #{articleId}</select></mapper>

8. MyBatis-Plus集成

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {public List<User> findUsersByCondition(String username, String email) {return this.list(new QueryWrapper<User>().like(StringUtils.isNotBlank(username), "username", username).eq(StringUtils.isNotBlank(email), "email", email));}
}