Android/Kotlin ) SQLlite 사용법.

SQLite 는 안드로이드 에서 제공하는 sql DB입니다.

 

1. SQLiteDBHelper 클래스 생성 하기.

SQLiteOpenHelper 를 상속받아 SQLiteDBHelper 클래스 생성합니다.

SQLiteOpenHelper 에는 sqlite 를 관리하기 위해 api 세트 입니다.

 

 

SQLiteDBHelper.kt

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.util.Log

class SQLiteDBHelper(
    context: Context?,
    dbName: String?,
    version: Int
) : SQLiteOpenHelper(context, dbName, null, version) {
    val TAG = javaClass.simpleName

    //db 테이블 생성
    override fun onCreate(db: SQLiteDatabase) {
        val creatSQL = "CREATE TABLE test ("+
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT NOT NULL)"
        db.execSQL(creatSQL)
        
        db.execSQL("INSERT INTO test('name') values('test1')")
        db.execSQL("INSERT INTO test('name') values('test2')")
        db.execSQL("INSERT INTO test('name') values('test3')")
        db.execSQL("INSERT INTO test('name') values('test4')")
        Log.e(TAG,"oncreate")
    }
    
    //버전 업그레이드 시 호출.
    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        var testSql = "DROP TABLE IF EXISTS test"
        db.execSQL(testSql)
        onCreate(db)
        Log.e(TAG,"onUpgrade "+oldVersion +" "+newVersion)
    }

    //downgrade 는 막혀 있다.
    //https://developer.android.com/reference/androidx/sqlite/db/SupportSQLiteOpenHelper.Callback?hl=ko#onDowngrade(androidx.sqlite.db.SupportSQLiteDatabase,%20int,%20int)
    
    fun getSelect() : String{
        var selectAll = "SELECT * FROM test"
        var db = this.writableDatabase
        var cursor = db.rawQuery(selectAll,null)
        var selectResult =""
        
        while (cursor.moveToNext()){
           selectResult += "\n" + cursor.getString(0)
           selectResult += cursor.getString(1)
        }
        

        return selectResult
    }

 

3. activity_main.xml.

<?xml version="1.0" encoding="utf-8"?>
<layout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools">
   

    <androidx.constraintlayout.widget.ConstraintLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        tools:context=".MainActivity">

        <Button
            android:id="@+id/test_btn"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="DB_SELECT!"
            app:layout_constraintBottom_toBottomOf="parent"
            app:layout_constraintLeft_toLeftOf="parent"
            app:layout_constraintRight_toRightOf="parent"
            app:layout_constraintTop_toTopOf="parent" />

    </androidx.constraintlayout.widget.ConstraintLayout>
</layout>

 

2. MainActivity 클래스 생성.

import androidx.appcompat.app.AppCompatActivity
import android.os.Bundle
import android.util.Log
import android.widget.Button

class MainActivity : AppCompatActivity() {

    val TAG = javaClass.simpleName

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

	//testDB 데이터 베이스가 없을시 testDB 생성. SQLiteDBHelper 에 onCreate() 실행됩니다.
        //SQLiteDBHelper(context,데이터베이스 이름,데이터베이스 버전)
        var sqLiteDBHelper = SQLiteDBHelper(this,"testDB",1)
        sqLiteDBHelper.writableDatabase

	//db 사용후 닫아준다.
        sqLiteDBHelper.close()

        var test_btn = findViewById<Button>(R.id.test_btn)
        test_btn.setOnClickListener {
            Log.e(TAG,sqLiteDBHelper.getSelect())
            sqLiteDBHelper.close()
        }
    }
}

 

 

test_btn 버튼을 클릭하면, 아래와 같은 Logcat 이 나타납니다.

E/MainActivity: 1 test1
    2 test2
    3 test3
    4 test4