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 AnotherDBService {

    private DBOpenHelper dbHelper;

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

    public void save(Person person) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", person.getName());
        db.insert("person", "name", values);
    }

    public void update(Person person) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", person.getName());
        db.update("person", values, "personid=?", new String[]{person.getId().toString()});
    }

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

    public Person find(Integer id) {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.query("person", new String[]{"personid", "name"}, "personid=?", 
                new String[]{id.toString()}, null, null, null);
        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.query("person", null, null, 
                null, null, null, null, offset + "," + maxResult);
        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.query("person", new String[]{"count(*)"}, null, null, null, null, null);
        cursor.moveToFirst();
        return cursor.getLong(0);
    }

}

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

public class AnotherTest extends AndroidTestCase {

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

    public void testSave() throws Exception {
        AnotherDBService dbService = new AnotherDBService(this.getContext());
        Person person = new Person();
        person.setName("凹凸曼");
        dbService.save(person);

        person = new Person();
        person.setName("迪迦凹凸曼");
        dbService.save(person);

        person = new Person();
        person.setName("咸蛋凹凸曼");
        dbService.save(person);

        person = new Person();
        person.setName("三角凹凸曼");
        dbService.save(person);

        person = new Person();
        person.setName("独角凹凸曼");
        dbService.save(person);
    }

    public void testUpdate() throws Exception {
        AnotherDBService dbService = new AnotherDBService(this.getContext());
        Person person = dbService.find(1);
        person.setName("小怪兽");
        dbService.update(person);
    }

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

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

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

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