IT/Android

sqlite3 디비

UIMarvel 2012. 2. 8. 15:55

안드로이드의 database 는 sqlite3이라는 녀석이다. 파일 기반 DB로써, 속도가 매우 빠르다는데...?

일단 안드로이드 내에서 DB를 건드리기위해 DB Adapter를 만들자. ListAapter나 ArrayAdapter는 저절로 제공이 되지만 DB Adapter는 제공이 안되는 것 같으므로 알아서 만들어야 하는 것 같다.

 

 public class DBAdaptor {  //완벽한 싱글톤은 아니지만 비슷하게 만듬
 public static final String WORD_KEY_COLUMN1 = "spelling";
 public static final String WORD_KEY_COLUMN2 = "wordclass";
 public static final String WORD_KEY_COLUMN3 = "meaning";
 public static final String PRIMARY_KEY_COLUMN = "_id";
 public static final String MAPPING_KEY_COLUMN1 = "tablename";
 public static final String MAPPING_KEY_COLUMN2 = "name";
 public static final String MYTABLE_KEY_COLUMN = "wordID";
 public static final String CURRENT_KEY_COLUMN1 = "mappingID";
 public static final String CURRENT_KEY_COLUMN2 = "interval";
 public static final int MAX_CURRENT_KEY_COLUMN2 = 5;
 public static final int MIN_CURRENT_KEY_COLUMN2 = 3;
 
 private static final String TAG = "DbAdapter";
 private static DatabaseHelper mDbHelper;
 private static SQLiteDatabase mDb; // 데이터베이스를 저장
 
 private static final String DATABASE_CREATE =
  "create table if not exists word(_id integer primary key autoincrement,"+
  "spelling text not null, wordclass text not null, meaning text not null);";
 private static final String DATABASE_CREATE2 =
  "create table if not exists mapping(_id integer primary key autoincrement, tablename text not null, name text not null);";
 private static final String DATABASE_CREATE3 =
  "create table if not exists current(_id integer primary key autoincrement, mappingID integer not null, interval interger not null);";
 
 
 private static final String DATABASE_NAME = "voca.db";
 private static final String DATABASE_TABLE = "word";
 private static final String DATABASE_TABLE2 = "mapping";
 private static final String DATABASE_TABLE3 = "current";
 private static final int DATABASE_VERSION = 1;
 
 private final Context mCtx;
 
 private class DatabaseHelper extends SQLiteOpenHelper{
  public DatabaseHelper(Context context) {
   super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }
  @Override
  public void onCreate(SQLiteDatabase db){
   db.execSQL(DATABASE_CREATE);
   db.execSQL(DATABASE_CREATE2);
   db.execSQL(DATABASE_CREATE3);
   
   ContentValues initialValues = new ContentValues();
   initialValues.put(CURRENT_KEY_COLUMN1, 1);
   initialValues.put(CURRENT_KEY_COLUMN2, 3);
   db.insert(DATABASE_TABLE3, null, initialValues);
   
   String createTrigger = "create trigger if not exists fk_delete_mapping_current" +
         " before delete on mapping for each row begin update current" +
         " set mappingID = 1 where mappingID = old._id; end;";
   db.execSQL(createTrigger);
   
   initialValues.clear();
   
   initialValues.put(MAPPING_KEY_COLUMN1, "word");
   initialValues.put(MAPPING_KEY_COLUMN2, "All Word");
   db.insert(DATABASE_TABLE2, null, initialValues);
  }
  
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
   Log.w(TAG, "Upgrading db from version" + oldVersion + " to" +
     newVersion + ", which will destroy all old data");
   db.execSQL("DROP TABLE IF EXISTS data");
   onCreate(db);
  }
 }
 
 public DBAdaptor(Context ctx){
  this.mCtx = ctx;
 }
 
 public DBAdaptor open() throws SQLException{
  mDbHelper = new DatabaseHelper(mCtx);
  mDb = mDbHelper.getWritableDatabase();
  return this;
 }
 
 public void close(){
  mDbHelper.close();
  mDb.close();
 }
 
 public static long insertWord(String spelling, String wordclass, String meaning)
 {
  ContentValues initialValues = new ContentValues();
  initialValues.put(WORD_KEY_COLUMN1, spelling);
  initialValues.put(WORD_KEY_COLUMN2, wordclass);
  initialValues.put(WORD_KEY_COLUMN3, meaning);
  
  return mDb.insert(DATABASE_TABLE, null, initialValues);
 }
 
 public static long insertMapping(String tablename, String name)
 {
  ContentValues initialValues = new ContentValues();
  initialValues.put(MAPPING_KEY_COLUMN1, tablename);
  initialValues.put(MAPPING_KEY_COLUMN2, name);
  
  return mDb.insert(DATABASE_TABLE2, null, initialValues);
 }
 
 public static long insertMyTable(long wordID, String tablename)
 {
  ContentValues initialValues = new ContentValues();
  initialValues.put(MYTABLE_KEY_COLUMN, wordID);
  
  return mDb.insert(tablename, null, initialValues);
 }

 public static boolean deleteWord(String spelling, String wordclass) throws SQLException
 {
  String[] whereArgs = new String[]{spelling, wordclass};
  return mDb.delete(DATABASE_TABLE, WORD_KEY_COLUMN1 + "=? AND " + WORD_KEY_COLUMN2 + "=?", whereArgs) > 0;
 }
 
 public static boolean deleteMapping(String tablename) throws SQLException
 {
  return mDb.delete(DATABASE_TABLE2, MAPPING_KEY_COLUMN1 + "='" + tablename +"'", null) > 0;
 }
 
 public static Cursor fetchAllWords(){
  Cursor mCursor = null;
  mCursor =
   mDb.query(DATABASE_TABLE, new String[]{PRIMARY_KEY_COLUMN, WORD_KEY_COLUMN1, WORD_KEY_COLUMN2, WORD_KEY_COLUMN3}, null, null, null, null, WORD_KEY_COLUMN1);
  if(mCursor != null)
   mCursor.moveToFirst();
  return mCursor;
 }
 
 public static Cursor fetchWord(String spelling, String wordclass) throws SQLException{
  String[] selectArgs;
  String[] col;
  col = new String[]{PRIMARY_KEY_COLUMN, WORD_KEY_COLUMN1, WORD_KEY_COLUMN2, WORD_KEY_COLUMN3};
  selectArgs = new String[]{spelling, wordclass};
  Cursor mCursor = null;
  mCursor =
   mDb.query(true, DATABASE_TABLE, col, WORD_KEY_COLUMN1 + "=? AND " + WORD_KEY_COLUMN2 + "=?", selectArgs, null, null, null, null);
  if(mCursor != null)
   mCursor.moveToFirst();
  return mCursor;
 }
 
 public static Cursor fetchWordAtSpell(String spelling) throws SQLException{
  String[] col;
  col = new String[]{PRIMARY_KEY_COLUMN, WORD_KEY_COLUMN2, WORD_KEY_COLUMN3};
  Cursor mCursor = null;
  mCursor =
   mDb.query(true, DATABASE_TABLE, col, WORD_KEY_COLUMN1 + "='" + spelling + "'", null, null, null, null, null);
  if(mCursor != null)
   mCursor.moveToFirst();
  return mCursor;
 }
 
 public static Cursor fetchAllWordsJoin(String tablename) throws SQLException{
  String sql = "Select * from word," + tablename +" where word._id=" + tablename + ".wordID order by spelling";

  Cursor mCursor = null;
  mCursor =
   mDb.rawQuery(sql, null);
  if(mCursor != null)
   mCursor.moveToFirst();
  return mCursor;
 }
 
 public static Cursor fetchAllMapping(){
  Cursor mCursor = null;
  mCursor =
   mDb.query(DATABASE_TABLE2, new String[]{PRIMARY_KEY_COLUMN, MAPPING_KEY_COLUMN1, MAPPING_KEY_COLUMN2}, null, null, null, null, null);
  if(mCursor != null)
   mCursor.moveToFirst();
  return mCursor;
 }
 
 public static Cursor fetchMappingAtTableName(String tablename) throws SQLException{
  String[] col;
  col = new String[]{PRIMARY_KEY_COLUMN, MAPPING_KEY_COLUMN1, MAPPING_KEY_COLUMN2};
  Cursor mCursor = null;
  mCursor =
   mDb.query(true, DATABASE_TABLE2, col, MAPPING_KEY_COLUMN1 + "='" + tablename + "'", null, null, null, null, null);
  if(mCursor != null)
   mCursor.moveToFirst();
  return mCursor;
 }
 
 public static Cursor fetchMappingAtName(String name) throws SQLException{
  String[] col;
  col = new String[]{PRIMARY_KEY_COLUMN, MAPPING_KEY_COLUMN1, MAPPING_KEY_COLUMN2};
  Cursor mCursor = null;
  mCursor =
   mDb.query(true, DATABASE_TABLE2, col, MAPPING_KEY_COLUMN2 + "='" + name + "'", null, null, null, null, null);
  if(mCursor != null)
   mCursor.moveToFirst();
  return mCursor;
 }
 
 public static Cursor fetchMappingAtID(int id) throws SQLException{
  String[] col;
  col = new String[]{PRIMARY_KEY_COLUMN, MAPPING_KEY_COLUMN1, MAPPING_KEY_COLUMN2};
  Cursor mCursor = null;
  mCursor =
   mDb.query(true, DATABASE_TABLE2, col, PRIMARY_KEY_COLUMN + "=" + id, null, null, null, null, null);
  if(mCursor != null)
   mCursor.moveToFirst();
  return mCursor;
 }
 
 public static Cursor fetchCurrent(){
  Cursor mCursor = null;
  mCursor =
   mDb.query(DATABASE_TABLE3, new String[]{PRIMARY_KEY_COLUMN, CURRENT_KEY_COLUMN1, CURRENT_KEY_COLUMN2}, null, null, null, null, null);
  if(mCursor != null)
   mCursor.moveToFirst();
  return mCursor;
 }
 
 public static boolean updateWord(long rowID, String spelling, String wordclass){
  ContentValues args = new ContentValues();
  args.put(WORD_KEY_COLUMN1, spelling);
  args.put(WORD_KEY_COLUMN2, wordclass);
  
  return mDb.update(DATABASE_TABLE, args, PRIMARY_KEY_COLUMN + "=" + rowID, null) > 0;
 }
 
 public static boolean updateCurrent(int mappingID){
  ContentValues args = new ContentValues();
  args.put(CURRENT_KEY_COLUMN1, mappingID);
  return mDb.update(DATABASE_TABLE3, args, PRIMARY_KEY_COLUMN + "=1", null) > 0;
 }
 
 public static boolean updateInterval(int interval){
  ContentValues args = new ContentValues();
  args.put(CURRENT_KEY_COLUMN2, interval);
  return mDb.update(DATABASE_TABLE3, args, PRIMARY_KEY_COLUMN + "=1", null) > 0;
 }
 
 public static void beginTransaction()
 {
  mDb.beginTransaction();
 }
 
 public static void setTransactionSuccessful()
 {
  mDb.setTransactionSuccessful();
 }
 
 public static void endTransaction()
 {
  mDb.endTransaction();
 }
 
 public static void execSQLFunc(String sql)
 {
  mDb.execSQL(sql);
 }
}

 

요렇게 db 어댑터 클래스를 만들어 놓고 필요한 activity에서 선언해서 쓰면 되겄다.

 

책하나 없이 독학하려니...좀 힘든데?