안드로이드 앱 개발 연습 - 9 | Database

SQLite

DB.kt

class DB(context: Context, name: String, version: Int): SQLiteOpenHelper(context, name, null, version) {
    override fun onCreate(db: SQLiteDatabase?) {
        val create = "create table memo ('no' integer primary key, 'content' text, 'datetime' integer)"
        db?.execSQL(create)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) { }

    fun insertMemo(memo: Memo) {
        val values = ContentValues()
        values.put("content", memo.content)
        values.put("datetime", memo.datetime)

        val wd = writableDatabase
        wd.insert("memo", null, values)
        wd.close()
    }

    fun selectMemo(): MutableList<Memo> {
        val list = mutableListOf<Memo>()

        val select = "select * from memo"
        val rd = readableDatabase
        val cursor = rd.rawQuery(select, null)

        while (cursor.moveToNext()) {
            val noIdx = cursor.getColumnIndex("no")
            val contentIdx = cursor.getColumnIndex("content")
            val dateIdx = cursor.getColumnIndex("datetime")

            val no = cursor.getLong(noIdx)
            val content = cursor.getString(contentIdx)
            val datetime = cursor.getLong(dateIdx)

            list.add(Memo(no, content, datetime))
        }
        cursor.close()
        rd.close()
        return list
    }

    fun updateMemo(memo: Memo) {
        val values = ContentValues()
        values.put("content", memo.content)
        values.put("datetime", memo.datetime)

        val wd = writableDatabase
        wd.update("memo", values, "no = ${memo.no}", null)
        wd.close()
    }

    fun deleteMemo(memo: Memo) {
        val delete = "delete from memo where no = ${memo.no}"
        val wd = writableDatabase
        wd.execSQL(delete)
        wd.close()
    }
}

data class Memo(var no: Long?, var content: String, var datetime: Long)

MainActivity.kt

class MainActivity : AppCompatActivity() {
    val binding by lazy { ActivityMainBinding.inflate(layoutInflater) }
    val helper = DB(this, "memo", 1)

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

        val adapter = RecyclerAdapter(this)
        adapter.listData.addAll(helper.selectMemo())
        binding.recyclerMemo.adapter = adapter
        binding.recyclerMemo.layoutManager = LinearLayoutManager(this)
        binding.btnSave.setOnClickListener {
            if (binding.editMemo.text.toString().isNotEmpty()) {
                val memo = Memo(null, binding.editMemo.text.toString(), System.currentTimeMillis())
                helper.insertMemo(memo)
                adapter.listData.clear()
                adapter.listData.addAll(helper.selectMemo())
                adapter.notifyDataSetChanged()
                binding.editMemo.setText("")
            }
        }
    }
}

RecyclerAdapter.kt

class RecyclerAdapter(context: Context): RecyclerView.Adapter<RecyclerAdapter.Holder>() {
    var helper = DB(context, "memo", 1)
    var listData = mutableListOf<Memo>()

    override fun onCreateViewHolder(parent: ViewGroup, viewType: Int): Holder {
        val binding = ItemRecyclerBinding.inflate(LayoutInflater.from(parent.context), parent, false)
        return Holder(binding)
    }

    override fun onBindViewHolder(holder: Holder, position: Int) {
        val memo = listData[position]
        holder.setMemo(memo)
    }

    override fun getItemCount(): Int {
        return listData.size
    }

    inner class Holder(val binding: ItemRecyclerBinding): RecyclerView.ViewHolder(binding.root) {
        var tempMemo: Memo? = null

        init {
            binding.btnDelete.setOnClickListener {
                helper.deleteMemo(tempMemo!!)
                listData.remove(tempMemo)
                notifyDataSetChanged()
            }
        }

        fun setMemo(memo: Memo) {
            binding.txtNo.text = "${memo.no}"
            binding.txtContent.text = memo.content
            val sdf = SimpleDateFormat("yyyy/MM/dd hh:mm")
            binding.txtDate.text = "${sdf.format(memo.datetime)}"
            this.tempMemo = memo
        }
    }
}

---

Room (ORM)

build.gradle (:app)

plugins {
    id 'com.android.application'
    id 'org.jetbrains.kotlin.android'
    id "kotlin-kapt"
}

android {
    buildFeatures {
        viewBinding true
    }
    ...
}

dependencies {
    implementation "androidx.room:room-runtime:2.3.0"
    kapt "androidx.room:room-compiler:2.3.0"
    implementation "androidx.room:room-ktx:2.3.0"
    ...
}

RoomDB.kt

@Entity(tableName = "memo")
class RoomDB {
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo
    var no: Long? = null

    @ColumnInfo
    var content: String = ""

    @ColumnInfo(name = "date")
    var datetime: Long = 0

    @Ignore
    var temp: String = "This var is not used as a table's column"

    constructor(content: String, datetime: Long) {
        this.content = content
        this.datetime = datetime
    }
}

RoomDao.kt

@Dao
interface RoomDao {
    @Query("SELECT * FROM memo")
    fun getAll(): List<RoomDB>

    @Query("SELECT * FROM memo WHERE memo.'no' = :ID")
    fun findByID(ID: Int): RoomDB

    @Insert(onConflict = REPLACE)
    fun insert(memo: RoomDB)

    @Update
    fun update(memo: RoomDB)

    @Delete
    fun delete(memo: RoomDB)
}

RoomHelper.kt

@Database(entities = [RoomDB::class], version = 1, exportSchema = false)
abstract class RoomHelper: RoomDatabase() {
    abstract fun roomDao(): RoomDao
}

MainActivity.kt

class MainActivity : AppCompatActivity() {
    val binding by lazy { ActivityMainBinding.inflate(layoutInflater) }
    var helper: RoomHelper? = null
    var mode: String = "submit" // Mode: submit, edit

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

        helper = Room.databaseBuilder(this, RoomHelper::class.java, "memo").allowMainThreadQueries().build()

        val adapter = RecyclerAdapter()
        adapter.helper = helper

        adapter.listData.addAll(helper?.roomDao()?.getAll()?: listOf())
        binding.recyclerMemo.adapter = adapter
        binding.recyclerMemo.layoutManager = LinearLayoutManager(this)

        binding.btnSubmit.setOnClickListener {
            if (binding.inputMemo.text.toString().isNotEmpty()) {
                val memo = RoomDB(binding.inputMemo.text.toString(), System.currentTimeMillis())
                helper?.roomDao()?.insert(memo)
                adapter.listData.clear()
                adapter.listData.addAll(helper?.roomDao()?.getAll()?: listOf())
                adapter.notifyDataSetChanged()
                binding.inputMemo.setText("")
            }
        }
    }
}

RecyclerAdapter.kt

class RecyclerAdapter: RecyclerView.Adapter<RecyclerAdapter.Holder>() {
    var listData = mutableListOf<RoomDB>()
    var helper: RoomHelper? = null

    inner class Holder(private val binding: RecyclerViewBinding): RecyclerView.ViewHolder(binding.root) {
        var tempMemo: RoomDB? = null

        init {
            binding.btnDelete.setOnClickListener {
                helper?.roomDao()?.delete(tempMemo!!)
                listData.remove(tempMemo)
                notifyDataSetChanged()
            }
        }

        fun setMemo(memo: RoomDB) {
            binding.textID.text = "${memo.no}"
            binding.textContents.text = memo.content
            binding.textDate.text = SimpleDateFormat("yyyy/MM/dd hh:mm").format(memo.datetime)
            this.tempMemo = memo
        }
    }

    override fun onCreateViewHolder(parent: ViewGroup, viewType: Int): Holder {
        val binding = RecyclerViewBinding.inflate(LayoutInflater.from(parent.context), parent, false)
        return Holder(binding)
    }

    override fun onBindViewHolder(holder: Holder, position: Int) {
        val memo = listData[position]
        holder.setMemo(memo)
    }

    override fun getItemCount(): Int {
        return listData.size
    }
}