Android SQLite增删改查、升级的基本使用

android系统中使用的数据库为SQLite
在这主要介绍SQLite的中的基本操作：增、删、改、查、升级

为了便于管理，我将SQLite的操作分为一下文件

• Person （存储类型的定义）
• SqlColumn （SQLite中的列名称）
• SqlHelp（继承于SQLiteOpenHelper，创建数据库、表格，升级数据库）
• SqlManager（定义增删改查）

注：本例中对数据库操作主要使用官方提供的接口，但也可通过 execSQL(String sql) 方法来使用SQL语句进行操作。

/**
 * Created by BigHeart on 15/11/5.
 */
public class Person {

    public Person(int id, String saying) {
        ID = id;
        SAYING = saying;
    }

    public Person(int id, String saying, String w) {
        ID = id;
        SAYING = saying;
        WORK = w;
    }

    public Person(int id, String saying, String w, String i) {
        ID = id;
        SAYING = saying;
        WORK = w;
        INFO = i;
    }

    private int ID;
    private String SAYING, WORK, INFO;


    public int getID() {
        return ID;
    }

    public String getSaying() {
        return SAYING;
    }

    public String getWork() {
        return WORK;
    }

    public String getInfo() {
        return INFO;
    }
}

/**
 * Created by BigHeart on 15/10/29.
 */
public class SqlHelp extends SQLiteOpenHelper {
    public static final int DbVersion = 1;//当前版本号为1
    public static String DbName = "DbTest_three.db";

    private static final String TEXT_TYPE = " TEXT";

    private static final String SQL_CREATE_ENTRIES =
            "CREATE TABLE " + SqlColumn.TABLE_NAME + " (" +
                    SqlColumn.ID + " INTEGER PRIMARY KEY," +
                    SqlColumn.SAYING + TEXT_TYPE + " )";

    public SqlHelp(Context context) {
        super(context, DbName, null, DbVersion);
    }

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

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        }
    }
}

/**
 * Created by BigHeart on 15/11/3.
 */
public class SqlManager {

    private SqlHelp helper;
    private SQLiteDatabase db;

    public SqlManager(Context context) {
        helper = new SqlHelp(context);
        db = helper.getWritableDatabase();
    }

    public void addDate(List<Person> persons) {
        try {
            db.beginTransaction();
            for (Person p : persons) {
                ContentValues contentValues = personInflatContentValue(p);
                db.insert(SqlColumn.TABLE_NAME, null, contentValues);
            }
            db.setTransactionSuccessful();
        } catch (IllegalStateException e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
        }
    }

    public void updateDate(Person p) {

        ContentValues values = personInflatContentValue(p);


        int count = db.update(SqlColumn.TABLE_NAME, values, SqlColumn.ID + "= 22", null);//假定修改ID为22的元素
        Log.i(SqlColumn.TABLE_NAME, "updateDate :" + count);
    }

    public void deleteDate(String deleteClause) {
        int count = db.delete(SqlColumn.TABLE_NAME, deleteClause, null);
        Log.i(SqlColumn.TABLE_NAME, "deleteDate :" + count);
    }

    public ArrayList<Person> query() {

        Cursor c = db.rawQuery("SELECT * FROM " + SqlColumn.TABLE_NAME, null);
        ArrayList<Person> persons = new ArrayList();
        while (c.moveToNext()) {
            Person person = new Person(c.getInt(c.getColumnIndex(SqlColumn.ID)), c.getString(c.getColumnIndex(SqlColumn.SAYING)));
            persons.add(person);
        }
        c.close();
        return persons;
    }

//将Person填充为 ContentValues
    private ContentValues personInflatContentValue(Person p) {
        ContentValues values = new ContentValues();
        values.put(SqlColumn.ID, p.getID());
        values.put(SqlColumn.SAYING, p.getSaying());
        return values;
    }

     public void closeDb(){
        db.close();
    }

}

...
//使用
        SqlManager manager = new SqlManager(MainActivity.this);
        ArrayList<Person> Qpes = new ArrayList<>();
        for (int i = 20; i < 25; i++) {
        Person p = new Person(i, "android L");
        Qpes.add(p);
       }

        manager.addDate(Qpes);
        Log.i(SqlColumn.TABLE_NAME, "insert success");

        Qpes = manager.query();
        for (int i = 0; i < Qpes.size(); i++) {
            Log.i(SqlColumn.TABLE_NAME, Qpes.get(i).getID() + " & " + Qpes.get(i).getSaying());
        }

        manager.updateDate(new Person(22, "android M");

        Qpes = manager.query();
        for (int i = 0; i < Qpes.size(); i++) {
            Log.i(SqlColumn.TABLE_NAME, Qpes.get(i).getID() + " & " + Qpes.get(i).getSaying() + " & " + Qpes.get(i).getWork() + " & " + Qpes.get(i).getInfo());
        }

        manager.deleteDate(SqlColumn.ID + "= 22");

        Qpes = manager.query();
        for (int i = 0; i < Qpes.size(); i++) {
            Log.i(SqlColumn.TABLE_NAME, Qpes.get(i).getID() + " & " + Qpes.get(i).getSaying() + " & " + Qpes.get(i).getWork() + " & " + Qpes.get(i).getInfo());
        }

下面介绍如何升级，在SqlHelp中，有一个onUpgrade(…)方法，只要版本号不对应，就会触发该方法执行。

/**
 * Created by BigHeart on 15/10/29.
 */
public class SqlHelp extends SQLiteOpenHelper {
    public static final int DbVersion = 2;//记得改版本号
    public static String DbName = "DbTest_three.db";

    private static final String TEXT_TYPE = " TEXT";

    private static final String SQL_CREATE_ENTRIES =
            "CREATE TABLE " + SqlColumn.TABLE_NAME + " (" +
                    SqlColumn.ID + " INTEGER PRIMARY KEY," +
                    SqlColumn.SAYING + TEXT_TYPE + " )";


    private static final String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS " + SqlColumn.TABLE_NAME;

    public SqlHelp(Context context) {
        super(context, DbName, null, DbVersion);
    }

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

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        switch (oldVersion) {
            //增加 "work" 属性
            case 2:
                db.execSQL("ALTER TABLE " + SqlColumn.TABLE_NAME + " ADD COLUMN " + SqlColumn.WORK + " TEXT");// no break
        }
    }
}
...

另外，打开数据库用两种方法：getWritableDatabase() 、getReadableDatabase()

对于getWritableDatabase，官网解释是：

Create and/or open a database that will be used for reading and writing. The first time this is called, the database will be opened and onCreate(SQLiteDatabase), onUpgrade(SQLiteDatabase, int, int) and/or onOpen(SQLiteDatabase) will be called.

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 the database.) Errors such as bad permissions or a full disk may cause this method to fail, but future attempts may succeed if the problem is fixed.

对于getReadableDatabase：

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.

也就是说，getReadableDatabase()一般情况下返回结果与getWritableDatabase()一致,所以可以先使用getWritableDatabase()，如果调用失败，再使用getReadableDatabase()，但这时返回的数据库便只能读不能写了，