android对sqlite数据库操作(创建 增 删 改 查)

操作sqlite数据库第一种方法execSql()  rawQuery () 第二种方法:insert() delete() update()  query()

/**
 * 通过继承SqliteOpenHelper来创建一个数据库
 * @author Administrator
 *
 */
public class DbOpenhelper extends SQLiteOpenHelper
{
	private static String DATABASENAME = "secn.db";
	private static int DATABASEVERSION = 2;
	

	/**
	 * (Context context, String name, CursorFactory factory,int version)
	 * @param context 上下文对象
	 * @param name 数据库名称 secb.db
	 * @param factory  游标工厂
	 * @param version 数据库版本
	 */
	public DbOpenhelper(Context context)
	{
		super(context, DATABASENAME, null, DATABASEVERSION);
	}
	
	/**数据库第一次被使用时创建数据库
	 * @param db 操作数据库的
	 */
	public void onCreate(SQLiteDatabase db)
	{
		//执行有更新行为的sql语句
		db.execSQL("CREATE Table person (personid integer primary key autoincrement, name varchar(20), amount integer,age integer)");
	}

	/**数据库版本发生改变时才会被调用,数据库在升级时才会被调用;
	 * @param db 操作数据库
	 * @param oldVersion 旧版本
	 * @param newVersion 新版本
	 */
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
	{
		db.execSQL("drop table if exists person");
		onCreate(db);
	}

}

 

 

 

/**
 * 对Person对象的sql操作(增删改查)
 * 
 * @author Administrator
 * 
 */
public class PersonService
{

	private DbOpenhelper dbOpenHelper;

	public PersonService(Context context)
	{
		dbOpenHelper = new DbOpenhelper(context);
	}

	/**
	 * 添加Person
	 * 
	 * @param person
	 */
	public void addPerson(Person person)
	{
		// 对读和写操作的方法
		// 如果当我们二次调用这个数据库方法,他们调用的是同一个数据库对象,在这里的方法创建的数据调用对象是用的同一个对象
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.execSQL("insert into Person(name,amount) values(?,?)", new Object[]
		{ person.getName(), person.getAmount() });
	}

	/**
	 * 修改Person
	 * 
	 * @param person
	 */
	public void modifyPerson(Person person)
	{
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.execSQL("update Person set name=? where personid=?", new Object[]
		{ person.getName(), person.getId() });
	}

	/**
	 * 删除Person
	 * 
	 * @param person
	 */
	public void deletePerson(Integer id)
	{
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.execSQL("delete from Person where personid=?", new Object[]
		{ id.toString() });
	}

	/**
	 * 根据person的Id查询Person对象
	 * 
	 * @param id
	 *            Person的ID
	 * @return Person
	 */
	public Person findPerson(Integer id)
	{
		// 只对读的操作的方法
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		// Cursor游标的位置,默认是0,所有在操作时一定要先cursor.moveToFirst()一下,定位到第一条记录
		// Cursor cursor =
		// db.rawQuery("select * from person Where personid=?",new
		// String[]{id.toString()});
		Cursor cursor = db.query("Person", new String[]
		{ "personid", "name", "amount" }, "personid=?", new String[]
		{ id.toString() }, null, null, null);
		if (cursor.moveToFirst())
		{
			int personId = cursor.getInt(cursor.getColumnIndex("personid"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			int amount = cursor.getInt(cursor.getColumnIndex("amount"));
			return new Person(personId, name, amount);
		}
		return null;
	}

	/**
	 * 返回Person对象的集合
	 * 
	 * @return List<Person>
	 */
	public List<Person> findPersonList(Integer start, Integer length)
	{
		List<Person> persons = new ArrayList<Person>();
		// 只对读的操作的方法
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select * from Person limit ?,?",
				new String[]
				{ start.toString(), length.toString() });
		cursor = db.query("Person", null, null, null, null, null, null, start
				+ "," + length);
		while (cursor.moveToNext())
		{
			int personId = cursor.getInt(cursor.getColumnIndex("personid"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			int amount = cursor.getInt(cursor.getColumnIndex("amount"));
			persons.add(new Person(personId, name, amount));
		}
		return persons;
	}

	/**
	 * 返回Person的记录总个数
	 * 
	 * @return
	 */
	public Long getCount()
	{
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select count(0) from Person ", null);
		// 这里必定有一条记录.所有不用判断,直接移到第一条.
		cursor.moveToFirst();
		// 这里只有一个字段时候 返回
		return cursor.getLong(0);
	}

	/**
	 * 操作一个事务
	 * 
	 * @return
	 */
	public String getTransaction()
	{
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		String success = "";
		db.beginTransaction();
		try
		{
			db.execSQL("update person set amount = amount+10 where personId=?",
					new Object[]
					{ 1 });
			db.execSQL("update person set amount = amount-10 where personId=?",
					new Object[]
					{ 2 });
			success = "success";
		} catch (Exception e)
		{
			success = "input";
		} finally
		{
			// db.setTransactionSuccessful();//设置事务标志为成功,当结束事务时就会提交事务
			db.endTransaction();
		}
		return success;
	}
}

 

 

 

public class SqliteTest extends AndroidTestCase
{
	/**
	 * 创建数据库及新建表
	 */
	public void testCreateSqllite()
	{
		DbOpenhelper db = new DbOpenhelper(this.getContext());
		// 第一次调用该方法会调用数据库
		db.getWritableDatabase();
	}

	/**
	 * 添加Person
	 */
	public void testSavePerson()
	{
		PersonService db = new PersonService(this.getContext());
		Person person = new Person();
		person.setName("LiMing");
		person.setAmount(888);
		db.addPerson(person);

	}

	/**
	 * 更新Person
	 */
	public void testUpdatePerson()
	{
		PersonService db = new PersonService(this.getContext());
		Person person = new Person();
		person.setId(1);
		person.setName("LiMingRen");
		person.setAmount(101);
		db.modifyPerson(person);

	}
	
	/**
	 * 删除Person
	 */
	public void testDeletePerson()
	{
		PersonService db = new PersonService(this.getContext());
		db.deletePerson(1);

	}

	/**
	 * 查询一条Person记录
	 */
	public void testPerson()
	{
		PersonService db = new PersonService(this.getContext());
		Person person = db.findPerson(2);
		Log.i("SqliteTest", person.toString());

	}

	/**
	 * 查询Person集合对象
	 */
	public void testPersonList()
	{
		PersonService db = new PersonService(this.getContext());
		List<Person> persons = db.findPersonList(0, 5);
		for (Person parson : persons)
		{
			Log.i("SqliteTest", parson.toString());
		}

	}
	/**
	 * 测试一个事务
	 */
	public void testgetTransaction(){
		PersonService db = new PersonService(this.getContext());
		String str = db.getTransaction();
		Log.i("SqliteTest", str);
	}
}

 

 

public class Person
{
	private int id;
	private String name;
	private int amount;

	public Person()
	{
	}

	public Person(int id, String name, int amount)
	{
		super();
		this.id = id;
		this.name = name;
		this.amount = amount;
	}

	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 int getAmount()
	{
		return amount;
	}

	public void setAmount(int amount)
	{
		this.amount = amount;
	}

	public String toString()
	{
		return "ID:"+id+" Name:" + name + " Amount:" + amount;
	}
}