sqlite实例

玩龙天子

　　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" /><applicationandroid:icon="@drawable/ic_launcher"android:label="@string/app_name" > <uses-library android:name="android.test.runner" /><activityandroid: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);}@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL("create table person(id integer primary key autoincrement,name varchar(20))");}@Overridepublic 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方法

　　会在上面的包下创建一个数据库person
　　然后测试 testSave方法 ，会在这张表中插入三条数据。
　　最后测试 testFindAll方法 ，接着看一下logcat控制台打印的信息：

　　OK！ 已经打印出来了，其他测试不再复述。