android sqlite database

好吧,现在来看一下在android里面使用sqlite数据库的一些基本操作吧。

对于大多数app而言对数据库的要求很简单,无非CURD,仅此而已。而我个人比较喜欢将所要使用的相应数据
表封装成javabean再进行操作,这样会显得逻辑比较清晰。

首先,我们写一个Entity:

package wenyue.justdoit.entity;

public class todoData {
private String todotadkname;
private int id;

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public todoData() {

}

public String getTodotadkname() {
    return todotadkname;
}

public void setTodotadkname(String todotadkname) {
    this.todotadkname = todotadkname;
}

}

在这之后呢,我们需要一个继承了SQLiteOpenHelper的工具类。在这之后,我们只需要在进行对entity进行数据库操作的时候对其进行实例化即可。

SQLiteOpenHelper需要重写onCreate,onUpgrade方法。android已经对sqlite数据库的操作进行了封装,所以若是你想要更深入了解数据库操作,可以参考JDBC的使用。OnCreate再首次创建数据库时调用。而我们要进行数据库操作时可用SQLiteDatabase db = this.getWritableDatabase()。文档说该方法使db获得了写数据库的权限,感觉其实就是拿到了相应数据库的廉洁实体。

那么下面就直接贴出继承了SQLiteOpenHelper的工具类代码吧,毕竟都是些很简单的东西,看方法名就能知道是干什么的了。

package wenyue.justdoit.util;

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

import wenyue.justdoit.entity.todoData;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class dbHelper extends SQLiteOpenHelper {

private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "todolistManager";

// tasks table name
private static final String TABLE_TASKS = "todolist";

// tasks Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_TASKNAME = "todotadkname";

public dbHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    // TODO Auto-generated constructor stub
}

@Override
public void onCreate(SQLiteDatabase db) {
    String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_TASKS + " ( "
            + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + KEY_TASKNAME + " TEXT" + ")";
    db.execSQL(sql);
    // db.close();

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_TASKS);
    // Create tables again
    onCreate(db);

}

public void save(todoData testdata) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_TASKNAME, testdata.getTodotadkname());
    db.insert(TABLE_TASKS, null, values);
    db.close();
}

public List<todoData> getAllTasks() {
    List<todoData> taskList = new ArrayList<todoData>();
    // Select All Query
    String selectQuery = "SELECT  * FROM " + TABLE_TASKS;

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            todoData task = new todoData();
            task.setId(cursor.getInt(0));
            task.setTodotadkname(cursor.getString(1));
            // Adding contact to list
            taskList.add(task);
        } while (cursor.moveToNext());
    }
    db.close();
    return taskList;
}

public int checkIsExits(String s) {
    List<todoData> taskList = new ArrayList<todoData>();
    String selectQuery = "SELECT * FROM " + TABLE_TASKS + " WHERE "
            + KEY_TASKNAME + " = ?";

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, new String[] { s });
    if (cursor.moveToFirst()) {
        do {
            todoData task = new todoData();
            task.setId(cursor.getInt(0));
            task.setTodotadkname(cursor.getString(1));
            // Adding contact to list
            taskList.add(task);
        } while (cursor.moveToNext());
    }
    Log.i("checkIsExits", String.valueOf(taskList.size()));
    db.close();
    return taskList.size();

}

public int getTaskID(String tsakName) {

    List<todoData> taskList = new ArrayList<todoData>();
    String selectQuery = "SELECT * FROM " + TABLE_TASKS + " WHERE "
            + KEY_TASKNAME + " = ?";

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, new String[] { tsakName });
    if (cursor.moveToFirst()) {
        do {
            todoData task = new todoData();
            task.setId(cursor.getInt(0));
            task.setTodotadkname(cursor.getString(1));
            // Adding contact to list
            taskList.add(task);
        } while (cursor.moveToNext());
    }
    Log.i("GET_TASK_ID", String.valueOf(taskList.get(0).getId()));
    db.close();
    return taskList.get(0).getId();

}

public void updateTask(String taskName, int id) {
    Log.i("updateTask", String.valueOf(id) + "-------------" + taskName);
    // updating row
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(KEY_TASKNAME, taskName);
    String[] args = { String.valueOf(id) };

    db.update(TABLE_TASKS, values, KEY_ID + " = ?", args);
    Log.i("UPDATESUCCESS", String.valueOf(id));
    db.close();
}

public void deleteTask(String taskname) {
    SQLiteDatabase db = this.getWritableDatabase();
    String[] args = { String.valueOf(taskname) };
    db.delete(TABLE_TASKS, KEY_TASKNAME + "=?", args);
    db.close();
}

}

至于具体的数据处理就不用多说了吧,大家拿到实例后,调用相应的方法即可。好吧,先写这么多了。