SQLite 이해하고 SQLiteOpenHelper로 CRUD 구현하기

이번 포스팅에서는 두 파트에 걸쳐 안드로이드에서 SQLite를 다루는 법에 대해 알아보도록 하겠습니다.

Part A. 데이터베이스

관계형 데이터베이스

여러 사람들의 연락처 데이터는 다음과 같이 테이블 구조로 관리할 수 있습니다. 이런 식으로 통일된 규칙의 데이터를 관리할 수 있는 테이블을 데이터베이스라고 합니다.

순서	이름	전화번호	이메일	주소
0	김철수	010-1111-1111	kim@abc.com	서울
1	이영희	010-2222-2222	lee@abc.com	서울
2	박모모	010-3333-3333	park@abc.com	인천

이 때 1행 2열의 이름을 Key라고 하고 2행 2열의 김철수를 Value라고 합니다. 전화번호, 이메일, 주소도 역시 Key이고 그에 대응되는 값들도 역시 Value가 되겠죠. 순서라는 Key는 각 데이터에 대해 겹치지 않는 고유한 값이기 때문에 고유키(Primary Key)라고 합니다.

이렇게 Key와 Value가 1:1이나 1:n 또는 n:n 의 관계를 가지는 데이터베이스를 관계형 데이터베이스(Relational Database)라고 합니다.

SQL과 CRUD

위에 보여드린 DB에서 박모모의 전화번호를 찾는건 매우 간단합니다. 하지만 만약 행의 길이가 수만줄에 이르고 그 안에 박모모의 동명이인도 수백명이 존재하는 테이블이 있다면 어떨까요? 그 안에서 내가 원하는 박모모의 전화번호를 찾는건 쉽지 않은 일일겁니다. Structured Query Language (SQL)는 그런 복잡한 DB를 좀 더 용이하게 다루기 위해서 만들어진 언어입니다.

CRUD라는 표현을 들어보신적 있으실 겁니다. CRUD는 기본적인 데이터 처리 기능인 Create(생성), Read(읽기), Update(갱신), Delete(삭제)를 의미하는 말로, 데이터베이스를 다루기 위해 가장 기본이 되는 4가지의 명령을 묶어놓은 단어입니다. SQL은 다음과 같은 명령어를 통해 CRUD를 구현할 수 있습니다.

이름	조작	SQL
Create	생성	INSERT
Read(또는 Retrieve)	읽기(또는 인출)	SELECT
Update	갱신	UPDATE
Delete(또는 Destroy)	삭제(또는 파괴)	DELETE

SQL에서 실제 CRUD는 다음과 같이 구현하게 되는데요, 언어의 전체 문법은 SQL syntax 페이지에서 확인할 수 있습니다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


// 작성자가 무명씨, 홍길동인 문서의 모든 항목을 가져옴
SELECT * FROM document WHERE author like '김*';

// table에 field1=value1, field2=value2, ...와 같은 속성값을 가지는 항목을 새로 생성하여 삽입
INSERT INTO table(field1, field2, ...) VALUES (value1, value2, ...);

// 데이터를 수정하는 구문으로 table의 field1에 value1, field2에 value2, ... 로 변경
UPDATE table SET field1=value1, field2=value2, {WHERE 조건};

// 데이터를 삭제
DELETE FROM table {WHERE 조건};

// 데이터베이스 내의 모든 테이블, 스키마, 관계(Relation)를 전부 삭제
DROP DATABASE database_name;

DataBase Management System

대량의 데이터를 다루는데 관계형 데이터베이스가 사용되고, SQL이라는 언어를 사용해 데이터베이스의 데이터를 다룬다는 걸 알았습니다. 이런 데이터베이스와 SQL을 결합해서, 사용할 수 있는 형태의 프로그램으로 만든 것을 Database Management System이라고 합니다. 거기에 관계형 데이터베이스를 사용했다면 RDBMS가 되겠죠.

여러분이 잘 아시는 Oracle이나 MySQL 등이 대표적인 DBMS입니다. DB-Engines Ranking에 따르면 Oracle, MySQL, Microsoft SQL Server가 현재 시장에서 제일 인기있는 DBMS라고 하네요.

출처:DB-Engines Ranking

SQLite란

그렇게 많고많은 DBMS중에 SQLite라는 것이 있습니다. 표준SQL을 지원하는 DB를 파일 하나로 구현한 경량 DBMS인데요, 운용시 리소스사용이 적고 무료로 사용할 수 있는 오픈소스이기 때문에 Android와 iOS에서 DB를 구현하기 위한 기본 라이브러리로 채택되어 있습니다.

처음엔 미해군의 구축함에서 이용하기 위해 만들어진 프로그램인데 공식 홈페이지에 따르면 현재는 전세계에서 1조개가 넘는 SQLite가 운용되고 있다고 하네요. SQLite에 대해 더 알고싶으신 분은 SQLite의 알려지지 않은 이야기를 읽어보시면 좋을 것 같습니다.

안드로이드의 SQLite 버전

상기한 이유로 안드로이드 API에는 SQLite가 기본 내장되어 있습니다. 복잡한 DB를 다룰 계획이 없다면 SQLite를 이용해 DB를 구축하는 것으로 충분합니다.

현재까지 개발된 SQLite의 최신 버전은 3.36.0 (2021-06-18)인데 Android API에 내장되는 SQLite는 안정성을 고려해 다음 표와 같이 좀 더 낮은 버전이 내장되어 있습니다.

Android API	SQLite Version
API 31	3.32
API 30	3.28
API 28	3.22
API 27	3.19
API 26	3.18

코틀린에서는 다음 명령어를 이용해 현재 사용중인 SQLite의 버전을 확인할 수도 있습니다.

1
2
3
4


val version = android.database.sqlite.SQLiteDatabase.create(null).use {
    DatabaseUtils.stringForQuery(it, "SELECT sqlite_version()", null)
}
println("Framework (API ${Build.VERSION.SDK_INT}) SQLite version: $version")

외부 라이브러리를 추가하면 안드로이드에서 최신버전의 SQLite를 사용할 수도 있습니다만 가능하면 공식 라이브러리를 사용하는 게 좋겠죠.

안드로이드에서의 SQLite 사용

SQLite를 사용하기 위해서는 DB를 만들고 그 DB에 대해 SQL query를 전달하여 CRUD를 수행하면 됩니다. 안드로이드에서는 SQLiteDatabase 클래스를 이용해, 작성한 데이터베이스에 대해 CRUD를 수행할 수 있습니다.

그런데 구글 공식문서인 Save data using SQLite에서는 다음과 같은 이유로 이 작업을 직접 수행하는것을 권하지 않고 있습니다.

원시 SQL 쿼리에 관한 컴파일 시간 확인이 없습니다. 따라서 데이터 그래프가 변경됨에 따라 영향을 받는 SQL 쿼리를 수동으로 업데이트해야 합니다. 이 과정은 시간이 오래 걸리고 오류가 쉽게 발생할 수 있습니다.

SQL 쿼리와 데이터 객체 간에 변환하려면 많은 상용구 코드를 사용해야 합니다.

그 대신 구글에서는 SQLite를 더 안전하게 사용할 수 있는 SQLiteOpenHelper라는 헬퍼클래스를 제공하고 있습니다. 다음 파트에서는 이 SQLiteOpenHelper 클래스를 사용해서 CRUD작업을 하는 간단한 앱을 만들어보도록 하겠습니다.

Part B. 샘플 앱 작성

메인 레이아웃

New Project > Empty Activity를 선택해 빈 프로젝트를 하나 만들어줍니다.

그리고 앱에서 사용할 화면을 만듭니다. 전화번호부 DB를 상정할 것이므로 이름, 전화번호, 이메일 그리고 데이터의 Primary Key로 사용할 ID를 화면에서 입력할 수 있도록 EditText를 배치하겠습니다. 고유키는 데이터를 데이터베이스에 입력할 때 자동으로 부여되기 때문에 입력할 필요가 없지만 삭제, 업데이트를 테스트할 때 필요하기 때문입니다.

CRUD 테스트를 위해서 데이터를 입력하는 INSERT, 갱신하는 UPDATE, 삭제하는 DELETE 그리고 DB의 모든 내용을 표시하는 VIEW 버튼을 만들겠습니다. 작동에 대한 로그는 화면 아래쪽에 배치한 ScrollView에 표시되도록 하였습니다.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155


<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".MainActivity">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:gravity="center"
            android:text="ID: "
            android:textStyle="bold" />

        <EditText
            android:id="@+id/et_id"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="2"
            android:hint="ID"
            android:inputType="number" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:gravity="center"
            android:text="NAME: "
            android:textStyle="bold" />

        <EditText
            android:id="@+id/et_name"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="2"
            android:hint="NAME"
            android:inputType="textPersonName" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:gravity="center"
            android:text="PHONE: "
            android:textStyle="bold" />

        <EditText
            android:id="@+id/et_phone"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="2"
            android:hint="PHONE"
            android:inputType="phone" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:gravity="center"
            android:text="Email: "
            android:textStyle="bold" />

        <EditText
            android:id="@+id/et_email"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="2"
            android:hint="Email"
            android:inputType="textEmailAddress" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:gravity="center_vertical"
        android:orientation="horizontal">

        <Button
            android:id="@+id/btn_insert"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_margin="1dp"
            android:layout_weight="1"
            android:textSize="12dp"
            android:text="INSERT" />

        <Button
            android:id="@+id/btn_update"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_margin="1dp"
            android:layout_weight="1"
            android:textSize="12dp"
            android:text="UPDATE" />

        <Button
            android:id="@+id/btn_delete"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_margin="1dp"
            android:layout_weight="1"
            android:textSize="12dp"
            android:text="DELETE" />

        <Button
            android:id="@+id/btn_view"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_margin="1dp"
            android:layout_weight="1"
            android:textSize="12dp"
            android:text="VIEW" />

    </LinearLayout>

    <ScrollView
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:background="@color/teal_200"
        android:padding="10dp">

        <TextView
            android:id="@+id/tv_result"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textSize="20sp" />

    </ScrollView>

</LinearLayout>

DatabaseHelper 작성

다음은 SQLiteOpenHelper 클래스를 상속하는 DatabaseHelper 클래스를 작성합니다. 이 앱에서 사용하게 될 데이터베이스의 프라이머리키는 _id이고 그 외에 name, phone, email 이라는 키를 갖고 있습니다. 데이터베이스의 파일명 및 그외 중복으로 써야하는 상수들을 companion object에 정의하고 상속에 필요한 생성자들을 전달해줍니다.

DatabaseHelper는 복수개의 인스턴스가 생성되어 DB에 동시접근할수 있는 문제를 방지하기 위해 싱글톤으로 작성합니다. constructor에 직접 접근하지 못하도록 private화 하고, getInstance에서 Double Checked Locking을 통해 인스턴스를 반환하는 싱글톤 구조를 구축합니다. 싱글톤에 대한 더 자세한 설명은 알기쉬운 Singleton Pattern 강의를 참고하세요.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24


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

    companion object {
        const val DATABASE_NAME = "PhoneBook.db"
        const val DATABASE_VERSION = 1
        const val TABLE_NAME = "book_table"
        const val COL1_ID = "_id"
        const val COL2_NAME = "name"
        const val COL3_PHONE = "phone"
        const val COL4_EMAIL = "email"

        //SingleTon Pattern(싱글톤 패턴)
        @Volatile
        private var instance: DatabaseHelper? = null

        fun getInstance(context: Context) =
            instance ?: synchronized(DatabaseHelper::class.java) {
                instance ?: DatabaseHelper(context).also {
                    instance = it
                }
            }
    }
}

onCreate 구현

테이블을 생성하는 onCreate함수를 오버라이드 합니다. onCreate는 시스템에 DB가 없을경우에만 실행되기 때문에 쿼리를 CREATE TABLE IF EXIST로 줄 필요가 없습니다.

_id는 데이터가 입력될 때 자동으로 증가시키기 위해 PRIMARY KEY AUTOINCREMENT를 추가해줍니다. 작성한 쿼리는 execSQL로 실행시키면 됩니다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


override fun onCreate(db: SQLiteDatabase?) {
    val createQuery = "CREATE TABLE $TABLE_NAME (" +
            "$COL1_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "$COL2_NAME TEXT, " +
            "$COL3_PHONE TEXT, " +
            "$COL4_EMAIL TEXT" +
            ")"

    db?.execSQL(createQuery)
}

onUpgrade 구현

다음은 DB버전이 변경되었을 때 동작하는 onUpgrade를 구현합니다. 여기서는 DB버전이 증가되었으면 기존 DB를 삭제하고 새로운 DB를 작성하는 단순한 구조로 정의했습니다.

1
2
3
4
5
6


override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
    if (oldVersion != newVersion) {
        db?.execSQL("DROP TABLE IF EXISTS $TABLE_NAME")
        onCreate(db)
    }
}

CRUD 구현

Create

우선은 CRUD의 C를 구현하겠습니다. insertData에서는 name, phone, email을 파라메터로 받게 됩니다. writableDatabase로 데이터베이스를 오픈하고 외부에서 받아온 데이터를 contentValues로 묶어서 insert 명령으로 데이터베이스에 값을 추가합니다.

1
2
3
4
5
6
7
8
9


fun insertData(name: String, phone: String, email: String) {
    val db = this.writableDatabase
    val contentValues = ContentValues().apply {
        put(COL2_NAME, name)
        put(COL3_PHONE, phone)
        put(COL4_EMAIL, email)
    }
    db.insert(TABLE_NAME, null, contentValues) // 값이 없으면 행을 삽입하지않음
}

Update

update에서는 id로 선택해 준 데이터의 내용을 갱신합니다. insert에서처럼 writableDatabase로 데이터베이스를 오픈하고 contentValues를 넘겨주어 데이터를 갱신합니다. 이 때 "$COL1_ID = ?" 쿼리를 사용해 갱신할 데이터를 특정해주면 됩니다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


fun updateData(id: String, name: String, phone: String, email: String) {
    val db = this.writableDatabase
    val contentValues = ContentValues().apply {
        put(COL1_ID, id)
        put(COL2_NAME, name)
        put(COL3_PHONE, phone)
        put(COL4_EMAIL, email)
    }
    db.update(TABLE_NAME, contentValues, "$COL1_ID = ?", arrayOf(id))
}

Delete

전달받은 id를 프라이머리키로 가진 데이터를 delete 명령으로 삭제합니다.

1
2
3
4


fun deleteData(id: String) {
    val db = this.writableDatabase
    db.delete(TABLE_NAME, "$COL1_ID = ?", arrayOf(id))
}

Read

마지막으로 읽기를 구현합니다. readableDatabase로 데이터베이스를 열고 rawQuery 에 "SELECT * FROM $TABLE_NAME" 을 전달하여 Cursor 객체를 받아옵니다.

객체가 정상적으로 얻어졌으면 try 블록 안에서 moveToNext로 데이터를 순회하면서 값을 확인하고 커서를 다 사용했으면 close로 리소스를 반환하도록 합니다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26


fun getAllData(): String {
    var result = "No data in DB"

    val db = this.readableDatabase
    val cursor = db.rawQuery("SELECT * FROM $TABLE_NAME", null)

    try {
        if (cursor.count != 0) {
            val stringBuffer = StringBuffer()
            while (cursor.moveToNext()) {
                stringBuffer.append("ID :" + cursor.getInt(0).toString() + "\n")
                stringBuffer.append("NAME :" + cursor.getString(1) + "\n")
                stringBuffer.append("PHONE :" + cursor.getString(2) + "\n")
                stringBuffer.append("EMAIL :" + cursor.getString(3) + "\n\n ")
            }
            result = stringBuffer.toString()
        }
    } catch (e: Exception) {
        e.printStackTrace()
    } finally {
        if (cursor != null && !cursor.isClosed) {
            cursor.close()
        }
    }
    return result
}

MainActivity 준비

뷰 바인딩을 적용한 메인액티비티를 작성합니다. dbHelper 인스턴스를 생성하고, 사용이 끝나면 onDestroy에서 close()로 리소스를 반환하도록 하였습니다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


class MainActivity : AppCompatActivity() {
    private val binding: ActivityMainBinding by lazy {
        ActivityMainBinding.inflate(layoutInflater)
    }

    private val dbHelper: DatabaseHelper by lazy {
        DatabaseHelper.getInstance(applicationContext)
    }

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(binding.root)
    }

    override fun onDestroy() {
        dbHelper.close()
        super.onDestroy()
    }
}

유틸 메소드 준비

데이터베이스 조작이 이루어졌을때 처리 결과를 텍스트뷰에 표시하기 위한 showTxt를 작성합니다. 그리고 버튼을 눌렀을 때 에디트텍스트에 입력한 내용을 삭제하는 clearEditTexts 함수도 작성합니다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


private fun showTxt(text: String) {
    binding.tvResult.append(text + "\n")
}

private fun clearEditTexts() {
    with(binding) {
        etId.setText("")
        etName.setText("")
        etPhone.setText("")
        etEmail.setText("")
    }
}

버튼기능 정의

각 버튼을 클릭했을 때 부여할 기능을 정의합니다. 값을 입력하는 insertDb, 갱신하는 updateDb, 삭제하는 deleteDb, 그리고 조회하는 getAllDb함수를 만듭니다. 작업은 try 블록 안에서 실행하며 에디트텍스트에 입력한 값을 dbHelper에 정의한 각 함수로 전달합니다. 이 때 입력되는 sql 쿼리에 공백이 있으면 안되기 때문에 trim으로 공백을 제거하도록 했습니다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55


private fun insertDb() {
    binding.btnInsert.setOnClickListener {
        try {
            dbHelper.insertData(
                binding.etName.text.toString().trim(),
                binding.etPhone.text.toString().trim(),
                binding.etEmail.text.toString().trim(),
            )
            clearEditTexts()
            showTxt("Data inserted")
        } catch (e: Exception) {
            e.printStackTrace()
        }
    }
}

private fun updateDb() {
    binding.btnUpdate.setOnClickListener {
        try {
            dbHelper.updateData(
                binding.etId.text.toString().trim(),
                binding.etName.text.toString().trim(),
                binding.etPhone.text.toString().trim(),
                binding.etEmail.text.toString().trim(),
            )
            showTxt("Data updated")

        } catch (e: Exception) {
            e.printStackTrace()
        }
    }
}

private fun deleteDb() {
    binding.btnDelete.setOnClickListener {
        try {
            dbHelper.deleteData(binding.etId.text.toString().trim())
            clearEditTexts()
            showTxt("Data deleted")
        } catch (e: Exception) {
            e.printStackTrace()
        }
    }
}

private fun getAllDb() {
    binding.btnView.setOnClickListener {
        try {
            val selectResult = dbHelper.getAllData()
            showTxt(selectResult)
        } catch (e: Exception) {
            e.printStackTrace()
        }
    }
}

onCreate 정의

마지막으로 위에서 정의한 버튼기능을 onCreate 안에서 실행하도록 하면 되겠죠.

1
2
3
4
5
6
7
8
9


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

    insertDb()
    updateDb()
    deleteDb()
    getAllDb()
}

이렇게 해서 SQLite의 개념과, SQLiteHelper를 이용해 안드로이드에서 데이터베이스를 다루는 법에 대해 알아보았습니다.