How to insert value of UUID?

Question

I'm using anorm 2.4 in play framework 2.3 backed postgresql 9.4

Give a model like this:

case class EmailQueue(id:UUID,
                  send_from:String,
                  send_to:String,
                  subject:String,
                  body:String,
                  created_date:Date,
                  is_sent:Boolean,
                   email_template:String)

This is my parser:

val parser: RowParser[EmailQueue] = {
get[UUID]("id") ~
  get[String]("send_from") ~
  get[String]("send_to") ~
  get[String]("subject") ~
  get[String]("body") ~
  get[Date]("created_date") ~
  get[Boolean]("is_sent") ~
  get[String]("email_template") map {
  case id ~ send_from ~ send_to ~ subject ~ body ~
    created_date ~ is_sent ~ email_template=> EmailQueue(id,
    send_from,
    send_to,
    subject,
    body,
    created_date,
    is_sent,
    email_template)
}

}

And this is my insert statement:

def insert(email:EmailQueue): Unit ={
DB.withTransaction { implicit c =>
  SQL(s"""
        INSERT INTO "email_queue" ( "body", "created_date", "id", "is_sent", "send_from", "send_to", "subject", "email_template")
        VALUES ( {body}, {created_date}, {id}, {is_sent}, {send_from}, {send_to}, {subject}, {email_template} );
      """).on(
      "body" -> email.body,
      "created_date" -> email.created_date,
      "id" -> email.id,
      "is_sent" -> email.is_sent,
      "send_from" -> email.send_from,
      "send_to" -> email.send_to,
      "subject" -> email.subject,
      "email_template" -> email.email_template
    ).executeInsert()
}

}

I receive following error when inserting:

[error] PSQLException: : ERROR: column "id" is of type uuid but expression is of type character varying [error] Hint: You will need to rewrite or cast the expression. [error] Position: 153 (xxxxxxxxxx.java:2270)

The database table is created by this query:

CREATE TABLE email_queue (
   id UUID PRIMARY KEY,
   send_from VARCHAR(255) NOT NULL,
   send_to VARCHAR(255) NOT NULL,
   subject VARCHAR(2000) NOT NULL,
   body text NOT NULL,
   created_date timestamp without time zone DEFAULT now(),
   is_sent BOOLEAN NOT NULL DEFAULT FALSE,
   email_template VARCHAR(2000) NOT NULL
);

Marrero

It seems a jave.util.UUID parameter is mapped to a String of length 17 for JDBC. – Bridgetbridgetown

Yes, there is a conversion in anorm.ToStatement. However, even I import anorm.ToStatement.uuidToStatement I still receive the same error – Marrero

If you look at the source of uuidToStatement, the UUID value is just set as a String value on the underlying java.sql.PreparedStatement. – Bridgetbridgetown

Use {id}::uuid in the statement, so that the java.util.UUID passed as String in JDBC parameters is then converted from passed VARCHAR to a specific PostgreSQL uuid. – Catabolism

Answer 1

Anorm is DB agnostic, as JDBC, so vendor specific datatype are not supported by default.

You can use {id}::uuid in the statement, so that the java.util.UUID passed as String in JDBC parameters is then converted from passed VARCHAR to a specific PostgreSQL uuid.

Using string interpolation in SQL(s"...") is not recommanded (SQL injection), but Anorm interpolation can be used.

def insert(email:EmailQueue): Unit = DB.withTransaction { implicit c =>
  SQL"""
    INSERT INTO "email_queue" ( "body", "created_date", "id", "is_sent", "send_from", "send_to", "subject", "email_template")
    VALUES ( ${email.body}, ${email.created_date}, ${email.id}::uuid, ${email.is_sent}, ${email.send_from}, ${email.send_to}, ${email.subject}, ${email.email_template} )
  """).executeInsert()
}

Not recommended, but can be useful sometimes for vendor specific type, the anorm.Object can be used to pass an opaque value as JDBC parameter (there the ::uuid is nicer for me).

You can also implement a custom ToStatement[java.util.UUID].

Catabolism

Thank you for your suggestion and the solution. Your solution works well for me. Only one small change is that instead of calling executeInsert() I called executeUpdate otherwise I receive error that cannot convert UUID to Long. Error: Execution exception[[RuntimeException: TypeDoesNotMatch(Cannot convert 87adfc2f-9985-42e1-a501-d36b9d9f83b6: class java.util.UUID to Long for column ColumnName(email_queue.id,Some(id)))]] I'm not sure why it has this problem – Marrero

The error is because your table seems to have return the UUID as JDBC generated key, and .executeInsert try to handle such key, but by default as auto-incremented numeric (Long). Fortunatly, you can use a custom parser also for such key returned on insertion: .executeInsert(anorm.SqlParser.scalar[java.util.UUID].singleOpt). – Catabolism