结果集显示为空,但表中至少有1条记录。

huangapple go评论68阅读模式
英文:

ResultSet showing empty but there is at least 1 record in the table

问题

问题:我的ResultSet为空,但我知道表格("tblStudents")中至少有1条记录。

代码

package smartdatabase;

/**
 *
 * @author travi
 */
import java.sql.*;

public class Conn {

    private Connection connect = null;
    private Statement stmt = null;

    public Conn() {
    }

    public void getStudents() {
        try {

            Class.forName("org.apache.derby.jdbc.ClientDriver");
            connect = DriverManager.getConnection("jdbc:derby://localhost:1527/SmartData", "root", "password");
            String sql;
            sql = "SELECT FirstName, Surname FROM tblStudents";
            stmt = connect.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            if (rs.next()) {
                String Firstname = rs.getString("Firstname");
                String Surname = rs.getString("surname");
                System.out.println(Firstname + "    " + Surname);
            } else {
                System.out.println("空的ResultSet");
            }
        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

我已经尝试检查大小写敏感性,似乎不是问题。

我也尝试过 ResultSet rs = stmt.executeQuery(sql); 但没有改变任何东西。有什么建议吗?

英文:

ISSUE My ResultSet is empty but I know there is at least 1 record in the table ("tblStudents")?

CODE :

package smartdatabase;

/**
 *
 * @author travi
 */
import java.sql.*;

public class Conn {

    private Connection connect = null;
    private Statement stmt = null;

    public Conn() {
    }

    public void getStudents() {
        try {

            Class.forName("org.apache.derby.jdbc.ClientDriver");
            connect = DriverManager.getConnection("jdbc:derby://localhost:1527/SmartData", "root", 
"password");
            String sql;
            sql = "SELECT FirstName, Surname FROM tblStudents";
            stmt = connect.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            if (rs.next()) {
                String Firstname = rs.getString("Firstname");
                String Surname = rs.getString("surname");
                System.out.println(Firstname + "    " + Surname);
            }else{
                System.out.println("Empty ResultSet");
            }
        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

I have tried checking the case sensitivity and it doesn't seem to be the issue.

I also tried ResultSet rs = stmt.executeQuery(sql); and it didn't change anything. Any ideas?

答案1

得分: 2

rs.next() 方法将会把当前对象的指针移动到下一行。

因此,当你使用条件 if (rs.next()) 时,指针只会指向下一行,而你已经说过在数据库中只有一行记录,所以这个指针指向的是空。

第一次调用 next() 方法时,结果集的指针/游标将移动到第一行。再次调用 next() 方法时,结果集的游标将移动到第二行。就是这样进行的。

你可以调用 first() 或 beforeFirst() 方法将结果集的游标重置回第一行。

你可以在下面的方式中实现检查是否为空,将游标移动到第一个位置,如果结果集为空,它将返回 false:

选项 1:

if (!rs.first()) {
    System.out.println("Empty ResultSet");
} else {
    do {
        String Firstname = rs.getString("Firstname");
        String Surname = rs.getString("surname");
        System.out.println(Firstname + "    " + Surname);
    } while (rs.next());
}

更新:

选项 2:

if (!rs.isBeforeFirst()) {    
    System.out.println("Empty ResultSet"); 
} else {
    do {
        String Firstname = rs.getString("Firstname");
        String Surname = rs.getString("surname");
        System.out.println(Firstname + "    " + Surname);
    } while (rs.next());
}

选项 3:

if (!rs.isBeforeFirst() && rs.getRow() == 0) {    
    System.out.println("Empty ResultSet"); 
} else {
    do {
        String Firstname = rs.getString("Firstname");
        String Surname = rs.getString("surname");
        System.out.println(Firstname + "    " + Surname);
    } while (rs.next());
}

更新 2:

对于 DERBY 数据库,在 createStatement 中使用以下参数:

stmt = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                               ResultSet.CONCUR_READ_ONLY);

然后使用上述提到的 选项 1

英文:

The rs.next() method will move the pointer of the current object to the next row.

So when you place the condition if (rs.next()) the pointer is pointing to the next row only which is null as you have said you have one row entry in the DB.

On calling the next() method for the first time the result set pointer/cursor will be moved to the 1st row. Again calling the next() method for the second time the result set cursor will be moved to the 2nd row. By this way it goes.

You can call the first() or beforeFirst() methods to reset the ResultSet cursor back to the first row.

You can find more explanation at : ResultSet---isBeforeFirst()---AND--first()

Now for checking empty you need to implement in the below way, move the cursor to the first position, it will return false if the result set is empty:

Option 1 :

  if (!rs.first()) {
      System.out.println("Empty ResultSet");
  } else {
      do {
          String Firstname = rs.getString("Firstname");
          String Surname = rs.getString("surname");
          System.out.println(Firstname + "    " + Surname);
      } while (rs.next());
  }

UPDATE :

Option 2 :

if (!rs.isBeforeFirst()) {    
    System.out.println("Empty ResultSet"); 
} else {
      do {
          String Firstname = rs.getString("Firstname");
          String Surname = rs.getString("surname");
          System.out.println(Firstname + "    " + Surname);
      } while (rs.next());
}

Option 3 :

if (!rs.isBeforeFirst() && rs.getRow() == 0) {    
    System.out.println("Empty ResultSet"); 
} else {
      do {
          String Firstname = rs.getString("Firstname");
          String Surname = rs.getString("surname");
          System.out.println(Firstname + "    " + Surname);
      } while (rs.next());
}

UPDATE 2 :

For DERBY database use the below parameters in createStatement:

// Replace : stmt = connect.createStatement();
stmt = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                               ResultSet.CONCUR_READ_ONLY);

ResultSet rs = stmt.executeQuery(sql);

Then use the above option 1 mentioned.

huangapple
  • 本文由 发表于 2020年9月1日 14:55:38
  • 转载请务必保留本文链接:https://go.coder-hub.com/63682768.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定