sqlite实例-CSDN博客

SQLite是Android平台软件开发中会经常用到的数据库产品，作为一款轻型数据库,SQLite的设计目标就是是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够。

废话不多说，让我们来写一个sqlite的demo。

首先建立一个项目目录如下：

在这里为了便于测试，我用了junit测试，要想用junit测试，首先要在在AndroidManifest.xml中加入下面粗体部分的代码：

<manifest xmlns:android="http://schemas.android.com/apk/res/android" package="cn.com.karl.db" android:versionCode="1" android:versionName="1.0" > <uses-sdk android:minSdkVersion="8" /> <application android:icon="@drawable/ic_launcher" android:label="@string/app_name" > <uses-library android:name="android.test.runner" /> <activity android:label="@string/app_name" android:name=".DbDemoActivity" > <intent-filter > <action android:name="android.intent.action.MAIN" /> <category android:name="android.intent.category.LAUNCHER" /> </intent-filter> </activity> </application> <instrumentation android:name="android.test.InstrumentationTestRunner" android:targetPackage="cn.com.karl.db" android:label="Tests for My App" /> </manifest>
接下来在service包下建立DBOpenHandler类继承SQLiteOpenHelper

public class DBOpenHandler extends SQLiteOpenHelper { public DBOpenHandler(Context context) { super(context, "person.db", null, 1); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL("create table person(id integer primary key autoincrement,name varchar(20))"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub } }
然后编写我们的增删改查的核心类

public class PersonService { private DBOpenHandler dbOpenHandler; public PersonService(Context context){ this.dbOpenHandler=new DBOpenHandler(context); } public void save(Person person){ SQLiteDatabase db=dbOpenHandler.getWritableDatabase(); db.execSQL("insert into person (name) values(?)", new Object[]{person.getName()}); } public void delete(Integer id){ SQLiteDatabase db=dbOpenHandler.getWritableDatabase(); db.execSQL("delete from person where id=", new Object[]{id.toString()}); } public void update(Person person){ SQLiteDatabase db=dbOpenHandler.getWritableDatabase(); db.execSQL("update person set name=? where" + " id=?", new Object[]{person.getName(),person.getId()}); } public Person find(Integer id){ Person person=null; SQLiteDatabase db=dbOpenHandler.getReadableDatabase(); Cursor cursor=db.rawQuery("select * from person where id=?", new String[]{id.toString()}); if(cursor.moveToFirst()) { person=new Person(); person.setId(cursor.getInt(cursor.getColumnIndex("id"))); person.setName(cursor.getString(cursor.getColumnIndex("name"))); } return person; } public List<Person> findAll(Integer offset,Integer maxLength){ List<Person> lists=new ArrayList<Person>(); Person person=null; SQLiteDatabase db=dbOpenHandler.getReadableDatabase(); Cursor cursor=db.rawQuery("select * from person limit ?,?", new String[]{offset.toString(),maxLength.toString()}); while(cursor.moveToNext()){ person=new Person(); person.setId(cursor.getInt(cursor.getColumnIndex("id"))); person.setName(cursor.getString(cursor.getColumnIndex("name"))); lists.add(person); } return lists; } public long getCount(){ SQLiteDatabase db=dbOpenHandler.getReadableDatabase(); Cursor cursor=db.rawQuery("select count(*) from person ", null ); cursor.moveToFirst(); return cursor.getLong(0); } } 在domain包下建立person类

public class Person { private int id; private String name; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } } 最后是测试类：

public class DBOpenHandlerTest extends AndroidTestCase { private static final String TAG = "DBOpenHandlerTest"; public void testCreate(){ DBOpenHandler dbHandler=new DBOpenHandler(this.getContext()); dbHandler.getWritableDatabase(); } public void testSave() throws Throwable{ PersonService personService = new PersonService(this.getContext()); Person person = new Person(); person.setName("xiaoming"); personService.save(person); person = new Person(); person.setName("xiaoli"); personService.save(person); person = new Person(); person.setName("xiaowang"); personService.save(person); person = new Person(); person.setName("xiaozhang"); personService.save(person); } public void testUpate() throws Throwable{ PersonService personService = new PersonService(this.getContext()); Person person = personService.find(1); person.setName("lili"); personService.update(person); } public void testDelete() throws Throwable{ PersonService personService = new PersonService(this.getContext()); personService.delete(1); } public void testFind() throws Throwable{ PersonService personService = new PersonService(this.getContext()); Person person = personService.find(1); Log.i(TAG, person.getName()); } public void testFindAll() throws Throwable{ PersonService personService = new PersonService(this.getContext()); List<Person> persons = personService.findAll(0, 3); for(Person person : persons){ Log.i(TAG, person.getName()); } } public void testGetCount() throws Throwable{ PersonService personService = new PersonService(this.getContext()); Log.i(TAG, personService.getCount()+""); } } 先测试testCreate方法