1.查询操作:
简单的直接使用SQLiteDatabase#query(table_name,columns,selections,selectionArgs,groupby,having,orderby,limits)
需要根据各种情况动态构建sql语句的,可以使用SQLiteQueryBuilder。用法如下:
DatabaseHelper database = getDatabaseForUri(uri);
SQLiteDatabase db = database.getReadableDatabase();
if (db == null) {
throw new IllegalStateException("Couldn't open database for " + uri);
}
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
int albumid = Integer.parseInt(uri.getPathSegments().get(3));
qb.setTables("audio_meta");
qb.appendWhere("album_id=" + albumid);
Cursor c = qb.query(db,
new String [] {
MediaStore.Audio.Media.DATA },
null, null, null, null, MediaStore.Audio.Media.TRACK);
insert、delete、update操作考虑sql语句的重复使用的,直接使用SQLiteDatabase#execSQL(String sql);如果需要考虑sql语句的重复使用的,那么可以选择SQLiteStatement。用法如下:
db.beginTransaction();
SQLiteStatement stmt = null;
try {
stmt = db.compileStatement("UPDATE global SET value = ? "
+ " WHERE name = ? AND value = ?");
stmt.bindString(1, getDefaultDeviceName()); // new default device name
stmt.bindString(2, Settings.Global.DEVICE_NAME);
stmt.bindString(3, getOldDefaultDeviceName()); // old default device name
stmt.execute();
db.setTransactionSuccessful();
} finally {
db.endTransaction();
if (stmt != null) stmt.close();
}
db.beginTransaction();
SQLiteStatement stmt = null;
try {
stmt = db.compileStatement("INSERT OR REPLACE INTO global(name,value)"
+ " VALUES(?,?);");
loadSetting(stmt, Settings.Global.MODE_RINGER, AudioManager.RINGER_MODE_NORMAL);
db.setTransactionSuccessful();
} finally {
db.endTransaction();
if (stmt != null) stmt.close();
}
private void loadSetting(SQLiteStatement stmt, String key, Object value) {
stmt.bindString(1, key);
stmt.bindString(2, value.toString());
stmt.execute();
}
SQLiteStatement的bindString(index,value)和bindLong(index,value)会代替compileStatement中的问号。在使用完SQLiteStatement时要close();注意在bindString时,本来文本类型作为值时需要加单引号''或者双引号“”,而使用bindString时是不用加的,具体看上面的例子。
/**
* Compiles an SQL statement into a reusable pre-compiled statement object.
* The parameters are identical to {@link #execSQL(String)}. You may put ?s in the
* statement and fill in those values with {@link SQLiteProgram#bindString}
* and {@link SQLiteProgram#bindLong} each time you want to run the
* statement. Statements may not return result sets larger than 1x1.
*<p>
* No two threads should be using the same {@link SQLiteStatement} at the same time.
*
* @param sql The raw SQL statement, may contain ? for unknown values to be
* bound later.
* @return A pre-compiled {@link SQLiteStatement} object. Note that
* {@link SQLiteStatement}s are not synchronized, see the documentation for more details.
*/
public SQLiteStatement compileStatement(String sql) throws SQLException
如果不使用SQLiteDatabase#execSQL也可以使用SQLiteDatabase的insert、delete、update方法:
public long insert(String table, String nullColumnHack, ContentValues values);
public int delete(String table, String whereClause, String[] whereArgs);
public int update(String table, ContentValues values, String whereClause, String[] whereArgs);
如果是直接操作sql语句的,如使用execSQL和SQLiteStatement,都要使用事务控制,这样可以保证操作的原子性。在执行一组sql语句前后加上:
db.beginTransaction();
try {
//执行一组sql,即执行多句sql语句。
db.setTransactionSuccessful();//设置执行成功标志
} finally {
db.endTransaction();//判断是否成功,是就commit,否则回滚所有操作
}
如果Cursor#getLong(columnIndex)访问为null数据时,返回0;如果是访问的是其它类型的数据,则强制转换为整型,除了Blob类型,该方法访问BLOB类型数据的话,将报SQLiteException。
primary key
1) 一个表中只能有一个主键。如果在其他字段上建立主键,则原来的主键就会取消。在ACCESS中,虽然主键不是必需的,但最好为每个表都设置一个主键。
2)主键的值不可重复,也不可为空(NULL)。
数据库操作及SQLiteDatabase框架的使用可以参考SettingsProvider和MediaProvider