-1

Let me clarify the question. So I decided to create a TreeView just like in PhpMyAdmin (xampp) that shows all Databases. I successfully managed to get the Databases and Tables in the Treeview, but not the content of the Tables in the TableView. So basically the first thing I want to do is, that if I click on a Table, the column headers should show up in the Table next to the Treeview. I managed to write some code, but it does not do what it is supposed to do. Please help me I am trying to learn this programming language (JavaFX).

This is the Controller class

package sample;

import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.TreeItem;
import javafx.scene.control.TreeView;
import javafx.scene.control.cell.PropertyValueFactory;
import java.net.URL;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

public class Controller implements Initializable {

    Statement statement;
    Statement statementDatabase;
    Statement stmntTables;
    Statement stmntUse;
    Statement stmntCols;
    ResultSet resultDatabase;
    ResultSet resultTables;
    ResultSet resultUse;
    ResultSet resultCols;
    
    @FXML
    TreeView <String> treeView;
    @FXML
    TableView<String> tableView;

    @Override
    public void initialize(URL url, ResourceBundle resourceBundle) {

        try {
            statement = DatabaseConnection.getInstance().getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet result = statement.executeQuery("SHOW DATABASES");

            TreeItem<String> root = new TreeItem<>("Datenbanken");

            while(result.next()) {
                TreeItem<String> node = new TreeItem<>(result.getString(1));
                root.getChildren().add(node);

                Statement statement2 = DatabaseConnection.getInstance().getConnection().createStatement();
                ResultSet subResult = statement2.executeQuery("SHOW TABLES FROM " + result.getString(1));
                while(subResult.next())
                {
                    TreeItem<String> node2 = new TreeItem<>(subResult.getString(1));
                    node.getChildren().add(node2);
                }
            }
            result.beforeFirst();
            treeView.setRoot(root);
            root.setExpanded(true);
            
            ShowDatabase();
            UseDatabase();

        } catch (Exception e){
            e.printStackTrace();
        }
    }
    
    
    public void ShowDatabase() {
        try {
            statementDatabase = DatabaseConnection.getInstance().getConnection().createStatement();
            resultDatabase = statementDatabase.executeQuery("SHOW DATABASES");
            System.out.println("All Databases displayed!");
        } catch (SQLException e) {
            System.out.println("Databases could not be displayed!");
            e.printStackTrace();
        }
    }

    public void UseDatabase() {
        try {
            while (resultDatabase.next()) {
                stmntUse = DatabaseConnection.getInstance().getConnection().createStatement();
                resultUse = stmntUse.executeQuery("USE " + resultDatabase.getString(1));
                //System.out.println(resultDatabase.getString(1));
                ShowTables();
                //System.out.println(resultTables.getString(1));
                ShowColumns();
            }
            System.out.println("Database injected!");
            System.out.println("All Tables displayed!");
            System.out.println("All Columns displayed!");
        } catch (SQLException e) {
            System.out.println("Database ejected!");
            e.printStackTrace();
        }
    }

    public void ShowTables() {
        try {
            while (resultDatabase.next()) {
                stmntTables =  DatabaseConnection.getInstance().getConnection().createStatement();
                // System.out.println(resultDatabase);
                resultTables = stmntTables.executeQuery("SHOW TABLES FROM " + resultDatabase.getString(1));
                // System.out.println(resultTables.getString(1));
            }
        } catch (SQLException e) {
            System.out.println("Tables could not be displayed!");
            e.printStackTrace();
        }
    }
    
    public void ShowColumns() {
        try {
            while (resultTables.next()) {
                stmntCols =  DatabaseConnection.getInstance().getConnection().createStatement();
                resultCols = stmntCols.executeQuery("SHOW COLUMNS FROM " + resultTables.getString(1));
                System.out.println(resultCols);
                FillTable(resultCols);
    
            }
        } catch (SQLException e) {
            System.out.println("Columns could not be displayed!");
            e.printStackTrace();
        }
    }
    
    public void FillTable(ResultSet res) {
        TableColumn<String, String>col = new TableColumn<String, String>();
        try {
            col.setCellValueFactory(new PropertyValueFactory<>(res.getString(1)));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        tableView.getColumns().add(col);
    }
}

and this is the Database Connection

package sample;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;


public class DatabaseConnection {

    private static DatabaseConnection instance;
    private Connection connection;
    private static String ip = "localhost";

    public static void setIp(String ip) {
        DatabaseConnection.ip = ip;
    }

    private DatabaseConnection() throws SQLException
    {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            connection = DriverManager.getConnection("jdbc:mysql://" + ip + ":" + 3306, "root", "");
            System.out.println("Successfully connected to " + ip + "!");

        } catch (Exception exc) {
            //exc.printStackTrace();
            System.out.println("Connection failed to " + ip + "!");
            getConnection().close();
        }
    }

    public Connection getConnection() {
        return connection;
    }

    public static DatabaseConnection getInstance() throws SQLException {
        if (instance == null) {
            instance = new DatabaseConnection();
        } else if (instance.getConnection().isClosed()) {
            instance = new DatabaseConnection();
        }
        return instance;
    }

}
1
  • java naming conventions please Commented Mar 25, 2021 at 23:00

1 Answer 1

1

Here is some code that should get you going. You will probably need a listener on the TreeView to change the table data and headers as you click on different items in the TreeView. This code simulates getting the headers from the database as List<String> and get the data from the database as List<List<String>>.

import java.util.ArrayList;
import java.util.List;
import javafx.application.Application;
import javafx.beans.property.ReadOnlyObjectWrapper;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.Scene;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.layout.StackPane;
import javafx.stage.Stage;


/**
 * JavaFX App
 */
public class App extends Application {

    
    
    @Override
    public void start(Stage primaryStage) {
        ObservableList<ObservableList<String>> data = FXCollections.observableArrayList();        
        final List<List<String>> databaseData = getTableDataFromDBAsListOfList();//Get data from excel file
        //Add database data to an observable list
        for(int i = 0; i < databaseData.size(); i++)
        {
            data.add(FXCollections.observableArrayList(databaseData.get(i)));
        }

        TableView<ObservableList<String>> tableView = new TableView();
        tableView.setItems(data);
        
        //Create the table columns, set the cell value factory and add the column to the tableview.
        List<String> tableHeaders = getTableHeadersFromDBAsList();
        for (int i = 0; i < tableHeaders.size(); i++) {
            final int curCol = i;
            final TableColumn<ObservableList<String>, String> column = new TableColumn<>(tableHeaders.get(i));
            column.setCellValueFactory(param -> new ReadOnlyObjectWrapper<>(param.getValue().get(curCol)));
            tableView.getColumns().add(column);
        }
        
        

        StackPane root = new StackPane(tableView);
        Scene scene = new Scene(root);
        primaryStage.setScene(scene);
        primaryStage.show();
    }

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

    public List<String> getTableHeadersFromDBAsList()
    {
        List<String> returnList = new ArrayList();
        returnList.add("Header1");
        returnList.add("Header2");
        returnList.add("Header3");
        returnList.add("Header4");
        
        return returnList;
    }
    
    public List<List<String>> getTableDataFromDBAsListOfList()
    {
        List<List<String>> returnList = new ArrayList();
        List<String> dataRow1 = new ArrayList();
        dataRow1.add("Data 1 1");
        dataRow1.add("Data 1 2");
        dataRow1.add("Data 1 3");
        dataRow1.add("Data 1 4");
        
        List<String> dataRow2 = new ArrayList();
        dataRow2.add("Data 2 1");
        dataRow2.add("Data 2 2");
        dataRow2.add("Data 2 3");
        dataRow2.add("Data 2 4");
        
        returnList.add(dataRow1);
        returnList.add(dataRow2);
        
        return returnList;
    }
}
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.