android中SQLite的使用总结，用excSQL和rawQuery方法实现一般得增删改查

1：androidmanifest.xml的内容

<?xml version="1.0" encoding="utf-8"?><manifest xmlns:android="http://schemas.android.com/apk/res/android"      package="cn.itcast.db"      android:versionCode="1"      android:versionName="1.0">    <application android:icon="@drawable/icon" android:label="@string/app_name">       <uses-library android:name="android.test.runner" />        <activity android:name=".MainActivity"                  android:label="@string/app_name">            <intent-filter>                <action android:name="android.intent.action.MAIN" />                <category android:name="android.intent.category.LAUNCHER" />            </intent-filter>        </activity>    </application>        <uses-sdk android:minSdkVersion="8" />    <!-- 配置测试环境 -->    <instrumentation android:name="android.test.InstrumentationTestRunner"                      android:targetPackage="cn.itcast.db"                     android:label="Test for My App"/></manifest> 

2：Person类

package cn.itcast.domain;public class Person {private Integer id;private String name;public Person() {}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}@Overridepublic String toString() {return "Person [id=" + id + ", name=" + name + "]";}}

3：DBOpenHelper类，该类继承了SQLiteOpenHelper类

package cn.itcast.service;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;import android.database.sqlite.SQLiteDatabase.CursorFactory;public class DBOpenHelper extends SQLiteOpenHelper {private static final String DATABASENAME = "cn.itcast.db";private static final int DATABASEVERSION = 1;/* * 构造函数 */public DBOpenHelper(Context context) {super(context, DATABASENAME, null, DATABASEVERSION);}/* * 数据库第一次生成时调用该方法，创建一些表或者初始化一些数据 * @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase) */@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL("create table person(personid integer primary key autoincrement, name varchar(20))");}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}}

4：PersonService类

package cn.itcast.service;import java.util.ArrayList;import java.util.List;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import cn.itcast.domain.Person;public class PersonService {private DBOpenHelper dbOpenHelper;public PersonService(Context context) {this.dbOpenHelper = new DBOpenHelper(context);}/* * save a person to the database */public void save(Person person) {SQLiteDatabase database = dbOpenHelper.getWritableDatabase();database.execSQL("insert into person(name) values (?)", new Object[]{person.getName()});}/* * updata a person to the database */public void update(Person person) {SQLiteDatabase database = dbOpenHelper.getWritableDatabase();database.execSQL("update person set name=? where personid=?", new Object[]{person.getName(), person.getId()});}/* * delete a person from the database according to the id */public void delete(Integer id) {SQLiteDatabase database = dbOpenHelper.getWritableDatabase();database.execSQL("delete from person where personid=?", new Object[]{id.toString()});}/* * find a person from the database according to the id */public Person find(Integer id) {SQLiteDatabase database = dbOpenHelper.getReadableDatabase();Cursor cursor = database.rawQuery("select * from person where personid=?", new String[]{id.toString()});Person person = null;if(cursor.moveToFirst()) {   Integer personid = cursor.getInt(cursor.getColumnIndex("personid"));   String name = cursor.getString(cursor.getColumnIndex("name"));   person = new Person();   person.setId(personid);   person.setName(name);}return person;}/* * get the data of person accroding to the offset and maxResult */public List<Person> getScrollData(Integer offset, Integer maxResult) {SQLiteDatabase database = dbOpenHelper.getReadableDatabase();Cursor cursor = database.rawQuery("select * from person limit ?,?", new String[] {offset.toString(), maxResult.toString()});int idIndex = 0;int nameIndex = 0;List<Person> personList = null;if(cursor.getCount() >= 0) { idIndex = cursor.getColumnIndex("personid"); nameIndex = cursor.getColumnIndex("name"); personList = new ArrayList<Person>();}while(cursor.moveToNext()) {Integer personid = cursor.getInt(idIndex);String name = cursor.getString(nameIndex);Person person = new Person();person.setId(personid);person.setName(name);personList.add(person);}return personList;}    /*     * get the count of the database     */public long getCount(){SQLiteDatabase database = dbOpenHelper.getReadableDatabase();Cursor cursor = database.rawQuery("select count(*) from person", null);cursor.moveToFirst();return cursor.getLong(0);}}

5：PersonServiceTest类

package cn.itcast.db;import java.util.List;import android.test.AndroidTestCase;import android.util.Log;import cn.itcast.domain.Person;import cn.itcast.service.DBOpenHelper;import cn.itcast.service.PersonService;public class PersonServiceTest extends AndroidTestCase {private static final String TAG = "PersonServiceTest";/* * 测试生成数据库的方法 */public void testCreateDB() throws Throwable {DBOpenHelper dbOpenHelper = new DBOpenHelper(this.getContext());dbOpenHelper.getWritableDatabase();  //第一次调用该方法会生成数据库}/* * 测试保存方法 */public void testSave() throws Throwable{PersonService personService = new PersonService(this.getContext());Person person1 = new Person();person1.setName("zhangsan");personService.save(person1);Person person2 = new Person();person2.setName("lisi");personService.save(person2);Person person3 = new Person();person3.setName("wangwu");personService.save(person3);}public void testDelete() {PersonService personService = new PersonService(this.getContext());personService.delete(1);}/* * 测试更新方法 */public void testUpdate() {PersonService personService = new PersonService(this.getContext());Person person = personService.find(1);person.setName("zhaoliu");personService.update(person);}/* * 测试获得数据方法 */public void testGetScrollData() throws Throwable{PersonService personService = new PersonService(this.getContext());List<Person> persons = personService.getScrollData(0, 3);for(Person person : persons) {Log.i(TAG, person.toString());}}/* * 测试根据id查找的方法 */public void testFind() throws Throwable{PersonService personService = new PersonService(this.getContext());Person person = personService.find(1);Log.i(TAG, person.toString());}/* * 测试获得数量的方法 */public void testGetCount() {PersonService personService = new PersonService(this.getContext());long count = personService.getCount();Log.i(TAG, count + "");}}