通过Scala Anorm（在Play框架中）向PostgreSQL的jsonb列插入数据

Question

PostgreSQL表my_table的data列格式为jsonb。我想插入一个Scala JsObject（或JsValue），但不知道该怎么做！

以下代码无法编译，因为on函数期望json是一个String：

  def add(json: JsObject): Option[Long] = {
    DB.withConnection {
      implicit c =>

        val query = """
             insert into my_table(data)
        values({data});"""

        SQL(query).on(
          "data" -> json
        ).executeInsert()
    }
  }

解决方案是什么？

- Blackbird

---

可能是PostgreSQL jsonb，`？`和JDBC的重复问题。 - cchantep

可以插入特定的参数转换来准备SQL语句的一部分并传递JSON值。 - cchantep

@applicius 这不是 PostgreSQL jsonb, '?' and JDBC 的重复。我的问题是关于一个 insert 语句的。 - Blackbird

@Blackbird，你解决了这个问题吗？ - mgosk

@mgosk 没有，我还没有解决它。 - Blackbird

Answer 1

在值的末尾使用::jsonb标签。例如：

//my scala variables

val jsonDescription: String = "{\"name\":\"ksulr\"}"
val age = 15

//my insert statement
val sql = "INSERT into person(json_description,age) VALUES(?::jsonb,?)"

//prepared statement
val statement = conn.prepareStatement(sql)

//insert values into the sql statement
statement.setString(1,jsonDescription)
statement.setInt(2,age)

statement.executeUpdate()

这将有效。它对我起作用了。这就是我将jsonb类型的列插入到postgres db中的方法。

- KJay_wer

---

谢谢你的回答！它可能有效，但它没有使用Anorm。 - Blackbird

Answer 2

借鉴@KJay_wer的方法，我们也可以在Scala Anorm（Play Framework）中使用::jsonb标签，并将您的JsObject（或其他JsValue）转换为String：

def add(json: JsObject): Option[Long] = {
    DB.withConnection {
      implicit c =>

        val query = """
             insert into my_table (data)
             values ({data}::jsonb);
             """.stripMargin

        SQL(query).on(
          "data" -> json.toString
        ).executeInsert()
    }
  }

- Ricardo

---

刚刚使用了::json进行测试。 - Ricardo

听起来是一个不错的解决方案，谢谢！但是这么久以前了，我无法验证它。虽然我已经点赞了 :) - Blackbird

我在问题的所有标签上测试了::json，它完美地工作了！ - Ricardo

你的代码示例中使用的是 ::json 还是 ::jsonb？ - Blackbird

在上面的代码示例中，我使用了::jsonb来匹配原始问题；在我的用例中，我使用了::json，它略有不同。我认为这个解决方案也适用于你的原始情况。 - Ricardo

Answer 3

您可以使用ToStatement转换器对象：

def add(json: JsObject): Option[Long] = {
   DB.withConnection { implicit c =>       
      val query = """insert into my_table(data) values(${data});"""
      SQL(query).executeInsert()
   }
}

implicit object jsObjectStatement extends ToStatement[JsObject] {
    override def set(s: PreparedStatement, index: Int, v: JsObject): Unit = {
      val jsonObject = new PGobject()
      jsonObject.setType("json")
      jsonObject.setValue(Json.stringify(v))
      s.setObject(index, jsonObject)
    }
  }

- mgosk