[Android] SQLite

안드로이드 내 데이터 저장 방법 중 하나

 

activity_main.xml

<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"/>

 

 

MainActivity

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

        // dbHelper인스턴스
        var dbHelper = DBHelper(this, "mydb.db", null, 1)

        val insertBtn = findViewById<Button>(R.id.insertBtn)
        val editInsert = findViewById<EditText>(R.id.editInsert)

        // dbHelper를 통해 writableDatabase를 넣어주는 과정
        var database = dbHelper.writableDatabase

        //  inserEditText에 입력한 값을 DB에 넣는 과정
        insertBtn.setOnClickListener {
            val txt = editInsert.text

            dbHelper.insert(database, txt.toString())
        }

        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)
        }

    }
}

 

DBHelper(kotlin class)

                        // 정해져 있는 매개변수!!
class DBHelper(context: Context?, name:String?, factory: SQLiteDatabase.CursorFactory?, version: Int)
    // 상속받는 부분
    : SQLiteOpenHelper(context, name, factory, version) {

             // on이 붙은건 자동 호출!
    override fun onCreate(db: SQLiteDatabase?) {
        // 테이블 작성  // TXT 컬럼명
        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
    }
}

 

 

* DB에 들어갔는지 확인 방법

보기 탭 > 도구 창 > Device File Explorer > data > data > com.example.프로젝트명 > databases > mydb.db 다운!

db연결해서 볼 수 있는 프로그램으로 확인(프로그램: sqlitebrowser)