Let me clarify the question.
I'm creating a JavaFX application that has 2 ComboBoxes. One displays available catalogs in MySQL DB and other shows available tables in the selected catalog from first ComboBox. Now the problem is that I need to create a TableView that would display the results of "desc tableName" MySQL command.
I'm using jdbc API, executing "desc ${tableName}" using Statement interface and getting the data into a ResultSet. I'm able to fill the TableView column names with column names from ResultSetMetaData object.
Now, I know that in order to fill the data cells in the TableView, we need to use a pojo class that would define the data model. But, by now you can understand that since the tables are being selected dynamically by user, I cannot create at least a generic data model class for TableView.
I also know that we cannot set data in individual cells of the TableView programmatically.
So, I have instead used a HashMap as data model for the TableView and used the keys of HashMap as the CellValueFactory properties.
TableView<HashMap<String, String>> tableView;
ObservableList<TableColumn<HashMap<String, String>, String>> tvcolumns = FXCollections.observableArrayList();
tableColumns.forEach((t) -> {
tvcolumns.add(new TableColumn<>(t));
});
for (int i = 0; i < tvcolumns.size(); i++) {
TableColumn<HashMap<String, String>, String> temp = tvcolumns.get(i);
temp.setCellValueFactory(new PropertyValueFactory<>(keySet.get(i)));
}
tableView.getItems().clear();
tableView.setItems(tableData);
tableView.getColumns().addAll(tvcolumns);
Now this HashMaps's keys contain columns names of the ResultSet data and values contains data of the corresponding columns in the ResultSet. I've written the code in such a way that TableView takes in an ObservableList<> of these HashMap objects (one HashMap object for each row in ResultSet) and populate the cells in TableView with HashMap's values, based on HashMap's keys.
But the data is not being shown in the TableView. I don't even know if something like this is possible.
Here is the full code:
--> MainUiController.java :
public class MainUiController implements Initializable {
@FXML
private AnchorPane rootPane;
@FXML
private VBox vBoxMain;
@FXML
private HBox hBoxTop;
@FXML
private VBox vBoxTopLeft;
@FXML
private ComboBox<String> cbCatalog;
@FXML
private VBox vBoxTopRight;
@FXML
private ComboBox<String> cbTables;
@FXML
private HBox hBoxBottom;
@FXML
private TitledPane titledPaneBottom;
@FXML
private AnchorPane anchorPaneBottom;
@FXML
private TableView<HashMap<String, String>> tableView;
DBHelper dbHelper = new DBHelper();
/**
* Initializes the controller class.
*
* @param url
* @param rb
*/
@Override
public void initialize(URL url, ResourceBundle rb) {
// TODO
ObservableList<String> catalogItems = cbCatalog.getItems();
catalogItems.clear();
catalogItems.addAll(dbHelper.getCatalogs());
titledPaneBottom.setExpanded(false);
}
@FXML
private void populateTables(ActionEvent event) {
String dbName = ((ComboBox<String>) event.getSource()).getValue();
System.out.println("catalog value: " + dbName);
dbHelper.useDB(dbName);
ObservableList<String> tables = cbTables.getItems();
tables.clear();
tables.addAll(dbHelper.getTables());
}
@FXML
private void descTable(ActionEvent event) {
String tableName = ((ComboBox<String>) event.getSource()).getValue();
ObservableList<String> tableColumns = dbHelper.getTableColumns(tableName);
ObservableList<HashMap<String, String>> tableData = dbHelper.getTableData();
List<String> keySet = null;
if (!tableData.isEmpty()) {
keySet = new ArrayList<>(tableData.get(0).keySet());
}
titledPaneBottom.setText("\"" + tableName + "\" description:");
ObservableList<TableColumn<HashMap<String, String>, String>> tvcolumns = FXCollections.observableArrayList();
tableColumns.forEach((t) -> {
tvcolumns.add(new TableColumn<>(t));
});
for (int i = 0; i < tvcolumns.size(); i++) {
TableColumn<HashMap<String, String>, String> temp = tvcolumns.get(i);
temp.setCellValueFactory(new PropertyValueFactory<>(keySet.get(i)));
}
tableView.getItems().clear();
tableView.setItems(tableData);
tableView.getColumns().addAll(tvcolumns);
}
}
--> DBHelper.java:
class DBHelper {
Connection con;
Statement st;
ObservableList<String> tablesList = FXCollections.observableArrayList();
ObservableList<String> columnList = FXCollections.observableArrayList();
HashMap<String, String> tableData;
ObservableList<HashMap<String, String>> tableDataList = FXCollections.observableArrayList();
String dbName, tableName;
ResultSet tableCols;
ResultSetMetaData colsMetaData;
public DBHelper() {
try {
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/", "*****", "******");
st = con.createStatement();
} catch (SQLException ex) {
Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
}
}
public ObservableList<String> getCatalogs() {
try {
ObservableList<String> catalogList = FXCollections.observableArrayList();
DatabaseMetaData dbmd = con.getMetaData();
ResultSet catalogs = dbmd.getCatalogs();
while (catalogs.next()) {
catalogList.add(catalogs.getString(1));
}
return catalogList;
} catch (SQLException ex) {
Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
}
return null;
}
public ObservableList<String> getTables() {
try {
ResultSet tables = st.executeQuery("show tables");
tablesList.clear();
while (tables.next()) {
tablesList.add(tables.getString(1));
}
return tablesList;
} catch (SQLException ex) {
Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
}
return null;
}
void useDB(String dbName) {
this.dbName = dbName;
try {
int execute = st.executeUpdate("use " + dbName);
} catch (SQLException ex) {
Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
}
}
public ObservableList<String> getTableColumns(String tableName) {
this.tableName = tableName;
try {
tableCols = st.executeQuery("desc " + tableName);
colsMetaData = tableCols.getMetaData();
columnList.clear();
int count = 1;
while (count <= colsMetaData.getColumnCount()) {
columnList.add(colsMetaData.getColumnName(count));
count++;
}
return columnList;
} catch (SQLException ex) {
Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
}
return null;
}
public ObservableList<HashMap<String, String>> getTableData() {
tableDataList.clear();
if (tableCols != null & colsMetaData != null) {
try {
while (tableCols.next()) {
tableData = new HashMap<>();
int count = 1;
while (count <= colsMetaData.getColumnCount()) {
tableData.put(colsMetaData.getColumnName(count), tableCols.getString(count));
count++;
}
tableDataList.add(tableData);
}
tableCols.close();
tableCols = null;
colsMetaData = null;
return tableDataList;
} catch (SQLException ex) {
Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
}
}
return null;
}
}
Please help me on solving this issue. I'm pretty sure that using HashMap methods as datamodel for a TableView is wrong/not possible. But I do not know how to create a generic datamodel object in this case. Please help solve this problem.
I'm currently learning JavaFx and I wanted to make this application as a challenge. Any insight on this would be highly helpful for my career.
This may be helpful. I use a custom TableView that accepts a ResultSet for the data.
It's a bit of a hack implementation, but it works for me in these cases. I am not sure that keeping a ResultSet open outside of the datasource is a wise decision, but it may lead you in the right direction at least:
import javafx.beans.property.SimpleStringProperty;
import javafx.beans.value.ObservableValue;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.util.Callback;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class ResultSetTableView extends TableView {
private ResultSet resultSet;
private List<String> columnNames = new ArrayList<>();
public ResultSetTableView(ResultSet resultSet) throws SQLException {
super();
this.resultSet = resultSet;
buildData();
}
private void buildData() throws SQLException {
ObservableList<ObservableList> data = FXCollections.observableArrayList();
for (int i = 0; i < resultSet.getMetaData().getColumnCount(); i++) {
final int j = i;
TableColumn col = new TableColumn(resultSet.getMetaData().getColumnName(i + 1));
col.setCellValueFactory((Callback<TableColumn.CellDataFeatures<ObservableList, String>, ObservableValue<String>>) param -> {
if (param.getValue().get(j) != null) {
return new SimpleStringProperty(param.getValue().get(j).toString());
} else {
return null;
}
});
getColumns().addAll(col);
this.columnNames.add(col.getText());
}
while (resultSet.next()) {
//Iterate Row
ObservableList<String> row = FXCollections.observableArrayList();
for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
//Iterate Column
row.add(resultSet.getString(i));
}
data.add(row);
}
//FINALLY ADDED TO TableView
setItems(data);
}
public List<String> getColumnNames() {
return columnNames;
}
}