Play2 for Scala中文文档 – 10. 使用anorm操作数据库

(本文译者是爱国者)

Anorm, simple SQL data access

Play包含一个叫 Anorm 的数据库存取层。该数据库存取层使用原生SQL与数据库交互,并提供了一组API来解析和转换查询结果集.

Anorm不是一个对象关系映射框架

我们在接下来的例子中使用MySQL官方提供的样本数据库([[ world sample database || http://dev.mysql.com/doc/world-setup/en/world-setup.html ]]) 。请参照这个[[ 文档 | ScalaDatabase ]] 配置好数据源..

概述

改用原生SQL操作数据库可能会让人觉得很奇怪，尤其习惯于ORM框架的java开发者. 虽然我们承认在Java中使用这些工具几乎是有必要的，但我们也认为使用像Scala这种表达能力很强的高级编程语言开发的应用完全没有必要使用这些ORM工具, 使用了反而会产生反效果.

提供更好的API减轻使用JDBC的痛苦

我们同意,尤其在Java中，直接使用JDBC编程是一件很乏味的事, 因为你不得不处理各种受检查异常(checked exception)， 不得不反复将原始结果集转换成你需要的数据结构。

我们为JDBC提供了一套简单易用的API。如果你使用Scala语言编写应用，你无需为各种异常处理而烦恼, 使用函数式语言转换数据是十分简单的。事实上，我们希望通过提供一组API让JDBC和Scala之间的数据结构转换变得更有效率。

你不需要另一种DSL操作关系型数据库

SQL是操作数据库最好的领域特定语言（Domain Specific Language, DSL）。 我们不需要发明一种新的DSL, 而且SQL的语法和数据库特性在不同的供应商之间存在差异。如果你尝试抽象出一种新的DSL，你不得不像Hibernate那样处理几种数据库的方言, 而且使用一些有趣的特性会受到限制。

Play提供了一些预定义的SQL语句，但目的不是为了隐藏某些操作细节, 只是为一些常用的查询减少编码量。而且你总是可以改回原生SQL.

使用类型安全的DSL生成SQL是错误的

有些观点认为，使用类型安全的DSL是更好的做法,因为所有的查询都可以经过编译器检查。不幸的是，编译器只基于你编写的对象关系映射的元数据模型定义来检验你的查询。 元数据模型无法保证是正确的。即使查询代码被编译器判定为类型正确，但仍有可能由于不匹配的关系对象映射而导致运行时出错。

掌控SQL代码

对象关系映射在一般情况下会工作得很好, 但当要处理复杂的schemas或者遗留的数据库，你就得在ORM框架上花大量时间生成你需要的SQL。为Hello world这种简单应用编写原生的SQL查询也许会很乏味，但对于实际应用来说，最终会节省时间并简化代码.

执行SQL查询

开始之前，你需要知道如何执行SQL查询。

首先，导入 anorm._ ，然后使用 SQL 对象创建查询。你需要一个 Connection 对象运行查询。 Connection 对象可以从 play.api.db.DB.withConnection 函数取得

import anorm._ 

DB.withConnection { implicit c =>
  val result: Boolean = SQL("Select 1").execute()
}

execute() 函数返回一个表示查询是否成功的布尔值。

如果要执行更新查询，使用 executeUpdate() 函数, 这个函数会返回记录被更新的个数

val result: Int = SQL("delete from City where id = 99").executeUpdate()

如果向一个拥有 Long 类型的自增主键的表插入数据，使用 executeInsert() .如果该表有联合主键或主键不是 Long 类型，那么可以向 executeInsert() 传入一个 ResultSetParser 返回正确的主键。

val id: Int = SQL("insert into City(name, country) values ({name}, {country}")
              .on("Cambridge", "New Zealand").executeInsert()

由于Scala支持多行文本的写法，因此可以将复杂的SQL表达式写成：

val sqlQuery = SQL(
  """
    select * from Country c
    join CountryLanguage l on l.CountryCode = c.Code
    where c.code = 'FRA';
  """
)

如果SQL查询包含动态参数，可以使用占位符表示，如 {name} , 然后在使用 on() 函数赋值.

SQL(
  """
    select * from Country c
    join CountryLanguage l on l.CountryCode = c.Code
    where c.code = {countryCode};
  """
).on("countryCode" -> "FRA")

使用Stream API获取数据

获取查询结果的第一种方法是使用Stream API. 调用 SQL 表达式对象上的 apply() 方法会返回一个 Stream 集合， Stream 集合中的每个元素代表一个 Row 实例，每个 Row 实例可以看成一个映射表（dictionary)

// Create an SQL query
val selectCountries = SQL("Select * from Country")

// Transform the resulting Stream[Row] to a List[(String,String)]
val countries = selectCountries().map(row =>
  row[String]("code") -> row[String]("name")
).toList

在下面的例子中，我们统计一下 Country 表共有有多少条记录, 因此查询结果只会返回包含一个字段的一条记录.

// First retrieve the first row
val firstRow = SQL("Select count(*) as c from Country").apply().head

// Next get the content of the 'c' column as Long
val countryCount = firstRow[Long]("c")

使用模式匹配

可以使用模式匹配的方法从 Row 抽取出所需的内容。在这种情况下，字段名不再重要，只要求顺序和参数类型匹配即可。

下面的例子使用模式匹配的方法将每条记录匹配到合适的Scala类型中：

case class SmallCountry(name:String)
case class BigCountry(name:String)
case class France

val countries = SQL("Select name,population from Country")().collect {
  case Row("France", _) => France()
  case Row(name:String, pop:Int) if(pop > 1000000) => BigCountry(name)
  case Row(name:String, _) => SmallCountry(name)
}

由于 collect(_) 会忽略偏应用函数没有定义的情况， 因此你可以忽略某些记录。

特殊数据类型

Clob类型

可以这样抽取CLOBs/TEXTs类型的值：

SQL("Select name,summary from Country")().map {
  case Row(name: String, summary: java.sql.Clob) => name -> summary
}

这里我们特地使用 map 函数，使得当出现我们非预期的记录时抛出异常。

二进制类型

抽取二进制类型的值也是类似的做法：

SQL("Select name,image from Country")().map {
  case Row(name: String, image: Array[Byte]) => name -> image
}

数据库互操作（Database interoperability）

对于记录的某个字段，不同的数据库引擎会返回不同的数据类型. 比如一个‘smallint’类型字段, org.h2.Driver会返回一个short，org.postgresql.Driver会返回一个Integer. 一个简单的解决办法是为每种数据库编写特定的版本。(比如一个版本用于开发数据库，一个版本用于生产数据库)

处理空值(null)的列

如果列含有空值( Null ），那么最好处理成 Option 类型。 例如 Country 表的 indepYear 列允许空值,那么使用 Option[Int] 匹配该列:

SQL("Select name,indepYear from Country")().collect {
  case Row(name:String, Some(year:Int)) => name -> year
}

或者用这种写法：

SQL("Select name,indepYear from Country")().map { row =>
  row[String]("name") -> row[Option[Int]]("indepYear")
}

如果将 indepYear 列直接匹配为 Int ， 那么当遇到null值会抛出 UnexpectedNullableFound(COUNTRY.INDEPYEAR) 异常：

SQL("Select name,indepYear from Country")().map { row =>
  row[String]("name") -> row[Int]("indepYear")
}

使用parser API

你可以使用parser API创建通用的可重用的解析器解析任意查询结果集。

备注: 这种做法是有用的，因为大部分we应用的查询都返回相似的结果集。例如，你可以定义一个能用于解析 Country 表查询结果集的解析器和一个用于解析 Language 表查询结果集的解析器。然后在用到连接查询的地方将两个解析器组合起来使用。 记得导入 anorm.SqlParser._

单一结果集

首先你需要一个 RowParser 将一条记录解析成一个Scala对象。下面定义一个将单一列的记录转换成 Long 值对象的记录解析器（row parser)：

val rowParser = scalar[Long]  // row parser that parse a record into scala Long

再将它转换成 ResultSetParser (结果集解析器):

val rsParser = rowParser.single  // resultset parser that transform the result set into an object.

然后可以使用 rsParser 将 select count 产生的结果集转换成 Long 值对象：

val count: Long = SQL("select count(*) from Country").as(scalar[Long].single)

单一可选结果集

假如要根据国家名查找country_id, 但表中可能不存在该国家名（或输入了一个错误的国家名），这种情况下查询会返回一个空值。 这时应该使用 singleOpt 解析器:

val countryId: Option[Long] = SQL("select country_id from Country C where C.country='France'").as(scalar[Long].singleOpt)

更复杂的结果集

下面我们编写一个更复杂的解析器. 首先, str("name") ~ int("population") 会创建一个记录解析器。这个解析器能解析一条包含类型为String的 name 字段和类型为Integer的 population 字段的记录。然后在此基础上使用 * 创建结果集解析器（ ResultSetParser ）,

val populations:List[String~Int] = {
  SQL("select * from Country").as( str("name") ~ int("population") * )
}

这个查询结果集最终被转换成 List[String~Int] 类型 ———— 一个包含国家名和该国人口数量的列表。

你可以将上面的例子改成:

val result:List[String~Int] = {
  Sql("select * from Country").as(get[String]("name")~get[Int]("population")*)
}

可能大家已经注意到 String~Int 这个类型。这是一个 Anorm 类型，但用起来不会很方便， 相信改成 （String, Int) 这样的二元组会更好。你可以在 RowParser 上使用 map 函数将一条记录转换成合适的类型：

str("name") ~ int("population") map { case n~p => (n,p) }

备注: 这里我们将一条记录转换成一个二元组 (String,Int) ，你可以换成你想要的类型，比如样本类(case class)

由于将 A~B~C 转换成(A,B,C)是很普遍的, Play提供了一个 flatten 函数实现同样的功能。上面的例子可以改成：

val result:List[(String,Int)] = {
  SQL("select * from Country").as(
    str("name") ~ int("population") map(flatten) *
  )
}

更复杂的例子

现在尝试更复杂的例子。如何根据国家代码查出国家名以及该国所使用的所有语言？

select c.name, l.language from Country c
    join CountryLanguage l on l.CountryCode = c.Code
    where c.code = 'FRA'

我们将查询结果集解析成 List[(String,String)] (一个包含name和language二元组的列表）

var p: ResultSetParser[List[(String,String)]] = {
  str("name") ~ str("language") map(flatten) *
}

最后我们会得到这样的结果：

List(
  ("France", "Arabic"),
  ("France", "French"),
  ("France", "Italian"),
  ("France", "Portuguese"),
  ("France", "Spanish"),
  ("France", "Turkish")
)

然后使用scala collection API转换成所需的结果：

case class SpokenLanguages(country:String, languages:Seq[String])

languages.headOption.map { f =>
  SpokenLanguages(f._1, languages.map(_._2))
}

最后整理一下：

case class SpokenLanguages(country:String, languages:Seq[String])

def spokenLanguages(countryCode: String): Option[SpokenLanguages] = {
  val languages: List[(String, String)] = SQL(
    """
      select c.name, l.language from Country c
      join CountryLanguage l on l.CountryCode = c.Code
      where c.code = {code};
    """
  )
  .on("code" -> countryCode)
  .as(str("name") ~ str("language") map(flatten) *)

  languages.headOption.map { f =>
    SpokenLanguages(f._1, languages.map(_._2))
  }
}

接下来，将官方语言和其他语言分开：

case class SpokenLanguages(
  country:String,
  officialLanguage: Option[String],
  otherLanguages:Seq[String]
)

def spokenLanguages(countryCode: String): Option[SpokenLanguages] = {
  val languages: List[(String, String, Boolean)] =  Sql(
    """
      select * from Country c
      join CountryLanguage l on l.CountryCode = c.Code
      where c.code = {code};
    """
  )
  .on("code" -> countryCode)
  .as {
    str("name") ~ str("language") ~ str("isOfficial") map {
      case n~l~"T" => (n,l,true)
      case n~l~"F" => (n,l,false)
    } *
  }

  languages.headOption.map { f =>
    SpokenLanguages(
      f._1,
      languages.find(_._3).map(_._2),
      languages.filterNot(_._3).map(_._2)
    )
  }
}

如果在Mysql world sample数据库上运行上述的查询，会得到：

$ spokenLanguages("FRA")
> Some(
    SpokenLanguages(France,Some(French),List(
        Arabic, Italian, Portuguese, Spanish, Turkish
    ))
)