Strange exception when mapping two columns in a table to the same column in another table

There are two entities:

@Entity
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "book_id")
    int id;

    @Column(name = "book_name")
    String name;

    @ManyToOne
    @JoinColumn(name = "book_author", referencedColumnName = "person_id")
    Person author;

    @ManyToOne
    @JoinColumn(name = "book_reviewer", referencedColumnName = "person_id")
    Person reviewer;

    public Book(String name, Person author, Person reviewer) {
        this.name = name;
        this.author = author;
        this.reviewer = reviewer;
    }

    public Book() {
    }

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", name='" + name + ''' +
                ", author=" + author +
                ", reviewer=" + reviewer +
                '}';
    }
}

and

@Entity
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "person_id")
    int id;

    @Column(name = "person_name")
    String name;

    public Person(String name) {
        this.name = name;
    }

    public Person() {
    }

    @Override
    public String toString() {
        return "Person{" +
                "id=" + id +
                ", name='" + name + ''' +
                '}';
    }
}

when i create repository with custom query

public interface BookRepository extends CrudRepository<Book, Integer> {
    List<Book> findAll();

    @Query("SELECT id, name, author, reviewer FROM Book")
    List<Book> testQuery();
}

the exception is thrown

Caused by: org.hibernate.QueryException: No data type for node:        org.hibernate.hql.internal.ast.tree.IdentNode 
 -[IDENT] IdentNode: 'reviewer' {originalText=reviewer}
at org.hibernate.hql.internal.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:161) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
at org.hibernate.hql.internal.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:1026) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
at org.hibernate.hql.internal.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:794) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:694) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:330) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:278) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:276) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
... 73 common frames omitted

but if i remove the second referencing column from the query

@Query("SELECT id, name, author FROM Book")
List<Book> testQuery();

the program starts without exceptions.

Moreover, if i use standart query method bookRepository.findAll():

Person a,b ;
a = new Person("Person A");
b = new Person("Person B");

personRepository.save(a);
personRepository.save(b);

Book book_a = new Book("Test book", a, b);
Book book_b = new Book("Test book", b, a);

bookRepository.save(book_a);
bookRepository.save(book_b);

System.out.println("Books:");

for (Book book: bookRepository.findAll()) {
    System.out.println(book);
}

all works fine

Books: Book{id=3, name=’Test book’, author=Person{id=1, name=’Person A’}, reviewer=Person{id=2, name=’Person B’}} Book{id=4, name=’Test book’, author=Person{id=2, name=’Person B’}, reviewer=Person{id=1, name=’Person A’}}

Answer

Replace this:

 @Query("SELECT id, name, author, reviewer FROM Book")
List<Book> testQuery();

with below (Use aliases)

@Query("SELECT id, name, b.author, b.reviewer FROM Book b")
List<Book> testQuery();

Advertisement

Answer