[안드로이드 SQLite] SQLite Database 간단한 예제

SQLiteOpenHelper를 사용하여 DB 생성

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
 
public class MyDBHelper extends SQLiteOpenHelper {
    // 나중에 데이터베이스를 변경하려면 버전을 증가시키면 됩니다.
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "my.db";
 
    public MyDBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    public void onCreate(SQLiteDatabase db) {
        // 테이블 생성
        db.execSQL("CREATE TABLE mytable (column1 INTEGER, column2 TEXT, column3 REAL, column4 TEXT)");
    }
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 버전이 증가하면 해당 테이블을 삭제하고 다시 생성합니다.
        db.execSQL("DROP TABLE IF EXISTS mytable");
        onCreate(db);
    }
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onUpgrade(db, oldVersion, newVersion);
    }
}

데이터를 담아둘 MyTableList 생성

public class MyTableList {
    public int column1;
    public String column2;
    public double column3;
    public String column4;
 
    public MyTableList(int column1, String column2, double column3, String column4) {
        this.column1 = column1;
        this.column2 = column2;
        this.column3 = column3;
        this.column4 = column4;
    }
}
 

MyDBHelper를 상속받아서 데이터를 처리하는 MyTable 생성

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
 
import java.util.ArrayList;
 
public class MyTable extends MyDBHelper {
    private SQLiteDatabase db;
 
    public MyTable(Context context) {
        super(context);
        // 데이터를 쓰고 읽기 위해서 db 열기
        db = getWritableDatabase();
    }
 
    public void insert(int column1, String column2, double column3, String column4) {
        // 데이터 쓰기
        db.execSQL("INSERT INTO mytable VALUES(" + id + ",'" + column1 + "','" + column2 + "','" + column3 + "','" + column4 + "')");
    }
 
    public void update(int column1, String column2, double column3, String column4) {
        // 조건에 일치하는 행의 데이터 변경
        db.execSQL("UPDATE mytable SET column2='" + column2 + "',column3='" + column3 + "',column4='" + column4 + "' WHERE column1=" + column1);
    }
 
    public void delete(int column1) {
        // 조건에 일치하는 행을 삭제
        db.execSQL("DELETE FROM mytable WHERE column1=" + column1);
    }
 
    public ArrayList<MyTable> select() {
        // 테이블의 모든 데이터 선택
        Cursor mCursor = db.rawQuery("SELECT * FROM mytable", null);
        ArrayList<MyTable> list = new ArrayList<>();
        if(mCursor.moveToFirst()) {
            do {
                list.add(new MyTable(mCursor.getInt(0), mCursor.getString(1), mCursor.getDouble(2), mCursor.getString(3)));
            } while(mCursor.moveToNext());
        }
        mCursor.close();
        return list;
    }
}

액티비티에서 데이터 insert, update, select

import android.os.Bundle;
import android.util.Log;
 
import androidx.appcompat.app.AppCompatActivity;
 
import com.win.database.MyTableList;
 
import java.util.ArrayList;
 
public class Activity extends AppCompatActivity {
 
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
 
        MyTable mMyTable = new MyTable(this);
        mMyTable.insert(1, "column2", 3.0, "column4");
        mMyTable.update(1, "column4", 2.0, "column2");
        ArrayList<MyTableList> list = mMyTable.select();
        for(MyTableList mtl : list)
            Log.d("MyTableList", mtl.column2);
    }
}

---