一、创建数据库
SQLiteOpenHelper是Android提供的一个管理数据库的工具类,可用于管理数据库的创建和版本更新。用法为继承SQLiteOpenHelper类,重写onCreate()和onUpdate()方法。
public class DBOPenHelp extends SQLiteOpenHelper {
public DBOPenHelp(Context context) {
super(context, "data.db", null, 2);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE person(personid integer primary key autoincrement ,name varchar(20),phone varchar(12))");
}
//数据库的版本号改变时,调用该方法
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL("ALTER TABLE person ADD sex VARCHAR(2) NULL ");
}
}
二、数据库表项增删改查操作
synchronized SQLiteDatabase getReadableDatabase():以只读的方式打开数据库对象。
synchronized SQLiteDatabase getWritableDatabase():以读写的方式打开数据库。
public class PersonService {
private DBOPenHelp dbopenhelper;
public PersonService(Context context) {
this.dbopenhelper = new DBOPenHelp(context);
}
//添加记录
public void save(Person person) {
SQLiteDatabase db = dbopenhelper.getWritableDatabase();
db.execSQL("insert into person(name,phone) values(?,?)", new Object[] {
person.getName(), person.getPhone() });
db.close();
}
public void delete(Integer id) {
SQLiteDatabase db = dbopenhelper.getWritableDatabase();
db.execSQL("delete from person where personid=?", new Object[] { id });
}
public void update(Person person) {
SQLiteDatabase db = dbopenhelper.getWritableDatabase();
db.execSQL(
"update person set name=? ,phone=? where personid=?",
new Object[] { person.getName(), person.getPhone(),
person.getId() });
}
public Person find(Integer id) {
SQLiteDatabase db = dbopenhelper.getWritableDatabase();
Cursor cursor = db.rawQuery("select * from person where personid=?",
new String[] { id.toString() });
if (cursor.moveToFirst()) {
int personid = cursor.getInt(cursor.getColumnIndex("personid"));// 字段索引号
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
return new Person(personid, name, phone);
}
cursor.close();
return null;
}
public List<Person> getScrollData(int offset, int maxResult) {
List<Person> persons = new ArrayList<Person>();
SQLiteDatabase db = dbopenhelper.getWritableDatabase();
Cursor cursor = db.rawQuery(
"select * from person order by personid asc limit ? ,?",
new String[] { String.valueOf(offset),
String.valueOf(maxResult) });
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.getWritableDatabase();
Cursor cursor = db.rawQuery("select count(*) from person ", null);
cursor.moveToFirst();
long result = cursor.getLong(0);
cursor.close();
return result;
}
}