JDBC驱动程序抛出“ResultSet Closed”空的ResultSet异常

问题描述

我正在使用SELECT语句执行查询。

如果我得到一个空的 ResultSet （0行），那么当调用 getString（1）。



没有很多以前的JDBC经验，我的理论（我无法通过JavaDocs来确认 ResultSet ）是

• getString（1）不工作在空（零行）结果集（按设计或由于错误）


• ResultSet 的open标志设置为 $ b

  我看到这个，但不确定是否相关。

  
  
  

  我的风格是：

  
  
  1. 上述理论是否正确？
  
  
  2. 这是一个bug吗？特征？ （如果有的话请问有人可以指出文档吗？）
  
  
  3. 它是特定于SQLIte的JDBC或通用的 ResultSet 在所有的JDBC司机？
  
  
  4. 正确的方式是做什么？？
  
  

  对于＃4，我的解决方案是在 executeQuery（）之后使用 isFirst（）检查结果集中是否有任何行。这是最好的做法吗？

  
  
  

  （我也可以简单地选择一个计数insetad，因为我真的不需要一个结果集，只是零非零的标志，但是我想知道正确的事情要做，如果我关心选择的结果）

  
  
  

  谢谢！

  解决方案

  空或不，但执行以下操作始终是错误：

    resultSet = statement.executeQuery（sql）; 
   string = resultSet.getString（1）; //史诗失败光标尚未设置。 
    

  这不是一个错误。这是 。每个提到它。您需要使用 next（）设置ResultSet的游标，才能访问任何数据。

  
  
  

  真的感兴趣，不管是否存在唯一行 ，然后只需检查 next（）的结果。例如在虚构的 UserDAO 类：

    public boolean exists username，String password）throws SQLException {
   boolean exists = false; 
   
   try（
  连接连接= database.getConnection（）; 
   PreparedStatement语句= connection.prepareStatement（SELECT id FROM user WHERE username =？AND password = MD5（？） ）; 
  ）{
   statement.setString（1，username）; 
   statement.setString（2，password）; 
   
   try（ResultSet resultSet = statement.executeQuery（））{
   exists = resultSet.next（）; 
  } 
  } 
   
  返回存在; 
  } 
    

  如果您只希望零或一个行，然后只需执行以下操作：

    public User find（String username，String password）throws SQLException {
   User user = null; 
   
   try（
  连接连接= database.getConnection（）; 
   PreparedStatement语句= connection.prepareStatement（SELECT id，username，email，birthdate FROM user WHERE username =？AND password = MD5（？））; 
  ）{
   statement.setString（1，username）; 
   statement.setString（2，password）; 
   
   try（resultSet = statement.executeQuery（））{
   if（resultSet.next（））{
   user = new User（
   resultSet.getLong id），
   resultSet.getString（username），
   resultSet.getString（email），
   resultSet.getDate（birthdate））; 
  } 
  } 
  } 
   
   return user; 
  } 
    

  然后只在business / domain对象中相应处理，例如

  
  
  

   用户user = userDAO.find（用户名，密码）; 
   
   if（user！= null）{
   //登录？ 
  } 
   else {
   //显示错误？ 
  } 
    

  如果您只希望零或许多行，然后只需执行以下操作：

    public List< User> list（）throws SQLException {
  列表< User> users = new ArrayList< User>（）; 
   
   try（
  连接连接= database.getConnection（）; 
   PreparedStatement语句= connection.prepareStatement（SELECT id，username，email，birthdate FROM user）; 
   ResultSet resultSet = statement.executeQuery（）; 
  ）{
   while（resultSet.next（））{
   users.add（new User（
   resultSet.getLong id），
   resultSet.getString（username），
   resultSet.getString（email），
   resultSet.getDate（birthdate）））; 
  } 
  } 
   
  返回用户; 
  } 
    

  然后只在business / domain对象中相应处理，例如

  
  
  

   列表<用户> users = userDAO.list（）; 
   
   if（！users.isEmpty（））{
   int count = users.size（）; 
   // ... 
  } 
   else {
   //帮助，没有用户？ 
  } 
    

  
  

  I have a problem in JDBC driver for SQLite.

  I am executing a query with SELECT statement.

  If I get an empty ResultSet (0 rows) then I see a "Closed ResultSet" exception thrown when calling getString(1).

  Without much prior JDBC experience, my theory (which I could not confirm via JavaDocs for ResultSet) is that

  • getString(1) does NOT work on an empty (zero-row) resultset (by design or due to a bug)
  • ResultSet's "open" flag is set to false on zero rows (again, by design or a bug)

  I saw this bug report but am not sure if it's related.

  My qeustions are:

  1. Is the theory above correct?
  2. Is it a bug? Feature? (and if so, can someone point to documentation please?)
  3. Is it specific to SQLIte's JDBC or to generic ResultSet in all JDBC drivers?
  4. What is the correct way of doing stuff like that??

  For #4, my solution was to use isFirst() call right after executeQuery() to check whether any rows are there in result set. Is this the best practice approach?

  (I could also have simply selected a count insetad since I didn't really need a result set, merely zero-nonzero flag, but I want to know the correct thingh to do if I did care about select's results)

  Thanks!

  解决方案

  Empty or not, but doing the following is always faulty:

  resultSet = statement.executeQuery(sql);
  string = resultSet.getString(1); // Epic fail. The cursor isn't set yet.
  

  This is not a bug. This is documented behaviour. Every decent JDBC tutorial mentions it. You need to set the ResultSet's cursor using next() before being able to access any data.

  If you're actually interested whether the supposedly unique row exist or not, then just check the outcome of next(). For example in a fictive UserDAO class:

  public boolean exist(String username, String password) throws SQLException {
      boolean exist = false;
  
      try (
          Connection connection = database.getConnection();
          PreparedStatement statement = connection.prepareStatement("SELECT id FROM user WHERE username = ? AND password = MD5(?)");
      ) {
          statement.setString(1, username);
          statement.setString(2, password);
  
          try (ResultSet resultSet = statement.executeQuery()) {
              exist = resultSet.next();
          }
      }
  
      return exist;
  }
  

  If you actually expect only zero or one row, then just do something like:

  public User find(String username, String password) throws SQLException {
      User user = null;
  
      try (
          Connection connection = database.getConnection();
          PreparedStatement statement = connection.prepareStatement("SELECT id, username, email, birthdate FROM user WHERE username = ? AND password = MD5(?)");
      ) {
          statement.setString(1, username);
          statement.setString(2, password);
  
          try (resultSet = statement.executeQuery()) {
              if (resultSet.next()) {
                  user = new User(
                      resultSet.getLong("id"),
                      resultSet.getString("username"),
                      resultSet.getString("email"),
                      resultSet.getDate("birthdate"));
              }
          }
      }
  
      return user;
  }
  

  and then just handle it accordingly in the business/domain object, e.g.

  User user = userDAO.find(username, password);
  
  if (user != null) {
      // Login?
  }
  else {
      // Show error?
  }
  

  If you actually expect only zero or many rows, then just do something like:

  public List<User> list() throws SQLException {
      List<User> users = new ArrayList<User>();
  
      try (
          Connection connection = database.getConnection();
          PreparedStatement statement = connection.prepareStatement("SELECT id, username, email, birthdate FROM user");
          ResultSet resultSet = statement.executeQuery();
      ) {
          while (resultSet.next()) {
              users.add(new User(
                  resultSet.getLong("id"),
                  resultSet.getString("username"),
                  resultSet.getString("email"),
                  resultSet.getDate("birthdate")));
          }
      }
  
      return users;
  }
  

  and then just handle it accordingly in the business/domain object, e.g.

  List<User> users = userDAO.list();
  
  if (!users.isEmpty()) {
      int count = users.size();
      // ...
  }
  else {
      // Help, no users?
  }
  

  这篇关于JDBC驱动程序抛出“ResultSet Closed”空的ResultSet异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！