Android平台下数据库的创建过程如下所示：
public class LocalSQLiteOpenHelper extends SQLiteOpenHelper {
private static final String TAG = "LocalSQLiteOpenHelper";
public static final String CREATE_TABLE_TRACK = String.format(
"CREATE TABLE IF NOT EXISTS %s ("
+ "%s INTEGER PRIMARY KEY,"
+ "%s INTEGER NOT NULL,"
+ "%s TEXT NOT NULL,"
+ "%s INTEGER NOT NULL,"
+ "%s INTEGER NOT NULL,"
+ "%s TEXT NOT NULL,"
+ "%s LONG ,"
+ "%s LONG ,"
+ "%s FLOAT ,"
+ "%s LONG ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s FLOAT ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s FLOAT ,"
+ "%s INTEGER ,"
+ "%s TEXT ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s INTEGER );",
DBConstants.TRACK_TABLE_NAME,
DBConstants.KEY_ID,
DBConstants.KEY_TRACK_ID,
DBConstants.KEY_USER_ID,
DBConstants.KEY_TRACK_YEAR,
DBConstants.KEY_TRACK_MONTH,
DBConstants.KEY_DEVICE_ID,
DBConstants.KEY_TRACK_STARTTIME,
DBConstants.KEY_TRACK_ENDTIME,
DBConstants.KEY_TRACK_DISTANCE,
DBConstants.KEY_TRACK_DURATION,
DBConstants.KEY_TRACK_STEPS,
DBConstants.KEY_TRACK_CALORY,
DBConstants.KEY_TRACK_AVERAGESPEED,
DBConstants.KEY_TRACK_AVERAGEHEARTRATE,
DBConstants.KEY_DELIVERY_STATUS,
DBConstants.KEY_MEDAL_COUNT,
DBConstants.KEY_TRACK_INDOOR,
DBConstants.KEY_TRACK_LOCATIONCOUNT,
DBConstants.KEY_TRACK_MAXINSPEED,
DBConstants.KEY_TARCK_MAX_HEARTRATE,
DBConstants.KEY_TARCK_RANGE_HEARTRATE,
DBConstants.KEY_TARCK_RUN_TYPE,
DBConstants.KEY_TRACK_IS_DELETE,
DBConstants.KEY_TARCK_RUN_INCHINA);
public static final String CREATE_TABLE_TRACK_HIDE = String.format(
"CREATE TABLE IF NOT EXISTS %s ("
+ "%s INTEGER PRIMARY KEY,"
+ "%s INTEGER NOT NULL,"
+ "%s TEXT NOT NULL,"
+ "%s TEXT ,"
+ "%s INTEGER);",
DBConstants.TRACK_TABLE_HIDE_NAME,
DBConstants.KEY_ID,
DBConstants.KEY_TRACK_ID,
DBConstants.KEY_USER_ID,
DBConstants.KEY_DEVICE_ID,
DBConstants.KEY_TRACK_IS_HIDE);
public static final String CREATE_TABLE_RECORD = String.format(
"CREATE TABLE IF NOT EXISTS %s ("
+ "%s INTEGER PRIMARY KEY,"
+ "%s TEXT ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s TEXT,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s LONG ,"
+ "%s DOUBLE ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s DOUBLE ,"
+ "%s DOUBLE ,"
+ "%s DOUBLE ,"
+ "%s INTEGER ,"
+ "%s LONG ,"
+ "%s DOUBLE ,"
+ "%s INTEGER ,"
+ "%s LONG );",
DBConstants.RECORD_TABLE_NAME,
DBConstants.KEY_ID,
DBConstants.KEY_USER_ID,
DBConstants.KEY_RECORD_COUNTS,
DBConstants.KEY_RECORD_MAXDISTANCE_TRACKID,
DBConstants.KEY_DEVICE_ID,
DBConstants.KEY_RECORD_MAXSPEED_TRACKID,
DBConstants.KEY_RECORD_LATEST_TRACKID,
DBConstants.KEY_RECORD_TOTALCOSTTIME,
DBConstants.KEY_RECORD_TOTALDISTANCE,
DBConstants.KEY_RECORD_MAXCALORY,
DBConstants.KEY_RECORD_MAXCALORY_TRACKID,
DBConstants.KEY_RECORD_MAXTIME_TRACKID,
DBConstants.KEY_RECORD_TOTALCALORY,
DBConstants.KEY_RECORD_MAXDISTANCE,
DBConstants.KEY_RECORD_MAXSPEED,
DBConstants.KEY_RECORD_LASTDISTACE,
DBConstants.KEY_RECORD_DEL_RUN_COUNT,
DBConstants.KEY_RECORD_DEL_DURATION,
DBConstants.KEY_RECORD_DEL_TOTAL_DISTANCE,
DBConstants.KEY_RECORD_DEL_TOTAL_CALORY,
DBConstants.KEY_RECORD_MAXTIME);
public static final String CREATE_TABLE_INSPEED = String.format(
"CREATE TABLE IF NOT EXISTS %s ("
+ "%s INTEGER PRIMARY KEY,"
+ "%s INTEGER NOT NULL,"
+ "%s TEXT NOT NULL,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s DOUBLE);",
DBConstants.KMSPEED_TABLE_NAME,
DBConstants.KEY_ID,
DBConstants.KEY_TRACK_ID,
DBConstants.KEY_USER_ID,
DBConstants.KEY_DELIVERY_STATUS,
DBConstants.KEY_INSPEED_PERKILOMETER,
DBConstants.KEY_INSPEED);
public static final String CREATE_TABLE_MEDAL = String.format(
"CREATE TABLE IF NOT EXISTS %s ("
+ "%s INTEGER PRIMARY KEY,"
+ "%s TEXT ,"
+ "%s TEXT ,"
+ "%s TEXT ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s TEXT ,"
+ "%s TEXT ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s INTEGER );",
DBConstants.MEDAL_TABLE_NAME,
DBConstants.KEY_ID,
DBConstants.KEY_MEDAL_ID,
DBConstants.KEY_DEVICE_ID,
DBConstants.KEY_USER_ID,
DBConstants.KEY_TRACK_ID,
DBConstants.KEY_MEDAL_STANDARD,
DBConstants.KEY_MEDAL_CATEGORY,
DBConstants.KEY_MEDAL_THRESHOLD,
DBConstants.KEY_MEDAL_TEXT,
DBConstants.KEY_MEDAL_ICON,
DBConstants.KEY_MEDAL_SYNC,
DBConstants.KEY_MEDAL_GET,
DBConstants.KEY_MEDAL_TIME);
public static final String CREATE_TABLE_MEDAL_IMAGE = String.format(
"CREATE TABLE IF NOT EXISTS %s ("
+ "%s TEXT NOT NULL PRIMARY KEY,"
+ "%s TEXT ,"
+ "%s TEXT NOT NULL,"
+ "%s TEXT NOT NULL,"
+ "%s TEXT NOT NULL,"
+ "%s INTEGER NOT NULL);",
DBConstants.MEDAL_IMAGE_TABLE_NAME,
DBConstants.KEY_DEVICE_ID,
DBConstants.KEY_MEDAL_IMAGE_FILENAME,
DBConstants.KEY_MEDAL_IMAGE_FOLDER,
DBConstants.KEY_MEDAL_IMAGE_URL,
DBConstants.KEY_MEDAL_IMAGE_MD5,
DBConstants.KEY_MEDAL_IMAGE_DOWNLOAD);
public static final String CREATE_TABLE_MEDAL_VIDEO = String.format(
"CREATE TABLE IF NOT EXISTS %s ("
+ "%s INTEGER PRIMARY KEY,"
+ "%s TEXT ,"
+ "%s TEXT ,"
+ "%s TEXT ,"
+ "%s TEXT ,"
+ "%s TEXT ,"
+ "%s INTEGER );",
DBConstants.MEDAL_VIDEO_TABLE_NAME,
DBConstants.KEY_ID,
DBConstants.KEY_DEVICE_ID,
DBConstants.KEY_MEDAL_VIDEO_FILENAME,
DBConstants.KEY_MEDAL_VIDEO_FOLDER,
DBConstants.KEY_MEDAL_VIDEO_URL,
DBConstants.KEY_MEDAL_VIDEO_MD5,
DBConstants.KEY_MEDAL_VIDEO_DOWNLOAD);
public static final String CREATE_TABLE_FIT_RECORD = String.format(
"CREATE TABLE IF NOT EXISTS %s ("
+ "%s INTEGER PRIMARY KEY,"
+ "%s TEXT NOT NULL,"
+ "%s INTEGER NOT NULL,"
+ "%s LONG ,"
+ "%s LONG ,"
+ "%s INTEGER ,"
+ "%s LONG ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s LONG ,"
+ "%s INTEGER ,"
+ "%s INTEGER );",
DBConstants.FIT_RECORD,
DBConstants.KEY_ID,
DBConstants.KEY_USER_ID,
DBConstants.FIT_COUNTS,
DBConstants.FIT_TOTAL_DURTIONS,
DBConstants.FIT_SINGLE_MAX_TIME,
DBConstants.FIT_SINGLE_MAX_TIME_ID,
DBConstants.FIT_LAST_TIME,
DBConstants.FIT_LAST_TIME_ID,
DBConstants.FIT_MAX_CALORIES,
DBConstants.FIT_MAX_CALORIES_ID,
DBConstants.DEL_TOTAL_CALORIES,
DBConstants.DEL_TOTAL_DURATION,
DBConstants.DEL_RECORD_COUNT,
DBConstants.FIT_TOTAL_CALORIES);
public static final String CREATE_TABLE_FIT_TRACK = String.format(
"CREATE TABLE IF NOT EXISTS %s ("
+ "%s INTEGER PRIMARY KEY,"
+ "%s INTEGER NOT NULL,"
+ "%s TEXT NOT NULL,"
+ "%s INTEGER NOT NULL,"
+ "%s INTEGER NOT NULL,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s LONG ,"
+ "%s LONG ,"
+ "%s LONG ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s INTEGER ,"
+ "%s TEXT ,"
+ "%s LONG );",
DBConstants.FIT_TRACK,
DBConstants.KEY_ID,
DBConstants.KEY_TRACK_ID,
DBConstants.KEY_USER_ID,
DBConstants.KEY_TRACK_YEAR,
DBConstants.KEY_TRACK_MONTH,
DBConstants.FIT_TRACK_ACTION_NAME,
DBConstants.FIT_TRACK_ACTION_TYPE,
DBConstants.FIT_TRACK_START_TIME,
DBConstants.FIT_TRACK_STOP_TIME,
DBConstants.FIT_TRACK_WORK_TIME,
DBConstants.FIT_TRACK_AVG_HEART,
DBConstants.FIT_TRACK_CALORIES,
DBConstants.FIT_TRACK_DELETE,
DBConstants.FIT_TRACK_DELIVER_NETWORK,
DBConstants.KEY_TARCK_RANGE_HEARTRATE,
DBConstants.FIT_TRACK_RECOVER);
/**
* @param context
* @param name
* @param factory
* @param version
*/
public LocalSQLiteOpenHelper(Context context) {
super(context, DBConstants.LOCAL_DATABASE, null, DBConstants.DATABASE_VERSION);
}
/**
* @param context
* @param name
* @param factory
* @param version
*/
public LocalSQLiteOpenHelper(Context context,String database) {
super(context, database, null, DBConstants.DATABASE_VERSION);
}
/*
* (non-Javadoc)
* @see
* android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)
*/
@Override
public void onCreate(SQLiteDatabase db) {
Log.d(TAG, "db onCreate.");
createRecordAndTrackDatabase(db);
createMedalDataBase(db);
createFitDataBase(db);
// 创建Swim data create
createSwimDataBaseData(db);
// 创建Ride data create
createRideDataBaseData(db);
createBleRunningData(db);
// 自由训练
createFreeTraningDataBase(db);
// 创建健走的数据库
createWalkDataBaseData(db);
}
private void createFreeTraningDataBase(SQLiteDatabase db){
db.execSQL(FreeTrainningConstants.CREATE_TABLE_RECORD);
db.execSQL(FreeTrainningConstants.CREATE_TABLE_TRACK);
}
private void deleteFreeTraningDataBase(SQLiteDatabase db){
db.execSQL("DROP TABLE IF EXISTS " + FreeTrainningConstants.RECORD_TABLE_NAME + ";");
db.execSQL("DROP TABLE IF EXISTS " + FreeTrainningConstants.TRACK_TABLE_NAME + ";");
}
private void createWalkDataBaseData(SQLiteDatabase db){
db.execSQL(WalkConstants.CREATE_TABLE_RECORD);
db.execSQL(WalkConstants.CREATE_TABLE_TRACK);
}
private void deleteWalkDataBaseData(SQLiteDatabase db){
db.execSQL("DROP TABLE IF EXISTS " + WalkConstants.RECORD_TABLE_NAME + ";");
db.execSQL("DROP TABLE IF EXISTS " + WalkConstants.TRACK_TABLE_NAME + ";");
}
private void createRideDataBaseData(SQLiteDatabase db){
db.execSQL(RideConstant.CREATE_TABLE_RECORD);
db.execSQL(RideConstant.CREATE_TABLE_TRACK);
}
private void createSwimDataBaseData(SQLiteDatabase db){
db.execSQL(SwimConstant.CREATE_TABLE_RECORD);
db.execSQL(SwimConstant.CREATE_TABLE_TRACK);
}
private void deleteRecordAndTrackDatabase(SQLiteDatabase db){
db.execSQL("DROP TABLE IF EXISTS " + DBConstants.KMSPEED_TABLE_NAME + ";");
db.execSQL("DROP TABLE IF EXISTS " + DBConstants.TRACK_TABLE_NAME + ";");
db.execSQL("DROP TABLE IF EXISTS " + DBConstants.RECORD_TABLE_NAME + ";");
db.execSQL("DROP TABLE IF EXISTS " + DBConstants.TRACK_TABLE_HIDE_NAME + ";");
}
private void createRecordAndTrackDatabase(SQLiteDatabase db){
db.execSQL(CREATE_TABLE_INSPEED);
db.execSQL(CREATE_TABLE_TRACK);
db.execSQL(CREATE_TABLE_RECORD);
db.execSQL(CREATE_TABLE_TRACK_HIDE);
}
private void createMedalDataBase(SQLiteDatabase db){
db.execSQL(CREATE_TABLE_MEDAL);
db.execSQL(CREATE_TABLE_MEDAL_IMAGE);
db.execSQL(CREATE_TABLE_MEDAL_VIDEO);
}
private void deleteMedalDataBase(SQLiteDatabase db){
db.execSQL("DROP TABLE IF EXISTS " + DBConstants.MEDAL_TABLE_NAME + ";");
db.execSQL("DROP TABLE IF EXISTS " + DBConstants.MEDAL_IMAGE_TABLE_NAME + ";");
db.execSQL("DROP TABLE IF EXISTS " + DBConstants.MEDAL_VIDEO_TABLE_NAME + ";");
}
private void createFitDataBase(SQLiteDatabase db){
db.execSQL(CREATE_TABLE_FIT_RECORD);
db.execSQL(CREATE_TABLE_FIT_TRACK);
}
private void createBleRunningData(SQLiteDatabase db){
db.execSQL(BleRunningConstants.CREATE_TABLE_RECORD);
db.execSQL(BleRunningConstants.CREATE_TABLE_TRACK);
}
private void deleteBleRunningData(SQLiteDatabase db){
db.execSQL("DROP TABLE IF EXISTS " + BleRunningConstants.RECORD_TABLE_NAME + ";");
db.execSQL("DROP TABLE IF EXISTS " + BleRunningConstants.TRACK_TABLE_NAME + ";");
}
private void deleteRideDataBaseData(SQLiteDatabase db){
db.execSQL("DROP TABLE IF EXISTS " + RideConstant.RECORD_TABLE_NAME + ";");
db.execSQL("DROP TABLE IF EXISTS " + RideConstant.TRACK_TABLE_NAME + ";");
}
/*
* (non-Javadoc)
* @see
* android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase,
* int, int)
*/
@Override
public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
QRomLog.d(TAG,
String.format("onUpgrade oldVersion=%s newVersion=%s", oldVersion, newVersion));
if (database != null) {
if(oldVersion < DBConstants.DATABASE_VERSION){
if(!tabIsExist(DBConstants.TRACK_TABLE_HIDE_NAME, database)) {
database.execSQL(CREATE_TABLE_TRACK_HIDE);
}
deleteMedalDataBase(database);
createMedalDataBase(database);
database.execSQL("DROP TABLE IF EXISTS " + DBConstants.FIT_RECORD + ";");
database.execSQL("DROP TABLE IF EXISTS " + DBConstants.FIT_TRACK + ";");
createFitDataBase(database);
deleteRecordAndTrackDatabase(database);
createRecordAndTrackDatabase(database);
database.execSQL("DROP TABLE IF EXISTS " + SwimConstant.SWIM_RECORD_TABLE_NAME + ";");
database.execSQL("DROP TABLE IF EXISTS " + SwimConstant.SWIM_TRACK_TABLE_NAME + ";");
// 创建Swim data create
createSwimDataBaseData(database);
// 创建Ride data create
deleteRideDataBaseData(database);
createRideDataBaseData(database);
// 创建Run data
deleteBleRunningData(database);
createBleRunningData(database);
// 创建健走
deleteWalkDataBaseData(database);
createWalkDataBaseData(database);
// 重新创建当前的自由训练表
deleteFreeTraningDataBase(database);
createFreeTraningDataBase(database);
}
}
}
public boolean tabIsExist(String tabName, SQLiteDatabase db){
boolean result = false;
if(tabName == null){
return false;
}
Cursor cursor = null;
try {
String sql = "select count(*) as c from sqlite_master where type ='table' and name ='"+tabName.trim()+"' ";
cursor = db.rawQuery(sql, null);
if (null == cursor || cursor.getCount() <= 0) {
closeCursor(cursor);
return false;
}
if(cursor.moveToNext()){
int count = cursor.getInt(0);
if(count>0){
result = true;
}
}
} catch (Exception e) {
// TODO: handle exception
} finally {
closeCursor(cursor);
}
return result;
}
private void closeCursor(Cursor cursor) {
if (cursor != null && !cursor.isClosed()) {
cursor.close();
cursor = null;
}
}
@Override
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (db != null) {
db.execSQL("DROP TABLE IF EXISTS " + DBConstants.TRACK_TABLE_NAME + ";");
db.execSQL("DROP TABLE IF EXISTS " + DBConstants.TRACK_TABLE_HIDE_NAME + ";");
db.execSQL("DROP TABLE IF EXISTS " + DBConstants.KMSPEED_TABLE_NAME + ";");
db.execSQL("DROP TABLE IF EXISTS " + DBConstants.RECORD_TABLE_NAME + ";");
db.execSQL("DROP TABLE IF EXISTS " + DBConstants.MEDAL_TABLE_NAME + ";");
db.execSQL("DROP TABLE IF EXISTS " + DBConstants.MEDAL_IMAGE_TABLE_NAME + ";");
db.execSQL("DROP TABLE IF EXISTS " + DBConstants.FIT_RECORD + ";");
db.execSQL("DROP TABLE IF EXISTS " + DBConstants.FIT_TRACK + ";");
onCreate(db);
}
}
-
降序排列查询： 1） db.query(TABLE_NAME, projection, null, null, null, null, "id desc"); 这里的 desc 必须是小写，和 id 之间有个空格
-
升序排列查询： db.query(TABLE_NAME, projection, null, null, null, null, "id asc");
-
删除表格 db.execSQL("DROP TABLE IF EXISTS " + DBConstants.FIT_RECORD + ";");
-
清空表数据 db.delete(DATABASE_TABLE, null, null);
