SpringBoot+MyBatis数据库整合实战指南-CSDN博客

本文链接：https://blog.csdn.net/qq_36708269/article/details/149417980

Spring Boot + MyBatis 数据库操作完整整合指南

本文将以spring boot 编写http接口-CSDN博客的示例代码为基础，系统化完善数据库操作部分，构建完整的Spring Boot数据访问层。以下是整合后的详细实现：

一、数据库表结构设计（MySQL）

CREATE TABLE `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `age` INT(3) DEFAULT NULL,
  `email` VARCHAR(100) DEFAULT NULL,
  `gender` ENUM('M','F','OTHER') DEFAULT 'OTHER',
  `address` VARCHAR(255) DEFAULT NULL,
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

二、Maven依赖补充（pom.xml）

<dependencies>
    <!-- 核心依赖 -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    
    <!-- 数据库相关 -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>3.0.3</version>
    </dependency>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <scope>runtime</scope>
    </dependency>
    
    <!-- Lombok简化代码 -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
</dependencies>

三、MyBatis配置强化（application.properties）

# 应用基础配置
spring.application.name=demo_test_01
server.port=8081

# 数据源配置
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/demo?characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=123456

# MyBatis高级配置
mybatis.mapper-locations=classpath:mapper/*.xml  # XML映射文件位置
mybatis.type-aliases-package=com.autumn.demo_test_01.entity  # 实体类别名
mybatis.configuration.map-underscore-to-camel-case=true  # 自动驼峰转换

四、完善数据访问层（Mapper层）

1. Mapper接口扩展（UserMapper.java）

public interface UserMapper {
    // 查询所有用户（注解方式）
    @Select("SELECT * FROM user")
    List<User> findAll();

    // 根据ID查询（XML方式）
    User findById(Integer id);

    // 插入用户
    @Insert("INSERT INTO user(name, age, email, gender, address) " +
            "VALUES(#{name}, #{age}, #{email}, #{gender}, #{address})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(User user);

    // 更新用户
    @Update("UPDATE user SET name=#{name}, age=#{age}, email=#{email}, " +
            "gender=#{gender}, address=#{address} WHERE id=#{id}")
    int update(User user);

    // 删除用户
    @Delete("DELETE FROM user WHERE id = #{id}")
    int delete(Integer id);
}

2. XML映射文件（src/main/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.autumn.demo_test_01.mapper.UserMapper">
    
    <select id="findById" resultType="User">
        SELECT * FROM user WHERE id = #{id}
    </select>
    
    <!-- 动态SQL示例 -->
    <select id="findByCondition" parameterType="map" resultType="User">
        SELECT * FROM user
        <where>
            <if test="name != null">AND name LIKE CONCAT('%', #{name}, '%')</if>
            <if test="minAge != null">AND age >= #{minAge}</if>
            <if test="maxAge != null">AND age &lt;= #{maxAge}</if>
        </where>
    </select>
</mapper>

五、服务层实现（Service Layer）

1. 服务接口（UserService.java）

public interface UserService {
    List<User> getAllUsers();
    User getUserById(Integer id);
    Integer createUser(User user);
    Integer updateUser(User user);
    Integer deleteUser(Integer id);
    List<User> searchUsers(String name, Integer minAge, Integer maxAge);
}

2. 服务实现（UserServiceImpl.java）

@Service
public class UserServiceImpl implements UserService {
    
    @Resource
    private UserMapper userMapper;

    @Override
    public List<User> getAllUsers() {
        return userMapper.findAll();
    }

    @Override
    public User getUserById(Integer id) {
        return userMapper.findById(id);
    }

    @Override
    public Integer createUser(User user) {
        userMapper.insert(user);
        return user.getId();  // 返回生成的主键
    }

    @Override
    public Integer updateUser(User user) {
        return userMapper.update(user);
    }

    @Override
    public Integer deleteUser(Integer id) {
        return userMapper.delete(id);
    }

    @Override
    public List<User> searchUsers(String name, Integer minAge, Integer maxAge) {
        Map<String, Object> params = new HashMap<>();
        params.put("name", name);
        params.put("minAge", minAge);
        params.put("maxAge", maxAge);
        return userMapper.findByCondition(params);
    }
}

六、增强控制器（Controller Layer）

@RestController
@RequestMapping("/api/users")
public class UserController {
    
    @Resource
    private UserService userService;

    // 获取所有用户
    @GetMapping
    public ResponseEntity<List<User>> getAllUsers() {
        return ResponseEntity.ok(userService.getAllUsers());
    }

    // 根据ID获取用户
    @GetMapping("/{id}")
    public ResponseEntity<User> getUserById(@PathVariable Integer id) {
        User user = userService.getUserById(id);
        return user != null ? 
            ResponseEntity.ok(user) : 
            ResponseEntity.notFound().build();
    }

    // 创建用户
    @PostMapping
    public ResponseEntity<Integer> createUser(@Valid @RequestBody User user) {
        Integer userId = userService.createUser(user);
        URI location = ServletUriComponentsBuilder
                .fromCurrentRequest()
                .path("/{id}")
                .buildAndExpand(userId)
                .toUri();
        return ResponseEntity.created(location).body(userId);
    }

    // 更新用户
    @PutMapping("/{id}")
    public ResponseEntity<Void> updateUser(
            @PathVariable Integer id, 
            @Valid @RequestBody User user) {
        user.setId(id);
        int result = userService.updateUser(user);
        return result > 0 ? 
            ResponseEntity.noContent().build() : 
            ResponseEntity.notFound().build();
    }

    // 删除用户
    @DeleteMapping("/{id}")
    public ResponseEntity<Void> deleteUser(@PathVariable Integer id) {
        int result = userService.deleteUser(id);
        return result > 0 ? 
            ResponseEntity.noContent().build() : 
            ResponseEntity.notFound().build();
    }

    // 用户搜索
    @GetMapping("/search")
    public ResponseEntity<List<User>> searchUsers(
            @RequestParam(required = false) String name,
            @RequestParam(required = false) Integer minAge,
            @RequestParam(required = false) Integer maxAge) {
        List<User> users = userService.searchUsers(name, minAge, maxAge);
        return ResponseEntity.ok(users);
    }
}

七、全局异常处理

@ControllerAdvice
public class GlobalExceptionHandler {
    
    // 处理数据不存在异常
    @ExceptionHandler(DataNotFoundException.class)
    public ResponseEntity<ErrorResponse> handleNotFound(DataNotFoundException ex) {
        ErrorResponse error = new ErrorResponse(
            "NOT_FOUND", 
            ex.getMessage(),
            LocalDateTime.now()
        );
        return ResponseEntity.status(HttpStatus.NOT_FOUND).body(error);
    }

    // 处理参数验证异常
    @ExceptionHandler(MethodArgumentNotValidException.class)
    public ResponseEntity<ErrorResponse> handleValidation(MethodArgumentNotValidException ex) {
        List<String> errors = ex.getBindingResult()
                .getFieldErrors()
                .stream()
                .map(FieldError::getDefaultMessage)
                .collect(Collectors.toList());
        
        ErrorResponse error = new ErrorResponse(
            "VALIDATION_FAILED", 
            "参数验证失败",
            errors,
            LocalDateTime.now()
        );
        return ResponseEntity.badRequest().body(error);
    }
}

八、关键优化点说明

分层架构：Controller → Service → Mapper 清晰分离职责
混合映射：注解SQL（简单操作）+ XML映射（复杂SQL）
RESTful设计：
- 合理使用HTTP状态码（200/201/204/404）
- 符合REST规范的URI设计
动态SQL：实现灵活查询条件组合
事务管理：Service层默认添加@Transactional注解
全局异常：统一处理数据访问异常和参数验证

完整项目结构：

src/main/java
├── com.autumn.demo_test_01
│   ├── controller
│   ├── entity
│   ├── exception  # 新增异常包
│   ├── mapper
│   └── service
src/main/resources
├── mapper        # XML映射文件目录
└── application.properties

通过以上整合，我们构建了包含完整CRUD操作、动态查询、分层架构和异常处理的企业级数据库访问实现，可直接用于生产环境开发。