Storing UUIDs with Anorm

I am not a big fan of Object Relational Mappers (ORMs). It is too easy to turn a simple query into a massive CPU-eating hydra - complete with 7 outer joins and no index usage. When I write code, I try to write it in such a way that the worst things to do are the hardest. That way, when I'm in a rush, I still write good code.

Enough about my rant: enter Anorm. I've been developing software on the Scala / Play stack for over a year now. Anorm is currently the supported database connector. It is a thin layer on top of JDBC that takes out all of the ugliness, but leaves the manual SQL writing goodness.

One of the things that Anorm does not do is read and write UUIDs. In 200 lines of copy-paste code, you'll have Anorm reading and writing UUIDs like a pro. A side effect of this code is that Anorm will also be able to handle byte arrays.

This code is going to store UUIDs as BINARY(16) in the database. If you are curious as to why I do it that way, read this blog post about why BINARY(16) is better than VARCHAR(36). If you still want to store them as strings, then modify valueToUUIDOption and uuidToStatement in the AnormImplicits class.

Now, the code.

In utils/AnormImplicits.scala:

package models

import java.sql.Blob
import java.sql.Clob
import java.util.UUID
import org.apache.commons.io.IOUtils
import utils.UUIDHelper
import anorm._

object AnormImplicits {
/**
* Attempt to convert a SQL value into a byte array
*
* @param value value to convert
* @return byte array
*/
private def valueToByteArrayOption(value: Any): Option[Array[Byte]] = {
try {
value match {
case bytes: Array[Byte] => Some(bytes)
case clob: Clob => Some(IOUtils.toByteArray(clob.getAsciiStream()))
case blob: Blob => Some(blob.getBytes(1, blob.length.asInstanceOf[Int]))
case _ => None
}
}
catch {
case e: Exception => None
}
}
/**
* Attempt to convert a SQL value into a UUID
*
* @param value value to convert
* @return UUID
*/
private def valueToUUIDOption(value: Any): Option[UUID] = {
try {
valueToByteArrayOption(value) match {
case Some(bytes) => Some(UUIDHelper.fromByteArray(bytes))
case _ => None
}
}
catch {
case e: Exception => None
}
}
/**
* Implicit conversion from anorm row to byte array
*/
implicit def rowToByteArray: Column[Array[Byte]] = {
Column.nonNull[Array[Byte]] { (value, meta) =>
val MetaDataItem(qualified, nullable, clazz) = meta
valueToByteArrayOption(value) match {
case Some(bytes) => Right(bytes)
case _ => Left(TypeDoesNotMatch("Cannot convert " + value + ":" + value.asInstanceOf[AnyRef].getClass + " to Byte Array for column " + qualified))
}
}
}
/**
* Implicit converstion from anorm row to uuid
*/
implicit def rowToUUID: Column[UUID] = {
Column.nonNull[UUID] { (value, meta) =>
val MetaDataItem(qualified, nullable, clazz) = meta
valueToUUIDOption(value) match {
case Some(uuid) => Right(uuid)
case _ => Left(TypeDoesNotMatch("Cannot convert " + value + ":" + value.asInstanceOf[AnyRef].getClass + " to UUID for column " + qualified))
}
}
}
/**
* Implicit conversion from UUID to anorm statement value
*/
implicit def uuidToStatement = new ToStatement[UUID] {
def set(s: java.sql.PreparedStatement, index: Int, aValue: UUID): Unit = s.setObject(index, UUIDHelper.toByteArray(aValue))
}
}

In utils/UUIDHelper.scala

package utils

import java.nio.ByteBuffer
import java.nio.ByteOrder
import java.util.UUID

object UUIDHelper {
/**
* BIG ENDIAN is easier to read in mysql.
*/
private val ENDIANNESS = ByteOrder.BIG_ENDIAN
/**
* Deterministically convert a byte array into a UUID.
*
* @param value byte array to convert
* @return corresponding UUID
*/
def fromByteArray(value: Array[Byte]) = {
require(value.length == 16)
val buffer = ByteBuffer.wrap(value)
buffer.order(ENDIANNESS)
val high = buffer.getLong
val low = buffer.getLong
new UUID(high, low)
}
/**
* Deterministically convert a UUID into a byte array
*
* @param value UUID to convert
* @return corresponding byte array
*/
def toByteArray(value: UUID) = {
val buffer = ByteBuffer.wrap(new Array[Byte](16))
buffer.order(ENDIANNESS)
buffer.putLong(value.getMostSignificantBits)
buffer.putLong(value.getLeastSignificantBits)
buffer.array
}
}

Now, you can use UUIDs in your models! Here is a stupid, simple example.

In models/UserModel.scala

package models

import java.util.Date
import java.util.UUID
import AnormImplicits._
import anorm._
import anorm.SqlParser._
import play.api.Play.current
import play.api.db.DB

class UserModel extends AppModel {
protected val rowParser = {
get[UUID]("id") ~
get[String]("username") map {
case id ~ username =>
new User(id, username)
}
}
def findUserById(userId: UUID): Option[User] = {
DB.withConnection("main") { connection =>
SQL("""
SELECT `id`, `username`
FROM `users`
WHERE `id` = {id}
""").on(
"id" -> id
).as(rowParser.singleOpt)(connection)
}
}
}

object UserModel extends UserModel

As a bonus, I give some MySQL commands to help you read UUIDs in development.

SELECT HEX(`id`), `username` FROM `users`;

UPDATE `users` SET `username` = 'henry' WHERE `id` = UNHEX(REPLACE('11112222-3333-4444-5555-666677778888', '-', '')) LIMIT 1;