Spring Boot: Filter by using array-CodePudding

New to spring here, I was wondering how can I search by multiple genres in the following example. Basically, what I have now returns all content that contains any string in the array of genre, while I want it to provide all content that contains everything in the genre array. For example, let us suppose we have two movies movie1 has two genres drama and comedy, and movie2 has two genres crime and drama. When the array of genre contains comedy and drama I want the query to return only the first movie. The way it's set up now it returns both. Any idea on how to achieve this?

Repository:

@Query(value = "SELECT CONTENTS.ID, CONTENTS.CONTENT_TYPE, CONTENTS.DESCRIPTION, CONTENTS.IMAGE_URL, CONTENTS.TRAILER_URL, CONTENTS.IS_AGE_RESTRICTED, CONTENTS.RELEASE_DATE, CONTENTS.RUNTIME, CONTENTS.SPOKEN_LANGUAGE, CONTENTS.TITLE, CONTENTS.TITLE, GROUP_CONCAT(GENRES.NAME) AS GENRE FROM CONTENTS INNER JOIN GENRES ON CONTENTS.ID=GENRES.CONTENT_ID WHERE GENRE IN (:search) GROUP BY CONTENTS.ID", nativeQuery = true)
List<Content> findContentByGenre(String[] search);

Controller:

@GetMapping("findAllContentByGenres/genre")
public List<Content> getAllContentByGenres(@Param("genre") String[] genre){
    return contentService.getAllContentByGenres(genre);
}

Service:

public List<Content> getAllContentByGenres(String[] genre) {

    return contentRepository.findContentByGenre(genre);
}

Basically, I want to achieve the following logic which is in the code below (php) but in spring boot is there any way to do it? I am open to any suggestions or maybe my logic is flawed since I am not very experienced with spring boot and I am looking at it the wring way. At any rate, any advice is more than welcomed.

$query = "SELECT CONTENTS.ID, CONTENTS.CONTENT_TYPE, CONTENTS.DESCRIPTION, CONTENTS.IMAGE_URL, CONTENTS.TRAILER_URL, CONTENTS.IS_AGE_RESTRICTED, CONTENTS.RELEASE_DATE, CONTENTS.RUNTIME, CONTENTS.SPOKEN_LANGUAGE, CONTENTS.TITLE, CONTENTS.TITLE, GROUP_CONCAT(GENRES.NAME) AS GENRE FROM CONTENTS INNER JOIN GENRES ON CONTENTS.ID=GENRES.CONTENT_ID WHERE";

for ($i = 0; $i <= count($genreArray) - 1; $i  ) {
    $query .= " GENRE LIKE '%$genreArray[$i]%";
    if(!(i = count($genreArray) - 1)){
        $query .= " AND"
    }   
}
$queryFinal = mysqli_query($conn, $query);

CodePudding user response：

You can do it with a native JDBC query, building the SQL string the same way you do in PHP but replacing "$genreArray[$I]" by "?", pass the string to prepareStatement and then loop on the genreArray and call "stmt.setString(i,genreArray[i])". Or with Spring JdbcTemplate, you will be able to pass directly genreArray as the source of parameters since you don't have other: "jdbcTemplate.queryForList(sql, genreArray)".