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";<strong>//这里放公共的字段</strong>    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"+ ");";@OverrideString getName() {return TABLE_NAME;}@Overridevoid 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"+ ");";@OverrideString getName() {return TABLE_NAME;}@Overridevoid onCreate(SQLiteDatabase db) {db.execSQL(CREATE_TABLE_STMT);}} static LinkedHashMap<String, DbBaseTable> sRCMDbTables = new LinkedHashMap<String, DbBaseTable>();        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) {       <strong> super(context, DataBaseClass.DB_FILE, null, DataBaseClass.DB_VERSION);</strong>    }        public static synchronized DbHelper getInstance(Context context) {     if (dbHelper == null) {      dbHelper = new DbHelper(context);     }     return dbHelper;    }    @Override    public void onCreate(SQLiteDatabase db) {               <strong> Collection<DbBaseTable> tables = DataBaseClass.sRCMDbTables.values();        Iterator<DbBaseTable> 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();        }</strong>    }        @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {    }        private String getTempTableName(String tableName, Collection<String> oldTableNames, Set<String> 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 {                <strong>db_r = super.getReadableDatabase();</strong>            } 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 {               <strong> db_w = super.getWritableDatabase();</strong>            } catch (SQLiteException e) {                //TODO Implement proper error handling                db_w = null;                                throw e;            }        }         return db_w;    }}