Android應用中內嵌SQLite資料庫的基本操作指南_Android

一、首先寫一個類繼承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) )");  }  /*當資料庫版本更新時調用此方法*/  @Overridepublic 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;}