android_sqlite

package com.example.myfirstapp;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBOpenHelper extends SQLiteOpenHelper {
    private static final String DATABSENAME="person.db";
    private static final Integer DATABASEVERSION=1;
    private static final String installSql="create table person(id integer primary key autoincrement,age integer,name varchar(20))";
    
    public DBOpenHelper(Context context) {
        super(context, DATABSENAME, null, DATABASEVERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(installSql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("drop table person if exists");
        onCreate(db);
    }

}

package com.example.myfirstapp;

import java.util.ArrayList;
import java.util.List;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.example.myfirstapp.domain.Person;

public class PersonService {
    private DBOpenHelper dbOpenHelper;

    public void setDbOpenHelper(DBOpenHelper dbOpenHelper) {
        this.dbOpenHelper = dbOpenHelper;
    }
    
    public void addPerson(Person person){
        SQLiteDatabase db=dbOpenHelper.getWritableDatabase();
        db.beginTransaction(); //开始事务
        try{
            db.execSQL("insert into person(name,age) values(?,?)",new Object[]{person.getName(),person.getAge()});
            db.execSQL("insert into person(name,age) values(?,?)",new Object[]{person.getName()+'_'+"clone",person.getAge()});
            db.setTransactionSuccessful();//标志事务成功
        }finally{
            db.endTransaction(); //结束事务
        }
    }

    public void delPerson(Person person) {
        SQLiteDatabase db=dbOpenHelper.getWritableDatabase();
        db.execSQL("delete from person where id=?",new Integer[]{person.getId()});
    }
    
    public Person getPersonById(Person person){
        SQLiteDatabase db=dbOpenHelper.getReadableDatabase();
        Cursor cursor=db.rawQuery("select id,name,age from person where id=?",new String[]{person.getId()+""});
        Person item = null;
        if(cursor.moveToFirst()){
            item=new Person();
            item.setId(cursor.getInt(cursor.getColumnIndex("id")));
            item.setAge(cursor.getInt(cursor.getColumnIndex("age")));
            item.setName(cursor.getString(cursor.getColumnIndex("name")));
        }
        cursor.close();
        return item;
    }
    
    public List<Person> queryPerson(){
        SQLiteDatabase db=dbOpenHelper.getReadableDatabase();
        Cursor cursor=db.rawQuery("select * from person limit 0,5", null);
        List<Person> personList=new ArrayList<Person>(5);
        Person person;
        while(cursor.moveToNext()){
            person=new Person();
            person.setId(cursor.getInt(cursor.getColumnIndex("id")));
            person.setAge(cursor.getInt(cursor.getColumnIndex("age")));
            person.setName(cursor.getString(cursor.getColumnIndex("name")));
            personList.add(person);
        }
        cursor.close();
        return personList;
    }
    

}

package com.kk.android.test;

import java.util.List;

import android.test.AndroidTestCase;
import android.util.Log;

import com.example.myfirstapp.DBOpenHelper;
import com.example.myfirstapp.PersonService;
import com.example.myfirstapp.domain.Person;

public class PersonServiceTest extends AndroidTestCase {
    
    public void testInsert() throws Exception {
        DBOpenHelper helper=new DBOpenHelper(getContext());
        PersonService service=new PersonService();
        service.setDbOpenHelper(helper);
        Person person1=new Person("kk1",1);
        Person person2=new Person("kk2",2);
        Person person3=new Person("kk3",3);
        Person person4=new Person("kk4",4);
        Person person5=new Person("kk5",5);
        Person person6=new Person("kk6",6);
        service.addPerson(person1);
        service.addPerson(person2);
        service.addPerson(person3);
        service.addPerson(person4);
        service.addPerson(person5);
        service.addPerson(person6);
    }
    
    public void testDel() throws Exception {
        DBOpenHelper helper=new DBOpenHelper(getContext());
        PersonService service=new PersonService();
        service.setDbOpenHelper(helper);
        Person person1=new Person("kk1",1);
        person1.setId(1);
        service.delPerson(person1);
    }
    
    public void testQueryById() throws Exception {
        DBOpenHelper helper=new DBOpenHelper(getContext());
        PersonService service=new PersonService();
        service.setDbOpenHelper(helper);
        Person person2=new Person();
        person2.setId(2);
        Person pserson=service.getPersonById(person2);
        Log.i("psersonTag",pserson.toString());
    }
    
    public void testQueryByPage()throws Exception{
        DBOpenHelper helper=new DBOpenHelper(getContext());
        PersonService service=new PersonService();
        service.setDbOpenHelper(helper);
        List<Person> personList=service.queryPerson();
        for(Person person:personList){
            Log.i("psersonTag", person.toString());
        }
    }
}