springBoot + mybatis 执行SQL

当使用Spring Boot与MyBatis结合进行数据库操作时，通常需要配置数据源、Mapper接口以及SQL语句的执行。以下是一个详细的步骤和示例代码，涵盖了Spring Boot整合MyBatis执行SQL的基本流程。

步骤概述

1. 配置application.properties或application.yml

   • 配置数据源（如MySQL、PostgreSQL等）
   • 配置MyBatis相关属性

2. 创建实体类

   • 对应数据库表的实体类，使用注解 @Entity 或 @Data 等

3. 编写Mapper接口

   • Mapper接口定义数据操作方法，使用注解 @Mapper 或 @Repository 等

4. 编写Mapper XML文件

   • 定义SQL语句，实现具体的数据操作逻辑

5. 编写Service层

   • 调用Mapper接口方法，组织业务逻辑

6. 编写Controller层

   • 处理HTTP请求，调用Service层方法，返回结果

具体步骤和示例代码

1. 配置application.properties或application.yml

yaml
复制代码
spring.datasource.url=jdbc:mysql://localhost:3306/testdb
spring.datasource.username=root
spring.datasource.password=password

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

2. 创建实体类

java
复制代码
package com.example.demo.entity;

public class User {
    private Long id;
    private String username;
    private String email;
    
    // Getters and setters
}

3. 编写Mapper接口

java
复制代码
package com.example.demo.mapper;

import com.example.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface UserMapper {
    List<User> findAll();
    User findById(Long id);
    void insert(User user);
    void update(User user);
    void delete(Long id);
}

4. 编写Mapper XML文件 (UserMapper.xml)

放置在resources/mapper目录下，对应上述mybatis.mapper-locations配置。

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="userResultMap" type="com.example.demo.entity.User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="email" column="email"/>
    </resultMap>

    <select id="findAll" resultMap="userResultMap">
        SELECT * FROM users
    </select>

    <select id="findById" resultMap="userResultMap" parameterType="Long">
        SELECT * FROM users WHERE id = #{id}
    </select>

    <insert id="insert">
        INSERT INTO users(username, email) VALUES (#{username}, #{email})
    </insert>

    <update id="update">
        UPDATE users SET username = #{username}, email = #{email} WHERE id = #{id}
    </update>

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

5. 编写Service层 (UserService.java)

java
复制代码
package com.example.demo.service;

import com.example.demo.entity.User;
import com.example.demo.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

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

    @Autowired
    public UserService(UserMapper userMapper) {
        this.userMapper = userMapper;
    }

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

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

    public void saveUser(User user) {
        if (user.getId() == null) {
            userMapper.insert(user);
        } else {
            userMapper.update(user);
        }
    }

    public void deleteUser(Long id) {
        userMapper.delete(id);
    }
}

6. 编写Controller层 (UserController.java)

java
复制代码
package com.example.demo.controller;

import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/api/users")
public class UserController {

    private final UserService userService;

    @Autowired
    public UserController(UserService userService) {
        this.userService = userService;
    }

    @GetMapping
    public List<User> getAllUsers() {
        return userService.getAllUsers();
    }

    @GetMapping("/{id}")
    public User getUserById(@PathVariable Long id) {
        return userService.getUserById(id);
    }

    @PostMapping
    public void createUser(@RequestBody User user) {
        userService.saveUser(user);
    }

    @PutMapping("/{id}")
    public void updateUser(@PathVariable Long id, @RequestBody User user) {
        user.setId(id);
        userService.saveUser(user);
    }

    @DeleteMapping("/{id}")
    public void deleteUser(@PathVariable Long id) {
        userService.deleteUser(id);
    }
}

总结

以上示例展示了如何使用Spring Boot与MyBatis进行数据库操作。关键点包括配置数据源、编写Mapper接口和XML文件、定义实体类、编写Service和Controller层。这种结构可以帮助你清晰地分离数据访问层和业务逻辑层，使代码结构更加清晰和易于维护。