[Kotlin] SQLite + Singleton Pattern

UserDBStructure.kt

package com.jwsoft.kotlinproject

import android.provider.BaseColumns

object UserDBStructure : BaseColumns {

    val TABLE_NAME = "user"
    val COLUMN_ID = BaseColumns._ID
    val COLUMN_NAME = "name"
    val COLUMN_PHONE = "phone"
    val COLUMN_AGE = "age"

    val CREATE_TABLE_USER = "CREATE TABLE IF NOT EXISTS $TABLE_NAME (" +
            "$COLUMN_ID INTEGER PRIMARY KEY NOT NULL, " +
            "$COLUMN_NAME TEXT NOT NULL, " +
            "$COLUMN_PHONE TEXT NOT NULL, " +
            "$COLUMN_AGE INTEGER NOT NULL)"

    val DROP_TABLE_USER = "DROP TABLE IF EXISTS $TABLE_NAME"

}

 

 

 

 

 

 

 

UserDBManager.kt

package com.jwsoft.kotlinproject

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

class UserDBManager(context: Context, fileName: String)
    : SQLiteOpenHelper(context, fileName, null, DATABASE_VERSION) {

    companion object {
        val DATABASE_VERSION = 1
        var INSTANCE: UserDBManager? = null

        fun getInstance(context: Context, fileName: String): UserDBManager {
            if (INSTANCE == null) {
                INSTANCE = UserDBManager(context, fileName)
            }
            return INSTANCE!!
        }
    }

    fun insert(values: ContentValues): Long {
        var db: SQLiteDatabase = writableDatabase
        return db.insert(UserDBStructure.TABLE_NAME, null, values)
    }

    fun query(columns: Array<String>,
              selection: String,
              selectionArgs: Array<String>,
              orderBy: String): Cursor {

        var db: SQLiteDatabase = readableDatabase
        return db.query(
            UserDBStructure.TABLE_NAME,
            columns,
            selection,
            selectionArgs,
            null,
            null,
            orderBy
        )
    }

    fun delete(whereClause: String, whereArgs: Array<String>): Int {
        var db: SQLiteDatabase = readableDatabase
        return db.delete(UserDBStructure.TABLE_NAME, whereClause, whereArgs)
    }

    fun update(values: ContentValues, whereClause: String, whereArgs: Array<String>): Int {
        var db: SQLiteDatabase = readableDatabase
        return db.update(UserDBStructure.TABLE_NAME, values, whereClause, whereArgs)
    }

    override fun onCreate(db: SQLiteDatabase?) {
        db?.execSQL(UserDBStructure.CREATE_TABLE_USER)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        db?.execSQL(UserDBStructure.DROP_TABLE_USER)
        onCreate(db)
    }

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

 

 

 

 

 

 

 

MainActivity.kt

package com.jwsoft.kotlinproject

import android.content.ContentValues
import androidx.appcompat.app.AppCompatActivity
import android.os.Bundle
import android.util.Log

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

        // insert
        var values = ContentValues()
        values.put("name", "James Kim")
        values.put("phone", "010-1234-1234")
        values.put("age", 30)
        var insertedID = UserDBManager.getInstance(applicationContext, "James.db")
            .insert(values)

        Log.e("insert : ", insertedID.toString())


        // query
        val columns = arrayOf(
            UserDBStructure.COLUMN_ID,
            UserDBStructure.COLUMN_NAME,
            UserDBStructure.COLUMN_PHONE,
            UserDBStructure.COLUMN_AGE
        )
        val selection = "${UserDBStructure.COLUMN_AGE} = ? OR ${UserDBStructure.COLUMN_NAME} = ?"
        val selectionArgs = arrayOf("27", "James Kim")
        val orderBy = "${UserDBStructure.COLUMN_ID} ASC"
        val cursor = UserDBManager.getInstance(applicationContext, "James.db")
            .query(columns, selection, selectionArgs, orderBy)

        while (cursor.moveToNext()) {
            val id = cursor.getInt(cursor.getColumnIndex(UserDBStructure.COLUMN_ID))
            val name = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_NAME))
            val phone = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_PHONE))
            val age = cursor.getInt(cursor.getColumnIndex(UserDBStructure.COLUMN_AGE))

            Log.e("query : ", "$id $name $phone $age")
        }


        // delete
        val where = "${UserDBStructure.COLUMN_ID} = ?"
        val whereArgs = arrayOf("5")
        val affectedRows = UserDBManager.getInstance(applicationContext, "James.db")
            .delete(where, whereArgs)

        Log.e("delete : ", affectedRows.toString())


        // update
        val updateValues = ContentValues()
        updateValues.put(UserDBStructure.COLUMN_NAME, "Sonata Lee")
        updateValues.put(UserDBStructure.COLUMN_AGE, 27)
        val updateWhere = "${UserDBStructure.COLUMN_ID} = ? OR ${UserDBStructure.COLUMN_ID} = ?"
        val updateWhereArgs = arrayOf("3", "4")
        val updatedRows = UserDBManager.getInstance(applicationContext, "James.db")
            .update(updateValues, updateWhere, updateWhereArgs)

        Log.e("update : ", updatedRows.toString())
    }

    override fun onDestroy() {
        UserDBManager.INSTANCE?.close()
        super.onDestroy()
    }
}

 

첫 번째 실행 결과

 

두 번째 실행 결과

 

세 번째 실행 결과

 

네 번째 실행 결과

 

다섯 번째 실행 결과

 

여섯 번째 실행 결과