안드로이드 SQLiteOpenHelper 구현

별거 없다. 그냥 유튭이나 블로그들에 잘 정리 되어 있다.

package com.example.mysqlite.helper

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

class SqliteHelper(context: Context, name: String = "todo.db", version: Int = 1) :
    SQLiteOpenHelper(context, name, null, version) {

    override fun onCreate(db: SQLiteDatabase?) {
        db?.execSQL(
            """create table todo (
            |`no` integer primary key
            |,content text
            |,datetime integer
            |)""".trimMargin()
        )
        Log.d("onCreate", db?.toString() ?: "What?!")

    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        //테이블구조에 변경사항이 있을 경우 생성자 호출할 때 버전을 넣으면 실행 됨
        //아마 onCreate는 무조건 딱 한번만 실행(없으면 생성)이 되나 보다 그래서 스키마에 변경 사항이 필요할 때 요걸 쓰나 보다
        //데이터를 보존한 상태에서 하고 싶다면 백업처리 후 삭제,신규 생성 후 데이터 저장, 백업삭제 이래야 하나 보다.
        db?.run {
            execSQL("create table todo_backup as select * from memo")
            execSQL("drop table if exists todo")
            onCreate(db)
            execSQL("insert into todo(`no`,content,datetime) select `no`,content,datetime from todo_backup")
            execSQL("drop table if exists todo_backup")
        }
    }

    fun selectTodoList(): MutableList<Todo> {
        val list = mutableListOf<Todo>()
        val rd = readableDatabase
        val cursor = rd.rawQuery("select * from todo", null)

        while (cursor.moveToNext()) {
            /*list.add(
                Todo(
                    cursor.getLong(0),
                    cursor.getString(1),
                    cursor.getLong(2)
                )
            )*/
            val no = cursor.getLong(cursor.getColumnIndexOrThrow("no"))
            val content = cursor.getString(cursor.getColumnIndexOrThrow("content"))
            val datetime = cursor.getLong(cursor.getColumnIndexOrThrow("datetime"))
            list.add(Todo(no, content, datetime))
        }
        cursor.close()
        rd.close()

        return list
    }

    fun insertTodo(content: String, datetime: Long): Long {
        val wd = writableDatabase
        val values = ContentValues().apply {
            put("content", content)
            put("datetime", datetime)
        }

        val newRowId = wd.insert("todo", "", values)
        wd.close()
        return newRowId
    }

    fun updateTodo(todo:Todo) : Int {
        val wd = writableDatabase
        val values = ContentValues().apply {
            put("content", todo.content)
            put("datetime", todo.datetime)
        }

        //val rowId = wd.update("todo", values,"no=?", arrayOf(todo.no.toString()))
        val rowId = wd.update("todo", values,"no=${todo.no}",null)
        wd.close()
        return rowId
    }

    fun deleteTodo(no:Long) : Int {
        val wd = writableDatabase
        val affectedRows = wd.delete("todo", "no=?", arrayOf(no.toString()))
        //val affectedRows = wd.delete("todo","no=${todo.no}",null)
        wd.close()
        return affectedRows
    }

    /*companion object {
        private const val DATABASE_VERSION = 1
        private const val DATABASE_NAME = "todo.db"
        private var instance: SqliteHelper? = null

        @JvmStatic
        fun getInstance(context: Context, version: Int = 1): SQLiteOpenHelper {
            return if (instance == null) SqliteHelper(
                context.applicationContext,
                DATABASE_NAME,
                if (DATABASE_VERSION == version) DATABASE_VERSION else version
            )
            else instance!!
        }
    }*/
}

data class Todo(val no: Long, val content: String, val datetime: Long)

package com.example.mysqlite

import android.os.Bundle
import android.util.Log
import androidx.activity.enableEdgeToEdge
import androidx.appcompat.app.AppCompatActivity
import androidx.core.view.ViewCompat
import androidx.core.view.WindowInsetsCompat
import com.example.mysqlite.databinding.ActivityMainBinding
import com.example.mysqlite.helper.SqliteHelper
import com.example.mysqlite.helper.Todo

class MainActivity : AppCompatActivity() {

    private val binding by lazy {
        ActivityMainBinding.inflate(layoutInflater)
    }

    private lateinit var helper : SqliteHelper

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        enableEdgeToEdge()
        setContentView(binding.root)
        ViewCompat.setOnApplyWindowInsetsListener(binding.main) { v, insets ->
            val systemBars = insets.getInsets(WindowInsetsCompat.Type.systemBars())
            v.setPadding(systemBars.left, systemBars.top, systemBars.right, systemBars.bottom)
            insets
        }

        helper = SqliteHelper(this)
        Log.d("helper",helper.toString())
        Log.d("path",this.getDatabasePath(helper.databaseName).toString())

        /*val rowId = helper.insertTodo("do something",System.currentTimeMillis())
        Log.d("rowId",rowId.toString())*/
        /*val rowId = helper.updateTodo(1)
        Log.d("rowId",rowId.toString())*/
        val affectedRows = helper.deleteTodo(1)
        Log.d("affectedRows",affectedRows.toString())
    }


}