Android應用中內嵌SQLite數據庫的基本操作指南

一、首先寫一個類繼承SQLiteOpenHelper類

重寫他的方法指定db的名稱、版本，重寫oncreat和onUpgrade方法，寫SQL語句創建表

public class MySQLiteOpenhelper extends SQLiteOpenHelper {
  private static String name = "person.db";
  private static int version = 1;
  public MySQLiteOpenhelper(Context context){
    super(context,name,null,version);  
  }

  /*
   *數據庫第一次被創建時調用的方法
   *db是被創建的數據庫
   */
  @Override
  public void onCreate(SQLiteDatabase db) {
    db.execSQL("create table person(id integer primary key autoincrement,name varchar(20),number varchar(20) )");

  }
  /*當數據庫版本更新時調用此方法*/
  @Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

  }

二、創建一個DAO類對外提供增刪改查接口
其中執行增刪改查的方法可以用SQL語句也可以使用系統給出的API,下面的代碼中把兩種方法都寫了出來

public class Persondao {
  private MySQLiteOpenhelper helper;
  public Persondao(){

  }
  public Persondao(Context context){
    helper = new MySQLiteOpenhelper(context);
  }
  public void add(String name,String number){
    SQLiteDatabase db = helper.getWritableDatabase();
    db.execSQL("insert into person (name,number)values(?,?)",new Object[]{name,number});
    /*ContentValues values = new ContentValues();
    values.put("number",number);
    values.put("name", name);
    long id = db.insert("Person",null, values);*/
    db.close();

  }
  public boolean find(String name){
    SQLiteDatabase db = helper.getWritableDatabase();
    //Cursor cursor = db.rawQuery("select *from person where name=?", new String[]{name});
    Cursor cursor = db.query("person", null,"name=?",new String[]{name},null,null, null);
    boolean result =cursor.moveToNext();
    cursor.close();
    db.close();
    return result;    
  }
  public int update(String name,String newnumber){
    SQLiteDatabase db = helper.getWritableDatabase();
    //db.execSQL("update person set number=? where name=?",new Object[]{newnumber,name});
    ContentValues values = new ContentValues();
    values.put("number",newnumber);
    int number = db.update("person", values,"name=?",new String[]{newnumber});
    db.close();
    return number;
  }
  public int delet(String name){
    SQLiteDatabase db = helper.getWritableDatabase();
    //db.execSQL("delete from person where name=?",new String[]{name});
    int number = db.delete("person","name=?",new String[]{name});
    db.close();  
    return number;

  }
  public List<Person> findAll(){
    List<Person> persons = new ArrayList<Person>();
    SQLiteDatabase db = helper.getWritableDatabase();
    //Cursor cursor = db.rawQuery("select *from person", null);
    Cursor cursor = db.query("person",new String[]{"id","name","number"}, null, null, null, null, null);
    while (cursor.moveToNext()) {
      int id = cursor.getInt(cursor.getColumnIndex("id"));
      String name = cursor.getString(cursor.getColumnIndex("name"));
      String number = cursor.getString(cursor.getColumnIndex("number"));
      Person p = new Person();
      persons.add(p);

    }
    db.close();
    cursor.close();
    return persons; 

  }

}

三、增刪改查操作

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
 
public class ToDoDB extends SQLiteOpenHelper {
  private final static String DATABASE_NAME = "todo_db";
  private final static int DATABASE_VERSION = 1;
  private final static String TABLE_NAME = "todo_table";
  public final static String FIELD_id = "_id";
  public final static String FIELD_TEXT = "todo_text";
 
  public ToDoDB(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }
 
  @Override
  public void onCreate(SQLiteDatabase db) {
    /* 建立table */
    String sql = "CREATE TABLE " + TABLE_NAME + " (" + FIELD_id
        + " INTEGER primary key autoincrement, " + " " + FIELD_TEXT
        + " text)";
    db.execSQL(sql);
  }
 
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
    db.execSQL(sql);
    onCreate(db);
  }
 
  public Cursor select() {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db
        .query(TABLE_NAME, null, null, null, null, null, null);
    return cursor;
  }
 
  public long insert(String text) {
    SQLiteDatabase db = this.getWritableDatabase();
    /* 將新增的值放入ContentValues */
    ContentValues cv = new ContentValues();
    cv.put(FIELD_TEXT, text);
    long row = db.insert(TABLE_NAME, null, cv);
    return row;
  }
 
  public void delete(int id) {
    SQLiteDatabase db = this.getWritableDatabase();
    String where = FIELD_id + " = ?";
    String[] whereValue = { Integer.toString(id) };
    db.delete(TABLE_NAME, where, whereValue);
  }
 
  public void update(int id, String text) {
    SQLiteDatabase db = this.getWritableDatabase();
    String where = FIELD_id + " = ?";
    String[] whereValue = { Integer.toString(id) };
    /* 將修改的值放入ContentValues */
    ContentValues cv = new ContentValues();
    cv.put(FIELD_TEXT, text);
    db.update(TABLE_NAME, cv, where, whereValue);
  }
}

四、寫一個JavaBean設置他的get、set方法

public class Person { 
  private int id;
  private String name;
  private String number;

  public Person(){

  }

  public Person(int id, String name, String number) {
    this.id = id;
    this.name = name;
    this.number = number;
  }
  public int getId() {
    return id;
  }
  public void setId(int id) {
    this.id = id;
  }
  public String getName() {
    return name;
  }
  public void setName(String name) {
    this.name = name;
  }
  public String getNumber() {
    return number;
  }
  public void setNumber(String number) {
    this.number = number;
  }

}

五、mainactivity中使用它

private SQLiteOpenHelper helper;
Persondao dao = new Persondao();

helper = new MySQLiteOpenhelper(this);
helper.getWritableDatabase();    
SQLiteDatabase db = helper.getWritableDatabase();

六、關於數據庫的事務處理
android開發中數據庫的操作非常慢，將所有操作打包成一個事務能夠大大的提高處理速度，其中最重要的是保證了數據的一致性，讓事務中的所有操作都能成功執行，或者失敗，或者這所有操作都回滾。

SQLiteDatabase db = helper.getWritableDatabase();
db.beginTransaction();
try{

 //在這裡執行多個數據庫操作，執行過程中可能會拋出異常
 db.execSQL("update person set number=? where name=?",new Object[]{"1",jacky});
 db.execSQL("update person set number=? where name=?",new Object[]{"2","sunny"});
 db.setTransactionSuccessful();
}catch{
 //捕獲異常
 throw e；
}finally{
  //所有操作完成結束一個事務
  db.endTransaction();
  db.close;
}