Android开发之SQLite数据操作

这一节讲述一下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;

}

到此，有关数据库的大体操作已经实现!