Springboot+mybatis的增刪改查寫法怎么用

# 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);
}

八、常見問題解決

1. Mapper接口無法注入

   • 確保添加了@Mapper注解或在啟動類添加@MapperScan

2. XML文件找不到

   • 檢查application.yml中的mybatis.mapper-locations配置

3. 字段名與屬性名不一致

   • 使用@Results注解或XML中的resultMap解決

4. 分頁查詢性能優化

   • 對于大數據量，建議使用PageHelper等分頁插件

九、總結

本文詳細介紹了SpringBoot整合MyBatis實現CRUD的完整流程，包含： 1. 項目環境搭建 2. 實體類與Mapper定義 3. XML映射文件編寫 4. 分層架構實現 5. 高級查詢技巧 6. 事務管理配置

通過這套標準化開發流程，可以快速實現企業級應用的數據庫操作需求。實際開發中可根據業務需求進行擴展，如加入緩存、優化SQL等。

項目完整代碼可參考：https://github.com/example/springboot-mybatis-demo “`

（全文約2200字）