【備忘録】SQLite関連

#はじめに
アプリの内部にデータを保存する場合、通常SQLite3を使用すると思われる。
筆者がSQLite3に感じているメリットはオフラインでも使用できる点だ。
通常のアプリならば、Webに繋がっている前提で話ができるため、
内部的にデータを保存する必要なさそうだ。
それならばWebアプリでいい。
しかし当方のようなネットワーク環境を自由に広げられない場所では
内部データで駆動できるのはとてもありがたい。

#SQLiteOpenHelper
SQLiteを操作するにはSQLiteOpenHelperを使用するといいらしい。
アプリ内の各アクティビティで使用することを考えると、外だししてしまった方が賢いような気がするので、こんな感じに書いてみた。

SqlConnectionHelper.java

package sample.pack;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import org.json.JSONArray;
import org.json.JSONObject;

public class SqlConnectionHelper extends SQLiteOpenHelper {
    //コンストラクタ
    SqlConnectionHelper(Context context, String DB_Name, int DB_Version) {
        super(context,DB_Name,null,DB_Version);
    }

    //extendに必要
    @Override
    public void onCreate(SQLiteDatabase db) {
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }

    //insert,update,delete用メソッド
    public void writeSql(String sql) throws RuntimeException {
        SQLiteDatabase db = getReadableDatabase();
        try {
            db.beginTransaction();
            db.execSQL(sql);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
            db.close();
        }
    }

    //select用メソッド(JSONArrayをreturn)
    public JSONArray readSql(String sql) throws RuntimeException {
        SQLiteDatabase db = getReadableDatabase();
        JSONArray jsonArr = new JSONArray();
        try {
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                JSONObject jsonObj = new JSONObject();
                for (String collumn : cursor.getColumnNames()) {
                    jsonObj.put(collumn, cursor.getString(cursor.getColumnIndex(collumn)));
                }
                jsonArr.put(jsonObj);
            }
            cursor.close();
        } finally {
            db.close();
            return jsonArr;
        }
    }
}

dml文をアクティビティ側で生成して渡し、結果はJSONArray形式で受けとる仕様。

部分ごとに見ていく

    //コンストラクタ
    SqlConnectionHelper(Context context, String DB_Name, int DB_Version) {
        super(context,DB_Name,null,DB_Version);
    }

個々のDB_NameとDB_Versionはこちらの記事で設定した値を引っ張ってくれば簡単。
Contextは呼び出し元のアクティビティからgetApplicationContext()あたりを使って渡す。

    //extendに必要
    @Override
    public void onCreate(SQLiteDatabase db) {
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }

この部分はSQLiteOpenHelperをextendsする都合上書かないとエラーになる。
DBが作成・更新されたときに呼ばれるようだが、活用方法がいまいちわかっていないので、今回はスルー。

    //insert,update,delete用メソッド
    public void writeSql(String sql) throws RuntimeException {
        SQLiteDatabase db = getReadableDatabase();
        try {
            db.beginTransaction();
            db.execSQL(sql);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
            db.close();
        }
    }

    //select用メソッド(JSONArrayをreturn)
    public JSONArray readSql(String sql) throws RuntimeException {
        SQLiteDatabase db = getReadableDatabase();
        JSONArray jsonArr = new JSONArray();
        try {
            Cursor cursor = db.rawQuery(sql, null);  //ここで問い合わせている
            while (cursor.moveToNext()) {
                JSONObject jsonObj = new JSONObject();
                for (String collumn : cursor.getColumnNames()) {
                    jsonObj.put(collumn, cursor.getString(cursor.getColumnIndex(collumn)));
                }
                jsonArr.put(jsonObj);
            }
            cursor.close();
        } finally {
            db.close();
            return jsonArr;
        }
    }

結果を受け取らないものをまとめてwrite、受け取るものをreadにまとめてしまった。
1個にまとめてしまおうかとも思ったが…
Write側ではTransactionを使用している。
エラーが発生してsetTransactionSuccessful()が呼ばれない場合は、endTransaction()時にロールバックする。
readの方ではJSONArrayで結果を返すために、
問い合わせ結果が入ったCursorをぶん回してCursor→JSONObject→JSONArrayという風にしている。