in my current project I use a H2 database to store the data from a JTable (to be precise the TableModel's data).
I have written the code to save all columns from my table and now I want to retrieve the data again (load from the database).
So far so good but I can't come up with any good way of retrieving the data from the database and polish it to be addable to my table again.
I have a method for my table to add a new row with data for all columns so that's no problem (something like public void addRow(Object dataForFirstCol, Object dataForSecondCol [...]) but I need to get the data row by row. How is that possible with H2 / Java SQL?
What I found is that a ResultSet will be helpful for that, but I still need to get data row by row via SQL and something like SELECT * FROM SOMEDATABASE won't help much.
I would really appreciate some help to get me started, currently I can't think of more than requesting all data for column A, add all necessary rows to the table and add all data from top to bottom for that column (row by row). Then request data for column B and also add this row by row to the tables column B. To me this generally seems to be a possible solution but adding a complete row would be way more satisfying and most likely way more performant for thousands of rows.
EDIT: This is the code I use to create the database as well as save it. The "DROP" query is just for testing purpose though.
/**
* Save tables content into H2 database
* @param filename of the database
* @param table to get the contents from
*/
public void save(File filename, JTable table) {
// prevent saving when user is editing a cell
if (table.isEditing()) {
table.getCellEditor().stopCellEditing();
}
try {
Class.forName("org.h2.Driver");
System.out.println(filename.toString());
Connection conn = DriverManager.getConnection("jdbc:h2:" + filename.toString(), "sa", "");
Statement state = conn.createStatement();
state.execute("DROP TABLE IF EXISTS TASKS");
state.execute("CREATE TABLE TASKS ("
+ "SeqNumber INT PRIMARY KEY,"
+ "FBNumber INT,"
+ "ReportNumber INT,"
+ "BetraNumber INT,"
+ "Date varchar(255),"
+ "StationName varchar(255),"
+ "Kilometrage varchar(255),"
+ "BlockTime varchar(255),"
+ "WorkTime INT,"
+ "Worker varchar(255),"
+ "Task varchar(255),"
+ "Comments varchar(255),"
+ "ClosedState BOOLEAN," + ")"
);
String sqlInsert = "INSERT INTO TASKS "
+ "(SeqNumber, "
+ "FBNumber, "
+ "ReportNumber, "
+ "BetraNumber, "
+ "Date, "
+ "StationName, "
+ "Kilometrage, "
+ "BlockTime, "
+ "WorkTime, "
+ "Worker, "
+ "Task, "
+ "Comments, "
+ "ClosedState) "
+ "VALUES"
+ "(?" // SeqNumber
+ ",?" // FBNumber
+ ",?" // ReportNumber
+ ",?" // BetraNumber
+ ",?" // Date
+ ",?" // StationName
+ ",?" // Kilometrage
+ ",?" // BlockTime
+ ",?" // WorkTime
+ ",?" // Worker
+ ",?" // Task
+ ",?" // Comments
+ ",?)"; // ClosedState
for (int rowIndex = 0; rowIndex < table.getModel().getRowCount(); rowIndex++) {
PreparedStatement sqlState = conn.prepareStatement(sqlInsert);
sqlState.setInt(COLUMN_SEQ_NUMBER, getSeqNumber(table, rowIndex));
sqlState.setInt(COLUMN_FB_NUMBER, getFBNumber(table, rowIndex));
sqlState.setInt(COLUMN_REPORT_NUMBER, getRepNumber(table, rowIndex));
sqlState.setInt(COLUMN_BETRA_NUMBER, getBetraNumber(table, rowIndex));
sqlState.setString(COLUMN_DATE, getDate(table, rowIndex));
sqlState.setString(COLUMN_STATION_NAME, getStationName(table, rowIndex));
sqlState.setString(COLUMN_KILOMETRAGE, getKilometrage(table, rowIndex));
sqlState.setString(COLUMN_BLOCK_TIME, getBlockTime(table, rowIndex));
sqlState.setInt(COLUMN_WORK_TIME, getWorkTime(table, rowIndex));
sqlState.setString(COLUMN_WORKER, getWorker(table, rowIndex));
sqlState.setString(COLUMN_TASK, getTask(table, rowIndex));
sqlState.setString(COLUMN_COMMENTS, getComments(table, rowIndex));
sqlState.setBoolean(COLUMN_CLOSED_STATE, getClosedState(table, rowIndex));
sqlState.executeUpdate();
}
// This is also just temporary code to see the contents
ResultSet dbContent = conn.createStatement().executeQuery("SELECT * FROM TASKS");
while(dbContent.next()) {
for (int i = 1; i+1 < DBDatabaseSystem.table.getColumnCount(); i++) {
System.out.println(dbContent.getString(i));
}
}
conn.close();
unsavedChanges = false;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
DBDatabaseSystem.infoSystem.addError("Es gab ein SQL Problem beim Speichern der Datenbank");
}
}
