SQLite 간단하게.

 

SQLite 는 내장 DB이다. 지난 프로젝트에서 SQLite 를 사용하여 이미 익숙하지만 나중을 위해 간단하게 사용법을 남겨 놓을까 한다.

 

SQLiteHelper.kt

class SQLiteHelperSample(context: Context) : SQLiteOpenHelper(
    context, DATABASE_NAME, null,
    DATABASE_VERSION
) {

    companion object {
        private const val DATABASE_VERSION = 1
        private const val DATABASE_NAME = "myTestDB.db"
        private const val TBL_NAME = "my_table"

        private const val ID = "ID"
        private const val TITLE = "title"
    }

    override fun onCreate(db: SQLiteDatabase?) {

        val SQL_CREATE_ENTRIES =
            "CREATE TABLE $TBL_NAME (" +
                    "$ID INTEGER PRIMARY KEY," +
                    "$TITLE TEXT)"

        db?.execSQL(SQL_CREATE_ENTRIES)



    }

    fun insert(str: String) {

        val db = this.writableDatabase

        val values = ContentValues().apply{
            put(TITLE,str)
        }

        db.insert(TBL_NAME,null,values)

    }

    fun getAllData(): ArrayList<String> {

        val db = this.writableDatabase

        val query = "SELECT * FROM $TBL_NAME"

        val cursor = db.rawQuery(query,null)

        val arr = ArrayList<String>()

        with(cursor){
            while(moveToNext()){
                arr.add(getString(1))
            }
        }



        return arr
    }

    fun deleteAll(){

        val db = this.writableDatabase

        db?.execSQL("DROP TABLE IF EXISTS $TBL_NAME")
        onCreate(db)


    }



    //DATABASE VERSION
    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {

        db?.execSQL("DROP TABLE IF EXISTS $TBL_NAME")
        onCreate(db)


    }


}

 

MainActivity.kt

class MainActivity : AppCompatActivity() {

    lateinit var db:SQLiteHelperSample

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = SQLiteHelperSample(this)

        val inputArea = findViewById<EditText>(R.id.inputArea)
        val insertBtn = findViewById<Button>(R.id.insert)
        val getAllBtn = findViewById<Button>(R.id.getAll)
        val resultBtn = findViewById<Button>(R.id.resultArea)
        val deleteBtn = findViewById<Button>(R.id.deleteAll)

        insertBtn.setOnClickListener {
            val inputText = inputArea.text.toString()
            db.insert(inputText)
            inputArea.setText("")

        }

        getAllBtn.setOnClickListener {

            val arr = db.getAllData()
            resultBtn.text = arr.toString()
        }
        deleteBtn.setOnClickListener {
            db.deleteAll()
        }


    }

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

 

실행결과: