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;
}