[Android] SQLite, SQLiteOpenHelper, Local DB 이해

SQLite란?

  Android 개발을 하면서 앱을 사용하고 종료하더라도 데이터를 계속 저장되어야할 필요성이 있습니다. 간단하게 저장하는 것이라면 SharedPreference를 사용하는 방법도 있겠지만, key와 value의 값으로만 모든 데이터를 표현하기 힘들고, 많은 데이터를 체계적으로 관리하기는 어렵습니다. 따라서 데이터베이스를 사용해야 하고 안드로이드에서는 가벼운 관계형 데이터베이스인 SQLite가 네이티브 라이브러리에 포함되어 있습니다. SQLite의 주요 특징은 데이터 조회가 빠르고, 표준 SQL 을 지원하여 데이터베이스의 주요 기능인 C(Create), R(Read), U(Update), D(Delete)를 사용할 수 있습니다. 

 

 

  SQLite를 사용하여 간단한 Todo List를 작성하는 어플리케이션을 만들어보려고 합니다. 아키텍처와 디자인 패턴을 사용하지 않고 간단하게 동작만 할 수 있는 예제를 구현해봤습니다.

 

 

 

데이터베이스 만들기

  먼저 데이터베이스를 만들기 위한 API는 ContextWrapper 클래스에 있는 openOrCreateDatabase 함수입니다.

https://developer.android.com/reference/android/content/ContextWrapper#peekWallpaper()

 DB 파일 이름, 파일 모드, Cursor 객체를 만드는 Factory를 매개변수로 받아 SQLiteDatabase를 생성합니다. 실제로 해당 API를 사용하면 name으로 지정된 파일명의 db 파일이 생기고 SQLiteDatabas 객체를 참조하여 데이터베이스를 열거나 만들 수 있습니다. 

 

 

https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#execSQL(java.lang.String)

  SQLiteDatabase객체에서 중요한 메소드로 execSQL가 있습니다. execSQL은 데이터베이스를 만든 후에 표준 SQL를 사용할 수 있도록 합니다. 하지만 리턴 값이 void이기 때문에 SELECT 구문과 같은 반환 값이 존재하는 SQL를 사용할 수 없습니다.

 

 

 

테이블 생성/삭제

※ 스키마

  테이블 생성하기에 앞서 스키마 용어에 대해 알아야 합니다. 스키마는 간단하게는 테이블의 구조를 정의한 것이고 사전적 정의는 데이터베이스에서 자료의 구조, 자료의 표현 방법, 자료 간의 관계를 형식 언어로 정의한 구조라고 합니다. 데이터베이스 내에는 여러 테이블이 존재하고 각 테이블에는 데이터의 형식, 갯수 ,Primary key 가 모두 다르기 때문에 이를 정의한 것이 스키마라고 생각하시면 됩니다. 

 

 

  안드로이드 공식문서에서는 스키마를 잘 작성하기 위한 방법으로 Contract 클래스를 생성하고 BaseColumns 인터페이스 구현체를 만들어 기본 키 필드를 상속받는 것을 가이드하고 있습니다. 

object TodoContract {
    const val DATABASE_NAME = "Todo.db"

    object TodoEntry : BaseColumns {
        const val TABLE_NAME = "TODO"
        const val COLUMN_TITLE = "title"
        const val COLUMN_DESCRIPTION = "description"
        const val COLUMN_DATE = "date"

        const val CREATE_QUERY = "CREATE TABLE IF NOT EXISTS $TABLE_NAME (" +
                "$_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "$COLUMN_TITLE TEXT, " +
                "$COLUMN_DESCRIPTION TEXT, " +
                "$COLUMN_DATE TEXT)"

        const val DROP_QUERY = "DROP TABLE IF EXISTS $TABLE_NAME"
    }
}

 TodoContract 오브젝트를 생성하고 TodoEntry는 하나의 테이블 정의하는데 필요한 상수들을 선언하고 있습니다. 이처럼 스키마를 작성할 때는 전체 데이터베이스 레이아웃 역할을 하는 Contract 오브젝트를 생성하고 각 테이블마다 Entry 오브젝트를 생성하여 동일 패키지의 모든 클래스에서 사용할 수 있도록 합니다. 

private fun createTable() {
        Thread {
            db.execSQL(CREATE_QUERY)
        }.start()
}

 TodoEntry에 테이블을 생성하는 SQL문을 선언하고 새로운 스레드를 만들어서 execSQL 메소드를 이용하면 실행시킬 수 있습니다. 간단한 명령어는 메인스레드에서 쿼리를 해도 문제는 없지만, 일반적으로 DB 명령은 백그라운드 스레드에서 실행하는 것을 권장합니다. 테이블 이름은 대소문자 구별 없고 띄어쓰기만 조심하여 syntax error가 나타나지 않도록 합니다.

 

  View - Tool Windows - Devie File Expolorer을 통해 db 파일이 생성된 것을 확인할 수 있습니다. /data/data/package_name 폴더에 들어가면 databases 폴더가 있고 해당 폴더에 openOrCreateDatabase 메소드로 생성한 DB 파일이 있습니다. DB 파일을 읽기 위해서는 오른쪽 클릭 후 Save as 눌러 저장하고 DB Browser for SQLite 툴을 설치해야 합니다. 

 

• https://sqlitebrowser.org/dl/

Downloads - DB Browser for SQLite

  설치 후 실행하여 해당 DB 파일을 열면 다음과 같이 DB 내 테이블을 확인할 수 있습니다. 데이터 보기 탭에서는 테이블 내 저장된 데이터들도 확인이 가능합니다. 삭제도 동일하게 DROP TABLE 을 사용하여 execSQL 메소드로 실행하면 테이블 삭제가 됩니다.

 

 

 

데이터 추가하기

 private fun bindViews() = with(binding) {
 	...   
    
        Thread {
            var sql = "INSERT INTO $TABLE_NAME " +
                    "($COLUMN_TITLE, $COLUMN_DESCRIPTION, $COLUMN_DATE)" +
                    " VALUES " +
                    "('$title', '$description', '$date')"

            Log.d(TAG, sql)
            db.execSQL(sql)
            updateRecyclerView()
        }.start()
        
        ...
 }

  floatingButton을 클릭하면 Dialog가 나타나고 3개의 EditText로 Title, Description, Date 문자를 입력받게 구현되어 있습니다. 이후 저장을 클릭하면 백그라운드 스레드에서 INSERT DB 명령을 실행하도록 하였습니다. INSERT 구문에서 데이터에 해당되는 VALUES를 표현할 때 따옴표로 구별하여 각 컬럼의 데이터를 매칭시켜줘야 syntax error가 나타나지 않습니다.

 

 DB 파일에 정상적으로 작성되는 것을 알 수 있습니다. primary key에 해당하는 _id 필드는 autoincrement 해두어 1부터 시작하여 자동으로 데이터를 추가할 때마다 1씩 증가하여 추가가 됩니다.

 

 

 

데이터 업데이트

private fun updateTodoData(position: Int) {
	...
    
        Thread {
            var sql = "UPDATE $TABLE_NAME SET " +
                    "$COLUMN_TITLE = '$title', " +
                    "$COLUMN_DESCRIPTION = '$description', " +
                    "$COLUMN_DATE = '$date' " +
                    "WHERE $_ID = $position"
            Log.d(TAG, sql)
            db.execSQL(sql)
            updateRecyclerView()
        }.start()
        
        ...
    }

 Update도 동일하게 Dialog가 나타나서 Title, Description, Date를 받아서 변경하도록 하였습니다.  primary key가 _ID이기 때문에 조건문으로 변경하고 싶은 ID로 필터를 걸어 SQL문을 작성합니다. 기존의 데이터가 Title = Android, Description = SQLite, Date = 2021-12-02 로 저장되어 있으니, SQLite에서 Room으로 변경하여 업데이트 하였습니다.

 

 

 

 

데이터 조회하기

    private fun updateRecyclerView() {
        if(db == null) return

        todoAdapter.clear()
        Thread {
            val list = mutableListOf<Todo>()
            val sql = "select * from ${TABLE_NAME}"
            val cursor = db.rawQuery(sql, null)
            while (cursor.moveToNext()) {
                list.add(
                    Todo(
                        cursor.getString(1),
                        cursor.getString(2),
                        cursor.getString(3)
                    )
                )
            }
            todoAdapter.addAll(list)
            runOnUiThread {
                todoAdapter.notifyDataSetChanged()
            }
        }.start()
    }

  데이터를 조회하기 위해서는 SELECT로 시작하는 SQL문을 사용하게 되는데 이전에는 execSQL 메소드를 사용했지만, 데이터 조회는 결과를 받아야 하기 때문에 rawQuery라는 메소드를 사용해야 합니다. 

https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#rawQuery(java.lang.String,%20java.lang.String[])

  rawQuery 메소드의 리턴 값인 Cursor 구현체는 쿼리의 결과에 읽고 쓸 수 있는 접근 권한을 제공하는 인터페이스입니다. 즉, Cursor 를 통해서 이미 저장된 데이터들을 순서대로 접근할 수 있으며, 처음에는 아무런 데이터를 가리키지 않고 moveToNext 메소드로 다음 데이터를 가리키도록 하여 값을 읽을 수 있습니다. 위 예시에서 moveToNext 결과가 false 전까지 실행하여 Todo list에 추가하도록 구현하였습니다.

 

 

데이터 삭제하기

    private fun deleteTodoData(id: Int) {
        Thread {
            var sql = "DELETE FROM $TABLE_NAME WHERE _id = ${id}"
            db?.execSQL(sql)
            updateRecyclerView()
        }.start()
    }

  데이터 삭제 또한 DELETE 쿼리문을 사용하여 WHERE 에 해당하는 조건을 충족시키는 데이터를 삭제할 수 있도록 할 수 있습니다.

 

반응형

 

SQLiteOpenHelper

  SQLiteDatabase 는 SQLite에 접근하는 클래스로, SQL 명령어를 실행하고 DB를 관리하는 메소드를 가지고 있습니다. 위의 예시에서는 SQLiteDatabase를 openOrCreate 메소드를 사용했지만, 사실상 직접 사용하고 접근하는 일은 흔하지 않습니다. 또한 기존의 생성한 데이터베이스에 테이블을 생성하거나, 데이터 추가로 인한 스키마 변경하는 일이 있을 경우도 있습니다. 이를 간편하게 도와주는 클래스가  SQLiteOpenHelper로 DB 생성과 DB의 버전관리를 알아서 해주는 헬퍼(Helper)클래스입니다.

 

https://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper#SQLiteOpenHelper(android.content.Context,%20java.lang.String,%20android.database.sqlite.SQLiteDatabase.CursorFactory,%20int)

 생성자는 다음과 같으며, 세 번째 파라미터에는 Cursor 구현체를 생성하는 Factory를 전달할 수 있습니다.  기본 구현체인 SQLiteCursor를 사용한다면 null 값을 전달하면 되고, 새로운 Factory를 생성해서 전달할 수도 있지만 대부분 사용하지는 않습니다.

네 번째 파라미터에는 데이터베이스의 버전을 입력하게 되고, 버전을 업데이트할 경우에 해당 파라미터에는 기존의 버전보다 높게 대입을 하여 버전 변경을 할 수 있습니다.

class TodoSQLHelper private constructor(context: Context
) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

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

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

    companion object {
        const val DATABASE_VERSION = 1
        const val DATABASE_NAME = "Todo.db"
        var instance : TodoSQLHelper? = null

        fun getInstance(context: Context) : TodoSQLHelper{
            return if(instance == null) TodoSQLHelper(context.applicationContext) else instance!!
        }
    }
}

  DB의 생성 시점은 SQLiteOpenHelper 인스턴스를 생성될 때로 착각할 수 있지만 실제로는 인스턴스를 생성한 후 getReadableDatabase, getWritalbeDatabase 메소드를 호출할 때 DB가 생성됩니다. SQLiteOpenHelper 인스턴스 이미 생성을 했다면 그것을 사용하고 한 번도 생성한 적이 없다면 새로 생성 후 onCreate 또는 onUpgrade 콜백 메소드가 호출됩니다.

 

  SQLiteOpenHelper 는 추상클래스이며 템플릿 메소드 패턴을 사용하여 만들어 놓은 것으로, 위 코드에서 클래스를 상속해서 만든 클래스가 TodoSQLHelper 입니다.  onCreate, onUpgrade 등 콜백메소드를 재정의하여 원하는 SQL문을 실행할 수 있도록 구현할 수 있습니다. 위 예시에서는 헬퍼클래스로 DB가 생성 시 TODO 테이블이 생성되는 것을 확인할 수 있습니다.

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)

        ...

        Thread {
            db = TodoSQLHelper.getInstance(this).writableDatabase
        }.start()

        ...
    }

  또한 DB 헬퍼는 앱 전체에서 공통으로 한 개의 인스턴스만 사용할 수 있도록 싱글톤으로 구현해야 합니다. getInstance 메소드에서 Context를 파라미터로 받아 실제 클래스 생성은 applicationContext를 사용하여 앱 전체에서 단일 인스턴스만 생성할 수 있도록 반환하고 있습니다. 이후 실제 DB 는 SQLiteOpenHelper 클래스의 getWritableDatabase, getReableDatabase 메소드의 반환 값으로 가져올 수 있습니다. 말 그대로 Read-Only DB를 반환하는지에 따라 달라지게 되는데 자세한 내용은 공식문서를 확인하시면 됩니다. 그리고 DB를 생성하거나 업그레이드 작업은 오래 걸릴 수 있는 작업이기 때문에 메인 스레드가 아닌 백그라운드 스레드에서 실행해야 합니다.

 

• https://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper#getReadableDatabase() 

SQLiteOpenHelper  |  Android Developers

 

 

 

데이터 추가하기

 private fun bindViews() = with(binding) {
 	...   
    
        Thread {
           val values = ContentValues().apply {
                put(COLUMN_TITLE, title)
                put(COLUMN_DESCRIPTION, description)
                put(COLUMN_DATE, date)
           }
           db?.insert(TABLE_NAME, null, values)
           updateRecyclerView()
        }.start()
        
        ...
 }

  SQLiteOpenHelper 클래스를 사용하더라도 위에서 데이터 추가하는 방식으로 SQL문을 작성한 후 execSQL 메소드를 이용하면 정상적으로 데이터를 추가할 수 있습니다. 하지만, SQLiteDatabase 클래스 내에는 데이터 추가를 쉽게 하기 위한 메소드가 정의되어 있어 소개하려고 합니다. 그 전에 추가할 데이터를 ContentValues에 Key와 Value의 형태로 저장할 수 있습니다. Key는 데이터를 추가할 테이블의 Colume 명을 적은 후 해당 컬럼에 대입할 데이터를 put하도록 하여 생성하였습니다.  이후 insert 메소드를 사용하면 쉽게 데이터가 추가할 수 있습니다.

 

 

데이터 업데이트

    private fun updateTodoData(position: Int) {
        ...
        Thread {
            val values = ContentValues().apply {
                put(COLUMN_TITLE, title)
                put(COLUMN_DESCRIPTION, description)
                put(COLUMN_DATE, date)
            }
            val selection = "$_ID = ?"
            val selectionArg = arrayOf("$position")
            db?.update(TABLE_NAME, values, selection, selectionArg)
            updateRecyclerView()
        }.start()

        ...
    }

  업데이트도 마찬가지로 SQL 쿼리를 작성하여 execSQL 메소드를 호출하면 되지만 ContentValues로 업데이트 데이터를 정의한 후 update 메소드로 변경할 수 있습니다. 여기서 selection은 WHERE 문에 해당하여 변경해야할 컬럼 명을 선택하게 되고 selectionArg는 선택된 컬럼 명의 값을 입력하여 해당 값으로 변경될 수 있도록 합니다. 

 

 

데이터 조회하기

    private fun updateRecyclerView() {
        if(db == null) return

        todoAdapter.clear()
        Thread {
            val list = mutableListOf<Todo>()
            val projection = arrayOf(COLUMN_TITLE, COLUMN_DESCRIPTION, COLUMN_DATE)
            val sortOrder = "$_ID ASC"
            val cursor = db?.query(
                TABLE_NAME,
                projection,
                null,
                null,
                null,
                null,
                sortOrder
            )
            
            while (cursor.moveToNext()) {
                list.add(
                    Todo(
                        cursor.getString(0),
                        cursor.getString(1),
                        cursor.getString(2)
                    )
                )
            }
            todoAdapter.addAll(list)
            runOnUiThread {
                todoAdapter.notifyDataSetChanged()
            }
        }.start()
    }

 데이터를 조회하는 방법도 query 라는 메소드를 이용하면 쉽게 Cursor 구현체를 반환받을 수 있습니다. projection은 조회하려고 하는 컬럼들을 배열로 정의하고, 그 외 selection, selectionArg, groupBy, having, orderBy 등 다양하게 조건을 설정할 수 있습니다. 저는 전체의 값들을 조회하려고 selection과 selectionArg 값에 null을 대입하였습니다.  역시나 자세한 건 공식문서에 잘 나와있습니다.

 

 

 

데이터 삭제하기

    private fun deleteTodoData(id: Int) {
        Thread {
            val selection = "$_ID = ?"
            val selectionArg = arrayOf("$id")
            db?.delete(TABLE_NAME, selection, selectionArg)
            updateRecyclerView()
        }.start()
    }

  데이터 삭제하는 SQL문처럼 WHERE 조건절만 설정 후 delete 메소드만 호출하면 조건에 맞는 데이터를 삭제할 수 있습니다.

반응형

 

 

버전 업데이트

object TodoContract {
    object TodoEntry : BaseColumns {
        const val TABLE_NAME = "TODO"
        const val COLUMN_TITLE = "title"
        const val COLUMN_DESCRIPTION = "description"
        const val COLUMN_DATE = "date"
        const val COLUMN_TIME = "time"

        const val CREATE_QUERY = "CREATE TABLE IF NOT EXISTS $TABLE_NAME (" +
                "$_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "$COLUMN_TITLE TEXT, " +
                "$COLUMN_DESCRIPTION TEXT, " +
                "$COLUMN_DATE TEXT)"

        const val CREATE_QUERY2 = "CREATE TABLE IF NOT EXISTS $TABLE_NAME (" +
                "$_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "$COLUMN_TITLE TEXT, " +
                "$COLUMN_DESCRIPTION TEXT, " +
                "$COLUMN_DATE TEXT, " +
                "$COLUMN_TIME TEXT"

        const val DROP_QUERY = "DROP TABLE IF EXISTS $TABLE_NAME"
    }
}

  SQLiteOpenHelper 생성자의 매개변수에 DB 명이 있어 새로운 DB를 생성할 때마다 DB Helper 클래스가 필요합니다. 가능하면 하나의 헬퍼클래스를 사용하면 좋지만, 여러 DB를 사용하다가는 DB락 문제가 있을수도 있기에 하나의 DB에는 하나의 Helper 클래스를 만드는게 좋습니다.

 

class TodoSQLHelper private constructor(context: Context
) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

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

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        Log.d("msg", "onUpgrade")
        when(oldVersion){
            1 -> db?.execSQL("ALTER TABLE $TABLE_NAME ADD COLUMN " +
                    "$COLUMN_TIME TEXT")
        }
    }

    companion object {
        const val DATABASE_VERSION = 2
        const val DATABASE_NAME = "Todo.db"
        var instance : TodoSQLHelper? = null

        fun getInstance(context: Context) : TodoSQLHelper{
            return if(instance == null) TodoSQLHelper(context.applicationContext) else instance!!
        }
    }
}

  onCreate 콜백메소드가 SQLiteOpenHelper 클래스를 생성한 후 getWritableDatabase, getReableDatabase 를 호출할 때 실행된다고 하였습니다. onUpgrade 콜백메소드는 이미 DB가 생성된 시점에서 스키마를 변경하거나, 테이블을 추가하는 등 DB의 버전이 업데이트가 될 때 호출이 됩니다. 
  oldVersion에 이전 버전이고 newVersion이 새로운 버전으로 downgrade는 되지 않습니다. 즉, 버전 업데이트 시에는 이전 버전보다는 높은 숫자를 대입하여야만 합니다. 그리고 onCreate, onUpgrade 둘 메소드 중 무조건 하나만 실행이 됩니다. 현재 DB 버전이 4로 최신버전이 나왔을 때 새롭게 앱을 설치한 사람에게는 onCreate 메소드로 SQL 쿼리를 실행하고, 이전에 DB를 설치한 사람에게는 onUpgrade 메소드로 스키마 또는 테이블을 변경합니다.

 

최신 버전의 앱을 설치한 사람에게는 onCreate 메소드가 호출되어 CREATE_QUERY2로 SQL를 실행하고, 이미 이전 버전의 DB가 설치된 사람에게는 onUpgrade로 ALTER TABLE 쿼리를 실행합니다. 위의 그림은 onUpgrade로 time 컬럼을 추가하여 테이블을 변경하는 것을 알 수 있습니다.

 

 

 

 

 

참고

• 안드로이드 프로그래밍 Next Step p169 ~ p184
• 안드로이드 앱 프로그래밍 p537 ~ p551