Sqlite3 增删改查操作实例
jopen
10年前
1) 在android中使用sqlite数据库,首先需要了解SQLiteOpenHerper这个类, 是用来实现数据库初始化的一个类,我们需要继承这个类,初始化我们的数据库:
DBOpenHelper.java
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); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL("create table persons (_id integer primary key autoincrement," + "name varchar null," + "age int null);" ); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub } }
Person:
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:
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:
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); } }