SQLite: SQL Query from Two Tables One with All Data and other SUM for Related BillNo

SQLite: SQL Query from Two Tables One with All Data and other SUM for Related BillNo
0

#1

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


#2

Please format the code using 3 backticks before and after your code, it looks like you only used one. This is the formatted code beflow, lacking indentation as it doesn’t exist over when I copied it. Or create a gist on github.

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" ));

}

}