冲刺4

设置

package timeline.lizimumu.com.t.db;

import android.provider.BaseColumns;

final class DbConst {

static final String DATABASE_NAME = "timeline";

private DbConst() {
}

static class TableIgnore implements BaseColumns {
static final String TABLE_NAME = "ignore";
static final String FIELD_PACKAGE_NAME = "package_name";
static final String FIELD_CREATE_TIME = "created_time";
}

static class TableHistory implements BaseColumns {
static final String TABLE_NAME = "history";
static final String FIELD_DATE = "date";
static final String FIELD_PACKAGE_NAME = "package_name";
static final String FIELD_NAME = "name";
static final String FIELD_SYSTEM = "is_system";
static final String FIELD_DURATION = "duration";
static final String FIELD_TIMESTAMP = "timestamp";
static final String FIELD_MOBILE_TRAFFIC = "mobile";
}

}

获取

package timeline.lizimumu.com.t.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DbHelper extends SQLiteOpenHelper {

private static final int DATABASE_VERSION = 2;
private static final String DATABASE_NAME = DbConst.DATABASE_NAME;

private static final String SQL_CREATE_IGNORE =
"CREATE TABLE " + DbConst.TableIgnore.TABLE_NAME + " (" +
DbConst.TableIgnore._ID + " INTEGER PRIMARY KEY," +
DbConst.TableIgnore.FIELD_PACKAGE_NAME + " TEXT," +
DbConst.TableIgnore.FIELD_CREATE_TIME + " INTEGER)";

private static final String SQL_CREATE_HISTORY =
"CREATE TABLE " + DbConst.TableHistory.TABLE_NAME + " (" +
DbConst.TableHistory._ID + " INTEGER PRIMARY KEY," +
DbConst.TableHistory.FIELD_PACKAGE_NAME + " TEXT," +
DbConst.TableHistory.FIELD_NAME + " TEXT," +
DbConst.TableHistory.FIELD_DATE + " TEXT," +
DbConst.TableHistory.FIELD_SYSTEM + " INTEGER," +
DbConst.TableHistory.FIELD_MOBILE_TRAFFIC + " INTEGER," +
DbConst.TableHistory.FIELD_TIMESTAMP + " INTEGER," +
DbConst.TableHistory.FIELD_DURATION + " INTEGER)";

private static final String SQL_DELETE_IGNORE =
"DROP TABLE IF EXISTS " + DbConst.TableIgnore.TABLE_NAME;

private static final String SQL_DELETE_HISTORY =
"DROP TABLE IF EXISTS " + DbConst.TableIgnore.TABLE_NAME;

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

public void onCreate(SQLiteDatabase db) {
db.execSQL(SQL_CREATE_IGNORE);
db.execSQL(SQL_CREATE_HISTORY);
}

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(SQL_DELETE_IGNORE);
db.execSQL(SQL_DELETE_HISTORY);
onCreate(db);
}

public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
onUpgrade(db, oldVersion, newVersion);
}
}

历史记录获取

package timeline.lizimumu.com.t.db;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;
import java.util.List;

import timeline.lizimumu.com.t.data.HistoryItem;

public class DbHistoryExecutor {

private static DbHistoryExecutor sInstance;
private static DbHelper mHelper;

private DbHistoryExecutor() {
}

public static void init(Context context) {
mHelper = new DbHelper(context);
sInstance = new DbHistoryExecutor();
}

public static DbHistoryExecutor getInstance() {
return sInstance;
}

public void clear() {
mHelper.getWritableDatabase().delete(DbConst.TableHistory.TABLE_NAME, null, null);
}

public void insert(HistoryItem historyItem) {
if (!exists(historyItem)) {
ContentValues values = itemToContentValue(historyItem);
mHelper.getWritableDatabase().insert(DbConst.TableHistory.TABLE_NAME, null, values);
}
}

public List<HistoryItem> getAllItems() {
Cursor cursor = null;
List<HistoryItem> items = new ArrayList<>();
try {
String[] columns = {
DbConst.TableHistory._ID,
DbConst.TableHistory.FIELD_DATE,
DbConst.TableHistory.FIELD_TIMESTAMP,
DbConst.TableHistory.FIELD_SYSTEM,
DbConst.TableHistory.FIELD_DURATION,
DbConst.TableHistory.FIELD_PACKAGE_NAME,
DbConst.TableHistory.FIELD_MOBILE_TRAFFIC,
DbConst.TableHistory.FIELD_NAME,
};
String orderBy = DbConst.TableHistory.FIELD_DURATION + " DESC";
cursor = mHelper.getReadableDatabase().query(
DbConst.TableHistory.TABLE_NAME,
columns,
null,
null,
null,
null,
orderBy);
while (cursor.moveToNext()) {
items.add(cursorToItem(cursor));
}
} finally {
if (cursor != null) cursor.close();
}
return items;
}
@SuppressLint("Range")
private boolean exists(HistoryItem historyItem) {
SQLiteDatabase database = mHelper.getWritableDatabase();
Cursor cursor = null;
try {
cursor = database.query(DbConst.TableHistory.TABLE_NAME,
new String[]{DbConst.TableHistory._ID},
DbConst.TableHistory.FIELD_DATE + " = ? AND " + DbConst.TableHistory.FIELD_PACKAGE_NAME + " = ?",
new String[]{historyItem.mDate, historyItem.mPackageName},
null,
null,
null);
if (cursor.moveToNext()) {

int id = cursor.getInt(cursor.getColumnIndex(DbConst.TableHistory._ID));
return id > 0;
}
} finally {
if (cursor != null) cursor.close();
}
return false;
}

private ContentValues itemToContentValue(HistoryItem historyItem) {
ContentValues values = new ContentValues();
values.put(DbConst.TableHistory.FIELD_DATE, historyItem.mDate);
values.put(DbConst.TableHistory.FIELD_DURATION, historyItem.mDuration);
values.put(DbConst.TableHistory.FIELD_MOBILE_TRAFFIC, historyItem.mMobileTraffic);
values.put(DbConst.TableHistory.FIELD_NAME, historyItem.mName);
values.put(DbConst.TableHistory.FIELD_PACKAGE_NAME, historyItem.mPackageName);
values.put(DbConst.TableHistory.FIELD_SYSTEM, historyItem.mIsSystem);
values.put(DbConst.TableHistory.FIELD_TIMESTAMP, historyItem.mTimeStamp);
return values;
}

@SuppressLint("Range")
private HistoryItem cursorToItem(Cursor cursor) {
HistoryItem item = new HistoryItem();
item.mPackageName = cursor.getString(cursor.getColumnIndex(DbConst.TableHistory.FIELD_PACKAGE_NAME));
item.mName = cursor.getString(cursor.getColumnIndex(DbConst.TableHistory.FIELD_NAME));
item.mDate = cursor.getString(cursor.getColumnIndex(DbConst.TableHistory.FIELD_DATE));
item.mDuration = cursor.getLong(cursor.getColumnIndex(DbConst.TableHistory.FIELD_DURATION));
item.mTimeStamp = cursor.getLong(cursor.getColumnIndex(DbConst.TableHistory.FIELD_TIMESTAMP));
item.mIsSystem = cursor.getInt(cursor.getColumnIndex(DbConst.TableHistory.FIELD_SYSTEM));
item.mMobileTraffic = cursor.getInt(cursor.getColumnIndex(DbConst.TableHistory.FIELD_MOBILE_TRAFFIC));
return item;
}
}

 

 

搜索

复制