Results 1 to 4 of 4
  1. #1
    beatzz is offline Member
    Join Date
    Mar 2009
    Posts
    6
    Rep Power
    0

    Default Populating a TableView's TableRows with data from a ResultSet

    So I've made some decent progress on this app so far, but I'm stuck again. When you click the connect button, given you have selected a database type from the ComboBox and have supplied a url in the TextField, it will access the database, and through a series of SQL queries, it will generate a TableView on a Tab within the TabPane for each table found in the database, then populate the TableViews with TableColumns with the corresponding names from the database, then (and here is where I am stuck) I want to query the database for all the data within each column and populate each TableColumn individually. As for now, this third while loop is generating rows for each result in the ResultSet returned from the query.

    Java FX Code:
    package fx;
     
    import javafx.application.Platform;
    import javafx.collections.FXCollections;
    import javafx.collections.ObservableList;
    import javafx.fxml.FXML;
    import javafx.fxml.Initializable;
    import javafx.scene.control.*;
    import javafx.scene.input.KeyCode;
    import javafx.scene.input.KeyEvent;
     
    import java.net.URL;
    import java.sql.*;
    import java.util.ResourceBundle;
     
    public class Controller implements Initializable {
     
        private Connection connection;
        private ObservableList<String> dbTypeList = FXCollections.observableArrayList("MariaDB", "MongoDB", "MySQL", "OracleDB", "SQLite");
     
        @FXML
        public ComboBox<String> dbTypeCbx;
        @FXML
        public TextField dbUrlTxt;
        @FXML
        public Button connectBtn;
        @FXML
        public TabPane tabPane;
     
        @Override
        public void initialize(URL location, ResourceBundle resources) {
            dbTypeCbx.setItems(dbTypeList);
            dbTypeCbx.setValue(dbTypeList.get(4));
            dbUrlTxt.setText("jdbc:sqlite:products.sqlite");
        }
     
        public void connectOnClick() { connect(); }
        public void connectOnReturn(KeyEvent e) {
            if (e.getCode().equals(KeyCode.ENTER))
                connect();
        }
        private void connect() {
            String dbUrl;
     
            if (dbTypeCbx.getValue() == null) {
                new Alert(Alert.AlertType.ERROR, "Database type must be selected.").showAndWait();
            } else if (dbUrlTxt.getText().isEmpty()) {
                new Alert(Alert.AlertType.ERROR, "Database url must be specified.").showAndWait();
            } else {
     
                dbUrl = dbUrlTxt.getText();
     
                try {
                    connection = DriverManager.getConnection(dbUrl);
                    System.out.println("Successfully connected to the database");
                } catch (SQLException e) {
                    System.err.println(e.toString());
                }
     
                String tableQuery = "SELECT * FROM sqlite_master WHERE type='table' ORDER BY name";
     
                try (PreparedStatement tableQueryPS = connection.prepareStatement(tableQuery)) {
                    ResultSet tableNames = tableQueryPS.executeQuery();
                    System.out.println("Table query successful.");
     
                    while (tableNames.next()) {
                        Tab tab = new Tab(tableNames.getString("name"));
                        TableView tableView = new TableView();
                        tabPane.getTabs().add(tab);
                        tab.setContent(tableView);
     
                        String columnQuery = "PRAGMA table_info(" + tableNames.getString("name") + ")";
     
                        try (PreparedStatement columnQueryPS = connection.prepareStatement(columnQuery)) {
                            ResultSet columnNames = columnQueryPS.executeQuery();
     
                            while (columnNames.next()) {
                                TableColumn column = new TableColumn(columnNames.getString("name"));
                                tableView.getColumns().add(column);
     
                                String dataQuery = "SELECT " + columnNames.getString("name") + " FROM " + tableNames.getString("name");
     
                                try (PreparedStatement dataQueryPS = connection.prepareStatement(dataQuery)) {
                                    ResultSet columnData = dataQueryPS.executeQuery();
     
                                    while (columnData.next()) {
                                        TableRow row = new TableRow();
                                        tableView.getItems().add(row);
                                        // populate the table!!!
                                        // I am here!!!
                                    }
                                } catch (SQLException dataQueryException) {
                                    System.err.println(dataQueryException.toString());
                                }
                            }
                            System.out.println("Added TableColumns to the TableView");
                        } catch (SQLException columnQueryException) {
                            System.err.println(columnQueryException.toString());
                        }
                    }
                    System.out.println("Added Tabs to the TabPane");
                } catch (SQLException tableQueryException) {
                    System.err.println(tableQueryException.toString());
                }
            }
        }
     
        public void exitOnClick() { exit(); }
        public void exitOnReturn(KeyEvent e) {
            if (e.getCode().equals(KeyCode.ENTER))
                exit();
        }
        private void exit() {
            new Alert(Alert.AlertType.CONFIRMATION, "Are you sure you want to quit?").showAndWait();
            connection = null;
            Platform.exit();
        }
     
    }
    Here is what the app looks like now after having connected to the db.

  2. #2
    benji2505 is offline Senior Member
    Join Date
    Sep 2014
    Location
    MA, USA
    Posts
    398
    Rep Power
    5

    Default Re: Populating a TableView's TableRows with data from a ResultSet

    The data in the TableView is kept in an ObservableList<YourObject>. You have to define that ObservableList, hook it up to your TableView and then feed the data you get from your ResultSet into that ObservableList. Check these examples:

    example 1

    example 2

  3. #3
    Norm's Avatar
    Norm is offline Moderator
    Join Date
    Jun 2008
    Location
    Eastern Florida
    Posts
    19,963
    Rep Power
    31

    Default Re: Populating a TableView's TableRows with data from a ResultSet

    If you don't understand my response, don't ignore it, ask a question.

  4. #4
    benji2505 is offline Senior Member
    Join Date
    Sep 2014
    Location
    MA, USA
    Posts
    398
    Rep Power
    5

    Default Re: Populating a TableView's TableRows with data from a ResultSet

    ok. I just checked the "latest posts" and did not see that it is ancient already.

    The trick is to cast the result set onto an object. From then on it is all nice and easy, if you only want one column, the easiest method is to use the PropertyValueFactory. You have to know what is returned from the db though, but I would think that is a given since you are executing a query on it.

Similar Threads

  1. Replies: 2
    Last Post: 08-09-2011, 07:52 PM
  2. Replies: 2
    Last Post: 05-07-2011, 06:32 PM
  3. Need help: Populating data
    By rahul202 in forum Java Servlet
    Replies: 0
    Last Post: 10-25-2010, 07:16 PM
  4. Error when populating the data
    By srinivas_k543 in forum Java Servlet
    Replies: 2
    Last Post: 07-10-2009, 08:29 AM
  5. Replies: 11
    Last Post: 02-20-2009, 02:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •