Spring+Hibernate无法创建查询，MySQL表创建失败求助

Spring+Hibernate项目中出现无法创建查询的问题，导致MySQL无法创建表。排查Query语句和实体参数后未解决，其他接口正常工作，仅CourseDao存在问题。

CourseDao接口 #

public interface CourseDao extends JpaRepository<Course, Long> {
    
    List<Course> findCourseBycourseName(String keyword);
    
    @Query(value = "select * from courses as c where c.course_id in (select e.course_id from enrolled_in as e where e.student_id = :studentId)")
    List<Course> getCourseBylistStudent(@Param("studentId") Long studentId);

}

Course实体 #

@Entity
@Table(name = "courses")
public class Course {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "course_id", nullable=false)
    private Long courseId;
    
    @Basic 
    @Column(name = "name", nullable=false, length = 45)
    private String courseName;
    
    @Basic
    @Column(name = "duration", nullable=false, length = 45)
    private String courseDuration;
    
    @Basic
    @Column(name = "description", nullable=false, length = 45)
    private String courseDescription;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "instructor_id", referencedColumnName = "instructor_id", nullable = false)
    private Instructor instructor;
    
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "enrolled_in", 
               joinColumns = {@JoinColumn(name = "course_id")}, 
               inverseJoinColumns = {@JoinColumn(name = "student_id")})
    private Set<Student> listStudent = new HashSet<>();
}

Student实体 #

@Entity
@Table(name = "Students")
public class Student {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "student_id", nullable = false)
    private Long studentId;

    @Basic
    @Column(name = "first_name", nullable = false, length = 45)
    private String firstName;

    @Basic
    @Column(name = "last_name", nullable = false, length = 45)
    private String lastName;

    @Basic
    @Column(name = "level", nullable = false, length = 45)
    private String studentLevel;

    @ManyToMany(mappedBy = "listStudent", fetch = FetchType.LAZY)
    private Set<Course> courses = new HashSet<>();

    @OneToOne(cascade = CascadeType.REMOVE)
    @JoinColumn(name = "user_id", referencedColumnName = "user_id", nullable = false)
    private User user;
}

Error creating bean with name 'courseDao' defined in spring.dao.CourseDao defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Could not create query for public abstract java.util.List spring.dao.CourseDao.getCourseBylistStudent(java.lang.Long); Reason: Validation failed for query for method public abstract java.util.List spring.dao.CourseDao.getCourseBylistStudent(java.lang.Long)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1751) ~[spring-beans-6.0.2.jar:6.0.2]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:599) ~[spring-beans-6.0.2.jar:6.0.2]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:521) ~[spring-beans-6.0.2.jar:6.0.2]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:326) ~[spring-beans-6.0.2.jar:6.0.2]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-6.0.2.jar:6.0.2]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:324) ~[spring-beans-6.0.2.jar:6.0.2]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200) ~[spring-beans-6.0.2.jar:6.0.2]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:955) ~[spring-beans-6.0.2.jar:6.0.2]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:915) ~[spring-context-6.0.2.jar:6.0.2]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:584) ~[spring-context-6.0.2.jar:6.0.2]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:730) ~[spring-boot-3.0.0.jar:3.0.0]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:432) ~[spring-boot-3.0.0.jar:3.0.0]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:308) ~[spring-boot-3.0.0.jar:3.0.0]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1302) ~[spring-boot-3.0.0.jar:3.0.0]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1291) ~[spring-boot-3.0.0.jar:3.0.0]
    at spring.CourseSystemApplication.main(CourseSystemApplication.java:12) ~[classes/:na]
Caused by: org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List spring.dao.CourseDao.getCourseBylistStudent(java.lang.Long); Reason: Validation failed for query for method public abstract java.util.List spring.dao.CourseDao.getCourseBylistStudent(java.lang.Long)
        at java.base/java.util.Optional.map(Optional.java:260) ~[na:na]
        at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.<init>(QueryExecutorMethodInterceptor.java:88) ~[spring-data-commons-3.0.0.jar:3.0.0]
        at 
    Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List spring.dao.CourseDao.getCourseBylistStudent(java.lang.Long)
        Caused by: org.hibernate.query.SemanticException: A query exception occurred [select * from courses as c where c.course_id in (select e.course_id from enrolled_in as e where e.student_id = :studentId)]
            ... 48 common frames omitted

1. 修复自定义@Query的原生SQL配置 #

JPA的@Query默认使用JPQL语法，而非原生SQL。你写的是原生MySQL语句，需要添加nativeQuery = true参数：

@Query(value = "select * from courses as c where c.course_id in (select e.course_id from enrolled_in as e where e.student_id = :studentId)", nativeQuery = true)
List<Course> getCourseBylistStudent(@Param("studentId") Long studentId);

2. 修复方法名的命名规范 #

Spring Data JPA的方法名需要遵循驼峰命名规则，findCourseBycourseName命名错误，应该改为findByCourseName（对应实体中的courseName字段）：

List<Course> findByCourseName(String keyword);

3. 可选：使用JPQL替代原生SQL（推荐） #

如果想遵循JPA规范，避免依赖特定数据库的SQL语法，可以改用JPQL：

@Query("select c from Course c join c.listStudent s where s.studentId = :studentId")
List<Course> getCourseBylistStudent(@Param("studentId") Long studentId);

JPQL通过实体和关联关系查询，不需要直接操作中间表enrolled_in，兼容性更强。

内容的提问来源于stack exchange，提问作者user18583088