创建数据库
package com.example.hante.newnetpas.home.data.local;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* Created by handan on 2016/8/16.
*/
public class TasksDBHelper extends SQLiteOpenHelper {
public static final int DB_VERION = 1;
public static final String DB_NAME = "Tasks.db";
private static final String TEXT_TYPE = "TEXT";
private static final String BOOLEAN_TYPE = "INTEGER";
private static final String COMMA_SEP = " , ";
private static final String SQL_CREATE_TABLE =
"CREATE TABLE" + DB_NAME + "(" + "id" + TEXT_TYPE + "PRIMARY KEY," +
"entryid" + TEXT_TYPE + COMMA_SEP +
"title" + TEXT_TYPE + COMMA_SEP + ")";
public TasksDBHelper(Context context) {
super(context, DB_NAME, null, DB_VERION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(SQL_CREATE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
@Override
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
super.onDowngrade(db, oldVersion, newVersion);
}
}
对数据库进行操作
import static com.google.common.base.Preconditions.checkNotNull;
/**
* Concrete implementation of a data source as a db.
*/
public class TasksLocalDataSource implements TasksDataSource {
private static TasksLocalDataSource INSTANCE;
private TasksDbHelper mDbHelper;
// Prevent direct instantiation.
private TasksLocalDataSource(@NonNull Context context) {
checkNotNull(context);
mDbHelper = new TasksDbHelper(context);
}
public static TasksLocalDataSource getInstance(@NonNull Context context) {
if (INSTANCE == null) {
INSTANCE = new TasksLocalDataSource(context);
}
return INSTANCE;
}
/**
* Note: {@link LoadTasksCallback#onDataNotAvailable()} is fired if the database doesn't exist
* or the table is empty.
*/
@Override
public void getTasks(@NonNull LoadTasksCallback callback) {
List<Task> tasks = new ArrayList<Task>();
SQLiteDatabase db = mDbHelper.getReadableDatabase();
String[] projection = {
TaskEntry.COLUMN_NAME_ENTRY_ID,
TaskEntry.COLUMN_NAME_TITLE,
TaskEntry.COLUMN_NAME_DESCRIPTION,
TaskEntry.COLUMN_NAME_COMPLETED
};
Cursor c = db.query( // 查询 所有
TaskEntry.TABLE_NAME, projection, null, null, null, null, null);
if (c != null && c.getCount() > 0) {
while (c.moveToNext()) {
String itemId = c.getString(c.getColumnIndexOrThrow(TaskEntry.COLUMN_NAME_ENTRY_ID));
String title = c.getString(c.getColumnIndexOrThrow(TaskEntry.COLUMN_NAME_TITLE));
String description =
c.getString(c.getColumnIndexOrThrow(TaskEntry.COLUMN_NAME_DESCRIPTION));
boolean completed =
c.getInt(c.getColumnIndexOrThrow(TaskEntry.COLUMN_NAME_COMPLETED)) == 1;
Task task = new Task(title, description, itemId, completed);
tasks.add(task);
}
}
if (c != null) {
c.close();
}
db.close();
if (tasks.isEmpty()) {
// This will be called if the table is new or just empty.
callback.onDataNotAvailable();
} else {
callback.onTasksLoaded(tasks);
}
}
/**
* Note: {@link GetTaskCallback#onDataNotAvailable()} is fired if the {@link Task} isn't
* found.
*/
@Override
public void getTask(@NonNull String taskId, @NonNull GetTaskCallback callback) {
SQLiteDatabase db = mDbHelper.getReadableDatabase();
String[] projection = {
TaskEntry.COLUMN_NAME_ENTRY_ID,
TaskEntry.COLUMN_NAME_TITLE,
TaskEntry.COLUMN_NAME_DESCRIPTION,
TaskEntry.COLUMN_NAME_COMPLETED
};
String selection = TaskEntry.COLUMN_NAME_ENTRY_ID + " LIKE ?";
String[] selectionArgs = { taskId };
Cursor c = db.query( // 有条件查询
TaskEntry.TABLE_NAME, projection, selection, selectionArgs, null, null, null);
Task task = null;
if (c != null && c.getCount() > 0) {
c.moveToFirst();
String itemId = c.getString(c.getColumnIndexOrThrow(TaskEntry.COLUMN_NAME_ENTRY_ID));
String title = c.getString(c.getColumnIndexOrThrow(TaskEntry.COLUMN_NAME_TITLE));
String description =
c.getString(c.getColumnIndexOrThrow(TaskEntry.COLUMN_NAME_DESCRIPTION));
boolean completed =
c.getInt(c.getColumnIndexOrThrow(TaskEntry.COLUMN_NAME_COMPLETED)) == 1;
task = new Task(title, description, itemId, completed);
}
if (c != null) {
c.close();
}
db.close();
if (task != null) {
callback.onTaskLoaded(task);
} else {
callback.onDataNotAvailable();
}
}
@Override
public void saveTask(@NonNull Task task) {
checkNotNull(task);
SQLiteDatabase db = mDbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(TaskEntry.COLUMN_NAME_ENTRY_ID, task.getId());
values.put(TaskEntry.COLUMN_NAME_TITLE, task.getTitle());
values.put(TaskEntry.COLUMN_NAME_DESCRIPTION, task.getDescription());
values.put(TaskEntry.COLUMN_NAME_COMPLETED, task.isCompleted());
db.insert(TaskEntry.TABLE_NAME, null, values); // 插入 数据
db.close();
}
@Override
public void completeTask(@NonNull Task task) {
SQLiteDatabase db = mDbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(TaskEntry.COLUMN_NAME_COMPLETED, true);
String selection = TaskEntry.COLUMN_NAME_ENTRY_ID + " LIKE ?";
String[] selectionArgs = { task.getId() };
db.update(TaskEntry.TABLE_NAME, values, selection, selectionArgs); // 更新数据库
db.close();
}
@Override
public void completeTask(@NonNull String taskId) {
// Not required for the local data source because the {@link TasksRepository} handles
// converting from a {@code taskId} to a {@link task} using its cached data.
}
@Override
public void activateTask(@NonNull Task task) {
SQLiteDatabase db = mDbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(TaskEntry.COLUMN_NAME_COMPLETED, false);
String selection = TaskEntry.COLUMN_NAME_ENTRY_ID + " LIKE ?";
String[] selectionArgs = { task.getId() };
db.update(TaskEntry.TABLE_NAME, values, selection, selectionArgs); // 更新数据库
db.close();
}
@Override
public void activateTask(@NonNull String taskId) {
// Not required for the local data source because the {@link TasksRepository} handles
// converting from a {@code taskId} to a {@link task} using its cached data.
}
@Override
public void clearCompletedTasks() {
SQLiteDatabase db = mDbHelper.getWritableDatabase();
String selection = TaskEntry.COLUMN_NAME_COMPLETED + " LIKE ?";
String[] selectionArgs = { "1" };
db.delete(TaskEntry.TABLE_NAME, selection, selectionArgs);// 删除
db.close();
}
@Override
public void refreshTasks() {
// Not required because the {@link TasksRepository} handles the logic of refreshing the
// tasks from all the available data sources.
}
@Override
public void deleteAllTasks() {
SQLiteDatabase db = mDbHelper.getWritableDatabase();
db.delete(TaskEntry.TABLE_NAME, null, null); // 删除 表
db.close();
}
@Override
public void deleteTask(@NonNull String taskId) {
SQLiteDatabase db = mDbHelper.getWritableDatabase();
String selection = TaskEntry.COLUMN_NAME_ENTRY_ID + " LIKE ?";
String[] selectionArgs = { taskId };
db.delete(TaskEntry.TABLE_NAME, selection, selectionArgs); // 删除指定ID
db.close();
}
}
