JP3-3-MyClub后台后端（一）

Java道经 - 项目 - MyClub - 后台后端（一）


传送门：JP3-1-MyClub项目简介
传送门：JP3-2-MyClub公共服务
传送门：JP3-3-MyClub后台后端（一）
传送门：JP3-3-MyClub后台后端（二）

心法：manage 是管理员后台核心项目，负责提供管理员的全部功能。

武技：创建 mc-manage 子项目

1. 添加三方依赖：包括 common，AOP，MySQL，SpringCache，Redis, Swagger，HibernateValidator，JWT，EasyExcel，MinIO，MyBatis，SpringBootAdmin，PageHelper 和 SpringRetry 等：

<dependencies>
    <!--引入自己的common项目-->
    <dependency>
        <groupId>com.joezhou</groupId>
        <artifactId>mc-common</artifactId>
        <version>1.0-SNAPSHOT</version>
    </dependency>
    <!--spring-boot-starter-aop-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-aop</artifactId>
    </dependency>
    <!--mysql-connector-j-->
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>${mysql-connector-j.version}</version>
        <scope>runtime</scope>
    </dependency>
    <!--spring-boot-starter-cache-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-cache</artifactId>
    </dependency>
	<!--spring-boot-starter-data-redis-->
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-data-redis</artifactId>
	</dependency>
    <!--knife4j-openapi3-jakarta-spring-boot-starter-->
    <dependency>
        <groupId>com.github.xiaoymin</groupId>
        <artifactId>knife4j-openapi3-jakarta-spring-boot-starter</artifactId>
        <version>${knife4j-openapi3-jakarta-spring-boot-starter.version}</version>
    </dependency>
    <!--hibernate-validator-->
    <dependency>
        <groupId>org.hibernate.validator</groupId>
        <artifactId>hibernate-validator</artifactId>
        <version>${hibernate-validator.version}</version>
    </dependency>
    <!--jjwt-->
    <dependency>
        <groupId>io.jsonwebtoken</groupId>
        <artifactId>jjwt</artifactId>
        <version>${jjwt.version}</version>
    </dependency>
    <!--jaxb-api：jdk8以上使用jjwt时需要-->
    <dependency>
        <groupId>javax.xml.bind</groupId>
        <artifactId>jaxb-api</artifactId>
        <version>${jaxb-api.version}</version>
    </dependency>
    <!--easyexcel-->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>${easyexcel.version}</version>
    </dependency>
    <!--minio-->
    <dependency>
        <groupId>io.minio</groupId>
        <artifactId>minio</artifactId>
        <version>${minio.version}</version>
        <!--冲突项排除-->
        <exclusions>
            <exclusion>
                <artifactId>jsr305</artifactId>
                <groupId>com.google.code.findbugs</groupId>
            </exclusion>
            <exclusion>
                <artifactId>okio</artifactId>
                <groupId>com.squareup.okio</groupId>
            </exclusion>
        </exclusions>
    </dependency>
    <!--mybatis-spring-boot-starter-->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>${mybatis-spring-boot-starter.version}</version>
    </dependency>
    <!--spring-boot-admin-starter-client-->
    <dependency>
        <groupId>de.codecentric</groupId>
        <artifactId>spring-boot-admin-starter-client</artifactId>
        <version>${spring-boot-admin-starter-client.version}</version>
    </dependency>
    <!--pagehelper-spring-boot-starter-->
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper-spring-boot-starter</artifactId>
        <version>${pagehelper-spring-boot-starter.version}</version>
        <!--冲突项排除-->
        <exclusions>
            <exclusion>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <groupId>org.mybatis.spring.boot</groupId>
            </exclusion>
        </exclusions>
    </dependency>
    <!--okhttp-->
    <dependency>
        <groupId>com.squareup.okhttp3</groupId>
        <artifactId>okhttp</artifactId>
        <version>${okhttp.version}</version>
    </dependency>
	<!--spring-retry-->
	<dependency>
		<groupId>org.springframework.retry</groupId>
		<artifactId>spring-retry</artifactId>
	</dependency>
</dependencies>

2. 开发主配文件：

server:
  port: 23101 # 端口号

spring:
  application:
    name: mc-manage # 项目名称
  datasource:
    url: jdbc:mysql://192.168.40.77:3306/myclub?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8
    username: root
    password: root
  boot:
    admin:
      client:
        instance:
          service-base-url: http://192.168.40.77:23101 # 客户端地址
        url: http://192.168.40.77:23102 # 服务端地址
        username: admin # SpringBootAdmin账号
        password: admin # SpringBootAdmin密码
  servlet:
    multipart:
      max-file-size: -1 # 文件上传最大限制，-1表示无限制
      max-request-size: 1GB # 每个文件大小限制
  cache:
    cache-names: room,school,assets,dept,emp,role,menu,assets_borrow,direction,club,club_progress,course,student # SpringCache缓存名称列表
  data:
    redis:
      host: 192.168.40.77 # redis服务地址
      port: 6379 # redis服务端口号

management:
  endpoints:
    web:
      exposure:
        include: "*" # 暴露所有端点
  endpoint:
    health:
      show-details: always # 展示 health 端点的详细信息

mybatis:
  configuration:
    map-underscore-to-camel-case: true # 下划线转驼峰
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # 控制台SQL
  type-aliases-package: com.joezhou.entity # 实体类别名包扫描

pagehelper:
  helper-dialect: mysql # 数据库方言
  reasonable: true # page小于0或大于总页数的时候视为查询首页或尾页
  page-size-zero: true #size为0时视为全查

springdoc:
  api-docs:
    enabled: true # 启用SpringDoc
  group-configs:
    - group: v1 # v1分组
      paths-to-match: /api/v1/** # 分组规则
      packages-to-scan: com.joezhou.controller # 控制器包扫描

knife4j:
  enable: true # 启用knife4j
  setting:
    language: zh_cn # 中文

3. 开发启动类：

package com.joezhou;

/** @author 周航宇 */
@EnableRetry
@EnableScheduling
@MapperScan("com.joezhou.mapper")
@SpringBootApplication
public class ManageApp {
    public static void main(String[] args) {
        SpringApplication.run(ManageApp.class, args);
    }
}

4. 开发 SpringDoc 配置类：

package com.joezhou.config;

/** @author 周航宇 */
@Configuration
public class SpringDocConfig {

    private static final String AUTHOR = "JoeZhou";
    private static final String URL = "http://localhost:23101/index.html";
    private static final String TITLE = "my-club";
    private static final String INFO = "MyClub 管理系统是基于 SpringBoot 开发的，旨在提供一个全面而高效的管理平台，该系统目前仅支持内部员工登录，登陆后可以轻松管理俱乐部的房间，学校，资产，部门，员工，角色，菜单，班级，课程，学员等相关数据。该系统使用前后端分离的模式进行开发，数据库使用 MySQL，后端使用经典的 SSM 架构，前端使用 Vue + ElementPlus 的组合。该系统具有良好的可扩展性和稳定性，为俱乐部的管理和运营提供了可靠的支持。";
    private static final String VERSION = "1.0.0";

    /** 通用信息Bean */
    @Bean
    public OpenAPI commonInfo() {
        return new OpenAPI().info(new Info()
                .title(TITLE)
                .description(INFO)
                .version(VERSION)
                .contact(new Contact().name(AUTHOR).url(URL)));
    }
}

5. 访问 SpringDoc 文档页面 http://localhost:23101/doc.html

S01. RMS资源模块

E01. 开发房间接口

1. 基础增删改查

1. 开发 DTO 实体类：

负责（添加）业务的实体类：

package com.joezhou.dto;

/** @author 周航宇 */
@Schema(description = "房间添加DTO")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class RoomInsertDTO implements Serializable {

    @Schema(description = "房间名称")
    @NotEmpty(message = "房间名称不能为空")
    @Pattern(regexp = MC.Regex.TITLE_RE, message = MC.Regex.TITLE_RE_MSG)
    private String title;

    @Schema(description = "房间地址")
    @NotEmpty(message = "房间地址不能为空")
    @Pattern(regexp = MC.Regex.ADDRESS_RE, message = MC.Regex.ADDRESS_RE_MSG)
    private String address;
    
    @Schema(description = "房间描述")
    @Pattern(regexp = MC.Regex.INFO_RE, message = MC.Regex.INFO_RE_MSG)
    private String info;

    @Schema(description = "房间容量")
    @Min(value = 0, message = "房间容量必须大于0")
    @NotNull(message = "房间容量不能为空")
    private Integer capacity;
}

负责（修改）业务的实体类：

package com.joezhou.dto;

/** @author 周航宇 */
@Schema(description = "房间修改DTO")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class RoomUpdateDTO implements Serializable {

    @Schema(description = "主键")
    @NotNull(message = "主键不能为空")
    private Long id;
    
    @Schema(description = "房间名称")
    @Pattern(regexp = MC.Regex.TITLE_RE, message = MC.Regex.TITLE_RE_MSG)
    private String title;
    
    @Schema(description = "房间地址")
    @Pattern(regexp = MC.Regex.ADDRESS_RE, message = MC.Regex.ADDRESS_RE_MSG)
    private String address;
    
    @Schema(description = "房间描述")
    @Pattern(regexp = MC.Regex.INFO_RE, message = MC.Regex.INFO_RE_MSG)
    private String info;
    
    @Min(value = 0, message = "房间容量必须大于0")
    @Schema(description = "房间容量")
    private Integer capacity;
}

负责（分页）业务的实体类：

package com.joezhou.dto;

/** @author 周航宇 */
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
@Schema(description = "按条件分页搜索房间DTO")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class RoomPageDTO extends PageDTO {

    @Schema(description = "房间名称")
    @Pattern(regexp = MC.Regex.TITLE_RE, message = MC.Regex.TITLE_RE_MSG)
    private String title;
}

负责（全查）业务的实体类：

package com.joezhou.vo;

/** @author 周航宇 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class RoomVO implements Serializable {
    /** 主键 */
    private Long id;
    /** 房间名称 */
    private String title;
}

2. 开发数据层代码：

package com.joezhou.mapper;

/** @author 周航宇 */
@Repository
public interface RoomMapper {

    @Insert("""
            insert into rms_room (title, address, info, capacity, version, deleted, created, updated)
            values (#{title}, #{address}, #{info}, #{capacity}, #{version}, #{deleted}, #{created}, #{updated})
            """)
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Room room);

    @Select("""
            select * from rms_room t
            where t.id = #{param1} and t.deleted = 0
            """)
    Room select(Long id);

    @Select("""
            <script>
            select * from rms_room t
            <where>
                <if test='title != null'> title like concat('%', #{title}, '%') and </if>
                t.deleted = 0
            </where>
            </script>
            """)
    List<Room> list(RoomPageDTO dto);

    @Update("""
            <script>
            update rms_room
            <set>
                <if test='title != null'> title = #{title}, </if>
                <if test='address != null'> address = #{address}, </if>
                <if test='info != null'> info = #{info}, </if>
                <if test='capacity != null'> capacity = #{capacity}, </if>
                <if test='deleted != null'> deleted = #{deleted}, </if>
                <if test='created != null'> created = #{created}, </if>
                <if test='updated != null'> updated = #{updated}, </if>
                version = version + 1
            </set>
            where id = #{id} and deleted = 0 and version = #{version}
            </script>
            """)
    int update(Room room);

    @Update("""
            update rms_room set deleted = 1, updated = current_timestamp
            where id = #{param1}
            """)
    int delete(Long id);

    @Update("""
            <script>
            update rms_room set deleted = 1, updated = current_timestamp
            where id in
            <foreach collection='list' item='e' open='(' close=')' separator=','>
                ${e}
            </foreach>
            </script>
            """)
    int deleteBatch(List<Long> ids);
}

3. 开发业务层代码：

package com.joezhou.service;

/** @author 周航宇 */
@Repository  
public interface RoomService {
    int insert(RoomInsertDTO dto);
    Room select(Long id);
    List<RoomVO> list();
    PageInfo<Room> page(RoomPageDTO dto);
    int update(RoomUpdateDTO dto);
    int delete(Long id);
    int deleteBatch(List<Long> ids);
}

package com.joezhou.service.impl;

/** @author 周航宇 */
@Service
@CacheConfig(cacheNames = "room")
public class RoomServiceImpl implements RoomService {

    @Resource
    private RoomMapper roomMapper;

    @CacheEvict(allEntries = true)
    @Override
    public int insert(RoomInsertDTO dto) {
        String info = dto.getInfo();
        // 拷贝属性
        Room room = BeanUtil.copyProperties(dto, Room.class);
        // 设置默认值
        room.setInfo(ObjectUtil.isNull(info) ? "暂无描述" : info);
        room.setVersion(0L);
        room.setDeleted(0);
        room.setCreated(LocalDateTime.now());
        room.setUpdated(LocalDateTime.now());
        // DB添加
        int result = roomMapper.insert(room);
        if (result <= 0) {
            throw new ServerErrorException("DB添加失败");
        }
        return result;
    }

    @Cacheable(key = "#p0", condition = "#p0 != null", unless = "#result == null")
    @Override
    public Room select(Long id) {
        Room result = roomMapper.select(id);
        if (ObjectUtil.isNull(result)) {
            throw new ServerErrorException("记录不存在");
        }
        return result;
    }

    @Cacheable(key = "#root.methodName", unless = "#result == null")
    @Override
    public List<RoomVO> list() {
        return roomMapper.list(new RoomPageDTO())
                .stream()
                .map(room -> BeanUtil.copyProperties(room, RoomVO.class))
                .collect(Collectors.toList());
    }

    @Cacheable(key = "#root.methodName + ':' + #p0.toString()",
            condition = "#p0 != null",
            unless = "#result == null")
    @Override
    public PageInfo<Room> page(RoomPageDTO dto) {
        PageHelper.startPage(dto.getPageNum(), dto.getPageSize());
        return new PageInfo<>(roomMapper.list(dto));
    }

    @CacheEvict(allEntries = true)
    @Transactional
    @Retryable(retryFor = VersionException.class)
    @Override
    public int update(RoomUpdateDTO dto) {
        Room room = roomMapper.select(dto.getId());
        if(ObjectUtil.isNull(room)){
            throw new ServerErrorException("记录不存在");
        }
        BeanUtil.copyProperties(dto, room);
        // 设置默认值
        room.setUpdated(LocalDateTime.now());
        // DB修改
        int result = roomMapper.update(room);
        if (result <= 0) {
            throw new VersionException("DB修改失败");
        }
        return result;
    }

    @CacheEvict(allEntries = true)
    @Override
    public int delete(Long id) {
        int result = roomMapper.delete(id);
        if (result <= 0) {
            throw new ServerErrorException("DB删除失败");
        }
        return result;
    }

    @CacheEvict(allEntries = true)
    @Override
    public int deleteBatch(List<Long> ids) {
        int result = roomMapper.deleteBatch(ids);
        if (result <= 0) {
            throw new ServerErrorException("DB批删失败");
        }
        return result;
    }
}

4. 开发控制层代码：

package com.joezhou.controller;

/** @author 周航宇 */  
@Tag(name = "房间模块")  
@RestController  
@RequestMapping("/api/v1/room")  
public class RoomController {  
  
	@Resource
    private RoomService roomService;

    @Operation(summary = "新增 - 单条新增")
    @PostMapping("insert")
    public Result<Integer> insert(@RequestBody @Validated RoomInsertDTO dto) {
        return new Result<>(roomService.insert(dto));
    }

    @Operation(summary = "查询 - 单条查询")
    @GetMapping("select/{id}")
    public Result<Room> select(@PathVariable("id") Long id) {
        return new Result<>(roomService.select(id));
    }

    @Operation(summary = "查询 - 全部记录")
    @GetMapping("list")
    public Result<List<RoomVO>> list() {
        return new Result<>(roomService.list());
    }

    @Operation(summary = "查询 - 分页查询")
    @GetMapping("page")
    public Result<PageInfo<Room>> page(@Validated RoomPageDTO dto) {
        return new Result<>(roomService.page(dto));
    }

    @Operation(summary = "修改 - 单条修改")
    @PutMapping("update")
    public Result<Integer> update(@RequestBody @Validated RoomUpdateDTO dto) {
        return new Result<>(roomService.update(dto));
    }

    @Operation(summary = "删除 - 单条删除")
    @DeleteMapping("delete/{id}")
    public Result<Integer> delete(@PathVariable("id") Long id) {
        return new Result<>(roomService.delete(id));
    }

    @Operation(summary = "删除 - 批量删除")
    @DeleteMapping("deleteBatch")
    public Result<Integer> deleteBatch(@RequestParam("ids") List<Long> ids) {
        return new Result<>(roomService.deleteBatch(ids));
    }
}

2. 下载数据报表

1. 开发 DTO 实体类：

package com.joezhou.excel;

/** @author 周航宇 */
@ColumnWidth(20)
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, verticalAlignment = VerticalAlignmentEnum.CENTER)  
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, verticalAlignment = VerticalAlignmentEnum.CENTER)
@Data
@AllArgsConstructor
@NoArgsConstructor
public class RoomExcel implements Serializable {
    @ExcelProperty(value = {"房间数据统计表", "房间标题"})
    private String title;
    @ExcelProperty(value = {"房间数据统计表", "房间容量（人）"})
    private Integer capacity;
    @ColumnWidth(40)
    @ExcelProperty(value = {"房间数据统计表", "房间地址"})
    private String address;
    @ColumnWidth(40)
    @ExcelProperty(value = {"房间数据统计表", "房间描述"})
    private String info;
    @ExcelProperty(value = {"房间数据统计表", "首次创建日期"})
    @DateTimeFormat("yyyy/MM/dd HH:mm:ss")
    private LocalDateTime created;
    @ExcelProperty(value = {"房间数据统计表", "最后创建日期"})
    @DateTimeFormat("yyyy/MM/dd HH:mm:ss")
    private LocalDateTime updated;
}

2. 开发业务层代码：

package com.joezhou.service;

/**
 * 获取房间记录的Excel数据
 *
 * @return 房间记录的Excel数据列表
 */
List<RoomExcel> getExcelData();

package com.joezhou.service.impl;

@Override
public List<RoomExcel> getExcelData() {
	// 获取所有房间数据并转换为Excel格式对象列表（使用Stream简化集合操作）
	return roomMapper.list(new RoomPageDTO())
			.stream()
			.map(room -> BeanUtil.copyProperties(room, RoomExcel.class))
			.collect(Collectors.toList());
}

3. 开发控制层代码：

package com.joezhou.controller;

@Operation(summary = "查询 - 报表打印")
@SneakyThrows
@GetMapping("/excel")
public void excel(HttpServletResponse resp) {
	EasyExcelUtil.download(resp, "房间统计表", roomService.getExcelData());
}

E02. 开发学校接口

1. 基础增删改查

1. 开发 DTO 实体类：

负责（添加）业务的实体类：

package com.joezhou.dto;

/** @author 周航宇 */
@Schema(description = "学校添加DTO")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class SchoolInsertDTO implements Serializable {

	@Schema(description = "学校名称")
    @NotEmpty(message = "学校名称不能为空")
    @Pattern(regexp = MC.Regex.TITLE_RE, message = MC.Regex.TITLE_RE_MSG)
    private String title;

    @Schema(description = "学院名称")
    @NotEmpty(message = "学院名称不能为空")
    @Pattern(regexp = MC.Regex.TITLE_RE, message = MC.Regex.TITLE_RE_MSG)
    private String college;

    @Schema(description = "专业名称")
    @NotEmpty(message = "专业名称不能为空")
    @Pattern(regexp = MC.Regex.TITLE_RE, message = MC.Regex.TITLE_RE_MSG)
    private String major;

    @Schema(description = "班级名称")
    @NotEmpty(message = "班级名称不能为空")
    @Pattern(regexp = MC.Regex.TITLE_RE, message = MC.Regex.TITLE_RE_MSG)
    private String clazz;
    
    @Schema(description = "学校地址")
    @Pattern(regexp = MC.Regex.ADDRESS_RE, message = MC.Regex.ADDRESS_RE_MSG)
    private String address;

    @Schema(description = "学校描述")
    @Pattern(regexp = MC.Regex.INFO_RE, message = MC.Regex.INFO_RE_MSG)
    private String info;
}

负责（修改）业务的实体类：

package com.joezhou.dto;

/** @author 周航宇 */
@Schema(description = "学校修改DTO")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class SchoolUpdateDTO implements Serializable {

    @Schema(description = "主键")
    @NotNull(message = "主键不能为空")
    private Long id;
    
    @Schema(description = "学校名称")
    @Pattern(regexp = MC.Regex.TITLE_RE, message = MC.Regex.TITLE_RE_MSG)
    private String title;
    
    @Schema(description = "学院名称")
    @Pattern(regexp = MC.Regex.TITLE_RE, message = MC.Regex.TITLE_RE_MSG)
    private String college;
    
    @Schema(description = "专业名称")
    @Pattern(regexp = MC.Regex.TITLE_RE, message = MC.Regex.TITLE_RE_MSG)
    private String major;
    
    @Schema(description = "班级名称")
    @Pattern(regexp = MC.Regex.TITLE_RE, message = MC.Regex.TITLE_RE_MSG)
    private String clazz;
    
    @Schema(description = "学校地址")
    @Pattern(regexp = MC.Regex.ADDRESS_RE, message = MC.Regex.ADDRESS_RE_MSG)
    private String address;
    
    @Schema(description = "学校描述")
    @Pattern(regexp = MC.Regex.INFO_RE, message = MC.Regex.INFO_RE_MSG)
    private String info;
}

负责（分页）业务的实体类：

package com.joezhou.dto;

/** @author 周航宇 */
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
@Schema(description = "按条件分页搜索学校DTO")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class SchoolPageDTO extends PageDTO {

    @Schema(description = "学校名称")
    @Pattern(regexp = MC.Regex.TITLE_RE, message = MC.Regex.TITLE_RE_MSG)
    private String title;
}

负责（全查）业务的实体类：

package com.joezhou.vo;

/** @author 周航宇 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class SchoolVO implements Serializable {
    /** 主键 */
    private Long id;
    /** 学校名称 */
    private String title;
}

2. 开发数据层代码：

package com.joezhou.mapper;

/** @author 周航宇 */
@Repository
public interface SchoolMapper {

    @Insert("""
            insert into rms_school (title, college, major, clazz, address, info, version, deleted, created, updated)
            values (#{title}, #{college}, #{major}, #{clazz}, #{address}, #{info}, #{version}, #{deleted}, #{created}, #{updated})
            """)
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(School school);

    @Select("""
            select * from rms_school t
            where t.id = #{param1} and t.deleted = 0
            """)
    School select(Long id);

    @Select("""
            <script>
            select * from rms_school t
            <where>
                <if test='title != null'> title like concat('%', #{title}, '%') and </if>
                t.deleted = 0
            </where>
            </script>
            """)
	List<School> list(SchoolPageDTO dto);

    @Update("""
            <script>
            update rms_school
            <set>
            <if test='title != null'> title = #{title}, </if>
            <if test='college != null'> college = #{college}, </if>
            <if test='major != null'> major = #{major}, </if>
            <if test='clazz != null'> clazz = #{clazz}, </if>
            <if test='address != null'> address = #{address}, </if>
            <if test='info != null'> info = #{info}, </if>
            <if test='deleted != null'> deleted = #{deleted}, </if>
            <if test='created != null'> created = #{created}, </if>
            <if test='updated != null'> updated = #{updated}, </if>
            version = version + 1
            </set>
            where id = #{id} and deleted = 0 and version = #{version}
            </script>
            """)
    int update(School school);

    @Update("""
            update rms_school set deleted = 1, updated = current_timestamp
            where id = #{param1}
            """)
    int delete(Long id);

    @Update("""
            <script>
            update rms_school set deleted = 1, updated = current_timestamp
            where id in
            <foreach collection='list' item='e' open='(' close=')' separator=','>
                ${e}
            </foreach>
            </script>
            """)
    int deleteBatch(List<Long> ids);

    @Select("""
            select * from rms_school t
            where t.deleted = 0 and t.title like concat('%', #{param1}, '%')
            """)
    List<School> listLikeTitle(String title);
}

3. 开发业务层代码：

package com.joezhou.service;

/** @author 周航宇 */
public interface SchoolService {
    int insert(SchoolInsertDTO dto);
    School select(Long id);
    List<SchoolVO> list();
    PageInfo<School> page(SchoolPageDTO dto);
    int update(SchoolUpdateDTO dto);
    int delete(Long id);
    int deleteBatch(List<Long> ids);
}

package com.joezhou.service.impl;

/** @author 周航宇 */
@Service
@CacheConfig(cacheNames = "school")
public class SchoolServiceImpl implements SchoolService {

    @Resource
    private SchoolMapper schoolMapper;

    @CacheEvict(allEntries = true)
    @Override
    public int insert(SchoolInsertDTO dto) {
        String address = dto.getAddress();
        String info = dto.getInfo();
        // 拷贝属性
        School school = BeanUtil.copyProperties(dto, School.class);
        // 设置默认值
        school.setAddress(StrUtil.isBlank(address) ? "暂无地址" : address);
        school.setInfo(StrUtil.isBlank(info) ? "暂无描述" : info);
        school.setVersion(0L);
        school.setDeleted(0);
        school.setCreated(LocalDateTime.now());
        school.setUpdated(LocalDateTime.now());
        // DB添加
        int result = schoolMapper.insert(school);
        if (result <= 0) {
            throw new ServerErrorException("DB添加失败");
        }
        return result;
    }

    @Cacheable(key = "#p0", condition = "#p0 != null", unless = "#result == null")
    @Override
    public School select(Long id) {
        School result = schoolMapper.select(id);
        if (ObjectUtil.isNull(result)) {
            throw new ServerErrorException("记录不存在");
        }
        return result;
    }

    @Cacheable(key = "#root.methodName", unless = "#result == null")
    @Override
    public List<SchoolVO> list() {
        return schoolMapper.list(new SchoolPageDTO())
                .stream()
                .map(school -> BeanUtil.copyProperties(school, SchoolVO.class))
                .collect(Collectors.toList());
    }

    @Cacheable(key = "#root.methodName + ':' + #p0.toString()",
            condition = "#p0 != null",
            unless = "#result == null")
    @Override
    public PageInfo<School> page(SchoolPageDTO dto) {
        PageHelper.startPage(dto.getPageNum(), dto.getPageSize());
        return new PageInfo<>(schoolMapper.list(dto));
    }

    @CacheEvict(allEntries = true)
    @Transactional
    @Retryable(retryFor = VersionException.class)
    @Override
    public int update(SchoolUpdateDTO dto) {
        School school = schoolMapper.select(dto.getId());
        if(ObjectUtil.isNull(school)){
            throw new ServerErrorException("记录不存在");
        }
        BeanUtil.copyProperties(dto, school);
        // 设置默认值
        school.setUpdated(LocalDateTime.now());
        // DB修改
        int result = schoolMapper.update(school);
        if (result <= 0) {
            throw new VersionException("DB修改失败");
        }
        return result;
    }

    @CacheEvict(allEntries = true)
    @Override
    public int delete(Long id) {
        int result = schoolMapper.delete(id);
        if (result <= 0) {
            throw new ServerErrorException("DB删除失败");
        }
        return result;
    }

    @CacheEvict(allEntries = true)
    @Override
    public int deleteBatch(List<Long> ids) {
        int result = schoolMapper.deleteBatch(ids);
        if (result <= 0) {
            throw new ServerErrorException("DB批删失败");
        }
        return result;
    }
}

4. 开发控制层代码：

package com.joezhou.controller;

/** @author 周航宇 */
@Tag(name = "学校模块")
@RestController
@RequestMapping("/api/v1/school")
public class SchoolController {

    @Resource
    private SchoolService schoolService;

    @Operation(summary = "新增 - 单条新增")
    @PostMapping("insert")
    public Result<Integer> insert(@RequestBody @Validated SchoolInsertDTO dto) {
        return new Result<>(schoolService.insert(dto));
    }

    @Operation(summary = "查询 - 单条查询")
    @GetMapping("select/{id}")
    public Result<School> select(@PathVariable("id") Long id) {
        return new Result<>(schoolService.select(id));
    }

    @Operation(summary = "查询 - 全部记录")
    @GetMapping("list")
    public Result<List<SchoolVO>> list() {
        return new Result<>(schoolService.list());
    }

    @Operation(summary = "查询 - 分页查询")
    @GetMapping("page")
    public Result<PageInfo<School>> page(@Validated SchoolPageDTO dto) {
        return new Result<>(schoolService.page(dto));
    }

    @Operation(summary = "修改 - 单条修改")
    @PutMapping("update")
    public Result<Integer> update(@RequestBody @Validated SchoolUpdateDTO dto) {
        return new Result<>(schoolService.update(dto));
    }

    @Operation(summary = "删除 - 单条删除")
    @DeleteMapping("delete/{id}")
    public Result<Integer> delete(@PathVariable("id") Long id) {
        return new Result<>(schoolService.delete(id));
    }

    @Operation(summary = "删除 - 批量删除")
    @DeleteMapping("deleteBatch")
    public Result<Integer> deleteBatch(@RequestParam("ids") List<Long> ids) {
        return new Result<>(schoolService.deleteBatch(ids));
    }
}

2. 下载数据报表

1. 开发 DTO 实体类：

package com.joezhou.excel;

/** @author 周航宇 */
@ColumnWidth(20)
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, verticalAlignment = VerticalAlignmentEnum.CENTER)  
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, verticalAlignment = VerticalAlignmentEnum.CENTER)
@Data
@AllArgsConstructor
@NoArgsConstructor
public class SchoolExcel implements Serializable {
    @ExcelProperty(value = {"学校数据统计表", "学校标题"})
    private String title;
    @ExcelProperty(value = {"学校数据统计表", "学院名称"})
    private String college;
    @ExcelProperty(value = {"学校数据统计表", "专业名称"})
    private String major;
    @ExcelProperty(value = {"学校数据统计表", "班级名称"})
    private String clazz;
    @ColumnWidth(40)
    @ExcelProperty(value = {"学校数据统计表", "学校地址"})
    private String address;
    @ColumnWidth(40)
    @ExcelProperty(value = {"学校数据统计表", "学校描述"})
    private String info;
    @ExcelProperty(value = {"学校数据统计表", "首次创建日期"})
    @DateTimeFormat("yyyy/MM/dd HH:mm:ss")
    private LocalDateTime created;
    @ExcelProperty(value = {"学校数据统计表", "最后创建日期"})
    @DateTimeFormat("yyyy/MM/dd HH:mm:ss")
    private LocalDateTime updated;
}

2. 开发业务层代码：

package com.joezhou.service;

/**
 * 获取学校记录的Excel数据
 *
 * @return 学校记录的Excel数据列表
 */
List<SchoolExcel> getExcelData();

package com.joezhou.service.impl;

@Override
public List<SchoolExcel> getExcelData() {
	return schoolMapper.list(new SchoolPageDTO())
			.stream()
			.map(school -> BeanUtil.copyProperties(school, SchoolExcel.class))
			.collect(Collectors.toList());
}

3. 开发控制层代码：

package com.joezhou.controller;

@Operation(summary = "查询 - 报表打印")
@SneakyThrows
@GetMapping("/excel")
public void excel(HttpServletResponse resp) {
	EasyExcelUtil.download(resp, "学校统计表", schoolService.getExcelData());
}

E03. 开发资产接口

1. 基础增删改查

1. 开发 DTO 实体类：

负责（添加）业务的实体类：

package com.joezhou.dto;

/** @author 周航宇 */
@Schema(description = "资产添加DTO")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class AssetsInsertDTO implements Serializable {

	@Schema(description = "资产名称")
    @NotEmpty(message = "资产名称不能为空")
    @Pattern(regexp = MC.Regex.TITLE_RE, message = MC.Regex.TITLE_RE_MSG)
    private String title;

    @Schema(description = "资产单价")
    @NotNull(message = "资产单价不能为空")
    @DecimalMin(value = "0.01", message = "资产单价不能小于0.01元")
    private Double price;

    @Schema(description = "单价单位")
    @NotEmpty(message = "单价单位不能为空")
    private String priceUnit;

    @Schema(description = "总计库存")
    @NotNull(message = "总计库存不能为空")
    @Min(value = 0, message = "总计库存不能小于0")
    private Integer total;

    @Schema(description = "库存单位")
    @NotEmpty(message = "库存单位不能为空")
    private String stockUnit;
    
    @Schema(description = "资产描述")
    @Pattern(regexp = MC.Regex.INFO_RE, message = MC.Regex.INFO_RE_MSG)
    private String info;
}

负责（修改）业务的实体类：

package com.joezhou.dto;

/** @author 周航宇 */
@Schema(description = "资产修改DTO")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class AssetsUpdateDTO implements Serializable {

    @Schema(description = "资产主键")
    @NotNull(message = "资产主键不能为空")
    private Long id;
    
    @Schema(description = "资产名称")
    @Pattern(regexp = MC.Regex.TITLE_RE, message = MC.Regex.TITLE_RE_MSG)
    private String title;
    
    @Schema(description = "资产单价")
    @DecimalMin(value = "0.01", message = "资产单价不能小于0.01元")
    private Double price;
    
    @Schema(description = "单价单位")
    private String priceUnit;
    
    @Schema(description = "剩余库存")
    @Min(value = 0, message = "剩余库存不能小于0")
    private Integer stock;
    
    @Schema(description = "总计库存")
    @Min(value = 0, message = "总计库存不能小于0")
    private Integer total;
    
    @Schema(description = "库存单位")
    private String stockUnit;
    
    @Schema(description = "资产描述")
    @Pattern(regexp = MC.Regex.INFO_RE, message = MC.Regex.INFO_RE_MSG)
    private String info;
}

负责（分页）业务的实体类：

package com.joezhou.dto;

/** @author 周航宇 */
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
@Schema(description = "按条件分页搜索资产DTO")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class AssetsPageDTO extends PageDTO {

    @Schema(description = "资产名称")
    @Pattern(regexp = MC.Regex.TITLE_RE, message = MC.Regex.TITLE_RE_MSG)
    private String title;
}

负责（全查）业务的实体类：

package com.joezhou.vo;

/** @author 周航宇 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class AssetsVO implements Serializable {
    /** 主键 */
    private Long id;
    /** 资产名称 */
    private String title;
}

2. 开发数据层代码：

package com.joezhou.mapper;

/** @author 周航宇 */
@Repository
public interface AssetsMapper {

    @Insert("""
            insert into rms_assets (title, picture, price, price_unit, stock, stock_unit, total, info, version, deleted, created, updated)
            values (#{title}, #{picture}, #{price}, #{priceUnit}, #{stock}, #{stockUnit}, #{total}, #{info}, #{version}, #{deleted}, #{created}, #{updated})
            """)
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Assets assets);

    @Select("""
            select * from rms_assets t
            where t.id = #{param1} and t.deleted = 0
            """)
    Assets select(Long id);

    @Select("""
            <script>
            select * from rms_assets t
            <where>
                <if test='title != null'> title like concat('%', #{title}, '%') and </if>
                t.deleted = 0
            </where>
            </script>
            """)
    List<Assets> list(AssetsPageDTO dto);

    @Update("""
            <script>
            update rms_assets
            <set>
            <if test='title != null'> title = #{title}, </if>
            <if test='picture != null'> picture = #{picture}, </if>
            <if test='price != null'> price = #{price}, </if>
            <if test='priceUnit != null'> price_unit = #{priceUnit}, </if>
            <if test='stock != null'> stock = #{stock}, </if>
            <if test='stockUnit != null'> stock_unit = #{stockUnit}, </if>
            <if test='total != null'> total = #{total}, </if>
            <if test='info != null'> info = #{info}, </if>
            <if test='deleted != null'> deleted = #{deleted}, </if>
            <if test='created != null'> created = #{created}, </if>
            <if test='updated != null'> updated = #{updated}, </if>
            version = version + 1
            </set>
            where id = #{id} and deleted = 0 and version = #{version}
            </script>
            """)
    int update(Assets assets);

    @Update("""
            update rms_assets set deleted = 1, updated = current_timestamp
            where id = #{param1}
            """)
    int delete(Long id);

    @Update("""
            <script>
            update rms_assets set deleted = 1, updated = current_timestamp
            where id in
            <foreach collection='list' item='e' open='(' close=')' separator=','>
                ${e}
            </foreach>
            </script>
            """)
    int deleteBatch(List<Long> ids);
}

3. 开发业务层代码：

package com.joezhou.service;

/** @author 周航宇 */
public interface AssetsService {
    int insert(AssetsInsertDTO dto);
    Assets select(Long id);
    List<AssetsVO> list();
    PageInfo<Assets> page(AssetsPageDTO dto);
    int update(AssetsUpdateDTO dto);
    int delete(Long id);
    int deleteBatch(List<Long> ids);
}

package com.joezhou.service.impl;

/** @author 周航宇 */
@Service
@CacheConfig(cacheNames = "assets")
public class AssetsServiceImpl implements AssetsService {

    @Resource
    private AssetsMapper assetsMapper;

    @CacheEvict(allEntries = true)
    @Override
    public int insert(AssetsInsertDTO dto) {
        String info = dto.getInfo();
        // 拷贝属性
        Assets assets = BeanUtil.copyProperties(dto, Assets.class);
        // 设置默认值
        assets.setPicture(MC.Assets.DEFAULT_ASSETS);
        assets.setStock(assets.getTotal());
        assets.setInfo(StrUtil.isBlank(info) ? "暂无描述" : info);
        assets.setVersion(0L);
        assets.setDeleted(0);
        assets.setCreated(LocalDateTime.now());
        assets.setUpdated(LocalDateTime.now());
        // DB添加
        int result = assetsMapper.insert(assets);
        if (result <= 0) {
            throw new ServerErrorException("DB添加失败");
        }
        return result;
    }

    @Cacheable(key = "#p0", condition = "#p0 != null", unless = "#result == null")
    @Override
    public Assets select(Long id) {
        Assets result = assetsMapper.select(id);
        if (ObjectUtil.isNull(result)) {
            throw new ServerErrorException("记录不存在");
        }
        return result;
    }

    @Cacheable(key = "#root.methodName", unless = "#result == null")
    @Override
    public List<AssetsVO> list() {
        return assetsMapper.list(new AssetsPageDTO())
                .stream()
                .map(assets -> BeanUtil.copyProperties(assets, AssetsVO.class))
                .collect(Collectors.toList());
    }

    @Cacheable(key = "#root.methodName + ':' + #p0.toString()",
            condition = "#p0 != null",
            unless = "#result == null")
    @Override
    public PageInfo<Assets> page(AssetsPageDTO dto) {
        PageHelper.startPage(dto.getPageNum(), dto.getPageSize());
        return new PageInfo<>(assetsMapper.list(dto));
    }

    @CacheEvict(allEntries = true)
    @Transactional
    @Retryable(retryFor = VersionException.class)
    @Override
    public int update(AssetsUpdateDTO dto) {
        Assets assets = assetsMapper.select(dto.getId());
        if (ObjectUtil.isNull(assets)) {
            throw new ServerErrorException("记录不存在");
        }
        BeanUtil.copyProperties(dto, assets);
        // 设置默认值
        assets.setUpdated(LocalDateTime.now());
        // DB修改
        int result = assetsMapper.update(assets);
        if (result <= 0) {
            throw new VersionException("DB修改失败");
        }
        return result;
    }

    @CacheEvict(allEntries = true)
    @Override
    public int delete(Long id) {
        int result = assetsMapper.delete(id);
        if (result <= 0) {
            throw new ServerErrorException("DB逻辑删除失败");
        }
        return result;
    }

    @CacheEvict(allEntries = true)
    @Override
    public int deleteBatch(List<Long> ids) {
        int result = assetsMapper.deleteBatch(ids);
        if (result <= 0) {
            throw new ServerErrorException("DB逻辑批删失败");
        }
        return result;
    }
}

4. 开发控制层代码：

package com.joezhou.controller;

/** @author 周航宇 */
@Tag(name = "资产模块")
@RestController
@RequestMapping("/api/v1/assets")
public class AssetsController {

    @Resource
    private AssetsService assetsService;

    @Operation(summary = "新增 - 单条新增")
    @PostMapping("insert")
    public Result<Integer> insert(@RequestBody @Validated AssetsInsertDTO dto) {
        return new Result<>(assetsService.insert(dto));
    }

    @Operation(summary = "查询 - 单条查询")
    @GetMapping("select/{id}")
    public Result<Assets> select(@PathVariable("id") Long id) {
        return new Result<>(assetsService.select(id));
    }

    @Operation(summary = "查询 - 全部记录")
    @GetMapping("list")
    public Result<List<AssetsVO>> list() {
        return new Result<>(assetsService.list());
    }

    @Operation(summary = "查询 - 分页查询")
    @GetMapping("page")
    public Result<PageInfo<Assets>> page(@Validated AssetsPageDTO dto) {
        return new Result<>(assetsService.page(dto));
    }

    @Operation(summary = "修改 - 单条修改")
    @PutMapping("update")
    public Result<Integer> update(@RequestBody @Validated AssetsUpdateDTO dto) {
        return new Result<>(assetsService.update(dto));
    }

    @Operation(summary = "删除 - 单条删除")
    @DeleteMapping("delete/{id}")
    public Result<Integer> delete(@PathVariable("id") Long id) {
        return new Result<>(assetsService.delete(id));
    }

    @Operation(summary = "删除 - 批量删除")
    @DeleteMapping("deleteBatch")
    public Result<Integer> deleteBatch(@RequestParam("ids") List<Long> ids) {
        return new Result<>(assetsService.deleteBatch(ids));
    }
}

2. 下载数据报表

1. 开发 DTO 实体类：

package com.joezhou.excel;

/** @author 周航宇 */
@ColumnWidth(20)
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, verticalAlignment = VerticalAlignmentEnum.CENTER)  
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, verticalAlignment = VerticalAlignmentEnum.CENTER)
@Data
@AllArgsConstructor
@NoArgsConstructor
public class AssetsExcel implements Serializable {
    @ExcelProperty(value = {"资产数据统计表", "资产标题"})
    private String title;
    @ExcelProperty(value = {"资产数据统计表", "资产图片"})
    private String picture;
    @ExcelProperty(value = {"资产数据统计表", "资产单价"})
    private Double price;
    @ExcelProperty(value = {"资产数据统计表", "单价单位"})
    private String priceUnit;
    @ExcelProperty(value = {"资产数据统计表", "剩余库存"})
    private Integer stock;
    @ExcelProperty(value = {"资产数据统计表", "总计库存"})
    private Integer total;
    @ExcelProperty(value = {"资产数据统计表", "库存单位"})
    private String stockUnit;
    @ColumnWidth(40)
    @ExcelProperty(value = {"资产数据统计表", "资产描述"})
    private String info;
    @ExcelProperty(value = {"资产数据统计表", "首次创建日期"})
    @DateTimeFormat("yyyy/MM/dd HH:mm:ss")
    private LocalDateTime created;
    @ExcelProperty(value = {"资产数据统计表", "最后创建日期"})
    @DateTimeFormat("yyyy/MM/dd HH:mm:ss")
    private LocalDateTime updated;
}

2. 开发业务层代码：

package com.joezhou.service;

/**
 * 获取资产记录的Excel数据
 *
 * @return 资产记录的Excel数据列表
 */
List<AssetsExcel> getExcelData();

package com.joezhou.service.impl;

@Override
public List<AssetsExcel> getExcelData() {
	return assetsMapper.list(new AssetsPageDTO())
			.stream()
			.map(assets -> BeanUtil.copyProperties(assets, AssetsExcel.class))
			.collect(Collectors.toList());
}

3. 开发控制层代码：

package com.joezhou.controller;

@Operation(summary = "查询 - 报表打印")
@SneakyThrows
@GetMapping("/excel")
public void excel(HttpServletResponse resp) {
	EasyExcelUtil.download(resp, "资产统计表", assetsService.getExcelData());
}

3. 上传资产图片

1. 开发业务层代码：

package com.joezhou.service;

/**
 * 上传资产图片
 *
 * @param newFile 上传资产图片DTO
 * @param id 资产主键
 * @return 文件名
 */
String uploadPicture(MultipartFile newFile, Long id);

package com.joezhou.service.impl;

@Transactional(rollbackFor = RuntimeException.class)
@CacheEvict(allEntries = true)
@Override
public String uploadPicture(MultipartFile newFile, Long id) {
    // 按主键查询记录
    Assets assets = assetsMapper.select(id);
    if (ObjectUtil.isNull(assets)) {
        throw new ServerErrorException("记录不存在");
    }
    // 备份旧文件
    String oldFile = assets.getPicture();
    // 生成新文件名
    String newFileName = MinioUtil.randomFilename(newFile);
    // DB更新文件名
    assets.setPicture(newFileName);
    if (assetsMapper.update(assets) <= 0) {
        throw new ServerErrorException("DB更新失败");
    }

    try {
        // MinIO删除旧文件（默认文件不删除）
        if (!MC.Assets.DEFAULT_ASSETS.equals(oldFile)) {
            MinioUtil.delete(oldFile, MC.MinIO.ASSETS_DIR, MC.MinIO.BUCKET_NAME);
        }
        // MinIO上传新文件
        MinioUtil.upload(newFile, newFileName, MC.MinIO.ASSETS_DIR, MC.MinIO.BUCKET_NAME);
    } catch (Exception e) {
        throw new ServerErrorException("MinIO操作失败：" + e.getMessage());
    }
    // 返回新文件名
    return newFileName;
}

2. 开发控制层代码：注意上传文件不是 JSON 参数，而是二进制参数，不能使用 @RequestBody 注解：

package com.joezhou.controller;

@Operation(summary = "上传 - 资产图片")
@PostMapping("/uploadPicture/{id}")
public Result<String> uploadPicture(@RequestParam("pictureFile") MultipartFile pictureFile,
									@PathVariable("id") Long id) {
	return new Result<>(assetsService.uploadPicture(pictureFile, id));
}

E04. 开发资产申请接口

心法：资产申请记录需要关联资产记录和员工记录，所以需要事先对实体类进行改造。

改造如下：

package com.joezhou.entity;

/** @author 周航宇 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class AssetsBorrow implements Serializable {
    
    ...

    /** 每条资产申请记录对应 1 条资产记录 */
    private Assets assets;
    /** 每条资产申请记录对应 1 条员工记录 */
    private Emp emp;
}

1. 基础增删改查

1. 开发 DTO 实体类：

负责（添加）业务的实体类：

package com.joezhou.dto;

/** @author 周航宇 */
@Schema(description = "资产申请添加DTO")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class AssetsBorrowInsertDTO implements Serializable {

    @Schema(description = "资产ID")
    @NotNull(message = "资产ID不能为空")
    private Long fkAssetsId;

    @Schema(description = "员工ID")
    @NotNull(message = "员工ID不能为空")
    private Long fkEmpId;

    @Schema(description = "申请数量")
    @NotNull(message = "申请数量不能为空")
    @Min(value = 0, message = "申请数量不能小于0")
    private Integer count;

    @Schema(description = "申请时间")
    @NotNull(message = "申请时间不能为空")
    private LocalDateTime borrowTime;

    @Schema(description = "预计归还时间")
    @NotNull(message = "预计归还时间不能为空")
    private LocalDateTime expectedReturnTime;

    @Schema(description = "资产申请描述")
    @Pattern(regexp = MC.Regex.INFO_RE, message = MC.Regex.INFO_RE_MSG)
    private String info;
}

负责（修改）业务的实体类：

package com.joezhou.dto;

/** @author 周航宇 */
@Schema(description = "资产申请修改DTO")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class AssetsBorrowUpdateDTO implements Serializable {

    @Schema(description = "资产申请ID")
    @NotNull(message = "资产申请ID不能为空")
    private Long id;

    @Schema(description = "资产ID")
    private Long fkAssetsId;

    @Schema(description = "员工ID")
    private Long fkEmpId;

    @Schema(description = "申请数量")
    @Min(value = 0, message = "申请数量不能小于0")
    private Integer count;

    @Schema(description = "申请时间")
    private LocalDateTime borrowTime;

    @Schema(description = "预计归还时间")
    private LocalDateTime expectedReturnTime;

    @Schema(description = "实际归还时间")
    private LocalDateTime returnTime;

    @Schema(description = "资产申请描述")
    @Pattern(regexp = MC.Regex.INFO_RE, message = MC.Regex.INFO_RE_MSG)
    private String info;
}

负责（分页）业务的实体类：

package com.joezhou.dto;

/** @author 周航宇 */
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
@Schema(description = "按条件分页搜索资产申请DTO")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class AssetsBorrowPageDTO extends PageDTO {

    @Schema(description = "资产ID")
    private Long fkAssetsId;

    @Schema(description = "员工ID")
    private Long fkEmpId;
}

2. 开发数据层代码：

package com.joezhou.mapper;

/** @author 周航宇 */
@Repository
public interface AssetsBorrowMapper {

    @Insert("""
            insert into rms_assets_borrow (fk_assets_id, fk_emp_id, count, borrow_time, expected_return_time, return_time, info, version, deleted, created, updated)
            values (#{fkAssetsId}, #{fkEmpId}, #{count}, #{borrowTime}, #{expectedReturnTime}, #{returnTime}, #{info}, #{version}, #{deleted}, #{created}, #{updated})
            """)
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(AssetsBorrow assetsBorrow);

    @Results(id = "assetsBorrowResultMap", value = {
            @Result(property = "id", column = "id", id = true),
            @Result(property = "fkAssetsId", column = "fk_assets_id"),
            @Result(property = "fkEmpId", column = "fk_emp_id"),
            @Result(property = "assets", column = "fk_assets_id", one = @One(select = "com.joezhou.mapper.AssetsMapper.select")),
            @Result(property = "emp", column = "fk_emp_id", one = @One(select = "com.joezhou.mapper.EmpMapper.select"))
    })
    @Select("""
            select * from rms_assets_borrow t
            where t.id = #{param1} and t.deleted = 0
            """)
    AssetsBorrow select(Long id);

    @ResultMap("assetsBorrowResultMap")
    @Select("""
            <script>
            select * from rms_assets_borrow t
            <where>
                <if test='fkAssetsId != null'> fk_assets_id = #{fkAssetsId} and </if>
                <if test='fkEmpId != null'> fk_emp_id = #{fkEmpId} and </if>
                t.deleted = 0
            </where>
            </script>
            """)
    List<AssetsBorrow> list(AssetsBorrowPageDTO dto);

    @Update("""
            <script>
            update rms_assets_borrow
            <set>
            <if test='fkAssetsId != null'> fk_assets_id = #{fkAssetsId}, </if>
            <if test='fkEmpId != null'> fk_emp_id = #{fkEmpId}, </if>
            <if test='count != null'> count = #{count}, </if>
            <if test='borrowTime != null'> borrow_time = #{borrowTime}, </if>
            <if test='expectedReturnTime != null'> expected_return_time = #{expectedReturnTime}, </if>
            <if test='returnTime != null'> return_time = #{returnTime}, </if>
            <if test='info != null'> info = #{info}, </if>
            <if test='deleted != null'> deleted = #{deleted}, </if>
            <if test='created != null'> created = #{created}, </if>
            <if test='updated != null'> updated = #{updated}, </if>
            version = version + 1
            </set>
            where id = #{id} and deleted = 0 and version = #{version}
            </script>
            """)
    int update(AssetsBorrow assetsBorrow);

    @Update("""
            update rms_assets_borrow set deleted = 1, updated = current_timestamp
            where id = #{param1}
            """)
    int delete(Long id);

    @Update("""
            <script>
            update rms_assets_borrow set deleted = 1, updated = current_timestamp
            where id in
            <foreach collection='list' item='e' open='(' close=')' separator=','>
                ${e}
            </foreach>
            </script>
            """)
    int deleteBatch(List<Long> ids);
}

在员工数据层 EmpMapper 中补充如下查询块：

@Select("""
		select * from ums_emp t
		where t.id = #{param1} and t.deleted = 0
		""")
Emp select(Long id);

3. 开发业务层代码：

package com.joezhou.service;

/** @author 周航宇 */
public interface AssetsBorrowService {
    int insert(AssetsBorrowInsertDTO dto);
    AssetsBorrow select(Long id);
    List<AssetsBorrow> list();
    PageInfo<AssetsBorrow> page(AssetsBorrowPageDTO dto);
    int update(AssetsBorrowUpdateDTO dto);
    int delete(Long id);
    int deleteBatch(List<Long> ids);
}

package com.joezhou.service.impl;

/** @author 周航宇 */
@Service
@CacheConfig(cacheNames = "assetsBorrow")
public class AssetsBorrowServiceImpl implements AssetsBorrowService {

    @Resource
    private AssetsBorrowMapper assetsBorrowMapper;

    @CacheEvict(allEntries = true)
    @Override
    public int insert(AssetsBorrowInsertDTO dto) {
        String info = dto.getInfo();
        // 拷贝属性
        AssetsBorrow assetsBorrow = BeanUtil.copyProperties(dto, AssetsBorrow.class);
        // 设置默认值
        assetsBorrow.setInfo(StrUtil.isBlank(info) ? "暂无描述" : info);
        assetsBorrow.setVersion(0L);
        assetsBorrow.setDeleted(0);
        assetsBorrow.setCreated(LocalDateTime.now());
        assetsBorrow.setUpdated(LocalDateTime.now());
        // DB添加
        int result = assetsBorrowMapper.insert(assetsBorrow);
        if (result <= 0) {
            throw new ServerErrorException("DB添加失败");
        }
        return result;
    }

    @Cacheable(key = "#p0", condition = "#p0 != null", unless = "#result == null")
    @Override
    public AssetsBorrow select(Long id) {
        AssetsBorrow result = assetsBorrowMapper.select(id);
        if (ObjectUtil.isNull(result)) {
            throw new ServerErrorException("记录不存在");
        }
        return result;
    }

    @Cacheable(key = "#root.methodName", unless = "#result == null")
    @Override
    public List<AssetsBorrow> list() {
        return assetsBorrowMapper.list(new AssetsBorrowPageDTO());
    }

    @Cacheable(key = "#root.methodName + ':' + #p0.toString()",
            condition = "#p0 != null",
            unless = "#result == null")
    @Override
    public PageInfo<AssetsBorrow> page(AssetsBorrowPageDTO dto) {
        PageHelper.startPage(dto.getPageNum(), dto.getPageSize());
        return new PageInfo<>(assetsBorrowMapper.list(dto));
    }

    @CacheEvict(allEntries = true)
    @Transactional
    @Retryable(retryFor = VersionException.class)
    @Override
    public int update(AssetsBorrowUpdateDTO dto) {
        AssetsBorrow assetsBorrow = assetsBorrowMapper.select(dto.getId());
        if (ObjectUtil.isNull(assetsBorrow)) {
            throw new ServerErrorException("记录不存在");
        }
        BeanUtil.copyProperties(dto, assetsBorrow);
        // 设置默认值
        assetsBorrow.setUpdated(LocalDateTime.now());
        // DB修改
        int result = assetsBorrowMapper.update(assetsBorrow);
        if (result <= 0) {
            throw new VersionException("DB修改失败");
        }
        return result;
    }

    @CacheEvict(allEntries = true)
    @Override
    public int delete(Long id) {
        int result = assetsBorrowMapper.delete(id);
        if (result <= 0) {
            throw new ServerErrorException("DB逻辑删除失败");
        }
        return result;
    }

    @CacheEvict(allEntries = true)
    @Override
    public int deleteBatch(List<Long> ids) {
        int result = assetsBorrowMapper.deleteBatch(ids);
        if (result <= 0) {
            throw new ServerErrorException("DB逻辑批删失败");
        }
        return result;
    }
}

4. 开发控制层代码：

package com.joezhou.controller;

/** @author 周航宇 */
@Tag(name = "资产申请模块")
@RestController
@RequestMapping("/api/v1/assetsBorrow")
public class AssetsBorrowController {

    @Resource
    private AssetsBorrowService assetsBorrowService;

    @Operation(summary = "新增 - 单条新增")
    @PostMapping("insert")
    public Result<Integer> insert(@RequestBody @Validated AssetsBorrowInsertDTO dto) {
        return new Result<>(assetsBorrowService.insert(dto));
    }

    @Operation(summary = "查询 - 单条查询")
    @GetMapping("select/{id}")
    public Result<AssetsBorrow> select(@PathVariable("id") Long id) {
        return new Result<>(assetsBorrowService.select(id));
    }

    @Operation(summary = "查询 - 全部记录")
    @GetMapping("list")
    public Result<List<AssetsBorrow>> list() {
        return new Result<>(assetsBorrowService.list());
    }

    @Operation(summary = "查询 - 分页查询")
    @GetMapping("page")
    public Result<PageInfo<AssetsBorrow>> page(@Validated AssetsBorrowPageDTO dto) {
        return new Result<>(assetsBorrowService.page(dto));
    }

    @Operation(summary = "修改 - 单条修改")
    @PutMapping("update")
    public Result<Integer> update(@RequestBody @Validated AssetsBorrowUpdateDTO dto) {
        return new Result<>(assetsBorrowService.update(dto));
    }

    @Operation(summary = "删除 - 单条删除")
    @DeleteMapping("delete/{id}")
    public Result<Integer> delete(@PathVariable("id") Long id) {
        return new Result<>(assetsBorrowService.delete(id));
    }

    @Operation(summary = "删除 - 批量删除")
    @DeleteMapping("deleteBatch")
    public Result<Integer> deleteBatch(@RequestParam("ids") List<Long> ids) {
        return new Result<>(assetsBorrowService.deleteBatch(ids));
    }
}

2. 下载数据报表

1. 开发 DTO 实体类：

package com.joezhou.excel;

/** @author 周航宇 */
@ColumnWidth(20)
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, verticalAlignment = VerticalAlignmentEnum.CENTER)  
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, verticalAlignment = VerticalAlignmentEnum.CENTER)
@Data
@AllArgsConstructor
@NoArgsConstructor
public class AssetsBorrowExcel implements Serializable {
	@ExcelProperty(value = {"资产申请数据统计表", "资产标题"})
    private String assetsTitle;
    @ExcelProperty(value = {"资产申请数据统计表", "员工姓名"})
    private String empName;
    @ExcelProperty(value = {"资产申请数据统计表", "申请数量"})
    private Integer count;
    @ExcelProperty(value = {"资产申请数据统计表", "申请时间"})
    @DateTimeFormat("yyyy/MM/dd HH:mm:ss")
    private LocalDateTime borrowTime;
    @ExcelProperty(value = {"资产申请数据统计表", "预计归还时间"})
    @DateTimeFormat("yyyy/MM/dd HH:mm:ss")
    private LocalDateTime expectedReturnTime;
    @ExcelProperty(value = {"资产申请数据统计表", "实际归还时间"})
    @DateTimeFormat("yyyy/MM/dd HH:mm:ss")
    private LocalDateTime returnTime;
    @ExcelProperty(value = {"资产申请数据统计表", "是否已归还"})
    private String isReturn;
    @ColumnWidth(40)
    @ExcelProperty(value = {"资产申请数据统计表", "资产申请描述"})
    private String info;
    @ExcelProperty(value = {"资产申请数据统计表", "首次创建日期"})
    @DateTimeFormat("yyyy/MM/dd HH:mm:ss")
    private LocalDateTime created;
    @ExcelProperty(value = {"资产申请数据统计表", "最后创建日期"})
    @DateTimeFormat("yyyy/MM/dd HH:mm:ss")
    private LocalDateTime updated;
}

2. 开发业务层代码：

package com.joezhou.service;

/**
 * 获取资产申请记录的Excel数据
 *
 * @return 资产申请记录的Excel数据列表
 */
List<AssetsBorrowExcel> getExcelData();

package com.joezhou.service.impl;

@Override
public List<AssetsBorrowExcel> getExcelData() {
	return assetsBorrowMapper.list(new AssetsBorrowPageDTO())
			.stream()
			.map(assetsBorrow -> {
				AssetsBorrowExcel assetsBorrowExcel = new AssetsBorrowExcel();
				BeanUtil.copyProperties(assetsBorrow, assetsBorrowExcel);
				if (ObjectUtil.isNotNull(assetsBorrow.getAssets())) {
					assetsBorrowExcel.setAssetsTitle(assetsBorrow.getAssets().getTitle());
				}
				if (ObjectUtil.isNotNull(assetsBorrow.getEmp())) {
					assetsBorrowExcel.setEmpName(assetsBorrow.getEmp().getRealname());
				}
				assetsBorrowExcel.setIsReturn(ObjectUtil.isNotNull(assetsBorrow.getReturnTime()) ? "已归还" : "未归还");
				return assetsBorrowExcel;
			})
			.collect(Collectors.toList());
}

3. 开发控制层代码：

package com.joezhou.controller;

@Operation(summary = "查询 - 报表打印")
@SneakyThrows
@GetMapping("/excel")
public void excel(HttpServletResponse resp) {
	EasyExcelUtil.download(resp, "资产申请统计表", assetsBorrowService.getExcelData());
}

Java道经 - 项目 - MyClub - 后台后端（一）


传送门：JP3-1-MyClub项目简介
传送门：JP3-2-MyClub公共服务
传送门：JP3-3-MyClub后台后端（一）
传送门：JP3-3-MyClub后台后端（二）