如何用Doobie将Scala样例类映射到PostgreSQL的tstzmultirange列？

针对你的场景（PostgreSQL vacations表的tstzmultirange列与ScalaPersonalLeaves样例类的映射），需要自定义Doobie的Meta类型来完成转换，具体实现步骤如下：

1. 确认依赖配置 #

确保项目中引入Doobie核心库、PostgreSQL扩展库以及PostgreSQL驱动，以SBT为例：

libraryDependencies ++= Seq(
  "org.tpolecat" %% "doobie-core" % "1.0.0-RC2", // 替换为最新稳定版本
  "org.tpolecat" %% "doobie-postgres" % "1.0.0-RC2",
  "org.postgresql" % "postgresql" % "42.6.0" // 替换为最新驱动版本
)

2. 自定义Meta映射 #

Doobie默认不支持PostgreSQL的tstzmultirange类型，需要手动实现Scala类型与SQL类型的双向转换。

2.1 单个tstzrange与Interval的映射 #

先实现单个时间范围的转换，将PostgreSQL的tstzrange解析为Scala的Interval样例类：

import doobie._
import doobie.implicits._
import java.time.Instant
import org.postgresql.util.PGobject

// 定义tstzrange与Interval的Meta实例
implicit val intervalMeta: Meta[Interval] = Meta.Advanced.other[PGobject]("tstzrange").timap[Interval] { pgObj =>
  // 解析PGobject中的tstzrange字符串，格式示例："[2023-01-01T00:00:00+00:00,2023-01-07T00:00:00)"
  val rangeStr = pgObj.getValue
  val cleanStr = rangeStr.substring(1, rangeStr.length - 1)
  val Array(startStr, endStr) = cleanStr.split(",")
  Interval(Instant.parse(startStr), Instant.parse(endStr))
} { interval =>
  val pgObj = new PGobject()
  pgObj.setType("tstzrange")
  // 构造符合PostgreSQL格式的tstzrange字符串（左闭右开为默认格式）
  pgObj.setValue(s"[${interval.start},${interval.end})")
  pgObj
}

2.2 tstzmultirange与Seq[Interval]的映射 #

基于单个范围的映射，扩展实现多范围类型的转换：

implicit val intervalSeqMeta: Meta[Seq[Interval]] = Meta.Advanced.other[PGobject]("tstzmultirange").timap[Seq[Interval]] { pgObj =>
  // 解析tstzmultirange字符串，格式示例："{[2023-01-01T00:00:00+00:00,2023-01-07T00:00:00), [2023-07-01T00:00:00+00:00,2023-07-14T00:00:00)}"
  val multiRangeStr = pgObj.getValue
  val rangeSegments = multiRangeStr.substring(1, multiRangeStr.length - 1)
    .split("\\],\\s*\\[")
    .map(_.replaceAll("\\[|\\]", ""))
  
  rangeSegments.map { seg =>
    val Array(startStr, endStr) = seg.split(",")
    Interval(Instant.parse(startStr), Instant.parse(endStr))
  }.toSeq
} { intervals =>
  val pgObj = new PGobject()
  pgObj.setType("tstzmultirange")
  val rangeStrs = intervals.map(interval => s"[${interval.start},${interval.end})").mkString(",")
  pgObj.setValue(s"{$rangeStrs}")
  pgObj
}

注意：上述解析逻辑基于PostgreSQL默认的左闭右开范围格式，若你的数据库使用全闭、全开等其他格式，需调整字符串解析和拼接逻辑。

3. 实现CRUD操作 #

完成映射后，即可直接使用Doobie API进行数据操作：

3.1 插入数据 #

def insertLeaves(leaves: PersonalLeaves): Update0 = {
  sql"""
    INSERT INTO vacations (person, vacations)
    VALUES (${leaves.person}, ${leaves.onLeave})
  """.update
}

// 示例插入
val sampleLeaves = PersonalLeaves(
  "Alice",
  Seq(
    Interval(Instant.parse("2023-01-01T00:00:00Z"), Instant.parse("2023-01-07T00:00:00Z")),
    Interval(Instant.parse("2023-07-01T00:00:00Z"), Instant.parse("2023-07-14T00:00:00Z"))
  )
)

// 假设已初始化数据库连接Transactor
val xa: Transactor[IO] = ... // 替换为你的Transactor实例
insertLeaves(sampleLeaves).run.transact(xa).unsafeRunSync()

3.2 查询数据 #

def getLeaves(person: String): Query0[PersonalLeaves] = {
  sql"""
    SELECT person, vacations
    FROM vacations
    WHERE person = $person
  """.query[PersonalLeaves]
}

// 示例查询
getLeaves("Alice").unique.transact(xa).unsafeRunSync()
// 返回结果：PersonalLeaves(Alice,List(Interval(2023-01-01T00:00:00Z,2023-01-07T00:00:00Z), Interval(2023-07-01T00:00:00Z,2023-07-14T00:00:00Z)))

4. 注意事项 #

• 确保Instant使用UTC时区，与PostgreSQL的tstzrange类型时区保持一致，避免转换错误。
• 若业务需要处理无边界范围（如[NULL, 2023-12-31)），需在解析逻辑中添加对应分支处理。
• 可使用正则表达式替代简单字符串分割，提升解析逻辑的健壮性。

内容的提问来源于stack exchange，提问作者David Herskovics