Query a list with JPARepository

Question

I need to get the exact values who match with my query but with my query it returns more values (who also contains my list) and return even two value of the same rows: the query that I do is this:

 List<Archive> findAllByIdentifierAndChannelsChannelNameIn(String identifier, List<String> channel);

my model class is this:

public class Archive {
    @Id
    @Column(name = "ARCHIVE_ID")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Generated(GenerationTime.ALWAYS)
    private Long archiveId;

    @Column(name = "IDENTIFIER")
    private String identifier;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "archive")
    @JsonManagedReference
    private Set<Channel> channels;

}
public class Channel{
     @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Generated(GenerationTime.ALWAYS)
        private Long channelId;
    
        @ManyToOne
        @JoinColumn(name = "archive_id")
        @JsonBackReference
        private Archive archive;
        @Column(name = "Channel_Name")
        private String channelName;
    }

The problem is when I pass these body:

{
"identifier": "NGLCRS97D12G866L",
"channels": ["Sky news","Rai 4"]
}

It give me back this:

"archiveId": 24,
        "identifier": "NGLCRS97D12G866L",
        "channels": [
            {
                "channelId": 20,
                "channelName": "Sky news"
            }
        ]
    },
    {
        "archiveId": 9,
        "identifier": "NGLCRS97D12G866L",
        "channels": [
            {
                "channelId": 2,
                "channelName": "Rai 4"
            },
            {
                "channelId": 40,
                "channelName": "Sky news"
            }
        ]
    },
    {
        "archiveId": 9,
        "identifier": "NGLCRS97D12G866L",
        "channels": [
            {
                "channelId": 2,
                "channelName": "Rai 4"
            },
            {
                "channelId": 40,
                "channelName": "Sky news"
            }
        ]
    },
    {
        "archiveId": 25,
        "identifier": "NGLCRS97D12G866L",
        "channels": [
            {
                "channelId": 41,
                "channelName": "Sky news"
            },
            {
                "channelId": 1,
                "channelName": "Boing"
            }
        ]
    },
    {
        "archiveId": 8,
        "identifier": "NGLCRS97D12G866L",
        "portal": "PORTALE_TITOLARI",
        "channels": [
            {
                "channelId": 39,
                "channelName": "Sky news"
            }
        ]
    }

As you can see it will give me back 2 value og the same row (archiveId: 9) but I need to get the exact value when I pass more channels to match what I want beacuse I need to use in a delete. thanks to all. Even if I'm going to use the native query I don't know how to write the right query to get just the values that I need it

Answer 1

Solved By adding this to my query:

 @Query(value = "Select * from tbl_archive A where a.archive_id IN (select A.ARCHIVE_ID FROM tbl_archive A JOIN tbl_channel C ON(C.ARCHIVE_ID =A.ARCHIVE_ID)" +
        "WHERE a.identifier= :Identifier AND c.channel_name IN :channels group by A.ARCHIVE_ID" +
        " having count(c.ARCHIVE_ID) = :channelSize)", nativeQuery = true)

if somebody has a better solution please let me know

Answer 2

For more complex queries I recommend using @Query instead of JPA repository. You can write it in several ways. First option is writing a native query.

@Query(value = "select * from table where something = :variableName", nativeQuery = true);
public List<MyClass> myQuery(@Param("variableName") String 
variable);

Second option is writing a simple non native Query and use your Entiry class names and fields. For this you purpose you can use javax.persistence.EntityManager or @Query(value = "", nativeQuery = false). By default @Query is non native query, you dont have to write nativeQuery = false

//@Autowired Constructor dependency injection is more preferred instead
@Autowired
private EntityManager entityManager;

List<MyClass> query = entityManager.createQuery("SELECT new 
MyClass(m.id, m.name) from MyClass m where m.name = :variableName", ValidDomain.class).getResultList();

You don't have to use EntityManager you can also write non native Query with the @Query annotation as I mentioned above.