MyBatis 速查表

本文档提供 MyBatis 常用配置和语法的快速参考。

核心配置文件

完整配置模板

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    "https://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <properties resource="db.properties"/>
    
    <settings>
        <setting name="cacheEnabled" value="true"/>
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="useGeneratedKeys" value="true"/>
        <setting name="defaultExecutorType" value="SIMPLE"/>
        <setting name="logImpl" value="SLF4J"/>
    </settings>
    
    <typeAliases>
        <package name="com.example.entity"/>
    </typeAliases>
    
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <package name="com.example.mapper"/>
    </mappers>
</configuration>

常用设置项

设置项	说明	默认值
cacheEnabled	开启二级缓存	true
lazyLoadingEnabled	延迟加载	false
mapUnderscoreToCamelCase	驼峰命名映射	false
useGeneratedKeys	主键回填	false
defaultExecutorType	执行器类型	SIMPLE
localCacheScope	一级缓存作用域	SESSION
jdbcTypeForNull	空值类型	OTHER

映射文件

基本结构

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
    "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.mapper.UserMapper">
    
    <resultMap id="BaseResultMap" type="User">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
    </resultMap>
    
    <sql id="Base_Column_List">
        id, username, email, phone
    </sql>
    
</mapper>

CRUD 语句

<!-- 查询 -->
<select id="selectById" resultType="User">
    SELECT * FROM user WHERE id = #{id}
</select>

<!-- 插入 -->
<insert id="insert" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO user (username, email) VALUES (#{username}, #{email})
</insert>

<!-- 更新 -->
<update id="update">
    UPDATE user SET username = #{username} WHERE id = #{id}
</update>

<!-- 删除 -->
<delete id="deleteById">
    DELETE FROM user WHERE id = #{id}
</delete>

动态 SQL

if 条件

<if test="username != null and username != ''">
    AND username = #{username}
</if>

where 子句

<where>
    <if test="username != null">
        AND username = #{username}
    </if>
    <if test="email != null">
        AND email = #{email}
    </if>
</where>

set 子句

<set>
    <if test="username != null">username = #{username},</if>
    <if test="email != null">email = #{email},</if>
</set>

foreach 循环

<!-- IN 查询 -->
<foreach collection="ids" item="id" open="(" separator="," close=")">
    #{id}
</foreach>

<!-- 批量插入 -->
<foreach collection="list" item="user" separator=",">
    (#{user.username}, #{user.email})
</foreach>

choose 选择

<choose>
    <when test="id != null">
        AND id = #{id}
    </when>
    <when test="username != null">
        AND username = #{username}
    </when>
    <otherwise>
        AND status = 1
    </otherwise>
</choose>

结果映射

基本映射

<resultMap id="BaseResultMap" type="User">
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    <result column="create_time" property="createTime"/>
</resultMap>

一对一关联

<resultMap id="UserWithOrderMap" type="User">
    <id column="id" property="id"/>
    <association property="order" javaType="Order">
        <id column="order_id" property="id"/>
        <result column="order_no" property="orderNo"/>
    </association>
</resultMap>

一对多关联

<resultMap id="UserWithOrdersMap" type="User">
    <id column="id" property="id"/>
    <collection property="orders" ofType="Order">
        <id column="order_id" property="id"/>
        <result column="order_no" property="orderNo"/>
    </collection>
</resultMap>

注解开发

基本注解

@Select("SELECT * FROM user WHERE id = #{id}")
User selectById(Long id);

@Insert("INSERT INTO user (username) VALUES (#{username})")
@Options(useGeneratedKeys = true, keyProperty = "id")
int insert(User user);

@Update("UPDATE user SET username = #{username} WHERE id = #{id}")
int update(User user);

@Delete("DELETE FROM user WHERE id = #{id}")
int deleteById(Long id);

结果映射注解

@Results(id = "userMap", value = {
    @Result(column = "id", property = "id", id = true),
    @Result(column = "username", property = "username")
})
@Select("SELECT * FROM user WHERE id = #{id}")
User selectById(Long id);

关联映射注解

@Results({
    @Result(column = "id", property = "id"),
    @Result(column = "id", property = "orders",
            many = @Many(select = "selectOrdersByUserId"))
})
@Select("SELECT * FROM user WHERE id = #{id}")
User selectWithOrders(Long id);

参数传递

参数占位符

占位符	说明	示例
#{}	预编译参数（安全）	#{username}
${}	字符串替换（不安全）	${tableName}

参数类型

<!-- 单个参数 -->
WHERE id = #{id}

<!-- 多个参数（使用 @Param） -->
WHERE username = #{username} AND email = #{email}

<!-- 对象参数 -->
WHERE username = #{username} AND email = #{email}

<!-- 集合参数 -->
<foreach collection="list" item="item">
    #{item}
</foreach>

缓存配置

一级缓存

<settings>
    <!-- SESSION 或 STATEMENT -->
    <setting name="localCacheScope" value="SESSION"/>
</settings>

二级缓存

<!-- XML 方式 -->
<cache eviction="LRU" flushInterval="60000" size="1024"/>

<!-- 注解方式 -->
@CacheNamespace
public interface UserMapper { }

类型别名

内置别名

别名	类型
int	Integer
long	Long
string	String
date	Date
map	Map
list	List

自定义别名

<typeAliases>
    <typeAlias type="com.example.entity.User" alias="User"/>
    <!-- 或包扫描 -->
    <package name="com.example.entity"/>
</typeAliases>

JDBC 类型

JDBC 类型	Java 类型
BIT	Boolean
TINYINT	Byte
SMALLINT	Short
INTEGER	Integer
BIGINT	Long
FLOAT	Float
DOUBLE	Double
CHAR	String
VARCHAR	String
DATE	Date
TIMESTAMP	Date
BLOB	byte[]
CLOB	String

常用工具代码

SqlSession 工具类

public class SqlSessionUtil {
    private static final SqlSessionFactory factory;
    
    static {
        try (InputStream is = Resources.getResourceAsStream("mybatis-config.xml")) {
            factory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
    
    public static SqlSession getSession() {
        return factory.openSession();
    }
    
    public static SqlSession getSession(boolean autoCommit) {
        return factory.openSession(autoCommit);
    }
}

基本使用流程

try (SqlSession session = SqlSessionUtil.getSession()) {
    UserMapper mapper = session.getMapper(UserMapper.class);
    
    // 查询
    User user = mapper.selectById(1L);
    
    // 插入
    User newUser = new User();
    newUser.setUsername("test");
    mapper.insert(newUser);
    
    // 提交事务
    session.commit();
}

XML 转义字符

字符	转义	说明
<	&lt;	小于
>	&gt;	大于
&	&amp;	和号
'	&apos;	单引号
"	&quot;	双引号

<!-- 使用转义 -->
<if test="age &lt; 18">...</if>

<!-- 使用 CDATA -->
<if test="age < 18"><![CDATA[ AND age < 18 ]]></if>

常见问题解决

1. 找不到映射文件

确保 pom.xml 中配置了资源过滤：

<build>
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.xml</include>
            </includes>
        </resource>
    </resources>
</build>

2. 主键回填不生效

<insert id="insert" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO user (username) VALUES (#{username})
</insert>

3. 驼峰映射不生效

<settings>
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

4. 延迟加载不生效

<settings>
    <setting name="lazyLoadingEnabled" value="true"/>
    <setting name="aggressiveLazyLoading" value="false"/>
</settings>