下面列出了怎么用android.database.sqlite.SQLiteDatabase的API类实例代码及写法,或者点击链接到github查看源代码。
private DownloadsDB(Context paramContext) {
this.mHelper = new DownloadsContentDBHelper(paramContext);
final SQLiteDatabase sqldb = mHelper.getReadableDatabase();
// Query for the version code, the row ID of the metadata (for future
// updating) the status and the flags
Cursor cur = sqldb.rawQuery("SELECT " +
MetadataColumns.APKVERSION + "," +
BaseColumns._ID + "," +
MetadataColumns.DOWNLOAD_STATUS + "," +
MetadataColumns.FLAGS +
" FROM "
+ MetadataColumns.TABLE_NAME + " LIMIT 1", null);
if (null != cur && cur.moveToFirst()) {
mVersionCode = cur.getInt(0);
mMetadataRowID = cur.getLong(1);
mStatus = cur.getInt(2);
mFlags = cur.getInt(3);
cur.close();
}
mDownloadsDB = this;
}
/**
* SQLite数据库中一个特殊的名叫 SQLITE_MASTER 上执行一个SELECT查询以获得所有表的索引。每一个 SQLite 数据库都有一个叫 SQLITE_MASTER 的表, 它定义数据库的模式。
* SQLITE_MASTER 表看起来如下:
* CREATE TABLE sqlite_master (
* type TEXT,
* name TEXT,
* tbl_name TEXT,
* rootpage INTEGER,
* sql TEXT
* );
* 对于表来说,type 字段永远是 ‘table’,name 字段永远是表的名字。
*/
public static boolean isTableExists(SQLiteDatabase db, String tableName) {
if (tableName == null || db == null || !db.isOpen()) return false;
Cursor cursor = null;
int count = 0;
try {
cursor = db.rawQuery("SELECT COUNT(*) FROM sqlite_master WHERE type = ? AND name = ?", new String[]{"table", tableName});
if (!cursor.moveToFirst()) {
return false;
}
count = cursor.getInt(0);
} catch (Exception e) {
OkLogger.printStackTrace(e);
} finally {
if (cursor != null) cursor.close();
}
return count > 0;
}
private boolean isPendingCopy(File dbPath) {
boolean result = false;
if (dbPath.exists()) {
SQLiteDatabase db =
context.openOrCreateDatabase(spaceHelper.getDatabaseName(), Context.MODE_PRIVATE, null);
try {
if (spaceHelper.getDatabaseVersion() > db.getVersion()) {
result = true;
}
} finally {
db.close();
}
} else {
result = true;
}
return result;
}
public DownloadInfo[] getDownloads() {
final SQLiteDatabase sqldb = mHelper.getReadableDatabase();
Cursor cur = null;
try {
cur = sqldb.query(DownloadColumns.TABLE_NAME, DC_PROJECTION, null,
null, null, null, null);
if (null != cur && cur.moveToFirst()) {
DownloadInfo[] retInfos = new DownloadInfo[cur.getCount()];
int idx = 0;
do {
DownloadInfo di = getDownloadInfoFromCursor(cur);
retInfos[idx++] = di;
} while (cur.moveToNext());
return retInfos;
}
return null;
} finally {
if (null != cur) {
cur.close();
}
}
}
/**
* 条件查寻数据
*/
<T extends DbEntity> List<T> findData(SQLiteDatabase db, Class<T> clazz, String... expression) {
db = checkDb(db);
if (!CommonUtil.checkSqlExpression(expression)) {
return null;
}
String sql = String.format("SELECT rowid, * FROM %s WHERE %s", CommonUtil.getClassName(clazz),
expression[0]);
String[] params = new String[expression.length - 1];
try {
// 处理系统出现的问题:https://github.com/AriaLyy/Aria/issues/450
System.arraycopy(expression, 1, params, 0, params.length);
} catch (Exception e) {
e.printStackTrace();
return null;
}
return exeNormalDataSql(db, clazz, sql, params);
}
public HistoryItem buildHistoryItem(int number) {
SQLiteOpenHelper helper = new DBHelper(activity);
SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = helper.getReadableDatabase();
cursor = db.query(DBHelper.TABLE_NAME, COLUMNS, null, null, null, null, DBHelper.TIMESTAMP_COL + " DESC");
cursor.move(number + 1);
String text = cursor.getString(0);
String display = cursor.getString(1);
String format = cursor.getString(2);
long timestamp = cursor.getLong(3);
String details = cursor.getString(4);
Result result = new Result(text, null, null, BarcodeFormat.valueOf(format), timestamp);
return new HistoryItem(result, display, details);
} finally {
close(cursor, db);
}
}
private synchronized List<String> executeSQL(final String query) {
final List<String> results = new ArrayList<>();
final SQLiteDatabase db = Cache.openDatabase();
final boolean transaction = !query.equals("vacuum");
if (transaction) db.beginTransaction();
try {
final Cursor cursor = db.rawQuery(query, null);
Log.d(TAG, "Got query results: " + query + " " + cursor.getCount());
while (cursor.moveToNext()) {
for (int c = 0; c < cursor.getColumnCount(); c++) {
if (D) Log.d(TAG, "Column: " + cursor.getColumnName(c));
results.add(cursor.getString(c));
}
}
cursor.close();
if (transaction) db.setTransactionSuccessful();
} finally {
if (transaction) db.endTransaction();
}
return results;
}
public static BookModel getBookModel(SQLiteDatabase db, int id) {
BookModel book = null;
Cursor cursor = helper.rawQuery(db, "select * from " + DBHelper.TABLE_NOVEL_BOOK
+ " where " + DBHelper.COLUMN_ID + " = ? ", new String[] {"" + id});
try {
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
book = cursorToBookModel(cursor);
//Log.d(TAG, "Found: " + book.getPage() + Constants.NOVEL_BOOK_DIVIDER + book.getTitle());
break;
}
} finally{
if(cursor != null) cursor.close();
}
return book;
}
@Override
public void beforeInsert(SQLiteDatabase db, ListAdapter list, boolean isSyncAdapter)
{
if (!isSyncAdapter)
{
throw new UnsupportedOperationException("Caller must be a sync adapter to create task lists");
}
if (TextUtils.isEmpty(list.valueOf(ListAdapter.ACCOUNT_NAME)))
{
throw new IllegalArgumentException("ACCOUNT_NAME is required on INSERT");
}
if (TextUtils.isEmpty(list.valueOf(ListAdapter.ACCOUNT_TYPE)))
{
throw new IllegalArgumentException("ACCOUNT_TYPE is required on INSERT");
}
verifyCommon(list, isSyncAdapter);
}
public void onCreate(final SQLiteDatabase db) {
// create the play count table
// WARNING: If you change the order of these columns
// please update getColumnIndexForWeek
StringBuilder builder = new StringBuilder();
builder.append("CREATE TABLE IF NOT EXISTS ");
builder.append(SongPlayCountColumns.NAME);
builder.append("(");
builder.append(SongPlayCountColumns.ID);
builder.append(" INT UNIQUE,");
for (int i = 0; i < NUM_WEEKS; i++) {
builder.append(getColumnNameForWeek(i));
builder.append(" INT DEFAULT 0,");
}
builder.append(SongPlayCountColumns.LAST_UPDATED_WEEK_INDEX);
builder.append(" INT NOT NULL,");
builder.append(SongPlayCountColumns.PLAYCOUNTSCORE);
builder.append(" REAL DEFAULT 0);");
db.execSQL(builder.toString());
}
@Override
public Uri insert(Uri uri, ContentValues values) {
final int match = sUriMatcher.match(uri);
SQLiteDatabase db = mDatabaseHelper.getWritableDatabase();
switch (match) {
case ENTRIES: {
db.insertOrThrow(Tables.ENTRIES, null, values);
notifyChange(uri);
return Entries.buildEntryUri(values.getAsString(Entries.UUID));
}
default: {
throw new UnsupportedOperationException("Unknown insert uri: " + uri);
}
}
}
public void addAllQuestions(ArrayList<Questions> allQuestions) {
SQLiteDatabase db = this.getWritableDatabase();
db.beginTransaction();
try {
ContentValues values = new ContentValues();
for (Questions question : allQuestions) {
values.put(QUESTION, question.getQUESTION());
values.put(ANSWER, question.getANSWER());
values.put(ANSWER2, question.getANSWER2());
values.put(RANDOMANS1, question.getRANDOMANS1());
values.put(RANDOMANS2, question.getRANDOMANS2());
values.put(USELESSSTRING, question.getUSELESSSTRING());
db.insert(TABLE_NAME, null, values);
}
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
public Cursor getPosts(String[] columns, String selection, String[] selectionArgs, String sortOrder) {
// Create and/or open the database for writing
SQLiteDatabase db = getReadableDatabase();
// It's a good idea to wrap our insert in a transaction. This helps with performance and ensures
// consistency of the database.
return db.query(PredatorContract.PostsEntry.TABLE_NAME,
columns,
selection,
selectionArgs,
null,
null,
sortOrder);
}
private void lowerCaseApkHashes(SQLiteDatabase db, int oldVersion) {
if (oldVersion >= 61) {
return;
}
Utils.debugLog(TAG, "Lowercasing all APK hashes");
db.execSQL("UPDATE " + InstalledAppTable.NAME + " SET " + InstalledAppTable.Cols.HASH
+ " = lower(" + InstalledAppTable.Cols.HASH + ")");
}
/**
* Insert videos into the subscription video table.
* @param videos
*/
public void insertVideos(List<YouTubeVideo> videos) {
SQLiteDatabase db = getWritableDatabase();
for (YouTubeVideo video : videos) {
if (video.getPublishDate() != null) {
ContentValues values = createContentValues(video, video.getChannelId());
db.insert(SubscriptionsVideosTable.TABLE_NAME, null, values);
}
}
}
public Cursor getInstructionsByRecipe(Uri uri) {
SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
queryBuilder.setTables(RecipeTable.TABLE + ", " + RecipeInstructionsTable.TABLE);
queryBuilder.appendWhere(RecipeTable.ID + "='" + uri.getLastPathSegment() + "' AND " + RecipeInstructionsTable.RECIPE_ID + "=" + RecipeTable.ID + "");
String[] projection = {RecipeInstructionsTable.NUM, RecipeInstructionsTable.DESCRIPTION, RecipeInstructionsTable.PHOTO};
SQLiteDatabase db = database.getReadableDatabase();
Cursor cursor = queryBuilder.query(db, projection, null, null, null, null, null);
cursor.setNotificationUri(getContext().getContentResolver(), uri);
return cursor;
}
/**
* Sets the version of a specified instance of a specified feature.
*
* @param writableDatabase The database to update.
* @param feature The feature.
* @param instanceUid The unique identifier of the instance of the feature.
* @param version The version.
* @throws DatabaseIOException If an error occurs executing the SQL.
*/
public static void setVersion(
SQLiteDatabase writableDatabase, @Feature int feature, String instanceUid, int version)
throws DatabaseIOException {
try {
writableDatabase.execSQL(SQL_CREATE_TABLE_IF_NOT_EXISTS);
ContentValues values = new ContentValues();
values.put(COLUMN_FEATURE, feature);
values.put(COLUMN_INSTANCE_UID, instanceUid);
values.put(COLUMN_VERSION, version);
writableDatabase.replaceOrThrow(TABLE_NAME, /* nullColumnHack= */ null, values);
} catch (SQLException e) {
throw new DatabaseIOException(e);
}
}
public static boolean hasComponent(Context context, String component, String categoryName) {
SQLiteDatabase db = getDatabase(context);
Cursor cursor = db.rawQuery("SELECT component FROM apps WHERE component = '"+ component+ "' AND categories LIKE '%@" + categoryName+ "@%'", null);
boolean result = cursor.getCount() != 0;
cursor.close();
close();
return result;
}
@Override
public int update(@NonNull Uri uri, ContentValues values, String selection,
String[] selectionArgs) {
SQLiteDatabase db = getDatabase();
switch (sURIMatcher.match(uri)) {
case APPLICATION :
return db.update(TABLENAME, values, selection, selectionArgs);
default:
throw new IllegalArgumentException("Unknown uri: " + uri);
}
}
private void recreateDatabase(SQLiteDatabase db) {
DatabaseIndexUtil.dropIndexes(db);
db.execSQL("DROP TABLE IF EXISTS " + table_notes);
db.execSQL("DROP TABLE IF EXISTS " + table_accounts);
db.execSQL("DROP TABLE IF EXISTS " + table_category);
onCreate(db);
}
@Override
public boolean onOptionsItemSelected(MenuItem item) {
int id = item.getItemId();
final DBHelper db = new DBHelper(getApplicationContext());
switch (id) {
case R.id.action_backup:
String outFileName = Environment.getExternalStorageDirectory() + File.separator + getResources().getString(R.string.app_name) + File.separator;
localBackup.performBackup(db, outFileName);
break;
case R.id.action_import:
localBackup.performRestore(db);
break;
case R.id.action_backup_Drive:
isBackup = true;
remoteBackup.connectToDrive(isBackup);
break;
case R.id.action_import_Drive:
isBackup = false;
remoteBackup.connectToDrive(isBackup);
break;
case R.id.action_delete_all:
//reinitialize the backup
SQLiteDatabase database = db.getWritableDatabase();
db.onUpgrade(database, getDatabaseVersion(), getDatabaseVersion());
TableLayout table = findViewById(R.id.table);
table.removeAllViews();
break;
default:
break;
}
return super.onOptionsItemSelected(item);
}
/**
* Returns List of sudokus to export.
*
* @param folderID Id of folder to export, -1 if all folders will be exported.
* @return
*/
public Cursor exportFolder(long folderID) {
String query = "select f._id as folder_id, f.name as folder_name, f.created as folder_created, s.created, s.state, s.time, s.last_played, s.data, s.puzzle_note, s.command_stack from folder f left outer join sudoku s on f._id = s.folder_id";
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
if (folderID != -1) {
query += " where f._id = ?";
}
return db.rawQuery(query, folderID != -1 ? new String[]{String.valueOf(folderID)} : null);
}
public Cursor getAllCursor() {
SQLiteDatabase db = this.getReadableDatabase();
String sql = "SELECT * FROM " + TABLE;
Cursor cursor = db.rawQuery(sql, null);
cursor.moveToFirst();
return cursor;
}
/**
* Execute an action on the database. The opening and closing of the database connection is handled for you.
*
* @param dbConsumer Action you want to execute on the database
*/
public void consumeDatabase(DbConsumer dbConsumer) {
SQLiteDatabase db = getDatabaseConnection();
try {
dbConsumer.consume(db);
} finally {
reduceDatabaseConnection();
}
}
public static List<City> loadCities(SQLiteDatabase db, int ProID) {
List<City> list = new ArrayList<>();
Cursor cursor = db.query("T_City", null, "ProID = ?", new String[] { String.valueOf(ProID) }, null, null, null);
if (cursor.moveToFirst()) {
do {
City city = new City();
city.mCityName = cursor.getString(cursor.getColumnIndex("CityName"));
city.mProID = ProID;
city.mCitySort = cursor.getInt(cursor.getColumnIndex("CitySort"));
list.add(city);
} while (cursor.moveToNext());
}
Util.closeQuietly(cursor);
return list;
}
public ArrayList<String> getAllTags(){
ArrayList<String> getTags = new ArrayList<String>();
SQLiteDatabase db = this.getReadableDatabase();
String query_norms = "SELECT " + ID_IMGKEY + " FROM " + TABLE_IMAGE;
Cursor cSor = db.rawQuery(query_norms, null);
if(cSor.moveToFirst()){
do{
getTags.add(cSor.getString(cSor.getColumnIndexOrThrow(SapphireImgDbHelper.ID_IMGKEY)));
}while(cSor.moveToNext());
}else{
return null;
}
return getTags;
}
private void createTable(SQLiteDatabase db) {
db.execSQL("CREATE TABLE " + TABLE + " (" +
"_id INTEGER PRIMARY KEY AUTOINCREMENT," +
COLUMN_KEY + " TEXT," +
COLUMN_USERID + " INTEGER," +
COLUMN_VALUE + " TEXT" +
");");
}
private void createHumidityTable(final SQLiteDatabase db) {
String sql = "CREATE TABLE " + TABLE_HUMIDITY + " (_id INTEGER PRIMARY KEY,"
+ HumidityColumns.VENDOR_ID + " INTEGER,"
+ HumidityColumns.EXTRA_ID + " INTEGER,"
+ HumidityColumns.TIME_STAMP + " INTEGER,"
+ HumidityColumns.HUMIDITY + " REAL"
+ ");";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
// For now simply drop the table and create a new one. This means if you change the
// DATABASE_VERSION the table will be dropped.
// In a production app, this method might be modified to ALTER the table
// instead of dropping it, so that existing data is not deleted.
sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + WaitlistEntry.TABLE_NAME);
onCreate(sqLiteDatabase);
}
@Override
public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) {
SQLiteDatabase db = null;
try {
db = helper.getWritableDatabase();
db.beginTransaction();
String newSelection = buildSelection(uri, selection);
int count = db.update(BufferScheme.TABLE_NAME, values, newSelection, selectionArgs);
getContext().getContentResolver().notifyChange(CONTENT_URI, null, false);
db.setTransactionSuccessful();
return count;
} finally {
Utils.endTransaction(db);
}
}