Android SQLite数据库操作（一）

Android SQLite数据库操作（一）

1. 新建DBOpenHelper继承自SQLiteOpenHelper类：

public class DBOpenHelper extends SQLiteOpenHelper {

    private final static String DATABASENAME = "my.db";
    private final static int DATABASEVERSION = 1;

    public DBOpenHelper(Context context) {
        super(context, DATABASENAME, null, DATABASEVERSION);

    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE person (personid integer primary key autoincrement, name varchar(20))");
    }
    @Override
    public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
    }
}

2.新建对应数据库中表的字段的JavaBean数据类：

public class Person {

    private Integer id;
    private String name;

    public Person() {

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

}

3. 新建业务操作类对数据进行增删查改：

public class DBService {

    private DBOpenHelper dbHelper;

    public DBService(Context context) {
        this.dbHelper = new DBOpenHelper(context);
    }

    public void save(Person person) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.execSQL("insert into person (name) values(?)", new Object[] {person.getName()});
        //db.close();
    }

    public void update(Person person) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.execSQL("update person set name=? where personid=?",
                new Object[] {person.getName(), person.getId()});
    }

    public void delete(Integer id) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.execSQL("delete from person where personid=?",
                new Object[] {id.toString()});
    }

    public Person find(Integer id) {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from person where personid=?",
                new String[]{id.toString()});
        if(cursor.moveToFirst()) {
            int personId = cursor.getInt(cursor.getColumnIndex("personid"));
            String personName = cursor.getString(cursor.getColumnIndex("name")); 
            return new Person(personId, personName);
        }
        cursor.close();
        return null;
    }

    public List<Person> getScrollData(Integer offset, Integer maxResult) {
        List<Person> persons = new ArrayList<Person>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from person limit?,?",
                new String[]{offset.toString(), maxResult.toString()});
        while(cursor.moveToNext()) {
            int personId = cursor.getInt(cursor.getColumnIndex("personid"));
            String personName = cursor.getString(cursor.getColumnIndex("name")); 
            Person person = new Person(personId, personName);
            persons.add(person);
        }
        cursor.close();
        return persons;
    }

    public long getCount() {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select count(*) from person", null);
        cursor.moveToFirst();
        return cursor.getLong(0);
    }

}

4.新建单元测试类对业务类中的方法进行测试：

public class Test extends AndroidTestCase {

    public void testCreateDB() throws Exception {
        DBOpenHelper dbHelper = new DBOpenHelper(this.getContext());
        dbHelper.getWritableDatabase();
    }

    public void testSave() throws Exception {
        DBService dbService = new DBService(this.getContext());
        Person person = new Person();
        person.setName("哈哈");
        dbService.save(person);
    }

    public void testUpdate() throws Exception {
        DBService dbService = new DBService(this.getContext());
        Person person = dbService.find(1);
        person.setName("不哈哈 ");
        dbService.update(person);
    }

    public void testDelete() throws Exception {
        DBService dbService = new DBService(this.getContext());
        dbService.delete(1);
    }

    public void testFind() throws Exception {
        DBService dbService = new DBService(this.getContext());
        Person person = dbService.find(1);
        Log.i("FIND", person.toString());
    }

    public void testgetScrollData() throws Exception {
        DBService dbService = new DBService(this.getContext());
        List<Person> persons = dbService.getScrollData(0, 3);
        for(Person person : persons) {
            Log.i("DATA", person.toString());
        }
    }

    public void testgetCount() throws Exception {
        DBService dbService = new DBService(this.getContext());
        Log.i("COUNT", dbService.getCount()+"");
    }
}