当前位置：首页 > news >正文

springbootCRUD

news 2026/3/27 0:33:10

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: 8082spring:datasource:url: jdbc:mysql://localhost:3306/database_nameusername: rootpassword: passworddriver-class-name: com.mysql.cj.jdbc.Drivermybatis:configuration:map-underscore-to-camel-case: true