java.sql.Statement或java.sql.PreparedStatement - 带参数的可滚动结果集

In my java app, it seems to use parameters in my query to the database, I need to utilize the PreparedStatement. However at the same time, I would like to use the resultset from the statement in forward/backward mode (scrollable) PreparedStatement does not seem to offer setting the scrollable mode Statement does not seem to offer parameters.

在我的java应用程序中，它似乎在我的查询中使用参数到数据库，我需要利用PreparedStatement。但同时，我想在前进/后退模式（可滚动）中使用语句的结果集PreparedStatement似乎没有提供设置可滚动模式Statement似乎没有提供参数。

Seems like a basic question..but nothing jumping out at me (other than using Statement and constructing the SQL without parameters). Is there really no way to supply parameters to a Statement..or have a preparedstatement scrollable? Am I missing something?

看起来像一个基本问题..但没有什么东西跳出来（除了使用Statement和构造没有参数的SQL）。真的没有办法为Statement提供参数。或者有一个可滚动的预备语句吗？我错过了什么吗？

            conn = Utility.getConnection();

            tmpSQL = "SELECT * FROM " + baseTable + " WHERE " + filterCriteria
                    + " ORDER BY " + sortCriteria;

//method 1

Statement stmt = conn.createStatement(
                       ResultSet.TYPE_SCROLL_INSENSITIVE,
                       ResultSet.CONCUR_UPDATABLE);

rset = stmt.executeQuery(tmpSQL);  //not using any parameters!


//method 2

            PreparedStatement pStatement = conn.prepareStatement(tmpSQL);  //not scrollable!

            if (params != null)
                for (int i = 0; i < params.size(); i++) {

                    pStatement.setString(i + 1,
                            ((Parameter) params.get(i)).getStringValue());

                }

            rset = pStatement.executeQuery();

2 个解决方案

#1

Use

使用

PreparedStatement pStatement = conn.prepareStatement(tmpSQL,
                                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_UPDATABLE);

Java Doc Info

Java Doc Info

Then to get the count of records in your ResultSet, use rset.last() followed by rset.getRow(). Then use rset.beforeFirst() to put the cursor back to where it was initially.

然后，要获取ResultSet中的记录计数，请使用rset.last（），然后使用rset.getRow（）。然后使用rset.beforeFirst（）将光标放回原来的位置。

#2

Some initial background comments

Scrollability is mostly depending on the underlying database. Even though JDBC has a method to scroll back, it is not implemented e.g. in Oracle JDBC driver.

可滚动性主要取决于底层数据库。即使JDBC有一种向后滚动的方法，它也没有实现，例如在Oracle JDBC驱动程序中。

I would suggest to avoid of scrolling the result set. In fact even if it works for some databases, it is quite inefficient to implement. Also inefficient to use on the GUI, since each scrolling would then trigger a database operation, which is slow.

我建议避免滚动结果集。实际上，即使它适用于某些数据库，实现效率也很低。在GUI上使用效率也很低，因为每次滚动都会触发数据库操作，这很慢。

The usual approach is to load all rows to a container (e.g. List<...> ) and process that, if you have a moderate number of rows (say up to 1000 rows). If you have a lot more rows, then:

通常的方法是将所有行加载到容器（例如List <...>）并处理该行，如果您有适度的行数（例如最多1000行）。如果你有更多的行，那么：

• think it over if you really need to read that many rows. For example, if this is a GUI list, it may not make sense loading 1 million rows, since the human user will not one-by-one scroll trough all 1 million rows. Probably a better filtering and/or pagination would make sense.
• 如果你真的需要阅读那么多行，请考虑一下。例如，如果这是一个GUI列表，加载100万行可能没有意义，因为人类用户不会逐行滚动所有100万行。可能更好的过滤和/或分页是有意义的。
• if you really need all the rows for business side processing, then think it over. Pulling all rows from the database to the app for processing is a super-inefficient programming pattern. Use stored procedures, or packages (Oracle) to process your data on the database side.
• 如果您确实需要所有行进行业务端处理，那么请仔细考虑。将所有行从数据库拉到应用程序进行处理是一种超低效的编程模式。使用存储过程或包（Oracle）在数据库端处理数据。
• but if you really really need to pull like 1 millon rows to the app for processing, do the processing in a streaming-manner. I.e. instead of first fetching 1 million rows to the memory and then processing it, fetch one row, process it, fetch another row, process it. This also explains why back-scrolling is usually not supported: that would require the driver or the db to actually hold in memory one million rows of the result of your select, because you might want to scroll back.
• 但如果您确实需要将1亿行拉到应用程序进行处理，请以流式方式进行处理。即而不是首先向内存中提取100万行然后处理它，获取一行，处理它，获取另一行，处理它。这也解释了为什么通常不支持反向滚动：这需要驱动程序或数据库实际在内存中保存一百万行的结果，因为您可能想要向后滚动。

To solve your question

To get the count of records, execute a separate statement with select count(*). Then execute another select to actually read the records and fetch them (only forward).

要获取记录计数，请使用select count（*）执行单独的语句。然后执行另一个选择以实际读取记录并获取它们（仅向前）。

It is much faster than reading all records just to count them.

它比读取所有记录要快得多。