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

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!```


</details>


# 答案1
**得分**: 1

让我们一步一步来看：
1. 这是一个非实体表 - 所以它是否导致了问题？你是否以正确的方式执行了操作？
2. 你正在尝试使用 Spring Data JPA 进行本地查询调用。

&lt;br&gt;
故障排除尝试1：从第二部分开始进行故障排除，因为它比较简单，以下是你应该尝试的内容：

仅仅使用 @Query 是不够的。请参考下面的示例，需要注意的重要部分是 "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)`

故障排除尝试2：如果上述方法不起作用，请转到第1部分。网络上有一些很好的解答。我在这里粘贴了一个好的帖子链接作为参考：https://stackoverflow.com/questions/29082749/spring-data-jpa-map-the-native-query-result-to-non-entity-pojo

<details>
<summary>英文:</summary>

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

&lt;br&gt;
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 &quot;nativeQuery=true&quot;

`@Query(value = &quot;SELECT u FROM User u INNER JOIN tbl_friends f ON User.id = f.personId WHERE f.friendId = :objId&quot;, 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 : https://stackoverflow.com/questions/29082749/spring-data-jpa-map-the-native-query-result-to-non-entity-pojo

</details>