title: Retrieve data from H2 database efficiently
body: <p>in my current project I use a <strong>H2</strong> database to store the data from a JTable (to be precise the TableModel's data). </p>
  
  <p>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). </p>
  
  <p>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 <code>public void addRow(Object dataForFirstCol, Object dataForSecondCol [...]</code>) but I need to get the data row by row. How is that possible with H2 / Java SQL?</p>
  
  <p>What I found is that a <code>ResultSet</code> will be helpful for that, but I still need to get data row by row via SQL and something like <code>SELECT * FROM SOMEDATABASE</code> won't help much.</p>
  
  <blockquote>
    <p>I would really appreciate some help to get me started, currently I
    can't think of more than requesting all data for <strong>column A</strong>, 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 <strong>column B</strong> and also add
    this row by row to the tables <strong>column B</strong>. 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.</p>
  </blockquote>
  
  <p>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.</p>
  
  <pre><code>/**
   * 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 &lt; 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 &lt; 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");
      } 
  
  
  }
  </code></pre>