MyBatis，这个从iBatis演变而来的Java持久层框架，凭借其强大的功能和性能，早已成为企业级应用的首选。本文展示几种MyBatis写法，保证数据库操作既高效又灵活。
1. 批量操作
批量操作是提升数据库操作效率的重要手段。MyBatis提供了标签，可以轻松实现批量插入、更新或删除操作。
批量插入：
<insert id="batchInsert" parameterType="java.util.List">
INSERT INTO user (username, email, create_time)
VALUES
<foreach collection="list" item="item" separator=",">
(#{item.username}, #{item.email}, #{item.createTime})
</foreach>
</insert>
批量更新：
<update id="batchUpdate" parameterType="java.util.List">
<foreach collection="list" item="item" separator=";">
UPDATE user
SET username=#{item.username}, email = #{item.email}
WHERE id=#{item.id}
</foreach>
</update>
批量删除：
<delete id="batchDelete" parameterType="java.util.List">
DELETE FROM user
WHERE id IN
<foreach collection="list" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
2. 动态SQL
动态SQL是MyBatis的强大特性之一，它允许你根据不同的条件动态构建SQL语句。标签是实现动态SQL的核心。
动态查询：
<select id="findUsers" resultType="User">
SELECT * FROM user WHERE 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>
多条件分支查询：
<select id="findUsersByCondition" resultType="User">
SELECT * FROM user WHERE 1=1
<choose>
<when test="searchType == 'username'">
AND username LIKE CONCAT('%', #{keyword}, '%')
</when>
<when test="searchType == 'email'">
AND email LIKE CONCAT('%', #{keyword}, '%')
</when>
<otherwise>
AND (username LIKE CONCAT('%', #{keyword}, '%') OR email LIKE CONCAT('%', #{keyword}, '%'))
</otherwise>
</choose>
</select>
3. SQL语句优化
标签可以帮助你优化生成的SQL语句，避免多余的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>
4. 自动生成主键
在插入操作中，我们经常需要获取数据库自动生成的主键。MyBatis提供了标签来实现这一功能。
自动生成主键：
<insert id="insertUser" parameterType="User" useGeneratedKeys="true" keyProperty="id">
<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
SELECT LAST_INSERT_ID()
</selectKey>
INSERT INTO user (username, email, create_time) VALUES (#{username}, #{email}, #{createTime})
</insert>
5. 使用注解
除了XML配置，MyBatis还支持使用注解来定义SQL操作，这种方式使代码更加简洁。
使用注解查询用户：
@Mapper
public interface UserMapper {
@Select("SELECT * FROM user WHERE id = #{id}")
User selectUserById(@Param("id") Integer id);
}
6. 关联查询
MyBatis支持多种关联查询方式，包括嵌套查询和延迟加载。
嵌套查询：
<select id="selectUserWithOrders" resultMap="userOrderResultMap">
SELECT * FROM users WHERE id = #{id}
</select>
<resultMap id="userOrderResultMap" type="User">
<id property="id" column="id"/>
<result property="name" column="name"/>
<collection property="orders" ofType="Order" select="selectOrdersByUserId" column="id"/>
</resultMap>
<select id="selectOrdersByUserId" resultType="Order">
SELECT * FROM orders WHERE user_id = #{userId}
</select>
延迟加载： 在MyBatis配置文件中启用延迟加载，可以减少数据库的访问次数。
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
7. 插件扩展
通过拦截器，MyBatis允许你实现插件，改变其默认行为。插件通过实现Interceptor接口，拦截SQL执行过程中的四个接口：Executor、StatementHandler、ResultSetHandler和ParameterHandler。
自定义插件：
@Intercepts({@Signature(type = StatementHandler.class, method = "parameterize", args = java.sql.Statement.class)})
public class MyPlugin implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
System.out.println("=========MyPlugin========");
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}
在全局配置文件中注册插件：
<plugins>
<plugin interceptor="com.fly.plugin.MyPlugin"/>
</plugins>
8. 其他常用写法
更新操作：
<update id="updateUser" parameterType="User">
UPDATE users
<trim prefix="SET" suffixOverrides=",">
<if test="name != null">name = #{name},</if>
<if test="age != null">age = #{age},</if>
</trim>
WHERE id=#{id}
</update>
删除操作：
<delete id="deleteUserById" parameterType="int">
DELETE FROM user WHERE id = #{id}
</delete>
查询操作：
<select id="selectUserByUsername" parameterType="String" resultType="User">
SELECT * FROM user WHERE username = #{username}
</select>
插入操作：
<insert id="insertUser" parameterType="User">
INSERT INTO user (username, email, create_time) VALUES (#{username}, #{email}, #{createTime})
</insert>
多表查询（一对一）：
<select id="selectOrderWithUser" resultType="Order">
SELECT o.*, u.username, u.email
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.id = #{id}
</select>
多表查询（一对多）：
<select id="selectUserWithOrders" resultType="User">
SELECT u.*, GROUP_CONCAT(o.order_id) as orderIds
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id
</select>
多表查询（多对多）：
<select id="selectStudentsWithCourses" resultType="Student">
SELECT s.*, GROUP_CONCAT(c.course_name) as courseNames
FROM students s
LEFT JOIN student_courses sc ON s.id = sc.student_id
LEFT JOIN courses c ON sc.course_id = c.id
GROUP BY s.id
</select>