安卓学习：（6）安卓操作sqlite数据库

1.编写DataBaseOpenHelper公共类

package com.smart.service;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;public class DataBaseOpenHelper extends SQLiteOpenHelper {// 数据名称，private static final String DBNAME = "smrtDataBase";// 数据库版本private static final int version = 1;// 构造方法参数，public DataBaseOpenHelper(Context context) {super(context, DBNAME, null, version);}// 数据库创建表的名子。@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL("CREATE TABLE person (personid integer primary key autoincrement,name varchar(20),age INTEGER)");}// 更新方法@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {db.execSQL("EROP TABLE IF EXISTS person");onCreate(db);}}

2.编写Person实体类

package com.smart.domain;public class Person {@Overridepublic String toString() {return "personid=" + personid + ",name=" + name + ",age=" + age;}public int personid;public String name;public Short age;public int getPersonid() {return personid;}public void setPersonid(int personid) {this.personid = personid;}public String getName() {return name;}public void setName(String name) {this.name = name;}// 增加一个构造器public Person(int personid, String name, Short age) {super();this.personid = personid;this.name = name;this.age = age;}// 创建构造器public Person(String name, short age) {this.name = name;this.age = age;}public Short getAge() {return age;}public void setAge(Short age) {this.age = age;}}

3.编写PersonService类

package com.smart.service;import java.util.ArrayList;import java.util.List;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import com.smart.domain.Person;public class PersonService {/** * 这里是处理业务的方法，和JAVA叫做实现类 如果用上下文打开数据库，一定要关闭数据库 * */private DataBaseOpenHelper dbOpenHelper;public PersonService(Context context) {dbOpenHelper = new DataBaseOpenHelper(context);}public void save(Person person) {SQLiteDatabase database = dbOpenHelper.getWritableDatabase();database.execSQL("insert into person(name,age) values(?,?)",new Object[] { person.getName(), person.getAge() });// database.close();}public void update(Person person) {SQLiteDatabase database = dbOpenHelper.getWritableDatabase();database.execSQL("update person set name=?,age=? where personid=?",new Object[] { person.getName(), person.getAge(),person.getPersonid() });}public Person find(Integer id) {SQLiteDatabase database = dbOpenHelper.getWritableDatabase();Cursor cursor = database.rawQuery("select * from person where personid=?",new String[] { String.valueOf(id) });if (cursor.moveToNext()) {return new Person(cursor.getInt(0), cursor.getString(1),cursor.getShort(2));}return null;}public void delete(Integer... ids) {if (ids.length > 0) {StringBuilder sb = new StringBuilder();for (Integer id : ids) {sb.append("?").append(",");}sb.deleteCharAt(sb.length() - 1);SQLiteDatabase database = dbOpenHelper.getWritableDatabase();database.execSQL("delete from person where personid in(" + sb + ")",(Object[]) ids);}}// 分页代码public List<Person> getScrollData(int startResult, int maxResult) {List<Person> persons = new ArrayList<Person>();SQLiteDatabase database = dbOpenHelper.getWritableDatabase();Cursor cursor = database.rawQuery("select * from person limit ?,?",new String[] { String.valueOf(startResult),String.valueOf(maxResult) });while (cursor.moveToNext()) {persons.add(new Person(cursor.getInt(0), cursor.getString(1),cursor.getShort(2)));}return persons;}// 取得所有的条数public long getCount() {SQLiteDatabase database = dbOpenHelper.getWritableDatabase();Cursor cursor = database.rawQuery("select * from person ", null);if (cursor.moveToNext()) {return cursor.getLong(0);}return 0;}}

4.编写PersonServiceTest测试类

package com.smart.dh;import java.util.Iterator;import java.util.List;import android.test.AndroidTestCase;import android.util.Log;import com.smart.domain.Person;import com.smart.service.PersonService;public class PersonServiceTest extends AndroidTestCase {private static final String TAG = "PersonServiceTest";// 保存数据。public void testSave() throws Exception {PersonService personService = new PersonService(this.getContext());// personService.save(new Person("老梁",(short)23));for (int i = 0; i < 10; i++) {personService.save(new Person("llb" + i, (short) (i + 1)));}}// 查询public void testFind() throws Exception {PersonService personService = new PersonService(this.getContext());Person person = personService.find(1);Log.i(TAG, person.toString());// personService.save(new Person("老梁",(short)23));}// 更新语句public void testUpdate() throws Exception {PersonService personService = new PersonService(this.getContext());Person person = personService.find(1);person.setName("smart");personService.update(person);Log.i(TAG, person.toString());}// 获得所有的条数public void testGetCount() throws Exception {PersonService personService = new PersonService(this.getContext());Log.i(TAG, String.valueOf(personService.getCount()));}// 分页功能public void testGetScrollData() throws Exception {PersonService personService = new PersonService(this.getContext());List<Person> persons = personService.getScrollData(0, 20);// 从0条到20条的数据for (Person person : persons) {Log.i(TAG, person.toString());}}public void testDelete() throws Exception {PersonService personService = new PersonService(this.getContext());personService.delete(1, 2, 3);// 删除1.2.3三条记录}}

5.执行结果