[Android] SQLite 사용하기 - 데이터베이스와 테이블 생성, 삽입, 조회

안드로이드 단말기에서 데이터를 저장할 수 있는 여러가지 방법이 있다. 그 중에서도 데이터베이스는 많은 양의 데이터를 체계적으로 관리하기 위해 사용한다. 이 글에선 안드로이드에서 기본적으로 지원하는 데이터베이스인 SQLite에 대해 정리하고자 한다.

 

이 글에서 SQL문 작성법에 대한 설명은 생략한다.

SQLite란?

SQLite는 안드로이드에서 사용할 수 있는 경량급(Light-weight) 관계형 데이터베이스로, 표준 SQL문을 이용해 데이터를 조회할 수 있다.

임베디드 데이터베이스로 만들어진 SQLite는 파일 기반으로 동작 하면서도 데이터베이스의 기능을 그대로 사용할 수 있다. 안드로이드 운영체제에 자체적으로 탑재되어있기 때문에 MySQL, Oracle 등 서버단에서 주로 사용되는 관계형 데이터베이스보다 가볍다.

1. 데이터베이스 생성

데이터베이스는 여러 개의 테이블을 가지고 있는 저장소같은 역할을 하며, 하나의 파일로 만들어진다.
처음에 한 번 create 해두면 그 뒤로는 만들어진 데이터베이스를 open해서 사용한다. 이 두개의 메서드는 Context 클래스를 상속받으므로 액티비티 클래스 안에서 데이터베이스를 만들거나 열 수 있다.

• openOrCreateDatabase(String name, int mode, SQLiteDatabase.CursorFactory factory) : 데이터베이스를 생성하거나 만들어진 데이터베이스를 연다.

  SQLiteDatabase db = openOrCreateDatabase(
    "database", // 데이터베이스의 이름
    MODE_PRIVATE, // 다른 앱에서의 접근 가능 범위
    null // 쿼리 결과로 리턴되는 커서를 만들 객체
  );

• deleteDatabase(String name) : 전달받은 이름의 데이터베이스를 삭제한다.

2. 테이블 생성

• execSQL(String sql) : 테이블을 새로 만드는 등 표준 SQL을 이용하기 위해 사용한다.

테이블을 생성할 땐 execSQL에 테이블을 생성하는 쿼리문을 문자열로 전달하면 된다.

 

예제 코드

다음은 액티비티 안에서 데이터베이스 생성, 테이블 생성, 레코드 삽입을 처리하는 간단한 코드이다.
생성할 테이블의 형태는 다음과 같다.

column name	type
_id	INTEGER
name	TEXT
age	INTEGER

class MainActivity extends AppCompatActivity {
    SQLiteDatabase db;
    static final String DB_NAME = "database";
    static final String TABLE_NAME = "people";
    ...

    void createDatabase() {
        db = openOrCreateDatabase(
                DB_NAME, // 데이터베이스의 이름
                MODE_PRIVATE, // 다른 앱에서의 접근 가능 범위
                null // 쿼리 결과로 리턴되는 커서를 만들 객체
        );
    }

    void createTable() {
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "(" +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT," +
                "age INTEGER);");
    }

    void insertRecode(String name, int age) {
        db.execSQL("INSERT INTO " + TABLE_NAME +
                " (name, age) VALUES (" +
                "'" + name + "', "
                + age + ");");
    }
    ...

   }

3. Contract Class 사용하기

SQLite를 사용할 때 데이터베이스 이름, 테이블 이름, 열 이름 등 동일한 문자열을 반복해서 써야하는 경우가 잦다.
이 모든 명칭을 하드코딩 하게된다면 코드의 양이 늘어날수록 수정 및 오타 발견에 있어 불편함이 커질 것이다.
Contact Class는 데이터베이스에 필요한 각종 상수를 한번에 모아서 관리하는 역할을 한다.
변경사항이 생기더라도 Contact Class에서 한 번만 변경을 하면 되므로 수정 작업이 용이해진다.

위에서 생성한 people 테이블에 대한 Contact Class의 예시이다.

public final class PeopleContract {
    private PeopleContract() {
    }

    // 하나의 테이블에 필요한 내용을 하나의 클래스에 정의한다.
    public static class PeopleEntry implements BaseColumns {
        public static final String TABLE_NAME = "people";
        public static final String COLUMN_NAME = "name";
        public static final String COLUMN_AGE = "age";
        public static final String SQL_CREATE_TABLE =
                "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" +
                        _ID + " INTEGER PRIMARY KEY," +
                        COLUMN_NAME + " TEXT," +
                        COLUMN_AGE + " INTEGER)";
        public static final String SQL_DELETE_TABLE =
                "DROP TABLE IF EXISTS " + TABLE_NAME;
    }
}

Contract Class의 인스턴스화를 방지하기 위해 생성자의 접근 지정자를 private로 지정했다.

여기서 하나의 inner 클래스는 하나의 테이블에 매치된다.
BaseColumns를 상속받는 것은 선택인데, 만약 상속한다면 내부적으로 _ID 열과 레코드 개수를 저장하는 _COUNT가 추가된다.

4. Helper 클래스로 데이터베이스 생성하기

스키마나 테이블의 구조가 변경될 경우 openOrCreateDatabase()를 사용하는 방식은 위험하고 비효율적일 수 있다.

따라서 데이터베이스를 생성할 땐 SQLiteDatabase보다 SQLiteOpenHelper를 사용하는 것이 권장된다.

SQLiteOpenHelper를 상속받은 helper 클래스는 데이터베이스를 만들거나 여는 역할을 한다.

public SQLiteOpenHelper (Context context, String name, SQLiteDatabase.CursorFactory factory, int version)

openOrCreateDatabase와 달리 version이라는 매개변수가 존재한다.
데이터베이스가 변경되었을 때 버전 정보를 다르게 지정해 데이터베이스의 구조를 변경할 수 있다.

다음은 helper 클래스에서 데이터베이스 파일을 만들기 위한 메서드들이다.

• getReadableDatabase() : 읽기 전용 데이터베이스를 생성하거나 연다.
• getWritableDatabase() : 쓰기 전용 데이터베이스를 생성하거나 연다.

콜백 메서드를 재정의 해두면 데이터베이스의 생성과 수정 등 변경이 발생했을 때 상태에 따른 처리를 할 수 있다.

• onCreate() : 데이터베이스를 생성했을 때
• onOpen() : 생성된 데이터베이스가 열렸을 때
• onUpgrade() : 데이터베이스를 수정했을 때

 

Helper 클래스 생성

public class PeopleDBHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "database";
    public static final int DATABASE_VERSION = 1;

    public PeopleDBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(PeopleEntry.SQL_CREATE_TABLE); // 테이블 생성
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        // 단순히 데이터를 삭제하고 다시 시작하는 정책이 적용될 경우
        sqLiteDatabase.execSQL(PeopleEntry.SQL_DELETE_TABLE);
        onCreate(sqLiteDatabase);
    }
    ...
}

레코드 삽입

insert() 메서드에 ContentValues 객체를 넘기면 레코드의 삽입 작업이 처리된다.
people 테이블에 새로운 데이터를 추가하는 코드이다.

public void insertRecord(String name, int age) {
    // 읽기 전용 DB를 가져온다
    SQLiteDatabase db = getReadableDatabase();

    // column name을 key로 테이블에 삽입할 값의 집합을 생성한다.
    ContentValues values = new ContentValues();
    values.put(PeopleEntry.COLUMN_NAME, name);
    values.put(PeopleEntry.COLUMN_AGE, age);

    db.insert(PeopleEntry.TABLE_NAME, null, values);
}

테이블 조회

query() 메서드로 조회한 데이터를 읽기 위해 Cursor 객체를 이용한다.

Coursor를 사용하면 조회된 레코드를 하나씩 참조하면서 데이터를 꺼내볼 수 있디.

• moveToNext() : Cursor를 다음 row로 이동시킨다.
• moveToPrevious() : Cursor를 이전 row로 이동시킨다.
• moveToPosition(int i) : Cursor를 특정 index로 이동시킨다.

public Cursor readRecordOrderByAge() {
    SQLiteDatabase db = getReadableDatabase();
    String[] projection = {
            BaseColumns._ID,
            PeopleEntry.COLUMN_NAME,
            PeopleEntry.COLUMN_AGE
        };

    String sortOrder = PeopleEntry.COLUMN_AGE + " DESC";

    Cursor cursor = db.query(
            PeopleEntry.TABLE_NAME,
            projection,   // 값을 가져올 column name의 배열
            null,   // where 문에 필요한 column
            null,   // where 문에 필요한 value
            null,   // group by를 적용할 column
            null,   // having 절
            sortOrder   // 정렬 방식
    );

    return cursor;
}

 

전체 코드

MainActivity에서 데이터를 추가하고 테이블의 내용을 출력하는 간단한 예제이다.

1. PeopleContract.java

   public final class PeopleContract {
    private PeopleContract() {
    }
   
    public static class PeopleEntry implements BaseColumns {
        public static final String TABLE_NAME = "people";
        public static final String COLUMN_NAME = "name";
        public static final String COLUMN_AGE = "age";
        public static final String SQL_CREATE_TABLE =
                "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" +
                        _ID + " INTEGER PRIMARY KEY," +
                        COLUMN_NAME + " TEXT," +
                        COLUMN_AGE + " INTEGER)";
        public static final String SQL_DELETE_TABLE =
                "DROP TABLE IF EXISTS " + TABLE_NAME;
    }
   }

2. PeopleDBHelper.java

   public class PeopleDBHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "database";
    public static final int DATABASE_VERSION = 1;
   
    public PeopleDBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
   
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(PeopleEntry.SQL_CREATE_TABLE); // 테이블 생성
    }
   
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        // 단순히 데이터를 삭제하고 다시 시작하는 정책이 적용될 경우
        sqLiteDatabase.execSQL(PeopleEntry.SQL_DELETE_TABLE);
        onCreate(sqLiteDatabase);
    }
   
    void insertRecord(String name, int age) {
        SQLiteDatabase db = getReadableDatabase();
   
        ContentValues values = new ContentValues();
        values.put(PeopleEntry.COLUMN_NAME, name);
        values.put(PeopleEntry.COLUMN_AGE, age);
   
        db.insert(PeopleEntry.TABLE_NAME, null, values);
    }
   
    public Cursor readRecordOrderByAge() {
        SQLiteDatabase db = getReadableDatabase();
        String[] projection = {
                BaseColumns._ID,
                PeopleEntry.COLUMN_NAME,
                PeopleEntry.COLUMN_AGE
        };
   
        String sortOrder = PeopleEntry.COLUMN_AGE + " DESC";
   
        Cursor cursor = db.query(
                PeopleEntry.TABLE_NAME,   // The table to query
                projection,   // The array of columns to return (pass null to get all)
                null,   // where 문에 필요한 column
                null,   // where 문에 필요한 value
                null,   // group by를 적용할 column
                null,   // having 절
                sortOrder   // 정렬 방식
        );
   
        return cursor;
    }
   }
   

3. MainActivity.java

   class MainActivity extends AppCompatActivity {
    private PeopleDBHelper dbHelper;
    private TextView textView;
   
    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
   
        textView = findViewById(R.id.textView);
   
        dbHelper = new PeopleDBHelper(this);
        dbHelper.insertRecord("갑", 33);
        dbHelper.insertRecord("을", 21);
        dbHelper.insertRecord("병", 44);
        dbHelper.insertRecord("정", 17);
   
        printTable();
    }
   
    private void printTable() {
        Cursor cursor = dbHelper.readRecordOrderByAge();
        String result = "";
   
        result += "row 개수 : " + cursor.getCount() + "\n";
        while (cursor.moveToNext()) {
            int itemId = cursor.getInt(cursor.getColumnIndexOrThrow(PeopleEntry._ID));
            String name = cursor.getString(cursor.getColumnIndexOrThrow(PeopleEntry.COLUMN_NAME));
            int age = cursor.getInt(cursor.getColumnIndexOrThrow(PeopleEntry.COLUMN_AGE));
   
            result += itemId + " " + name + " " + age + "\n";
        }
   
        textView.setText(result);
        cursor.close();
    }
   
    @Override
    protected void onDestroy() {
        dbHelper.close();
        super.onDestroy();
    }
   }

 

추가 정보

• SQLite는 강력한 기능을 제공하지만, low-level의 API이기에 오류가 발생하기 쉽고 많은 양의 보일러 플레이트가 생겨난다.
• 따라서 안드로이드 공식 문서에서는 SQLite를 추상화한 라이브러리 Room의 사용을 강력하게 권장하고 있다.

 

참고

• SQLite 공식 문서