android 数据库基础用法

1.文件中查找到自己要用到的数据库

public class DBOpenHelper extends SQLiteOpenHelper {	// 定义工具类, 继承SQLiteOpenHelper
	
	public DBOpenHelper(Context context) {				// 创建对象的时候, 需要传入上下文环境
		super(context, "itcast.db", null, 4);
		/*
		 * 由于父类没有无参构造函数, 必须显式调用有参的构造函数
		 * 参数1: 上下文环境, 用来确定数据库文件存储的目录
		 * 参数2: 数据库文件的名字
		 * 参数3: 生成游标的工厂, 填null就是使用默认的
		 * 参数4: 数据库的版本, 从1开始
		 */
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		System.out.println("onCreate");
		db.execSQL("CREATE TABLE person(id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20))");		// 执行SQL语句, 创建表
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		System.out.println("onUpgrade");
		db.execSQL("ALTER TABLE person ADD balance INTEGER");
	}

}

包括初始创建和更新。

2.android对数据库的增删改查

public class PersonDao {
	private DBOpenHelper helper;
	
	public PersonDao(Context context) {
		helper = new DBOpenHelper(context);
	}

	public void insert(Person p) {
		SQLiteDatabase db = helper.getWritableDatabase();	// 获取数据库连接(可写的)
		db.execSQL("INSERT INTO person(name, balance) VALUES(?, ?)", new Object[] { p.getName(), p.getBalance() });		// 执行SQL语句, 插入
		db.close();
	}
	
	public void delete(int id) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("DELETE FROM person WHERE id=?", new Object[] { id });
		db.close();
	}

	public void update(Person p) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("UPDATE person SET name=?, balance=? WHERE id=?", new Object[] { p.getName(), p.getBalance(), p.getId() });
		db.close();
	}
	
	public Person query(int id) {
		SQLiteDatabase db = helper.getReadableDatabase();			// 获取数据库连接(可读的)
		Cursor c = db.rawQuery("SELECT name, balance FROM person WHERE id=?", new String[] { id + "" });	// 执行SQL语句, 查询, 得到游标
		Person p = null;
		if (c.moveToNext()) {										// 判断游标是否包含下一条记录, 如果包含将游标向后移动一位
			String name = c.getString(c.getColumnIndex("name"));	// 获取"name"字段的索引, 然后根据索引获取数据
			int balance = c.getInt(1);								// 获取1号索引上的数据
			p = new Person(id, name, balance);	
		}
		c.close();
		db.close();
		return p;
	}
	
	public List<Person> queryAll() {
		SQLiteDatabase db = helper.getReadableDatabase();					
		Cursor c = db.rawQuery("SELECT id, name, balance FROM person", null);	
		List<Person> persons = new ArrayList<Person>();
		while (c.moveToNext()) {										
			Person p = new Person(c.getInt(0), c.getString(1), c.getInt(2));
			persons.add(p);
		}
		c.close();
		db.close();
		return persons;
	}
	
	public int queryCount() {
		SQLiteDatabase db = helper.getReadableDatabase();					
		Cursor c = db.rawQuery("SELECT COUNT(*) FROM person", null);	
		c.moveToNext();
		int count = c.getInt(0);
		c.close();
		db.close();
		return count;
	}
	
	public List<Person> queryPage(int pageNum, int capacity) {		
		String offset = (pageNum - 1) * capacity + "";		// 偏移量
		String len = capacity + "";							// 个数
		SQLiteDatabase db = helper.getReadableDatabase();					
		Cursor c = db.rawQuery("SELECT id, name, balance FROM person LIMIT ?,?", new String[]{offset , len});	
		List<Person> persons = new ArrayList<Person>();
		while (c.moveToNext()) {										
			Person p = new Person(c.getInt(0), c.getString(1), c.getInt(2));
			persons.add(p);
		}
		c.close();
		db.close();
		return persons;
	}
	
}