How to populate a tableview with database data

Question

I am trying to load a TableView with data requested from a database, but cannot seem to get them to work.

This is my first attempt to try to populate the database with database query elements, in case my code seems vintage and far from good.

FXML was executed through the 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 an FXML script generated using a JavaFx script:

 <?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> 

Answer 1

Here is the best solution to populate tableView data from a database.

 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 link

Thanks..

Answer 2

If the database contains different data types, not just String, then the purpose of the column is better to make dynamic:

 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, the SQL database "sqlbase_schema" contains the table "sqlbase_table" with three columns: "id" (Integer (INT (11))), "name" (String (VARCHAR (45))), "married (Boolean (TINYINT ) (1)));