Query a list with JPARepository-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

You should add @JsonIgnoreProperties("archive") instead of @JsonManagedReference in Archive class where you are mapping and use @JsonIgnoreProperties("channel") instead of @JsonBackReference in Channel class. Further you can add @JsonIgnoreProperties({"hibernateLazyInitializer", "handler"}) this annotation for your entity class. Hope so this will solve your problem. You can also visit this link. How to solve duplication in a JSON response of a Spring JPA Rest API with a @OneToMany relationship.

CodePudding user response：

Use Distinct keyword for getting the unique values

Refer the below code

@Query(value="select distinct * from table_name where something = ?1 and something IN ?2",nativeQuery=true)
List<Archive> findByIdentifierAndChannelsChannelNameIn(String identifier, List<String> channel);