[안드로이드] 안드로이드에서 SQLite를 다뤄보자 (1)

안드로이드에서 SQLite를 다뤄보자 (1)

안드로이드 플랫폼은 Database를 위해서 플랫폼 자체적으로 SQLite를 기본 탑재하고 있다.

우선 SQLite의 특징을 살펴보자.

• SQLite는 다른 프로그램에 임베딩하기 좋으면서도 깔끔한 SQL 인터페이스를 제공한다.
• 메모리도 적게 사용하면서 속도도 빠르다.
• 실행파일과 소스 코드가 무료이고 공개되어 있기 때문에 많이 사용된다.
• 위와 같은 이유로, 안드로이드는 SQLite를 기본탑재하여 내장하고 있으며, 그결과 모든 안드로이드 애플리케이션은 간단하게 SQLite 데이터베이스를 생성해 활용할수 있게 됐다.
• SQLite는 표준 SQL 인터페이스를 사용한다. -> SQLite는 SQL 문법에 맞는 명령을 통해 데이터를 가져오거나(SELECT) 데이터를 변경하고(INSERT 등) 데이터 구조를 정의하는(CREATE TABLE 등) 작업을 처리한다.
• SQLite가 JDBC를 기본 API로 제공하지 않으며, 휴대폰과 같은 환경에서 JDBC와 같은 규모 있는 프레임워크는 무리가 되기 때문이다.

참고로 안드로이드의 액티비티는 일반적으로 컨텐트 프로바이더(Content Provider)나 서비스(Service) 등을 통해 데이터베이스에 접근한다.

또한 SQLite의 재미있는 특징은 실제 데이터를 추가할 때 '컬럼마다 데이터 타입에 상관없이 어떤 데이터라도 마음대로 넣을수 있다'는 것이다. 예를 들어 INTEGER로 정의된 컬럼에 문자열 값도 아무런 문제없이 넣을수 있다. 이와 같은 기능을 매니페스트 타입이라고 표현한다.

 매니페스트 타입 입장에서 보면 데이터 타입은 컬럼 자체가 아닌 개별값에 연결되는 속성이다. 따라서 SQLite는 애초에 해당 컬럼에 지정된 데이터 타입과 상관없이 어떤 데이터 타입의 어떤 값이라도 아무 컬럼에나 집어 넣을수 있는 컨셉이다.

그런데 표준 SQL 구문에 정의된 기능 중에 SQLite가 지원하지 않는 기능이 몇가지 있다. 기억해두자. 

FOREIGN KEY, 중첩 트랜잭션, RIGHT OUTER JOIN, FULL OUTER JOIN, ALTER TABLE 은 SQLite가 지원하지 않는다

코드에 적용하기 위한 기초

데이터베이스를 생성하고 오픈하려면 SQLiteOpenHelper 객체를 사용한다. SQLiteOpenHelper 클래스는 애플리케이션에서 요구하는 내용에 따라 데이터베이스를 생성하거나 업그레이드하는 기능을 제공한다. SQLiteOpenHelper 클래스를 상속받아 구현하려면 다음과 같은 세가지 기능을 준비해야한다

1. 생성 메소드 : 상위 클래스의 생성 메소드를 호출, Activity 등의 Context 인스턴스와 데이터베이스의 이름, 커서 팩토리(보통 Null 지정) 등을 지정하고, 데이터베이스 스키마 버전을 알려주는 숫자값을 넘겨 준다.
2. onCreate() 메소드 : SQLiteDatabase를 넘겨 받으며, 데이블을 생성하고 초기 데이터를 추가하기에 적당한 위치이다.
3. onUpgrade() 메소드 : SQLiteDatabase 인스턴스를 넘겨 받으며, 현재 스키마 버전과 최신 스키마 버전 번호도 받는다.

 SQLiteOpenHelper를 상속받은 클래스를 사용하려면 먼저 인스턴스를 하나 생성한 다음, 하려는 작업이 읽기 전용인지 여부에 따라 getReadableDatabase()나 getWritealbleDatabese() 메소드를 호출 해야 한다.

아래 예제코드를 살펴보길 바란다.

db = (new DatabaseHelper(getContext())).getWritableDatabase();
return (db == null) ? false : ture;

결과적으로 db 변수에 SQLiteDatabase 인스턴스를 받아오게 되는데, SQLiteDatabase 인스턴스를 사용해 데이터를 호출하거나 내용을 변경 할수 있다. 그리고 액티비티가 종료되는 등 데이터베이스를 모두 사용하고 나면 SQLiteDatabase인스턴스의 close() 메소드를 호출해 연결을 해제하면 된다.

DB테이블 준비

본격적으로 SQLite로 DB를 다루기 위해서는 DB테이블 준비해야 한다. 

테이블과 색인 등을 생성하려면 생성하길 원하는 DB Scheme에 대한 DDL 구문을 준비해서 SQLiteDatabase 인스턴스의 execSQL() 메소드의 인자로 넣어 호출하면 된다.

다음 예제코드를 살펴보자.

db.execSQL("CREATE TABLE constants (_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, value REAL);");

위 예제코드의 의미는 아래와 같다. 

1. constants라는 테이블이 생성되고, constants 테이블 기본키로 _id 컬럼이 사용되며 _id 컬럼은 정수형의 숫자값이 자동으로 증가되는 컬럼이다.
2. 실제 데이터는 title이라는 문자열 컬럼과 value라는 실수형 컬럼에 들어간다.

안드로이드의 SQLite는 키본키(Primary key)에 해당하는 컬럼에 대해서는 자동으로 색인(index)을 생성한다. 그리고 다른 컬럼에도 색인이 필요하다면 CREATE INDEX 구문으로 색인을 걸어 준다. 알아두자.

그리고 만약 테이블이나 색인을 제거해야 하는 상황이라면 DROP INDEX나 DROP TABLE 구문을 execSQL()로 실행하면 된다.

데이터 추가

위에서 생성한 DB테이블에 데이터를 추가하는 방법은 2가지가 있다.

첫번째는 execSQL( ) 메소드를 사용하는 방법이다. 이는 값을 가져오는 문장이 아닌 INSERT, UPDATE, DELETE등의 모든 SQL 구문을 String 타입의 인자로 넣어 처리할 수 있다. 아래와 같이 말이다.

db.execSQL("INSERT INTO widgets (name, inventory)" + "VALUES ('Sprocket', 5)");

두번째는 SQLiteDatabase 클래스에서 제공하는 insert( ), update( ), delete( ) 등의 개별 메소드를 사용하는 방법이 있다. 이와같은 개별 메소드는 인자로 입력받은 값을 조합해 최종적으로 SQL 문장을 동일하게 실핼하도록 만들어져 있다. 개별 메소드는 Map과 비슷한 구조로 만들어져 있으면서 SQLite의 데이터 타입에 맞춰 동작하도록 구성된 ContentValues 객체를 사용해 동작한다.

그리고 지정한 키에 해당하는 값을 찾아올때는 단순하게 get() 메소드를 사용하는 대신, getAsInteger( ), getAsString( ) 등의 메소드를 호출하면 된다.

insert( ) 메소드는 대상이 되는 테이블 이름, null 처리 컬럼명, ContentValues 객체에 컬럼별 값을 넣어 인자로 넘겨 준다.

참고로, 안드로이드의 SQLite는 값이 하나도 없는 행은 허용하지 않는다. 따라서 ContentValues 인스턴스 값이 하나도 없는 경우 행이 생성되지 않기 때문에 이런경우 null 처리 컬럼 이름으로 지정된 컬럼값으로 NULL을 지정해 행이 생성되게 해야 한다. 

contentValues cv = new ContentValues();
cv.put(ContentValues.TITLE, "Gravity, Death Star I");
cv.put(ContentValues.VALUE, SensorManager.GRAVITY_DEATH_STAR_I);
db.insert("constants", getNullColumnHack(), cv);

update() 메소드는 대상 테이블 이름과 변경할 값이 들어 있는 ContentValues 객체를 넘겨준다. 그리고 값을 변경할 대상을 한정지으려면 WHERE 구문과 함께 WHERE 조건에 해당하는 값 역시 넘겨주면 된다. WHERE 구문은 물음표로 지정된 위치에 각자의 값이 배치돼 처리된다.

update() 메소드는 다른 정보를 사용해 계산된 값이 아닌 고정된 값을 갖는 컬럼만 변경할 수 있으므로, 필요한 경우에는 execSQL() 메소드를 사용해야 할 수도 있다. WHERE 구문에 표시된 물음표와 각 조건값을 지정하는 방법은 다른 SQL API에서 많이 사용하던 방법과 별반 다르지 않다.

// replacements는 ContentValues 인스턴스
String[] parms = new String[] {"snicklefritz"};
db.update("widgets", replacements, "name=?", parms);

delete() 메소드 역시 테이블 이름과 WHERE 구문을 사용한다. 변경할 값을 지정하지 않는다는 점만 제외하면 update()와 동일한 방법으로 동작한다.

데이터 불러오기

INSERT, UPDATE, DELETE와 비슷하게 SELECT 구문으로 데이터를 가져올때도 2가지 방법을 사용할 수 있다.

첫번째는 rawQuery( ) 메소드를 사용해 SELECT 구문을 직접 실행하는 방법이고, 두번째 방법은 query( ) 메소드의 인자로 각 부분의 값을 넘겨 실행하는 방법이다. SQLiteQueryBuilder 클래스와 관련된 부분과 커서와 커서 팩토리에 관한 부분이 가장 복잡하다.

SQL문 직접 작성 하는 방법을 좀더 살펴보자. API 호출 방법만 놓고 보면 rawQuery() 메소드를 사용하는 방법이 가장 간단하다. rawQuery() 메소드에 SELECT 구문을 인자로 넘겨 주기만 하면 되기때문이다.

SELECT 구문 역시 위치에 맞는 인자 배열을 함께 넘겨 줄수 있다. 아래 예제 코드를 살펴보자.

 

Cursor c = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='constants'", null);

위의 코드는 SQLite의 기본 테이블 가운데 하나인 sqlite_master 테이블 내용을 가져오는데, 내용으로 보면 constants라는 이름의 테이블이 만들어져 있는지 확인하는 구문이다. 결과로 받아오는 값은 Cursor 인스턴스인데, Cursor를 사용하면 여러 건의 결과를 하나씩 받아오면서 처리할수 있다. SELECT 문장이 동적으로 변경되지 않고 아예 프로그램 내부에 고정시켜버릴 예정이라면 위 방법이 가장 간단하다.

SELECT 구문 가운데 이부분이 동적으로 변경되거나, 위치로 인자값을 지정하는 방법으로 한계가 있는 수준이라면 굉장히 복잡해진다. 예를 들어 값을 가져와야 할 컬럼 개수가 개발 당시에 정해지지 않고 동적으로 변경된다면 처리하기 쉽지 않다. 그렇다고 해서 컬럼 이름을 필요할 때마다 쉼표로 연결해 사용하는건 물론 좋은 방법이 아니다. 이런 경우에는 query() 메소드를 사용하는게 훨씬 간편하다.

이번엔 일정한 형식의 쿼리를 위한 query( ) 메소드를 사용하는 방법을 좀 살펴보자.

query() 메소드는 SELECT 구문의 각 부분을 분할하여 각 인자로 넘겨받고, 최종적으로는 SELECT문을 생성해 실행한다.

query() 가 받아서 처리하는 인자의 순서는 아래와 같다.

1. 대상 테이블 이름
2. 값을 가져올 컬럼 이름의 배열
3. WHERE 구문. 물음표를 사용해 인자의 위치를 지정할 수 있다.
4. WHERE 구문에 들어가는 인자값
5. GROUP BY 구문
6. ORDER BY 구문
7. HAVING 구문

테이블 이름을 제외한 각 값이 필요없는 경우라면 null을 지정하도록 한다.

String[] columns={"ID", "inventory"};
Steing[] parms={"snicklefritz"};
Cursor result=db.query("widgets", columns, "name=?", parms, null, null, null);

 

쿼리 구문 생성을 위한 자세한 방법도 알아보자.

SQLiteQueryBuilder 클래스를 활용하면 훨씬 다양한 방법으로 UNION이나 하위 쿼리 등을 포함하는 복잡한 구문을 생성할 수 있다. SQLiteQueryBuilder 클래스가 ContentProvider 인터페이스와 완벽하게 맞아 떨어진다. 컨텐트 프로바이더의 query() 메소드를 구현하는 가장 일반적인 방법은 SQLiteQueryBuilder 인스턴스를 생성하고 기본값을 일부 채워넣은 다음 전체 쿼리를 생성하고 실행할수 있게 구성하는 것이다. 

SQLiteQueryBuilder 클래스를 사용해 요청을 처리하는 컨텐츠 프로바이더의 예제 코드를 살펴보면서 이해해보자.

@Override
public Cursor query(Uri url, String[] projection, String selection, String[] selectionArgs, String sort) {
	SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
	qb.setTables(getTableName());
	if (isCollectionUri(url)){
		qb.setProjectionMap(getDefaultProjection());
	} else {
		qb.appendWhere(getIdColumnName()+"="+url.getPathSegments().get(1));
	}

	String orderBy;

	if (TextUtils.isEmpty(sort)){
		orderBy = getDefaultSortOrder();
	} else {
		orderBy = sort;
	}

	Cursor c = qb.query(db, projection, selection, selectionArgs, null, null, orderBy); 
	c.setNotificationUri(getContext().getContentResolver(), url);
	return c;
}

위 코드는 아래와 같은 일을 한다.

1. SQLiteQueryBuilder 인스턴스를 생성함.
2. 쿼리에 사용할 테이블 이름을 설정함 -> setTables(getTableName( ))
3. 값을 가져올 기본 컬럼 이름의 목록을 지정하거나 (setProjectionMap( )), 또는 Uri값에 들어 있는 ID값으로 테이블 항목 가운데 특정한 값을 가져올 수 있도록 WHERE 구문을 추가했다. -> (appendWhere( ))
4. 마지막으로 기본값과 요청이 들어온 값을 조합해 생성된 쿼리 구문을 실행한다. -> qb.query(db, projection, selection, selectionArgs, null, null, orderBy)

 SQLiteQueryBuilder에서 쿼리를 직접 실행하는 대신 buildQuery( ) 메소드를 호출해 최종 생성된 SELECT 구문만을 리턴하게 할 수도 있다. 그러면 넘겨 받은 SELECT문을 필요할때 실행할수 있다.

커서 활용

안드로이드 SQLite에서는 SELECT 쿼리를 어떻게 실행하건 간에 그 결과로는 Cursor 인스턴스를 결과로 반환 받게된다. 커서 개념을 안드로이드와 SQLite에서 구현한 클래스가 바로 Cursor다. 

이 Cursor의 대표적인 메소드 몇가지를 잠깐 살펴보자.

• getCount() 메소드 : 전체 결과 건수가 몇개인지 확인할 수 있다.
• moveToFirst(), moveToNext(), isAfterLast() 등의 메소드 : 결과건을 모두 확인할수있다.
• getColumnNames() 메소드 : 결과에 포함된 전체 컬럼 이름을 알수 있다.
• requery() 메소드 : 쿼리를 재실행 할수 있다.
• close() 메소드 : 커서가 확보한 자원을 모두 해제한다.

 

아래 예제코드를 보자. widgets 테이블에 있는 항목을 모두 가져온 후, 반환받은 Cursor의 인스턴스로부터 모든 결과값을 뽑아내는 반복문 예제이다.

Cursor result = db.rawQuery("SELECT ID, namem inventory FROM widgets");
result.moveToFirst();
while (!result.isAfterLast()){
	int id = result.getInt(0);
	String name = result.getString(1);
	int inventory = result.getInt(2);
	//실제 필요한 작업 처리
	result.moveToNext();
}
result.close();

그런데 때때로 기본적으로 제공하는 Cursor 인스턴스 대신 Cursor를 상속받아 새로운 커서를 구현해야 할 피요가 있을 수도 있다. queryWithFactory() 메소드나 rawQueryWithFactory() 메소드에 SQLiteDatabase.CursorFactory 인스턴스를 인자로 넘겨 사용한다.

CursorFactory 클래스는 newCursor() 메소드가 구현된 내용에 따라 새로운 Cursor를 생성한다. 그런데 다행인 것은 일반적인 안드로이드 애플리케이션을 개발하고 있다면 커서를 새로 구현해야할 일이 많지는 않다는 것이다.

데이터 직접 다루기

안드로이드 SDK 에뮬레이터에는 sqlite3 프로그램이 포함되어 있고, adb shell 명령을 통해 실행해 사용할수있다. 에뮬레이터의 adb shell에 접속한 다음 sqlite3명령을 실행하면서 데이터베이스 파일이 위치한 경로를 함께 지정해 주면 된다.

데이터베이스 파일의 일반적인 위치는 '/data/data/your.app.package/database/your-db-name' 와 같다.

• your.app.package 부분은 애플리케이션이 들어 있는 자바 패키지 명을 의미한다.
• your-db-name 부분은 createDatabase() 명령을 실행할 때 지정했던 데이터베이스 이름을 넣는다.

sqlite3 프로그램은 충분한 기능을 갖추고 있으며, 콘솔 화면에서 데이터베이스를 다루는게 익숙하다면 adb shell도 괜찮은 방법이다.

그렇지만 만약 콘솔 인터페이스가 익숙치 않다면, GUI를 갖춘 화면이 필요할 수 있다. 위의 특정 경로에 보관되어 있는 데이터베이스 파일을 개발에 사용중인 Host PC(데스크탑, 안드로이드 SDK가 설치된 PC)으로 복사한 다음,  SQLite 데이터베이스 파일을 인식하는 다양한 프로그램을 활용해 데이터를 조회하고 다룰 수 있다. 이때는 adb pull을 사용하거나, 안드로이드 SDK의 에뮬레티어 file explorer를 활용하여 꺼낼 수 있다. 바깥으로 불러낸 복사본에 변경 작업을 진행했다면 변경된 데이터베이스 파일을 다시 기기(에뮬레티어)에 업로드해야 반영된다.

데이터베이스 파일을 기기에서 뽑아내려면 콘솔 (윈도우라면 cmd 혹은 동등한 커맨드라인 툴, 리눅스나 맥이라면 shell)에서 adb pull을 사용하여, 원본 경로와 대상 디렉토리 등을 적어주어 파일을 복사할 수 있으니 참고하자. 또한 변경 완료된 데이터베이스 파일을 기기에 업로드 하려면 adb push 명령을 사용한다. adb push도 adb pull과 마찬가지로 원본 파일 경로와 대상 디렉토리 등을 알려줘야 한다.

 일반적으로 가장 많이 사용되는 SQLite 클라이언트 프로그램은 파이어폭스 브라우저의 확장 기능으로 구현돼있는 SQLite Manager이다. 파이어 폭스 확장 기능이기 때문에 운영체제 플랫폼에 상관없이 어디서든 사용할 수 있다는 장점이 있다.