java - Retrieve data from H2 database efficiently

Question

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");
    } 


}

Answer 1

You ask about retrive to Jtable and include code for save and simple print.

Here are a some theoretical ideas:

• Showing GUI data from database can mean lots of rows. GUI (swing Jtable, SWT Table, web, etc.) displays under 100 rows at start. I think that you should put these rows at the start.
• Interface public interface TableModel to retrieve lazy or dynamic, or paging (such word are present in net) implementation, unfortunately official support/implementation is not known (to me). Google shows a few, I don't know if they are good or bad.
• almost all impelmentation must do select count(*) from ... to implement getRowCount(); then get data with norportable sql clause, in H2 read about this syntax select * ... LIMIT OFFSET to get concrete rows. Question isn't that clear to me, but probably you know about this idea?

EDIT: part over is about vertical dimension (this question is difficult for me, I'm not a native English speaker). If you think about horizontal dimension, usually I get all objects (columns) of one row from ResulSet row to Map<String,Object>