使用FXCollections.observableArrayList()从数据库填充树表视图

Question

我有一个支出报告查询，它生成每天的字段总数(指定为列"root"作为1(和当天的下拉详细信息(指定为"root"列作为2(。我可以直接填充表视图，没有问题，也可以在不使用FXCollections.observableArrayList()的情况下直接填充树表视图。问题是我需要使用FXCollections.observableArrayList()，因为我将使用rowchecker()和cellchecker()，它们使用cellFactory回调来检查单元格值，或者更改特定单元格的背景，或者更改填充表视图时工作的整行的背景。这是我的目标：从数据库结果集中填充树表视图；隐藏父项，并为每个日期用“root” = 1和“root” = 2填充树表视图；根据单元格值更改单元格/行的背景色。

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;
}

}
}

接下来是查询的结果

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

这是我正在使用的行检查器。我目前没有将要查看的列包含在结果集中，但您可以看到我将使用的代码。

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

Answer 1

我能够按照自己的意愿找到它的工作方式。下面是代码。

package TreeTableView;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.Date;
import java.util.ResourceBundle;
import javafx.application.Platform;
import javafx.beans.property.ReadOnlyObjectWrapper;
import javafx.beans.property.SimpleIntegerProperty;
import javafx.beans.property.SimpleStringProperty;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.scene.control.Alert;
import javafx.scene.control.TreeItem;
import javafx.scene.control.TreeTableCell;
import javafx.scene.control.TreeTableColumn;
import javafx.scene.control.TreeTableRow;
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;
Integer idjobs_by;
Integer loginID;
@FXML
private TreeTableView<ERItems> tableview;
TreeItem root = new TreeItem<>("rootxxx");
@FXML
private TreeTableColumn<ERItems, Integer> col1;
@FXML
private TreeTableColumn<ERItems, String> col2;
@FXML
private TreeTableColumn<ERItems, String> col3;
@FXML
private TreeTableColumn<ERItems, String> col4;

@Override
public void initialize(URL url, ResourceBundle rb) {
conn = connector.ConnectDb();           
idjobs = 286; 
idjobs_by = 6; 
loginID = 4;
dataset2();     
tableview.setEditable(true);
Platform.runLater(() -> {
col2.setCellValueFactory(cellData -> {
if (cellData.getValue().getValue()instanceof ERItems) {
return new ReadOnlyObjectWrapper(cellData.getValue().getValue().getCol_date());
}
return new ReadOnlyObjectWrapper(cellData.getValue().getValue());
});
col3.setCellValueFactory(cellData -> {
if (cellData.getValue().getValue()instanceof ERItems) {
return new ReadOnlyObjectWrapper(cellData.getValue().getValue().getCol_meals());
}
return new ReadOnlyObjectWrapper(cellData.getValue().getValue());
});    
col1.setMinWidth(0);
col1.setMaxWidth(0);
col1.setCellValueFactory(cellData -> {
if (cellData.getValue().getValue()instanceof ERItems) {
return new ReadOnlyObjectWrapper(cellData.getValue().getValue().getCol_workedDay());
}
return new ReadOnlyObjectWrapper(cellData.getValue().getValue());
}); 
col4.setCellValueFactory(cellData -> {
if (cellData.getValue().getValue()instanceof ERItems) {
return new ReadOnlyObjectWrapper(cellData.getValue().getValue().getCol_air());
}
return new ReadOnlyObjectWrapper(cellData.getValue().getValue());
}); 
tableview.setTreeColumn(col2);
tableview.setRoot(root);                
tableview.setShowRoot(false);
rowchecker();
root.setExpanded(true);
});   
}
public void dataset2() {
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();                
String currentDate = null;
String currentRoot = null;
TreeItem<String> tiDate = null;
TreeItem<String> tiRoot = null;  
TreeItem<ERItems> nodeRoot = null;
TreeItem<ERItems> nodeChild = null;
ReliantER tiReliantER = null;
TreeItem<ReliantER> nodeItem = null;
Integer inputRowCounter = 0;
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("Airfare");
if(c_airD > 0) {
c_AirfareS = "$ " + df2.format(c_airD) ;
}    
if (c_rootI == 1 ){
nodeRoot = new TreeItem<> (new ERItems(rs.getInt("workedDay"), c_DateS, c_MealsS, c_AirfareS)); 
root.getChildren().add(nodeRoot);
}else{
if(rs.getInt("receiptActive") == 1) {
nodeChild = new TreeItem<> (new ERItems(rs.getInt("workedDay"), c_DateS, c_MealsS, c_AirfareS));    
nodeRoot.getChildren().add(nodeChild);
}
}
inputRowCounter = inputRowCounter + 1;        
}   
} catch (SQLException e) {
e.printStackTrace();
Alert a1 = new Alert(Alert.AlertType.ERROR);
a1.showAndWait();
}                       
}        

public void rowchecker() {
Callback<TreeTableColumn<ERItems, Integer>, TreeTableCell<ERItems, Integer>> cellFactory
= new Callback<TreeTableColumn<ERItems, Integer>, TreeTableCell<ERItems, Integer>>() {
public TreeTableCell call(TreeTableColumn p) {
TreeTableCell cell = new TreeTableCell<ERItems, Integer>() {
@Override
public void updateItem(Integer item, boolean empty) {
super.updateItem(item, empty);
if (!empty) {
TreeTableRow ttr = getTreeTableRow();
if (item == null || empty){
setText(null);
ttr.setStyle("");
setStyle("");
} else {
ttr.setStyle(item.doubleValue() > 0 
? ""
: "-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;
}
};
col1.setCellFactory(cellFactory);   
}
public static class ERItems {
private final SimpleIntegerProperty col_workedDay;
private final SimpleStringProperty col_meals;
private final SimpleStringProperty col_air;
private final SimpleStringProperty col_date;

private ERItems(Integer col_workedDay, String col_date, String col_meals, String col_air) {
this.col_workedDay = new SimpleIntegerProperty(col_workedDay);
this.col_date = new SimpleStringProperty(col_date);
this.col_meals = new SimpleStringProperty(col_meals);
this.col_air = new SimpleStringProperty(col_air);
}

public String getCol_meals() {
return col_meals.get();
}
public String getCol_air() {
return col_air.get();
}
public String getCol_date() {
return col_date.get();
}

public Integer getCol_workedDay() {
return col_workedDay.get();
}
}       
}

现在，它给了我三行表，其中包含当天的每日总计的父行和个人收据的子行。然后，行检查器查看col1，查看员工是否记录了工作时间，并突出显示该行(如果他们有当天的费用(。

树视图结果