SQLite(CRUD)

1. activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout
        xmlns:android="http://schemas.android.com/apk/res/android"
        xmlns:tools="http://schemas.android.com/tools"
        xmlns:app="http://schemas.android.com/apk/res-auto"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        tools:context=".MainActivity">
    <EditText
            android:layout_width="510dp"
            android:layout_height="55dp"
            android:inputType="textPersonName"
            android:ems="10"
            android:id="@+id/editInsert"
            app:layout_constraintTop_toTopOf="parent" app:layout_constraintStart_toStartOf="parent"
            app:layout_constraintEnd_toEndOf="parent" app:layout_constraintBottom_toBottomOf="parent"
            app:layout_constraintVertical_bias="0.038"/>
    <EditText
            android:layout_width="510dp"
            android:layout_height="55dp"
            android:inputType="textPersonName"
            android:ems="10"
            android:id="@+id/editDelete"
            app:layout_constraintTop_toTopOf="parent" app:layout_constraintStart_toStartOf="parent"
            app:layout_constraintEnd_toEndOf="parent" app:layout_constraintBottom_toBottomOf="parent"
            app:layout_constraintVertical_bias="0.238"/>
    <Button
            android:text="추가"
            android:layout_width="222dp"
            android:layout_height="63dp" android:id="@+id/insertBtn"
            app:layout_constraintTop_toBottomOf="@+id/editInsert"
            android:layout_marginTop="24dp" app:layout_constraintStart_toStartOf="parent"
            android:layout_marginStart="196dp"/>
    <Button
            android:text="삭제"
            android:layout_width="222dp"
            android:layout_height="63dp" android:id="@+id/deleteBtn"
            app:layout_constraintTop_toBottomOf="@+id/editInsert"
            android:layout_marginTop="220dp" app:layout_constraintStart_toStartOf="parent"
            android:layout_marginStart="188dp"/>
    <Button
            android:text="검색"
            android:layout_width="222dp"
            android:layout_height="63dp" android:id="@+id/selectBtn"
            app:layout_constraintTop_toBottomOf="@+id/editInsert"
            android:layout_marginTop="492dp" app:layout_constraintStart_toStartOf="parent"
            android:layout_marginStart="188dp"/>
    <EditText
            android:layout_width="510dp"
            android:layout_height="55dp"
            android:inputType="textPersonName"
            android:ems="10"
            android:id="@+id/editSelect"
            app:layout_constraintTop_toTopOf="parent" app:layout_constraintStart_toStartOf="parent"
            app:layout_constraintEnd_toEndOf="parent" app:layout_constraintBottom_toBottomOf="parent"
            app:layout_constraintVertical_bias="0.483"/>
    <EditText
            android:layout_width="510dp"
            android:layout_height="55dp"
            android:inputType="textPersonName"
            android:ems="10"
            android:id="@+id/editSelectresult"
            app:layout_constraintTop_toTopOf="parent" app:layout_constraintStart_toStartOf="parent"
            app:layout_constraintEnd_toEndOf="parent" app:layout_constraintBottom_toBottomOf="parent"
            app:layout_constraintVertical_bias="0.566"/>
</androidx.constraintlayout.widget.ConstraintLayout>

2. app - src - main - java - 메인패키지 우클릭 - 새로만들기 - kotlin 클래스/파일 - DBHelper로 생성

DBHelper.kt

class DBHelper(context: Context?, name:String?, factory: SQLiteDatabase.CursorFactory?, version: Int)
    : SQLiteOpenHelper(context, name, factory, version) {

    override fun onCreate(db: SQLiteDatabase?) {
        var sql : String = " CREATE TABLE IF NOT EXISTS MYTABLE( " +
                           "    SEQ INTEGER PRIMARY KEY AUTOINCREMENT, " +
                           "    TXT TEXT) "
        db?.execSQL(sql)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        var sql : String = " DROP TABLE IF EXISTS MYTABLE "
        db?.execSQL(sql)
        onCreate(db)
    }

    fun insert(db: SQLiteDatabase, txt:String){
        var sql = " INSERT INTO MYTABLE(TXT) " +
                "   VALUES('${txt}') "

        db.execSQL(sql)
    }

    fun delete(db: SQLiteDatabase, seq:Int){
        var sql = " DELETE FROM MYTABLE " +
                "   WHERE seq=${seq} "

        db.execSQL(sql)
    }

    fun select(db: SQLiteDatabase, txt:String) : String?{
        var sql = " SELECT * FROM MYTABLE " +
                "   WHERE TXT='${txt}' "
        var result = db.rawQuery(sql, null)

        var str:String? = ""
        while (result.moveToNext()){
            str += result.getString(result.getColumnIndex("SEQ")) + " " +
                    "" + result.getString(result.getColumnIndex("TXT"))
        }
        return str
    }
}

 

3. MainActivity.kt

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

        var dbHelper = DBHelper(this, "mydb.db", null, 1)
        
        // 추가
        val insertBtn = findViewById<Button>(R.id.insertBtn)
        val editInsert = findViewById<EditText>(R.id.editInsert)

        var database = dbHelper.writableDatabase

        insertBtn.setOnClickListener {
            val txt = editInsert.text

            dbHelper.insert(database, txt.toString())
        }
        
        // 삭제
        val deleteBtn = findViewById<Button>(R.id.deleteBtn)
        val editDelete = findViewById<EditText>(R.id.editDelete)

        deleteBtn.setOnClickListener {
            val num = editDelete.text

            dbHelper.delete(database, num.toString().toInt())
        }
        
        // 검색
        val selectBtn = findViewById<Button>(R.id.selectBtn)
        val editSelect = findViewById<EditText>(R.id.editSelect)
        val editSelectresult = findViewById<EditText>(R.id.editSelectresult)

        selectBtn.setOnClickListener {
            val txt = editSelect.text

            val resultTxt = dbHelper!!.select(database, txt.toString())
            editSelectresult.setText(resultTxt)
        }
    }
}