Sqlite3 增删改查操作实例

(1) 在android中使用sqlite数据库,首先需要了解SQLiteOpenHerper这个类, 是用来实现数据库初始化的一个类,我们需要继承这个类,初始化我们的数据库:

DBOpenHelper.java

package com.xiaoming.domain;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;public class DBOpenHelper extends SQLiteOpenHelper{public DBOpenHelper(Context context){super(context, "sqlite.db", null, 1);}@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL("create table persons (_id integer primary key autoincrement," +"name varchar null," +"age int null);" );}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stub}}

(2)然后我们需要一个业务类,来操作我们的数据库中的内容. 而且可能有很多种业务类, 对数据库中不同的表进行操作,我们这里写一个PersonService业务类, 其所用到的数据类是Person:

Person:

package com.xiaoming.domain;public class Person {private String  name;private Integer age;private Integer personid;public Person() {    name= null;age      = null;personid = null;}public Person(Integer personid,String name, int age) {this.personid = personid;this.name = name;this.age = age;}public Person(String name, int age) {this.personid = null;this.name = name;this.age = age;}public Integer getPersonid() {return personid;}public void setPersonid(Integer personid) {this.personid = personid;}public String getName() {return name;}public void setName(String name) {this.name = name;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}}

PersonService:

package com.xiaoming.service;import java.util.ArrayList;import java.util.List;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import com.xiaoming.domain.DBOpenHelper;import com.xiaoming.domain.Person;public class PersonService {Context      context = null;DBOpenHelper dbOpenHelper = null;public PersonService(Context context) {this.context = context;dbOpenHelper = new DBOpenHelper( context );}/** * 添加记录 * @param p */public void insert(Person p){SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.execSQL("insert into persons(name,age) values(?,?);",new Object[]{p.getName(),p.getAge()});}/** * 删除记录 * @param id */public void delete(Integer id){SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.execSQL("delete from persons where _id=?;",new Object[]{id});}/** * 更新记录 * @param p */public void update(Person p){SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.execSQL("update persons set name=?,age=? where _id=?",new Object[]{p.getName(),p.getAge(),p.getPersonid()} );}/** * 查找记录 * @param id * @return */public Person find(Integer id){SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.rawQuery("select * from persons where _id=?", new String[]{id.toString()}) ;if( cursor.moveToFirst() ){int personid = cursor.getInt(cursor.getColumnIndex("_id"));String name  = cursor.getString(cursor.getColumnIndex("name"));int age      = cursor.getInt(cursor.getColumnIndex("age"));return new Person(personid,name,age);}cursor.close();return null;}/** * 分页查找记录 * @param offset * @param maxResult * @return */public List<Person> getScrollData(int offset, int maxResult){List<Person> personlist = new ArrayList<Person>();SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.rawQuery("select * from persons order by _id asc limit ?,? ",new String[]{String.valueOf(offset), String.valueOf(maxResult)}) ;while( cursor.moveToNext() ){int personid = cursor.getInt(cursor.getColumnIndex("_id"));String name  = cursor.getString(cursor.getColumnIndex("name"));int age      = cursor.getInt(cursor.getColumnIndex("age"));personlist.add(new Person(personid,name,age) );}cursor.close();return personlist;}/** * 得到记录数 * @return */public long getCount(){SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.rawQuery("select count(*) from persons",null );cursor.moveToFirst();long result = cursor.getLong(0);return result;}}

(3)然后需要写一个测试类PersonServiceTest:

package com.xiaoming.test;import java.util.ArrayList;import java.util.List;import com.xiaoming.domain.DBOpenHelper;import com.xiaoming.domain.Person;import com.xiaoming.service.PersonService;import android.test.AndroidTestCase;import android.util.Log;public class PersonServiceTest extends AndroidTestCase {private static final String TAG = "PersonServiceTest";public void testInsert(){PersonService pService = new PersonService(getContext());Person p = new Person("王强",40);Person p1 = new Person("小花",40);Person p2 = new Person("小狗",40);Person p3 = new Person("小猫",40);Person p4 = new Person("自傲做",40);Person p5 = new Person("我晕哦",40);Person p6 = new Person("么得",40);Person p7 = new Person("张建",40);pService.insert(p1);pService.insert(p2);pService.insert(p3);pService.insert(p4);pService.insert(p5);pService.insert(p6);pService.insert(p7);}public void testdelete(){PersonService pService = new PersonService(getContext());pService.delete(1);}public void testUpdate(){PersonService pService = new PersonService(getContext());Person p = pService.find(5);if( p ==  null ){Log.i(TAG,"id="+p.getPersonid()+"的人没有找到");return ;}p.setName("哈哈哈哈");pService.update(p);}public void testFind(){PersonService pService = new PersonService(getContext());Person p = pService.find(2);Log.i(TAG,"_id="+p.getPersonid()+", name="+p.getName()+", age="+p.getAge());}public void testGetScrollData(){PersonService pService = new PersonService(getContext());ArrayList<Person> pList = (ArrayList<Person>) pService.getScrollData(1, 5);for(Person p:pList){Log.i(TAG,"_id="+p.getPersonid()+", name="+p.getName()+", age="+p.getAge());}}public void testGetCount(){PersonService pService = new PersonService(getContext());long  count = pService.getCount();Log.i(TAG,""+count);}}