Spring Boot公交调度应用SQL JOIN报错及JPA查询构建问题

问题背景 #

开发公交调度Web应用，需求是搜索包含用户输入站点的时刻表列表，但执行数据库查询时出现500内部服务器错误。

原生SQL查询错误 #

错误提示 #

ERROR: argument of JOIN/ON must be type boolean, not type 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)

出错的原生SQL代码 #

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);

}

JPA查询修改后的错误 #

修改后的JPA查询代码 #

@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]

错误原因与修复方案 #

1. 原生SQL错误修复 #

SQL的JOIN ON子句必须是布尔条件，原代码直接写字段名，数据库无法识别为关联条件。需要明确指定字段的等于关系：

修改后的原生SQL Repository方法：

@Query(value = "SELECT DISTINCT c.* FROM schedules c " +
                "JOIN routes r ON c.routes_id = r.id " +
                "JOIN routes_stops rs on r.id = rs.route_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)
List<Schedule> findAllByStopsAndTimeForSearch(@Param("stopName") String stopName,
                                          @Param("departureTime")LocalDateTime departureTime,
                                          @Param("endOfDay") LocalDateTime endOfDay);

2. JPA查询错误修复 #

JPQL中，实体间的关联关系已通过注解定义，不需要手动写ON条件，直接通过实体属性关联即可：

修改后的JPQL Repository方法：

@Query("SELECT DISTINCT c FROM Schedule c " +
        "JOIN c.route r " +
        "JOIN r.routesStops rs " +
        "JOIN rs.stop s " +
        "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);

语法说明

• JOIN c.route r：通过Schedule实体的route属性关联Route实体
• JOIN r.routesStops rs：通过Route实体的routesStops集合关联RoutesStops实体
• JOIN rs.stop s：通过RoutesStops实体的stop属性关联Stop实体

JPA会自动根据实体注解生成正确的关联条件，无需手动指定ON子句。

3. 额外检查项 #

• 确认实体类关联字段与数据库外键一致：比如Schedule的route字段对应数据库routes_id列，RoutesStops的routeId对应Route的id列等
• 验证数据库表的外键约束是否正确配置

内容的提问来源于stack exchange，提问作者Артем Лебідь