问题描述
我对数据库、SLICK和Scala非常(非常)陌生,所以我使用了他们在http://slick.typesafe.com/doc/3.0.0/gettingstarted.html
文档中的示例代码我的问题是,由于某些原因,我必须多次运行查询才能返回数据。在它返回结果之前,我必须至少重新运行3-4次。我使用for循环重新运行查询,它们也不一定每次都给我完全相同的结果。
如下所示创建两个表:
class Patients(tag: Tag) extends Table[(String, String, Int, String)](tag, "Patientss") { def PID = column[String]("Patient Id", O.PrimaryKey) def Gender = column[String]("Gender") def Age = column[Int]("Age") def Ethnicity = column[String]("Ethnicity") def * = (PID, Gender, Age, Ethnicity) } val patientsss = TableQuery[Patients] class DrugEffect(tag: Tag) extends Table[(String, String, Double)](tag, "DrugEffectss") { def DrugID = column[String]("Drug ID", O.PrimaryKey) def PatientID = column[String]("Patient_ID") def DrugEffectssss = column[Double]("Drug Effect") def * = (DrugID, PatientID, DrugEffectssss) def Patient = foreignKey("Patient_FK", PatientID, patientsss)(_.PID)} val d_effects = TableQuery[DrugEffect]
然后我使用
创建这些表val create_empty = DBIO.seq((patientsss.schema ++ d_effects.schema).create) val setup_1 = db.run(create_empty)
我有两个文本文件中的实际数据,我使用缓冲阅读器对它们进行解析。 我将所有的药物ID存储在一个名为DrugIds
的列表中然后,我开始按以下方式填表
我首先填写患者表:
while (switch != 1) { val Patient = CurPatient.split("\s+") if (Patient(2).toUpperCase() == "NA" || (Patient(2).toFloat % 1 != 0)) age = -1 else age = Patient(2).toInt val insertPatient: DBIO[Option[Int]] = patientsss ++= Seq( (Patient(0), Patient(1), age, Patient(3)) ) var future = db.run(insertPatient) CurPatient = PatientReader.readLine() if (CurPatient == null) switch = 1 //switch to 1 }
对于DrugEffect表,我执行以下操作:
while (switch != 1) { val Effect = CurEffect.split("\s+") for (i <- 1 until DrugIds.size - 1) { if (Effect(i).toUpperCase() == "NA") d_ef = -1.00 else d_ef = (Effect(i).toFloat).asInstanceOf[Double] val insertEffect: DBIO[Option[Int]] = d_effects ++= Seq( (DrugIds(i), Effect(0), d_ef) ) var future2 = db.run(insertEffect) } CurEffect = EffectReader.readLine() if (CurEffect == null) switch = 1 }
然后我使用以下代码段运行查询
val q1 = for { c <- patientsss } yield (c.PID, c.Gender, c.Age, c.Ethnicity) db.stream(q1.result).foreach(println)
这应该只给我提供患者表中的所有数据,但它不一定会这样做。
有时,我会收到以下错误(但并非总是):
java.util.concurrent.RejectedExecutionException: Task slick.backend.DatabaseComponent$DatabaseDef$$anon$3@47089c2c rejected from java.util.concurrent.ThreadPoolExecutor@6453123[Terminated, pool size = 0, active threads = 0, queued tasks = 0, completed tasks = 215] at java.util.concurrent.ThreadPoolExecutor$AbortPolicy.rejectedExecution(ThreadPoolExecutor.java:2048) at java.util.concurrent.ThreadPoolExecutor.reject(ThreadPoolExecutor.java:821) at java.util.concurrent.ThreadPoolExecutor.execute(ThreadPoolExecutor.java:1372) at scala.concurrent.impl.ExecutionContextImpl$$anon$1.execute(ExecutionContextImpl.scala:136) at slick.backend.DatabaseComponent$DatabaseDef$class.scheduleSynchronousStreaming(DatabaseComponent.scala:253) at slick.jdbc.JdbcBackend$DatabaseDef.scheduleSynchronousStreaming(JdbcBackend.scala:38) at slick.backend.DatabaseComponent$BasicStreamingActionContext.restartStreaming(DatabaseComponent.scala:516) at slick.backend.DatabaseComponent$BasicStreamingActionContext.request(DatabaseComponent.scala:531) at slick.backend.DatabasePublisher$$anon$3$$anonfun$onNext$2.apply(DatabasePublisher.scala:50) at slick.backend.DatabasePublisher$$anon$3$$anonfun$onNext$2.apply(DatabasePublisher.scala:49) at scala.concurrent.impl.CallbackRunnable.run(Promise.scala:32) at scala.concurrent.impl.ExecutionContextImpl$AdaptedForkJoinTask.exec(ExecutionContextImpl.scala:121) at scala.concurrent.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260) at scala.concurrent.forkjoin.ForkJoinPool$WorkQueue.pollAndExecAll(ForkJoinPool.java:1253) at scala.concurrent.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1346) at scala.concurrent.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979) at scala.concurrent.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)
如果我运行一个更复杂的查询,我得到的数据与查询的参数是准确的,但同样的问题也会出现,即结果重复、不存在或不完整(当我多次重新运行查询时)。
如果可以,就像我5岁一样解释,或者给我介绍一个可以帮助我解决这些问题的资源
编辑:
bjfletcher的回答起作用了(谢谢!),但现在我有另一个问题:
每隔一段时间,代码就会失败,并显示错误:
Exception in thread "main" org.h2.jdbc.JdbcSQLException: Table "Patientss" not found; SQL statement: insert into "Patientss" ("Patient Id","Gender","Age","Ethnicity") values (?,?,?,?) [42102-162] at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) at org.h2.message.DbException.get(DbException.java:169) at org.h2.message.DbException.get(DbException.java:146) at org.h2.command.Parser.readTableOrView(Parser.java:4758) at org.h2.command.Parser.readTableOrView(Parser.java:4736) at org.h2.command.Parser.parseInsert(Parser.java:954) at org.h2.command.Parser.parsePrepared(Parser.java:375) at org.h2.command.Parser.parse(Parser.java:279) at org.h2.command.Parser.parse(Parser.java:251) at org.h2.command.Parser.prepareCommand(Parser.java:217) at org.h2.engine.Session.prepareLocal(Session.java:415) at org.h2.engine.Session.prepareCommand(Session.java:364) at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1121) at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:71) at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:267) at slick.jdbc.JdbcBackend$SessionDef$class.prepareStatement(JdbcBackend.scala:252) at slick.jdbc.JdbcBackend$BaseSession.prepareStatement(JdbcBackend.scala:386) at slick.jdbc.JdbcBackend$SessionDef$class.withPreparedStatement(JdbcBackend.scala:301) at slick.jdbc.JdbcBackend$BaseSession.withPreparedStatement(JdbcBackend.scala:386) at slick.driver.JdbcInsertInvokerComponent$BaseInsertInvoker.preparedInsert(JdbcInsertInvokerComponent.scala:177) at slick.driver.JdbcInsertInvokerComponent$BaseInsertInvoker$$anonfun$internalInsertAll$1.apply(JdbcInsertInvokerComponent.scala:201) at slick.jdbc.JdbcBackend$BaseSession.withTransaction(JdbcBackend.scala:422) at slick.driver.JdbcInsertInvokerComponent$BaseInsertInvoker.internalInsertAll(JdbcInsertInvokerComponent.scala:198) at slick.driver.JdbcInsertInvokerComponent$BaseInsertInvoker.insertAll(JdbcInsertInvokerComponent.scala:194) at slick.driver.JdbcInsertInvokerComponent$InsertInvokerDef$class.$plus$plus$eq(JdbcInsertInvokerComponent.scala:73) at slick.driver.JdbcInsertInvokerComponent$BaseInsertInvoker.$plus$plus$eq(JdbcInsertInvokerComponent.scala:152) at slick.driver.JdbcActionComponent$InsertActionComposerImpl$$anonfun$$plus$plus$eq$1.apply(JdbcActionComponent.scala:459) at slick.driver.JdbcActionComponent$InsertActionComposerImpl$$anonfun$$plus$plus$eq$1.apply(JdbcActionComponent.scala:459) at slick.driver.JdbcActionComponent$InsertActionComposerImpl$$anon$8.run(JdbcActionComponent.scala:449) at slick.driver.JdbcActionComponent$InsertActionComposerImpl$$anon$8.run(JdbcActionComponent.scala:447) at slick.backend.DatabaseComponent$DatabaseDef$$anon$2.liftedTree1$1(DatabaseComponent.scala:231) at slick.backend.DatabaseComponent$DatabaseDef$$anon$2.run(DatabaseComponent.scala:231) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745)
并不总是发生,但经常发生,我不知道这意味着什么
推荐答案
所有数据库调用都将使用Futures立即返回,即使它们尚未完成操作。这是异步的,不是同步的。
您可以通过以下两种方式之一更改代码以适应Futures:
您可以对所有数据库调用使用Await.result,以在该点等待,直到它们完成,例如:Await.result(db.run(insertEffect), Duration.Inf)
您可以使用.map(或者.flatMap,如果您从内部使用另一个Future),以及您希望在DB操作完成时运行的代码。例如:db.run(insertEffect).map(_ => ... do stuff... )
查看有关该异常的另一个堆栈溢出thread,并了解一些原因。