Spring Boot 错误：JOIN/ON的参数必须是boolean类型，而不是bigint类型,Java Sping Boot 应用程序

8cdiaqws  于 2023-10-16  发布在  Spring
关注(0)|答案(1)|浏览(114)

我正在开发一个带有公交时刻表的Web应用程序。我的任务是搜索一个时间表列表，其中有一个停止输入的用户。但是当我将查询写入数据库时，我得到一个白标签错误页面“ERROR：JOIN/ON的参数必须是boolean类型，而不是bigint类型”

There was an unexpected error (type=Internal Server Error, status=500).
JDBC exception executing SQL [SELECT DISTINCT c FROM schedules c JOIN routes r ON c.routes_id JOIN routes_stops rs on r.route_id JOIN stops s ON rs.stop_id WHERE s.name = ? and c.departure_time >= ? and c.departure_time < ?] [ERROR: argument of JOIN/ON must be type boolean, not type bigint Position: 53] [n/a]; SQL [n/a]
org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [SELECT DISTINCT c FROM schedules c JOIN routes r ON c.routes_id JOIN routes_stops rs on r.route_id JOIN stops s ON rs.stop_id WHERE s.name = ? and c.departure_time >= ? and c.departure_time < ?] [ERROR: argument of JOIN/ON must be type boolean, not type bigint
  Position: 53] [n/a]; SQL [n/a]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:256)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:229)

我的请求看起来像这样：

public interface ScheduleRepository extends JpaRepository<Schedule, Long> {
    
// more methods ... 

    @Query(value = "SELECT DISTINCT c FROM schedules c " +
                    "JOIN routes r ON c.routes_id " +
                    "JOIN routes_stops rs on r.route_id " +
                    "JOIN stops s ON rs.stop_id " +
                    "WHERE s.name = :stopName and c.departure_time >= :departureTime and c.departure_time < :endOfDay", nativeQuery = true)
    List<Schedule> findAllByStopsAndTimeForSearch(@Param("stopName") String stopName,
                                              @Param("departureTime")LocalDateTime departureTime,
                                              @Param("endOfDay") LocalDateTime endOfDay);

}

服务接口：

public interface ScheduleService {

    // more methods ... 

    List<Schedule> findAllByStopsAndTimeForSearchToday(String stopName);
    List<Schedule> findAllByStopsAndTimeForSearchTomorrow(String stopName);
    List<Schedule> findAllByStopsAndTimeForSearchAfterTomorrow(String stopName);
}

服务实施：

@Service
public class ScheduleServiceImpl implements ScheduleService {
    private ScheduleRepository repository;

    @Autowired
    public ScheduleServiceImpl(ScheduleRepository repository) {
        this.repository = repository;
    }

    // more methods ... 

    @Override
    public List<Schedule> findAllByStopsAndTimeForSearchToday(String stopName) {
        LocalDateTime currentDateTime = LocalDateTime.now();
        LocalDateTime endOfDay = currentDateTime.toLocalDate().plusDays(1).atTime(LocalTime.MIDNIGHT);

        List<Schedule> scheduleToday = repository.findAllByStopsAndTimeForSearch(stopName, currentDateTime, endOfDay);
        return scheduleToday;
    }

    @Override
    public List<Schedule> findAllByStopsAndTimeForSearchTomorrow(String stopName) {
        LocalDateTime currentDateTime = LocalDateTime.now();
        LocalDateTime startOfNextDay = currentDateTime.toLocalDate().plusDays(1).atTime(LocalTime.MIDNIGHT);
        LocalDateTime endOfNextDay = startOfNextDay.toLocalDate().plusDays(1).atTime(LocalTime.MIDNIGHT);
        List<Schedule> scheduleTomorrow = repository.findAllByStopsAndTimeForSearch(stopName, startOfNextDay, endOfNextDay);
        return scheduleTomorrow;
    }

    @Override
    public List<Schedule> findAllByStopsAndTimeForSearchAfterTomorrow(String stopName) {
        LocalDateTime currentDateTime = LocalDateTime.now();
        LocalDateTime startOfNextDay = currentDateTime.toLocalDate().plusDays(2).atTime(LocalTime.MIDNIGHT);
        LocalDateTime endOfNextDay = startOfNextDay.toLocalDate().plusDays(2).atTime(LocalTime.MIDNIGHT);
        List<Schedule> scheduleAfterTomorrow = repository.findAllByStopsAndTimeForSearch(stopName, startOfNextDay, endOfNextDay);
        return scheduleAfterTomorrow;
    }
}

控制器：

@Controller
public class BusController {
    private ScheduleService scheduleService;

    @Autowired
    public BusController(ScheduleService scheduleService) {
        this.scheduleService = scheduleService;
    }

    // more methods ...

    @GetMapping("/schedule/search")
    public String searchBuses(@Valid @ModelAttribute("search") SearchDto search,
                              BindingResult bindingResult,
                              Model model){
        if(bindingResult.hasErrors()){
            LocalDateTime currentTime = LocalDateTime.now();

            model.addAttribute("time", currentTime);
            model.addAttribute("search", search);
            return "buses-list";
        }

        LocalDateTime currentTime = LocalDateTime.now();
        List<Schedule> scheduleToday = scheduleService.findAllByStopsAndTimeForSearchToday(search.getSearchLine());
        List<Schedule> scheduleTomorrow = scheduleService.findAllByStopsAndTimeForSearchTomorrow(search.getSearchLine());
        List<Schedule> scheduleAfterTomorrow = scheduleService.findAllByStopsAndTimeForSearchAfterTomorrow(search.getSearchLine());

        model.addAttribute("time", currentTime);
        model.addAttribute("scheduleToday", scheduleToday);
        model.addAttribute("scheduleTomorrow", scheduleTomorrow);
        model.addAttribute("scheduleAfterTomorrow", scheduleAfterTomorrow);
        model.addAttribute("search", new SearchDto());

        return "buses-list";
    }
}

数据库表对象看起来像这样：
时间表：

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@Table(name = "schedules")
public class Schedule {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @ManyToOne
    @JoinColumn(name = "buses_id")
    private Bus bus;
    @ManyToOne
    @JoinColumn(name = "routes_id")
    private Route route;
    private Double price;
    private String accessibility;
    private Integer seats;
    private LocalDateTime arrivalTime;
    private LocalDateTime departureTime;
}

路线：

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@Table(name = "routes")
public class Route {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    @OneToMany(mappedBy = "route")
    private List<Schedule> schedule;
    @OneToMany(
            mappedBy = "route",
            cascade = CascadeType.ALL,
            orphanRemoval = true
    )
    private List<RoutesStops> routesStops;
}

停止：

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@Table(name = "stops")
public class Stop {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    @OneToMany(
            mappedBy = "stop",
            cascade = CascadeType.ALL,
            orphanRemoval = true
    )
    private List<RoutesStops> routesStops;
}

RoutesStopsId：

@Embeddable
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class RoutesStopsId implements Serializable {
    private Long routeId;
    private Long stopId;

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        RoutesStopsId that = (RoutesStopsId) o;
        return Objects.equals(routeId, that.routeId) && Objects.equals(stopId, that.stopId);
    }

    @Override
    public int hashCode() {
        return Objects.hash(routeId, stopId);
    }
}

路线停靠站：

@Entity
@Table(name = "routes_stops")
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class RoutesStops {
    @EmbeddedId
    private RoutesStopsId id;
    @ManyToOne(fetch = FetchType.LAZY)
    @MapsId("routeId")
    private Route route;
    @ManyToOne(fetch = FetchType.LAZY)
    @MapsId("stopId")
    private Stop stop;
    private Integer stopNumber;
    private Double price;
    private LocalTime arrivalTime;
    private LocalTime departureTime;
}

公交车：

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@Table(name = "buses")
public class Bus {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String licensePlate;
    @OneToMany(mappedBy = "bus")
    private List<Schedule> schedule;
    @ManyToOne
    @JoinColumn(name = "company_id")
    private Company company;
}

公司名称：

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@Table(name = "companies")
public class Company {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    @OneToMany(mappedBy = "company")
    private List<Bus> bus;
}

我认为问题出在对数据库的请求的构造上，或者我没有正确地组织或链接DB表。
根据我读到的一个建议，我将请求更改为类似于存储库中的方法：

@Query("SELECT DISTINCT c FROM Schedule c " +
        "JOIN Route r ON c.route " +
        "JOIN RoutesStops rs on r.routesStops " +
        "JOIN Stop s ON rs.stop " +
        "WHERE s.name = :stopName and c.departureTime >= :departureTime and c.departureTime < :endOfDay")
List<Schedule> findAllByStopsAndTimeForSearch(@Param("stopName") String stopName,
                                             @Param("departureTime")LocalDateTime departureTime,
                                             @Param("endOfDay") LocalDateTime endOfDay);

但在那之后，我又犯了一个错误：

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'busController' defined in file [/Users/artem/IdeaProjects/Bus Schedule Application/target/classes/com/busschedule/web/controller/BusController.class]: Unsatisfied dependency expressed through constructor parameter 0: Error creating bean with name 'scheduleServiceImpl' defined in file [/Users/artem/IdeaProjects/Bus Schedule Application/target/classes/com/busschedule/web/service/impl/ScheduleServiceImpl.class]: Unsatisfied dependency expressed through constructor parameter 0: Error creating bean with name 'scheduleRepository' defined in com.busschedule.web.repository.ScheduleRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Could not create query for public abstract java.util.List com.busschedule.web.repository.ScheduleRepository.findAllByStopsAndTimeForSearch(java.lang.String,java.time.LocalDateTime,java.time.LocalDateTime); Reason: Validation failed for query for method public abstract java.util.List com.busschedule.web.repository.ScheduleRepository.findAllByStopsAndTimeForSearch(java.lang.String,java.time.LocalDateTime,java.time.LocalDateTime)
    at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:800) ~[spring-beans-6.0.11.jar:6.0.11]
    at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:245) ~[spring-beans-6.0.11.jar:6.0.11]

现在我很困惑，不明白哪个错误更好纠正，朝哪个方向前进

spring-boot

1条答案

按热度按时间
yvt65v4c

yvt65v4c1#

因为你使用的是nativeQuery（而不是JPQL/HQL），你需要正确地指定连接表达式：

@Query(value = "SELECT DISTINCT c FROM schedules c " +
                "JOIN routes r ON c.routes_id = r.id " +
                "JOIN routes_stops rs on rs.route_id = r.id " +
                "JOIN stops s ON rs.stop_id = s.id" +
                "WHERE s.name = :stopName and c.departure_time >= :departureTime and c.departure_time < :endOfDay", nativeQuery = true)
赞(0）分享  回复(0）举报  2023-10-16
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com