像处理 Scala 流一样处理 SQL ResultSet
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9636545/
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
Treating an SQL ResultSet like a Scala Stream
提问by Ralph
When I query a database and receive a (forward-only, read-only) ResultSet back, the ResultSet acts like a list of database rows.
当我查询一个数据库并收到一个（只进，只读）ResultSet 时，ResultSet 就像一个数据库行列表。
I am trying to find some way to treat this ResultSet like a Scala Stream. This will allow such operations as filter, map, etc., while not consuming large amounts of RAM.
我试图找到某种方法来像 Scala 一样对待这个 ResultSet Stream。这将允许这样的操作的filter，map等等，而不是消耗大量的内存。
I implemented a tail-recursive method to extract the individual items, but this requires that all items be in memory at the same time, a problem if the ResultSet is very large:
我实现了一个尾递归方法来提取单个项目，但这要求所有项目同时在内存中，如果 ResultSet 非常大，则会出现问题：
// Iterate through the result set and gather all of the String values into a list
// then return that list
@tailrec
def loop(resultSet: ResultSet,
accumulator: List[String] = List()): List[String] = {
if (!resultSet.next) accumulator.reverse
else {
val value = resultSet.getString(1)
loop(resultSet, value +: accumulator)
}
}
回答by elbowich
I didn't test it, but why wouldn't it work?
我没有测试它，但为什么它不起作用？
new Iterator[String] {
def hasNext = resultSet.next()
def next() = resultSet.getString(1)
}.toStream
回答by hraban
Utility function for @elbowich's answer:
@elbowich 答案的实用函数：
def results[T](resultSet: ResultSet)(f: ResultSet => T) = {
new Iterator[T] {
def hasNext = resultSet.next()
def next() = f(resultSet)
}
}
Allows you to use type inference. E.g.:
允许您使用类型推断。例如：
stmt.execute("SELECT mystr, myint FROM mytable")
// Example 1:
val it = results(stmt.resultSet) {
case rs => rs.getString(1) -> 100 * rs.getInt(2)
}
val m = it.toMap // Map[String, Int]
// Example 2:
val it = results(stmt.resultSet)(_.getString(1))
回答by Jeroen Minnaert
This sounds like a great opportunity for an implicit class. First define the implicit class somewhere:
对于隐式类来说，这听起来是一个很好的机会。首先在某处定义隐式类：
import java.sql.ResultSet
object Implicits {
implicit class ResultSetStream(resultSet: ResultSet) {
def toStream: Stream[ResultSet] = {
new Iterator[ResultSet] {
def hasNext = resultSet.next()
def next() = resultSet
}.toStream
}
}
}
Next, simply import this implicit class wherever you have executed your query and defined the ResultSet object:
接下来，只需在您执行查询并定义 ResultSet 对象的任何位置导入这个隐式类：
import com.company.Implicits._
Finally get the data out using the toStream method. For example, get all the ids as shown below:
最后使用 toStream 方法将数据取出。例如，获取如下所示的所有 id：
val allIds = resultSet.toStream.map(result => result.getInt("id"))
回答by Greg
i needed something similar. Building on elbowich's very cool answer, I wrapped it a bit, and instead of the string, I return the result (so you can get any column)
我需要类似的东西。基于肘部非常酷的答案，我将其包裹了一下，而不是字符串，我返回结果（因此您可以获得任何列）
def resultSetItr(resultSet: ResultSet): Stream[ResultSet] = {
new Iterator[ResultSet] {
def hasNext = resultSet.next()
def next() = resultSet
}.toStream
}
I needed to access table metadata, but this will work for table rows (could do a stmt.executeQuery(sql) instead of md.getColumns):
我需要访问表元数据，但这适用于表行（可以执行 stmt.executeQuery(sql) 而不是 md.getColumns）：
val md = connection.getMetaData()
val columnItr = resultSetItr( md.getColumns(null, null, "MyTable", null))
val columns = columnItr.map(col => {
val columnType = col.getString("TYPE_NAME")
val columnName = col.getString("COLUMN_NAME")
val columnSize = col.getString("COLUMN_SIZE")
new Column(columnName, columnType, columnSize.toInt, false)
})
回答by Brendan
Because ResultSet is just a mutable object being navigated by next, we need to define our own concept of a next row. We can do so with an input function as follows:
因为 ResultSet 只是一个被 next 导航的可变对象，我们需要定义我们自己的下一行概念。我们可以使用输入函数来做到这一点，如下所示：
class ResultSetIterator[T](rs: ResultSet, nextRowFunc: ResultSet => T)
extends Iterator[T] {
private var nextVal: Option[T] = None
override def hasNext: Boolean = {
val ret = rs.next()
if(ret) {
nextVal = Some(nextRowFunc(rs))
} else {
nextVal = None
}
ret
}
override def next(): T = nextVal.getOrElse {
hasNext
nextVal.getOrElse( throw new ResultSetIteratorOutOfBoundsException
)}
class ResultSetIteratorOutOfBoundsException extends Exception("ResultSetIterator reached end of list and next can no longer be called. hasNext should return false.")
}
EDIT: Translate to stream or something else as per above.
编辑：按上述翻译为流或其他内容。
回答by thoredge
This implementation, although longer and clumsier it is in better correspondence with the ResultSet contract. The side-effect has been removed from hasNext(...) and moved into next().
这种实现虽然更长更笨拙，但它更符合 ResultSet 契约。副作用已从 hasNext(...) 中移除并移至 next() 中。
new Iterator[String] {
private var available = resultSet.next()
override def hasNext: Boolean = available
override def next(): String = {
val string = resultSet.getString(1)
available = resultSet.next()
string
}
}
回答by Matzz
I think most of above implementations has a nondeterministic hasNextmethod. Calling it two times will move cursor to the second row. I would advise to use something like that:
我认为上述大多数实现都有一个不确定的hasNext方法。调用它两次会将光标移动到第二行。我建议使用类似的东西：
new Iterator[ResultSet] {
def hasNext = {
!resultSet.isLast
}
def next() = {
resultSet.next()
resultSet
}
}
回答by Sergey Alaev
Iterator.continually(rs.next())
.takeWhile(identity)
.map(_ => Model(
id = rs.getInt("id"),
text = rs.getString("text")
))
回答by henko
Here is an alternative, similar to Sergey Alaev's and thoredge's solutions, for when we need a solution which honors the Iteratorcontract where hasNextis side-effect free.
这里是一个另类，类似于谢尔盖Alaev的和thoredge的解决方案，因为当我们需要这些荣誉的一个解决方案Iterator合同，其中hasNext不含副作用。
Assuming a function f: ResultSet => T:
假设一个函数f: ResultSet => T：
Iterator.unfold(resultSet.next()) { hasNext =>
Option.when(hasNext)(f(resultSet), resultSet.next())
}
I've found it useful to have as map"extension method" on ResultSet.
我发现map在ResultSet.
implicit class ResultSetOps(resultSet: ResultSet) {
def map[T](f: ResultSet => T): Iterator[T] = {
Iterator.unfold(resultSet.next()) { hasNext =>
Option.when(hasNext)(f(resultSet), resultSet.next())
}
}
}
