Why this Hibernate HQL query that should select some random rows doesn't works? I am obtaining an "QuerySyntaxException: unexpected token"

Question

I have the following situation:

I am working on an application that uses Hibernate and I am trying to create an HQL query that extract n random record tht have some specific condition.

So I have these entity classes:

1) I have this Room entity class that represent a room of an accomodation:

@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) Then I have this RoomMedia entity class that represent a photo related to a specific Room entity:

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

}

As you can see in the previous code these table are linked togheter.

Ok, now I am trying to create an HQL query that return 2 random records of table mapped by the RoomMedia entity class belonging to a specific room tipology, that is a specific field of the Room entity class, this one:

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

So, searching online I found this interesting SO post: MySQL select 10 random rows from 600K rows fast

that referer to this article: http://jan.kneschke.de/projects/mysql/order-by-rand/

This is a pure SQL solution, I need to translate it into an SQL adapted to my entity

So I have chose to implement the proposed solution (the one proposed on the SO post because I can have "holes"), I have implemented this HQL query into my DAO class:

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

}

As you can see I tryid to retrace the same reasoning on my RoomMedia entity

• I Work on the RoomMedia entity.
• I have added the extra logic related to the specification of a specifc room tipology by: rm1.roomTipology.id = :roomTipologyId

It doesn't work, at the application startup I am obtaining this error message:

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

Why? What is the problem? How can I fix it converting it into a propper working HQL query?

Answer 1

Your sub-query occur in the JOIN clause :

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

but you cannot because Hibernate documentation states :

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

whence the exception caused by the unexpected ( character after the JOIN clause :

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

1) In your case, a alternative suitable solution would be to create and execute a native SQL query with Hibernate.

You could reuse your original SQL query in this way by using an Entity Manager (I propose it as you seem using standard):

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

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

Not tested but you have the idea.

2) Otherwise, another way would be to modify your query to place the subquery in the WHERE clause.