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" />

    <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方法 

     

        会在上面的包下创建一个数据库person

       然后测试    testSave方法 ，会在这张表中插入三条数据。

       最后测试  testFindAll方法  ，接着看一下logcat控制台打印的信息：

     

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