sql - 使用 Postgres 枚举进行 JDBC 插入

Question

给定以下 Person SQL 和模型代码:

SQL

CREATE TYPE sex AS ENUM ('male', 'female');

CREATE TABLE person (
    id bigserial primary key,
    name varchar(100) NOT NULL,
    age integer NOT NULL,
    gender sex NOT NULL );

代码

object Person {

    sealed trait Gender 
    case object Male extends Gender 
    case object Female extends Gender 

    // credit to Travis Brown: http://stackoverflow.com/a/30946172/409976
    implicit val GenderShows: Show[Gender] = Show.shows {
      case Male   => "male"
      case Female => "female"
    }

}

case class Person private(id: Option[Long], name: String, age: Int, gender: Person.Gender)

我编写了以下对象来创建一个Person:

object PostgresRepository  {

    val xa = DriverManagerTransactor[IO](
      "org.postgresql.Driver", "jdbc:postgresql:person", "postgres", "postgres"
    )

    def insert1(name: String, age: Int, gender: Gender) =
        sql"insert into person (name, age, gender) values 
            ($name, $age, 
       ${implicitly[Show[Gender]].shows(gender)})".update.run.transact(xa)
}

然后，我运行 sbt console，并尝试插入:

scala> PostgresRepository.insert1("foo", 3, net.phone.model.Person.Male)
res0: scalaz.effect.IO[Int] = scalaz.effect.IOFunctions$$anon$6@2697b43b

scala> res0.unsafePerformIO
org.postgresql.util.PSQLException: ERROR: column "gender" is of type sex but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 56

Answer 1

正如已经建议的异常，您需要转换枚举值表达式。

def insert1(name: String, age: Int, gender: Gender) = {
    val sgender = implicitly[Show[Gender]].shows(gender)
    sql"insert into person (name, age, gender)
          values ($name, $age, CAST($sgender AS sex))
       ".update.run.transact(xa)
}