1. 创建实体类Person
public class Person {
public int personId;
public String name;
public String sex;
public int age;
public String phone;
public Person() { }
public Person(String name, String sex, int age) {
this.name = name;
this.sex = sex;
this.age = age;
}
public Person(String name, String sex, int age, String phone) {
this(name, sex, age);
this.phone = phone;
}
public Person(int personId, String name, String sex, int age, String phone) {
this(name, sex, age, phone);
this.personId = personId;
}
}
2. 书写DBOpenHelper类继承SQLiteOpenHelper
public class DBOpenHelper extends SQLiteOpenHelper {
public DBOpenHelper(Context context) {
super(context, "myDB.db", null, 2);
//保存在<包>/data/data/databases
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
//数据库每一次调用时创建
sqLiteDatabase.execSQL("create table person(" + "personid integer primary key autoincrement, name varchar(20), " + "sex varchar(10), age integer)");
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i2) {
//数据库版本改变时执行
sqLiteDatabase.execSQL("alter table person add phone varchar(12) null");
}
}
3. 书写服务类完成增删改查
public class PersonService {
private DBOpenHelper dbOpenHelper;
public PersonService(Context context) {
this.dbOpenHelper = new DBOpenHelper(context);
}
public void save(Person person) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", person.name);
values.put("sex", person.sex);
values.put("age", person.age);
values.put("phone", person.phone);
db.insert("person", null, values);
}
public void delete(int personId) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.delete("person", "personid=?", new String[]{String.valueOf(personId)});
}
public void update(Person person) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", person.name);
values.put("sex", person.sex);
values.put("age", person.age);
values.put("phone", person.phone);
db.update("person", values, "personid=?", new String[]{String.valueOf(person.pers onId)});
}
public Person find(int personId) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
Cursor cursor = db.query("person", null, "personid=?", new String[]{String.valueOf( personId)}, null, null, null);
Person person = null;
while(cursor.moveToNext()) {
int id = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String sex = cursor.getString(cursor.getColumnIndex("sex"));
int age = cursor.getInt(cursor.getColumnIndex("age"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
person = new Person(id, name, sex, age, phone);
}
return person;
}
public List<Person> getScrollData(int offset, int maxResult) {
List<Person> persons = new ArrayList<Person>();
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
Cursor cursor = db.query("person", null, null, null, null, null, "personid asc", offset + ", " + maxResult);
while(cursor.moveToNext()) {
int id = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String sex = cursor.getString(cursor.getColumnIndex("sex"));
int age = cursor.getInt(cursor.getColumnIndex("age"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
persons.add(new Person(id, name, sex, age, phone));
}
return persons;
}
public long getCount() {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
Cursor cursor = db.query("person", new String[]{"count(*)"}, null, null, null, null, null); cursor.moveToFirst();
long result = cursor.getLong(0);
return result;
}
}