Android 로컬 SQLite 이용하기

Android 기본 SDK 에 이미 SQLite 가 포함되어 있기 때문에, 바로 사용이 가능하다!

( 별도 디펜던시 필요가 없다! )

 

package kr.samdogs.simpledatabaseapplication

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

// SQLiteOpenHelper 를 상속 받는다. 사용할 db 명 지정
class SimpleDataSource(context: Context) : SQLiteOpenHelper(context, "contact.db", null, 1) {
    // 첫 실행시 테이블 생성 ( 생성되어 있으면 생성안하는듯? )
    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(
            "CREATE TABLE contact (" +
                    "id CHAR(8) PRIMARY KEY, " +
                    "phone_number VARCHAR(64) NOT NULL, " +
                    "name VARCHAR(64) NOT NULL, "+
                    "email VARCHAR(64) "+
            ")"
        )
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        TODO("Not yet implemented")
    }

    //데이터베이스를 사용할떄 읽기인지 쓰기인지 선언해서 사용!
    fun getFriends(): List<Friend> {
        val db = this.readableDatabase
        val projection = arrayOf("id", "phone_number", "name", "email")
        val sortOrder = "id DESC"
        val cursor = db.query("contact", projection, null, null, null, null, sortOrder)
        val friends = mutableListOf<Friend>()
        with(cursor) {
            while(moveToNext()) {
                val friend = Friend(
                    id = getString(getColumnIndexOrThrow("id")),
                    phoneNumber = getString(getColumnIndexOrThrow("phone_number")),
                    name = getString(getColumnIndexOrThrow("name")),
                    email = getString(getColumnIndexOrThrow("email"))
                )
                friends.add(friend)
            }
        }
        cursor.close()

        return friends
    }

    fun addFriend(friend: Friend) {
        val db = this.writableDatabase
        val values = ContentValues().apply {
            put("id", friend.id)
            put("phone_number", friend.phoneNumber)
            put("name", friend.name)
            put("email", friend.email)
        }
        db.insert("contact", null, values)
    }

    fun removeFriend(friendId: String) {
        val db = this.writableDatabase
        val selection= "id = ?"
        val selectionArgs = arrayOf(friendId)
        db.delete("contact", selection, selectionArgs)
    }
}

 

샘플에서 사용한 VO 클래스

package kr.samdogs.simpledatabaseapplication

data class Friend(val id: String, val phoneNumber: String, val name: String, val email: String?)