Java 如何用数据库数据填充 TableView

How to fill up a TableView with database data

提问by ORey

I've been trying to load a TableView with data queried from a database, but can't seem to get it to work.

我一直在尝试使用从数据库查询的数据加载 TableView，但似乎无法让它工作。

This is my first attempt at trying to fill up a database with database query items, in case my code seems mungled and far from good.

这是我第一次尝试用数据库查询项填充数据库，以防我的代码看起来杂乱无章，而且效果不佳。

The FXML was done via JavaFx SceneBuilder.

FXML 是通过 JavaFx SceneBuilder 完成的。

This is the database query class:

这是数据库查询类：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.fxml.FXML;
import javafx.scene.control.TableView;

public class StudentInfo {
    static String JDBC_DRIVER = "org.h2.Driver";
    static String DB_URL = "jdbc:h2:file:C:/WAKILI/WAKILIdb";
    //  Database credentials
    static final String USER = "sa";
    static final String PASS = "";

    public static Connection conn = null;
    @FXML
    private TableView<StudentInfo> lovelyStudents;

    private ObservableList data;

    // Public static ObservableList<COA> getAllCOA(){
    public void getAllstudentInfo() {
        Statement st = null;
        ResultSet rs;
        String driver = "org.h2.Driver";

        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            st = conn.createStatement();
            String recordQuery = ("SELECT id, KIWI FROM KIWI");

            rs = st.executeQuery(recordQuery);
            while (rs.next()) {
                ObservableList row = FXCollections.observableArrayList();

                for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                    row.add(rs.getString(i));
                    System.out.println(row);
                }

                data.add(row);

            }
            lovelyStudents.setItems(data);

        } catch (ClassNotFoundException | SQLException ex) {
            // CATCH SOMETHING
        }
    }
}

This is the FXML script generated via JavaFx scene builder:

这是通过 JavaFx 场景生成器生成的 FXML 脚本：

<?xml version="1.0" encoding="UTF-8"?>

<?import java.lang.*?>
<?import java.util.*?>
<?import javafx.scene.*?>
<?import javafx.scene.control.*?>
<?import javafx.scene.layout.*?>

<AnchorPane id="AnchorPane" prefHeight="400.0" prefWidth="700.0" xmlns:fx="http://javafx.com/fxml" fx:controller="wakiliproject.SampleController">
  <children>
    <TableView prefHeight="400.0" prefWidth="700.0" AnchorPane.bottomAnchor="0.0" AnchorPane.leftAnchor="0.0" AnchorPane.rightAnchor="0.0" AnchorPane.topAnchor="0.0">
      <columns>
        <TableColumn prefWidth="75.0" text="Column X" />
      </columns>
    </TableView>
  </children>
</AnchorPane>

采纳答案by Java Man

Here is the best solution for the filling data to the tableView From the database.

这里是从数据库向tableView填充数据的最佳解决方案。

import java.sql.Connection;
import java.sql.ResultSet;
import javafx.application.Application;
import javafx.beans.property.SimpleStringProperty;
import javafx.beans.value.ObservableValue;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.Scene;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableColumn.CellDataFeatures;
import javafx.scene.control.TableView;
import javafx.stage.Stage;
import javafx.util.Callback;

/**
 * 
 * @author Narayan
 */

public class DynamicTable extends Application{

    //TABLE VIEW AND DATA
    private ObservableList<ObservableList> data;
    private TableView tableview;

    //MAIN EXECUTOR
    public static void main(String[] args) {
        launch(args);
    }

    //CONNECTION DATABASE
    public void buildData(){
          Connection c ;
          data = FXCollections.observableArrayList();
          try{
            c = DBConnect.connect();
            //SQL FOR SELECTING ALL OF CUSTOMER
            String SQL = "SELECT * from CUSTOMer";
            //ResultSet
            ResultSet rs = c.createStatement().executeQuery(SQL);

            /**********************************
             * TABLE COLUMN ADDED DYNAMICALLY *
             **********************************/
            for(int i=0 ; i<rs.getMetaData().getColumnCount(); i++){
                //We are using non property style for making dynamic table
                final int j = i;                
                TableColumn col = new TableColumn(rs.getMetaData().getColumnName(i+1));
                col.setCellValueFactory(new Callback<CellDataFeatures<ObservableList,String>,ObservableValue<String>>(){                    
                    public ObservableValue<String> call(CellDataFeatures<ObservableList, String> param) {                                                                                              
                        return new SimpleStringProperty(param.getValue().get(j).toString());                        
                    }                    
                });

                tableview.getColumns().addAll(col); 
                System.out.println("Column ["+i+"] ");
            }

            /********************************
             * Data added to ObservableList *
             ********************************/
            while(rs.next()){
                //Iterate Row
                ObservableList<String> row = FXCollections.observableArrayList();
                for(int i=1 ; i<=rs.getMetaData().getColumnCount(); i++){
                    //Iterate Column
                    row.add(rs.getString(i));
                }
                System.out.println("Row [1] added "+row );
                data.add(row);

            }

            //FINALLY ADDED TO TableView
            tableview.setItems(data);
          }catch(Exception e){
              e.printStackTrace();
              System.out.println("Error on Building Data");             
          }
      }


      @Override
      public void start(Stage stage) throws Exception {
        //TableView
        tableview = new TableView();
        buildData();

        //Main Scene
        Scene scene = new Scene(tableview);        

        stage.setScene(scene);
        stage.show();
      }
}

Here is the Reference

这是参考

Thanks..

谢谢..

回答by Yerbol

If Database contains different types of data, not only String, then column type assigning is better to make dynamic:

如果 Database 包含不同类型的数据，而不仅仅是 String，那么列类型分配最好是动态的：

package sample;

import javafx.application.Application;
import javafx.beans.property.*;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.stage.Stage;
import java.sql.*;
import java.util.ArrayList;
import java.util.TimeZone;

//Author: Yerbol
//SQL database "sqlbase_schema" contains a Table "sqlbase_table" with 3 columns: "id" (Integer(INT(11))), "name" (String(VARCHAR(45))), "married" (Boolean(TINYINT(1)));

public class Main extends Application {
    private TableView<Person> tableView = new TableView<>();

    @Override
    public void start(Stage primaryStage) throws SQLException, ClassNotFoundException {
        //Show window
        buildData();
        Parent root = tableView;
        primaryStage.setScene(new Scene(root, 300, 275));
        primaryStage.show();
    }

    public void buildData() throws ClassNotFoundException, SQLException {

        Connection dbConnection;
        //SQL Database connection params
        String dbHost = "localhost";
        String dbPort = "3306";
        String dbUser = "root";
        String dbPassword = "12345";
        String dbName = "sqlbase_schema";
        String dbTableName = "sqlbase_table";
        String select = "SELECT * FROM " + dbTableName;
        String connectionString = "jdbc:mysql://" + dbHost + ":" + dbPort +"/" + dbName+"?useLegacyDatetimeCode=false&amp&serverTimezone=" + TimeZone.getDefault().getID();
        Class.forName("com.mysql.cj.jdbc.Driver");

        //Connecting to Database
        dbConnection = DriverManager.getConnection(connectionString, dbUser, dbPassword);

        //Extracting data from Databasee
        ResultSet resultSet = null;
        try {
            PreparedStatement preparedStatement = dbConnection.prepareStatement(select);
            resultSet = preparedStatement.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        ObservableList dbData = FXCollections.observableArrayList(dataBaseArrayList(resultSet));

        //Giving readable names to columns
        for(int i=0 ; i<resultSet.getMetaData().getColumnCount(); i++) {
            TableColumn column = new TableColumn<>();
            switch (resultSet.getMetaData().getColumnName(i+1)) {
                case "id":
                    column.setText("ID #");
                    break;
                case "name":
                    column.setText("Person Name");
                    break;
                case "married":
                    column.setText("Marital Status");
                    break;
                default: column.setText(resultSet.getMetaData().getColumnName(i+1)); //if column name in SQL Database is not found, then TableView column receive SQL Database current column name (not readable)
                    break;
            }
            column.setCellValueFactory(new PropertyValueFactory<>(resultSet.getMetaData().getColumnName(i+1))); //Setting cell property value to correct variable from Person class.
            tableView.getColumns().add(column);
        }

        //Filling up tableView with data
        tableView.setItems(dbData);
    }

    public class Person {

        IntegerProperty id = new SimpleIntegerProperty(); //variable names should be exactly as column names in SQL Database Table. In case if you want to use <int> type instead of <IntegerProperty>, then you need to use getter/setter procedures instead of xxxProperty() below
        StringProperty name = new SimpleStringProperty();
        BooleanProperty married = new SimpleBooleanProperty();

        public IntegerProperty idProperty() { //name should be exactly like this [IntegerProperty variable name (id) + (Property) = idProperty] (case sensitive)
            return id;
        }

        public StringProperty nameProperty() {
            return name;
        }

        public BooleanProperty marriedProperty() {
            return married;
        }

        public Person(int idValue, String nameValue, boolean marriedValue) {
            id.set(idValue);
            name.set(nameValue);
            married.set(marriedValue);
        }

        Person(){}
    }

    //extracting data from ResulSet to ArrayList
    private ArrayList dataBaseArrayList(ResultSet resultSet) throws SQLException {
        ArrayList<Person> data =  new ArrayList<>();
        while (resultSet.next()) {
            Person person = new Person();
            person.id.set(resultSet.getInt("id"));
            person.name.set(resultSet.getString("name"));
            person.married.set(resultSet.getBoolean("married"));
            data.add(person);
        }
        return data;
    }

    public static void main(String[] args) {
        launch(args);
    }
}

In this example SQL database "sqlbase_schema" contains a Table "sqlbase_table" with 3 columns: "id" (Integer(INT(11))), "name" (String(VARCHAR(45))), "married (Boolean(TINYINT(1)));

在这个例子中，SQL 数据库“sqlbase_schema”包含一个表“sqlbase_table”，有 3 列：“id”(Integer(INT(11)))、“name”(String(VARCHAR(45)))、“married(Boolean(TINYINT) (1)));

回答by Alisher Gulov

public TableView queryToTable(String sql) {
    TableView result = new TableView();
    ObservableList data = FXCollections.observableArrayList();

    jdbcTemplate.query(sql, (rs)->{
        for(int i=0 ; i<rs.getMetaData().getColumnCount(); i++){
            final int j = i;
            TableColumn col = new TableColumn(rs.getMetaData().getColumnName(i+1));
            col.setCellValueFactory(new Callback<TableColumn.CellDataFeatures<ObservableList,String>,ObservableValue<String>>(){
                public ObservableValue<String> call(TableColumn.CellDataFeatures<ObservableList, String> param) {
                    return new SimpleStringProperty(param.getValue().get(j).toString());
                }
            });
            result.getColumns().addAll(col);
        }

        while(rs.next()){
            ObservableList<String> row = FXCollections.observableArrayList();
            for(int i=1 ; i<=rs.getMetaData().getColumnCount(); i++)
                row.add(rs.getString(i));
            data.add(row);
        }
        return null;
    });
    return result;
}