SQLite을 이용한 custom database 사용하기 -1 ( Android)

1> SQLiteOpenHelper를 상속한  MyOwnDbHelper.kt 파일을 만들어 줍니다.

 

 

class MyOwnDbHelper(context: Context) : 
       SQLiteOpenHelper(context, DATABASE_NAME,null,DATABASE_VERSION) {
    companion object{
        val DATABASE_NAME = "MyOwnDb.db"
        val DATABASE_VERSION =1
    }

    override fun onCreate(p0: SQLiteDatabase?) {
        TODO("Not yet implemented")
    }

    override fun onUpgrade(p0: SQLiteDatabase?, p1: Int, p2: Int) {
        TODO("Not yet implemented")
    }

} 

>> 위의 내용은  override 함수 onCreate(), onUpgrage()를 만들기 전 상태입니다.

 

일단 SQLite 테이블 만드는 문법은 아래와 같습니다.

CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

 

 

 <예제> 

 https://www.tutorialspoint.com/sqlite/sqlite_create_table.htm

 

sqlite> CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

sqlite> CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

 

테이블 삭제 문법은 아래와 같습니다.

DROP TABLE database_name.table_name;

 

 

< 예제>

https://www.tutorialspoint.com/sqlite/sqlite_drop_table.htm

SQLite - DROP Table - Tutorialspoint

sqlite>.tables
COMPANY       test.COMPANY
sqlite>DROP TABLE COMPANY;

 

Select query 문법

 

SELECT column1, column2, columnN FROM table_name;

 

< 예제>

 

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0

sqlite> SELECT ID, NAME, SALARY FROM COMPANY;

 

==> 결과 값

ID          NAME        SALARY
----------  ----------  ----------
1           Paul        20000.0
2           Allen       15000.0
3           Teddy       20000.0

 

 

MyOwnDbHelper.kt 는 다음과 같습니다.

package com.xxxx.mykotlin

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

    private val TAG = MyOwnDbHelper::class.java.simpleName
    private val SQL_CREATE_ENTRIES =
        "CREATE TABLE ${MyContract.MyEntry.TABLE_NAME} (" +
                "${BaseColumns._ID} INTEGER PRIMARY KEY," +
                "${MyContract.MyEntry.COLUMN_NAME_TITLE} TEXT," +
                "${MyContract.MyEntry.COLUMN_NAME_SCORE} INT)"
    private val SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS ${MyContract.MyEntry.TABLE_NAME}"

    object MyContract {
        object MyEntry : BaseColumns {
            const val TABLE_NAME = "english"
            const val COLUMN_NAME_TITLE = "name"
            const val COLUMN_NAME_SCORE = "score"
        }
    }
    companion object{
        val DATABASE_NAME = "MyClassEnglishDb.db"
        val DATABASE_VERSION =1
    }

    override fun onCreate(p0: SQLiteDatabase?) {
        // CREATE TABLE tells the system to create a new database table keyword.
        Log.d(TAG,"onCreate() +")
        p0?.execSQL(SQL_CREATE_ENTRIES)
    }

    override fun onUpgrade(p0: SQLiteDatabase?, p1: Int, p2: Int) {
        // This database is only a cache for online data, so its upgrade policy is
        // to simply to discard the data and start over
        Log.d(TAG,"onUpgrade() +")
        p0?.execSQL(SQL_DELETE_ENTRIES)
        onCreate(p0)
    }

}

 

 

MainActivity.kt 는 다음과 같습니다.

 

package com.xxxx.mykotlin

class MainActivity : AppCompatActivity() {
    val TAG = MainActivity::class.java.simpleName
    private lateinit var myEngScoreDatabase : MyOwnDbHelper
    private lateinit var readEngDb: SQLiteDatabase
    private lateinit var writeEngDb: SQLiteDatabase
    private lateinit var editName  : EditText
    private lateinit var editScore : EditText

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        myEngScoreDatabase = MyOwnDbHelper(this)
        readEngDb = myEngScoreDatabase.readableDatabase
        writeEngDb = myEngScoreDatabase.writableDatabase
        editName = findViewById<EditText>(R.id.edit_name)
        editScore = findViewById<EditText>(R.id.edit_score)


        val btnReadDb = findViewById<Button>(R.id.read_db)
        val btnWriteDb = findViewById<Button>(R.id.write_db)

        btnReadDb.setOnClickListener {
            Log.d(TAG,"read database")
            var strName =  editName.text
            val projection = arrayOf(BaseColumns._ID,
                MyOwnDbHelper.MyContract.MyEntry.COLUMN_NAME_TITLE,
                MyOwnDbHelper.MyContract.MyEntry.COLUMN_NAME_SCORE )

            val selection = "${MyOwnDbHelper.MyContract.MyEntry.COLUMN_NAME_TITLE} = ?"

            val selectionArgs = arrayOf("${strName.toString()}")
            val cursor = readEngDb.query(
                MyOwnDbHelper.MyContract.MyEntry.TABLE_NAME,   // The table to query
                projection,             // The array of columns to return (pass null to get all)
                selection,              // The columns for the WHERE clause
                selectionArgs,          // The values for the WHERE clause
                null,                   // don't group the rows
                null,                   // don't filter by row groups
                null               // The sort order
            )

            with(cursor) {
                while (moveToNext()) {
                    val score = getInt(getColumnIndexOrThrow(MyOwnDbHelper.MyContract.MyEntry.COLUMN_NAME_SCORE))
                    Log.d(TAG,"score = $score")
                    editScore.setText(score.toString())
                }
            }
        }

        btnWriteDb.setOnClickListener {
            Log.d(TAG,"write database")
            var strName =  editName.text
            val strScore = editScore.text

            // Create a new map of values , where column names are the keys
            val values = ContentValues().apply{
                put(MyOwnDbHelper.MyContract.MyEntry.COLUMN_NAME_TITLE, strName.toString())
                put(MyOwnDbHelper.MyContract.MyEntry.COLUMN_NAME_SCORE, strScore.toString().toInt())
            }

            // Insert the new row,  returning the primary key value of the new row
            val newRowId = writeEngDb.insert(MyOwnDbHelper.MyContract.MyEntry.TABLE_NAME, null,values)
            Log.d(TAG,"id = $newRowId")
        }

    }

    override fun onDestroy() {
        myEngScoreDatabase.close()
        super.onDestroy()

    }

}

 

<핵심 포인트 : 이것이라도 알고 가자.>

 1> database read/write 시 별도로  SQLiteDatabase 객체를 생성해야합니다.

 readEngDb = myEngScoreDatabase.readableDatabase
 writeEngDb = myEngScoreDatabase.writableDatabase

 

2> WRITE 시는 insert() 함수를 사용합니다. 인자로  ContentValues가 사용된다는것

 // Create a new map of values , where column names are the keys
 val values = ContentValues().apply{
     put(COLUMN_NAME_TITLE, strName.toString())
     put(COLUMN_NAME_SCORE, strScore.toString().toInt())
 }
 val newRowId = writeEngDb.insert(TABLE_NAME, null,values)

 

3> READ 시는 query() 함수를 사용하고 리턴되는 Cursor 오브젝트를 분석해야합니다.

 val projection = arrayOf(BaseColumns._ID, COLUMN_NAME_TITLE, COLUMN_NAME_SCORE )
 val selection = "${COLUMN_NAME_TITLE} = ?"
 val selectionArgs = arrayOf("${strName.toString()}")
 val cursor = readEngDb.query( TABLE_NAME,   // The table to query
    ..... 중략
 )

 with(cursor) {
     while (moveToNext()) {
         val score = getInt(getColumnIndexOrThrow(MyOwnDbHelper.MyContract.MyEntry.COLUMN_NAME_SCORE))
         editScore.setText(score.toString())
     }
 }

 

 

결과 화면은