Hi All,
I have Two Tables
Bill
TRX
i am trying to List all the data from Table Bill and its Total from Table TRX. the Common Key is “BillNo” in Both Tables.
Trying to List it in a TableView.
Below is what i have done so far which is not working.
OrderListModel.java which contains the Constructor and Getter&Setter as follows;
public class OrderListModel {
private Integer Nbr;
private String Status;
private String Date;
private Integer BillNbr;
private Integer Cif;
private String Customer;
private Integer Mobile;
private Double BillValue;
OrderListController.java which has the related code to display data in TableView;
public class OrderListController implements Initializable {
@FXML private TableView<OrderListModel> OrderListTable;
@FXML private TableColumn<?, Integer > nbr;
@FXML private TableColumn<?, ?> status;
@FXML private TableColumn<?, ?> dtd;
@FXML private TableColumn<?, ?> billnbr;
@FXML private TableColumn<?, ?> cif;
@FXML private TableColumn<?, ?> customer;
@FXML private TableColumn<?, ?> mobile;
@FXML private TableColumn<?, ?> billvalue;
Connection connect;
private PreparedStatement pst = null ;
private ResultSet rs = null ;
private ObservableList<OrderListModel> oblist;
@Override
public void initialize(URL url, ResourceBundle rb) {
oblist = FXCollections.observableArrayList();
loadTableView();
getOrderListTotal();
}
public void getOrderListTotal(){
try (Connection connect = SqliteConnection.Connector();)
{
pst = connect.prepareStatement( "SELECT Bill.Status, Bill.dtd, Bill.BillNo, Bill.CIF, Bill.CustomerName, Bill.Mobile, TRX.SUM(Price) FROM Bill INNER JOIN TRX ON Bill.BillNo = TRX.BillNo" );
rs = pst.executeQuery();
while (rs.next()){
oblist.add( new OrderListModel(rs.getString( "Status" ),rs.getString( "dtd" ),rs.getInt( "BillNo" ),rs.getInt( "CIF" ),rs.getString( "CustomerName" ),rs.getInt( "Mobile" ),rs.getDouble( "TRX.SUM(Price)" )));
}
}
catch (SQLException ex)
{
Logger.getLogger(InvoiceContoller. class .getName()).log(Level.SEVERE, null , ex);
}
OrderListTable.setItems(oblist);
}
public void loadTableView(){
nbr.setCellValueFactory(nbr-> new ReadOnlyObjectWrapper<>(OrderListTable.getItems().indexOf(nbr.getValue())+ 1 ));
status.setCellValueFactory( new PropertyValueFactory<>( "Status" ));
dtd.setCellValueFactory( new PropertyValueFactory<>( "Date" ));
billnbr.setCellValueFactory( new PropertyValueFactory<>( "BillNbr" ));
cif.setCellValueFactory( new PropertyValueFactory<>( "Cif" ));
customer.setCellValueFactory( new PropertyValueFactory<>( "Customer" ));
mobile.setCellValueFactory( new PropertyValueFactory<>( "Mobile" ));
billvalue.setCellValueFactory( new PropertyValueFactory<>( "BillValue" ));
}
}
Please kindly someone help me.
Thanks
