Android Studio - SQLite 데이터베이스

 

2022-08-24

---

💡 SQLite 데이터베이스

📄SQLite CRUD 예제

---

📝activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".MainActivity">

    <Button
        android:id="@+id/btn_add"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="추가"
        android:textSize="20sp" />

    <Button
        android:id="@+id/btn_read"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="읽기"
        android:textSize="20sp" />

    <Button
        android:id="@+id/btn_update"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="수정"
        android:textSize="20sp" />

    <Button
        android:id="@+id/btn_del"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="삭제"
        android:textSize="20sp" />

    <TextView
        android:id="@+id/tv_data"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:background="#9999ff"
        android:gravity="center_horizontal"
        android:text="TextView"
        android:textSize="30sp" />

</LinearLayout>

---

📝MainActivity.java

◾ 읽기 - read()

🔸void read() {}
rawQuery : sql문을 실행한다.
데이터베이스로부터 rawQuery를 호출하게되면 데이터를읽어서 2차원 배열(컬럼, 레코드)로 저장할 수있다.

Cursor객체를 통해 데이터에 접근할 수 있다. 

mCursor.getString(1);
컬럼의 인덱스가 1인 String데이터를 가져온다.

package com.example.aug0824;

import androidx.appcompat.app.AppCompatActivity;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.TextView;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {
    MySQLHelper helper;
    SQLiteDatabase mdb;
    Cursor mCursor;
    TextView tv;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        helper = new MySQLHelper(this, "mydb", null, 1);
        mdb = helper.getWritableDatabase();

        tv = findViewById(R.id.tv_data);

        findViewById(R.id.btn_add).setOnClickListener(lis);
        findViewById(R.id.btn_read).setOnClickListener(lis);
        findViewById(R.id.btn_update).setOnClickListener(lis);
        findViewById(R.id.btn_del).setOnClickListener(lis);

    }

    View.OnClickListener lis = new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            int id = v.getId();
            switch(id) {
                case R.id.btn_add:
                    insert();
                    break;
                case R.id.btn_read:
                    read();
                    break;
                case R.id.btn_update:
                    update();
                    break;
                case R.id.btn_del:
                    del();
                    break;
            }
        }
    };
// 추가
    void insert() {
        mdb.execSQL("INSERT INTO world VALUES(null, 'korea', 'seoul')");
    }
// 읽기
    void read() {
        mCursor = mdb.rawQuery("SELECT * FROM world", null);
        String str = "";
        String strCon, strCap;
        while(mCursor.moveToNext()) {
            strCon = mCursor.getString(1);
            strCap = mCursor.getString(2);
            str += strCon + ", " + strCap + "\n";
        }

        if(str.length() > 0) {
            tv.setText(str);
        }
        else {
            tv.setText("데이터 없음");
        }
    }
// 수정
   void update() {
       // null값이면 밑의 문장을 수행하지 않도록 한다.
       if(mCursor == null) {return;}

       // 첫 번째 레코드로 커서 위치 변경
       mCursor.moveToFirst();

       // 커서가 가리키는 레코드의 첫 번째 컬럼인 _id 컬럼 값 얻기
       int id = mCursor.getInt(0);

       mdb.execSQL("UPDATE world SET capital='aaa' WHERE _id=" + id + ";");
   }
// 삭제
    void del() {
        // null값이면 밑의 문장을 수행하지 않도록 한다.
        if(mCursor == null) {return;}

        // 첫 번째 레코드로 커서 위치 변경
        mCursor.moveToFirst();

        // 커서가 가리키는 레코드의 첫 번째 컬럼인 _id 컬럼 값 얻기
        int id = mCursor.getInt(0);

        // world 테이블에서 _id 값이 변수 id 값인 레코드를 삭제
        mdb.execSQL("DELETE FROM world WHERE _id=" + id + ";");
    }
}

---

📝MySQLHelper.java

package com.example.listview;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

public class MySQLHelper extends SQLiteOpenHelper {
    public MySQLHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE world (_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "country TEXT, capital TEXT);");

        db.execSQL("INSERT INTO world VALUES (null, '한국', '서울');");
        db.execSQL("INSERT INTO world VALUES (null, '중국', '베이징');");
        db.execSQL("INSERT INTO world VALUES (null, '일본', '도쿄');");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

---

📄SQLite 응용

---

📝layout.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <Button
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/btn_read"
            android:layout_weight="1"
            android:text="read"/>
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <EditText
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/et_con"
            android:layout_weight="1"
            android:hint="나라"/>

        <EditText
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/et_cap"
            android:layout_weight="1"
            android:hint="수도"/>

        <Button
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/btn_insert"
            android:layout_weight="1"
            android:text="add"/>
    </LinearLayout>



    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">
        <EditText
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:inputType="number"
            android:layout_weight="1"
            android:id="@+id/et_update"/>
        <Button
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/btn_update"
            android:layout_weight="1"
            android:text="update"/>
    </LinearLayout>
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">
        <EditText
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:inputType="number"
            android:id="@+id/et_delete"/>
        <Button
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/btn_delete"
            android:layout_weight="1"
            android:text="delete"/>
    </LinearLayout>

    <TextView
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:gravity="center_horizontal"
        android:textSize="25sp"
        android:id="@+id/tv_data"/>

</LinearLayout>

---

📝MySQLHelper.java

package com.example.sqlite;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

public class MySQLHelper extends SQLiteOpenHelper {
    public MySQLHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE world (_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "country TEXT, capital TEXT);");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

---

📝MainActivity.java

package com.example.sqlite;

import androidx.appcompat.app.AppCompatActivity;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.text.Editable;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;

public class MainActivity extends AppCompatActivity {
    MySQLHelper helper;
    SQLiteDatabase mdb;
    Cursor mCursor;
    TextView tv;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.layout);

        helper = new MySQLHelper(this, "mydb", null, 1);
        mdb = helper.getWritableDatabase();
        tv = findViewById(R.id.tv_data);

        findViewById(R.id.btn_insert).setOnClickListener(lis);
        findViewById(R.id.btn_read).setOnClickListener(lis);
        findViewById(R.id.btn_update).setOnClickListener(lis);
        findViewById(R.id.btn_delete).setOnClickListener(lis);
    }

    View.OnClickListener lis = new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            int id = v.getId();

            switch (id) {
                case R.id.btn_insert:
                    insert();
                    break;
                case R.id.btn_read:
                    read();
                    break;
                case R.id.btn_update:
                    update();
                    break;
                case R.id.btn_delete:
                    delete();
                    break;
            }
        }
    };

    // ADD
    void insert() {
        EditText et_con = findViewById(R.id.et_con);
        EditText et_cap = findViewById(R.id.et_cap);

        String str_con = et_con.getText().toString();
        String str_cap = et_cap.getText().toString();

        mdb.execSQL( "INSERT INTO world VALUES(null,'" + str_con + "', '" + str_cap + "');" );
    }

    // READ
    void read() {
        mCursor = mdb.rawQuery("SELECT * FROM world", null);

        String str = "";
        String strCon, strCap;

        while(mCursor.moveToNext()) {
            strCon = mCursor.getString(1);
            strCap = mCursor.getString(2);
            str += strCon + ", " + strCap + "\n";
        }

        if(str.length()> 0) {
            tv.setText(str);
        }
        else {
            tv.setText("데이터 없음");
        }
    }

    // UPDATE
    void update() {
        if(mCursor == null) {return;}

        // 사용자가 입력한 나라와 수도 명
        String strCon = ((EditText)findViewById(R.id.et_con)).getText().toString();
        String strCap = ((EditText)findViewById(R.id.et_cap)).getText().toString();

        if(strCon.length() <= 0) strCon = "aaa"; // 입력값 없을 때 aaa로 기본 값
        if(strCap.length() <= 0) strCap = "bbb"; // 입력값 없을 때 bbb로 기본 값

        // 사용자가 입력한 위치로 커서 이동
        String str = ((EditText)findViewById(R.id.et_update)).getText().toString();
        int index = Integer.valueOf(str);
        mCursor.moveToPosition(index);

        // 이동한 커서 위치의 _id 절에서 나라와 수도명 변경
        int id = mCursor.getInt(0);
        mdb.execSQL("UPDATE world SET capital='" + strCap + "', country='" +
                strCon + "' WHERE _id=" + id + ";");
        ((EditText)findViewById(R.id.et_update)).setText("");
    }

    // DELETE
    void delete() {
        if(mCursor == null) {return;}

        EditText et_delete = findViewById(R.id.et_delete);
        int index = Integer.parseInt(et_delete.getText().toString());

        mCursor.moveToPosition(index);
        int id = mCursor.getInt(0);

        mdb.execSQL("DELETE FROM world WHERE _id=" + id + ";");
    }
}

---