android SQLite数据库

（1）数据库的创建
在android中数据库的创建借助于SQLiteOpenHelper类

public class MyDBOpenHelper extends SQLiteOpenHelper {

public MyDBOpenHelper(Context context) {
super(context, "itheima.db", null, 1);
}

// 在mydbOpenHelper 在数据库第一次被创建的时候  会执行onCreate();
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE person (personid integer primary key autoincrement, name varchar(20))");
}

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("ALTER TABLE person ADD phone VARCHAR(12) NULL ");
}
}

（2）数据库的增删查改

public class PersonDao {
    private static final String TAG = "PersonDao";
    private MyDBOpenHelper dbOpenHelper;

    // 在personDao被new出来的时候 就完成初始化
    public PersonDao(Context context) {
        dbOpenHelper = new MyDBOpenHelper(context);
    }

    /**
     * 往数据库添加一条数据
     */
    public void add(String name, String phone) {
        boolean result = find(name);
        if (result)
            return;
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
        if (db.isOpen()) {
            //方法一
            db.execSQL("insert into person (name,phone) values (?,?)",
                    new Object[] { name, phone });

            //方法二
            ContentValues values = new ContentValues();
            values.put("name", name);
            values.put("age", age);
            db.insert("person", null, values); 

            // 关闭数据库 释放数据库的链接
            db.close();
        }
    }

    /**
     * 查找数据库的操作
     */
    public boolean find(String name) {
        boolean result = false;
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
        if (db.isOpen()) {
        //方法一
            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);

        //利用游标查询
            if (cursor.moveToFirst()) {
                int index = cursor.getColumnIndex("phone"); // 得到phone在表中是第几列
                String phone = cursor.getString(index);
                Log.i(TAG, "phone =" + phone);
                result = true;

            }
            // 记得关闭掉 cursor
            cursor.close();
            result = false;
            // 释放数据库的链接
            db.close();
        }
        return result;
    }

    /**
     * 删除一条记录
     * 
     * @param name
     */
    public void delete(String name) {
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
        if (db.isOpen()) {
        //方法一
            db.execSQL("delete from person where name =?",
                    new Object[] { name });

        //方法二
            db.delete("person", "name=?", new String[] { name });

        // 释放数据库的链接
            db.close();
        }
    }

    /**
     * 更新一条记录
     * 
     */
    public void update(String name, String newname, String newphone) {
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
        if (db.isOpen()) {
        //方法一
            db.execSQL("update person set name=? , phone=? where name=?",
                    new Object[] { newname, newphone, name });

        //方法二
        ContentValues values = new ContentValues();
            values.put("name", newname);
            values.put("age", newage);
            db.update("person", values, "name=?", new String[] { name });

        // 释放数据库的链接
            db.close();
        }
    }

    /**
     * 查找全部
     */
    public List<Person> getAllPersons() {
        List<Person> persons=null;
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
        if (db.isOpen()) {
            persons = new ArrayList<Person>();
            //方法一
            Cursor cursor = db.rawQuery("select * from person ", null);

            //方法二
            Cursor cursor = db.query("person", null, null, null, null, null,
                    null);

                while (cursor.moveToNext()) {
                Person person = new Person();
                int nameindex = cursor.getColumnIndex("name");
                int phoneindex = cursor.getColumnIndex("phone");
                String name = cursor.getString(nameindex);
                String phone = cursor.getString(phoneindex);
                person.setName(name);
                person.setNumber(phone);
                persons.add(person);
            }
            cursor.close();
            db.close();
        }
        return persons;
    }

}

（3）数据库事务

public void transaction() {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
if (db.isOpen()) {
try {
// 开启数据库的事务
db.beginTransaction();
// 给张三设置1000块钱的账户
db.execSQL("update person set account=? where name=?",
new Object[] { 1000, "zhangsan98" });
// 把张三的账户扣除200块钱
db.execSQL("update person set account=account-? where name=?",
new Object[] { 200, "zhangsan98" });
// 出现了异常
// 把张三的钱给李四
//初始化李四账户 为 0
db.execSQL("update person set account=? where name=?",
new Object[] { 0, "lisi" });
db.execSQL("update person set account=account+? where name=?",
new Object[] { 200, "lisi" });
db.setTransactionSuccessful();
}
// 显示的设置事务是否成功
catch (Exception e) {

} finally {
db.endTransaction();
db.close();
}
}
}
}

引用一篇好的博客：http://blog.youkuaiyun.com/jason0539/article/details/10248457