---
# System prepended metadata

title: SQLiteOpenHelper

---

# SQLiteOpenHelper
1. Define class
```kotlin=
class MyDBHelper(context: Context) : SQLiteOpenHelper(context, DBName, null, DBVersion) {

    companion object{

        private const val DBVersion = 1

        private const val DBName = "SampleList.db"

        const val TableName = "MySample"
    }

    override fun onCreate(db: SQLiteDatabase?) {

        val sql:String = "CREATE TABLE IF NOT EXISTS " + TableName + "( " +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "TITLE VARCHAR(50), " +
                "CONTENT TEXT" +
                ");"

        db?.execSQL(sql)
    }

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

        val sql = "DROP TABLE $TableName"

        db?.execSQL(sql);
    }
}
```

2. Usage
```kotlin=
lateinit var myDBHelper: MyDBHelper

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

        myDBHelper = MyDBHelper(this)
}

override fun onDestroy() {

        myDBHelper.close()

        super.onDestroy()
    }
    
private fun deleteData(){

        val db: SQLiteDatabase = myDBHelper.writableDatabase

        db.execSQL("delete from ${MyDBHelper.TableName}")
    }

private fun addData(value:String){

        val db: SQLiteDatabase = myDBHelper.writableDatabase
        val values = ContentValues()
        values.put("TITLE", "Title 1")
        values.put("CONTENT", "$value")

        val result = db.insert(MyDBHelper.TableName, null, values)

        Toast.makeText(this,"DB result = $result",Toast.LENGTH_SHORT).show()
    }

private fun getDBData(){

        val db: SQLiteDatabase = myDBHelper.readableDatabase

        var c: Cursor = db.rawQuery("SELECT * FROM ${MyDBHelper.TableName}", null)

        var str = ""

        for(row in 0 until c.count){

            c.moveToPosition(row)

            str += "Row : " + (row+1) + " =>\r\n"

            for(col in 0 until c.columnCount){

                val name = c.getColumnName(col)

                val value = c.getString(col)

                str += "$name : $value \r\n"
            }
        }

        text.text = str

//another way to read
//        if (c.count > 0) {    // 若有資料
//            var str = """
//                總共有 ${c.getCount().toString()}筆資料
//
//                """.trimIndent()
//            str += "-----\n"
//            c.moveToFirst() // 移到第 1 筆資料
//            do {        // 逐筆讀出資料
//                str += """
//                    name:${c.getString(0).toString()}
//
//                    """.trimIndent()
//                str += """
//                    phone:${c.getString(1).toString()}
//
//                    """.trimIndent()
//                str += """
//                    email:${c.getString(2).toString()}
//
//                    """.trimIndent()
//                str += "-----\n"
//            } while (c.moveToNext()) // 有一下筆就繼續迴圈
//            text.text = str
//        }
    }


```

