CODEWITHSUNDEEP
javaspringspring-data-jpa
Rüdiger
Rüdiger

Reputation: 943

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

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)

Upvotes: 0

Views: 1277

Answers (2)

Andres Rincon
Andres Rincon

Reputation: 409

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;

Upvotes: 2

Bernie
Bernie

Reputation: 2366

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.

Upvotes: 2

Related Questions