Spring Boot JPA: Path expected for join

Question

Please note: not a dupe of this one...I'm getting the same exception but for a different reason!

JPA/Hibernate here, being used by a Spring Boot app written in Groovy (Groovy shouldn't matter; just mentioning it for good measure). I have the 3 following entities:

@Entity(name = 'accounts')
class Account {
    @Id
    @Column(name='account_id')
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    Long id

    @Column(name='account_ref_id')
    String refId

    @Column(name = 'account_email')
    @NotEmpty
    String email

    @Column(name = 'account_username')
    String username
}

@Entity(name = 'security_token_types')
class SecurityTokenType {
  @Id
  @Column(name='security_token_type_id')
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  Long id

  @Column(name='security_token_type_ref_id')
  String refId

  @Column(name='security_token_type_name')
  String name

  @Column(name='security_token_type_label')
  String label

  @Column(name='security_token_type_description')
  String description
}

@Entity(name = 'security_tokens')
class SecurityToken {
    @Id
    @Column(name='security_token_id')
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    Long id

    @Column(name='security_token_ref_id')
    String refId

    @OneToOne(fetch = FetchType.EAGER, cascade = [CascadeType.PERSIST, CascadeType.MERGE])
    @JoinColumn(name = 'account_id', referencedColumnName = 'account_id')
    Account account

    @Column(name = 'security_token')
    String token

    @OneToOne(fetch = FetchType.EAGER, cascade = [CascadeType.PERSIST, CascadeType.MERGE])
    @JoinColumn(name = 'security_token_type_id', referencedColumnName = 'security_token_type_id')
    SecurityTokenType type

    @Column(name = 'security_token_generated_on')
    Date generatedOn
}

And a SecurityPersistor (DAO) repository:

interface SecurityTokenPersistor extends CrudRepository<SecurityToken, Long> {
    @Query("FROM security_tokens st INNER JOIN security_token_types stt ON st.security_token_type_id = stt.security_token_type_id WHERE stt.security_token_type_label = :type AND st.account_id = :accountId")
    Set<SecurityToken> findTokensByAccountAndType(@Param('accountId') Long accountId, @Param('type') String type)
}

At runtime I'm getting these:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Path expected for join! [FROM com.armbet.ws.domain.entities.SecurityToken st INNER JOIN security_token_types stt ON st.security_token_type_id = stt.security_token_type_id WHERE stt.security_token_type_label = :type AND st.account_id = :accountId]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74)
    at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:268)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:190)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:76)

I feel like my JOIN syntax in the SecurityPersistor#findTokensByAccountAndType method is wrong. Any ideas where I'm going awry?

Possible duplicate of [HQL ERROR: Path expected for join](https://stackoverflow.com/questions/10448935/hql-error-path-expected-for-join) — pvpkiran, Jan 11 '18 at 19:52
Thanks @pvpkiran I saw that one too but if you read the problem description as well as the separate answer you'll see that I've already tried that solution. Same exception/error message, two different problems! — smeeb, Jan 11 '18 at 20:14

Simon Martinelli · Answer 1 · 2018-01-12T07:32:00.587

3

If you have a toOne mapped relationship you don't need to join.

This query should work:

@Query("FROM security_tokens st WHERE st.type.label = :type AND st.account.id = :accountId")

You should read about JPQL. A good source is the Hibernate documentation:

http://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html#hql

edited Jan 12 '18 at 07:32

answered Jan 11 '18 at 19:56

Simon Martinelli

34,053
5
48
82

Thanks @Simon (+1) immensely appreciated! However your suggestion changes the exception to `Caused by: org.hibernate.QueryException: could not resolve property: account_id of: com.armbet.ws.domain.entities.SecurityToken`...any ideas? Maybe change it to `account`? – smeeb Jan 11 '18 at 20:04
it should be st.account.id – Simon Martinelli Jan 12 '18 at 07:31

score 3 · Accepted Answer · answered Jan 11 '18 at 20:07

3

also, don't change entity names, if you want to point to table with different name then entity use:

@Entity
@Table(name = "security_tokens")
class SecurityToken {
}

instead of

@Entity(name = 'security_tokens')
class SecurityToken {
}

then query:

@Query("FROM SecurityToken st where st.type.label = :type AND st.account.id = :accountId")

answered Jan 11 '18 at 20:07

dawid

207
2
5

Thanks @dawid (+1) I gave your suggestion a try and still the same exception persists. Any ideas? – smeeb Jan 11 '18 at 20:16
But it the query I proposed there are no joins so if u use it you can't have: QuerySyntaxException: Path expected for join! (there are joins on db level but not on JPQL level - exception would be different) – dawid Jan 11 '18 at 20:23
ahh so sorry! cut n paste error! With your suggestion I get `Caused by: org.hibernate.QueryException: could not resolve property: account_id of: com.armbet.ws.domain.entities.SecurityToken`. – smeeb Jan 11 '18 at 20:38
1

again, please use `st.account.id = :accountId` not `st.account_id = :accountId` - it's java names - not sql – dawid Jan 11 '18 at 20:49
and the plot thickens! making that latest change now creates `Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: security_tokens is not mapped` exceptions! What does that mean (not mapped)?! – smeeb Jan 11 '18 at 21:10
:D `FROM SecurityToken` not `FROM security_tokens` - again - it's java names, not sql – dawid Jan 11 '18 at 21:16
Doh!!! That was it, thanks @dawid (+1) I'm sending a green check and some good vibes + energy your way! Thank you so much! – smeeb Jan 11 '18 at 21:19

Spring Boot JPA: Path expected for join

2 Answers2