SQLite笔记

新建DbHelper继承自SQLiteOpenHelper，实现其虚方法：

public class MyDbHelper extends SQLiteOpenHelper {
    final static String DB_NAME = "database.db";
    final static int DB_VERSION = 1;

    static final String TABLE_NAME = "pepelu";
    //注意空格
    static final String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS " +
            TABLE_NAME + "(" +
            " id INTEGER PRIMARY KEY AUTOINCREMENT," +
            " name TEXT NOT NULL," +
            " age INTEGER" +
            " )";

    public MyDbHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

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

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //升级数据库，去掉break，从头跑到尾
        switch (oldVersion) {
            case 1:
                //do version 1 update thing
            case 2:
                //do version 2 update thing
            default:
                break;
        }
    }
}

创建数据库

myDbHelper = new MyDbHelper(this);
SQLiteDatabase db = null;
try {
    db = myDbHelper.getWritableDatabase();
} finally {
    IOUtil.close(db);
}

增

SQLiteDatabase#insertWithOnConflict(String table, String nullColumnHack,
            ContentValues initialValues, int conflictAlgorithm)

table : 表名
nullColumnHack : 如果插入的数据为空，用此作为默认值
initialValues : 用以封装被插入数据
conflictAlgorithm : 错误处理模式，有以下几种，具体的意思去看源码注释

public static final int CONFLICT_ROLLBACK = 1;
public static final int CONFLICT_ABORT = 2;
public static final int CONFLICT_FAIL = 3;
public static final int CONFLICT_IGNORE = 4;
public static final int CONFLICT_REPLACE = 5;
public static final int CONFLICT_NONE = 0;

例子：

SQLiteDatabase db = null;
try {
    db = myDbHelper.getWritableDatabase();
    db.insertWithOnConflict(MyDbHelper.TABLE_NAME, "", getContentValues(), 
                    SQLiteDatabase.CONFLICT_IGNORE);
} finally {
    IOUtil.close(db);
}

ContentValues getContentValues() {
    ContentValues contentValues = new ContentValues();
    contentValues.put("name", "pepelu");
    contentValues.put("age", 12);
    return contentValues;
}

删

SQLiteDatabase#delete(String table, String whereClause, String[] whereArgs)

table : 表名
whereClause : 限制条件
whereArgs : 限制条件的值

SQLiteDatabase db = null;
        try {
            db = myDbHelper.getWritableDatabase();
            //没有不会抛异常
            db.delete(MyDbHelper.TABLE_NAME, "name = ? and age = ?",
                     new String[]{"pepelu1", "12"});
        } finally {
            IOUtil.close(db);
        }

删除所有

SQLiteDatabase db = null;
Cursor cursor = null;
try {
    db = myDbHelper.getWritableDatabase();
    //删除表中所有行
    db.delete(MyDbHelper.TABLE_NAME, null, null);
} finally {
    IOUtil.close(db);
}

改

SQLiteDatabase#updateWithOnConflict(String table, ContentValues values,
            String whereClause, String[] whereArgs, int conflictAlgorithm)

table : 表名
values : 数据封装的bean
whereClause : 限制条件
whereArgs : 限制条件的值
conflictAlgorithm : 冲突处理方案，和增加的一样

SQLiteDatabase db = null;
try {
    db = myDbHelper.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.put("name", "spike");
    cv.put("age", 19);
    db.updateWithOnConflict(MyDbHelper.TABLE_NAME, cv, 
            "id = ?", new String[]{"2"}, SQLiteDatabase.CONFLICT_IGNORE);
} finally {
    IOUtil.close(db);
}

查

查询所有数据

SQLiteDatabase db = null;
Cursor cursor = null;
try {
    db = myDbHelper.getWritableDatabase();
    cursor = db.query(MyDbHelper.TABLE_NAME, null, null, null, null, null, null);
    if (cursor.moveToFirst()) {
        do {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            int age = cursor.getInt(cursor.getColumnIndex("age"));
            LogUtil.d("id=" + id + ",name=" + name + ",age=" + age);
        } while (cursor.moveToNext());
    }
} finally {
    IOUtil.close(cursor);
    IOUtil.close(db);
}

查询数据行数

SQLiteDatabase db = null;
Cursor cursor = null;
try {
    db = myDbHelper.getWritableDatabase();
    cursor = db.query(MyDbHelper.TABLE_NAME, new String[]{"count(*)"},
             null, null, null, null, null);
    cursor.moveToFirst();
    long count = cursor.getLong(0);
    LogUtil.d("queryCount count=" + count);
} finally {
    IOUtil.close(cursor);
    IOUtil.close(db);
}

按条件查询

SQLiteDatabase db = null;
Cursor cursor = null;
try {
    db = myDbHelper.getWritableDatabase();
    cursor = db.query(MyDbHelper.TABLE_NAME, new String[]{"id", "name"}, 
             null, null, null, null, null);
    //要加判断
    if (!cursor.moveToFirst()) {
        LogUtil.d("cursor is empty");
        return;
    }
    do {
        int id = cursor.getInt(cursor.getColumnIndex("id"));
        String name = cursor.getString(cursor.getColumnIndex("name"));
        //没有相应栏目的话会抛异常
        // String age = cursor.getString(cursor.getColumnIndex("age"));
        LogUtil.d("queryCount id=" + id + ",name=" + name);
    } while (cursor.moveToNext());
} finally {
    IOUtil.close(cursor);
    IOUtil.close(db);
}

事务

SQLiteDatabase#beginTransaction()
SQLiteDatabase#setTransactionSuccessful()
SQLiteDatabase#endTransaction()

SQLiteDatabase db = null;
try {
    db = myDbHelper.getWritableDatabase();
    db.beginTransaction();
    ContentValues cv = new ContentValues();
    cv.put("name", "there");
    cv.put("age", 23);
    db.insertWithOnConflict(MyDbHelper.TABLE_NAME, null, cv, 
                            SQLiteDatabase.CONFLICT_IGNORE);
    db.setTransactionSuccessful();
} finally {
    if (db != null) {
        db.endTransaction();
    }
    IOUtil.close(db);
}

其他

直接 SQLiteDatabase#execSQL(String sql) 哪里那么多废话！

代码：