操作Sqlite数据库

1、编写MyDbHelper类继承自SQLiteOpenHelper。代码如下：

package com.gnnuit.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class MyDbHelper extends SQLiteOpenHelper {
    /**
     * 
     * @param context应用程序上下文
     * @param name数据库的名字
     * @param factory查询数据库的游标工厂一般情况下用SDK默认的
     * @param version数据库的版本版本号必须不小1
     * 
     */
    public MyDbHelper(Context context) {
        super(context, "person.db", null, 2);

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        System.out.println("我只有第一次创建才执行");
        db.execSQL("CREATE TABLE person (personid integer primary key autoincrement, name varchar(20))");

    }

    // 在mydbOpenHelper 在数据库第一次被创建的时候 会执行onCreate();
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        System.out.println("我只有在更新时才执行");
        db.execSQL("ALTER TABLE person ADD phone VARCHAR(12) NULL ");
    }

}

2、编写增删改查的dao类。代码如下：

package com.gnnuit.dao;

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

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.gnnuit.db.MyDbHelper;
import com.gnnuit.domain.Person;

public class PersonDao {
    private static final String TAG = "PersonDao";
    private MyDbHelper dbHelper;

    public PersonDao(Context context) {
        dbHelper = new MyDbHelper(context);
    }

    public PersonDao() {

    }

    /**
     * 向数据库添加一条数据
     * 
     * @param name
     * @param phone
     */
    public void add(String name, String phone) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.execSQL("insert into person(name,phone) values(?,?); ",
                    new Object[] { name, phone });
            db.close();
        }
    }

    /**
     * 查询数据
     * 
     * @param name根据姓名查询
     * @return 查询是否成功
     */
    public boolean get(String name) {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        boolean result = false;
        if (db.isOpen()) {
            Cursor cursor = db.rawQuery(
                    "select phone from person where name=?",
                    new String[] { name });
            if (cursor.moveToFirst()) {
                int phoneIndex = cursor.getColumnIndex("phone");
                String phone = cursor.getString(phoneIndex);
                Log.i(TAG, "phone=" + phone);
                result = true;
            }
            cursor.close();
            db.close();
        }
        return result;
    }

    /**
     * 删除一条数据
     * 
     * @param name根据姓名删除
     */
    public void delete(String name) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.execSQL("delete from person where name=?", new Object[] { name });
            db.close();
        }
    }

    /**
     * 修改数据
     * 
     * @param name
     * @param phone
     */
    public void update(String name, String phone) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.execSQL("update person set phone=? where name=?", new Object[] {
                    phone, name });
            db.close();
        }
    }

    /**
     * 获得所有数据
     * 
     * @return
     */
    public List<Person> getAll() {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        List<Person> persons = null;
        if (db.isOpen()) {
            Cursor cursor = db.rawQuery("select * from person", null);
            persons = new ArrayList<Person>();
            while (cursor.moveToNext()) {
                Person p = new Person();
                int nameIndex = cursor.getColumnIndex("name");
                int phoneIndex = cursor.getColumnIndex("phone");
                String name = cursor.getString(nameIndex);
                String phone = cursor.getString(phoneIndex);
                p.setName(name);
                p.setPhone(phone);
                persons.add(p);
            }
            cursor.close();
            db.close();
        }
        return persons;
    }
}