这一节讲述一下Android中有关轻量级数据库SQLite的操作,主要正对该数据库的增删查改进行一些举例说明。
首先,得建一个数据库帮助类,用于建立数据库以及进行数据库方面的一些操作,该类必须继承自android.database.sqlite.SQLiteOpenHelper,重载两个方法,一个是onCreate()用于在数据库第一次创建的时候的操作,onUpgrade()用于在数据库版本更新时的操作
public class DBHelper extends SQLiteOpenHelper {
public static final String DB_NAME="yuchao";
public static final int DB_VERSION = 2;
public DBHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}
public void onCreate(SQLiteDatabase db) {//创建数据库时的操作
db.execSQL("CREATE TABLE person(id Integer primary key autoincrement,name varchar(20),amount Integer)");
}
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// 数据库版本号变更时执行
db.execSQL("drop table person");
onCreate(db);
}
}
然后我们建立一个PersonbService类,用来说明增删查改时的具体操作
增
public void save(Person person) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.execSQL("insert into person(name,amount) values (?,?)",
new Object[] { person.getName(), person.getAmount() });
}
删
public void delete(Integer id) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.execSQL("delete from person where id=?",
new Object[] { id.toString() });
}
查
public Person find(Integer id) {
Person person = null;
SQLiteDatabase db = dbHelper.getWritableDatabase();
Cursor cursor = db.rawQuery("select id,name from person where id=?",
new String[] { id.toString() });
if (cursor.moveToFirst()) {
String name = cursor.getString(cursor.getColumnIndex("name"));
person = new Person(id, name);
}
return person;
}
改
public void update(Person person) {//包含事务的操作
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.beginTransaction();
try{
db.execSQL("update person set name=? where id=?",
new Object[] { person.getName(), person.getId().toString() });
db.setTransactionSuccessful
();
}finally{
db.endTransaction();
}
}
查某一范围的人
返回游标
public Cursor getScollCursor(Integer offset, Integer maxNum) {
SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor cursor = db.rawQuery(
"select id as _id,name,amount from person limit ?,?", new
String[] {offset.toString(), maxNum.toString() });
return cursor;
}
返回所有人
public List<Person> getScoll(Integer offset, Integer maxNum) {
List<Person> persons = new ArrayList<Person>();
SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor cursor = db.rawQuery(
"select id,name,amount from person limit ?,?", new String[] {
offset.toString(), maxNum.toString() });
while (cursor.moveToNext()) {
int id = cursor.getInt(cursor.getColumnIndex("id"));
String name = cursor.getString(cursor.getColumnIndex("name"));
Integer amount = cursor.getInt(cursor.getColumnIndex("amount"));
Person person = new Person(id, name);
person.setAmount(amount);
persons.add(person);
}
return persons;
}
到此,有关数据库的大体操作已经实现!