How to take data from crosstabs from Database related ManyToMany relationship using Spring Boot / Thymeleaf

Question

There are two tables Books and Users. They are connected by a many-to-many relationship and have a crosstab between them with columns book_id, reader_id, start date, and end date. I need to take data from this table as User.login, Books.title, start_date and end_date. How can I get data from this table if I have the following entities:

Book Entity:

@Entity
@Table(name = "books")
public class Book {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private Long id;

@Column(name = "title")
private String title;

@Column(name = "publishing_year")
private Integer publishingYear;

@Column(name = "sell_cost")
private BigDecimal sellCost;

@Column(name = "rent_cost")
private BigDecimal rentCost;

@Column(name = "amount")
private Integer amount;

//=======Relationships=======

@ManyToMany(cascade = {CascadeType.DETACH,CascadeType.MERGE,CascadeType.PERSIST,CascadeType.REFRESH,CascadeType.REMOVE})
@JoinTable(name = "rented_books",
joinColumns = @JoinColumn(name = "book_id"),
inverseJoinColumns = @JoinColumn(name = "reader_id"))
private List<User> usersRented;
}

User Entity:

@Entity
@Table(name = "users")
public class User {

@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column(name = "username")
private String login;

@Column(name = "password")
private String password;

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

@Column(name = "surname")
private String surname;

@Column(name = "wallet")
private Integer wallet;

@Column(name = "enabled")
private boolean enabled;

//=======Relationships=======

@ManyToMany(mappedBy = "usersRented")
private List<Book> rentedBooks;

Book Repository

@Repository
public interface BookRepository  extends CrudRepository<Book,Long> {

@Query(value = "SELECT b.title,rb.start_date,rb.expiration_date FROM books b INNER JOIN rented_books rb ON rb.book_id = b.id INNER JOIN users u ON u.id = rb.reader_id WHERE u.id = ?1",nativeQuery = true)
Page<Book> findAllRentedBookByUser(Long id, Pageable pageable);

}

But this query doesn't work, throws this error:

java.sql.SQLException: Column 'id' not found.

Although in DBMS this query works fine

Answer 1

You are returning the Book entity so you need to select all fields for that entity. You can't return a partially filled entity, because JPA does not support partial entity population from native queries.
According to JPA specification:

3.10.16.1 Returning Managed Entities from Native Queries

When an entity is to be returned from a native query, the SQL statement should select all of the columns that are mapped to the entity object. This should include foreign key columns to related entities. The results obtained when insufficient data is available are undefined

Example of correct query:

@Repository
public interface BookRepository  extends CrudRepository<Book,Long> {
    @Query(value = "SELECT b.* FROM books b INNER JOIN rented_books rb ON rb.book_id = b.id INNER JOIN users u ON u.id = rb.reader_id WHERE u.id = ?1", nativeQuery = true)
    Page<Book> findAllRentedBookByUser(Long id, Pageable pageable);
}

In case you want to return a custom column set you need to create an additional simple POJO class and define a mapping for it.
Example: How to fix convertion error in Nativequery in Spring-boot

1. Create a custom POJO class

import java.util.Date;

public class BookDetails {
   private String title;
   private Date start_date;
   private Date expiration_date;

   public BookDetails(String title, Date start_date, Date expiration_date) {
        this.title = title;
        this.start_date = start_date;
        this.expiration_date = expiration_date;
    }

    public String getTitle() {
        return this.title;
    }

    public Date getStart_date() {
        return this.start_date;
    }

    public Date getExpiration_date() {
        return this.expiration_date;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public void setStart_date(Date start_date) {
        this.start_date = start_date;
    }

    public void setExpiration_date(Date expiration_date) {
        this.expiration_date = expiration_date;
    }
}

2. Define @NamedNativeQuery and mapping for POJO class

import javax.persistence.*;
import java.math.BigDecimal;
import java.util.List;

@Entity
@Table(name = "books")
@NamedNativeQuery(name ="BookDetailsByUser",
        query =
                " SELECT b.title, rb.start_date, rb.expiration_date " +
                " FROM books b INNER JOIN rented_books rb ON rb.book_id = b.id INNER JOIN users u ON u.id = rb.reader_id " +
                " WHERE u.id = ?1 ",
        resultSetMapping = "BookDetailsMapping"
)
@SqlResultSetMapping(name="BookDetailsMapping",
        classes = {
                @ConstructorResult(targetClass = BookDetails.class,
                        columns = {@ColumnResult(name = "title"),
                                   @ColumnResult(name = "start_date"),
                                   @ColumnResult(name = "expiration_date")
                        })
        })
public class Book {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "title")
    private String title;

    @Column(name = "publishing_year")
    private Integer publishingYear;

    @Column(name = "sell_cost")
    private BigDecimal sellCost;

    @Column(name = "rent_cost")
    private BigDecimal rentCost;

    @Column(name = "amount")
    private Integer amount;

//=======Relationships=======

    @ManyToMany(cascade = {CascadeType.DETACH,CascadeType.MERGE,CascadeType.PERSIST,CascadeType.REFRESH,CascadeType.REMOVE})
    @JoinTable(name = "rented_books",
            joinColumns = @JoinColumn(name = "book_id"),
            inverseJoinColumns = @JoinColumn(name = "reader_id"))
    private List<User> usersRented;
}

3. Use named query in repository

@Repository
public interface BookRepository  extends CrudRepository<Book,Long> {
    @Query(name = "BookDetailsByUser", nativeQuery = true)
    Page<BookDetails> findBookDetailsByUser(Long id, Pageable pageable);
}