안드로이드 SQLite 데이터베이스 CRUD 간단 예제

안드로이드 SQLite 데이터베이스 CRUD 간단 예제 

 

긴 말 필요없이 소스코드로

 

먼저 레이아웃부터 만들겠습니다. 간단히 만들었습니다. 

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout 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"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">

    <EditText
        android:id="@+id/editTextName"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="112dp"
        android:layout_marginTop="60dp"
        android:ems="10"
        android:inputType="textPersonName"

        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent" />

    <EditText
        android:id="@+id/editTextPhone"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="112dp"
        android:layout_marginTop="36dp"
        android:ems="10"
        android:inputType="textPersonName"

        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/editTextName" />

    <EditText
        android:id="@+id/editTextAddress"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="112dp"
        android:layout_marginTop="16dp"
        android:ems="10"
        android:inputType="textPersonName"

        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/editTextPhone" />

    <Button
        android:id="@+id/buttonInsert"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="48dp"
        android:layout_marginTop="48dp"
        android:text="추가하기"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/editTextAddress" />

    <TextView
        android:id="@+id/textView"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="16dp"
        android:layout_marginTop="73dp"
        android:layout_marginEnd="38dp"
        android:text="이름"
        app:layout_constraintEnd_toStartOf="@+id/editTextName"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent" />

    <TextView
        android:id="@+id/textView2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="22dp"
        android:layout_marginTop="71dp"
        android:layout_marginEnd="32dp"
        android:text="전화번호"
        app:layout_constraintEnd_toStartOf="@+id/editTextPhone"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/textView" />

    <TextView
        android:id="@+id/textView3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="22dp"
        android:layout_marginTop="32dp"
        android:layout_marginEnd="32dp"
        android:text="주소"
        app:layout_constraintEnd_toStartOf="@+id/editTextAddress"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/textView2" />

    <Button
        android:id="@+id/buttonView"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="27dp"
        android:layout_marginTop="46dp"
        android:text="보여주기"
        app:layout_constraintStart_toEndOf="@+id/buttonInsert"
        app:layout_constraintTop_toBottomOf="@+id/editTextAddress" />

    <Button
        android:id="@+id/buttonUpdate"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="44dp"
        android:layout_marginTop="11dp"
        android:text="수정하기"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/buttonInsert" />

    <EditText
        android:id="@+id/editTextID"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="29dp"
        android:layout_marginTop="15dp"
        android:ems="10"
        android:inputType="textPersonName"

        app:layout_constraintStart_toEndOf="@+id/buttonUpdate"
        app:layout_constraintTop_toBottomOf="@+id/buttonView" />

    <Button
        android:id="@+id/buttonDelete"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="42dp"
        android:layout_marginTop="29dp"
        android:text="삭제"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/buttonUpdate" />
</androidx.constraintlayout.widget.ConstraintLayout>

 

 

그리고 데이터베이스 클래스를 만들어줍니다.  DatabaseHelper.java 

package com.example.sqlite_ex;

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

import androidx.annotation.Nullable;

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "STUDENT.db"; // 데이터베이스 명
    public static final String TABLE_NAME = "student_table"; // 테이블 명

    // 테이블 항목
    public static final String COL_1 = "ID";
    public static final String COL_2 = "Name";
    public static final String COL_3 = "Phone";
    public static final String COL_4 = "Address";


    public DatabaseHelper(@Nullable Context context) {
        super(context, DATABASE_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table " + TABLE_NAME + "(ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, PHONE TEXT, ADDRESS TEXT)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS "+ TABLE_NAME);
        onCreate(db);

    }

    // 데이터베이스 추가하기 insert

    public boolean insertData(String name, String phone, String address){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL_2,name);
        contentValues.put(COL_3,phone);
        contentValues.put(COL_4,address);
        long result = db.insert(TABLE_NAME, null,contentValues);
        if(result == -1)
            return false;
        else
            return true;

    }

    //데이터베이스 항목 읽어오기 Read
    public Cursor getAllData(){
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor res = db.rawQuery("select * from "+TABLE_NAME,null);
        return  res;
    }

    // 데이터베이스 삭제하기
    public Integer deleteData(String id){
        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete(TABLE_NAME, "ID = ? ",new String[]{id});
    }

    //데이터베이스 수정하기
    public boolean updateData(String id, String name, String phone, String address){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL_1,id);
        contentValues.put(COL_2,name);
        contentValues.put(COL_3,phone);
        contentValues.put(COL_4,address);
        db.update(TABLE_NAME,contentValues,"ID = ?", new String[] { id });
        return true;
    }
}

 

마지막으로 MainActivity.java 소스코드입니다. 

 

package com.example.sqlite_ex;

import androidx.appcompat.app.AlertDialog;
import androidx.appcompat.app.AppCompatActivity;

import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {

    DatabaseHelper myDB;

    EditText editTextName, editTextPhone, editTextAddress, editTextID;
    Button buttonInsert, buttonView, buttonUpdate, buttonDelete;

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

        myDB = new DatabaseHelper(this);

        editTextName = findViewById(R.id.editTextName);
        editTextPhone = findViewById(R.id.editTextPhone);
        editTextAddress = findViewById(R.id.editTextAddress);
        editTextID = findViewById(R.id.editTextID);

        buttonInsert = findViewById(R.id.buttonInsert);
        buttonView = findViewById(R.id.buttonView);
        buttonUpdate = findViewById(R.id.buttonUpdate);
        buttonDelete =findViewById(R.id.buttonDelete);

        AddData();
        viewAll();
        UpdateData();
        DeleteData();



    }

    //데이터베이스 추가하기
    public void AddData(){
        buttonInsert.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                boolean isInserted = myDB.insertData(editTextName.getText().toString(),
                        editTextPhone.getText().toString(),
                        editTextAddress.getText().toString());

                if(isInserted == true)
                    Toast.makeText(MainActivity.this,"데이터추가 성공",Toast.LENGTH_LONG).show();
                else
                    Toast.makeText(MainActivity.this,"데이터추가 실패",Toast.LENGTH_LONG).show();
            }
        });
    }

    // 데이터베이스 읽어오기
    public void viewAll()
    {
        buttonView.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Cursor res = myDB.getAllData();
                if(res.getCount() == 0){
                    ShowMessage("실패","데이터를 찾을 수 없습니다.");
                    return;
                }

                StringBuffer buffer = new StringBuffer();
                while(res.moveToNext()){
                    buffer.append("ID: "+res.getString(0)+"\n");
                    buffer.append("이름: "+res.getString(1)+"\n");
                    buffer.append("전화번호: "+res.getString(2)+"\n");
                    buffer.append("주소: "+res.getString(3)+"\n\n");

                }
                ShowMessage("데이터",buffer.toString());


            }
        });

    }

    //데이터베이스 수정하기
    public void UpdateData(){
        buttonUpdate.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                boolean isUpdated = myDB.updateData(editTextID.getText().toString(),
                        editTextName.getText().toString(),
                        editTextPhone.getText().toString(),
                        editTextAddress.getText().toString());

                if(isUpdated == true)
                    Toast.makeText(MainActivity.this,"데이터 수정 성공",Toast.LENGTH_LONG).show();
                else
                    Toast.makeText(MainActivity.this,"데이터 수정 실패",Toast.LENGTH_LONG).show();



            }
        });
    }

    // 데이터베이스 삭제하기
    public void DeleteData(){
        buttonDelete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Integer deleteRows = myDB.deleteData(editTextID.getText().toString());
                if(deleteRows>0)
                    Toast.makeText(MainActivity.this,"데이터 삭제 성공",Toast.LENGTH_LONG ).show();
                else
                    Toast.makeText(MainActivity.this,"데이터 삭제 실패",Toast.LENGTH_LONG ).show();
            }
        });
    }




    public void ShowMessage(String title, String Message){
        AlertDialog.Builder builder = new AlertDialog.Builder(this);
        builder.setCancelable(true);
        builder.setTitle(title);
        builder.setMessage(Message);
        builder.show();
    }


}

 

이제 제가 안드로이드에서 SQLite CRUD를 구현하는걸 동영상으로 녹화했습니다. 

혼자 중얼중얼 하면서.. ㅋㅋ