Find parent element by searching child / Spring boot JPA + PostgresSQL

Question

I am trying to figure out how to find and return all Books that are associated with a specific author name.

{
    "bookId": 5,
    "bookName": "test2",
    "publishYear": 2022,
    "publisher": "test2",
    "authors": [
        {
            "id": 5,
            "name": "Heny",
            "surname": "Blakc"
        },
        {
            "id": 6,
            "name": "Garyy",
            "surname": "Harrys"
        }
    ]
}

I want to return all books where Garyy is the author.

I am using Spring Boot + Postgres SQL OneToMany annotation.

Would appreciate any suggestions.

AuthorController:

package com.example.demo.controller;


import com.example.demo.entity.AuthorEntity;
import com.example.demo.repository.AuthorRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/author")
public class AuthorController {
    @Autowired
    AuthorRepository authorRepository;

    @GetMapping("/all")
    public List<AuthorEntity> author(){
        return authorRepository.findAll();
    }
    @PostMapping("/create")
    public AuthorEntity createAuthor(@RequestBody AuthorEntity author){
        AuthorEntity savedAuthor = authorRepository.save(author);
        return savedAuthor;
    }
    @GetMapping("/find/{author}")
    public List<AuthorEntity> findAuthor(@PathVariable(value = "author") String name){
        return authorRepository.findByName(name);
    }

}

BookController:

package com.example.demo.controller;


import com.example.demo.entity.BookEntity;
import com.example.demo.repository.BookRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;


@RestController
@RequestMapping("/book")
public class BookController {


    @Autowired
    private BookRepository bookRepository;




    @GetMapping("/all")
    public List<BookEntity> getAllBooks(){
        List<BookEntity> allBookList = bookRepository.findAll();

        return allBookList;
    }

    @GetMapping("/findBook/{name}")
    public List<BookEntity> getBookByName(@PathVariable(value = "name")String bookName)
    {
        return bookRepository.findByBookName(bookName);
    }

    @GetMapping("/year/{year}")
    public List<BookEntity> getBookByYear(@PathVariable(value = "year")Integer year)
    {
        return bookRepository.findByPublishYear(year);
    }
    @GetMapping("/publisher/{publisher}")
    public List<BookEntity> getBookByPublisher(@PathVariable(value = "publisher")String publisher)
    {
        return bookRepository.findByPublisher(publisher);
    }

    @PostMapping("/create-book")
    public BookEntity createBook (@RequestBody BookEntity book){
        BookEntity savedBook = bookRepository.save(book);

        return savedBook;
    }


}

AuthorEntity:

package com.example.demo.entity;

import javax.persistence.*;

@Entity
@Table(name = "authors")
public class AuthorEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(name = "name", nullable = false)
    private String name;
    @Column(name = "surname",nullable = false)
    private String surname;

    @ManyToOne(fetch =FetchType.LAZY)
    private BookEntity books;



    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getSurname() {
        return surname;
    }

    public void setSurname(String surname) {
        this.surname = surname;
    }

    @Override
    public String toString() {
        return "AuthorEntity{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", surname='" + surname + '\'' +
                ", books=" + books +
                '}';
    }

}

BookEntity:

package com.example.demo.entity;

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

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

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)

    private Integer bookId;

    @Column(name = "book_name", nullable = false)
    private String bookName;

    @Column(name = "publish_year",nullable = false)
    private Integer publishYear;

    @Column(name = "publisher",nullable = false)
    private String publisher;

    @OneToMany(targetEntity = AuthorEntity.class, cascade = CascadeType.ALL)
    private List<AuthorEntity> authors;


    public List<AuthorEntity> getAuthors() {
        return authors;
    }

    public void setAuthors(List<AuthorEntity> authors) {
        this.authors = authors;
    }

    public Integer getBookId() {
        return bookId;
    }

    public void setBookId(Integer bookId) {
        this.bookId = bookId;
    }

    public String getBookName() {
        return bookName;
    }

    public void setBookName(String bookName) {
        this.bookName = bookName;
    }

    public Integer getPublishYear() {
        return publishYear;
    }

    public void setPublishYear(Integer publishYear) {
        this.publishYear = publishYear;
    }

    public String getPublisher() {
        return publisher;
    }

    public void setPublisher(String publisher) {
        this.publisher = publisher;
    }

    @Override
    public String toString() {
        return "BookEntity{" +
                "bookId=" + bookId +
                ", bookName='" + bookName + '\'' +
                ", publishYear=" + publishYear +
                ", publisher='" + publisher + '\'' +
                ", authors=" + authors +
                '}';
    }
}

AuthorRepository:

package com.example.demo.repository;

import com.example.demo.entity.AuthorEntity;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

public interface AuthorRepository extends JpaRepository<AuthorEntity, Integer> {
    List<AuthorEntity> findByName(String name);
}

BookRepository:

package com.example.demo.repository;

import com.example.demo.entity.BookEntity;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

public interface BookRepository extends JpaRepository<BookEntity, Integer> {
    public List<BookEntity> findByBookName(String bookName);
    public List<BookEntity> findByPublishYear(Integer year);
    public List<BookEntity> findByPublisher(String publisher);
}

manikant gautam · Answer 1 · 2020-11-15T14:30:26.620

1

There are multiple way to do so , one approach is to use forEach and filter in java itself like

 List<BookeEntity> books = getBooks();
 books.forEach(e->{
        List<AuthorEntity> al= e.getAuthors().stream().filter(ee->ee.getName().equals("Garyy")).collect(Collectors.toList());
        e.setAuthors(al);
    });

edited Nov 15 '20 at 14:30

answered Nov 15 '20 at 14:07

manikant gautam

3,521
1
17
27

Thank you for your suggestion, how can I use to return a list of books? I tried to use it within the List method and return a list of books but this method return void only. – Alohas Nov 15 '20 at 15:31
i didn't found any `but this method return void only` – manikant gautam Nov 16 '20 at 13:58
Doesn't `getBooks()` still generate SQL to fetch all books, and then only locally you're filtering? – Azhari Mar 30 '23 at 15:07

score -1 · Answer 2 · answered Nov 15 '20 at 15:47

Your database design was wrong. Reality:

1 author wrote one or many books,
1 book wrote by one or many authors.

Therefore, you need an table author-book to convert relationship many-many to 2 relationships: one-many, many-one.

You can see https://www.stat.auckland.ac.nz/~paul/ItDT/HTML/node42.html section 5.6.3.2 Relationships

Find parent element by searching child / Spring boot JPA + PostgresSQL

2 Answers2