I'm writing a program that dynamically fills a JavaFX TableView. Everything is working fine, and I am able to use the same method over and over to fill multiple tables with info from various queries. I see there are many questions on this but I just cannot find the answer for this situation.
My Code is based on the answer to this question.
However, my issue only arises when I try to assign functionality to a button later on in the code. I am able to call the above method from the link multiple times to populate multiple tables, and I have tried explicitly calling .close() on the connection and re-opening it in my various methods as a local variable thus reopening it each time the method is called, and also instantiating the connection as a class variable. I just cannot figure out why I am continuing to receive this area despite everything. I am relatively new to coding in general so I am sure I am missing some information regarding how connections work.
Additionally, if anyone has any tips on how to update the TableView after changes have been made to the .db file that would be great as well.
Here is my specific method that uses the code from the answer above. The only modification I made is that I gave it parameters for an input query, and the associated table on which I want to display the results of that query for my three tables I am displaying and this works great for populating the tables:
There is a screenshot below for context.
public void buildData(String query, TableView table) {
data = FXCollections.observableArrayList();
try {
//SQL FOR SELECTING ALL OF CUSTOMER
String SQL = query;
//ResultSet
ResultSet rs = c.createStatement().executeQuery(SQL);
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<TableColumn.CellDataFeatures<ObservableList, String>, ObservableValue<String>>() {
public ObservableValue<String> call(TableColumn.CellDataFeatures<ObservableList, String> param) {
return new SimpleStringProperty(param.getValue().get(j).toString());
}
});
col.setMinWidth(200);
table.getColumns().addAll(col);
System.out.println("Column [" + i + "] ");
}
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
table.setItems(data);
} catch (Exception e) {
e.printStackTrace();
System.out.println("Error on Building Data");
}
}
The issue for the database locked error then arises here, in this method that I am using when the "add" button is clicked, I thought by using the same Connection c it would make a difference but it has not. Also, like I said I tried to close the connection after each use an open a new one at the top of each method but it still claims a connection was left open and that the database is busy, resulting the Database Locked error:
public void addTenant(String name, double leaseLength, boolean rentPaid, String propID, TableView table) {
data = FXCollections.observableArrayList();
try {
//SQL FOR SELECTING ALL OF CUSTOMER
String query = "INSERT INTO tenants VALUES (?,?,?,?)";
//ResultSet
PreparedStatement statement = c.prepareStatement(query);
statement.setString(1, name);
statement.setDouble(2, leaseLength);
statement.setBoolean(3, rentPaid);
statement.setString(4, propID);
ResultSet rs = c.createStatement().executeQuery("SELECT * FROM tenants");
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<TableColumn.CellDataFeatures<ObservableList, String>, ObservableValue<String>>() {
public ObservableValue<String> call(TableColumn.CellDataFeatures<ObservableList, String> param) {
return new SimpleStringProperty(param.getValue().get(j).toString());
}
});
col.setMinWidth(200);
table.getColumns().addAll(col);
System.out.println("Column [" + i + "] ");
}
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
table.setItems(data);
} catch (Exception e) {
e.printStackTrace();
System.out.println("Error on Building Data");
}
}
This one is a single function button that is pulling input from TextFields. This is why the query is hardcoded here, but takes those inputs from the TextField.getText() method for the various inputs to add a "tenant" to the table. (Think rent management system.) The second rs after the initial prepared statement was my attempt to get the new info from the table after adding to possibly repopulate the table after it's been added to. I have not been able to test this as I am not able to make it past the Sqlite Database Locked Error
FINALLY
Here is a screenshot of what my table looks like if that helps bring this into context, and I need all tables to update as I "add" tenants, but obviously only once the add button has functionality, which it cannot currently due to the database locked error:
