SQLite查询优化，提高获取数据速度

我们在是用SQlite查询数据，获取某个字段的值时，经常用到的方法是：
int age = cursor.getInt(cursor.getColumnIndex("age"));


其实我们可以优化一下，我们查询的时候，是知道查询数据库返回cursor中某一字段的索引值。
假如“age”字段的索引值是：3。（不是数据库当中字段的索引值）可以优化为：
int age = cursor.getInt(3);
这样就可以提高查询速度了。


例子：
public class ExceciseDbHelper {
public static final String tag = "ExceciseDbHelper";
public static final String KEY_ID = "_id";
public static final String KEY_DATE = "date";
public static final String KEY_TYPE = "activity_type";
public static final String KEY_TIME = "time";
public static final String KEY_SPEED = "speed";
public static final String KEY_DISTANCE = "distance";
public static final String KEY_CALORIES = "calories";
public static final String KEY_FINISH = "isFinish";
public static final String KEY_COLA = "cola";
private static final String DATABASE_NAME = "health.db";
private static final String DATABASE_TABLE = "excercise";
private static final int DATABASE_VERSION = 1;
private static ExceciseDbHelper mHRHelper;
private static final String DATABASE_CREATE = "create table excercise (_id integer primary key autoincrement, "
+ "date date not null, activity_type integer not null,time integer not null, speed text not null, "
+ "distance text not null, calories text not null, isFinish integer not null, cola text);";
private SQLiteDatabase mDb;
private DatabaseHelper mDbHelper;



// 要查询数据库的字段
public String[] projection = new String[]{KEY_ID, KEY_DATE,KEY_TYPE,KEY_TIME,KEY_SPEED,
KEY_DISTANCE,KEY_CALORIES,KEY_FINISH};
// 对应字段的索引值 对应上面数组的下标
public static final int ID_INDEX = 0;
public static final int DATE_INDEX = 1;
public static final int TYPE_INDEX = 2;
public static final int TIME_INDEX = 3;
public static final int SPEED_INDEX = 4;
public static final int DISTANCE_INDEX = 5;
public static final int CALORIES_INDEX = 6;
public static final int FINISH_INDEX = 7;

private ExceciseDbHelper(Context context) {
mDbHelper = new DatabaseHelper(context);
mDb = mDbHelper.getWritableDatabase();
}


public static ExceciseDbHelper getInstance(Context context) {
if (mHRHelper == null) {
mHRHelper = new ExceciseDbHelper(context);
}
return mHRHelper;
}


public void closeDatabase() {
mDbHelper.close();
}

public Cursor getAllData() {
return mDb.query(DATABASE_TABLE, projection, null, null, null, null, "_id desc");
}

public Cursor getShowData(int number) {
return mDb.query(DATABASE_TABLE, projection, null, null, null, null, "_id desc","0,"+number);
}
public List<ActivityData> getDataForshow(int number) {
List<ActivityData> data = null;
Cursor cursor = getShowData(number);
if(cursor !=null && cursor.getCount() > 0) {
data = new ArrayList<ActivityData>();
while(cursor.moveToNext()) {
//int activityType = cursor.getInt(cursor.getColumnIndex(KEY_TYPE)); 不推荐
// 直接使用索引值
int activityType = cursor.getInt(TYPE_INDEX);
int elpasedTime = cursor.getInt(TIME_INDEX);
String distance = cursor.getString(DISTANCE_INDEX);
String speed = cursor.getString(SPEED_INDEX);
String cal = cursor.getString(CALORIES_INDEX);
String date = cursor.getString(DATE_INDEX);
int finish = cursor.getInt(FINISH_INDEX);
ActivityData mActivityData = new ActivityData(activityType, elpasedTime, distance, speed, cal, date,finish);
mActivityData.id = cursor.getInt(ID_INDEX);
data.add(mActivityData);
}
}
if(cursor != null) {
cursor.close();
}
return data;

}

private static class DatabaseHelper extends SQLiteOpenHelper {


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


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


@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS excercise");
onCreate(db);
}


}

}