java - Spring-boot with MySQL - RESTful API Web服务: SQL Error: 1054, SQLState : 42S22, 'field list'中的未知列

Question

我正在使用 Spring Boot 和 MySQL 制作 RESTful API Web 服务。在顶层，我有一个实体sites。第二层是实体地板。每个站点可能有多个楼层。这是Floors类:

@Entity
public class Floors {

    @Id
    private String id;
    private String floor;

    @ManyToOne
    private Sites site;

    public Sites getSite() {
        return site;
    }

    public void setSite(Sites site) {
        this.site = site;
    }

    public Floors(){}

    public Floors(String id, String floor, String siteId) {
        super();
        this.id = id;
        this.floor = floor;
        this.site = new Sites(siteId, "", "");
    }

//getters and setters

这是FloorsController:

@RestController
public class FloorsController {

    @Autowired
    private FloorsService floorsService;

    @RequestMapping("/api/sites/{id}/floors")
    public List<Floors> getAllFloors(@PathVariable String id){
        return floorsService.getAllFloors(id);
    }

    @RequestMapping(method = RequestMethod.POST, value = "/api/sites/{siteId}/floors")
    public void addFloor(@RequestBody Floors floor, @PathVariable String siteId){
        floor.setSite(new Sites(siteId, "",""));
        floorsService.addFloor(floor);
    }

}

这是SitesService:

@Service
public class SitesService {

    @Autowired
    private SitesRepository sitesRepository;

    public List<Sites> getAllSites(){
        List<Sites> sites = new ArrayList<>();
        sitesRepository.findAll()
                .forEach(sites::add);
        return sites;
    }

    public void addSite(Sites site){
        sitesRepository.save(site);
    }
}

这是我的FloorsRepository:

public interface FloorsRepository extends CrudRepository<Floors, Integer> {

    public List<Floors> getFloorsBySiteId(String siteId);
}

然后在 MySQL Workbench 中，我创建了两个表:

CREATE TABLE `employeelocator1`.`sites` (
  `id` VARCHAR(45) NOT NULL,
  `site` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `employeelocator1`.`floors` (
  `id` VARCHAR(45) NOT NULL,
  `floor` VARCHAR(45) NULL,
  `siteId` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `FK_siteId_idx` (`siteId` ASC),
  CONSTRAINT `FK_siteId`
    FOREIGN KEY (`siteId`)
    REFERENCES `employeelocator1`.`sites` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

当我使用 /api/sites 获取所有站点的列表时，它运行良好。但是，当我尝试使用 /api/sites/1/floors 获取站点 #1 中所有楼层的列表时，出现错误:

o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1054, SQLState: 42S22
o.h.engine.jdbc.spi.SqlExceptionHelper   : Unknown column 'floors0_.site_id' in 'field list'

是不是我的数据库和表有问题？或者代码有问题？

Answer 1

找到了我的问题的答案。 floors 表中的 FK 应命名为 site_id 而不是 siteId