postgresql - 我正在努力从 Play 中的 postgresql 取回 id

Question

我正在努力将一条记录插入到 postgresql 中:

val res = DB.withConnection {
        implicit con =>
          SQL(
            """INSERT INTO
              my_table(id, user_id, file_name, added_at)
              VALUES(DEFAULT, {userId}, {fileName}, {currentTime})
              RETURNING id
            """)
            .on("userId" -> 1, "fileName" -> "fileName1", "currentTime" -> new java.sql.Timestamp(DateTime.now.getMillis))
            .executeInsert()
      }

Ok(toJson(JsObject(Seq("res" -> JsString(res.toString)))))

它确实插入了一个值(我可以通过 pdAdmin 看到)但它最终返回一个错误 ERROR: syntax error at or near "RETURNING"。

如果我删除 RETURNING id 然后错误变为 TypeDoesNotMatch(Cannot convert requestBody4050249427671619471asTemporaryFile :class java.lang.String to Long for column ColumnName(my_table.file_name,Some(file_name))) ]]

当我切断 id 和 DEFAULT 时，错误保持不变。

my_table 定义为

CREATE TABLE my_table
(
  file_name character(255),
  user_id integer,
  added_at timestamp without time zone,
  id serial NOT NULL,
  CONSTRAINT my_table_pk PRIMARY KEY (id),
  CONSTRAINT scan_fk FOREIGN .....
)

这是为什么呢？它与 file_name 有什么关系？

Answer 1

给定这张表:

CREATE TABLE my_table
(
  file_name character(255),
  user_id integer,
  added_at timestamp without time zone,
  id serial NOT NULL,
  CONSTRAINT my_table_pk PRIMARY KEY (id)
);

您可以像这样插入记录并检索其 ID:

package dao

import anorm._
import anorm.SqlParser._
import play.api.db.DB
import play.api.Logger
import play.api.Play.current

object MyDAO {

  def insert(fileName: String, userId: Int): Int = {
    val sql = SQL(
      """
        insert into my_table(file_name, user_id, added_at)
        values ({fileName}, {userId}, CURRENT_TIMESTAMP)
        returning id
      """)
      .on(
        "fileName" -> fileName,
        "userId" -> userId)
    DB.withConnection { implicit c =>
      val id = sql.as(scalar[Int].single)
      Logger.info(s"Inserted '$fileName' with id '$id'.")
      id
    }
  }

}

然后像这样测试它:

import org.junit.runner._
import org.specs2.mutable._
import org.specs2.runner._
import play.api.test._
import play.api.test.Helpers._
import dao.MyDAO

@RunWith(classOf[JUnitRunner])
class MyDAOSpec extends Specification {
  "MyDAO" should {
    "insert a record and return its id" in new WithApplication {
      val id = MyDAO.insert("file1", 101)
      id must be_>=(0)
    }
  }
}

参见 Anorm, simple SQL data access有关如何从结果集中检索数据的更多示例。