sql - 如何从 JavaFx tableview 上的一个数据库中填充两个表

Question

我是 JavaFx 和数据库的新手。我正在尝试将一个数据库中的两个或多个表填充到 JavaFx 表视图中。我怎样才能做到这一点？这是我到目前为止所拥有的。

1）这是我的 GeneralData.java

public class GeneralData {
    private final StringProperty Year;
    private final StringProperty Month;
    private final StringProperty Temperature;
    private final StringProperty Wind_Lowest;
    private final StringProperty Wind_Highest;
    private final StringProperty MinUVI;
    private final StringProperty MaxUVI;
    private final StringProperty MinAQI;
    private final StringProperty MaxAQI;
    private final StringProperty Humidity;
    private final StringProperty Snowfall;
    private final StringProperty Pressure;

    public GeneralData(String year, String month, String temperature, 
        String windlo, String Windhi,String minUVI, String maxUVI, String 
        minAQI, String maxAQI, String humidity, String snowfall, String 
        pressure){

        this.Year = new SimpleStringProperty(year);
        this.Month = new SimpleStringProperty(month);
        this.Temperature = new SimpleStringProperty(temperature);
        this.Wind_Lowest = new SimpleStringProperty(windlo);
        this.Wind_Highest= new SimpleStringProperty(Windhi);
        this.MinUVI = new SimpleStringProperty(minUVI);
        this.MaxUVI = new SimpleStringProperty(maxUVI);
        this.MinAQI = new SimpleStringProperty(minAQI);
        this.MaxAQI = new SimpleStringProperty(maxAQI);
        this.Humidity = new SimpleStringProperty(humidity);
        this.Snowfall = new SimpleStringProperty(snowfall);
        this.Pressure = new SimpleStringProperty(pressure);

    }

2）这是我的 GeneralMode.java

public class GeneralModel {
    Connection conn;

    public GeneralModel() {

        try {
            this.conn = dbConnection.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        if (this.conn == null) {
            System.exit(1);
        }
    }

    public boolean isDatabasedConnected() {

        return this.conn != null;
    }

}

3）这是我的generalController.java

public class GeneralController implements Initializable {


    GeneralModel generalModel = new GeneralModel();
    @FXML
    private Label dbStatus;

    @FXML
    private TextField miniField;
    @FXML
    private TextField maxField;

    @FXML
    private TableView<GeneralData> weatherTracerTable;
    @FXML
    private TableColumn<GeneralData, String> yearCol;
    @FXML
    private TableColumn<GeneralData, String> monthCol;
    @FXML
    private TableColumn<GeneralData, String> temperatureCol;
    @FXML
    private TableColumn<GeneralData, String> windLowCol;
    @FXML
    private TableColumn<GeneralData, String> windHighCol;
    @FXML
    private TableColumn<GeneralData, String> minUVICol;
    @FXML
    private TableColumn<GeneralData, String> maxUVICol;
    @FXML
    private TableColumn<GeneralData, String> minAQICol;
    @FXML
    private TableColumn<GeneralData, String> maxAQICol;
    @FXML
    private TableColumn<GeneralData, String> humidityCol;
    @FXML
    private TableColumn<GeneralData, String> snowFallCol;
    @FXML
    private TableColumn<GeneralData, String> pressureCol;
    @FXML
    private ComboBox Year;
    @FXML
    private ComboBox Type;

    private dbConnection dbconn;
    private ObservableList<GeneralData> data;
    private String sql = "SELECT * FROM weather_tracer";
    public void initialize(URL url, ResourceBundle rb) {


        if (this.generalModel.isDatabasedConnected()) {

            this.dbStatus.setText("Connected to Database");
        } else {
            this.dbStatus.setText("Not Connected to Database");
        }
        Year.getItems().addAll("2018","2017","2016","2015","2014","2013");
        Type.getItems().addAll("Temperature","Pressure","UV","Humidity","Wind","Snow");

    }

    @FXML
    private void loadData(ActionEvent event)throws SQLException{
        try{
            Connection connection = dbconn.getConnection();
            this.data = FXCollections.observableArrayList();

            ResultSet rs = connection.createStatement().executeQuery(sql);

            while (rs.next()) {
                this.data.add(new GeneralData(rs.getString(1),
                        rs.getString(2), rs.getString(3), rs.getString(4), 
                        rs.getString(5), rs.getString(6), rs.getString(7), 
                        rs.getString(8), rs.getString(9),rs.getString(10), 
                         rs.getString(11), rs.getString(12)));
            }
        }
        catch (SQLException e) {
            System.err.println("Error " + e);
        }
        this.yearCol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("Year"));
        this.monthCol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("Month"));
        this.temperatureCol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("Temperature"));
        this.windLowCol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("Wind_Lowest"));
        this.windHighCol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("Wind_Highest"));
        this.minUVICol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("MinUVI"));
        this.maxUVICol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("MaxUVI"));
        this.minAQICol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("MinAQI"));
        this.maxAQICol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("MaxAQI"));
        this.humidityCol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("Humidity"));
        this.snowFallCol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("Snowfall"));
        this.pressureCol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("Pressure"));
        this.weatherTracerTable.setItems(null);
        this.weatherTracerTable.setItems(data);
    }

    @FXML
    private void search(ActionEvent event)throws SQLException {

        int max = Integer.parseInt(maxField.getText());
        int min = Integer.parseInt(miniField.getText());
        String selectedYear = (String) Year.getSelectionModel().getSelectedItem();
        String selectedType = (String) Type.getSelectionModel().getSelectedItem();
        String fsql = "SELECT * FROM weather_tracer";
        try {

            if (!(Type.getSelectionModel().isEmpty()) && !(Year.getSelectionModel().isEmpty())) {
                fsql = "SELECT * FROM weather_tracer where Year = " + selectedYear + " AND " + selectedType + " between " + min + " and " + max;
            } 
            else if (!(Type.getSelectionModel().isEmpty())) {
                fsql = "SELECT * FROM weather_tracer where " + selectedType + " between " + min + " and " + max;
            }
            Connection connection = dbconn.getConnection();
            this.data = FXCollections.observableArrayList();
            ResultSet rs = connection.createStatement().executeQuery(fsql);
            while (rs.next()) {
                this.data.add(new GeneralData(rs.getString(1),rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9),rs.getString(10), rs.getString(11), rs.getString(12)));
            }
        } catch (SQLException e) {
            System.err.println("Error " + e);
        }
        this.yearCol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("Year"));
        this.monthCol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("Month"));
        this.temperatureCol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("Temperature"));
        this.windLowCol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("Wind_Lowest"));
        this.windHighCol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("Wind_Highest"));
        this.minUVICol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("MinUVI"));
        this.maxUVICol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("MaxUVI"));
        this.minAQICol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("MinAQI"));
        this.maxAQICol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("MaxAQI"));
        this.humidityCol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("Humidity"));
        this.snowFallCol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("Snowfall"));
        this.pressureCol.setCellValueFactory(new PropertyValueFactory<GeneralData, String>("Pressure"));
        this.weatherTracerTable.setItems(null);
        this.weatherTracerTable.setItems(this.data);

    }
}

在我展示演示后，给我的教授。他告诉我我犯了一些错误，因为我将所有数据组合成一个关系。我需要将它分成不同的小关系。现在，我有 7 个关系。如空气质量关系，湿度关系，雪关系等......

我正在尝试将 aNATURAL JOIN用于上面列出的任一关系并将它们显示在 tableview 上。但是我很难做到这一点，因为一旦我声明了 tableview，我需要定义类型并且之后我无法更改类型。有没有办法绕过它？

标签： sqldatabasejavafx