JPA Query - JOIN Clause

Question

I have two tables Table A Table B

Table A contains

Column X Column Y Column Z Column W

Tables B Contains

Column P Column Q Column R Column W

Column W is common in both the tables.

and their entities

First Entity

@Entity
@Table(name = "A")
@Cache(usage=CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class TableA extends AbstractBaseEntity {

    @Id
    @NotNull
    @Column(name = "X")
    private Long sampleId1;

    @Id
    @NotNull
    @Column(name = "Y")
    private Long sampleId2;

    @Id
    @NotNull
    @Column(name = "Z")
    private Date sampleDate3;

    @ManyToOne(targetEntity = TableB.class)
    @JoinColumn(name = "W")
    private TableB tableB;

    ...
    getter
    setter
    ....
}

Second Entity

@Entity
@Table(name = "TableB")
@Cache(usage=CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class TableB extends AbstractBaseEntity {

    @Id
    @NotNull
    @Column(name = "W")
    private Long sampleId4; 

    @Id
    @NotNull
    @Column(name = "P")
    private Long sampleId1;

    @Id
    @NotNull
    @Column(name = "Q")
    private Long sampleId2;

    @Id
    @NotNull
    @Column(name = "R")
    private Long sampleId3;

    ...
    getter
    setter
    ....
}

I have an Interface where all the queries are written

    Public interface sqlquery{

          String query1 = "from TableA ORDER BY" +
                sampleDate3 asc;";

          String query2= "from TableB";

}

Right now i am fetching all data separately in these query, I need some help in writing a new single query where data should be shown on the basis of same ID i.e SampleId4(column W) using JOIN clause and where. and store the data in result list.

Answer 1

Your mapping doesn't make much sense. If all the columns in table B are part of the ID, that probably means that you might have several rows in it which have the same value in column W. So, if a row in table A has this shared value, it actually references all these rows of table B. So you don't have a ManyToOne association between TableA and TableB.

Regarding your query, since the mapping is wrong in the first place, I don't see how I could write it. End even if it was OK, you should tell us what the query should return, because it's far from being clear.