查询在奎尔在运行时崩溃的语法错误
问题描述:
相关片段:查询在奎尔在运行时崩溃的语法错误
case class Video(
id: String,
title: String,
url: String,
pictureUrl: String,
publishedAt: Date,
channel: String,
duration: Option[String],
createdOn: Date
)
“连接”到DB工作,从预期的结果视频台返回选择。我有储蓄的问题,这个简单的方法:
def saveToCache(item: Video): Unit = {
logger.trace(item)
import ctx._
val x = quote {
val lItem = lift(item)
val fromDb = query[Video].filter(_.id == lItem.id)
if (fromDb.isEmpty) query[Video].insert(lItem)
else fromDb.update(lItem)
}
logger.trace(x.ast)
run(x)
}
崩溃在运行时用(缩短):
CASE WHEN NOT EXISTS (SELECT x3.* FROM video x3 WHERE x3.id = ?) THEN INSERT INTO video (id,title,url,picture_url,published_at,channel,duration,created_on) VALUES (?, ?, ?, ?, ?, ?, ?, ?) ELSE UPDATE video SET id = ?, title = ?, url = ?, picture_url = ?, published_at = ?, channel = ?, duration = ?, created_on = ? WHERE id = ? END
我想:
11:14:06.640 [scala-execution-context-global-84] ERROR io.udash.rpc.AtmosphereService - RPC request handling failed
org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "CASE": syntax error)
at org.sqlite.core.DB.newSQLException(DB.java:909) ~[sqlite-jdbc-3.18.0.jar:na]
at org.sqlite.core.DB.newSQLException(DB.java:921) ~[sqlite-jdbc-3.18.0.jar:na]
at org.sqlite.core.DB.throwex(DB.java:886) ~[sqlite-jdbc-3.18.0.jar:na]
at org.sqlite.core.NativeDB.prepare_utf8(Native Method) ~[sqlite-jdbc-3.18.0.jar:na]
at org.sqlite.core.NativeDB.prepare(NativeDB.java:127) ~[sqlite-jdbc-3.18.0.jar:na]
at org.sqlite.core.DB.prepare(DB.java:227) ~[sqlite-jdbc-3.18.0.jar:na]
at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:41) ~[sqlite-jdbc-3.18.0.jar:na]
at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30) ~[sqlite-jdbc-3.18.0.jar:na]
at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:19) ~[sqlite-jdbc-3.18.0.jar:na]
at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:48) ~[sqlite-jdbc-3.18.0.jar:na]
at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:263) ~[sqlite-jdbc-3.18.0.jar:na]
at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:235) ~[sqlite-jdbc-3.18.0.jar:na]
at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:317) ~[HikariCP-2.7.2.jar:na]
at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) ~[HikariCP-2.7.2.jar:na]
at io.getquill.context.jdbc.JdbcContext.$anonfun$executeAction$1(JdbcContext.scala:98) ~[quill-jdbc_2.12-2.2.0.jar:2.2.0]
at io.getquill.context.jdbc.JdbcContext.$anonfun$executeAction$1$adapted(JdbcContext.scala:97) ~[quill-jdbc_2.12-2.2.0.jar:2.2.0]
at io.getquill.context.jdbc.JdbcContext.$anonfun$withConnection$1(JdbcContext.scala:46) ~[quill-jdbc_2.12-2.2.0.jar:2.2.0]
at scala.Option.getOrElse(Option.scala:121) ~[scala-library-2.12.2.jar:1.0.0-M1]
at io.getquill.context.jdbc.JdbcContext.withConnection(JdbcContext.scala:44) ~[quill-jdbc_2.12-2.2.0.jar:2.2.0]
at io.getquill.context.jdbc.JdbcContext.executeAction(JdbcContext.scala:97) ~[quill-jdbc_2.12-2.2.0.jar:2.2.0]
从宏观编译过程中相关的输出
启用日志记录查询,但我不知道如何去做(Quill文档只是说了一些关于SLF4J的信息,这对我来说是无用的信息 - 我没有看到任何日志,我不知道要在SLF4J中搜索什么内容OCS)。到目前为止,我对Quill感到非常失望 - 第一次在使用默认排序类型时,它会生成无效的排序查询,现在这个。
答
你应该重写你的查询:
import ctx._
def saveToCache(item: Video): Unit = {
if (ctx.run(query[Video].filter(_.id == lift(item.id)).isEmpty))
ctx.run(query[Video].insert(lift(item)))
else ctx.run(query[Video].filter(_.id == lift(item.id)).update(lift(item)))
()
}
因为quote
必须包含将被编译成一个单一的查询表达式。 此代码将编译为三个查询。
但更好地利用优化的版本:
import ctx._
def saveToCacheOptimized(item: Video): Unit = {
if (ctx.run(query[Video].filter(_.id == lift(item.id)).update(lift(item))) == 0)
ctx.run(query[Video].insert(lift(item)))
()
}
其编译成两个查询。
Quill使用SLF4J进行日志记录,您应该阅读本项目文档。启用日志记录,你应该添加一些日志记录的后端,例如,logback:
libraryDependencies ++= Seq(
"ch.qos.logback" % "logback-classic" % "1.2.3"
)
应该为刚刚记录的查询是不够的。
我修复了我的答案中的错误并添加了有关日志记录的信息。 – mixel