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,new String[] { name });
			db.close();
		}
	}

	/**
	 * 数据库的查询操作
	 */
	public boolean find(String name) {
		boolean result = false;
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		if (db.isOpen()) {
			// select * from person
			Cursor cursor = db.query("person",new String[] { name },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);
			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);*/

//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" });
				// 出现了异常
				// 把张三的钱给李四
				//初始化李四账户 为 0
				db.execSQL("update person set account=? where name=?",new Object[] { 0,"lisi" });
				db.execSQL("update person set account=account+? where name=?","lisi" });
				//3设置事务的状态
				db.setTransactionSuccessful();
			}
			// 显示的设置事务是否成功
			catch (Exception e) {
				// TODO: handle exception
			} finally {
			//4结束事务
				db.endTransaction();
				db.close();
			}
		}
	}
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!