AndroidのSQLiteOpenHelperを軽く触ったのでメモ

コンストラクタ

java

javaではコンストラクタでsuperを呼ぶ
super(context, DATABASE_NAME, null, DATABASE_VERSION);

kotlin

superの呼び方がjavaとは違うため書き方に気をつける必要がある
下記のように記載する
class DatabaseHelper(context : Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION)

注意

コンストラクタを呼び出す際にContextがnullの場合はNullPointerExceptionが発生します。

ユニットテスト

Mockitoを使用してユニットテストを行う場合、Contextに注意をする。
RuntimeEnvironment.application
をDatabaseHelperのコンストラクタへ渡すと、
ユニットテスト内でDBへのアクセス、書き込み、読み込みが可能となります。

sample

    @Mock
    private DatabaseHelper mDatabaseHelper;
    private Context mContext;

    @Before
    public void setUp() {
        MockitoAnnotations.initMocks(this);
        mContext = RuntimeEnvironment.application;
        mDatabaseHelper = spy(new DatabaseHelper(mContext));
    }

サンプルコード

sample

    static class DatabaseHelper extends SQLiteOpenHelper {
        private static final String DATABASE_NAME = "Test.db";
        private static final int DATABASE_VERSION = 1;
        private static final String TABLE_NAME = "tablename";
        private static final String COLUMN_KEY = "name";

        public DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        private void createTable(SQLiteDatabase db) {
            db.execSQL("CREATE TABLE " + TABLE_NAME + " ("
                    + COLUMN_KEY + " TEXT NOT NULL);");
        }

        /**
         * This call needs to be made while the mCacheLock is held. The way to
         * ensure this is to get the lock any time a method is called ont the DatabaseHelper
         * @param db The database.
         */
        @Override
        public void onCreate(SQLiteDatabase db) {
            createTable(db);
        }

        @Override
        public void onOpen(SQLiteDatabase db) {
        }

        @Override
        public void onConfigure(SQLiteDatabase db) {
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int currentVersion) {
        }

        @Override
        public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        }
    }

MainActivity.kt

/*
 * Copyright (C) 2018 Sacred Sanctuary Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package jp.sacredsanctuary.databasesample

import android.content.ContentValues
import android.content.Context
import android.database.DatabaseUtils
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteException
import android.database.sqlite.SQLiteOpenHelper
import android.support.v7.app.AppCompatActivity
import android.os.Bundle

class MainActivity : AppCompatActivity() {
    companion object {
        const val DATABASE_NAME = "Test.db"
        const val DATABASE_VERSION = 1
        const val TABLE_NAME = "tablename"
        const val COLUMN_KEY = "name"
        const val QUERY = "SELECT * FROM " + COLUMN_KEY + " limit 1"
    }

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
    }

    override fun onResume() {
        super.onResume()
        try {
            val db = DatabaseHelper(this).writableDatabase
            if (null != db) {
                try {
                    val values = ContentValues()
                    values.put(COLUMN_KEY, "TEST")
                    db.delete(TABLE_NAME, null, null)
                    db.insert(TABLE_NAME, null, values)
                } catch (e: SQLiteException) {
                    // DBへの書き込み失敗などで発生
                } finally {
                    db.close()
                }
            }
        } catch (e: SQLiteException) {
            // DBが開けない場合などに発生
        } catch (e: NullPointerException) {
            // Contextがnullの場合SQLiteOpenHelperのコンストラクタで発生する
        }
    }

    override fun onPause() {
        super.onPause()
        try {
            val db = DatabaseHelper(this).readableDatabase
            try {
                val name = DatabaseUtils.stringForQuery(db, QUERY, null)
            } catch (e: SQLiteException) {
                // クエリ文が不正な場合などに発生
            } finally {
                db.close()
            }
        } catch (e: SQLiteException) {
            // DBが開けない場合などに発生
        } catch (e: NullPointerException) {
            // Contextがnullの場合SQLiteOpenHelperのコンストラクタで発生する
        }
    }

    class DatabaseHelper(context : Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
        fun createTable(db: SQLiteDatabase?) {
            db?.execSQL("CREATE TABLE " + TABLE_NAME + " ("
                    + COLUMN_KEY + " TEXT NOT NULL);")
        }

        /**
         * This call needs to be made while the mCacheLock is held. The way to
         * ensure this is to get the lock any time a method is called ont the DatabaseHelper
         * @param db The database.
         */
        override fun onCreate(db: SQLiteDatabase?) {
            createTable(db)
        }

        override fun onOpen(db: SQLiteDatabase) {
        }

        override fun onConfigure(db: SQLiteDatabase) {
        }

        override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        }
    }
}