안드로이드 SQLite

📌SQLite

• 로컬 데이터베이스를 생성하기 위해 사용
• 관계형 데이터베이스 구조를 따름
• 작은 규모의 안드로이드 앱에서 사용하기 적합한 데이터베이스
• android.database.sqlite 패키지 안에 API 명세되어있음

🔑관련 클래스, 메서드

클래스

• SQLiteDatabase
  CRUD 담당
• SQLiteOpenHelper
  데이터베이스 생성
• ContentValues
  데이터베이스에 자료 입력할 때 사용(안쓰기도 함)
• Cursor
  SQL을 실행하는 객체
  현재 가리키고 있는 로우를 나타내는 위치가 있음

메서드
moveToNext(), moveToFirst(), getColumnIndex(String heading)

🔑DBHelper.kt

SQListOpenHelper을 상속받아 테이블 생성하는 클래스
onCreate, onOpen, onUpgrade(DB가 구버전이라면 업그레이드 처리)

DB안에 table을 생성할때 일반적으로 _id라는 자동으로 ++되는 column도 추가함

private const val TAG = "DBHelper_싸피"
private const val TABLE = "mytable"

class DBHelper(
    context: Context,
    name: String,
    factory: SQLiteDatabase.CursorFactory?,
    version: Int
) : SQLiteOpenHelper(context, name, factory, version) {
    private lateinit var db: SQLiteDatabase
    override fun onCreate(db: SQLiteDatabase) {
        // 테이블 생성 쿼리
        val query: String =
            "CREATE TABLE if not exists $TABLE ( _id integer primary key autoincrement, txt text);"
        db.execSQL(query)
    }

    //  upgrade 가 필요한 경우 기존 테이블 drop 후 onCreate로 새롭게 생성
    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        val sql: String = "DROP TABLE if exists $TABLE"
        db.execSQL(sql)
        onCreate(db)
    }

    override fun onOpen(db: SQLiteDatabase) {
        super.onOpen(db)
        this.db = db
        Log.d(TAG, "onOpen: database 준비 완료")
    }

    fun insert(content: String) {
        // ContentValues를 이용한 저장
        val contentValues = ContentValues()
        contentValues.put("txt", content)
        db.beginTransaction()
        val result = db.insert(TABLE, null, contentValues)
        // sql을 이용한 저장
        // val query = "INSERT INTO mytable('txt') values('sql 문장 이용용');";
        // db.execSQL(query)
        if (result > 0) {
            db.setTransactionSuccessful()
        }
        db.endTransaction()
    }

    fun list(): String {
        var result = ""
//        val columns = arrayOf("_id", "txt")
//        db.query(TABLE, columns, null, null, null, null, null).use{
//            while (it.moveToNext()) {
//                result += "_id: ${it.getInt(0)}, txt: ${it.getString(1)}\n"
//            }
//        }
        db.rawQuery("select * from $TABLE", null).use{
            while (it.moveToNext()) {
                result += "_id: ${it.getInt(0)}, txt: ${it.getString(1)}\n"
            }
        }
        return result
    }

    fun update(id: String, content: String) {
        // ContentValues를 이용한 수정
        val contentValues = ContentValues()
        contentValues.put("txt", content)
        db.beginTransaction()
        val result = db.update(TABLE, contentValues, "_id=?", arrayOf(id))
        // sql을 이용한 수정
        // val query = "update $TABLE set txt=$content where _id=$id";
        // db.execSQL(query)
        if (result > 0) {
            db.setTransactionSuccessful()
        }
        db.endTransaction()
    }

    fun delete(id: String) {
        db.beginTransaction()
        val result = db.delete(TABLE, "_id=?", arrayOf(id))
        // sql을 이용한 삭제
        //val query = "delete from $TABLE where _id=$id";
        // db.execSQL(query)
        if (result > 0) {
            db.setTransactionSuccessful()
        }
        db.endTransaction()
    }

    fun select(id: String): Map<String, Any> {
        val columns = arrayOf("_id", "txt")
        val cursor = db.query(TABLE, columns, "_id=?", arrayOf(id), null, null, null)
        var result = mutableMapOf<String, Any>()
        if (cursor.moveToNext()) {
            result.put("_id", cursor.getInt(0))
            result.put("txt", cursor.getString(1))
        }
        return result
    }
}

insert문을 보면 직접 쿼리를 작성하여 db에 execute 하는 것은 주석처리 되어있고
contentValues를 사용하는 방법으로 되어있다.

contentValues에 내가 넣을 컬럼과 value를 넣어 db.insert문에 넣어서 쿼리를 실행할 수도 있다.

또한 list()문에서는 db.rawQuery문 안에 쿼리문을 넣어서 실행시켰다.

update()문도 contentValues를 활용하여 쿼리를 실행하였다.
val result = db.update(TABLE, contentValues, "_id=?", arrayOf(id))
?가 여러개 들어올 수 있기 때문에 arrayOf로 값을 전달하도록 함수가 구성되어 있다.

🔑DBHelper을 부르는 activity

private const val TAG = "DBHelper_싸피"
private const val TABLE = "mytable"

class DBHelper(
    context: Context,
    name: String,
    factory: SQLiteDatabase.CursorFactory?,
    version: Int
) : SQLiteOpenHelper(context, name, factory, version) {
    private lateinit var db: SQLiteDatabase
    override fun onCreate(db: SQLiteDatabase) {
        // 테이블 생성 쿼리
        val query: String =
            "CREATE TABLE if not exists $TABLE ( _id integer primary key autoincrement, txt text);"
        db.execSQL(query)
    }

    //  upgrade 가 필요한 경우 기존 테이블 drop 후 onCreate로 새롭게 생성
    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        val sql: String = "DROP TABLE if exists $TABLE"
        db.execSQL(sql)
        onCreate(db)
    }

    override fun onOpen(db: SQLiteDatabase) {
        super.onOpen(db)
        this.db = db
        Log.d(TAG, "onOpen: database 준비 완료")
    }

    fun insert(content: String) {
        // ContentValues를 이용한 저장
        val contentValues = ContentValues()
        contentValues.put("txt", content)
        db.beginTransaction()
        val result = db.insert(TABLE, null, contentValues)
        // sql을 이용한 저장
        // val query = "INSERT INTO mytable('txt') values('sql 문장 이용용');";
        // db.execSQL(query)
        if (result > 0) {
            db.setTransactionSuccessful()
        }
        db.endTransaction()
    }

    fun list(): String {
        var result = ""
//        val columns = arrayOf("_id", "txt")
//        db.query(TABLE, columns, null, null, null, null, null).use{
//            while (it.moveToNext()) {
//                result += "_id: ${it.getInt(0)}, txt: ${it.getString(1)}\n"
//            }
//        }
        db.rawQuery("select * from $TABLE", null).use{
            while (it.moveToNext()) {
                result += "_id: ${it.getInt(0)}, txt: ${it.getString(1)}\n"
            }
        }
        return result
    }

    fun update(id: String, content: String) {
        // ContentValues를 이용한 수정
        val contentValues = ContentValues()
        contentValues.put("txt", content)
        db.beginTransaction()
        val result = db.update(TABLE, contentValues, "_id=?", arrayOf(id))
        // sql을 이용한 수정
        // val query = "update $TABLE set txt=$content where _id=$id";
        // db.execSQL(query)
        if (result > 0) {
            db.setTransactionSuccessful()
        }
        db.endTransaction()
    }

    fun delete(id: String) {
        db.beginTransaction()
        val result = db.delete(TABLE, "_id=?", arrayOf(id))
        // sql을 이용한 삭제
        //val query = "delete from $TABLE where _id=$id";
        // db.execSQL(query)
        if (result > 0) {
            db.setTransactionSuccessful()
        }
        db.endTransaction()
    }

    fun select(id: String): Map<String, Any> {
        val columns = arrayOf("_id", "txt")
        val cursor = db.query(TABLE, columns, "_id=?", arrayOf(id), null, null, null)
        var result = mutableMapOf<String, Any>()
        if (cursor.moveToNext()) {
            result.put("_id", cursor.getInt(0))
            result.put("txt", cursor.getString(1))
        }
        return result
    }

}