Android SQLite操作笔记

openwudi 2011-02-10

展开全文

Email : i#openwudi.com

Android当中通过SQLiteOpenHelper抽象类来对数据库进行版本管理和控制，必须继承SQLiteOpenHelper来使用，SQLiteOpenHelper有两个重要的方法（1）onCreate(SQLiteDatabase db) （2）onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 。Called when the database is created for the first time. 调用onCreate 。Called when the database needs to be upgraded.调用onUpgrade。

这样就可以在onCreate方法中来创建数据库了。整个代码如下。

Java语言:

package com.openwudi.service;

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

public class DBOpenHelper extends SQLiteOpenHelper {
    private static String NAME = "person.db";
    private static int VERSION = 1;

    public DBOpenHelper(Context context) {
        super(context, NAME, null, VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE IF NOT EXISTS person (personid integer primary key autoincrement, name varchar(20), age INTEGER)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS person");
        onCreate(db);
    }

}

由于考虑到移动设备资源的消耗问题，在Android应用中将不创建DAO接口，为了节省开销。

并且暂时Android应用不需要做解耦操作，DAO接口此时显得有些多余。

对数据库的操作将创建一个类，来直接实现。下面此类实现了增，删，改，查，分页，取得数据总数的操作。

Java语言:

package com.openwudi.service;

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

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

import com.openwudi.domain.Person;

public class PersonService {
    private DBOpenHelper dbOpenHelper;

    public PersonService(Context context) {
        this.dbOpenHelper = new DBOpenHelper(context);
    }

    public void save(Person person) {
        // 要对数据进行更改，调用此方法，该方法以读和写的方式打开数据库
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
        db.execSQL("insert into person(name,age) values (?,?)", new Object[] {
                person.getName(), person.getAge() });
    }

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

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

    public Person find(Integer id) {
        // 要对数据进行读取，建议调用此方法
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from person where personid=?",
                new String[] { id.toString() });
        if (cursor.moveToFirst()) {
            Integer personid = cursor.getInt(cursor.getColumnIndex("personid"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            Integer age = cursor.getInt(cursor.getColumnIndex("age"));
            return new Person(personid, name, age);
        } else {
            return null;
        }
    }

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

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

解释一下 getReadableDatabase () 和getWritableDatabase () 的区别。

这两个方法都可以获得SQLiteDatabase对象。

getWritableDatabase () 获得一个可进行操作的数据库类。并且查看源代码，了解到获得的是单实例的SQLiteDatabase。因为不论是调用几次getWritableDatabase () 方法返回的都是同一个连接。

getReadableDatabase () 同样是获得SQLiteDatabase对象。一样查看源代码，了解到getReadableDatabase () 内部同样是调用getWritableDatabase ()来实现获得SQLiteDatabase对象的。但是，getReadableDatabase () 判断一旦getWritableDatabase () 抛出异常（比如存储空间满了），将以只读的方式读取数据库，此时获得的SQLiteDatabase对象将和getWritableDatabase () 获得的不一样。

public synchronized SQLiteDatabase getReadableDatabase ()

Since: API Level 1

Create and/or open a database. This will be the same object returned by getWritableDatabase() unless some problem, such as a full disk, requires the database to be opened read-only. In that case, a read-only database object will be returned. If the problem is fixed, a future call to getWritableDatabase() may succeed, in which case the read-only database object will be closed and the read/write object will be returned in the future.

Returns

a database object valid until getWritableDatabase() or close() is called.

Throws

SQLiteException	if the database cannot be opened

public synchronized SQLiteDatabase getWritableDatabase ()

Since: API Level 1

Create and/or open a database that will be used for reading and writing. Once opened successfully, the database is cached, so you can call this method every time you need to write to the database. Make sure to call close() when you no longer need it.

Errors such as bad permissions or a full disk may cause this operation to fail, but future attempts may succeed if the problem is fixed.