How to create a custom @Query on Spring Boot using a non Entity table?

Question

I am stuck at a simple Friends schema. I have a User entity that has a List of users as friends. Spring is creating for me tbl_friends that will keep the data of the join, like I set user 1 to be friends of user3 and user4. When I pick the user1 it gets the user3 and user4 as friends. That is working fine, but what I want to do is when I get user3 it keeps the information of it being friends with user1.

@GeneratedValue(strategy=GenerationType.IDENTITY)
private Integer id;
private String userName;
private String fullName;
private String phoneNumber;
private String email;
@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinTable(name="TBL_FRIENDS",
    joinColumns=@JoinColumn(name="person_id"),
    inverseJoinColumns=@JoinColumn(name="friend_id")        
)
private List<User> friends;

@JsonIgnore
@ManyToMany(mappedBy = "friends")
private List<User> friendOf;

In a regular SQL query all I would to do to get a list of users friends of user3 would be:

SELECT user.id, user.full_name, FROM user INNER JOIN tbl_friends f ON user.id = f.person_id WHERE f.friend_id = 3 ; // (this code actually works on h2-console to check the tables created by spring jpa)

When I try do something similar in UserRepository it gives and error because it expects a path (?)

    @Query("SELECT u FROM User u INNER JOIN tbl_friends f ON User.id = f.personId WHERE f.friendId = :objId")
    List<User> findByFriendsId(@Param("objId") Integer friend_id);```

**This the error: **

```2020-05-04 18:39:55.660  INFO 22292 --- [  restartedMain] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
2020-05-04 18:39:55.666  INFO 22292 --- [  restartedMain] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2020-05-04 18:39:55.694  WARN 22292 --- [  restartedMain] o.s.b.d.a.OptionalLiveReloadServer       : Unable to start LiveReload server
2020-05-04 18:39:55.995 ERROR 22292 --- [  restartedMain] o.h.hql.internal.ast.ErrorTracker        :  Path expected for join!
2020-05-04 18:39:56.000 ERROR 22292 --- [  restartedMain] o.h.hql.internal.ast.ErrorTracker        :  Path expected for join!

antlr.SemanticException: Path expected for join!```

Chandan · Accepted Answer · 2020-05-04T23:59:52.953

Lets see it step by step : 1. It is non entity table - so is it causing the issue, did you do it the right way ? 2. You are trying to make a Native Query call using Spring Data JPA

Troubleshooting Trial 1. Try troubleshooting from 2nd part, as it is simple, here is what you should try:

Simply mentioning @Query is not sufficient. See below, the important part to notice is "nativeQuery=true"

@Query(value = "SELECT u FROM User u INNER JOIN tbl_friends f ON User.id = f.personId WHERE f.friendId = :objId", nativeQuery = true)

Troubleshooting Trial 2: If the above doesnt work then jump to the part 1. There are some good answers on the web for that. I am pasting link of one such good thread here : Spring Data JPA map the native query result to Non-Entity POJO

How to create a custom @Query on Spring Boot using a non Entity table?

1 Answers1