How to depict joins with @Query annotation in Spring JPA Repository method

Question

I am using Spring-Boot with JPA and a MySQL backend. Now I got quite confused about the repositories Spring-Boot provides. I know these are quite powerful (and seem to be quite useful since they can shorten your code a lot). Still, I do not understand how to represent Joins within them, since the result-set should be a combination of specified attributes in the select of a few Entities.

Now let's assume we have three tables Book, Author, AuthorOfBook, where the last one is simply connecting Book and Author by a combined Primary key. I guess we had the following Java-Classes:

Entity Book:

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

  @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "ID")
  private int id;
  @Column(name = "TITLE")
  private String title;

}

Entity Author

@Entity
@Table(name="AUTHOR")
public class Author {

  @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "ID")
  private int id;
  @Column(name = "LASTNAME")
  private String lastname;
  @Column(name = "FIRSTNAME")
  private String firstname;
  //Let's assume some getters and setters and a constructor
}

Entity AuthorOfBook:

@Entity
@Table(name="BOOK")
public class Book {
  @EmbeddedId
  private AuthorOfBookId pk;
}

An Embedded ID

@Embeddable
public class AuthorOfBookId implements Serializable {
  private int authorId;
  private int bookId;
}

Repository

@Repository
public interface AuthorOfBookRepository extends JpaRepository<,AuthorOfBookId> {

}

Now how would I represent that query:

SELECT b.name, a.firstname, a.lastname from AuthorOfBook ab inner join Book b on b.id = ab.book_id inner join Author a on a.id = ab.author_id where a.lastname = :lastname;

in my repository? I know the signature would need to be like

@Query([the query string from above])
public (...) findAuthorAndBookByAuthorLastname(@Param("lastname") String lastname);

but I cannot make out what Type the return would be like. What is that method returning? (simply AuthorOfBook would not work I guess)

By reading your query it looks like you are looking for term projection or widely known as dto which is covered in this question: https://stackoverflow.com/questions/45401734/how-to-work-with-dto-in-spring-data-rest-projects — daredesm, Dec 11 '18 at 22:36

score 2 · Accepted Answer · answered Dec 11 '18 at 22:08

2

You don't want AuthorOfBook as a separate Entity. Book should have a field of type Author as a @ManyToOne relationship. That way, given any Book, you can find the author's details.

answered Dec 11 '18 at 22:08

Bernie

2,253
1
16
18

Thanks for the answer. So even in my Entity-representation I want to wire the entities with relations and not just represent the database-structure? I'd have the following questions on that: Assuming I have additional information in my relationship-database (like a `created`-timestamp) - would I pick the same structure and let JPA handle that? And how do I handle a third ID in that? So I have `BookId`, `AuthorId` and `CustomerId`? – Rüdiger Dec 11 '18 at 22:15
And one more thing: That would mean for me, that I never want to have ForeignKeys in my `@Entity`? So everytime I would reference a `Foreign-Key-Constraint` I use a direct reference to that other Entity instead? – Rüdiger Dec 11 '18 at 22:18
1

Yes, the relations should be shown in your entities. That is representing your database structure, as typically you would have foreign key constraints in your database, and the joins (@OneToOne, @OneToMany or @ManyToMany) represent those foreign key relationships. A `created` timestamp usually represents the time that the entity (such as `Book`) was created. The relationship between a `Book` and an `Author` would be created at the same time. Customer would be a separate entity again, and would be a @ManyToMany relationship. Please ask a new question if you're unclear on that. – Bernie Dec 11 '18 at 22:24
1

You might have `ForeignKey`s in your entities. They are used for schema generation. – Bernie Dec 11 '18 at 22:26
Thank you for your effort :) As suggested, I tried to realise it with 3 tables that are connected and opened a new question since I ran into problems (can be found [here](https://stackoverflow.com/questions/53733602/how-to-wire-up-three-classes-in-spring-boot-jpa-instead-of-two-with-manytomany). Could you also have a look at that and what I was doing wrong? :) – Rüdiger Dec 12 '18 at 16:07

score 2 · Answer 2 · answered Dec 11 '18 at 22:25

If you want to handle audits fields you can do something like this:

Audit class

@Embeddable
public class Audit {

    @Column(name = "created_on")
    private Timestamp createdOn;

    @Column(name = "updated_on")
    private Timestamp updatedOn;

    @Column(name = "is_deleted")
    private Boolean isDeleted;

    //getters and setters

    }

AuditListener to update automatically audits fields

public class AuditListener {

    private Long loggedUser = 1001L;

    /**
     * Method to set the fields createdOn, and isDeleted when an entity is persisted
     * @param auditable
     */
    @PrePersist
    public void setCreatedOn(Auditable auditable) {
        Audit audit = auditable.getAudit();

        if (audit == null) {
            audit = new Audit();
            auditable.setAudit(audit);
        }

        audit.setIsDeleted(Boolean.FALSE);
        audit.setCreatedOn(Timestamp.from(Instant.now()));
    }

    /**
     * Method to set the fields updatedOn and updatedBy when an entity is updated
     * @param auditable
     */
    @PreUpdate
    public void setUpdatedOn(Auditable auditable) {
        Audit audit = auditable.getAudit();
        audit.setUpdatedOn(Timestamp.from(Instant.now()));
    }
}

And add this to the entities

@EntityListeners(AuditListener.class)
public class Book implements Auditable {

    @Embedded
    private Audit audit;

How to depict joins with @Query annotation in Spring JPA Repository method

2 Answers2