SQLiteOpenHelper 를 이용한 SQLite3 제어

# SQLiteOpenHelper 상속 받은 객체 생성

public class WYSQLiteOpenHelper extends SQLiteOpenHelper {
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "wy_database.db3";

    private static final String SQL_CREATE =
            "CREATE TABLE IF NOT EXISTS tb_wy (" +
            "uid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
            "title TEXT NOT NULL)";
            
    private static final String SQL_DELETE = "DROP TABLE IF EXISTS tb_wy";

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

    // 데이터베이스가 최초 생성될 때 실행
    @Override
    public void onCreate(SQLiteDatabase db){
        Log.d("--wy--", "onCreate");
        // 앱을 삭제후 재 설치시 데이터베이스는 남아있을수 있어서 제거 쿼리 추가
        db.execSQL(SQL_DELETE);
        db.execSQL(SQL_CREATE);
    }

    // 데이터베이스가 업그레이드가 필요할때 실행
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    	// This database is only a cache for online data, so its upgrade policy is
        // to simply to discard the data and start over
        Log.d("--wy--", "onUpgrade");
        db.execSQL(SQL_DELETE);
        onCreate(db);
    }

    // 데이터베이스가 다운그레이드가 필요할 때 실행
    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d("--wy--", "onDowngrade");
        onUpgrade(db, oldVersion, newVersion);
    }
}

# SELECT (query)

WYSQLiteOpenHelper dbHelper = null;
SQLiteDatabase db = null;
Cursor cursor = null;
String strSQL = "";

try {
	dbHelper = new NewsPicSQLiteOpenHelper(getActivity());
	db = dbHelper.getReadableDatabase();
	
	String[] projection = {
		"uid",
		"title"
	};
    
    String selection = "title LIKE ?";
	String[] selectionArgs = {"titlevalue"};
    
    String sortOrder = "title DESC";
    
	cursor = db.query(
    	"tb_wy",		// The table to query
        projection,		// The array of columns to return (pass null to get all)
        selection,		// The columns for the WHERE clause
        selectionArgs,		// The values for the WHERE clause
        null,			// don't group the rows
        null,			// don't filter by row groups
        sortOrder		// The sort order
        );
        
	while (cursor.moveToNext()) {
		Log.d("--wy--", "uid : " + cursor.getString(0));
		Log.d("--wy--", "title : " + cursor.getString(1));
	}
}catch (Exception ex){
	Log.d("--wy--", "Exception: "+ ex.toString());
}
if(cursor != null) cursor.close();
if(db != null) db.close();
if(dbHelper != null) dbHelper.close();

 

# SELECT (rawQuery)

WYSQLiteOpenHelper dbHelper = null;
SQLiteDatabase db = null;
Cursor cursor = null;
String strSQL = "";

try {
	dbHelper = new NewsPicSQLiteOpenHelper(getActivity());
	db = dbHelper.getWritableDatabase();

	strSQL = "SELECT * FROM tb_wy";
	cursor = db.rawQuery(strSQL, null);
	while (cursor.moveToNext()) {
		Log.d("--wy--", "uid : " + cursor.getString(0));
		Log.d("--wy--", "title : " + cursor.getString(1));
	}
}catch (Exception ex){
	Log.d("--wy--", "Exception: "+ ex.toString());
}
if(cursor != null) cursor.close();
if(db != null) db.close();
if(dbHelper != null) dbHelper.close();

# INSERT

WYSQLiteOpenHelper dbHelper = null;
SQLiteDatabase db = null;
try {
	dbHelper = new NewsPicSQLiteOpenHelper(getActivity());
	db = dbHelper.getWritableDatabase();
    
	ContentValues values = new ContentValues();
	values.put("title", title);
	
    // Insert the new row, returning the primary key value of the new row
	long newRowId = db.insert("tb_wy", null, values);
}catch (Exception ex){
	Log.d("--wy--", "Exception: "+ ex.toString());
}
if(db != null) db.close();
if(dbHelper != null) dbHelper.close();   

insert의 반환값은 새로 생성된 행의 ID를 반환한다. 오류가 발생헌면 -1을 반환한다.

 

# DELETE

WYSQLiteOpenHelper dbHelper = null;
SQLiteDatabase db = null;
try {
	dbHelper = new NewsPicSQLiteOpenHelper(getActivity());
	db = dbHelper.getWritableDatabase();
 	String selection = "title LIKE ?";
	String[] selectionArgs = {"titlevalue"};

	long deleteRows = db.delete("tb_wy", selection, selectionArgs);
}catch (Exception ex){
	Log.d("--wy--", "Exception: "+ ex.toString());
}
if(db != null) db.close();
if(dbHelper != null) dbHelper.close();   

delete의 반환값은 데이터베이스에서 삭제된 행 수를 반환한다.

 

# UPDATE

WYSQLiteOpenHelper dbHelper = null;
SQLiteDatabase db = null;
try {
	dbHelper = new NewsPicSQLiteOpenHelper(getActivity());
	db = dbHelper.getWritableDatabase();
    
	ContentValues values = new ContentValues();
	values.put("title", "chgtitle");
    
	String selection = "title LIKE ?";
	String[] selectionArgs = {"titlevalue"};

	int count = db.update("tb_wy", values, selection, selectionArgs);
}catch (Exception ex){
	Log.d("--wy--", "Exception: "+ ex.toString());
}
if(db != null) db.close();
if(dbHelper != null) dbHelper.close();   

update의 반환값은 데이터베이스에서 update된 행 수를 반환한다.

 

# 연결해제

db.close();
dbHelper.close();

getWritableDatabase() 및 getReadableDatabase() 호출에는 리소스가 많이 사용되므로 데이터베이스에 액세스해야 하는 동안에는 최대한 데이터베이스 연결을 열린 상태로 두어야 한다..
일반적으로 호출 활동의 onDestroy()에서 데이터베이스를 닫는 것이 가장 좋음.
데이터베이스가 정상적으로 닫히지 않아 다른 화면에서 사용할 때 이슈가 발생할수도 있음.