'가게 재고 관리 앱' - DB 연결

이전 포스트와 이어집니다.

'가게 재고 관리 앱' 만들기

---

요구사항

• 이전과 동일
• 같은 내용을 database(sqlite)를 사용해서 저장하자

로직 설명

• DBHelper는 database를 관리하는 클래스입니다.
• Dao는 dbhelper에 접근하는 클래스입니다.

---

DBHelper.kt

private const val TABLE ="stuffTable"
class DBHelper (
    context: Context
) : SQLiteOpenHelper(context,"stuff.db",null,3) {
    private lateinit var db: SQLiteDatabase
    private val columns = arrayOf("id", "name","amount")

    override fun onCreate(p0: SQLiteDatabase?) { //database를 생성
        val query = "CREATE TABLE if not exists $TABLE ( " +
                "id integer primary key autoincrement, " +
                "name text," +
                "amount Integer)"
        db.execSQL(query)
        Log.d(TAG, "create")
    }

    override fun onUpgrade(p0: SQLiteDatabase?, p1: Int, p2: Int) {
        val sql: String = "DROP TABLE if exists $TABLE"
        db.execSQL(sql)
    }

    override fun onOpen(db: SQLiteDatabase) { //database를 호출
        super.onOpen(db)
        this.db = db
    }

    fun selectAllStuffs(): MutableList<StuffDto>{ //모두 가져옴
        var dtos = mutableListOf<StuffDto>()
        db.rawQuery("select * from $TABLE", null).use{
            while (it.moveToNext()) {
                dtos.add(StuffDto(it.getInt(0), it.getString(1), it.getInt(2)))
            }
        }
        return dtos
    }

    fun getCount():Int{ //개수를 가져옴
        var count : Int = 0
        db.rawQuery("select count(*) from $TABLE", null).use{
            if (it.moveToNext()) {
                count = it.getInt(0)
            }
        }
        return count
    }

    fun selectStuff(num: Int): StuffDto?{ //검색
        val cursor = db.query(TABLE, columns, "id=${num}", arrayOf(), null, null, null)
        if (cursor.moveToNext()) {
            cursor.also {
                var dto = StuffDto(it.getInt(0), it.getString(1), it.getInt(2))
                return dto
            }
        }
        return null
    }

    fun insertStuff(dto: StuffDto) : Long{
        // ContentValues를 이용한 저장
        val contentValues = getContent(dto)
        Log.v(TAG,"insert")

        db.beginTransaction()
        val result = db.insert(TABLE, null, contentValues)
        if (result > 0) {
            db.setTransactionSuccessful()
        }
        db.endTransaction()
        return 1
    }

    fun updateStuff(dto : StuffDto): Int{
        // ContentValues를 이용한 수정
        val contentValues = ContentValues().apply {
            put("name", dto.name)
            put("amount", dto.amount)
        }
        db.beginTransaction()
        val result = db.update(TABLE, contentValues, "id=?", arrayOf(dto.id.toString()))
        if (result > 0) {
            db.setTransactionSuccessful()
            Log.v(TAG,"sucess")
        }
        Log.v(TAG,"end")
        db.endTransaction()
        return 1
    }

    fun deleteStuff(num: Int):Int{
        db.beginTransaction()
        val result = db.delete(TABLE, "id=${num}", arrayOf())
        if (result > 0) {
            db.setTransactionSuccessful()
        }
        db.endTransaction()
        return 1
    }

    private fun getContent(dto: StuffDto): ContentValues {
        val contentValues = ContentValues()
        contentValues.put(columns[1],dto.name)
        contentValues.put(columns[2],dto.amount)
        return contentValues
    }
}

StuffDao.kt

class StuffDao {
    //DB선언부
    lateinit var helper: DBHelper
    lateinit var sqlDB : SQLiteDatabase
    private var mCtx: Context? = null

    fun DbOpenHelper(context: Context){
        Log.v(TAG,"DbOpenHelper")
        mCtx = context
    }

    @Throws(SQLiteException::class)
    fun open(){
        Log.v(TAG,"open")
        helper = DBHelper(mCtx!!)
        sqlDB = helper.writableDatabase
    }

    fun selectAllStuffs(): MutableList<StuffDto>{
        return helper.selectAllStuffs()
    }

    fun getCount():Int{
        return helper.getCount()
    }

    fun selectStuff(num: Int): StuffDto?{
        return helper.selectStuff(num)
    }

    fun insertStuff(dto: StuffDto) : Long{
        return helper.insertStuff(dto)
    }

    fun updateStuff(dto : StuffDto): Int{
        return helper.updateStuff(dto)
    }

    fun deleteStuff(num: Int):Int{
        return helper.deleteStuff(num)
    }
}

Actifvity에서 database 사용

private var stuffDao = StuffDao()
    
//database
stuffDao.DbOpenHelper(this)
stuffDao.open()