I have an expense report query that produces field totals for each day (designated as column “root” as a 1) and dropdown detail for that day (designated as column “root” as a 2). I can directly fill a table view no problem and can directly fill a treetableview without using FXCollections.observableArrayList(). The problem is I need to use the FXCollections.observableArrayList() since I will be using rowchecker() and cellchecker() that use cellFactory callbacks to check cell value and either change the background of the specific cell OR change the background of the entire row which works when filling a tableview. This is my goal:
Fill a treetableview from database result set; Hide parent and fill treetableview with “root” = 1 with “root” = 2 for each date; change background color of cell/row based on cell value.
package TreeTableView;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.util.Date;
import java.util.ResourceBundle;
import com.jfoenix.controls.RecursiveTreeItem;
import com.jfoenix.controls.datamodels.treetable.RecursiveTreeObject;
import javafx.beans.property.SimpleIntegerProperty;
import javafx.beans.property.SimpleStringProperty;
import javafx.beans.value.ObservableValue;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.scene.control.TreeItem;
import javafx.scene.control.TreeTableColumn;
import javafx.scene.control.TreeTableView;
import javafx.util.Callback;
public class FXMLDocument implements Initializable{
Connection conn = null;
ResultSet rs = null;
PreparedStatement pst = null;
Integer idjobs = 286; //temp
Integer idjobs_by = 6; //temp
Integer loginID = 4;
@FXML
private TreeTableView<ER_Report> tableview;
ObservableList<ER_Report> ER_report = FXCollections.observableArrayList();
@FXML
private TreeTableColumn<ER_Report, Number> col1;
@FXML
private TreeTableColumn<ER_Report, String> col2;
@FXML
private TreeTableColumn<ER_Report, String> col3;
@FXML
private TreeTableColumn<ER_Report, String> col4;
TreeItem<ER_Report> root;
public void dataset() {
String sql = "SELECT * FROM expensereport_total_withlist where job = '" + idjobs + "' and jobby = '" + idjobs_by + "' and employee = '" + loginID + "' "
+ "order by newSort, root";
try {
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
while(rs.next()) {
DecimalFormat df2 = new DecimalFormat("###0.00");
DecimalFormat df3 = new DecimalFormat("###0.000");
Integer c_rootI = 0;
Date c_Date;
Integer c_WorkedDayI = 0;
String c_MealsS = "";
String c_AirfareS = "";
c_rootI = rs.getInt("root");
String c_rootS = c_rootI.toString();
c_Date = rs.getDate("newSort");
String c_DateS = c_Date.toString();
c_WorkedDayI = rs.getInt("workedDay");
String c_WorkedDayS = c_WorkedDayI.toString();
Double c_MealsD = rs.getDouble("Meals");
if(c_MealsD > 0) {
c_MealsS = "$ " + df2.format(c_MealsD) ;
}
Double c_airD = rs.getDouble("Meals");
if(c_airD > 0) {
c_AirfareS = "$ " + df2.format(c_airD) ;
}
ER_report.add(new ER_Report(c_rootI, c_DateS,c_MealsS,c_AirfareS));
}
final TreeItem<ER_Report> root = new RecursiveTreeItem<ER_Report>(ER_report, RecursiveTreeObject::getChildren);
col1.setCellValueFactory(new Callback<TreeTableColumn.CellDataFeatures<ER_Report, Number>, ObservableValue<Number>>() {
@Override
public ObservableValue<Number> call(TreeTableColumn.CellDataFeatures<ER_Report, Number> param) {
return param.getValue().getValue().rootProperty;
}
});
col2.setCellValueFactory(new Callback<TreeTableColumn.CellDataFeatures<ER_Report, String>, ObservableValue<String>>() {
@Override
public ObservableValue<String> call(TreeTableColumn.CellDataFeatures<ER_Report, String> param) {
return param.getValue().getValue().newDateProperty;
}
});
col3.setCellValueFactory(new Callback<TreeTableColumn.CellDataFeatures<ER_Report, String>, ObservableValue<String>>() {
@Override
public ObservableValue<String> call(TreeTableColumn.CellDataFeatures<ER_Report, String> param) {
return param.getValue().getValue().mealsProperty;
}
});
col4.setCellValueFactory(new Callback<TreeTableColumn.CellDataFeatures<ER_Report, String>, ObservableValue<String>>() {
@Override
public ObservableValue<String> call(TreeTableColumn.CellDataFeatures<ER_Report, String> param) {
return param.getValue().getValue().airProperty;
}
});
root.getChildren().setAll(root);
tableview.setRoot(root);
}catch(Exception e) {
e.printStackTrace();
}
root = new TreeItem<>(new ER_Report(0, "Name", "None", "None"));
}
@Override
public void initialize(URL arg0, ResourceBundle arg1) {
conn = connector.ConnectDb();
idjobs = 286; //temp
idjobs_by = 6; //temp
dataset();
}
class ER_Report extends RecursiveTreeObject<ER_Report>{
SimpleIntegerProperty rootProperty;
SimpleStringProperty newDateProperty;
SimpleStringProperty mealsProperty;
SimpleStringProperty airProperty;
public ER_Report(Integer root, String newDate, String meals, String air) {
this.rootProperty = new SimpleIntegerProperty(root);
this.newDateProperty = new SimpleStringProperty(newDate);
this.mealsProperty = new SimpleStringProperty(meals);
this.airProperty = new SimpleStringProperty(air);
}
/**
* @return the rootProperty
*/
public SimpleIntegerProperty getRootProperty() {
return rootProperty;
}
/**
* @param rootProperty the rootProperty to set
*/
public void setRootProperty(SimpleIntegerProperty rootProperty) {
this.rootProperty = rootProperty;
}
/**
* @return the newDateProperty
*/
public SimpleStringProperty getNewDateProperty() {
return newDateProperty;
}
/**
* @param newDateProperty the newDateProperty to set
*/
public void setNewDateProperty(SimpleStringProperty newDateProperty) {
this.newDateProperty = newDateProperty;
}
/**
* @return the mealsProperty
*/
public SimpleStringProperty getMealsProperty() {
return mealsProperty;
}
/**
* @param mealsProperty the mealsProperty to set
*/
public void setMealsProperty(SimpleStringProperty mealsProperty) {
this.mealsProperty = mealsProperty;
}
/**
* @return the airProperty
*/
public SimpleStringProperty getAirProperty() {
return airProperty;
}
/**
* @param airProperty the airProperty to set
*/
public void setAirProperty(SimpleStringProperty airProperty) {
this.airProperty = airProperty;
}
}
}
Next is the result of the query
newSort root Meals Airfare
6/22/2018 1 94.16 756.46
6/22/2018 2 NULL 756.46
6/22/2018 2 38.53 NULL
6/22/2018 2 55.63 NULL
6/27/2018 1 6.92 NULL
6/27/2018 2 3.63 NULL
6/27/2018 2 3.29 NULL
6/29/2018 1 NULL 698.9
6/29/2018 2 NULL 698.9
This is the rowchecker I am using. I currently don't have the column it will be looking at included in the result set but you can see the code I will be using.
public void rowchecker() {
Callback<TableColumn<ExpenseReportTable, Integer>, TableCell<ExpenseReportTable, Integer>> cellFactory
= new Callback<TableColumn<ExpenseReportTable, Integer>, TableCell<ExpenseReportTable, Integer>>() {
public TableCell call(TableColumn p) {
TableCell cell = new TableCell<ExpenseReportTable, Integer>() {
@Override
public void updateItem(Integer item, boolean empty) {
super.updateItem(item, empty);
if (!empty) {
TableRow ttr = getTableRow();
if (item == null || empty){
setText(null);
ttr.setStyle("");
setStyle("");
} else {
ttr.setStyle(item.doubleValue() > 0
? "-fx-background-color:lightgreen"
: "-fx-background-color:#FFE793");
setText(item.toString());
setStyle(item.doubleValue() > 0
? "-fx-background-color:green"
: "-fx-background-color:lightgreen");
}
} else {
setText(null);
}
}
private String getString() {
return getItem() == null ? "" : getItem().toString();
}
};
return cell;
}
};
col_NonWorkedDate.setCellFactory(cellFactory);
}
