Java Code Examples for android.database.sqlite.SQLiteDatabase

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);
    }
}