How to use UUID in a VARCHAR column with Slick?
I have a MySQL 5.7 database with several tables that store UUIDs in columns with a VARCHAR data type. I am trying to convert our code base to use Slick 3.1.1, but I am facing a problem with UUID to String conversions. This is my column definition:
def myCol: Rep[UUID] = column[UUID]("myCol", SqlType("VARCHAR"))
When I run queries I get exceptions like this:
Got the exception java.sql.SQLException: Incorrect string value: 'xDAxFDxDAuOL...' for column 'myCol' at row 1
From what I understand, Slick assumes that UUIDs should be stored as binary column types so I tried to implicitly convert to String using:
implicit val uuidToString = MappedColumnType.base[UUID, String](_.toString, UUID.fromString)
def myCol: Rep[UUID] = column[UUID]("myCol", SqlType("VARCHAR"))(uuidToString)
This works for insertion, but when I select values with myCol results come empty. Anyone knows how can I force Slick to use VARCHAR instead of BINARY(16) for the data conversion?
EDIT:
With the implicit this query does not return any results:
db.run { table.filter(_.myCol === val)).result }
But this one does:
db.run { table.result }.map(_.filter(_.myCol == val))
EDIT2:
I have a small project with a demo here: https://github.com/nmatpt/slick-uuid-test
You sure about empty values coming from db? I just checked my implementation (in one of the projects, exactly same solution) and it seems to work perfectly. Make sure your physical column (in DB) is actually of type VARCHAR (and not some binary).
EDIT: OK, the problem that we have is that UUID definition as BINARY is already included in MySQL profile. Quick (though maybe a little dirty) was of redefining it globally would be like this:
package profile
import java.sql.{PreparedStatement, ResultSet}
import slick.ast._
import slick.jdbc.MySQLProfile
object CustomMySqlProfile extends MySQLProfile {
import java.util.UUID
override val columnTypes = new JdbcTypes
class JdbcTypes extends super.JdbcTypes {
override val uuidJdbcType = new UUIDJdbcType {
override def sqlTypeName(sym: Option[FieldSymbol]) = "UUID"
override def valueToSQLLiteral(value: UUID) = "'" + value + "'"
override def hasLiteralForm = true
override def setValue(v: UUID, p: PreparedStatement, idx: Int) = p.setString(idx, toString(v))
override def getValue(r: ResultSet, idx: Int) = fromString(r.getString(idx))
override def updateValue(v: UUID, r: ResultSet, idx: Int) = r.updateString(idx, toString(v))
private def toString(uuid: UUID) = if(uuid != null) uuid.toString else null
private def fromString(uuidString: String) = if(uuidString != null) UUID.fromString(uuidString) else null
}
}
}
And then importing it instead of regular api :
import profile.CustomMySqlProfile.api._
This definitely works - but keep in mind that now UUID would be mapped to string throughout your whole application (instead of default binary). Basically I needed to override profile only because UUID conversion is built in. Normally you would use type mappings.
Similar thing got solved in a similar way here: https://github.com/slick/slick/issues/1446 (also UUID but for Oracle driver).
链接地址: http://www.djcxy.com/p/37340.html