04-springbootCRUD

SpringBoot CRUD

一、项目依赖配置

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.5</version>
    </dependency>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper-spring-boot-starter</artifactId>
        <version>1.4.6</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
</dependencies>

二、实体类设计

Employee实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employee {
    private Long id;
    private String name;
    private Double sal;
    private Boolean sex;
}

查询对象EmployeeQO

@Data
@AllArgsConstructor
@NoArgsConstructor
public class EmployeeQO {
    private String name;
    private BigDecimal salStart;
    private BigDecimal salEnd;
}

三、Mapper接口CRUD操作

1. 查询所有记录

@Select("select * from employee")
@Results({
    @Result(property = "id", column = "id", id = true),
    @Result(property = "name", column = "name"),
    @Result(property = "sal", column = "sal"),
    @Result(property = "sex", column = "sex")
})
List<Employee> selectAll();

要点：

• @Select：指定查询SQL
• @Results：映射结果集，将数据库字段映射到实体类属性
• @Result：定义字段映射关系，id = true表示主键

2. 新增记录

@Insert(value = "insert into employee(name,sal,sex) values(#{name},#{sal},#{sex})")
@Options(useGeneratedKeys = true, keyProperty = "id")
int save(Employee employee);

要点：

• @Insert：指定插入SQL，使用#{属性名}占位符
• @Options：配置选项
  • useGeneratedKeys = true：使用数据库自增主键
  • keyProperty = "id"：将生成的主键值设置到对象的id属性中

3. 更新记录（全量更新）

@Update(value = "update employee set name=#{name},sal=#{sal},sex=#{sex} where id=#{id}")
int update(Employee employee);

4. 动态更新（部分字段更新）

@UpdateProvider(type = EmployeeMapper.SQLProvider.class, method = "update")
int patch(Employee employee);

SQLProvider实现：

class SQLProvider {
    public String update(Employee employee) {
        StringBuffer sql = new StringBuffer("update employee set");
        
        if (!StringUtils.isEmpty(employee.getName())) {
            sql.append(" name=#{name},");
        }
        if (!StringUtils.isEmpty(employee.getSal())) {
            sql.append(" sal=#{sal},");
        }
        if (!StringUtils.isEmpty(employee.getSex())) {
            sql.append(" sex=#{sex},");
        }
        
        String substring = sql.substring(0, sql.length() - 1);
        substring += " where id=#{id}";
        return substring;
    }
}

要点：

• @UpdateProvider：使用Provider类动态生成SQL
• type：指定Provider类
• method：指定Provider类中的方法
• 动态拼接SQL，只更新非空字段

5. 根据ID查询

@Select("select * from employee where id=#{id}")
@Results({
    @Result(property = "id", column = "id", id = true),
    @Result(property = "name", column = "name"),
    @Result(property = "sal", column = "sal"),
    @Result(property = "sex", column = "sex")
})
Employee findById(Long id);

6. 根据ID删除

@Delete("delete from employee where id=#{id}")
int deleteById(Long id);

7. 批量删除

@DeleteProvider(type = EmployeeMapper.SQLProvider.class, method = "deleteByIds")
int deleteByIds(Long[] ids);

SQLProvider实现：

public String deleteByIds(Long[] ids) {
    StringBuffer sql = new StringBuffer("delete from employee where id in(");
    for (Long id : ids) {
        sql.append(id).append(",");
    }
    String substring = sql.substring(0, sql.length() - 1);
    substring += ")";
    return substring;
}

8. 条件查询

@SelectProvider(type = EmployeeMapper.SQLProvider.class, method = "queryByCondition")
@Results({
    @Result(property = "id", column = "id", id = true),
    @Result(property = "name", column = "name"),
    @Result(property = "sal", column = "sal"),
    @Result(property = "sex", column = "sex")
})
List<Employee> queryByCondition(EmployeeQO qo);

SQLProvider实现：

public String queryByCondition(EmployeeQO qo) {
    StringBuffer sql = new StringBuffer("select * from employee where 1=1");
    
    if (!StringUtils.isEmpty(qo.getName())) {
        sql.append(" and name like concat('%',#{name},'%')");
    }
    if (qo.getSalStart() != null) {
        sql.append(" and sal>=#{salStart}");
    }
    if (qo.getSalEnd() != null) {
        sql.append(" and sal<=#{salEnd}");
    }
    
    return sql.toString();
}

要点：

• where 1=1：便于动态拼接AND条件
• concat('%',#{name},'%')：实现模糊查询
• 使用StringUtils.isEmpty()判断字符串是否为空
• 使用!= null判断数值类型是否为空

四、MyBatis注解总结

注解	用途	说明
@Select	查询操作	执行SELECT语句
@Insert	插入操作	执行INSERT语句
@Update	更新操作	执行UPDATE语句
@Delete	删除操作	执行DELETE语句
@Results	结果映射	定义结果集映射规则
@Result	字段映射	定义单个字段的映射关系
@Options	选项配置	配置如主键回填等选项
@SelectProvider	动态查询	使用Provider类动态生成查询SQL
@UpdateProvider	动态更新	使用Provider类动态生成更新SQL
@DeleteProvider	动态删除	使用Provider类动态生成删除SQL

五、关键技巧

1. 动态SQL拼接

使用@XxxProvider注解配合内部类实现动态SQL：

class SQLProvider {
    public String methodName(Parameter param) {
        StringBuffer sql = new StringBuffer("SQL基础语句");
        if (条件) {
            sql.append(" 动态SQL片段");
        }
        return sql.toString();
    }
}

2. 字段非空判断

StringUtils.isEmpty(value)  // 判断字符串是否为空
value != null              // 判断对象是否为空

3. 模糊查询

sql.append(" and name like concat('%',#{name},'%')");

4. 批量操作

StringBuffer sql = new StringBuffer("delete from table where id in(");
for (Long id : ids) {
    sql.append(id).append(",");
}
sql.substring(0, sql.length() - 1) + ")";

六、配置文件

application.yml

server:
  port: 8082

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/database_name
    username: root
    password: password
    driver-class-name: com.mysql.cj.jdbc.Driver

mybatis:
  configuration:
    map-underscore-to-camel-case: true

七、注意事项

1. Mapper接口注解：在Mapper接口上添加@Mapper注解，可以自动生成实现类并放入IOC容器
2. 主键回填：使用@Options(useGeneratedKeys = true, keyProperty = "id")实现主键回填
3. 结果映射：当数据库字段名与实体类属性名不一致时，使用@Results进行映射
4. 动态SQL：复杂的动态SQL建议使用Provider类，提高代码可读性和可维护性
5. 参数传递：使用#{属性名}占位符，MyBatis会自动进行参数绑定