S-JIS[2011-03-06/2013-06-09] 変更履歴

Scala JDBC select

ScalaJDBCを使ってテーブルからselectしてみる。


Seqにselect

テーブルからデータをselectしてくるときの一番の問題は、どこにデータを格納するかということ。
JDBCでは項目毎にインデックスでデータを取得するので、配列か何かに入れるのが一番手軽。
for式(yield)を使うと、IndexedSeq(Vector)に入れてくる。

val sql = "select * from EMP order by EMPNO"

val st = conn.createStatement
val rs = st.executeQuery(sql)
while(rs.next) {
  // データ取得
  val r = for(n <- 1 to 8) yield rs.getObject(n)
  println(r)
}
rs.close
st.close
Vector(7369, SMITH, CLERK, 7902, 1980-12-17 00:00:00.0, 10733, null, 20)
Vector(7499, ALLEN, SALESMAN, 7698, 1981-02-20 00:00:00.0, 11532, 300, 30)
…

さて、しかし。
まずfor式で項目数を指定しているのが気になる。selectするSQLに応じて項目数を数えなければならない。
(これについては、メタデータ(ResultSetMetaData)のgetColumnCount()で項目数を取得できる)

また、Vector[Object]に1行分のデータを入れているのも気になる。
本来、Seq(Vectorの継承元)は同じ種類のデータを入れ、順次処理していくのに使うもの。テーブルの1行分のデータは、各項目を順次処理するものではない。
これがタプルならそういう目的のものだからそんなに気にならないが、タプルは初期化時点で項目を1つずつ指定する必要があるから、項目数が不定(なおかつ型が実行時まで決まらない)の場合には使えないんだよねぇ。


タプルにselect

一応、タプルもやってみた。

val sql = "select * from EMP order by EMPNO"

val st = conn.createStatement
val rs = st.executeQuery(sql)
while(rs.next) {
  // データ取得
  val r = (rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getDate(5), rs.getDouble(6), rs.getDouble(7), rs.getInt(8))
  println(r)
}
rs.close
st.close
(7369,SMITH,CLERK,7902,1980-12-17,10733.0,0.0,20)
(7499,ALLEN,SALESMAN,7698,1981-02-20,11532.0,300.0,30)
…

ResultSetのgetInt()・getDouble()はテーブル内のデータがNULLだった場合は0が返る。


NULLの場合にnullを返すような暗黙変換の関数を定義しておくとnullも受け取れる。[2011-03-09]

implicit def nullResultSet(rs:ResultSet) = new {
  def getNullInt(n: Int)      :java.lang.Integer = { val v = rs.getInt(n);    if(rs.wasNull) null else v }
  def getNullInt(n: String)   :java.lang.Integer = { val v = rs.getInt(n);    if(rs.wasNull) null else v }
  def getNullDouble(n: Int)   :java.lang.Double  = { val v = rs.getDouble(n); if(rs.wasNull) null else v }
  def getNullDouble(n: String):java.lang.Double  = { val v = rs.getDouble(n); if(rs.wasNull) null else v }
}
val sql = "select * from EMP order by EMPNO"

val st = conn.createStatement
val rs = st.executeQuery(sql)
while(rs.next) {
  // データ取得
  val r = (rs.getInt(1), rs.getString(2), rs.getString(3), rs.getNullInt(4), rs.getDate(5), rs.getNullDouble(6), rs.getNullDouble(7), rs.getNullInt(8))
  println(r)
}
rs.close
st.close
(7369,SMITH,CLERK,7902,1980-12-17,10733.0,null,20)
(7499,ALLEN,SALESMAN,7698,1981-02-20,11532.0,300.0,30)
…

Mapにselect

JDBCにはメタデータというのがあって、select結果の項目名や項目数を取得することが出来る。
これを使って、項目名と値のペアのMapを作れる。

val sql = "select * from EMP order by EMPNO"

val st = conn.createStatement
val rs = st.executeQuery(sql)
val md = rs.getMetaData
while(rs.next) {
  // データ取得
  val r = (1 to md.getColumnCount).map{n => md.getColumnName(n)->rs.getObject(n)}.toMap
  println(r)
}
rs.close
st.close
Map(MGR -> 7698, DEPTNO -> 30, JOB -> SALESMAN, EMPNO -> 7499, COMM -> 300, ENAME -> ALLEN, HIREDATE -> 1981-02-20 00:00:00.0, SAL -> 11532)
Map(MGR -> 7698, DEPTNO -> 30, JOB -> SALESMAN, EMPNO -> 7521, COMM -> 500, ENAME -> WARD, HIREDATE -> 1981-02-22 00:00:00.0, SAL -> 11182)
…

しかしまだ、この型はMap[String, Object]であり、値が一律Objectになってしまっている。(当然だけど)


ケースクラスにselect

項目毎に決まった型でデータを持ちたいと思ったら、どうしても個別のクラスにしなければならない。
insertのときと同じく、ケースクラスにしてみよう。

case class Emp(
  var EMPNO: java.lang.Integer = null,
  var ENAME: String = null,
  var JOB: String = null,
  var MGR: java.lang.Integer = null,
  var HIREDATE: Date = null,
  var SAL: java.lang.Double = null,
  var COMM: java.lang.Double = null,
  var DEPTNO: java.lang.Integer = null
)

// 項目一覧
val cs = classOf[Emp].getMethods.withFilter(_.getName.forall(_.isUpper)).map(_.getName)
// セッターメソッド一覧
val ss = classOf[Emp].getMethods.withFilter(_.getName.endsWith("_$eq")).map{m => m.getName.dropRight(4)->m}.toMap

val sql = "select " + cs.mkString(", ") + " from EMP order by EMPNO";

val st = conn.createStatement
val rs = st.executeQuery(sql)
while(rs.next) {
  val r = Emp()
  cs.foreach{ n => ss(n).invoke(r, rs.getObject(n)) }
  println(r)
}
rs.close
st.close

ケースクラスのフィールドに値をセットするメソッド(セッターメソッド)は、「フィールド名_=」という名前になっている。
ただしコンパイルすると「=」は「$eq」に置き換えられるので、末尾が「_=」であるという条件は「endsWith("_$eq")」になる。


しかし案の定、セッターメソッド実行(invoke)で例外が発生してしまった。

java.lang.IllegalArgumentException: argument type mismatch

何の型から何の型へ変換しようとしてタイプミスマッチだったのか分からないのが困りもの。
とりあえず表示するように変えてみよう。

val rs = st.executeQuery(sql)
rs.next
val r = Emp()
cs.foreach{ n =>
  val m = ss(n)
  val v = rs.getObject(n)
  println(m.getParameterTypes()(0), if(v!=null) v.getClass else null)
}
rs.close
(class java.lang.Integer,class java.math.BigDecimal)
(class java.lang.String,class java.lang.String)
(class java.lang.String,class java.lang.String)
(class java.lang.Integer,class java.math.BigDecimal)
(class java.sql.Date,class java.sql.Timestamp)
(class java.lang.Double,class java.math.BigDecimal)
(class java.lang.Double,null)
(class java.lang.Integer,class java.math.BigDecimal)

ふーむ、数値は全部BigDecimalだし、日付はTimestampなのか。(Oracle以外でどうなのかは知らないが)
こりゃ代入先の型に応じて場合分けしてやるしかないかぁ。
caseに直接caseOf[]を指定できないので、ガード条件(if)を使っている。また、対応していないクラスが来た場合はMatchErrorが出るが、クラス名がそれで分かるからOK)

val rs = st.executeQuery(sql)
while(rs.next) {
  val r = Emp()
  cs.foreach{ n =>
    val m = ss(n)
    m.getParameterTypes()(0) match {
      case c if c==classOf[java.lang.Integer] => m.invoke(r, rs.getInt(n).asInstanceOf[AnyRef])
      case c if c==classOf[java.lang.Double]  => m.invoke(r, rs.getDouble(n).asInstanceOf[AnyRef])
      case c if c==classOf[String]            => m.invoke(r, rs.getString(n))
      case c if c==classOf[java.sql.Date]     => m.invoke(r, rs.getDate(n))
    }
  }
  println(r)
}
rs.close
Emp(7369,SMITH,CLERK,7902,1980-12-17,10733.0,0.0,20)
Emp(7499,ALLEN,SALESMAN,7698,1981-02-20,11532.0,300.0,30)
…

ResultSetのgetInt()・getDouble()はJavaのプリミティブ型を返すので、invokeを呼ぶ際には参照型AnyRefとか)にキャスト(変換)する必要がある。
(上で作ったgetNullInt()getNullDouble()を呼ぶようにすればキャストは不要だし、NULLにも対応する。[2011-03-09]

なんだかずいぶん長くなってしまった(苦笑) REPL向きじゃないなぁ。


まとめて関数化してみた。

引数にケースクラスのインスタンスを渡して、内部ではテーブルの行毎にインスタンスをコピーして新しいインスタンスを作り、そのリストを返す。
このコピーを行う為にcreate()というメソッドが必要なことになってしまったが…。
とにかくケースクラスさえ作ればselectする項目も簡単に絞れる。
全項目を用意したクラスで一部しか値が入らない、というのは嫌いなので、敢えてそうしてみた(笑)

def select[C <: { def create():C }](conn: Connection, cc: C, tableName: String, other: String): List[C] = {

  val ms = cc.getClass.getMethods

  // 項目一覧
  val cs = ms.withFilter(_.getName.forall(_.isUpper)).map(_.getName)
  // セッターメソッド一覧
  val ss = ms.withFilter(_.getName.endsWith("_$eq")).map{m => m.getName.dropRight(4)->m}.toMap

  val sql = "select " + cs.mkString(", ") + " from " + tableName + " " + other;

  var list = List[C]()
  val st = conn.createStatement
  try {
    val rs = st.executeQuery(sql)
    try {
      while(rs.next) {
        val r = cc.create
        cs.foreach{ n =>
          val m = ss(n)
          m.getParameterTypes()(0) match {
            case c if c==classOf[java.lang.Integer] => m.invoke(r, rs.getNullInt(n))
            case c if c==classOf[java.lang.Double]  => m.invoke(r, rs.getNullDouble(n))
            case c if c==classOf[String]            => m.invoke(r, rs.getString(n))
            case c if c==classOf[java.sql.Date]     => m.invoke(r, rs.getDate(n))
          }
        }
        list = r :: list
      }
    } finally {
      rs.close
    }
  } finally {
    st.close
  }
  list.reverse
}
case class Emp(
  var EMPNO: java.lang.Integer = null,
  var ENAME: String = null,
  var JOB: String = null,
  var MGR: java.lang.Integer = null,
  var HIREDATE: Date = null,
  var SAL: java.lang.Double = null,
  var COMM: java.lang.Double = null,
  var DEPTNO: java.lang.Integer = null
) {
  def create() = copy()
}
val list = select(conn, new Emp(), "EMP", "order by EMPNO")

ケースクラスに「create()」という余計なメソッド(新しいインスタンスを生成するメソッド)を追加している。

最初、このselect関数は以下のような定義にしたかった。

def select[C](conn: Connection, cc: C, tableName: String, other: String): List[C] =

でもこれだと、関数内部で「cc.getClass」をしているのでコンパイルエラーになる。
型パラメーターに何も制限が無いとAnyクラス扱いとなり、AnyにはgetClassが無いから。
getClassがあるのはAnyRef。という訳で

def select[C <: AnyRef](conn: Connection, cc: C, tableName: String, other: String): List[C] = {
〜
        val r = cc.copy()
〜
}

次いで、copy()メソッドでエラー。
ケースクラスには自分自身のインスタンスをコピーするcopy()メソッドがあるんだけど、型パラメーターCにはケースクラスである宣言がされてないから。
ところがケースクラスである事を示す共通の親クラス(トレイト)って存在しない(と思う)し、
copy()メソッドの引数はケースクラスによって異なるから、構造的部分型を使って「C <: { def copy():C }」と書いても実際のcopy()メソッドとは合致しない。
で、仕方なくcreate()というメソッドを別途定義することにした。

def select[C <: { def create():C }](conn: Connection, cc: C, tableName: String, other: String): List[C] =

ここでさらに、ケースクラスに特別なメソッドを追加せず、呼び出し時にcreate()メソッドだけ追加できないかと考えた。

// 単なるケースクラスEmpを使ってこういう呼び出しがしたい
val list = select(conn, new Emp{ def create() = copy() }, "EMP", "order by EMPNO")

でもこの合成クラス(Empとcreate()の合体)がCになるから、create()はCそのものを返していない(Empを返している)ので、型パラメーターの条件に合致しないんだよね…。


ふーむ、しかしこの余計なインスタンス生成メソッドは無くせないかなぁ。[2011-03-09]

コピーメソッドは「copy」という名前だと分かっているし、リフレクションで引数の型を取って、それに応じてデフォルト値を与えれば、呼べなくはないか。
あるいはコンストラクターもリフレクションで取れるし、クラス名を取得して末尾に「$」を付ければコンパニオンオブジェクトも取得できるからapply()も呼べる。

ただ、copyメソッドをオーバーロードして複数作られるとどれを呼べばいいか分からないし、派生クラスを渡された場合はコンストラクターやコンパニオンオブジェクトが正しく取れないような気がする。


つーか、素直にインスタンスを生成する関数を渡せばいいんじゃん![2011-03-09]
Javaと違ってScalaは簡単に関数が渡せるんだから^^;

def select[C](conn: Connection, c: Class[C], create: ()=>C, tableName: String, other: String): List[C] = {

  val ms = c.getMethods
〜
        val r = create()
〜
}

val list = select(conn, classOf[Emp], () => new Emp, "EMP", "order by EMPNO")
val list = select(conn, classOf[Emp], () => Emp()  , "EMP", "order by EMPNO")

しかも、ClassManifestを使えばクラスも(明示的には)渡す必要ないし!
インスタンス生成関数createも、呼び出し時に引数が要らないから「() => C」を「=> C」に出来る。

def select[C](conn: Connection, create: =>C, tableName: String, other: String)(implicit cm:ClassManifest[C]): List[C] = {

  val ms = cm.erasure.getMethods

  // 項目一覧
  val cs = ms.withFilter(_.getName.forall(_.isUpper)).map(_.getName)
  // セッターメソッド一覧
  val ss = ms.withFilter(_.getName.endsWith("_$eq")).map{m => m.getName.dropRight(4)->m}.toMap

  val sql = "select " + cs.mkString(", ") + " from " + tableName + " " + other;

  var list = List[C]()
  val st = conn.createStatement
  try {
    val rs = st.executeQuery(sql)
    try {
      while(rs.next) {
        val r = create
        cs.foreach{ n =>
          val m = ss(n)
          m.getParameterTypes()(0) match {
            case c if c==classOf[java.lang.Integer] => m.invoke(r, rs.getNullInt(n))
            case c if c==classOf[java.lang.Double]  => m.invoke(r, rs.getNullDouble(n))
            case c if c==classOf[String]            => m.invoke(r, rs.getString(n))
            case c if c==classOf[java.sql.Date]     => m.invoke(r, rs.getDate(n))
          }
        }
        list = r :: list
      }
    } finally {
      rs.close
    }
  } finally {
    st.close
  }
  list.reverse
}
case class Emp(
  var EMPNO: java.lang.Integer = null,
  var ENAME: String = null,
  var JOB: String = null,
  var MGR: java.lang.Integer = null,
  var HIREDATE: Date = null,
  var SAL: java.lang.Double = null,
  var COMM: java.lang.Double = null,
  var DEPTNO: java.lang.Integer = null
)
val list = select(conn, Emp(), "EMP", "order by EMPNO")
val list = select(conn, { Emp() }, "EMP", "order by EMPNO")

selectの第2引数の型は「=>C」なので、指定している「Emp()」はselect呼び出し前に実行されるのではなく、「Emp()」という関数を渡してselect内部で何度も呼び出される。
関数を渡していることを明示するために、波括弧でくくった方がいい(分かり易い)かも。


select補間子

Scala2.10でStringContext(文字列補間)とDynamicが加わったので、それを使ってselectする方法を考えてみた。[2013-06-09]
以下の様な感じで実行することが出来る。

scala> val (user, pass) = ("scott", "tiger")
user: String = scott
pass: String = tiger

scala> val conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", user, pass)
conn: java.sql.Connection = oracle.jdbc.driver.T4Connection@7d2ba6d7

scala> val s = select" EMPNO, ENAME from EMP$conn where EMPNO=7788"
s: Seq[Entity] = List(Entity:Map(EMPNO -> 7788, ENAME -> SCOTT))

scala> val e = s.head
e: Entity = Entity:Map(EMPNO -> 7788, ENAME -> SCOTT)

scala> e.empno
res0: Any = 7788

scala> e.ename
res1: Any = SCOTT

scala> conn.close()
val s = select" * from ${conn}EMP where SAL >= 3000"
s.foreach{ e =>
  printf("%s %s%n", e.ename, e.sal)
}

select補間子を用意することで、「select"〜"」を解釈することが出来る。

コネクションを渡す必要があるので、${}でコネクションを渡すようにしてみた。
SQLを解釈する際に空文字列に置換することにしたので、文字列内のどこにあってもいい。
つまり「EMP$conn」でも「${conn}EMP」でも「E${conn}MP」でも問題ない(笑)
まぁテーブル名付近にある方がSQL文っぽい感じで良いと思う。

結果はEntityというクラスで返ってくるが、これがDynamicなので、「e.ename」の様に書ける。
つまり、あたかもenameというフィールドがあるかの様に扱える。


これを実現する為のクラスは以下のもの。

import java.sql.{ Array => _, _ }
import javax.sql._
import scala.language.dynamics
class Entity(val map: Map[String, Any]) extends Dynamic {
  def selectDynamic(name: String) = map(name.toUpperCase)

  override def toString() = "Entity:" + map
}

このEntityクラスはMapのラッパーのような感じ。
selectDynamicを実装することで、フィールド取得の形式でMapの値を取得できる。

implicit class SqlStringContext(val sc: StringContext) extends AnyVal {
  def select(args: Any*): Seq[Entity] = {
    val conn = args.find(_.isInstanceOf[Connection]).get.asInstanceOf[Connection]
    val ss = args.map(s => if (s.isInstanceOf[Connection]) "" else s)

    val sql = "select " + sc.s(ss: _*) //s補間子を利用してString化

    var result = Seq.empty[Entity]

    val st = conn.createStatement
    try {
      val rs = st.executeQuery(sql)
      try {
        val md = rs.getMetaData

        while (rs.next) {
          val r = (1 to md.getColumnCount).map{ n => md.getColumnName(n) -> rs.getObject(n) }.toMap
          result :+= new Entity(r)
        }
      } finally rs.close
    } finally st.close

    result
  }
}

StringContextの暗黙クラスでselectメソッドを用意することにより、select補間子を実現する。
補間子メソッドの引数には${}の値が入ってくるので、その中からConnectionだけ抽出している。


けっこう少ないステップ数でフィールドアクセスっぽいことが出来てしまった(笑)

しかしこの方法だと、Entity内に存在しない名前を指定しても、実行するまで正しいかどうか分からないのが難点。
(フィールドアクセスの様な書き方なので、存在しない名前ならコンパイル時にエラーになって欲しいよな、やっぱり)

scala> e.zzz
java.util.NoSuchElementException: key not found ZZZ


また、戻り値の型がAnyになってしまうのも微妙なところ。
これに関しては、selectDynamicの戻り値の型をジェネリクスにしてしまうことも出来るが。

  def selectDynamic[A](name: String) = map(name.toUpperCase()).asInstanceOf[A]

使う際に型を明示しないとエラーになってしまう(苦笑)

scala> e.ename
java.lang.ClassCastException: java.lang.String cannot be cast to scala.runtime.Nothing$

scala> val name:String = e.ename
name: String = SCOTT

scala> e.ename: String
res13: String = SCOTT

JDBC実験へ戻る / Scala実験へ戻る / Scala目次へ戻る / 技術メモへ戻る
メールの送信先:ひしだま