Springboot+mybatis的增删改查写法怎么用

发布时间：2021-07-15 10:36:20 作者：chen
来源：亿速云阅读：290

# SpringBoot+MyBatis的增删改查写法详解

## 一、环境准备与项目搭建

### 1.1 创建SpringBoot项目
通过Spring Initializr（https://start.spring.io/）创建项目，勾选以下依赖：
- Spring Web
- MyBatis Framework
- MySQL Driver

或通过Maven手动添加依赖：

```xml
<dependencies>
    <!-- SpringBoot基础依赖 -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    
    <!-- MyBatis整合SpringBoot -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.0</version>
    </dependency>
    
    <!-- MySQL驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    
    <!-- Lombok简化代码 -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
</dependencies>

1.2 数据库配置

在application.yml中配置数据源：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test_db?useSSL=false&serverTimezone=UTC
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.example.demo.entity

二、实体类与Mapper接口

2.1 创建实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Long id;
    private String username;
    private String password;
    private String email;
    private Date createTime;
}

2.2 Mapper接口定义

@Mapper
public interface UserMapper {
    // 插入用户
    int insert(User user);
    
    // 根据ID删除
    int deleteById(Long id);
    
    // 更新用户信息
    int update(User user);
    
    // 根据ID查询
    User selectById(Long id);
    
    // 查询所有用户
    List<User> selectAll();
}

三、XML映射文件编写

在resources/mapper/下创建UserMapper.xml：

<?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.demo.mapper.UserMapper">
    
    <resultMap id="BaseResultMap" type="User">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="username" property="username" jdbcType="VARCHAR"/>
        <result column="password" property="password" jdbcType="VARCHAR"/>
        <result column="email" property="email" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
    </resultMap>
    
    <insert id="insert" parameterType="User" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO user(username, password, email, create_time)
        VALUES(#{username}, #{password}, #{email}, #{createTime})
    </insert>
    
    <delete id="deleteById" parameterType="Long">
        DELETE FROM user WHERE id = #{id}
    </delete>
    
    <update id="update" parameterType="User">
        UPDATE user SET
        username = #{username},
        password = #{password},
        email = #{email}
        WHERE id = #{id}
    </update>
    
    <select id="selectById" parameterType="Long" resultMap="BaseResultMap">
        SELECT * FROM user WHERE id = #{id}
    </select>
    
    <select id="selectAll" resultMap="BaseResultMap">
        SELECT * FROM user
    </select>
</mapper>

四、Service层实现

4.1 接口定义

public interface UserService {
    int addUser(User user);
    int deleteUser(Long id);
    int updateUser(User user);
    User getUserById(Long id);
    List<User> getAllUsers();
}

4.2 实现类

@Service
public class UserServiceImpl implements UserService {
    
    @Autowired
    private UserMapper userMapper;
    
    @Override
    public int addUser(User user) {
        user.setCreateTime(new Date());
        return userMapper.insert(user);
    }
    
    @Override
    public int deleteUser(Long id) {
        return userMapper.deleteById(id);
    }
    
    @Override
    public int updateUser(User user) {
        return userMapper.update(user);
    }
    
    @Override
    public User getUserById(Long id) {
        return userMapper.selectById(id);
    }
    
    @Override
    public List<User> getAllUsers() {
        return userMapper.selectAll();
    }
}

五、Controller层实现

@RestController
@RequestMapping("/api/user")
public class UserController {
    
    @Autowired
    private UserService userService;
    
    @PostMapping
    public Result addUser(@RequestBody User user) {
        int result = userService.addUser(user);
        return Result.success(result);
    }
    
    @DeleteMapping("/{id}")
    public Result deleteUser(@PathVariable Long id) {
        int result = userService.deleteUser(id);
        return Result.success(result);
    }
    
    @PutMapping
    public Result updateUser(@RequestBody User user) {
        int result = userService.updateUser(user);
        return Result.success(result);
    }
    
    @GetMapping("/{id}")
    public Result getUserById(@PathVariable Long id) {
        User user = userService.getUserById(id);
        return Result.success(user);
    }
    
    @GetMapping
    public Result getAllUsers() {
        List<User> users = userService.getAllUsers();
        return Result.success(users);
    }
}

// 统一返回结果封装
@Data
class Result<T> {
    private int code;
    private String msg;
    private T data;
    
    public static <T> Result<T> success(T data) {
        Result<T> result = new Result<>();
        result.setCode(200);
        result.setMsg("success");
        result.setData(data);
        return result;
    }
}

六、高级查询实现

6.1 动态SQL查询

<!-- 在UserMapper.xml中添加 -->
<select id="selectByCondition" parameterType="map" resultMap="BaseResultMap">
    SELECT * FROM user
    <where>
        <if test="username != null and username != ''">
            AND username LIKE CONCAT('%', #{username}, '%')
        </if>
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
        <if test="startTime != null">
            AND create_time >= #{startTime}
        </if>
        <if test="endTime != null">
            AND create_time <= #{endTime}
        </if>
    </where>
    ORDER BY create_time DESC
</select>

6.2 分页查询实现

// Mapper接口添加
List<User> selectByPage(@Param("offset") int offset, 
                      @Param("pageSize") int pageSize,
                      @Param("condition") Map<String, Object> condition);

// XML实现
<select id="selectByPage" resultMap="BaseResultMap">
    SELECT * FROM user
    <where>
        <include refid="conditionSql"/>
    </where>
    LIMIT #{offset}, #{pageSize}
</select>

<sql id="conditionSql">
    <if test="condition.username != null">
        AND username LIKE CONCAT('%', #{condition.username}, '%')
    </if>
</sql>

七、事务管理

在Service方法上添加@Transactional注解：

@Override
@Transactional
public int updateUser(User user) {
    // 业务逻辑...
    return userMapper.update(user);
}

八、常见问题解决

Mapper接口无法注入
- 确保添加了@Mapper注解或在启动类添加@MapperScan
XML文件找不到
- 检查application.yml中的mybatis.mapper-locations配置
字段名与属性名不一致
- 使用@Results注解或XML中的resultMap解决
分页查询性能优化
- 对于大数据量，建议使用PageHelper等分页插件

九、总结

本文详细介绍了SpringBoot整合MyBatis实现CRUD的完整流程，包含： 1. 项目环境搭建 2. 实体类与Mapper定义 3. XML映射文件编写 4. 分层架构实现 5. 高级查询技巧 6. 事务管理配置

通过这套标准化开发流程，可以快速实现企业级应用的数据库操作需求。实际开发中可根据业务需求进行扩展，如加入缓存、优化SQL等。

项目完整代码可参考：https://github.com/example/springboot-mybatis-demo “`

（全文约2200字）