Squeryl:明确运行查询 [英] Squeryl: Run query explicitly

问题描述

当我在squeryl中创建查询时，它返回一个Query [T]对象.当我遍历Query对象(Query [T]扩展Iterable [T])时，该查询尚未执行.

When I create a query in squeryl, it returns a Query[T] object. The query was not yet executed and will be, when I iterate over the Query object (Query[T] extends Iterable[T]).

在执行查询前后，必须有一个transaction {}或一个inTransaction {}块.

Around the execution of a query there has to be either a transaction{} or a inTransaction{} block.

我只是说说SELECT查询和事务不是必需的，但是squeryl框架需要它们.

I'm just speaking of SELECT queries and transactions wouldn't be necessary, but the squeryl framework needs them.

我想在我的应用程序模型中创建一个查询，并将其直接传递给视图，在该视图中模板中的视图助手在其上进行迭代并显示数据. 这仅在将transaction {}块放入控制器中时才有可能(控制器包括模板的调用，因此进行迭代的模板也位于内部).无法将transaction {}块放入模型中，因为该模型并未真正执行查询.

I'd like to create a query in the model of my application and pass it directly to the view where a view helper in the template iterates over it and presents the data. This is only possible when putting the transaction{} block in the controller (the controller includes the call of the template, so the template which does the iteration is also inside). It's not possible to put the transaction{} block in the model, because the model doesn't really execute the query.

但是据我了解，交易与控制器无关.由模型决定使用哪个数据库框架，如何使用它以及在哪里使用事务.因此，我希望transaction {}块位于模型中.

But in my understanding the transaction has nothing to do with the controller. It's a decision of the model which database framework to use, how to use it and where to use transactions. So I want the transaction{} block to be in the model.

我知道我可以-代替返回Query [T]实例-在此Query [T]对象上调用Iterable [T] .toList，然后返回创建的列表.然后在模型中执行整个查询，一切都很好.但是我不喜欢这种方法，因为从数据库请求的所有数据都必须缓存在此列表中.我更喜欢将这些数据直接传递到视图的方法.我喜欢MySql功能，当结果集很大时，可以流式传输结果集.

I know that I can - instead of returning the Query[T] instance - call Iterable[T].toList on this Query[T] object and then return the created list. Then the whole query is executed in the model and everything is fine. But I don't like this approach, because all the data requested from the database has to be cached in this list. I'd prefer a way where this data is directly passed to the view. I like the MySql feature of streaming the result set when it's large.

有没有可能?也许像Query [T] .executeNow()这样的函数可以将请求发送到数据库，能够关闭事务，但仍使用MySQL流功能，并在以下情况下接收其余(选定并因此固定)的结果集:它被访问了吗?因为结果集在查询时是固定的，所以关闭事务应该不是问题.

Is there any possibility? Maybe something like a function Query[T].executeNow() which sends the request to the database, is able to close the transaction, but still uses the MySQL streaming feature and receives the rest of the (selected and therefore fixed) result set when it's accessed? Because the result set is fixed in the moment of the query, closing the transaction shouldn't be a problem.

推荐答案

我在这里看到的一般问题是，您尝试将以下两个想法结合起来:

The general problem that I see here is that you try to combine the following two ideas:

• 数据的惰性计算；此处:数据库结果

• lazy computation of data; here: database results

隐藏了进行计算时必须触发的后处理操作的需要；此处:从您的控制器中隐藏或查看数据库会话必须关闭

hiding the need for a post-processing action that must be triggered when the computation is done; here: hiding from your controller or view that the database session must be closed

由于您的计算是懒惰的，并且由于没有义务执行到最后(这里:遍历整个结果集)，因此没有明显的钩子可以触发后处理步骤.

Since your computation is lazy and since you are not obliged to perform it to the very end (here: to iterate over the whole result set), there is no obvious hook that could trigger the post-processing step.

您提出的调用Query[T].toList的建议没有出现此问题，因为计算一直进行到最后，并且请求结果集的最后一个元素可以用作关闭会话的触发器.

Your suggestion of invoking Query[T].toList does not exhibit this problem, since the computation is performed to the very end, and requesting the last element of the result set can be used as a trigger for closing the session.

也就是说，我能想到的最好的是以下内容，它是org.squeryl.dsl.QueryDsl._using中代码的改编:

That said, the best I could come up with is the following, which is an adaptation of the code inside org.squeryl.dsl.QueryDsl._using:

class IterableQuery[T](val q: Query[T]) extends Iterable[T] {
  private var lifeCycleState: Int = 0
  private var session: Session = null
  private var prevSession: Option[Session] = None

  def start() {
    assert(lifeCycleState == 0, "Queries may not be restarted.")
    lifeCycleState = 1

    /* Create a new session for this query. */
    session = SessionFactory.newSession

    /* Store and unbind a possibly existing session. */
    val prevSession = Session.currentSessionOption
    if(prevSession != None) prevSession.get.unbindFromCurrentThread

    /* Bind newly created session. */
    session.bindToCurrentThread
  }

  def iterator = {
    assert(lifeCycleState == 1, "Query is not active.")
    q.toStream.iterator
  }

  def stop() {
    assert(lifeCycleState == 1, "Query is not active.")
    lifeCycleState = 2

    /* Unbind session and close it. */
    session.unbindFromCurrentThread
    session.close

    /* Re-bind previous session, if it existed. */
    if(prevSession != None) prevSession.get.bindToCurrentThread
  }
}

客户可以按如下方式使用查询包装器:

Clients can use the query wrapper as follows:

var manualIt = new IterableQuery(booksQuery)
manualIt.start()
manualIt.foreach(println)
manualIt.stop()
//      manualIt.foreach(println) /* Fails, as expected */

manualIt = new IterableQuery(booksQuery) /* Queries can be reused */
manualIt.start()
manualIt.foreach(b => println("Book: " + b))
manualIt.stop()

在创建对象时，即已经在IterableQuery的构造函数中，或者在将对象传递给控制器​​之前，已经可以完成manualIt.start()的调用.

The invocation of manualIt.start() could already be done when the object is created, i.e., inside the constructor of IterableQuery, or before the object is passed to the controller.

但是，以这种方式使用资源(文件，数据库连接等)非常脆弱，因为在发生异常的情况下不会触发后处理.如果查看org.squeryl.dsl.QueryDsl._using的实现，您会看到IterableQuery中缺少的几个try ... finally块.

However, working with resources (files, database connections, etc.) in such a way is very fragile, because the post-processing is not triggered in case of exceptions. If you look at the implementation of org.squeryl.dsl.QueryDsl._using you will see a couple of try ... finally blocks that are missing from IterableQuery.

这篇关于Squeryl:明确运行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！