[안드로이드] 데이터 저장하기 - SQLite Database사용법

 

안드로이드 데이터 저장하기 - SQLite Database사용법

 

 

SQLite는 안드로이드에서 제공하는 데이터베이스입니다. 

구글에서는 데이터베이스 사용시 Room을 권고하고 있지만 일반적 SQL에 익숙한 사람들은 SQLite API를 직접 사용하는 것을 선호할 수 있습니다. 

여기서는 데이터베이스 관리 API가 포함되어 있는 SQLiteOpenHelper 클래스를 사용하여 데이터베이스 사용 예제에 대해 알아보겠습니다. 

 

 

1. MainActivity.java

 

public class MainActivity extends AppCompatActivity {
    private UserDatabaseHelper userDatabaseHelper;
    public static final String TABLE_NAME = "user";
    SQLiteDatabase database;

    Button selectButton;
    Button insertButton;
    Button deleteButton;
    EditText idEdittext;
    EditText passwordEditText;
    TextView textView;

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

        selectButton = findViewById(R.id.select_button);
        insertButton = findViewById(R.id.insert_button);
        deleteButton = findViewById(R.id.delete_button);

        idEdittext = findViewById(R.id.id_editText);
        passwordEditText = findViewById(R.id.password_editText);
        textView = findViewById(R.id.textView);

        userDatabaseHelper = UserDatabaseHelper.getInstance(this);
        database = userDatabaseHelper.getWritableDatabase();

        selectButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                selectData(TABLE_NAME);
            }
        });

        insertButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                String id = idEdittext.getText().toString().trim();
                String password = passwordEditText.getText().toString().trim();
                insertData(id, password);
            }
        });

        deleteButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                String id = idEdittext.getText().toString().trim();
                deleteData(id);
            }
        });
    }

    private void selectData(String tableName) {
        if (database != null) {
            String sql = "SELECT id, password FROM " + tableName;
            Cursor cursor = database.rawQuery(sql, null);

            println("데이터 갯수 : " +  cursor.getCount());

            for (int i = 0; i <cursor.getCount(); i++) {
                cursor.moveToNext();
                String id = cursor.getString(0);
                String password = cursor.getString(1);
                //int number = cursor.getInt(3);
                println("[" + i + "] ID : " + id + ", PASSWORD : " + password);
            }
            cursor.close();
        }
    }

    private void insertData(String id, String password) {
        if (database != null) {
            String sql = "INSERT INTO user(id, password) VALUES(?, ?)";
            Object[] params = {id, password};
            database.execSQL(sql, params);
        }
    }

    private void deleteData(String id) {
        if (database != null) {
            String sql = "DELETE FROM user WHERE id=?";
            Object[] params = {id};
            database.execSQL(sql, params);
        }
    }

    public void println (String data) {
        textView.append(data + "\n");
    }

    @Override
    protected void onDestroy() {
        userDatabaseHelper.close();
        super.onDestroy();
    }
}

 

 

 

2. activity_main.xml

 

<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/select_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:textSize="16dp"
        android:text="데이터 조회" />
    
    <EditText
        android:id="@+id/id_editText"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="ID"
        android:textSize="16dp" />

    <EditText
        android:id="@+id/password_editText"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="PASSWORD"
        android:textSize="16dp" />

    <Button
        android:id="@+id/insert_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:textSize="16dp"
        android:text="데이터 입력" />

    <Button
        android:id="@+id/delete_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:textSize="16dp"
        android:text="데이터 삭제" />

    <TextView
        android:id="@+id/textView"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" />
</LinearLayout>

 

 

 

3.  UserDatabaseHelper.java

 

public class UserDatabaseHelper extends SQLiteOpenHelper {

    //---(Singleton 적용) -------------------------------
    private static UserDatabaseHelper instance;
    public static synchronized UserDatabaseHelper getInstance(Context context) {
        if (instance == null) {
            instance = new UserDatabaseHelper(context, "User", null, 1);
        }
        return instance;
    }
    //---------------------------------------------------


    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "User.db";
    public static final String TABLE_NAME = "user";
    public static final String COLUMN_ID = "id";
    public static final String COLUMN_PASSWORD = "password";

    public static final String SQL_CREATE_USER =
            "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" +
                    COLUMN_ID + " TEXT, " +
                    COLUMN_PASSWORD + " TEXT" +
                    ");";
        
    private UserDatabaseHelper(@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(SQL_CREATE_USER);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // DB 버전에 따른 변경사항 적용 - 예제에서는 그냥 DB Table삭제후 재생성함
        if (newVersion > 1) {
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
            db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME);
        }
    }
}