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 userSET username=#{item.username}, email = #{item.email}WHERE id=#{item.id}</foreach>
</update>
批量删除：
<delete id="batchDelete" parameterType="java.util.List">DELETE FROM userWHERE 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 {@Overridepublic Object intercept(Invocation invocation) throws Throwable {System.out.println("=========MyPlugin========");return invocation.proceed();}@Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this);}@Overridepublic 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.emailFROM orders oLEFT JOIN users u ON o.user_id = u.idWHERE o.id = #{id}
</select>
多表查询（一对多）：
<select id="selectUserWithOrders" resultType="User">SELECT u.*, GROUP_CONCAT(o.order_id) as orderIdsFROM users uLEFT JOIN orders o ON u.id = o.user_idGROUP BY u.id
</select>
多表查询（多对多）：
<select id="selectStudentsWithCourses" resultType="Student">SELECT s.*, GROUP_CONCAT(c.course_name) as courseNamesFROM students sLEFT JOIN student_courses sc ON s.id = sc.student_idLEFT JOIN courses c ON sc.course_id = c.idGROUP BY s.id
</select>
