JavaとScalaでJDBC使う。

Java8のGold資格を取得するためのついでの勉強メモです。
Scalaで`try−with-resource`的な仕組みがなさそうだったので、後半はScalaでの実装方法について記述しています。


## Java

### JDBCの基本的な使い方。

コネクション管理のクラスを作っておく。

```java
import java.sql.*;

/**
 * DBコネクション取得クラス
 */
public class DbConnector {
    public static Connection getConnect() throws SQLException {
        String url = "jdbc:mysql://localhost/golddb";
        String user = "username";
        String password = "password";
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }
}
```

`try-with-resource`を使って接続する。
基本的なポイントは以下。

1. `Connection`オブジェクトを生成してコネクションを取得
2. コネクションを使って`Statement`オブジェクトを生成する。
3. `Statement`オブジェクトを使ってSQLを実行
    * 参照系の場合は通常は`executeQuery`を使い、結果を`ResultSet`オブジェクトに格納して使う。
    * 更新系の場合は`executeUpdate`を使う。
    * `execute`は参照、更新のどちらもできるが、極力使わない。

#### executeQueryメソッド

```java
import java.sql.*;


public class JDBCExecuteQuerySample {
    public static void main(String[] args) {
        String sql = "SELECT dept_name FROM department";

        try (Connection connection = DbConnector.getConnect();
             Statement stmt = connection.createStatement()) {

            ResultSet rs = stmt.executeQuery(sql);

            if (rs != null) {
                System.out.println("rs != null");
            }

            while (rs.next()) {
                System.out.println("dept_name : " + rs.getString(1));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
```

#### executeUpdateメソッド

```java
import java.sql.*;


public class JDBCExecuteUpdateSample {
    public static void main(String[] args) {
        try (Connection connection = DbConnector.getConnect();
             Statement stmt = connection.createStatement()) {

            String sql =
                    "INSERT INTO department VALUES (6 , 'Plannning', 'Yokohama', '909-000-0000')";

            int col = stmt.executeUpdate(sql);
            System.out.println("col : " + col);

        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }
}
```

#### executeメソッド

```java
import java.sql.*;

public class JDBCExecuteSample {
    public static void main(String[] args) {

        try (Connection connection = DbConnector.getConnect();
             Statement statement = connection.createStatement()) {

            String[] sqls = {
                    //"insert into department values " + "(7, 'Planning', 'Yokohama', '055-555-5555')",
                    "select dept_name from department where dept_code = 2"
            };

            for (String sql : sqls) {
                // executeのメソッドの戻り値はboolean
                boolean isResultSet = statement.execute(sql);


                if (isResultSet) { // selectの場合はisResultSetの結果がtrueになる。
                    // executeで実行した場合はResultSetのオブジェクトを
                    // getResultSet()メソッドで取得する
                    ResultSet rs = statement.getResultSet();

                    rs.next();
                    System.out.println(rs.getString(1));

                } else { // insertの場合はisResultSetはfalse
                    int count = statement.getUpdateCount();
                    System.out.println(count);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
```

### PreparedStatement

大抵の場合はStatementではなくてPreparedStatementを利用してSQLインジェクション対策する。

```java
import java.sql.*;

public class JDBCPreparedStatementSample {
    public static void main(String[] args) {

        String sql = "SELECT dept_code, dept_name FROM department WHERE dept_name = ?";

        try (Connection connection = DbConnector.getConnect();
             PreparedStatement statement = connection.prepareStatement(sql)) {

            // ?の部分をセットして実行。
            statement.setString(1, "Education");
            ResultSet resultSet = statement.executeQuery();

            resultSet.next();
            System.out.format("dept_code: %d, dept_name: %s",
                    resultSet.getInt(1), resultSet.getString(2));

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
```

### ResultSetの拡張

`ResultSet`オブジェクトを順方向モード、かつ読み込み専用で利用するだけでなく、以下の機能も使用できる。

* 問い合わせ結果のスクロール、絶対・相対位置指定
* ResultSetオブジェクト上でデータの挿入・更新 

### ResultSetインタフェースの定数

| 定数名 | 説明 |
|:------|:----|
|CONCUR_READ_ONLY| 更新できないResultSetオブジェクトの並行処理モード|
|CONCUR_UPDATABLE| 更新できるResultSetオブジェクトの並行処理モード|
|TYPE_FORWARD_ONLY | カーソルが順方向にしか移動しないResultSetオブジェクトのタイプ|
|TYPE_SCROLL_INSENTIVE| スクロール可能だが、データベースのデータに対して行われた変更を反映しないResultSetオブジェクトのタイプ|
|TYPE_SCROLL_SENSITIVE| スクロール可能で、データベースの最新の内容を反映するResultSetオブジェクトタイプ|

使い方は、以下のように`createStatement`メソッドの引数に定数を指定する。

```java
Statement stmt = connection.createStatement(
                     ResultSet.TYPE_SCROLL_INSENSITIVE, // 順方向、または逆方向でデータベースに対して変更しないように指定
                     ResultSet.CONCUR_READ_ONLY // 更新を不可、参照のみに指定
                     )
```


[注意事項]  
DB製品が実装しているJDBC(Oracle, PostgreSQL, MySQLなど)の実装にもよる。
たとえば、MySQLの場合は、JDBCドライバ(mysql-connector-java-5.1.42.jar)では`TYPE_SCROLL_INSENSITIVE`しかサポートしていなかった。
指定した場合でも、暗黙でスクロール可能な`ResultSet`オブジェクトになる。

```java

import java.sql.*;

public class JDBCGetMetaDataSample {

    public static void main(String[] args) {
        try (Connection connection = DbConnector.getConnect()) {

            DatabaseMetaData metaData = connection.getMetaData();

            System.out.println("TYPE_SCROLL_SENSITIVE: " + metaData.supportsResultSetType(
                    ResultSet.TYPE_SCROLL_SENSITIVE));
            System.out.println("TYPE_SCROLL_INSENSITIVE: " + metaData.supportsResultSetType(
                    ResultSet.TYPE_SCROLL_INSENSITIVE));
            System.out.println("TYPE_FORWARD_ONLY: " + metaData.supportsResultSetType(
                    ResultSet.TYPE_FORWARD_ONLY));
            System.out.println("CONCUR_READ_ONLY: " + metaData.supportsResultSetType(
                    ResultSet.CONCUR_READ_ONLY));
            System.out.println("CONCUR_UPDATABLE: " + metaData.supportsResultSetType(
                    ResultSet.CONCUR_UPDATABLE));

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
```

```
TYPE_SCROLL_SENSITIVE: false
TYPE_SCROLL_INSENSITIVE: true
TYPE_FORWARD_ONLY: false
CONCUR_READ_ONLY: false
CONCUR_UPDATABLE: false
```

### カーソル移動用のメソッド

```
mysql> select * from department;
+-----------+-------------+--------------+--------------+
| dept_code | dept_name   | dept_address | pilot_number |
+-----------+-------------+--------------+--------------+
|         1 | Sales       | Tokyo        | 03-3333-xxxx |
|         2 | Engineer    | Yokohama     | 045-444-xxxx |
|         3 | Development | Osaka        | NULL         |
|         4 | Marketing   | Fukuoka      | 092-222-xxxx |
|         5 | Education   | Tokyo        | NULL         |
|         6 | Plannning   | Yokohama     | 909-000-0000 |
|         7 | Planning    | Yokohama     | 055-555-5555 |
+-----------+-------------+--------------+--------------+
7 rows in set (0.00 sec)
```

これらに対してカーソル移動メソッドを試してみる。

```java
import java.sql.*;

public class JDBCCursorMoveSample {
    public static void main(String[] args) {
        // 結果をわかりやすくするために昇順ソートする。
        String sql = "SELECT dept_code, dept_name FROM department ORDER BY dept_code";

        try (Connection con = DbConnector.getConnect();
             Statement stmt = con.createStatement(
                     ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE);
             ResultSet rs = stmt.executeQuery(sql)) {

            // 最終行にカーソル移動
            rs.absolute(-1);
            System.out.format("cursor: %d, dept_code: %d, dept_name: %s\n",
                    rs.getRow(), rs.getInt(1), rs.getString(2));

            // 先頭にカーソル移動
            rs.absolute(1);
            System.out.format("cursor: %d, dept_code: %d, dept_name: %s\n",
                    rs.getRow(), rs.getInt(1), rs.getString(2));

            // 一番最後の行にカーソル移動
            rs.last();
            System.out.format("cursor: %d, dept_code: %d, dept_name: %s\n",
                    rs.getRow(), rs.getInt(1), rs.getString(2));

            // 最終行の次の行にカーソル移動
            rs.afterLast();
            System.out.format("cursor: %d\n", rs.getRow());

            // 先頭にカーソル移動
            rs.first();
            System.out.format("dept_code: %d, dept_name: %s\n",
                    rs.getInt(1), rs.getString(2));

            // 先頭の前の行にカーソル移動
            rs.beforeFirst();
            System.out.format("cursor: %d\n", rs.getRow());

            // 最終行の次の行に移動してから逆方向にスクロールしてみる
            rs.afterLast();
            System.out.println("逆スクロールで結果を出力----");
            while (rs.previous()) { // 逆方向スクロール
                System.out.format("dept_code: %d, dept_name: %s\n",
                        rs.getInt(1), rs.getString(2));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
```



## Scala

Scalaには`try-with-resources`のようなものがないので、
自前で実装する。`using`メソッドを定義して利用する。

まずはusingメソッドを実装したシングルトンオブジェクトを用意することにする。
関数`f`がリソースを使って何かしらの処理を実行し、
処理が完了したら`close()`メソッドを実行するようになっている。

```scala
object LoanPattern {

  /**
    * using メソッド
    * 処理が終わったら閉じる処理
    * Javaのtry-catch-resourceの代替方法
    *
    * @param resource finallyでcloseを呼ぶメソッド
    * @param f        引数resourceを使用して実行する処理
    * @tparam A closeメソッドを持っている型
    * @tparam B 関数fの戻り値
    */
  def using[A <: {def close() : Unit}, B](resource: A)(f: A => B): B = {
    try {
      f(resource) // 処理実行
    } finally {
      if (resource != null) resource.close()
    }
  }
```

ケースクラスで取得した値を保持する。

```scala
case class UserAccount(id: Long, firstName: String, lastName: String)
```

DAOのトレイト。RDBやKVS向けにリポジトリを変更したい場合はこのトレイトを継承する。

```scala
trait UserDao {
  // 全ユーザを取得する
  def getUsers(): Seq[UserAccount]

  // idでユーザを取得する
  def getById(id: Long): Option[UserAccount]
}
```

実装クラス。今回はMySQLです。

```scala
/**
  * UserDaoの実装クラス
  * MySQLに接続する。
  */
class UserDaoOnMySQL extends UserDao {

  import java.sql._
  import scala.collection.mutable.ArrayBuffer
  import LoanPattern.using

  override def getUsers(): Seq[UserAccount] = {
    using(getConnection()) { dbResource =>

      val stmt = dbResource.createStatement()
      val rs = stmt.executeQuery("select * from customers")

      val arrayBuffer = ArrayBuffer[UserAccount]()

      while (rs.next()) {
        arrayBuffer += UserAccount(
          rs.getInt("id"),
          rs.getString("first_name"),
          rs.getString("last_name"))
      }

      arrayBuffer.toList
    }
  }

  override def getById(id: Long): Option[UserAccount] = {
    using(getConnection()) { dbResource =>
      val stmt = dbResource.createStatement()
      val rs = stmt.executeQuery(s"select * from customers where id = ${id}")

      val arrayBuffer = ArrayBuffer[UserAccount]()

      while (rs.next()) {
        arrayBuffer += UserAccount(
          rs.getInt("id"),
          rs.getString("first_name"),
          rs.getString("last_name"))
      }
      arrayBuffer.find(_.id == id)
    }
  }

  private def getConnection() = 
    DriverManager.getConnection("jdbc:mysql://localhost/db", "username", "password")
}
```

使う側。

```scala
object SampleLoanPatternApp extends App {
  val dao = new UserDaoOnMySQL
  println(dao.getUsers())
  println(dao.getById(1))
}
```