Java Code Examples for android.database.sqlite.SQLiteDatabase#beginTransaction()

@Override
public void transactionAround(boolean readOnly, CallBack callback)
{
	SQLiteDatabase sqLiteDatabase = null;
	if (readOnly)
	{
		sqLiteDatabase = this.getReadableDatabase();
	}
	else
	{
		sqLiteDatabase = this.getWritableDatabase();
	}
	sqLiteDatabase.beginTransaction();
	callback.doSomething(sqLiteDatabase);
	sqLiteDatabase.setTransactionSuccessful();
	sqLiteDatabase.endTransaction();
}
 

public int udpate(Task task) {
	if (task == null || task.getTaskId() == null) {
		return -1;
	}
	
	SQLiteDatabase sqLiteDatabase = dbHelper.getWritableDatabase();
	sqLiteDatabase.beginTransaction();

	int rowsAffected = 0;
	try {
		ContentValues values = new ContentValues();			

		values.put("Result_ID", task.getResultId());
		if (task.getFinishedAt() != null) {
		    values.put("Finished_At", task.getFinishedAt().getTime());
		}
		values.put("State", task.getState());
		
		rowsAffected = sqLiteDatabase.update(TABLE, values, "Task_ID = " + task.getTaskId(), null);
	} finally {
		sqLiteDatabase.endTransaction();
	}

	return rowsAffected;
}
 

/**
 * 批量插入，数据越多越好用；最好多于100条
 *
 * @param table
 * @param list
 * @return
 */
public long transactInsert(String table, List<ContentValues> list) {
    if (dbHelp == null) {
        Ln.i("create db fail", "lost permission--->android.permission.WRITE_EXTERNAL_STORAGE");
        return -1;
    }

    SQLiteDatabase db = dbHelp.getWritableDatabase();
    db.beginTransaction();
    long rowId = -1;
    try {
        for (int i = 0; i < list.size(); i++) {
            rowId = db.insert(table, null, list.get(i));
        }
        db.setTransactionSuccessful();
    } catch (Exception e) {
        Ln.e("when insert database occur error table:" + table, e.getMessage());
    } finally {
        db.endTransaction();
    }

    return rowId;
}
 

public void updateSyncState(ArrayList arraylist, int i1, int j1, int k1)
{
    if (arraylist == null || arraylist.size() < 1)
    {
        return;
    }
    SQLiteDatabase sqlitedatabase = getWritableDatabase();
    sqlitedatabase.beginTransaction();
    String s;
    for (Iterator iterator = arraylist.iterator(); iterator.hasNext(); sqlitedatabase.execSQL(s))
    {
        UploadData uploaddata = (UploadData)iterator.next();
        s = (new StringBuilder()).append("update date_data set sync = ").append(i1).append(" where ").append("date").append(" = ").append("'").append(uploaddata.date).append("'").append(" and ").append("type").append(" = ").append(j1).append(" and ").append("source").append(" = ").append(k1).append(";").toString();
        Debug.i("WPJ", (new StringBuilder()).append("update sync state:").append(s).toString());
    }

    sqlitedatabase.setTransactionSuccessful();
    sqlitedatabase.endTransaction();
}
 

public void update(String url, List<ThreadData> threadDataList) {
//        LogUtils.d(TAG, "update: url = " + url + ", threadDataList = " + threadDataList.toString());
        SQLiteDatabase database = openDatabase();
        database.beginTransaction();
        try {
            for (ThreadData data : threadDataList) {
                database.execSQL("update " + DBOpenHelper.TABLE_NAME + " set "
                                + DBOpenHelper.FIELD_DOWNLOAD_LENGTH + "=?, "
                                + DBOpenHelper.FIELD_FILE_SIZE + "=? where "
                                + DBOpenHelper.FIELD_THREAD_ID + "=? and "
                                + DBOpenHelper.FIELD_URL + "=?",
                        new String[]{"" + data.getDownloadLength(),
                                "" + data.getFileSize(),
                                "" + data.getThreadId(),
                                url});
            }
            database.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            database.endTransaction();
        }
        database.close();
    }
 

public void destroyPassport() {
	SQLiteDatabase sqLiteDatabase = dbHelper.getWritableDatabase();
	sqLiteDatabase.beginTransaction();
	try {
		put(sqLiteDatabase, Constants.PASSPORT_EMAIL,
				null, "通行证邮箱");
		put(sqLiteDatabase, Constants.PASSPORT_USERNAME,
				null, "通行证用户名");
		put(sqLiteDatabase, Constants.PASSPORT_TOKEN,
				null, "通行证认证Token");
		put(sqLiteDatabase, Constants.PASSPORT_STATE,
				null, "通行证状态");
		put(sqLiteDatabase, Constants.PASSPORT_VIP,
				null, "通行证是否VIP");
		put(sqLiteDatabase, Constants.PASSPORT_POINTS,
				0, "通行证积分");
		put(sqLiteDatabase, Constants.PASSPORT_TITLE,
				null, "通行证头衔");
		sqLiteDatabase.setTransactionSuccessful();
	} finally {
		sqLiteDatabase.endTransaction();
	}
}
 

@Override
public int bulkInsert(Uri uri, @NonNull ContentValues[] values) {
    SQLiteDatabase db = helper.getWritableDatabase();
    db.beginTransaction();
    int count = 0;
    try {
        SQLiteStatement insertStmt = db.compileStatement(
                "INSERT INTO " + BufferScheme.TABLE_NAME + " (" +
                        BufferScheme.COLUMN_CLASS + ", " +
                        BufferScheme.COLUMN_BODY + ") VALUES (?, ?);");
        for (ContentValues value : values) {
            insertStmt.bindString(1, value.getAsString("name"));
            long id = insertStmt.executeInsert();
            if (id != -1) {
                count++;
            }
        }
        db.setTransactionSuccessful();
    } finally {
        Utils.endTransaction(db);
    }
    return count;
}
 

public static List<TreeholeLocalInfo> getAll() {
    List<TreeholeLocalInfo> result = new ArrayList<>();
    SQLiteDatabase db = DatabaseHelper.getInstance().getReadableDatabase();
    db.beginTransaction();
    Cursor cursor = db.query("treehole", null, null, null, null, null, null);
    if (cursor.moveToFirst()) {
        do {
            TreeholeLocalInfo comment = new TreeholeLocalInfo();
            comment.setDatetime(cursor.getString(cursor.getColumnIndex(keys[0])));
            comment.setState(cursor.getString(cursor.getColumnIndex(keys[1])));
            comment.setPicAddress(cursor.getString(cursor.getColumnIndex(keys[2])));
            comment.setUniquecode(cursor.getString(cursor.getColumnIndex(keys[3])));
            comment.setLike(TRUE == cursor.getInt(cursor.getColumnIndex(keys[4])));
            comment.setUnlike(TRUE == cursor.getInt(cursor.getColumnIndex(keys[5])));
            result.add(comment);
        } while (cursor.moveToNext());
    }
    cursor.close();
    db.setTransactionSuccessful();
    db.endTransaction();
    return result;
}
 

@Override
public void cache() {
	SQLiteDatabase db = mHelper.getWritableDatabase();
	db.beginTransaction();
	db.execSQL(Constants.SQL_DROP_TABLE + MentionsTimeLineTable.NAME);
	db.execSQL(MentionsTimeLineTable.CREATE);

	ContentValues values = new ContentValues();
	values.put(MentionsTimeLineTable.ID, 1);
	values.put(MentionsTimeLineTable.JSON, new Gson().toJson(mMessages));

	db.insert(MentionsTimeLineTable.NAME, null, values);

	db.setTransactionSuccessful();
	db.endTransaction();
}
 

public static void save(List<TreeholeInfo> infos) {
    SQLiteDatabase db = DatabaseHelper.getInstance().getWritableDatabase();
    db.beginTransaction();
    for (TreeholeInfo info : infos) {
        ContentValues values = new ContentValues();
        values.put(keys[0], info.getDatetime());
        values.put(keys[1], info.getState());
        values.put(keys[2], info.getPicAddress());
        values.put(keys[3], info.getUniquecode());
        values.put(keys[4], FALSE);
        values.put(keys[5], FALSE);
        db.insert("treehole", null, values);
    }
    db.setTransactionSuccessful();
    db.endTransaction();
}
 

@Override
public void onCreate(SQLiteDatabase db) {
    db.beginTransaction();
    try {
        db.execSQL(DbConstants.CREATE_IMAGE_SDCARD_CACHE_TABLE_SQL.toString());
        db.execSQL(DbConstants.CREATE_IMAGE_SDCARD_CACHE_TABLE_INDEX_SQL.toString());

        db.execSQL(DbConstants.CREATE_HTTP_CACHE_TABLE_SQL.toString());
        db.execSQL(DbConstants.CREATE_HTTP_CACHE_TABLE_INDEX_SQL.toString());
        db.execSQL(DbConstants.CREATE_HTTP_CACHE_TABLE_UNIQUE_INDEX.toString());
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }
}
 

public void insertOrUpdateNewTask(String taskId, String url, int status, int progress, String fileName,
                                   String savedDir, String headers, boolean showNotification, boolean openFileFromNotification) {
    SQLiteDatabase db = dbHelper.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(TaskContract.TaskEntry.COLUMN_NAME_TASK_ID, taskId);
    values.put(TaskContract.TaskEntry.COLUMN_NAME_URL, url);
    values.put(TaskContract.TaskEntry.COLUMN_NAME_STATUS, status);
    values.put(TaskContract.TaskEntry.COLUMN_NAME_PROGRESS, progress);
    values.put(TaskContract.TaskEntry.COLUMN_NAME_FILE_NAME, fileName);
    values.put(TaskContract.TaskEntry.COLUMN_NAME_SAVED_DIR, savedDir);
    values.put(TaskContract.TaskEntry.COLUMN_NAME_HEADERS, headers);
    values.put(TaskContract.TaskEntry.COLUMN_NAME_MIME_TYPE, "unknown");
    values.put(TaskContract.TaskEntry.COLUMN_NAME_SHOW_NOTIFICATION, showNotification ? 1 : 0);
    values.put(TaskContract.TaskEntry.COLUMN_NAME_OPEN_FILE_FROM_NOTIFICATION, openFileFromNotification ? 1 : 0);
    values.put(TaskContract.TaskEntry.COLUMN_NAME_RESUMABLE, 0);
    values.put(TaskContract.TaskEntry.COLUMN_NAME_TIME_CREATED, System.currentTimeMillis());

    db.beginTransaction();
    try {
        db.insertWithOnConflict(TaskContract.TaskEntry.TABLE_NAME, null, values, SQLiteDatabase.CONFLICT_REPLACE);
        db.setTransactionSuccessful();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        db.endTransaction();
    }
}
 

@Override
public ContentProviderResult[] applyBatch(final ArrayList<ContentProviderOperation> operations) throws OperationApplicationException {
	final SQLiteDatabase database = getDatabase();
	database.beginTransaction();
	try {
		final ContentProviderResult[] results = super.applyBatch(operations);
		database.setTransactionSuccessful();
		return results;
	} finally {
		database.endTransaction();
	}
}
 

private static void upgradeFeatureTable(SQLiteDatabase db) {
    logger.error("  Upgrade feature table");
    SQLiteStatement statement = db.compileStatement("UPDATE " + TABLE_FEATURES + " SET " +
            COLUMN_FEATURES_X + " = ?, " + COLUMN_FEATURES_Y + " = ? WHERE " +
            COLUMN_FEATURES_ID + " = ?");
    String SQL_SELECT_FEATURES = "SELECT " + COLUMN_FEATURES_ID + ", " + COLUMN_FEATURES_LAT +
            ", " + COLUMN_FEATURES_LON + " FROM " + TABLE_FEATURES;
    db.beginTransaction();
    Cursor cursor = db.rawQuery(SQL_SELECT_FEATURES, null);
    cursor.moveToFirst();
    int[] xy = new int[] {0, 0};
    while (!cursor.isAfterLast()) {
        long id = cursor.getLong(0);
        double lat = cursor.getDouble(1);
        double lon = cursor.getDouble(2);
        cursor.moveToNext();
        if (lat == 0 && lon == 0)
            continue;

        getFourteenthTileXY(lat, lon, xy);
        statement.bindLong(1, xy[0]);
        statement.bindLong(2, xy[1]);
        statement.bindLong(3, id);
        statement.execute();
    }
    cursor.close();
    db.setTransactionSuccessful();
    db.endTransaction();
}
 

@Override
public void onCreate(SQLiteDatabase db) {
    db.beginTransaction();
    try {
        db.execSQL(SQL_CREATE_TABLE);
        db.execSQL(SQL_CREATE_UNIQUE_INDEX);
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }
}
 

/**
 * This updates all the results for the getTopPlayedResults so that we can get an
 * accurate list of the top played results
 */
private synchronized void updateResults() {
    if (mDatabaseUpdated) {
        return;
    }

    final SQLiteDatabase database = getWritableDatabase();

    database.beginTransaction();

    int oldestWeekWeCareAbout = mNumberOfWeeksSinceEpoch - NUM_WEEKS + 1;
    // delete rows we don't care about anymore
    database.delete(SongPlayCountColumns.NAME, SongPlayCountColumns.LAST_UPDATED_WEEK_INDEX
            + " < " + oldestWeekWeCareAbout, null);

    // get the remaining rows
    Cursor cursor = database.query(SongPlayCountColumns.NAME,
            new String[]{SongPlayCountColumns.ID},
            null, null, null, null, null);

    if (cursor != null && cursor.moveToFirst()) {
        // for each row, update it
        do {
            updateExistingRow(database, cursor.getLong(0), false);
        } while (cursor.moveToNext());

        cursor.close();
    }

    mDatabaseUpdated = true;
    database.setTransactionSuccessful();
    database.endTransaction();
}
 

protected long add(String table, ContentValues values) {
    SQLiteDatabase db = mDatabaseHelper.getWritableDatabase();
    db.beginTransaction();
    long key = db.insert(table, null, values);
    db.setTransactionSuccessful();
    db.endTransaction();

    return key;
}
 

public static void cleanUpLists(Context context, SQLiteDatabase db, Account[] accounts, String authority)
{
    // make a list of the accounts array
    List<Account> accountList = Arrays.asList(accounts);

    db.beginTransaction();

    try
    {
        Cursor c = db.query(Tables.LISTS, new String[] { TaskListColumns._ID, TaskListSyncColumns.ACCOUNT_NAME, TaskListSyncColumns.ACCOUNT_TYPE }, null,
                null, null, null, null);

        // build a list of all task list ids that no longer have an account
        List<Long> obsoleteLists = new ArrayList<Long>();
        try
        {
            while (c.moveToNext())
            {
                String accountType = c.getString(2);
                // mark list for removal if it is non-local and the account
                // is not in accountList
                if (!TaskContract.LOCAL_ACCOUNT_TYPE.equals(accountType))
                {
                    Account account = new Account(c.getString(1), accountType);
                    if (!accountList.contains(account))
                    {
                        obsoleteLists.add(c.getLong(0));

                        // remove syncstate for this account right away
                        db.delete(Tables.SYNCSTATE, SyncState.ACCOUNT_NAME + "=? and " + SyncState.ACCOUNT_TYPE + "=?", new String[] {
                                account.name,
                                account.type });
                    }
                }
            }
        }
        finally
        {
            c.close();
        }

        if (obsoleteLists.size() == 0)
        {
            // nothing to do here
            return;
        }

        // remove all accounts in the list
        for (Long id : obsoleteLists)
        {
            if (id != null)
            {
                db.delete(Tables.LISTS, TaskListColumns._ID + "=" + id, null);
            }
        }
        db.setTransactionSuccessful();
    }
    finally
    {
        db.endTransaction();
    }
    // notify all observers

    ContentResolver cr = context.getContentResolver();
    cr.notifyChange(TaskLists.getContentUri(authority), null);
    cr.notifyChange(Tasks.getContentUri(authority), null);
    cr.notifyChange(Instances.getContentUri(authority), null);

    Utils.sendActionProviderChangedBroadCast(context, authority);
}
 

/**
 * Recreates workspace table and migrates data to the new table.
 */
public boolean recreateWorkspaceTable(SQLiteDatabase db) {
    db.beginTransaction();
    try {
        Cursor c = db.query(TABLE_WORKSPACE_SCREENS,
                new String[] {LauncherSettings.WorkspaceScreens._ID},
                null, null, null, null,
                LauncherSettings.WorkspaceScreens.SCREEN_RANK);
        ArrayList<Long> sortedIDs = new ArrayList<Long>();
        long maxId = 0;
        try {
            while (c.moveToNext()) {
                Long id = c.getLong(0);
                if (!sortedIDs.contains(id)) {
                    sortedIDs.add(id);
                    maxId = Math.max(maxId, id);
                }
            }
        } finally {
            c.close();
        }

        db.execSQL("DROP TABLE IF EXISTS " + TABLE_WORKSPACE_SCREENS);
        addWorkspacesTable(db);

        // Add all screen ids back
        int total = sortedIDs.size();
        for (int i = 0; i < total; i++) {
            ContentValues values = new ContentValues();
            values.put(LauncherSettings.WorkspaceScreens._ID, sortedIDs.get(i));
            values.put(LauncherSettings.WorkspaceScreens.SCREEN_RANK, i);
            addModifiedTime(values);
            db.insertOrThrow(TABLE_WORKSPACE_SCREENS, null, values);
        }
        db.setTransactionSuccessful();
        mMaxScreenId = maxId;
    } catch (SQLException ex) {
        // Old version remains, which means we wipe old data
        Log.e(TAG, ex.getMessage(), ex);
        return false;
    } finally {
        db.endTransaction();
    }
    return true;
}
 

@Override
public int bulkInsert(Uri uri, ContentValues[] values) {
    final int callingUser = UserHandle.getCallingUserId();
    if (LOCAL_LOGV) Slog.v(TAG, "bulkInsert() for user " + callingUser);
    SqlArguments args = new SqlArguments(uri);
    if (TABLE_FAVORITES.equals(args.table)) {
        return 0;
    }
    checkWritePermissions(args);
    SettingsCache cache = cacheForTable(callingUser, args.table);

    final AtomicInteger mutationCount;
    synchronized (this) {
        mutationCount = sKnownMutationsInFlight.get(callingUser);
    }
    if (mutationCount != null) {
        mutationCount.incrementAndGet();
    }
    DatabaseHelper dbH = getOrEstablishDatabase(
            TABLE_GLOBAL.equals(args.table) ? UserHandle.USER_OWNER : callingUser);
    SQLiteDatabase db = dbH.getWritableDatabase();
    db.beginTransaction();
    try {
        int numValues = values.length;
        for (int i = 0; i < numValues; i++) {
            checkUserRestrictions(values[i].getAsString(Settings.Secure.NAME), callingUser);
            if (db.insert(args.table, null, values[i]) < 0) return 0;
            SettingsCache.populate(cache, values[i]);
            if (LOCAL_LOGV) Log.v(TAG, args.table + " <- " + values[i]);
        }
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
        if (mutationCount != null) {
            mutationCount.decrementAndGet();
        }
    }

    sendNotify(uri, callingUser);
    return values.length;
}