从H2数据库有效地检索数据 [英] Retrieve data from H2 database efficiently

问题描述

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

我写了代码来保存表中的所有列，现在我想再次检索数据（从数据库加载）。

到目前为止这么好，但是我无法想出任何从数据库中检索数据的好方法，并抛光它，以便再次添加到我的表中。
我有一个方法为我的表添加一个新的行，所有列的数据，所以没有问题（类似 public void addRow（Object dataForFirstCol，Object dataForSecondCol [...] code>）但是我需要逐行获取数据，如何使用H2 / Java SQL呢？





我发现一个< c $ c> ResultSet 将对此有所帮助，但我仍然需要通过SQL逐行获取数据，例如 SELECT * FROM SOMEDATABASE 将不会帮助太多。

我真的很感谢一些帮助让我开始，目前我
不能想更多地请求列A 的所有数据，将所有
个必需行添加到表中，并为
该列（逐行）从上到下添加所有数据，然后请求列B 的数据，并将此行逐行添加到表列B 中。对我来说，通常似乎
是一个可能的解决方案，添加一个完整的行会更满意
，最有可能在数千行中表现更好。

是我用来创建数据库以及保存它的代码。 DROP查询只是用于测试目的。

  / ** 
 *将表内容保存到H2数据库
 * @param数据库的文件名
 * @param表以获取
 * / 
的内容public void save（文件名，JTable表）{
 
 //当用户编辑单元格时阻止保存
 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，）; 
语句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，
 +日期varchar（255），
 +StationName varchar（255），
 +Kilometrage varchar b $ b +BlockTime varchar（255），
 +WorkTime INT，
 +Worker varchar（255），
 +Task varchar（255） $ b +注释varchar（255），
 +ClosedState BOOLEAN，+）
）; 
 
 String sqlInsert =INSERT IN TO TASKS
 
 +（SeqNumber，
 +FBNumber，
 +ReportNumber，
 +BetraNumber，
 +Date，
 +StationName，
 +Kilometrage，
 +BlockTime ，
 +工作，
 +任务，
 +注释，
 +ClosedState）
 
 +VALUES 
 +（？// SeqNumber 
 +，？// FBNumber 
 +，？// ReportNumber 
 +，？// BetraNumber 
 +，？// Date 
 +，？// StationName 
 +，？// Kilometrage 
 +，？// BlockTime 
 +，？// WorkTime 
 +，？// Worker 
 +，？// Task 
 +，？//注释
 + ，？）; // 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（）; 
} 
 
 //这也只是临时代码来查看内容
 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自动生成的catch块
 e.printStackTrace（）; 
 DBDatabaseSystem.infoSystem.addError（Es gab ein SQL Problem beim Speichern der Datenbank）; 
} 
 
 
} 
  

解决方案

Yoy询问 retrive 到Jtable并包含 save 的代码和简单打印。 p>

很少有理论上的词汇：

• 在数据库中显示GUI数据可以达到非常大的计数
  行，GUI（swing Jtable，SWT表，web，没有事情）显示在
  下100行在开始。


• 接口公共接口TableModel
（如果用户滚动，可能没有）读取lazy。 c $ c>允许实现lazy，或
动态，或pagging（这样的单词存在于网络）实现，不幸的是官方支持/实现是不知道的（对我）。 Google显示几个，我不知道好还是不好。


• 几乎所有impelmentation必须从 select count（*）from ...
  实现 getRowCount（）; 然后获取带有norportable sql子句的数据，
  在H2中阅读关于此语法 select * ... LIMIT OFFSET 以获取具体行。

编辑：部分是关于垂直维度问题对我来说很难，不是母语英语等）。如果Yoy考虑水平维度，通常我得到从ResulSet行到映射< String，Object>的一行的所有对象（列）

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


}

解决方案

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

Few theoretical words:

• displaying in GUI data from database can reach very big count of rows, GUI (swing Jtable, SWT Table, web, no matters) displays under 100 rows at start. Lets get on the start only these rows, and rest (if user scrolls, maybe not) read lazy.
• Interface public interface TableModel allow to realize lazy, or dynamic, or pagging (such word are present in net) implementation, unfortunately official support/implementation is not known (to me). Google show few, I don't know 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 full clear for me, but probably You thing about this idea?

EDIT: part over is about vertical dimension (question is hard to me, im not native english etc). If Yoy think about horizontal dimension, usually I get all objects (columns) of one row from ResulSet row to Map<String,Object>

这篇关于从H2数据库有效地检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！