JSF Inner Joins on Datatable

[thumper_ipw]

Good Morning,

I am trying to display a table in JSF that displays information Joined with another table, but cannot work out how to display it.

I have one table (parts) which contains information: partNo, ManPrtNo, Description and supplier. I wish to join it to another table called partsBuild) which contains information: rowID, partNo, etc etc.

I would like the displayed table (PartBuild) to show the ManPrtNo, Description and Supplier when partNo is the same in both of the tables parts and PartBuild. How would I go about doing this?

Any help would be greatly appreciated!!!

Parts.java

@Entity
@Table(name = "parts")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "Parts.findAll", query = "SELECT p FROM Parts p"),
    @NamedQuery(name = "Parts.findByPartsNo", query = "SELECT p FROM Parts p WHERE p.partsNo = :partsNo"),
    @NamedQuery(name = "Parts.findByManPrtNo", query = "SELECT p FROM Parts p WHERE p.manPrtNo = :manPrtNo"),
    @NamedQuery(name = "Parts.findByDescription", query = "SELECT p FROM Parts p WHERE p.description = :description"),
    @NamedQuery(name = "Parts.findBySupplier", query = "SELECT p FROM Parts p WHERE p.supplier = :supplier")})
public class Parts implements Serializable {
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "partsNo")
    private Collection<PartBuild> partBuildCollection;
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 50)
    @Column(name = "parts_no")
    private String partsNo;
    @Size(max = 50)
    @Column(name = "man_prt_no")
    private String manPrtNo;
    @Size(max = 100)
    @Column(name = "description")
    private String description;
    @Size(max = 50)
    @Column(name = "supplier")
    private String supplier;

    public Parts() {
    }

    public Parts(String partsNo) {
        this.partsNo = partsNo;
    }
.
.
.

PartBuild.java

@Entity
@Table(name = "partBuild")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "PartBuild.findAll", query = "SELECT p FROM PartBuild p"),
    @NamedQuery(name = "PartBuild.findByRowId", query = "SELECT p FROM PartBuild p WHERE p.rowId = :rowId"),
    @NamedQuery(name = "PartBuild.findBySuffix", query = "SELECT p FROM PartBuild p WHERE p.suffix = :suffix"),
    @NamedQuery(name = "PartBuild.findByResponsibility", query = "SELECT p FROM PartBuild p WHERE p.responsibility = :responsibility"),
    @NamedQuery(name = "PartBuild.findByPendingTVDC", query = "SELECT p FROM PartBuild p WHERE p.pendingTVDC = :pendingTVDC"),
    @NamedQuery(name = "PartBuild.findByApproved", query = "SELECT p FROM PartBuild p WHERE p.approved = :approved"),
    @NamedQuery(name = "PartBuild.findByPlannedApprovalDate", query = "SELECT p FROM PartBuild p WHERE p.plannedApprovalDate = :plannedApprovalDate"),
    @NamedQuery(name = "PartBuild.findByActualApprovalDate", query = "SELECT p FROM PartBuild p WHERE p.actualApprovalDate = :actualApprovalDate"),
    @NamedQuery(name = "PartBuild.findByPlannedIssueDate", query = "SELECT p FROM PartBuild p WHERE p.plannedIssueDate = :plannedIssueDate"),
    @NamedQuery(name = "PartBuild.findByActualIssueDate", query = "SELECT p FROM PartBuild p WHERE p.actualIssueDate = :actualIssueDate"),
    @NamedQuery(name = "PartBuild.findByDesignCharger", query = "SELECT p FROM PartBuild p WHERE p.designCharger = :designCharger"),
    @NamedQuery(name = "PartBuild.findByRiskGradeRank", query = "SELECT p FROM PartBuild p WHERE p.riskGradeRank = :riskGradeRank"),
    @NamedQuery(name = "PartBuild.findBySchematicNo", query = "SELECT p FROM PartBuild p WHERE p.schematicNo = :schematicNo"),
    @NamedQuery(name = "PartBuild.findByPcb", query = "SELECT p FROM PartBuild p WHERE p.pcb = :pcb"),
    @NamedQuery(name = "PartBuild.findByQty", query = "Select p FROM PartBuild p WHERE p.qty = :qty")})
public class PartBuild implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    //@GeneratedValue(strategy = GenerationType.SEQUENCE)
    @Basic(optional = false)
   // @NotNull
    @GeneratedValue(strategy=GenerationType.SEQUENCE,generator="code_caisse_SEQ") 
    @SequenceGenerator(name="code_caisse_SEQ",sequenceName="code_caisse_SEQ", allocationSize=1) 
    @Column(name = "row_id")
    //@NotNull
    private Integer rowId;
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 20)
    @Column(name = "suffix")
    private String suffix;
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 10)
    @Column(name = "responsibility")
    private String responsibility;
    @Basic(optional = false)
    @NotNull
    @Column(name = "pendingTVDC")
    private boolean pendingTVDC;
    @Basic(optional = false)
    @NotNull
    @Column(name = "qty")
    private Integer qty;
    @Basic(optional = false)
    @NotNull
    @Column(name = "approved")
    private boolean approved;
    @Column(name = "plannedApprovalDate")
    @Temporal(TemporalType.DATE)
    private Date plannedApprovalDate;
    @Column(name = "actualApprovalDate")
    @Temporal(TemporalType.DATE)
    private Date actualApprovalDate;
    @Column(name = "plannedIssueDate")
    @Temporal(TemporalType.DATE)
    private Date plannedIssueDate;
    @Column(name = "actualIssueDate")
    @Temporal(TemporalType.DATE)
    private Date actualIssueDate;
    @Size(max = 50)
    @Column(name = "designCharger")
    private String designCharger;
    @Size(max = 5)
    @Column(name = "riskGradeRank")
    private String riskGradeRank;
    @Size(max = 50)
    @Column(name = "schematicNo")
    private String schematicNo;
    @Size(max = 2)
    @Column(name = "pcb")
    private String pcb;
    @JoinColumn(name = "model_no", referencedColumnName = "model_no")
    @ManyToOne
    private ModelMatrix modelNo;
    @JoinColumn(name = "chassis_no", referencedColumnName = "chassis_no")
    @ManyToOne(optional = false)
    private Chassis chassisNo;
    @JoinColumn(name = "parts_no", referencedColumnName = "parts_no")
    @ManyToOne(optional = false)
    private Parts partsNo;
  
    
    
    public PartBuild() {
    }

    public PartBuild(Integer rowId) {
        this.rowId = rowId;
    }

    public PartBuild(Integer rowId, String suffix, String responsibility, boolean pendingTVDC, boolean approved, Integer qty) {
        this.rowId = rowId;
        this.suffix = suffix;
        this.responsibility = responsibility;
        this.pendingTVDC = pendingTVDC;
        this.approved = approved;
        this.qty = qty;
    }

  
    
    public Integer getRowId() {
        return rowId;
    }

    
    public void setRowId(Integer rowId) {
        this.rowId = rowId;
    }
.
.
.

Thanks

[thumper_ipw]

Sorted :)

The way I was thinking was the wrong way to go about it - Join the tables in JSF.

What I did was create the JOin in the database and create the pages to view the finished join. :) Happy days!

One problem was the lack of an ID for the table so i created on like so:

@Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE,generator="code_caisse_SEQ") 
    @SequenceGenerator(name="code_caisse_SEQ",sequenceName="code_caisse_SEQ", allocationSize=1) 
    @Column(name = "'#'")
    private String id;