path expected join error! JPQL inner join query many to many example

Question

I was writing a query where i was fetching the list of books with the particular author_id, book and author are mapped as many to many relation book entity contains id, name and set of authors and author entity contain name and id. I created an intermediatory table writes which contain author_id and book_id. i have not created any writes entity class, its in the mysql db

What is wrong with the following query, i am getting an path expected join error!

@Query (SELECT b.name from Book b inner join writes w on b.id=w.book_id where w.author_id = ?1)
List<Book> findByAuthorId(Integer AuthorId);

My book entity is

package golive.data;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.persistence.Transient;

import com.fasterxml.jackson.annotation.JsonIgnore;
import com.sun.istack.internal.NotNull;

@Entity
@Table(name="book")
public class Book implements java.io.Serializable{

 @Id
 @GeneratedValue
 private Integer id;
 
 @NotNull
 @Column(name="name")
 private String name;


 @ManyToMany(cascade = CascadeType.ALL,fetch = FetchType.LAZY)
 @JoinTable(name = "writes", joinColumns = { @JoinColumn(name = "book_id") }, inverseJoinColumns = { @JoinColumn(name = "author_id") })
 private Set<Author> authors = new HashSet<Author>(); 


 public Set<Author> getAuthors() {
  return authors;
 }

 public Integer getId() {
  return id;
 }

 public String getName() {
  return name;
 }

 public void setAuthors(Set<Author> authors) {
  this.authors = authors;
 }

 public void setId(Integer id) {
  this.id = id;
 }

 public void setName(String name) {
  this.name = name;
 }
 
 
}

My author entity is

package golive.data;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.OneToMany;
import javax.persistence.OneToOne;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;

import com.fasterxml.jackson.annotation.JsonIgnore;
import com.sun.istack.internal.NotNull;

@Entity
@Table(name="author")
public class Author implements java.io.Serializable{

 @Id
 @GeneratedValue
 @Column(name="id")
 private Integer Id;
 
 @NotNull
 @Column(name="name")
 private String name;
 
 public Integer getId() {
  return Id;
 }

 public String getName() {
  return name;
 }

 public void setId(Integer id) {
  Id = id;
 }

 public void setName(String name) {
  this.name = name;
 }
 
}

I dont have any writes entity, it there in my database

Answer 1

You should add nativeQuery = true to Query annotation. And change your query to native form. If your database names are also Book and writes and column names are correct this should work.

@Query ("SELECT * from Book b inner join writes w on b.id=w.book_id where w.author_id = ?1", nativeQuery = true)
List<Book> findByAuthorId(Integer AuthorId);

Update

Star is added to query. You cannot select only bookName to book object. But you can select book names to a String list.

@Query ("SELECT b.name from Book b inner join writes w on b.id=w.book_id where w.author_id = ?1", nativeQuery = true)
List<String> findByAuthorId(Integer AuthorId);