digistone:

Hope someone can enlighten me; this is my first foray into JPA, and I'm attempting to reverse-engineer entity relationships based on an Oracle SQL query. Here is the query:

select n.incoming, n.outgoing, c.claim_id, c.claim_label, c.claim_date, c.claim_status
from CLAIM c
    left join PERSON p on p.person_id = c.person_id
      left join NMI_VIEW n on n.claim_id = c.claim_id
            and n.person_identifier = p.person_identifier
;

Basically, the PERSON table is acting as an intermediary table in this query, but not guaranteed to have a match on the c.person_id column. And I only want to get a row from the NMI_VIEW view if it has a match for both the claim_id and person_identifier. So the NMI_VIEW join is based on 2 columns from 2 different tables.

So my question is, is it possible (and how) to get JPA projections/entities to produce this type of query? I will list the basic pieces of the current classes I have below.

NmiViewProjection:

@Data
@Immutable
@Entity
@Table(name = "NMI_VIEW")
public class NmiViewEntity {
    @Column(name = "CLAIM_ID")
    private Long claimId;

    @Column(name="PERSONAL_IDENTIFIER")
    private String personalIdentifier;

    // ??? This is either a 1 or 0; can I make it a Boolean instead of Integer??
    @Column(name = "OUTGOING")
    private Integer outgoing;

    // ??? This is either a 1 or 0; can I make it a Boolean instead of Integer??
    @Column(name = "INCOMING")
    private Integer incoming;

    @Column(name = "SINGLE_STATUS")
    private Integer singleStatus;
}

PersonProjection:

@Data
@Entity
@Immutable
@EqualsAndHashCode(of = "personId")
@ToString(of = "personId")
@Table(name = "PERSON")
public class PersonProjection {

    public PersonProjection() {
        // JPA
    }

    @Id
    @Column(name = "PERSON_ID")
    private Long personId;

    @Column(name = "PERSON_IDENTIFIER")
    private String personIdentifier;

    @Column(name = "FIRST_NAME")
    private String firstName;

    @Column(name = "MIDDLE_NAME")
    private String middleName;

    @Column(name = "LAST_NAME")
    private String lastName;

    @Column(name = "SSN")
    private String ssn;

    @Column(name = "DATE_OF_BIRTH")
    private Date dateOfBirth;

}

ClaimProjection:

@NamedEntityGraph(name = "ClaimProjection.graph",
        attributeNodes = {
                @NamedAttributeNode(value = "personProjection"),
                @NamedAttributeNode(value = "nmiViewEntity")})
@Data
@Entity
@Immutable
@EqualsAndHashCode(of = "claimId")
@ToString(of = "claimId")
@Table(name = "CLAIM")
public class ClaimProjection {

    public ClaimProjection() {
        // JPA
    }

    @Id
    @Column(name = "CLAIM_ID")
    private Long claimId;

    @Column(name = "CLAIM_LABEL")
    private String claimLabel;

    @Column(name = "CLAIM_DATE")
    @Temporal(TemporalType.TIMESTAMP)
    private Date claimDate;

    @Column(name = "CLAIM_STATUS")
    private String claimStatus;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "PERSON_ID")
    @NotFound(action = NotFoundAction.IGNORE)
    private PersonProjection personProjection;

    // TODO Do not know how to properly join NMI_VIEW here!!
    @OneToOne(fetch = FetchType.Lazy)
    @LazyToOne(LazyToOneOption.NO_PROXY)
    @JoinColumns({
        @JoinColumn(name = "CLAIM_ID", insertable = false, updatable = false),
        @JoinColumn(name = "PersonEntity.PERSON_ID", insertable = false, updatable = false)
    })
    @NotFound(action = NotFoundAction.IGNORE)
    private NmiViewEntity nmiViewEntity;

}

I kind of inherited this code, and put some annotations on ClaimProjection.nmiViewEntity as a best guess on how to perform the join, but I have a feeling they're not going to work. If anyone has any ideas on how to do this join correctly, and perhaps any corrections to anything else here, I'd really appreciate it! Thanks in advance!

Edit 1: I have tested these classes as-is and it currently does not work, in part because there is not a proper composite ID on the NmiViewEntity. (Not sure if I should use a composite ID class or embeddable ID class here?)