android基础--使用嵌入式关系型SQLite数据库存储数据

public class DBOpenHelper extends SQLiteOpenHelper {

 

    public DBOpenHelper(Context context) {

       super(context, "test.db", null, 2);

    }

 

    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");

    }

 

}

 

测试：

public void testCreateDB () throws Throwable{

       DBOpenHelper dbOpenHelper = new DBOpenHelper(getContext());

       dbOpenHelper.getWritableDatabase();//数据库文件test.db放的位置：<当前包>/databases

    }

增删改查的业务service

public class PersonService {
	private DbOpenHelper dbOpenHelper;

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

	public void save(Person p) {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		String sql = "insert into person(name,phone) values(?,?)";
		Object[] objs = new Object[] { p.getName(), p.getPhone() };
		db.execSQL(sql, objs);
	}

	public void update(Person p) {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		String sql = "update person set name = ?, phone = ? where personid = ?";
		Object[] objs = new Object[] { p.getName(), p.getPhone(), p.getId() };
		db.execSQL(sql, objs);
	}

	public void del(int id) {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		String sql = "delete from person where personid = ?";
		Object[] objs = new Object[] { id };
		db.execSQL(sql, objs);
	}

	public Person find(int id) {
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		String sql = "select * from person where personid = ?";
		String[] strs = { String.valueOf(id) };
		Cursor cursor = db.rawQuery(sql, strs);
		if (cursor.moveToFirst()) {
			int personid = cursor.getInt(cursor.getColumnIndex("personid"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			String phone = cursor.getString(cursor.getColumnIndex("phone"));
			cursor.close();
			return new Person(personid, name, phone);
		}
		return null;
	}

	public List<Person> getScroll(int offset, int maxResult) {
		List<Person> persons = new ArrayList<Person>();
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		String sql = "select * from person order by personid asc limit ?,?";
		String[] strs = { String.valueOf(offset), String.valueOf(maxResult) };
		Cursor cursor = db.rawQuery(sql,strs);
		while(cursor.moveToNext()){
			int personid = cursor.getInt(cursor.getColumnIndex("personid"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			String phone = cursor.getString(cursor.getColumnIndex("phone"));
			persons.add(new Person(personid, name, phone));
		}
		cursor.close();
		return persons;
	}

	public long getcount() {
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		String sql = "select count(*) from person";
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToFirst();
		return cursor.getLong(0);
	}
}

测试

public class DbTest extends AndroidTestCase{
	public void testCreate()throws Throwable{
		DbOpenHelper dbhelp = new DbOpenHelper(getContext());
		dbhelp.getWritableDatabase();
	}
	public void testSave()throws Throwable{
		PersonService service = new PersonService(getContext());
		for(int i = 0;i<20;i++){
			Person p = new Person("qq"+i, "100000"+i);
			service.save(p);
		}
	}
	public void testDel() throws Throwable{
		PersonService service = new PersonService(getContext());
		service.del(22);
	}
	public void testFidn()throws Throwable{
		PersonService service = new PersonService(getContext());
		Person p = service.find(2);
		Log.i("find", p.getName()+p.getPhone());
	}
	public void testUpdate()throws Throwable{
		PersonService service = new PersonService(getContext());
		Person p = service.find(2);
		p.setName("update");
		p.setPhone("5432463653");
		service.update(p);
	}
	public void testCount()throws Throwable{
		PersonService service = new PersonService(getContext());
		Log.i("count", service.getcount()+"");
	}
	public void testScrolldata() throws Throwable{
		PersonService service = new PersonService(getContext());
		List<Person> persons = service.getScroll(12, 8);
		for(Person p : persons){
			Log.i("Person", p.toString());
		}
	}
}

数据库事务：

SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		
		db.beginTransaction();
		try{
			db.execSQL("update person set amount=amount-10 where personid=1");
			db.execSQL("update person set amount=amount+10 where personid=2");
			db.setTransactionSuccessful();
		}finally{
			//事务标志的默认值为false
			db.endTransaction();//提交、回滚，由事务标志决定，如果事务标志为True(成功)，提交,否则回滚
		}