Java Spring Boot, Repository method for getting all entity by value from Array

Question

I have Book entity and book repository. Book has string array with genres. I need to implement findAllByGenre method for searching all books which will be includes passed "genre" at their array. How it will be better to implements? Book Entity Book Repository

I tried to create method which will be returns List of books, but can't understood how should I pass params and do select with string

UPDATED: I've tried to implement Repository method like that

@Repository
public interface BookRepository extends CrudRepository<BookEntity, Long> {
        BookEntity findOneBySlug(String slug);
    
        @Query("SELECT b FROM book_entity WHERE :genre in b.genre")
        List<BookEntity> findAllByGenre(@Param("genre") String genre);
}

Also I'm sharing BookEntity code

package com.uabookserver.uabookserver.entity;

import jakarta.persistence.*;
import org.springframework.lang.NonNull;

    @Entity
    public class BookEntity {
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
        @NonNull
        private String slug;
        private String title;
        private String author;
        private String country;
        private int year;
        private String[] genre;
        private int likes = 0;
        private int bookmarks = 0;
        private int downloads = 0;
        @Lob
        private String image;
        @Lob
        private String[] file;
    
        public BookEntity() {}
    
        public Long getId() {
          return id;
        }
    
        public void setId(Long id) {
          this.id = id;
        }
    
        public String getSlug() {
          return slug;
        }
    
        public void setSlug(String slug) {
          this.slug = slug;
        }
    
        public String getTitle() {
          return title;
        }
    
        public void setTitle(String title) {
          this.title = title;
        }
    
        public String getAuthor() {
          return author;
        }
    
        public void setAuthor(String author) {
          this.author = author;
        }
    
        public int getYear() {
          return year;
        }
    
        public void setYear(int year) {
          this.year = year;
        }
    
        public String[] getGenre() {
          return genre;
        }
    
        public void setGenre(String[] genre) {
          this.genre = genre;
        }
    
        public int getLikes() {
          return likes;
        }
    
        public void setLikes(int likes) {
          this.likes = likes;
        }
    
        public int getBookmarks() {
          return bookmarks;
        }
    
        public void setBookmarks(int bookmarks) {
          this.bookmarks = bookmarks;
        }
    
        public String getCountry() {
          return country;
        }
    
        public void setCountry(String country) {
          this.country = country;
        }
    
        public int getDownloads() {
          return downloads;
        }
    
        public void setDownloads(int downloads) {
          this.downloads = downloads;
        }
    
        public String getImage() {
          return image;
        }
    
        public void setImage(String image) {
          this.image = image;
        }
    
        public String[] getFile() {
          return file;
        }
    
        public void setFile(String[] file) {
          this.file = file;
        }
    }

And BokkService method for getting all books by genre

public List<Book> getAllByGenre(String genre) {
        List<BookEntity> books = bookRepository.findAllByGenre(genre);
        List<Book> models = this.convertToModelsList(books);
        return models;
    }

Most people won't look at your screenshots... Because if they try to solve your problem, they need to write a complete entity class by hand according to your screenshot. — 时间只会一直走, Jun 25 '23 at 07:54
Hello Vlad, please don't share code as picture but paste it as formatted code. Please also tell us what kind of error are you getting. — Mar-Z, Jun 25 '23 at 07:57
Thanks for your comment,I've updated my question with code example — Vlad Skoromnyi, Jun 25 '23 at 10:07
@Query("SELECT b.* FROM BookEntity b WHERE b.genre = :genre") List findAllByGenre(@Param("genre") String genre); — Awais Latif, Jun 25 '23 at 12:38

score 0 · Answer 1 · answered Jun 25 '23 at 07:21

0

The beauty of JPA is it automatically generates queries internally. In your case, you already passing the necessary parameters as array of genre. This ideally will work and JPA will do the heavy lifting for you.

In case you want to define a custom SQL query by yourself do refer the following read:

Is it possible to use an Array object as a parameter in Spring Repository @Query annotation?

Hope this helps.

answered Jun 25 '23 at 07:21

lib

21
4

@Vlad Please care to upvote or accept if this answer helped. – lib Jun 26 '23 at 05:11

score 0 · Answer 2 · edited Jun 27 '23 at 15:15

0

It's better to be this way, first, create a Genre class:

public class Genre {
    Long id;
    String title;
    String description;
}

and Book class:

public class Book {
    Long id;
    String title;
    String author;
    .
    .
    .
    @OneToMany
    List<Genre> genres;
}

now you can get Book entities using a query; read this to create query: SQL query one to many relationship

edited Jun 27 '23 at 15:15

william xyz

710
5
19

answered Jun 25 '23 at 10:40

Ali Booresh

53
1
7

score 0 · Answer 3 · answered Jun 25 '23 at 11:01

Thanks for you suggestions, I've found solution with Native Query That is working for me This is code of BookRepository method for getting all books by genre

 @Query(value = "SELECT * FROM book_entity WHERE genre LIKE %:genre%", nativeQuery = true)
    List<BookEntity> findAllByGenre(@Param("genre") String genre);

Java Spring Boot, Repository method for getting all entity by value from Array

3 Answers3