有效地从H2数据库中检索数据

in my current project I use a H2 database to store the data from a JTable (to be precise the TableModel's data).

在我当前的项目中，我使用H2数据库来存储来自JTable的数据（确切地说是TableModel的数据）。

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?

到目前为止这么好但我无法想出任何从数据库中检索数据的好方法，并将其抛光以便再次添加到我的表中。我有一个方法让我的表为所有列添加一个包含数据的新行，这样就没有问题了（比如public void addRow（Object dataForFirstCol，Object dataForSecondCol [...]），但我需要逐行获取数据。这怎么可能用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.

我发现ResultSet会对此有所帮助，但我仍然需要通过SQL逐行获取数据，而像SELECT * FROM SOMEDATABASE这样的东西也无济于事。

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.

我真的很感谢帮助我开始的一些帮助，目前我不能想到的只是请求A列的所有数据，将所有必要的行添加到表中并为该列添加从上到下的所有数据（逐行）。然后请求列B的数据，并将这一行逐行添加到表列B.对我来说，这通常似乎是一种可能的解决方案，但添加一个完整的行将更令人满意，并且很可能对数千行更具性能。

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.

编辑：这是我用来创建数据库以及保存它的代码。 “DROP”查询仅用于测试目的。

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


}

1 个解决方案

#1

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

您询问有关Jtable的回溯并包含用于保存和简单打印的代码。

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.
从数据库显示GUI数据可能意味着很多行。 GUI（swing Jtable，SWT Table，web等）在开始时显示在100行以下。我认为你应该把这些行放在一开始。
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.
接口公共接口TableModel检索延迟或动态，或者分页（这样的字存在于net中）实现，遗憾的是官方支持/实现未知（对我来说）。谷歌显示了一些，我不知道他们是好还是坏。
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?
几乎所有的impelmentation都必须从...中选择count（*）来实现getRowCount（）;然后用norportable sql子句获取数据，在H2中读取这个语法select * ... LIMIT OFFSET得到具体的行。问题对我来说不是很清楚，但可能你知道这个想法吗？

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>

编辑：部分结束是关于垂直维度（这个问题对我来说很难，我不是母语为英语的人）。如果你考虑水平维度，通常我会从ResulSet行获取一行的所有对象（列）到Map ，object>

#1