[Kotlin] SQLite

UserDBStructure.kt

package com.jwsoft.kotlinproject

import android.provider.BaseColumns

object UserDBStructure : BaseColumns {

    const val TABLE_NAME = "user"
    const val COLUMN_NAME = "name"
    const val COLUMN_AGE = "age"
    const val COLUMN_PHONE = "phone"
    const val COLUMN_GENDER = "gender"
    const val CREATE_USER_TABLE = "CREATE TABLE IF NOT EXISTS $TABLE_NAME (" +
            "${BaseColumns._ID} INTEGER PRIMARY KEY NOT NULL, " +
            "$COLUMN_NAME TEXT NOT NULL, " +
            "$COLUMN_PHONE TEXT NOT NULL, " +
            "$COLUMN_AGE TEXT NOT NULL, " +
            "$COLUMN_GENDER TEXT NOT NULL)"
    const val DROP_USER_TABLE = "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) {

    var dbHelper: DBHelper = DBHelper(context, fileName)

    fun onDestroy() {
        dbHelper.close()
    }

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

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

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

    fun delete(whereClause: String?,
               whereArgs: Array<String>?): Int {

        var db = dbHelper.readableDatabase
        return db.delete(UserDBStructure.TABLE_NAME, whereClause, whereArgs)
    }

    fun update(values: ContentValues,
               whereClause: String,
               whereArgs: Array<String>): Int {

        var db = dbHelper.readableDatabase
        return db.update(UserDBStructure.TABLE_NAME, values, whereClause, whereArgs)
    }


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

        companion object {
            const val DATABASE_VERSION = 1
        }

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

        override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
            db?.execSQL(UserDBStructure.DROP_USER_TABLE)
            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.provider.BaseColumns
import android.util.Log

class MainActivity : AppCompatActivity() {

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

        val userDBManager = UserDBManager(applicationContext, "MyDB.db")

        var contentValues = ContentValues().apply {
            put(UserDBStructure.COLUMN_NAME, "James Kim")
            put(UserDBStructure.COLUMN_PHONE, "010-1234-5678")
            put(UserDBStructure.COLUMN_AGE, "30")
            put(UserDBStructure.COLUMN_GENDER, "male")
        }
        Log.e("insert : ", userDBManager.insert(contentValues).toString())


        val columns = arrayOf(
            BaseColumns._ID,
            UserDBStructure.COLUMN_NAME,
            UserDBStructure.COLUMN_PHONE,
            UserDBStructure.COLUMN_AGE,
            UserDBStructure.COLUMN_GENDER
        )
        val selection = "${UserDBStructure.COLUMN_AGE} = ?"
        val selectionArgs = arrayOf("30")
        val orderBy = "${BaseColumns._ID} ASC"
        val cursor = userDBManager.query(columns, selection, selectionArgs, orderBy)

        while (cursor.moveToNext()) {
            val id = cursor.getInt(cursor.getColumnIndex(BaseColumns._ID))
            val name = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_NAME))
            val phone = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_PHONE))
            val age = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_AGE))
            val gender = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_GENDER))
            Log.e("query : ", "$id $name $phone $age $gender")
        }


        var whereClause = "${UserDBStructure.COLUMN_NAME} = ? AND ${BaseColumns._ID} = 5"
        var whereArgs = arrayOf("James Kim")
        val resultDelete = userDBManager.delete(whereClause, whereArgs)
        Log.e("delete : ", resultDelete.toString())


        contentValues = ContentValues().apply {
            put(UserDBStructure.COLUMN_NAME, "Jason Park")
        }
        val whereClauseUpdate = "${BaseColumns._ID} = ? OR ${BaseColumns._ID} = ?"
        val whereArgsUpdate = arrayOf("2", "3")
        val resultUpdate = userDBManager.update(contentValues, whereClauseUpdate, whereArgsUpdate)
        Log.e("update : ", resultUpdate.toString())

    }
    
    override fun onDestroy() {
        userDBManager.onDestroy()
        super.onDestroy()
    }
    
}

첫 번째 실행 결과

 

두 번째 실행 결과

 

세 번째 실행 결과

 

네 번째 실행 결과

 

다섯 번째 실행 결과

 

여섯 번째 실행 결과

 

 

 

 

 

 

MainActivity.kt

package com.jwsoft.kotlinproject

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

class MainActivity : AppCompatActivity() {

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

        val userDBManager = UserDBManager(applicationContext, "MyDB.db")

        var contentValues = ContentValues().apply {
            put(UserDBStructure.COLUMN_NAME, "James Kim")
            put(UserDBStructure.COLUMN_PHONE, "010-1234-5678")
            put(UserDBStructure.COLUMN_AGE, "30")
            put(UserDBStructure.COLUMN_GENDER, "male")
        }
        Log.e("insert : ", userDBManager.insert(contentValues).toString())


        val columns = arrayOf(
            BaseColumns._ID,
            UserDBStructure.COLUMN_NAME,
            UserDBStructure.COLUMN_PHONE,
            UserDBStructure.COLUMN_AGE,
            UserDBStructure.COLUMN_GENDER
        )
        val selection = "${UserDBStructure.COLUMN_AGE} = ?"
        val selectionArgs = arrayOf("30")
        val orderBy = "${BaseColumns._ID} ASC"
        val cursor = userDBManager.query(columns, selection, selectionArgs, orderBy)

        while (cursor.moveToNext()) {
            val id = cursor.getInt(cursor.getColumnIndex(BaseColumns._ID))
            val name = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_NAME))
            val phone = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_PHONE))
            val age = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_AGE))
            val gender = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_GENDER))
            Log.e("query : ", "$id $name $phone $age $gender")
        }


        var whereClause = "${UserDBStructure.COLUMN_NAME} = ? AND ${BaseColumns._ID} = 5"
        var whereArgs = arrayOf("James Kim")
        val resultDelete = userDBManager.delete(null, null)
        Log.e("delete : ", resultDelete.toString())


        contentValues = ContentValues().apply {
            put(UserDBStructure.COLUMN_NAME, "Jason Park")
        }
        val whereClauseUpdate = "${BaseColumns._ID} = ? OR ${BaseColumns._ID} = ?"
        val whereArgsUpdate = arrayOf("2", "3")
        val resultUpdate = userDBManager.update(contentValues, whereClauseUpdate, whereArgsUpdate)
        Log.e("update : ", resultUpdate.toString())
    }
    
    override fun onDestroy() {
        userDBManager.onDestroy()
        super.onDestroy()
    }
}

val resultDelete = userDBManager.delete(whereClause, whereArgs) 에서

val resultDelete = userDBManager.delete(null, null) 로 수정

 

수정 후 첫 번째 실행 결과

 

수정 후 두 번째 실행 결과