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

}

三、写一个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;
}