JPAReposiry fetch all the object instead the one I want-CodePudding

I need to get just where the channel is true (1) from sql but when I do the same query on JPARepository it fetch all the rows from the many table my query on sql is this: :

select * FROM tbl_archive A JOIN tbl_channel C ON(C.ARCHIVE_ID =A.ARCHIVE_ID)
WHERE a.identifier='NGLCRS97D12G866L' AND c.ENABLED_CHANNEL=1

and I get this result (I wanna this one):

when I try to do this on JPARepository:

    @Query(value = "select * FROM TBL_ARCHIVE A JOIN TBL_CHANNEL C ON(C.ARCHIVE_ID =A.ARCHIVE_ID) "  
            "WHERE a.identifier= :identifier AND c.ENABLED_CHANNEL=1", nativeQuery = true)
    Page<Archive> findChannelEnabled(@Param("identifier") String identifier, Pageable pageable);

I get this in the json Result:

but I don't need where is false. I also tried this queries too but nothing changed, when fetch the result it will fetch all the channels (many) who has a relation with Archive (one)

    Page<Archive> findByIdentifierAndChannelsEnabledChannelTrue(String identifier, Pageable pageable);

My Models are these:

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;

    @Column(name = "ENABLED_CHANNEL")
    private Boolean enabledChannel;
}

My service:

public Page<Archive> getChannelEnabled(String identifier) {
        return archiveRepo.findChannelEnabled(identifier, PageRequest.of(0, 10));
    }

CodePudding user response：

You can do this by modifying your repository method like this:

@Query(value = "select a FROM Archive a join fetch a.channels c where a.identifier=:identifier and c.enabledChannel=1",
            countQuery = " select count(a) from Archive a left join a.channels where a.identifier=:identifier")
Page<Archive> findChannelEnabled(@Param("identifier") String identifier, Pageable pageable);

CodePudding user response：

Try to use NumericBooleanType which, according to the definition, maps between INTEGER and Boolean (using 1 and 0).

@Column(name = "ENABLED_CHANNEL")
@Type(type = "org.hibernate.type.NumericBooleanType")
private Boolean enabledChannel;