mysql - 当数据明显更多时，为什么 resultSet.next() 不前进？

Question

所以我很难理解为什么当明显有更多条目要迭代时，已执行查询的结果集不会继续前进。我有一个名为 all_projects 的表，当我运行此查询时:

SELECT project_title, created_date, isActive FROM all_projects WHERE project_lead='myUser' ORDER BY created_date DESC;

在我的 PSQL shell 中，这是我得到的结果:

列数据类型有:String、Timestamp 和 Boolean。

我试图获取每一行，从中创建一个 Array[String]，最终创建一个字符串数组列表(List[Array[String]])

当我使用 resultSet.next() 对其进行迭代时，它能够检索前两个值，但是当我在获取第一个时间戳值后调用 next 时，它失败并返回 false。

下面是我的代码 - 充满了很多 println 调试语句以查看会发生什么，堆栈/和打印跟踪将在底部。

 def getAll(userName: String, db: Database): List[Array[String]] = {
    val tablesQuery = s"SELECT project_title, created_date, isActive FROM all_projects WHERE project_lead=? ORDER BY created_date DESC;"
    var returnResult = new ListBuffer[Array[String]]
    db.withConnection { conn =>
      val ps = conn.prepareStatement(tablesQuery)
      ps.setString(1, userName)
      val qryResult = ps.executeQuery()
      val columnCount = qryResult.getMetaData.getColumnCount
      println("RETRIEVED THIS MANY COLUMNS: " + columnCount)
      while (qryResult.next()) {
        println("Achieved next in while loop >>>>>>>>>>>")
        val row = new Array[String](columnCount)
        for (i <- 0 to columnCount - 1) {
          println(s"Inserting into Array($i) from the column index(${i + 1})")
          if (i < 2) {
            println("Tried to get string: ");
            row(i) = qryResult.getString(i + 1)
          } else { // note I also just tried to keep it all as .getString()
            println("Tried to get boolean: ");
            row(i) = qryResult.getBoolean(i+1).toString
          } // retrieve by column index SQL columns start at 1
          println("Row before we move on: " + row.mkString(", "))
          if (i <= columnCount - 2) {
            println("Called next? -> " + qryResult.next())
          }
        }
        returnResult += row
      }
    }
    returnResult.toList
  }

这是生成的打印堆栈，本来应该没问题，但如您所见，当光标位于第一个时间戳值。

THIS MANY COLUMNS: 3
Achieved next in while loop >>>>>>>>>>>
Inserting into Array(0) from the column index(1)
Tried to get string: 
Row before we move on: Wild Elephants of Mexico, null, null
Called next? -> true
Inserting into Array(1) from the column index(2)
Tried to get string: 
Row before we move on: Wild Elephants of Mexico, 2017-08-05 11:00:44.078232, null
Called next? -> false
Inserting into Array(2) from the column index(3)
Tried to get boolean: 
[error] o.j.ResultSetLogger - java.sql.ResultSet.getBoolean: 
throws exception: org.postgresql.util.PSQLException: ResultSet not positioned properly, perhaps you need to call next.
org.postgresql.util.PSQLException: ResultSet not positioned properly, perhaps you need to call next.

这里发生了什么？

Answer 1

您正在调用 qryResult.next()，您的意思是处理单行，破坏了所有内容。 (男孩，你让你的生活变得太困难了。)

ResultSet 将查询结果表示为指向查询结果单行的可移动指针。您一次处理一行，然后调用 next() 仅当您完全完成该行的处理时。

让我们让事情变得简单得多。 (我只是把它写在网页上，没有时间检查或编译它，所以请原谅我的嘘声。)

def handleRow( qryResult : ResultSet, columnCount : Int ) : Array[String] = {
   (1 to columnCount).map( i => qryResult.getString(i) ).toArray
}

def getAll(userName: String, db: Database): List[Array[String]] = {
  val tablesQuery = s"SELECT project_title, created_date, isActive FROM all_projects WHERE project_lead=? ORDER BY created_date DESC;"
  db.withConnection { conn =>
    val ps = conn.prepareStatement(tablesQuery)
    ps.setString(1, userName)
    val qryResult = ps.executeQuery()
    val columnCount = qryResult.getMetaData.getColumnCount
    println("RETRIEVED THIS MANY COLUMNS: " + columnCount)
    var buffer = new ListBuffer[Array[String]]
    while (qryResult.next()) {
      buffer += handleRow( qryResult, columnCount )
    }
    buffer.toList
  }
}