android 数据库操作

public class DatabaseHelper extends SQLiteOpenHelper {

    private final static String NAME="sharp.db" ;
    //Factory:代表记录集游标工厂，是专门用来生成记录集游标，记录集游标是对查询结果进行迭代的，后面我们会继续介绍。
    public DatabaseHelper(Context context, String name, CursorFactory factory,
            int version) {
        super(context, name, factory, version);
        // TODO Auto-generated constructor stub
    }
    public DatabaseHelper(Context context){
        super(context, NAME, null, 1 );
    }

    /**
     * 用户第一次使用软件时调用，实现数据库的操作crud
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        Log.i("save" ,  "create" );
        db.execSQL("create table person(personid integer primary key autoincrement,name varcahr(20),age integer)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("drop table if exists person" );
        onCreate(db);
    }
    /**
     * 根据版本号进行更新
     * @param db
     * @param mNewVersion
     */
    public void checkVersionCreate(SQLiteDatabase db,int mNewVersion){
        int version = db.getVersion();
        if (version != mNewVersion) {
        db.beginTransaction();
        try {
        if (version == 0 ) {
        onCreate(db);
        }else{
        onUpgrade(db, version,mNewVersion);
        }
        db.setVersion(mNewVersion); //设置为新的版本号
        db.setTransactionSuccessful();
        }
        finally{
        db.endTransaction();
        }
        }
    }
    /**
     * 数据库名不空，获得数据库实例
     * @param mDatabase
     * @param mContext
     * @param mName
     * @param mFactory
     * @return
     */
    public SQLiteDatabase getDatabase(SQLiteDatabase mDatabase,Context mContext,String mName,CursorFactory mFactory){
            if (mDatabase != null && mDatabase.isOpen() && !mDatabase.isReadOnly()) {
                return mDatabase; // The database is already open for business
                }
            if (mName == null) {
                mDatabase = SQLiteDatabase.create(null);
                } else {
                    mDatabase = mContext.openOrCreateDatabase(mName, 0 , mFactory);
                }
            return mDatabase;
        }
}

 

DbService：

public   class  DbService {
    private  DatabaseHelper databaseHelper;
    private  Context context;
    public  DbService(Context context){
        this .context=context;
        databaseHelper=new  DatabaseHelper( this .context);
    }
    //保存
    public   void  save(Person person){
        SQLiteDatabase db = databaseHelper.getWritableDatabase();
        db.execSQL("insert into person(name, age) values(?,?)" , new  Object[]{person.getName(), person.getAge()});
        }
    //更新
    public   void  update(Person person){
        SQLiteDatabase db = databaseHelper.getWritableDatabase();
        db.execSQL("update person set name=?,age=? where personid=?" ,
        new  Object[]{person.getName(), person.getAge(),
        person.getId()});
        }
    //根据id查找
    public  Person find(Integer id){
        SQLiteDatabase db = databaseHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select personid,name,age from person where personid=?",  new  String[]{String.valueOf(id)});
        if (cursor.moveToNext()){  //迭代记录集
        Person person = new  Person(); //实例化person
        person.setId(cursor.getInt(cursor.getColumnIndex("personid" )));
        person.setName(cursor.getString(1 ));
        person.setAge(cursor.getInt(2 ));  //将查到的字段，放入person，
        return  person;
        }
        cursor.close();//游标关闭
        return   null ;
        }
    //删除
    public   void  delete(Integer id){
        SQLiteDatabase db=databaseHelper.getWritableDatabase();
        db.execSQL("delete from person where id=?" , new  Object[]{id});
    }
    //
    public  List<Person> getScrollData( int  firstResult,  int  maxResult){
        List<Person> persons = new  ArrayList<Person>();
        SQLiteDatabase db = databaseHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select personid,name,age from person limit ?,?" ,
        new  String[]{String.valueOf(firstResult),String.valueOf(maxResult)}); //firstResult开始索引
        while (cursor.moveToNext()){  //maxResult每页获取的记录数
        Person person = new  Person();
        person.setId(cursor.getInt(cursor.getColumnIndex("personid" )));
        person.setName(cursor.getString(1 ));
        person.setAge(cursor.getInt(2 ));
        persons.add(person);
        }
        cursor.close();
        return  persons;
        }
    //
    public   long  getCount(){
        SQLiteDatabase db = databaseHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select count(*) from person" ,  null );
        //没有占位符参数的话，直接用null
        cursor.moveToFirst();
        long  count = cursor.getLong( 0 );
        cursor.close();
        return  count;
        }
    //
    public   void  close(){
        databaseHelper.close();
        }
}

 

Person：

public   class  Person {
    private  Integer id;
    private  String name;
    private  Integer age;
    public  Person(){}
    public  Person(String name,Integer age){
        this .name=name;
        this .age=age;
    }
    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;
    }
    public  Integer getAge() {
        return  age;
    }
    public   void  setAge(Integer age) {
        this .age = age;
    }
    @Override
    public  String toString() {
    return   "Person [age="  + age +  ", id="  + id +  ", name="  + name +  "]" ;
    }
}

 

PersonServiceTest：

public   class  PersonServiceTest  extends  AndroidTestCase{
    public   void  testSave()  throws  Throwable{  //测试保存方法
        DbService personService = new  DbService( this .getContext());
        //传入上下文
        Person person = new  Person( "Tom" ,  21 );
        personService.save(person);
        }
}

 

一个activity：

 

public   class  SqlLiteActivity  extends  Activity {
    /** Called when the activity is first created. */
    private   final   static  String TAG= "PersonActivity" ;
    private  ListView listView;
    private  DbService personService;
    @Override
    public   void  onCreate(Bundle savedInstanceState) {
        super .onCreate(savedInstanceState);
        setContentView(R.layout.main);
        listView = (ListView)findViewById(R.id.personList);
        personService = new  DbService( this );
        save(personService);
        List<Person> persons = personService.getScrollData(0 ,  3 ); //前十条数据
        List<HashMap<String, String>> data = new
        ArrayList<HashMap<String,String>>();
        HashMap<String, String>title = new  HashMap<String, String>();
        title.put("personid" , "编号" );
        title.put("name" ,  "姓名" );
        title.put("age" ,  "年龄" );
        data.add(title); //标题
        for (Person person : persons){
            HashMap<String, String> map = new  HashMap<String, String>();
            map.put("personid" , String.valueOf(person.getId()));
            map.put("name" , person.getName());
            map.put("age" , String.valueOf(person.getAge()));
            data.add(map); //显示各个数据
        }
        SimpleAdapter adapter = new  SimpleAdapter(SqlLiteActivity. this ,
                data, R.layout.personitem, new  String[]{ "personid" ,  "name" , "age"},
                new   int []{R.id.personid, R.id.name, R.id.age});
        listView.setAdapter(adapter);
    }

    //
    public   void  save(DbService service){
        for ( int  i= 0 ;i< 5 ;i++){
        Person person=new  Person();
        person.setName("d" );
        person.setAge(12 );
        service.save(person);
        }
    }
}

 main.xml<?xml version="1.0" encoding="utf-8"?>

<LinearLayout
xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
>
<ListView
android:id="@+id/personList"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
></ListView>
</LinearLayout>

 personitem.xml<?xml version="1.0" encoding="utf-8"?>

<RelativeLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content" >
    <TextView
        android:layout_width="60px"
        android:layout_height="wrap_content"
        android:id="@+id/personid"
    />

    <TextView
       android:layout_width="160px"
       android:layout_height="wrap_content"
       android:layout_toRightOf="@id/personid"
       android:layout_alignTop="@id/personid"
       android:gravity="center_horizontal"
       android:id="@+id/name"
      />

    <TextView
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_toRightOf="@id/name"
    android:layout_alignTop="@id/name"
    android:id="@+id/age"
    />

</RelativeLayout>