MyBatis 实战案例：从 0 到 1 搭建企业级持久层

内容分享3个月前发布 -陆离不吃鱼-

1 1 0

MyBatis 实战案例：从 0 到 1 搭建企业级持久层

一、前言：为什么选择 MyBatis？

在企业级应用开发中，持久层是至关重大的一环。MyBatis 在 SQL 可控性 与 开发效率 之间取得了平衡：

相比全自动 ORM（如 Hibernate），它让开发者能编写和优化原生 SQL。
相比传统 JDBC，它大幅减少了样板代码。

因此，MyBatis 在电商、金融、政企系统中被广泛使用。本文将带你从零开始搭建一个 基于 Spring Boot + MyBatis 的持久层，并逐步覆盖 初级 → 中级 → 高级 → 企业级实战 场景。

二、项目搭建与环境配置

1. Maven 依赖

推荐使用 Spring Boot + MyBatis Starter 简化配置：

<dependencies>
    <!-- Web 层 (可选，如需提供 REST 接口) -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!-- JDBC & 数据源支持 -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>

    <!-- MyBatis 集成 -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.3.1</version>
    </dependency>

    <!-- MySQL 驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

    <!-- 分页插件 -->
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper-spring-boot-starter</artifactId>
        <version>1.4.7</version>
    </dependency>

    <!-- 开发工具，支持热加载 -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-devtools</artifactId>
        <optional>true</optional>
    </dependency>
</dependencies>

2. application.yml 配置

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mybatis_demo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.example.model
  configuration:
    map-underscore-to-camel-case: true
    cache-enabled: true
    default-executor-type: reuse

logging:
  level:
    com.example.mapper: debug

3. 表结构与实体类

CREATE TABLE `user` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    `email` VARCHAR(100) NOT NULL COMMENT '邮箱',
    `status` TINYINT DEFAULT 1 COMMENT '状态:0-禁用,1-启用',
    `version` INT DEFAULT 0 COMMENT '乐观锁版本号',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

public class User {
    private Long id;
    private String username;
    private String email;
    private Integer status;
    private Integer version;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
    // getter、setter省略
}

三、基础 CRUD 操作（初级）

1. Mapper 接口

@Mapper
public interface UserMapper {
    User getUserById(Long id);
    User getUserByUsername(String username);
    List<User> getAllUsers();
    int insertUser(User user);
    int updateUser(User user);
    int updateEmailById(@Param("id") Long id, @Param("email") String email);
    int deleteUserById(Long id);
}

2. Mapper XML

<mapper namespace="com.example.mapper.UserMapper">

    <resultMap id="userMap" type="User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="email" column="email"/>
        <result property="status" column="status"/>
        <result property="version" column="version"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
    </resultMap>

    <select id="getUserById" resultMap="userMap">
        SELECT * FROM user WHERE id = #{id}
    </select>

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

    <update id="updateUser">
        UPDATE user
        SET email = #{email}, status = #{status}, version = version + 1
        WHERE id = #{id} AND version = #{version}
    </update>

    <delete id="deleteUserById">
        DELETE FROM user WHERE id = #{id}
    </delete>
</mapper>

3. Service 层

@Service
@Transactional
public class UserService {
    @Autowired
    private UserMapper userMapper;

    public User getUserById(Long id) {
        return userMapper.getUserById(id);
    }

    public void addUser(User user) {
        if (userMapper.getUserByUsername(user.getUsername()) != null) {
            throw new RuntimeException("用户名已存在");
        }
        userMapper.insertUser(user);
    }

    public boolean updateUser(User user) {
        return userMapper.updateUser(user) > 0;
    }
}

四、进阶功能（中级）

1. 动态 SQL

<select id="findUsers" resultMap="userMap">
    SELECT * FROM user
    <where>
        <if test="username != null and username != ''">
            AND username LIKE CONCAT('%', #{username}, '%')
        </if>
        <if test="email != null and email != ''">
            AND email LIKE CONCAT('%', #{email}, '%')
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
    </where>
    ORDER BY id DESC
</select>

2. 批量操作

<insert id="batchInsertUsers">
    INSERT INTO user(username, email, status) VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.username}, #{user.email}, #{user.status})
    </foreach>
</insert>

3. 分页查询（PageHelper）

PageHelper.startPage(1, 10);
List<User> users = userMapper.findUsers("tom", 1);
PageInfo<User> pageInfo = new PageInfo<>(users);

五、高级特性（企业级）

1. 自定义 TypeHandler

@MappedTypes(UserStatus.class)
@MappedJdbcTypes(JdbcType.INTEGER)
public class UserStatusTypeHandler extends BaseTypeHandler<UserStatus> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, UserStatus parameter, JdbcType jdbcType) throws SQLException {
        ps.setInt(i, parameter.getCode());
    }
    @Override
    public UserStatus getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return UserStatus.fromCode(rs.getInt(columnName));
    }
}

2. 乐观锁

<update id="updateUser">
    UPDATE user
    SET email = #{email}, version = version + 1
    WHERE id = #{id} AND version = #{version}
</update>

3. 二级缓存

<cache type="org.mybatis.caches.ehcache.EhcacheCache">
    <property name="timeToLiveSeconds" value="3600"/>
    <property name="maxEntriesLocalHeap" value="1000"/>
</cache>

六、最佳实践与常见陷阱

避免 SQL 注入：使用 #{} 而不是 ${}。
避免 N+1 查询：合理使用 <association>、<collection>。
大数据处理：使用分页或流式查询。
批量优化：使用 ExecutorType.BATCH。
事务回滚：@Transactional(rollbackFor = Exception.class)。

七、项目结构示例

src/main/java/com/example
├── controller
│   └── UserController.java
├── service
│   └── UserService.java
├── mapper
│   └── UserMapper.java
├── model
│   └── User.java
├── handler
│   └── UserStatusTypeHandler.java
└── Application.java

src/main/resources
├── mapper
│   └── UserMapper.xml
└── application.yml