I need to get the value from table (Tracker) who has a one to many relation with (Channels) I need to get a value where the name of channel is equal to the one I select the query that I'va done on db is:

SELECT * FROM TBL_TRACKER T JOIN TBL_CHANNEL C ON(C .CHANNEL_ID = T .ARCHIVE_ID) WHERE C.CHANNEL_NAME='777' AND T.PORTAL='PORTALE_TITOLARI' AND T.CAMPAIGN_NAME='blu' AND IDENTIFIER_TYPE='CF' AND DELIVERY_CHANNEL='TTL' AND FLAG='READ' AND ENABLE='TRUE'  AND T.Notification_Category!='SECURITY' AND IDENTIFIER='rob' AND T.EXPIRE_VISUALIZATION_DATE>sysdate

My problem is I can't find a way to do this on JPA, I know about the nativeQuery=true but I'd like to avoid because I need to use the pageable, on My Tracker Entity I have this:

    @Entity
@Table(name = "TBL_TRACKER")
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class Tracker {
    @Id
    @Column(name = "ARCHIVE_ID")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Generated(GenerationTime.ALWAYS)
    private Long archiveId;

    @Column(name = "NOTIFICATION_ID")
    private String notificationId;

    @Column(name = "NOTIFICATION_ID_INDEX")
    private String notificationIdIndex;

    @Column(name = "DELIVERY_CHANNEL")
    @Enumerated(EnumType.STRING)
    private DeliveryChannel deliveryChannel;

    @Column(name = "SENDING_DATE")
    @DateTimeFormat
    private Date sendingDate;

    @Column(name = "SERVICE_ID")
    private String serviceId;

    @Column(name = "IDENTIFIER_TYPE")
    @Enumerated(EnumType.STRING)
    private IdentifierType identifierType;

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

    @Column(name = "PAN")
    private String pan;


    @Column(name = "FLAG")
    @Enumerated(EnumType.STRING)
    private FlagEnum flag;

    @Column(name = "NOTIFICATION_CATEGORY")
    @Enumerated(EnumType.STRING)
    private NotificationCategory notificationCategory;

    @Column(name = "EXPIRING_DATE")
    @DateTimeFormat
    private Date expiringDate;

    @Column(name = "CREATION_DATE")
    @DateTimeFormat
    @CreationTimestamp
    private Date creationDate;

    @Column(name = "VISUALIZATION_DATE")
    @DateTimeFormat
    private Date visualizationDate;

    @Column(name = "EXPIRE_VISUALIZATION_DATE")
    @DateTimeFormat
    private Date expireVisualizationDate;


    @Column(name = "portal")
    @Enumerated(EnumType.STRING)
    private PortalEnum portalEnum;

    @Column(name = "campaign_name")
    private String campaignName;

    @Column(name = "business_priority")
    private int businessPriority;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "tracker")
    @JsonManagedReference
    private List<Channel> channels;
    @Column(name = "Enable")
    private Boolean enabled;

on My Channel I have this:

public class Channel {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Generated(GenerationTime.ALWAYS)
private Long id;

@ManyToOne
@JoinColumn(name = "channel_id")
@JsonBackReference
private Tracker tracker;
@Column(name = "Channel_Name")
private String channelName;

the query I tried to do is this but it didn't work:

  Optional<Tracker> findTrackerByIdentifierAndIdentifierTypeAndDeliveryChannelAndCampaignNameAndPortalEnumAndChannels(String identifier, IdentifierType identifierType, DeliveryChannel deliveryChannel, String campaign, PortalEnum portal, Channel channels);

In my service side I'm trying to apply this query like this:

     public Tracker deleteTracker(String identifier, String campaignName, String channel, String portal) throws Exception {
        log.info("Deleting TTL with values: identifier: {} campaignName: {} channel: {} portal: {}", identifier, campaignName, channel, portal);
        Channel channelOBJ = new Channel();
        channelOBJ.setChannelName(channel);
  return trackerRepo.findTrackerByIdentifierAndIdentifierTypeAndDeliveryChannelAndCampaignNameAndPortalEnumAndChannels
                (identifier, IdentifierType.CF, DeliveryChannel.TTL, campaignName, PortalEnum.valueOf(portal.toUpperCase()), channelOBJ).map(
                tracker -> {
                    tracker.setEnabled(false);
                    trackerRepo.save(tracker);
                    return tracker;
                }
        ).orElseThrow(
                () -> new Exception(String.format("Tracker with identifier: %s campaignName: %s channel: %s portal: %s was not found",
                        identifier, campaignName, channel, portal)));
    }

The error that I got is this:

org.hibernate.TransientObjectException: object references an unsaved transient instance - save the transient instance before flushing: com.example.NetflixProve.model.Channel

Somebody has any idea? If something isn't clear I can explain better

CodePudding user response：

Try with the @Query annotation without the nativeQuery filter. You are refer to your entities in the query.

Something like this :

@Query(value="select T from Tracker T where  T.channel.id=:CHANNEL_ID")
Page<Tracker> getTrackers(@Param("CHANNEL_ID") Long channelId, Pageable pageable);

This can be used with Pageable. Extend this example to include additional parameters and filters as needed.

CodePudding user response：

I solved by using the nativeQuery of sql and create a PageRequest after I fetched the results, even if I don't like to use the nativeQuery