java - 为什么这个应该选择一些随机行的 Hibernate HQL 查询不起作用？我获得了 "QuerySyntaxException: unexpected token"

Question

我有以下情况:

我正在开发一个使用 Hibernate 的应用程序，我正在尝试创建一个 HQL 查询来提取具有某些特定条件的 n 条随机记录。

所以我有这些实体类:

1)我有这个 Room 实体类，代表住宿的房间:

@Entity
@Table(name = "room")
public class Room implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;


    @ManyToOne
    @JoinColumn(name = "id_accomodation_fk", nullable = false)
    private Accomodation accomodation;

    @ManyToOne
    @JoinColumn(name = "id_room_tipology_fk", nullable = false)
    private RoomTipology roomTipology;

    @OneToMany(mappedBy = "room")
    private List<RoomMedia> roomMediaList;

    @Column(name = "room_number")
    private String number;

    @Column(name = "room_name")
    private String name;

    @Column(name = "room_description")
    private String description;

    @Column(name = "is_enabled")
    private Boolean isEnabled;

    ........................................................................
    ........................................................................
    GETTER AND SETTER METHODS
    ........................................................................
    ........................................................................
}

2) 然后我有这个 RoomMedia 实体类，它表示与特定 Room 实体相关的照片:

@Entity
@Table(name = "room_media")
public class RoomMedia {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "id_room")
    private Long idRoom;

    @ManyToOne
    @JoinColumn(name = "id_room", nullable = false)     // da rinominare anche sul DB in room_fk
    private Room room;

    @Lob
    @Column(name = "media")
    private byte[] media;

    private String description;

    ........................................................................
    ........................................................................
    GETTER AND SETTER METHODS
    ........................................................................
    ........................................................................

}

正如您在前面的代码中看到的，这些表链接在一起。

好的，现在我正在尝试创建一个 HQL 查询，该查询返回由属于特定房间拓扑的 RoomMedia 实体类映射的表的 2 个随机记录，即Room实体类的特定字段，这个:

@ManyToOne
@JoinColumn(name = "id_room_tipology_fk", nullable = false)
private RoomTipology roomTipology;

所以，在网上搜索我发现了这个有趣的帖子:MySQL select 10 random rows from 600K rows fast

这篇文章的引用者:http://jan.kneschke.de/projects/mysql/order-by-rand/

这是一个纯SQL解决方案，我需要将其转换为适合我的实体的SQL

因此，我选择实现建议的解决方案(SO 帖子中建议的解决方案，因为我可能有“漏洞”)，我已将这个 HQL 查询实现到我的 DAO 类中:

@Repository
@Transactional(propagation = Propagation.MANDATORY)
public interface RoomMediaDAO extends JpaRepository<RoomMedia, Long> {

    @Query( "FROM RoomMedia as rm1 JOIN " +
            "(SELECT (RAND() * (SELECT MAX(id) FROM RoomMedia)) AS id) AS rm2 " +
            "WHERE rm1.id >= rm2.id AND rm1.roomTipology.id = :roomTipologyId " +
            "ORDER BY rm1.id ASC " +
            "LIMIT 2")
    List<RoomMedia> getRandomRoomMediaOfACategory(@Param(value = "roomTipologyId") Long roomTipologyId);

}

如您所见，我尝试在我的 RoomMedia 实体上追溯相同的推理

• 我负责 RoomMedia 实体。
• 我通过以下方式添加了与特定房间技术规范相关的额外逻辑:rm1.roomTipology.id = :roomTipologyId

它不起作用，在应用程序启动时我收到此错误消息:

Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.betrivius.dao.RoomMediaDAO.getRandomRoomMediaOfACategory(java.lang.Long)!
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:92)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:62)
    at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:72)
    at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromQueryAnnotation(JpaQueryFactory.java:53)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:144)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:212)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:77)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.<init>(RepositoryFactorySupport.java:435)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:220)
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.initAndReturn(RepositoryFactoryBeanSupport.java:266)
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:252)
    at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:92)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1642)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1579)
    ... 39 more
Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 49 [FROM com.betrivius.domain.RoomMedia as rm1 JOIN (SELECT (RAND() * (SELECT MAX(id) FROM RoomMedia)) AS id) AS rm2 WHERE rm1.id >= rm2.id AND rm1.roomTipology.id = :roomTipologyId ORDER BY rm1.id ASC LIMIT 2]
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1750)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1683)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:331)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:347)
    at com.sun.proxy.$Proxy107.createQuery(Unknown Source)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:86)
    ... 52 more
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 49 [FROM com.betrivius.domain.RoomMedia as rm1 JOIN (SELECT (RAND() * (SELECT MAX(id) FROM RoomMedia)) AS id) AS rm2 WHERE rm1.id >= rm2.id AND rm1.roomTipology.id = :roomTipologyId ORDER BY rm1.id ASC LIMIT 2]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:91)
    at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:109)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:304)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:203)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:131)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:93)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:167)
    at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)
    at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1836)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:328)
    ... 59 more

为什么？问题是什么？我该如何修复它，将其转换为正确的 HQL 查询？

Answer 1

您的子查询出现在 JOIN 子句中:

"FROM RoomMedia as rm1 JOIN " +
            "(SELECT (RAND() * (SELECT MAX(id) FROM RoomMedia)) AS id) AS rm2 " 

但你不能，因为 Hibernate documentation状态:

Note that HQL subqueries can occur only in the select or where clauses.

JOIN 子句后意外的 ( 字符引起的异常:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: (

1) 对于您的情况，另一种合适的解决方案是使用 Hibernate 创建并执行 native SQL 查询。

您可以通过使用实体管理器以这种方式重用原始 SQL 查询(我建议您使用标准):

 String sqlQuery = " SELECT rm1 FROM room_media as rm1 JOIN room_media on... "      );

List<RoomMedia> roomMediat= (List<RoomMedia>)em.createQuery(sqlQuery)
                              .getResultList(); 

未经测试，但你有想法。

2) 否则，另一种方法是修改查询以将子查询放在 WHERE 子句中。