Home Spring Boot JPA: Path expected for join
Reply: 2

Spring Boot JPA: Path expected for join

smeeb
1#
smeeb Published in 2018-01-11 19:36:58Z

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?

dawid
2#
dawid Reply to 2018-01-11 20:07:19Z

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")
Simon Martinelli
3#
Simon Martinelli Reply to 2018-01-12 07:32:00Z

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

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.356442 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO