使用Anorm在JSON的JSON字段中插入Json对象 [英] Inserting Json objects in PostgreSQL json fields with Anorm

问题描述

如何使用Anorm将JsObject传递到PostgreSQL PostgreSQL 9.3数据库中的json数据类型字段中，而不必将其转换为字符串?

How can I pass a JsObject into a json data type field in a PostgreSQL 9.3 database with Anorm without having to cast it as a string?

给出PostgreSQL 9.3表，例如:

Given a PostgreSQL 9.3 table such as:

create table profiles
(
  id serial primary key,
  profile json null
);

对于Play 2.2，该测试成功完成:

With Play 2.2, this test succeeds:

package helpers

import anorm._
import org.specs2.mutable._
import org.specs2.runner._
import org.junit.runner._
import play.api.db.DB
import play.api.libs.json._
import play.api.test._

@RunWith(classOf[JUnitRunner])
class AnormTest extends Specification {
  "AnormTest" should {
    "insert a JSON object" in new WithApplication {
      val profile = Json.obj("language" -> "en")
      val sql = SQL("insert into profiles (profile) values (CAST({profile} AS json))")
        .on("profile" -> profile.toString)
      DB.withConnection { implicit c => sql.execute() }
    }
  }
}

但是这些行更改了:

      val sql = SQL("insert into profiles (profile) values ({profile})")
        .on("profile" -> profile)

它会产生此错误:

org.postgresql.util.PSQLException: 
Can't infer the SQL type to use for an instance of play.api.libs.json.JsObject. 
Use setObject() with an explicit Types value to specify the type to use.

由于使用Anorm，我们通常传递适当的数据类型而不是文本(例如，uuid数据类型的列的UUID对象)，因此不必将JsObject转换为字符串并将其转换回SQL语句中的json数据类型.

Since with Anorm we usually pass the appropriate data types instead of text (for instance, an UUID object for a column of uuid data type), it's not optimal having to convert the JsObject to a string and cast it back to the json data type in the SQL statement.

有关此问题及其解决方法的示例，请参阅使用PostgreSQL的Play Framework 2.1-RC1中的本机JSON支持.

For an example of this issue and its workaround, please refer to Using PostgreSQL's native JSON support in Play Framework 2.1-RC1.

如何使用Anorm避免这种情况，以便将JsObject直接作为json数据类型传递?

How can this be avoided with Anorm in order to pass the JsObject directly as a json data type?

推荐答案

对于Play 2.4及更高版本，请使用anorm.Object(value:org.postgresql.util.PGobject)类，而不要直接使用value:

For the Play 2.4 and above use the anorm.Object(value: org.postgresql.util.PGobject) class instead of value directly:

val pgObject = new org.postgresql.util.PGobject();
pgObject.setType("json");
pgObject.setValue(profile);
val sql = SQL("insert into profiles (profile) values ({profile})")
    .on("profile" -> anorm.Object(pgObject))

这篇关于使用Anorm在JSON的JSON字段中插入Json对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！