表系统管理表，有编号，系统编码，系统名称，根url，状态，删除标记，创建人，更新人，创建时间，更新时间（自动更新）

2023-04-06by@fannyfinal&@ChatGPT.Model: gpt-3.5

编号	系统编码	系统名称	根url	状态	删除标记	创建人	更新人	创建时间	更新时间
1	SYS001	系统一	http://www.sys1.com	启用	否	张三	李四	2021-01-01 10:00:00	2021-01-02 11:00:00
2	SYS002	系统二	http://www.sys2.com	停用	否	王五	赵六	2021-01-02 12:00:00	2021-01-03 13:00:00
3	SYS003	系统三	http://www.sys3.com	启用	否	张三	李四	2021-01-03 14:00:00	2021-01-04 15:00:00
4	SYS004	系统四	http://www.sys4.com	启用	是	王五	赵六	2021-01-04 16:00:00	2021-01-05 17:00:00
5	SYS005	系统五	http://www.sys5.com	停用	否	张三	李四	2021-01-05 18:00:00	2021-01-06 19:00:00

fannyfinal

创建以上表，并生成insert语句

以下是创建系统管理表的SQL语句：

CREATE TABLE system_management (
  id INT NOT NULL AUTO_INCREMENT,
  system_code VARCHAR(20) NOT NULL,
  system_name VARCHAR(50) NOT NULL,
  root_url VARCHAR(100) NOT NULL,
  status ENUM('启用','停用') NOT NULL,
  is_deleted ENUM('是','否') NOT NULL DEFAULT '否',
  created_by VARCHAR(20) NOT NULL,
  updated_by VARCHAR(20) NOT NULL,
  created_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

以下是向系统管理表中插入数据的SQL语句：

INSERT INTO system_management (system_code, system_name, root_url, status, is_deleted, created_by, updated_by)
VALUES
  ('SYS001', '系统一', 'http://www.sys1.com', '启用', '否', '张三', '李四'),
  ('SYS002', '系统二', 'http://www.sys2.com', '停用', '否', '王五', '赵六'),
  ('SYS003', '系统三', 'http://www.sys3.com', '启用', '否', '张三', '李四'),
  ('SYS004', '系统四', 'http://www.sys4.com', '启用', '是', '王五', '赵六'),
  ('SYS005', '系统五', 'http://www.sys5.com', '停用', '否', '张三', '李四');

fannyfinal

使用lombok创建entity,以及repository

首先需要在项目中引入 Lombok 依赖，然后在实体类上使用相应的注解来简化实体类的代码。

创建 Entity：

import lombok.Data;
import javax.persistence.*;
import java.util.Date;

@Data
@Entity
@Table(name = "system_management")
public class SystemManagement {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "system_code", nullable = false)
    private String systemCode;

    @Column(name = "system_name", nullable = false)
    private String systemName;

    @Column(name = "root_url", nullable = false)
    private String rootUrl;

    @Column(name = "status", nullable = false)
    private String status;

    @Column(name = "is_deleted", nullable = false)
    private String isDeleted;

    @Column(name = "created_by", nullable = false)
    private String createdBy;

    @Column(name = "updated_by", nullable = false)
    private String updatedBy;

    @Column(name = "created_time", nullable = false)
    @Temporal(TemporalType.TIMESTAMP)
    private Date createdTime;

    @Column(name = "updated_time", nullable = false)
    @Temporal(TemporalType.TIMESTAMP)
    private Date updatedTime;
}

创建 Repository：

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface SystemManagementRepository extends JpaRepository<SystemManagement, Long> {
}

这里使用了 Spring Data JPA 的 JpaRepository 接口，该接口提供了常用的 CRUD 方法。继承该接口后，我们就可以直接在 Service 层中使用该接口提供的方法进行数据库操作。

fannyfinal

创建service，含有更新新增，逻辑删除，以及分页方法（使用，name模糊查询，系统编码模糊查询，状态，删除标记）

创建 Service：

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.Date;
import java.util.List;
import java.util.Optional;

@Service
public class SystemManagementService {
    @Autowired
    private SystemManagementRepository systemManagementRepository;

    /**
     * 新增系统
     * @param systemManagement
     * @return
     */
    public SystemManagement addSystem(SystemManagement systemManagement) {
        systemManagement.setCreatedTime(new Date());
        systemManagement.setUpdatedTime(new Date());
        return systemManagementRepository.save(systemManagement);
    }

    /**
     * 更新系统
     * @param systemManagement
     * @return
     */
    @Transactional
    public SystemManagement updateSystem(SystemManagement systemManagement) {
        Optional<SystemManagement> optionalSystemManagement = systemManagementRepository.findById(systemManagement.getId());
        if (optionalSystemManagement.isPresent()) {
            SystemManagement existSystemManagement = optionalSystemManagement.get();
            existSystemManagement.setSystemCode(systemManagement.getSystemCode());
            existSystemManagement.setSystemName(systemManagement.getSystemName());
            existSystemManagement.setRootUrl(systemManagement.getRootUrl());
            existSystemManagement.setStatus(systemManagement.getStatus());
            existSystemManagement.setUpdatedBy(systemManagement.getUpdatedBy());
            existSystemManagement.setUpdatedTime(new Date());
            return systemManagementRepository.save(existSystemManagement);
        } else {
            throw new RuntimeException("SystemManagement not found.");
        }
    }

    /**
     * 逻辑删除系统
     * @param id
     */
    public void deleteSystem(Long id) {
        Optional<SystemManagement> optionalSystemManagement = systemManagementRepository.findById(id);
        if (optionalSystemManagement.isPresent()) {
            SystemManagement existSystemManagement = optionalSystemManagement.get();
            existSystemManagement.setIsDeleted("是");
            existSystemManagement.setUpdatedTime(new Date());
            systemManagementRepository.save(existSystemManagement);
        } else {
            throw new RuntimeException("SystemManagement not found.");
        }
    }

    /**
     * 分页查询系统
     * @param pageNo
     * @param pageSize
     * @param name
     * @param systemCode
     * @param status
     * @param isDeleted
     * @return
     */
    public Page<SystemManagement> getSystemByPage(int pageNo, int pageSize, String name, String systemCode, String status, String isDeleted) {
        Pageable pageable = PageRequest.of(pageNo - 1, pageSize);
        return systemManagementRepository.findAll((root, query, criteriaBuilder) -> {
            List<Predicate> predicates = Lists.newLinkedList();
            if (StringUtils.isNotBlank(name)) {
                predicates.add(criteriaBuilder.like(root.get("systemName"), "%" + name + "%"));
            }
            if (StringUtils.isNotBlank(systemCode)) {
                predicates.add(criteriaBuilder.like(root.get("systemCode"), "%" + systemCode + "%"));
            }
            if (StringUtils.isNotBlank(status)) {
                predicates.add(criteriaBuilder.equal(root.get("status"), status));
            }
            if (StringUtils.isNotBlank(isDeleted)) {
                predicates.add(criteriaBuilder.equal(root.get("isDeleted"), isDeleted));
            }
            return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
        }, pageable);
    }
}

上述 Service 中包括了新增、更新、逻辑删除和分页查询四个方法，其中分页查询方法根据参数 name、systemCode、status 和 isDeleted 进行动态查询，返回分页数据。需要注意的是，分页查询的参数 pageNo 和 pageSize 需要从前端传入，name、systemCode、status 和 isDeleted 可以为空，分别对应查询条件中的模糊查询字段和精确查询字段。

fannyfinal

创建controller

创建 Controller：

import com.google.common.collect.Maps;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.web.bind.annotation.*;
import java.util.Map;

@RestController
@RequestMapping("/api/system")
public class SystemManagementController {
    @Autowired
    private SystemManagementService systemManagementService;

    /**
     * 新增系统
     * @param systemManagement
     * @return
     */
    @PostMapping("")
    public SystemManagement addSystem(@RequestBody SystemManagement systemManagement) {
        return systemManagementService.addSystem(systemManagement);
    }

    /**
     * 更新系统
     * @param systemManagement
     * @return
     */
    @PutMapping("")
    public SystemManagement updateSystem(@RequestBody SystemManagement systemManagement) {
        return systemManagementService.updateSystem(systemManagement);
    }

    /**
     * 逻辑删除系统
     * @param id
     * @return
     */
    @DeleteMapping("/{id}")
    public Map<String, Object> deleteSystem(@PathVariable Long id) {
        systemManagementService.deleteSystem(id);
        Map<String, Object> result = Maps.newHashMap();
        result.put("success", true);
        return result;
    }

    /**
     * 分页查询系统
     * @param pageNo
     * @param pageSize
     * @param name
     * @param systemCode
     * @param status
     * @param isDeleted
     * @return
     */
    @GetMapping("")
    public Page<SystemManagement> getSystemByPage(
            @RequestParam(defaultValue = "1") int pageNo,
            @RequestParam(defaultValue = "10") int pageSize,
            @RequestParam(required = false) String name,
            @RequestParam(required = false) String systemCode,
            @RequestParam(required = false) String status,
            @RequestParam(required = false) String isDeleted) {
        return systemManagementService.getSystemByPage(pageNo, pageSize, name, systemCode, status, isDeleted);
    }
}

上述 Controller 中对应了四个方法，分别对应新增、更新、逻辑删除和分页查询系统。需要注意的是，分页查询的参数 pageNo 和 pageSize 需要从前端传入，name、systemCode、status 和 isDeleted 可以为空，分别对应查询条件中的模糊查询字段和精确查询字段。其中逻辑删除方法返回一个 Map 类型的结果，表示删除成功或失败的状态。

fannyfinal

新增service分页方法，在原有的基础上增加创建时间范围。并且同时新增api

在原有 Service 的基础上新增分页查询系统方法，增加创建时间范围作为查询条件：

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.Date;
import java.util.List;
import java.util.Optional;

@Service
public class SystemManagementService {
    @Autowired
    private SystemManagementRepository systemManagementRepository;

    /**
     * 新增系统
     * @param systemManagement
     * @return
     */
    public SystemManagement addSystem(SystemManagement systemManagement) {
        systemManagement.setCreatedTime(new Date());
        systemManagement.setUpdatedTime(new Date());
        return systemManagementRepository.save(systemManagement);
    }

    /**
     * 更新系统
     * @param systemManagement
     * @return
     */
    @Transactional
    public SystemManagement updateSystem(SystemManagement systemManagement) {
        Optional<SystemManagement> optionalSystemManagement = systemManagementRepository.findById(systemManagement.getId());
        if (optionalSystemManagement.isPresent()) {
            SystemManagement existSystemManagement = optionalSystemManagement.get();
            existSystemManagement.setSystemCode(systemManagement.getSystemCode());
            existSystemManagement.setSystemName(systemManagement.getSystemName());
            existSystemManagement.setRootUrl(systemManagement.getRootUrl());
            existSystemManagement.setStatus(systemManagement.getStatus());
            existSystemManagement.setUpdatedBy(systemManagement.getUpdatedBy());
            existSystemManagement.setUpdatedTime(new Date());
            return systemManagementRepository.save(existSystemManagement);
        } else {
            throw new RuntimeException("SystemManagement not found.");
        }
    }

    /**
     * 逻辑删除系统
     * @param id
     */
    public void deleteSystem(Long id) {
        Optional<SystemManagement> optionalSystemManagement = systemManagementRepository.findById(id);
        if (optionalSystemManagement.isPresent()) {
            SystemManagement existSystemManagement = optionalSystemManagement.get();
            existSystemManagement.setIsDeleted("是");
            existSystemManagement.setUpdatedTime(new Date());
            systemManagementRepository.save(existSystemManagement);
        } else {
            throw new RuntimeException("SystemManagement not found.");
        }
    }

    /**
     * 分页查询系统
     * @param pageNo
     * @param pageSize
     * @param name
     * @param systemCode
     * @param status
     * @param isDeleted
     * @return
     */
    public Page<SystemManagement> getSystemByPage(int pageNo, int pageSize, String name, String systemCode, String status, String isDeleted) {
        Pageable pageable = PageRequest.of(pageNo - 1, pageSize);
        return systemManagementRepository.findAll((root, query, criteriaBuilder) -> {
            List<Predicate> predicates = Lists.newLinkedList();
            if (StringUtils.isNotBlank(name)) {
                predicates.add(criteriaBuilder.like(root.get("systemName"), "%" + name + "%"));
            }
            if (StringUtils.isNotBlank(systemCode)) {
                predicates.add(criteriaBuilder.like(root.get("systemCode"), "%" + systemCode + "%"));
            }
            if (StringUtils.isNotBlank(status)) {
                predicates.add(criteriaBuilder.equal(root.get("status"), status));
            }
            if (StringUtils.isNotBlank(isDeleted)) {
                predicates.add(criteriaBuilder.equal(root.get("isDeleted"), isDeleted));
            }
            return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
        }, pageable);
    }

    /**
     * 分页查询系统，增加创建时间范围作为查询条件
     * @param pageNo
     * @param pageSize
     * @param name
     * @param systemCode
     * @param status
     * @param isDeleted
     * @param startTime
     * @param endTime
     * @return
     */
    public Page<SystemManagement> getSystemByPage(int pageNo, int pageSize, String name, String systemCode, String status, String isDeleted, Date startTime, Date endTime) {
        Pageable pageable = PageRequest.of(pageNo - 1, pageSize);
        return systemManagementRepository.findAll((root, query, criteriaBuilder) -> {
            List<Predicate> predicates = Lists.newLinkedList();
            if (StringUtils.isNotBlank(name)) {
                predicates.add(criteriaBuilder.like(root.get("systemName"), "%" + name + "%"));
            }
            if (StringUtils.isNotBlank(systemCode)) {
                predicates.add(criteriaBuilder.like(root.get("systemCode"), "%" + systemCode + "%"));
            }
            if (StringUtils.isNotBlank(status)) {
                predicates.add(criteriaBuilder.equal(root.get("status"), status));
            }
            if (StringUtils.isNotBlank(isDeleted)) {
                predicates.add(criteriaBuilder.equal(root.get("isDeleted"), isDeleted));
            }
            if (startTime != null) {
                predicates.add

fannyfinal

继续

An error occurred with your deployment

EDGE_FUNCTION_INVOCATION_TIMEOUT

fannyfinal

继续

An error occurred with your deployment

EDGE_FUNCTION_INVOCATION_TIMEOUT

fannyfinal

新增service分页方法，在原有的基础上增加创建时间范围，只需输出这一方法。并且controller中也只输出这个api

OpenPrompt

表 系统管理表，有编号，系统编码，系统名称，根url，状态，删除标记，创建人，更新人，创建时间，更新时间（自动更新）

表系统管理表，有编号，系统编码，系统名称，根url，状态，删除标记，创建人，更新人，创建时间，更新时间（自动更新）