sqlite数据库之三---封装好的sql语句的增删查改

package cn.itcast.db.dao;import java.util.ArrayList;import java.util.List;import cn.itcast.db.MyDBOpenHelper;import cn.itcast.db.domain.Person;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;public class PersonDBDao {private Context context;MyDBOpenHelper dbOpenHelper;public PersonDBDao(Context context) {this.context = context;dbOpenHelper = new MyDBOpenHelper(context);}/** * 添加一条记录 */public void add(String name, int age) {SQLiteDatabase db = dbOpenHelper.getWritableDatabase();if (db.isOpen()) {// db.execSQL("insert into person (name,age) values (?,?)",new// Object[]{name,age});// db.execSQL("insert into person ",null) // 不合法的sql语句ContentValues values = new ContentValues();values.put("name", name);values.put("age", age);// 如果 contentvalues为空db.insert("person", null, values); // 组拼sql语句完成的添加的操作// insert into person name values (NULL) ;db.close();}}/** * 删除一条记录 */public void delete(String name) {SQLiteDatabase db = dbOpenHelper.getWritableDatabase();if (db.isOpen()) {db.delete("person", "name=?", new String[] { name });db.close();}}/** * 数据库的更改操作 */public void update(String name, String newname, int newage) {SQLiteDatabase db = dbOpenHelper.getWritableDatabase();if (db.isOpen()) {ContentValues values = new ContentValues();values.put("name", newname);values.put("age", newage);db.update("person", values, "name=?", new String[] { name });db.close();}}/** * 数据库的查询操作 */public boolean find(String name) {boolean result = false;SQLiteDatabase db = dbOpenHelper.getReadableDatabase();if (db.isOpen()) {// select * from personCursor cursor = db.query("person", null, "name=?",new String[] { name }, null, null, null);if (cursor.moveToFirst()) {result = true;}cursor.close();db.close();}return result;}/** * 查询所有信息 */public List<Person> findAll() {List<Person> persons = null;SQLiteDatabase db = dbOpenHelper.getReadableDatabase();if (db.isOpen()) {//得到游标.Cursor cursor = db.query("person", null, null, null, null, null,null);persons = new ArrayList<Person>();while (cursor.moveToNext()) {Person person = new Person();String name = cursor.getString(cursor.getColumnIndex("name"));person.setName(name);int age = cursor.getInt(cursor.getColumnIndex("age"));person.setAge(age);persons.add(person);}cursor.close();db.close();}return persons;}/** * 查询所有信息,游标查询的话必须用rawQuery. */public Cursor findAllbyCursor() {SQLiteDatabase db = dbOpenHelper.getReadableDatabase();if (db.isOpen()) {/*Cursor cursor = db.query("person", null, null, null, null, null,null);*/

//select personid as _id,是给personid起了一个别名.游标查询适配器的时候不用_id容易出问题.Cursor cursor = db.rawQuery("select personid as _id,age,name from person", null);return cursor;// 注意了  一定不要把数据库 关闭了 }return null;}/** * 银行转账的方法 */public void transaction() {SQLiteDatabase db = dbOpenHelper.getWritableDatabase();if (db.isOpen()) {try {// 一共四步.1开启数据库的事务db.beginTransaction();//2执行语句// 给张三设置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" });// 出现了异常// 把张三的钱给李四//初始化李四账户 为 0db.execSQL("update person set account=? where name=?",new Object[] { 0, "lisi" });db.execSQL("update person set account=account+? where name=?",new Object[] { 200, "lisi" });//3设置事务的状态db.setTransactionSuccessful();}// 显示的设置事务是否成功catch (Exception e) {// TODO: handle exception} finally {//4结束事务db.endTransaction();db.close();}}}}