Android SQLite数据库 实现 创建表 和增删改查分页

PersonServiceDB类内容

public class PersonServiceDB extends SQLiteOpenHelper {public PersonServiceDB(Context context) {super(context, "itcast.db", null, 2); //第二个参数是设置数据库名，第四个是设置版本号// TODO Auto-generated constructor stub}@Overridepublic void onCreate(SQLiteDatabase db) {//创建数据库时调用// TODO Auto-generated method stubdb.execSQL("create table person(personid integer primary key autoincrement,name varchar(20))");}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {//版本号被修改时调用// TODO Auto-generated method stub db.execSQL("ALTER TABLE person ADD phone varchar(20) NULL");}}

PersonServiceImpl类内容

public class PersonServiceImpl implements PersonService {PersonServiceDB db = null;public PersonServiceImpl(Context context) {this.db = new PersonServiceDB(context);}@Overridepublic void save(Person person) {SQLiteDatabase database = db.getWritableDatabase();database.execSQL("insert into person(name,phone) values(?,?)",new Object[] { person.getName(), person.getPhone() });}@Overridepublic void delet(Person person) {SQLiteDatabase database = db.getWritableDatabase();database.execSQL("delete from person where personid=?",new Object[] { person.getPersonid() });}@Overridepublic void update(Person person) {SQLiteDatabase database = db.getWritableDatabase();database.execSQL("update person set name=?,phone=? where personid=?",new Object[] { person.getName(), person.getPhone(),person.getPersonid() });}@Overridepublic Person find(Integer id) {SQLiteDatabase database = db.getReadableDatabase();Cursor cursor = database.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;}@Overridepublic List<Person> getScrollData(int offset, int maxResult) {SQLiteDatabase database = db.getReadableDatabase();List<Person> persons = new ArrayList<Person>();Cursor cursor = database.rawQuery("select * from person limit ?,?", new String[] {String.valueOf(offset), String.valueOf(maxResult) });while (cursor.moveToNext()) {persons.add(new Person(cursor.getInt(cursor.getColumnIndex("personid")), cursor.getString(cursor.getColumnIndex("name")), cursor.getString(cursor.getColumnIndex("phone"))));}cursor.close();return persons;}@Overridepublic long getCount() {SQLiteDatabase database = db.getReadableDatabase();Cursor cursor = database.rawQuery("select count(*) from person", null);cursor.moveToFirst();return cursor.getLong(0);}}

PersonTest类，测试类

public class PersonTest extends AndroidTestCase {public void androidTest() throws Exception {PersonServiceDB personServiceDB = new PersonServiceDB(getContext());personServiceDB.getWritableDatabase();}public void tt() throws Exception {PersonServiceImpl impl = new PersonServiceImpl(this.getContext());for (int i = 2; i <22 ; i++) {Person person = new Person();person.setName("libo"+i);person.setPhone("2122222");impl.save(person);}}public void find() throws Exception {PersonServiceImpl impl = new PersonServiceImpl(this.getContext());Person person = impl.find(1);Log.i("find", person.getName());}public void delete() throws Exception {PersonServiceImpl impl = new PersonServiceImpl(this.getContext());Person person = impl.find(22);impl.delet(person);}public void update() throws Exception {PersonServiceImpl impl = new PersonServiceImpl(this.getContext());Person person = impl.find(1);person.setName("sadad");impl.update(person);}public void getScrollData() throws Exception {PersonServiceImpl impl = new PersonServiceImpl(this.getContext());List<Person> persons = impl.getScrollData(3, 10);for (Person person : persons) {Log.i("test", person.getPersonid()+"--"+person.getName());}}public void getCount() throws Exception {PersonServiceImpl impl = new PersonServiceImpl(this.getContext());long count = impl.getCount();Log.i("test", String.valueOf(count));}}