android DataBase的相關操作(建立表結構和創建表)

先建立一個table的基類:

public abstract class DbBaseTable {
 private static final String TAG = "DbBaseTable";
 /**
  * @return the DB table name
  */
 abstract String getName();
 /**
  * Creates the DB table according to the DB scheme
  *
  * @param db
  */
 abstract void onCreate(SQLiteDatabase db);
 
 void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion, String tempName) {
       
    }
   
    
    void joinColumns(SQLiteDatabase db, String tempName, String tableName) {
     DbUtils.joinColumns(db, tempName, tableName);
    }
}

我們通過這種方式來建立表:

public final class DataBaseClass {

    private DataBaseClass() {};

    public static final int DB_VERSION = 2;
    
    static final String DB_FILE = "my.db";
//這裡放公共的字段
    public interface RCMColumns {
        public static final String account_ID = "account_id";                   //INTEGER (long)
    }
    
    public static final class MyTest1Table extends DbBaseTable implements BaseColumns, RCMColumns {

		private MyTest1Table() {
		}

		private static final MyTest1Table sInstance = new MyTest1Table();

		static MyTest1Table getInstance() {
			return sInstance;
		}

		private static final String TABLE_NAME = "Mytest1";

		/* Columns */
		public static final String TEST1 					= "test1";
		public static final String TEST2 						= "test2";
		public static final String TEST3				= "test3";

		private static final String CREATE_TABLE_STMT = "CREATE TABLE IF NOT EXISTS " 
				+ TABLE_NAME + " (" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT," 
				+ account_ID + " INTEGER, " 
				+ TEST1 + " TEXT, "
//				+ TEST2 + " TEXT, "
				+ TEST3 + " TEXT"
				+ ");";
		
		@Override
		String getName() {
			return TABLE_NAME;
		}

		@Override
		void onCreate(SQLiteDatabase db) {
			db.execSQL(CREATE_TABLE_STMT);
		}
	}
    
    
    public static final class MyTest2Table extends DbBaseTable implements BaseColumns, RCMColumns {

		private MyTest2Table() {
		}

		private static final MyTest2Table sInstance = new MyTest2Table();

		static MyTest2Table getInstance() {
			return sInstance;
		}

		private static final String TABLE_NAME = "Mytest2";

		/* Columns */
		public static final String TEST4 					= "test4";
		public static final String TEST5						= "test5";
		public static final String TEST6				= "test6";

		private static final String CREATE_TABLE_STMT = "CREATE TABLE IF NOT EXISTS " 
				+ TABLE_NAME + " (" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT," 
				+ account_ID + " INTEGER, " 
				+ TEST4 + " TEXT, "
				+ TEST5 + " TEXT, "
				+ TEST6 + " TEXT"
				+ ");";
		
		@Override
		String getName() {
			return TABLE_NAME;
		}

		@Override
		void onCreate(SQLiteDatabase db) {
			db.execSQL(CREATE_TABLE_STMT);
		}
	}

 static LinkedHashMap sRCMDbTables = new LinkedHashMap();
    
    static {
        sRCMDbTables.put(MyTest1Table.getInstance().getName(), MyTest1Table.getInstance());
      //  sRCMDbTables.put(MyTest2Table.getInstance().getName(), MyTest2Table.getInstance());
    }
}

android一些SQL語句的執行得依靠,SQLiteOpenHelper這個類,

我們來建立一個這類的子類:

final class DbHelper extends SQLiteOpenHelper {

    private static final String TAG = "[RC]RCMDbHelper";

    private static final String TEMP_SUFFIX = "_temp_";
    
    private SQLiteDatabase db_r = null; // readable database
    private SQLiteDatabase db_w = null; // writable database
    
    private static DbHelper dbHelper;
    
    private DbHelper(Context context) {
        super(context, DataBaseClass.DB_FILE, null, DataBaseClass.DB_VERSION);
    }
    
    public static synchronized DbHelper getInstance(Context context) {
     if (dbHelper == null) {
      dbHelper = new DbHelper(context);
     }
     return dbHelper;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        
        Collection tables = DataBaseClass.sRCMDbTables.values();
        Iterator iterator = tables.iterator();
        System.out.println("====DBHelp oncreate");
        try {
            db.beginTransaction();
            while (iterator.hasNext()) {
                iterator.next().onCreate(db);
            }
            db.setTransactionSuccessful();
        } catch (Throwable e) {
            //TODO Implement proper error handling
            
            throw new RuntimeException("DB creation failed: " + e.getMessage());
        } finally {
            db.endTransaction();
        }
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    
    private String getTempTableName(String tableName, Collection oldTableNames, Set newTableNames) {
        String temp_name_base = tableName + TEMP_SUFFIX;
         
        if (!oldTableNames.contains(temp_name_base) && !newTableNames.contains(temp_name_base)) {
            return temp_name_base;
        }
    
        Random random = new Random();
        String temp_name;
        for (;;) {
            temp_name = temp_name_base + random.nextInt();
            if (!oldTableNames.contains(temp_name) && !newTableNames.contains(temp_name)) {
                return temp_name;
            }
        }
    }

    
    @Override
    public synchronized SQLiteDatabase getReadableDatabase() {
        if (db_r == null || !db_r.isOpen()) {
            try {
                db_r = super.getReadableDatabase();
            } catch (SQLiteException e) {
                //TODO Implement proper error handling
                db_r = null;
                
                throw e;
            }
        } 
        return db_r;
    }

    
    @Override
    public synchronized SQLiteDatabase getWritableDatabase() {
        if (db_w == null || !db_w.isOpen() || db_w.isReadOnly()) {
            try {
                db_w = super.getWritableDatabase();
            } catch (SQLiteException e) {
                //TODO Implement proper error handling
                db_w = null;
                
                throw e;
            }
        } 
        return db_w;
    }

}