【Kotlin研修7日目】SQLiteデータベースを用いたデータ管理

# Androidにおけるデータ保存
`端末内`にデータを保存する方法は以下の4通り。

|方法|内容|
|:--|:--|
|SQLiteデータベース|`Android OS`標準の`RDB`|
|プレファレンス|`キー`と`値`の簡易データ<br>→アプリの`設定値`の保存|
|内部ストレージ|`端末内部`の`ストレージ`|
|外部ストレージ|`カードスロット`に挿入する`ストレージ`|

# SQLiteデータベースの利用
[参考: SQLite](https://developer.android.com/training/data-storage/sqlite?hl=ja)
[参考: SQLite入門(SQL文)](https://www.dbonline.jp/sqlite/#section_insert)
`SQLiteデータベース`を利用する手順は、以下の通り。

> 1. `SQLiteOpenHelper`クラスを継承する`DatabaseHelper`(=`DBヘルパーオブジェクト`)クラスを作成し、<br>`SQLiteDatabase`オブジェクト(=`DB接続オブジェクト`)を生成
> 2. `アクティビティ`で、`DBヘルパーオブジェクト`から`DB接続オブジェクト`を取得
> 3. `アクティビティ`で、`DB接続オブジェクト`を用いて`SQL文`を実行
> 4. `アクティビティ`終了時に`DBヘルパーオブジェクト`を解放

## データベースヘルパー(DatabaseHelper)クラスの作成
`app/java/<パッケージ名>`フォルダに`Kotlin File`を新規追加し、
`SQLiteOpenHelper`クラスを継承する`DatabaseHelper`クラスを作成する。

### SQLiteOpenHelperクラス
`サブクラス`を作成することで、保持している`DB接続オブジェクト`を通じて`SQLiteデータベース`の`作成`と`管理`を可能にするクラス。
`SQLiteOpenHelper`のサブクラスオブジェクトは`DBヘルパーオブジェクト`と呼ばれ、`DB接続オブジェクト`を所有する。

#### SQLiteDatabaseクラス
`アクティビティ`と`SQLiteデータベース`の橋渡しを行うクラス。
`アクティビティ`で記述した`SQL文`を用いて`SQLiteデータベース`にアクセスし、
実行結果を`Cursor`オブジェクトに格納する`DB接続オブジェクト`。

#### Cursorクラス
`SQL文(SELECT文)`の実行結果を`表`形式で格納する`カーソル`クラス。
`Cursor`クラスのメソッドを用いて`データ`を取得する。

### 定義

```kotlin:DatabaseHelper.kt
class DatabaseHelper(context: Context): SQLiteOpenHelper(
    context: Context?, 
    name: String?, 
    factory: SQLiteDatabase.CursorFactory?. 
    version: Int
) {
    ...
    // 端末内部のSQLiteデータベースのバージョンが古い場合に実行される処理
    // -> 抽象メソッドのため、処理が不要でも記述する必要がある
    override fun onUpgrade(
        db: SQLiteDatabase?, 
        oldVersion: Int, 
        newVersion: Int
    ) {}
}
// パラメータ(SQLiteOpenHelperコンストラクタ)
// context: DatabaseHelperクラスを利用するアクティビティオブジェクト(コンテキスト)
// -> DatabaseHelperクラス側では用意できないため、クラスコンストラクタの引数を利用
// name: DB名
// factory: 手動的に作成するCursorFactoryオブジェクト(通常: null)
// version: DBのバージョン番号(作成時は1で定義)

// パラメータ(onUpgrade()メソッド)
// db: DB接続オブジェクト(後述)
// oldVersion: 内部DBのバージョン番号
// newVersion: コンストラクタで設定したバージョン番号
```

## DB接続オブジェクトの生成
定義した`DBヘルパーオブジェクト`の`初期化`時(=`onCreate()`)に、
`DDL文`を用いて`SQLiteデータベース`を生成する。

### DDL(Data Definition Language)
`SQLiteデータベース`の`生成`・`削除`を行う`SQL文`。

#### CREATE文によるDB生成

```sql:CREATE文
CREATE TABLE <テーブル名> (
    _id INTEGER PRIMARY KEY,  -- 主キーとなるID(INTEGER型)
    /* 主キーによってレコード(=行)が特定できる */
    <カラム名1> TEXT,  -- カラム①(TEXT型)
    <カラム名2> BOOLEAN,  -- カラム②(BOOLEAN型)
    <カラム名3> DOUBLE,  -- カラム③(DOUBLE型)
    <カラム名4> DATE,  -- カラム④(DATE型)
    <カラム名5> BLOB  -- カラム⑤(BLOB型)
);
```

### 定義

```kotlin
SQLiteDatabase?.execSQL(sql: String!): Unit
// パラメータ
// sql: DDL文
```

### サンプルコード

```kotlin:DatabaseHelper.kt
// SQLiteデータベースを生成するクラス
// -> SQLiteOpenHelperのコンテキストを代入するため、
//    クラスコンストラクタにContextを必要とする
class DatabaseHelper(context: Context): SQLiteOpenHelper(
    context,
    DATABASE_NAME,
    null,
    DATABASE_VERSION
) {
    // private定数の定義
    companion object {
        // DB名
        private const val DATABASE_NAME = "itemmemo.db"
        // 生成時のDBバージョン番号
        private const val DATABASE_VERSION = 1
    }

    // 初期化時に実行される処理
    // -> DatabaseHelperクラスの初期化と同時に、
    //    保持しているSQLiteDatabaseオブジェクトを通じてSQLiteデータベースを生成
    override fun onCreate(db: SQLiteDatabase?) {
        // DDL文
        // -> 改行によって可読性を高めるため、append()ができるStringBuilderを用いる
        // StringBuilder: 文字列を格納する(同一ポインタ内での)可変配列を定義するクラス
        val sb = StringBuilder()

        // DDL文の定義
        sb.append("<DBを作成するDDL文>")
        ...   // DDL文に応じてappend()の繰り返し

        // DDL文をString型に変換
        val sql = sb.toString()

        // SQLiteデータベースの生成
        db?.execSQL(sql)
    }
    ...
}
```

## SQLiteDatabaseオブジェクトの取得
`DBヘルパーオブジェクト`が所有する`DB接続オブジェクト`には、`プロパティ`を通じてアクセスすることができる。

### 定義

```kotlin
// 読み書き可能なSQLiteDatabaseオブジェクト
val db = SQLiteOpenHelper.writableDatabase

// 読み込み専用のSQLiteDatabaseオブジェクト
// -> データベースにデータが書き込めない場合などに用いる
val db = SQLiteOpenHelper.readableDatabase
```

### サンプルコード

```kotlin:MainActivity.kt
class MainActivity : AppCompatActivity() {
    // DatabaseHelperオブジェクトの生成
    // -> クラスコンストラクタを定義しているため、
    //    DatabaseHelperクラスを利用するコンテキストを指定
    // <- 様々な処理で使用するため、処理直前ではなく事前に生成
    private val _helper = DatabaseHelper(this@MainActivity)

    // SQLiteDatabaseオブジェクト(DB接続オブジェクト)の指定
    // SQLiteOpenHelper.writableDatabase: 読み書き可能なSQLiteDatabaseオブジェクト
    val db = _helper.writableDatabase
    ...
}
```

## DBヘルパーオブジェクトの解放
`SQLiteデータベース`を利用する`アクティビティ`の終了(=`onDestroy()`)時、
`close()`メソッドを用いて`DBヘルパーオブジェクト`を解放する必要がある。

### 定義

```kotlin
SQLiteOpenHelper.close()
```

### サンプルコード

```kotlin:MainActivity.kt
class MainActivity : AppCompatActivity() {
    // DatabaseHelperオブジェクトの生成
    private val _helper = DatabaseHelper(this@MainActivity)

    // アクティビティ終了時に実行する処理
    override fun onDestroy() {

        // DatabaseHelperオブジェクトの解放
        // SQLiteOpenHelper.close(): Databaseオブジェクトの解放
        _helper.close()

        // アクティビティの終了
        super.onDestroy()
    }
}
```

***

# SQL文によるデータベースへのアクセス
`SQL文`を用いてデータベースにアクセスする手順は、`データ更新処理`と`データ取得処理`によって異なる。

## データ更新処理
`データ更新処理`を行う手順は、以下の通り。

> 1. `DML文`を`SQLiteDatabase`クラスのメソッドを用いて`コンパイル`を実行
> 2. 1.で`バインド変数`(後述)を用いた場合は値を`バインド`
> 3. `コンパイル`した`DML文`を実行

### DML文のコンパイル
`コンパイル`された`DML文`は、`ステートメントオブジェクト`に変換される。
`ステートメントオブジェクト`は`SQLiteStatement`クラスで定義される。

### DML(Data Manipulation Language)
`テーブル`に対して、データの`取得`・`追加`・`更新`・`削除`を行う`SQL文`。

#### SQLiteStatementクラス
`ステートメントオブジェクト`を定義するクラス。
`SQLiteStatement`クラスのメソッドを用いて、定義した`DML文`を実行することができる。

#### 定義

```kotlin
SQLiteDatabase.compileStatement(sql: String!): SQLiteStatement!
// sql: SQL文
```

#### サンプルコード

```kotlin:MainActivity.kt
class MainActivity : AppCompatActivity() {
    // DatabaseHelperオブジェクトの生成
    private val _helper = DatabaseHelper(this@MainActivity)

    // ボタンの"タップ"イベント検知時の処理(イベントハンドラ)
    fun onSaveButtonClick(view: View) {
        // SQLiteDatabaseオブジェクト(DB接続オブジェクト)の指定
        val db = _helper.writableDatabase

        // 更新前データを削除するSQL文
        // ?: 変数が後から挿入される箇所(=バインド変数)
        val sqlDelete = "DELETE FROM itemmemos WHERE _id = ?"

        // 更新前データを削除するSQL文のコンパイル
        var stmt = db.compileStatement(sqlDelete)

        ...
    }
}
```

### バインド変数への値のバインド
`バインド変数`(=`?`)を用いた場合は、`SQLiteStatement`クラスが継承している、
`SQLiteProgram`クラスの`bind<T>()`メソッドを用いて`バインド変数`に値を`バインド`する。
※`<T>`は`型`の`プレースホルダ`

#### バインド変数
後から`SQL文`中に値を`バインド`できる変数で、`?`によって表される。

#### SQLiteProgram
`コンパイル`された`SQL文`を実行する`SQLiteStatement`クラスの基本クラス。

#### 定義

```kotlin
SQLiteProgram.bind<T>(index: Int, value: <T>): Unit
// パラメータ
// index: SQL文の"?"のインデックス番号
// -> 1番目は"1", 2番目は"2", ..., n番目は"n"
// value: SQL文の"?"にバインドする値
```

#### サンプルコード

```kotlin:MainActivity.kt
// 更新前データを削除するSQL文
// ?: 変数が後から挿入される箇所(=バインド変数)
val sqlDelete = "DELETE FROM itemmemos WHERE _id = ?"

// 更新前データを削除するSQL文のコンパイル
var stmt = db.compileStatement(sqlDelete)

// バインド変数への値のバインド
stmt.bindLong(1, _itemId.toLong())

// -------------------------------

// 更新後データを挿入するSQL文
// ?: 変数が後から挿入される箇所(=バインド変数)
val sqlInsert = "INSERT INTO itemmemos (_id, item, note) VALUES (?, ?, ?)"

// 更新後データを挿入するSQL文のコンパイル
stmt = db.compileStatement(sqlInsert)

// バインド変数への値のバインド
stmt.bindLong(1, _itemId.toLong())
stmt.bindString(2, _item)
stmt.bindString(3, note)
```

### SQL文(DML文)の実行
`コンパイル`された`SQL文`(=`ステートメントオブジェクト`)は、
`更新・削除`を行う`executeUpdateDelete()`メソッドと、
`追加(=挿入)`を行う`executeInsert()`メソッドを用いて実行する。

#### DELETE, INSERT, UPDATE文によるテーブルの更新

```sql
-- データの削除
DELETE FROM <テーブル名> WHERE <条件式>;

-- データの挿入(追加)
/* 全てのカラムにデータを追加する場合 */
INSERT INTO <テーブル名> VALUES (<追加するデータ>)
/* 特定のカラムにデータを追加する場合 */
INSERT INTO <テーブル名> (<カラム名>) VALUES (<追加するデータ>);

-- データの更新
UPDATE <テーブル名> SET <カラム名>=<更新後のデータ> WHERE <条件式>;
```

#### 定義

```kotlin
// データの更新・削除
// -> 返り値はSQL文の実行によって変更された行の数
SQLiteStatement.executeUpdateDelete(): Int

// データの追加(挿入)
// -> 返り値はSQL文の実行によって変更された行の主キー
SQLiteStatement.executeInsert(): Long
```

#### サンプルコード

```kotlin:MainActivity.kt
// データの(更新・)削除

// 更新前データを削除するSQL文(DML文)
val sqlDelete = "DELETE FROM itemmemos WHERE _id = ?"

// 更新前データを削除するSQL文のコンパイル
var stmt = db.compileStatement(sqlDelete)

...  // 値のバインド処理

// (更新・削除を行う)DML文の実行
stmt.executeUpdateDelete()

// -------------------------------

// データの追加(挿入)

// 更新後データを挿入するSQL文(DML文)
val sqlInsert = "INSERT INTO itemmemos (_id, item, note) VALUES (?, ?, ?)"

// 更新後データを挿入するSQL文のコンパイル
stmt = db.compileStatement(sqlInsert)

...  // 値のバインド処理

// (挿入を行う)SQL文の実行
stmt.executeInsert()
```

## データ取得処理
`データ取得処理`を行う手順は、以下の通り。

> 1. `SQLiteDatabase`クラスのメソッドを用いて`DML文`を実行し、<br>実行結果を`Cursor`オブジェクトに格納<br>※`コンパイル`は**不要**<br>※バインド変数を用いる場合は、実行時にバインド処理を行う
> 2. `カーソル`を`ループ処理`で`走査`しながら、各レコードのデータを取得

### SQL文(DML文)の実行、Cursorオブジェクトへの格納

#### SELECT文によるデータの取得

```sql
SELECT <カラム名> FROM <テーブル名> WHERE <条件式>;
```

#### 定義

```kotlin
SQLiteDatabase.rawQuery(
    sql: String!, 
    selectionArgs: Array<String!>!
): Cursor!
// パラメータ
// sql: 実行するSQL文
// selectionArgs: バインド変数用のString型配列
// -> バインド変数を利用しない場合はnull
// 返り値はSQL文の実行結果表を格納するCursorオブジェクト
```

#### サンプルコード

```kotlin:MainActivity.kt
// SQLiteDatabaseオブジェクト(DB接続オブジェクト)の指定
val db = _helper.writableDatabase

// データを取得するSQL文
val sql = "SELECT * FROM itemmemos WHERE _id = ${_itemId}"

// SQL文(SELECT文)の実行結果表を格納するCursorオブジェクト
val cursor = db.rawQuery(sql, null)
```

### カーソルの走査によるデータの取得
`SQL文(SELECT文)`によって得られた実行結果は、
`表`形式で`Cursor`オブジェクト(=`カーソル`)に格納されているため、
`カーソル`を`レコード(=行)`単位で`走査`することで実行結果を参照する。

`走査`を行う中で、`カーソル`がもつ`テーブル`から、**指定した`カラム(=列)`以外を排除**しながら
条件に一致する`レコード`の`インデックス番号`を取得できる`getColumnIndex()`メソッドを用いて、
`カーソル`から取得するデータを抽出する。

#### 定義

```kotlin
// 指定したカラム(=列)名に一致するレコード(=行)の、Index番号を取得
// -> 該当レコードが存在しない場合は-1を返却
// <- 指定したカラム以外のカラムは排除される
Cursor.getColumnIndex(columnName: String!): Int
// パラメータ
// columnName: カラム名

// データの取得
Cursor.get<T>(columnIndex: Int): <T>
// パラメータ
// columnIndex: 取得する列のIndex番号
```

#### サンプルコード

```kotlin:MainActivity.kt
// 取得する文字列
// -> データが存在しない場合に備えて空文字で初期化
var note = ""

// SQL文(SELECT文)の実行結果表を格納するCursorオブジェクト
val cursor = db.rawQuery(sql, null)

// ループによるデータ取得
// Cursor.moveToNext(): 次の行に移動
// -> 次の行が存在する場合はtrue, 存在しない場合はfalseを返す
// <- 最初はテーブルの0行目に位置しているため、
//    while構文を用いて最初に1行目に移る処理を行う
while (cursor.moveToNext()) {

    // 列名を指定してIndex番号を取得
    // <- 指定したカラム以外のカラムは排除される
    val idxNote = cursor.getColumnIndex("note")

    // 文字列の取得
    note = cursor.getString(idxNote)
}
```

### バインド変数を用いたデータ取得

#### サンプルコード

```kotlin:MainActivity.kt
// 発展:

// データを取得するSQL文
val sql = "SELECT * FROM itemmemos WHERE _id = ?"

// バインドする値を格納した配列
val params = arrayOf(_itemId.toString())

// SQL文(SELECT文)の実行結果表を格納するCursorオブジェクト
val cursor = db.rawQuery(sql, params)
```

### SQL文を記述せずにデータを取得

#### 定義

```kotlin
SQLiteDatabase.query(
    table: String!, 
    columns: Array<String!>!, 
    selection: String!,
    selectionArgs: Array<String!>!, 
    groupBy: String!, 
    having: String!, 
    orderBy: String!
): Cursor!
// パラメータ
// table: データベース名
// columns: 取得する列名を格納したString型配列
// -> nullの場合は全ての列を取得
// selection: 取得する行の条件
// selectionArgs: バインド変数用のString型配列
// -> バインド変数を利用しない場合はnull
// groupBy: グループ化条件
// -> nullの場合はグループ化を行わない
// having: 抽出するグループ条件
// -> nullの場合はグループ化を行わない
// orderBy: ソート条件
// -> nullの場合はソートを行わない
```