java 在 JDBC 应用程序中向前和向后移动 ResultSet 游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15819153/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
moving ResultSet cursor forward and backward in a JDBC application
提问by Anafam
I'm working on a Stock Inventory System software which connects to a Ms Sql server using a JDBC ODBC connection. I want to move the Result Set cursor to the next row and backwards. The connection works and the program can retrieve the fields from the database, so there's no issue with that.
我正在开发使用 JDBC ODBC 连接连接到 Ms Sql 服务器的 Stock Inventory System 软件。我想将结果集光标移动到下一行并向后移动。连接有效，程序可以从数据库中检索字段，因此没有问题。
The code that I have here is a on a button labeled "Next". When you click this button it should move to the next row in the database and retrieve the data from that row. The data retrieved should be displayed in the "textfields".The problem is when I click next nothing happens?
我在这里的代码是一个标记为“下一步”的按钮。当您单击此按钮时，它应该移动到数据库中的下一行并从该行检索数据。检索到的数据应显示在“文本字段”中。问题是当我点击下一步时什么都没有发生？
private void NextBtnActionPerformed(java.awt.event.ActionEvent evt) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbc");
Connection con;
con = DriverManager.getConnection("jdbc:odbc:StockInventory","sa","123");
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
String query = "select * from Stocktbl";
ResultSet rs;
rs = stmt.executeQuery(query);
if(!rs.isLast()) {
rs.next();
TxtStockid.setText(rs.getString("StockId"));
TxtItem.setText(rs.getString("ItemName"));
TxtQuantity.setText(rs.getString("Quantity"));
TxtUnitprice.setText(rs.getString("UnitPrice"));
TxtNetprice.setText(rs.getString("NetPrice"));
TxtUnitweight.setText(rs.getString("UnitWeight"));
TxtNetweight.setText(rs.getString("Netweight"));
TxtDescription.setText(rs.getString("Description"));
}
rs.close();
stmt.close();
con.close();
}
catch (SQLException ex)
{
Logger.getLogger(StockScr.class.getName()).log(Level.SEVERE, null, ex);
}
catch (ClassNotFoundException ex)
{
Logger.getLogger(StockScr.class.getName()).log(Level.SEVERE, null, ex);
}
}
Here is the rest of the coding for the program, The "Next" button is added to this Panel.
这是程序的其余编码，“下一步”按钮添加到此面板。
public class StockScr extends javax.swing.JPanel {
ResultSet rs;
Connection con = null;
public StockScr() {
initComponents();
ShowTable();
}
void ShowTable(){
try {
Class.forName("sun.jdbc.odbc.JdbcOdbc");
Connection con;
con = DriverManager.getConnection("jdbc:odbc:StockInventory","sa","123");
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
String query = "select * from Stocktbl";
ResultSet rs;
rs = stmt.executeQuery(query);
rs.first();
TxtStockid.setText(rs.getString("StockId"));
TxtItem.setText(rs.getString("ItemName"));
TxtQuantity.setText(rs.getString("Quantity"));
TxtUnitprice.setText(rs.getString("UnitPrice"));
TxtNetprice.setText(rs.getString("NetPrice"));
TxtUnitweight.setText(rs.getString("UnitWeight"));
TxtNetweight.setText(rs.getString("Netweight"));
TxtDescription.setText(rs.getString("Description"));
rs.close();
stmt.close();
con.close();
}
catch (SQLException ex)
{
Logger.getLogger(StockScr.class.getName()).log(Level.SEVERE, null, ex);
}
catch (ClassNotFoundException ex)
{
Logger.getLogger(StockScr.class.getName()).log(Level.SEVERE, null, ex);
}
}
public void fetchResultSet()
{
try {
if(con==null || con.isClosed())
{
Class.forName("sun.jdbc.odbc.JdbcOdbc");
con = DriverManager.getConnection("jdbc:odbc:StockInventory","sa","123");
}
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
String query = "select * from Stocktbl";
rs = stmt.executeQuery(query);
}catch(Exception ex){
System.out.println(ex);
Logger.getLogger(StockScr.class.getName()).log(Level.SEVERE, null, ex);
}
try
{
if(con != null)
{
con.close();
}
}catch(Exception ex){
}
}
private void NextBtnActionPerformed(java.awt.event.ActionEvent evt) {
try
{
if (rs == null)
{
fetchResultSet();
}
if (rs!=null)
{
if (rs.next())
{
TxtStockid.setText(rs.getString("StockId"));
TxtItem.setText(rs.getString("ItemName"));
TxtQuantity.setText(rs.getString("Quantity"));
TxtUnitprice.setText(rs.getString("UnitPrice"));
TxtNetprice.setText(rs.getString("NetPrice"));
TxtUnitweight.setText(rs.getString("UnitWeight"));
TxtNetweight.setText(rs.getString("Netweight"));
TxtDescription.setText(rs.getString("Description"));
}
else
{
rs = null;
}
}
}catch(Exception ex){
System.out.println(ex);
}
}
采纳答案by Vishal K
You should creat Connection to database and fetch fresh record only once before you are clicking nextbutton. And then keep moving forward using rs.nextin NextBtnActionPerformedmethod.
在单击next按钮之前，您应该创建与数据库的连接并仅获取一次新记录。然后使用rs.nextinNextBtnActionPerformed方法继续前进。
For example you should have a method call fetchResultSet as follows and that should be called beforenextbutton is clicked.
例如，您应该有一个方法调用 fetchResultSet 如下，并且应该在next单击按钮之前调用。
ResultSet rs;
Connection con = null;
public void fetchResultSet()
{
try {
if(con==null || con.isClosed())
{
Class.forName("sun.jdbc.odbc.JdbcOdbc");
con = DriverManager.getConnection("jdbc:odbc:StockInventory","sa","123");
}
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
String query = "select * from Stocktbl";
rs = stmt.executeQuery(query);
}catch(Exception ex)
{
System.out.println(ex);
Logger.getLogger(StockScr.class.getName()).log(Level.SEVERE, null, ex);
try
{
if(con != null)
{
con.close();
}
}catch(Exception x){}
}
}
And then your NextBtnActionPerformedshould be like this:
然后你NextBtnActionPerformed应该是这样的：
private void NextBtnActionPerformed(java.awt.event.ActionEvent evt)
{
try
{
if (rs == null)
{
fetchResultSet();
}
if (rs!=null)
{
if (rs.next())
{
TxtStockid.setText(rs.getString("StockId"));
TxtItem.setText(rs.getString("ItemName"));
TxtQuantity.setText(rs.getString("Quantity"));
TxtUnitprice.setText(rs.getString("UnitPrice"));
TxtNetprice.setText(rs.getString("NetPrice"));
TxtUnitweight.setText(rs.getString("UnitWeight"));
TxtNetweight.setText(rs.getString("Netweight"));
TxtDescription.setText(rs.getString("Description"));
}
else
{
rs = null;
}
}
}catch(Exception ex){System.out.println(ex);}
}
回答by Reimeus
You need to call rs.next()beforeyou can call rs.isLast(). The call to rs.isLast()does not offer much in terms of functionality:
你需要先打电话，rs.next()然后才能打电话rs.isLast()。对功能的调用rs.isLast()并没有提供太多功能：
if (rs.next()) {
TxtStockid.setText(rs.getString("StockId"));
...
With this approach, you're not advancing the ResultSetcursor so get the same data each time a button is clicked. A WHEREclause would be required to return a differentrow. If the total amount of records is not large, caching the data would be the preferred approach.
使用这种方法，您不会前进ResultSet光标，因此每次单击按钮时都会获得相同的数据。需要一个WHERE子句来返回不同的行。如果记录总量不大，缓存数据将是首选方法。
Caching is suitable where the amount of items is relatively small and large scale scaling up is not required. In this case, you could load all data into an ArrayList<MyStockItem>at startup and navigate though the Listrather than making database connections. Products such as EHCacheare useful for this purpose.
缓存适用于项目数量相对较少且不需要大规模扩展的情况。在这种情况下，您可以将所有数据加载到ArrayList<MyStockItem>启动时并浏览List而不是建立数据库连接。如产品的EHCache是用于这一目的。
回答by Micha? Tabor
You create new result set on every button click. So new result set has it's cursor on first position and that's why you always get the same record while clicking it. To solve it you need to create result set field, then fill it with records in your main class' constrcutor and then operate on this result set.
您在每次单击按钮时都会创建新的结果集。因此，新结果集的光标位于第一个位置，这就是为什么单击它时总是会获得相同的记录。要解决它，您需要创建结果集字段，然后用主类的构造函数中的记录填充它，然后对该结果集进行操作。
回答by M Sach
You should use
你应该使用
while(rs.next()) instead of if(!rs.isLast()) and then rs.next()
I dont think resultset provide the api to move backward. Question is why do you need it.
我不认为结果集提供了向后移动的 api。问题是你为什么需要它。
