android.database.sqlite.SQLiteDatabase#updateWithOnConflict ( )源码实例Demo

public void updateAlarm(String oldId,AlarmInfo alarmInfo){
    SQLiteDatabase db=mHelper.getWritableDatabase();

    ContentValues values=new ContentValues();
    values.put(ConsUtils.ALARM_HOUR,alarmInfo.getHour());
    values.put(ConsUtils.ALARM_MINUTE,alarmInfo.getMinute());
    values.put(ConsUtils.ALARM_LAZY_LEVEL,alarmInfo.getLazyLevel());
    values.put(ConsUtils.ALARM_RING,alarmInfo.getRing());
    values.put(ConsUtils.ALARM_TAG,alarmInfo.getTag());
    values.put(ConsUtils.ALARM_REPEAT_DAY, getDataDayofWeek(alarmInfo.getDayOfWeek()));
    values.put(ConsUtils.ALARM_ID,alarmInfo.getId());
    values.put(ConsUtils.ALARM_RING_ID,alarmInfo.getRingResId());
    db.updateWithOnConflict(ConsUtils.ALARM_TABLE, values, ConsUtils.ALARM_ID + " = ?", new String[]{oldId}, SQLiteDatabase.CONFLICT_IGNORE);
    Toast.makeText(mContext, "修改成功", Toast.LENGTH_SHORT).show();
    db.close();
    Log.d("alarm","update完成");
}
 

public void saveParamString(int widgetId, String paramName, String value) {
    SQLiteDatabase db = getWritableDatabase();

    try {
        String oldValue = getParamString(widgetId, paramName);

        ContentValues values = new ContentValues();
        values.put(WidgetSettingsContract.WidgetSettings.COLUMN_NAME_PARAM_STRING, value);
        if (oldValue == null) {
            values.put(WidgetSettingsContract.WidgetSettings.COLUMN_NAME_PARAM_NAME, paramName);
            values.put(WidgetSettingsContract.WidgetSettings.COLUMN_NAME_WIDGET_ID, widgetId);
            db.insert(WidgetSettingsContract.WidgetSettings.TABLE_NAME, null, values);
        } else {
            db.updateWithOnConflict(WidgetSettingsContract.WidgetSettings.TABLE_NAME,
                    values,
                    WidgetSettingsContract.WidgetSettings.COLUMN_NAME_WIDGET_ID + "=" + widgetId +
                            " AND " + WidgetSettingsContract.WidgetSettings.COLUMN_NAME_PARAM_NAME + "=\"" + paramName + "\"",
                    null,
                    SQLiteDatabase.CONFLICT_IGNORE);
        }
    } finally {
    }
}
 

public void saveStringParam(Long voiceSettingId, int paramType, String value) {
    SQLiteDatabase db = getWritableDatabase();

    try {
        ContentValues values = new ContentValues();
        values.put(VoiceSettingParameterContract.VoiceSettingParameters.COLUMN_NAME_PARAM_STRING_VALUE, value);
        if (!dbRecordExists(voiceSettingId, paramType)) {
            values.put(VoiceSettingParameterContract.VoiceSettingParameters.COLUMN_NAME_PARAM_TYPE_ID, paramType);
            values.put(VoiceSettingParameterContract.VoiceSettingParameters.COLUMN_NAME_VOICE_SETTING_ID, voiceSettingId);
            db.insert(VoiceSettingParameterContract.VoiceSettingParameters.TABLE_NAME, null, values);
        } else {
            db.updateWithOnConflict(VoiceSettingParameterContract.VoiceSettingParameters.TABLE_NAME,
                    values,
                    VoiceSettingParameterContract.VoiceSettingParameters.COLUMN_NAME_VOICE_SETTING_ID + "=" + voiceSettingId +
                            " AND " + VoiceSettingParameterContract.VoiceSettingParameters.COLUMN_NAME_PARAM_TYPE_ID + "=" + paramType,
                    null,
                    SQLiteDatabase.CONFLICT_IGNORE);
        }
    } catch (Exception e) {
        appendLog(context, TAG, "Error:", e);
    } finally {
    }
}
 

public void saveGeneralStringParam(int paramType, String value) {
    SQLiteDatabase db = getWritableDatabase();

    try {
        ContentValues values = new ContentValues();
        values.put(VoiceSettingParameterContract.VoiceSettingParameters.COLUMN_NAME_PARAM_STRING_VALUE, value);
        if (!dbRecordExists(paramType)) {
            values.put(VoiceSettingParameterContract.VoiceSettingParameters.COLUMN_NAME_PARAM_TYPE_ID, paramType);
            db.insert(VoiceSettingParameterContract.VoiceSettingParameters.TABLE_NAME, null, values);
        } else {
            db.updateWithOnConflict(VoiceSettingParameterContract.VoiceSettingParameters.TABLE_NAME,
                    values,
                    VoiceSettingParameterContract.VoiceSettingParameters.COLUMN_NAME_PARAM_TYPE_ID + "=" + paramType,
                    null,
                    SQLiteDatabase.CONFLICT_IGNORE);
        }
    } finally {
    }
}
 

public void saveLongParam(Long voiceSettingId, int paramType, long value) {
    SQLiteDatabase db = getWritableDatabase();

    try {
        ContentValues values = new ContentValues();
        values.put(VoiceSettingParameterContract.VoiceSettingParameters.COLUMN_NAME_PARAM_LONG_VALUE, value);
        if (!dbRecordExists(voiceSettingId, paramType)) {
            values.put(VoiceSettingParameterContract.VoiceSettingParameters.COLUMN_NAME_PARAM_TYPE_ID, paramType);
            values.put(VoiceSettingParameterContract.VoiceSettingParameters.COLUMN_NAME_VOICE_SETTING_ID, voiceSettingId);
            db.insert(VoiceSettingParameterContract.VoiceSettingParameters.TABLE_NAME, null, values);
        } else {
            db.updateWithOnConflict(VoiceSettingParameterContract.VoiceSettingParameters.TABLE_NAME,
                    values,
                    VoiceSettingParameterContract.VoiceSettingParameters.COLUMN_NAME_VOICE_SETTING_ID + "=" + voiceSettingId +
                    " AND " + VoiceSettingParameterContract.VoiceSettingParameters.COLUMN_NAME_PARAM_TYPE_ID + "=" + paramType,
                    null,
                    SQLiteDatabase.CONFLICT_IGNORE);
        }
    } finally {
    }
}
 

public void saveWeatherForecast(long locationId, int forecastType, long weatherUpdateTime, CompleteWeatherForecast completeWeatherForecast) {
    SQLiteDatabase db = getWritableDatabase();

    WeatherForecastRecord oldWeatherForecast = getWeatherForecast(locationId, forecastType);

    ContentValues values = new ContentValues();
    values.put(WeatherForecastContract.WeatherForecast.COLUMN_NAME_WEATHER_FORECAST,
               getCompleteWeatherForecastAsBytes(completeWeatherForecast));
    values.put(WeatherForecastContract.WeatherForecast.COLUMN_NAME_LOCATION_ID, locationId);
    values.put(WeatherForecastContract.WeatherForecast.COLUMN_NAME_LAST_UPDATED_IN_MS, weatherUpdateTime);
    values.put(WeatherForecastContract.WeatherForecast.COLUMN_NAME_FORECAST_TYPE, forecastType);
    if (oldWeatherForecast == null) {
        db.insert(WeatherForecastContract.WeatherForecast.TABLE_NAME, null, values);
    } else {
        db.updateWithOnConflict(WeatherForecastContract.WeatherForecast.TABLE_NAME,
                values,
                WeatherForecastContract.WeatherForecast.COLUMN_NAME_LOCATION_ID + "=" + locationId +
                " AND " + WeatherForecastContract.WeatherForecast.COLUMN_NAME_FORECAST_TYPE + "=" + forecastType,
                null,
                SQLiteDatabase.CONFLICT_IGNORE);
    }
}
 

public void updateToken(String requestUri, String token) {
    SQLiteDatabase db = getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(LicenseKeysContract.LicenseKeys.COLUMN_NAME_TOKEN, token);
    values.put(LicenseKeysContract.LicenseKeys.COLUMN_NAME_LAST_CALL_TIME_IN_MS, System.currentTimeMillis());
    if (!dbRecordExists(requestUri)) {
        values.put(LicenseKeysContract.LicenseKeys.COLUMN_NAME_REQUEST_URI, requestUri);
        db.insert(LicenseKeysContract.LicenseKeys.TABLE_NAME, null, values);
    } else {
        db.updateWithOnConflict(
                LicenseKeysContract.LicenseKeys.TABLE_NAME,
                values,
                LicenseKeysContract.LicenseKeys.COLUMN_NAME_REQUEST_URI + "='" + requestUri + "'",
                null,
                SQLiteDatabase.CONFLICT_IGNORE);
    }
}
 

public void updateAutoLocationAddress(final Context context, final String locale, final Address address) {
    SQLiteDatabase db = getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(LocationsContract.Locations.COLUMN_NAME_ADDRESS, getAddressAsBytes(address));
    values.put(LocationsContract.Locations.COLUMN_NAME_LOCALE, locale);
    values.put(LocationsContract.Locations.COLUMN_NAME_ADDRESS_FOUND, 1);
    values.put(LocationsContract.Locations.COLUMN_NAME_LAST_UPDATE_TIME_IN_MS, System.currentTimeMillis());
    db.updateWithOnConflict(
            LocationsContract.Locations.TABLE_NAME,values,
            LocationsContract.Locations.COLUMN_NAME_ORDER_ID +"=0",
            null,
            SQLiteDatabase.CONFLICT_IGNORE);
    SensorLocationUpdater.autolocationForSensorEventAddressFound = true;
    appendLog(context,
              TAG,
             "updateAutoLocationAddress:autolocationForSensorEventAddressFound=",
                    SensorLocationUpdater.autolocationForSensorEventAddressFound);
}
 

public void updateAutoLocationGeoLocation(final double latitude,
                                          final double longitude,
                                          final String locationSource,
                                          final float accuracy,
                                          final long locationTime) {
    appendLog(context, TAG, "updateLocationSource:entered:", latitude, ":", longitude, ":", locationSource);
    SQLiteDatabase db = getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(LocationsContract.Locations.COLUMN_NAME_LONGITUDE, longitude);
    values.put(LocationsContract.Locations.COLUMN_NAME_LATITUDE, latitude);
    values.put(LocationsContract.Locations.COLUMN_NAME_LOCATION_UPDATE_SOURCE, locationSource);
    values.put(LocationsContract.Locations.COLUMN_NAME_LOCATION_ACCURACY, accuracy);
    values.put(LocationsContract.Locations.COLUMN_NAME_LAST_UPDATE_TIME_IN_MS, locationTime);
    db.updateWithOnConflict(
            LocationsContract.Locations.TABLE_NAME,values,
            LocationsContract.Locations.COLUMN_NAME_ORDER_ID +"=0",
            null,
            SQLiteDatabase.CONFLICT_IGNORE);
}
 

public void setNoLocationFound() {
    SQLiteDatabase db = getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(LocationsContract.Locations.COLUMN_NAME_ADDRESS_FOUND, 0);
    values.put(LocationsContract.Locations.COLUMN_NAME_LAST_UPDATE_TIME_IN_MS, System.currentTimeMillis());

    db.updateWithOnConflict(
            LocationsContract.Locations.TABLE_NAME,values,
            LocationsContract.Locations.COLUMN_NAME_ORDER_ID +"=0",
            null,
            SQLiteDatabase.CONFLICT_IGNORE);
    SensorLocationUpdater.autolocationForSensorEventAddressFound = false;
    appendLog(context,
            TAG,
            "setNoLocationFound:autolocationForSensorEventAddressFound=",
                    SensorLocationUpdater.autolocationForSensorEventAddressFound);
}
 

public void updateLocationSource(final long locationId, final String locationSource) {
    Location locationToChange = getLocationById(locationId);
    if (locationToChange == null) {
        return;
    }
    String locationToChangeLocationSource = locationToChange.getLocationSource();
    if ((locationToChangeLocationSource != null) && locationToChangeLocationSource.equals(locationSource)) {
        return;
    }
    appendLog(context, TAG, "updateLocationSource:entered:", locationId, ":", locationSource);
    SQLiteDatabase db = getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(LocationsContract.Locations.COLUMN_NAME_LOCATION_UPDATE_SOURCE, locationSource);

    db.updateWithOnConflict(
            LocationsContract.Locations.TABLE_NAME,
            values,
            LocationsContract.Locations._ID + "=" + locationId,
            null,
            SQLiteDatabase.CONFLICT_IGNORE);
    appendLog(context, TAG, "updateLocationSource:updated");
}
 

public void updateLastUpdatedAndLocationSource(final long locationId,
                                               final long updateTime,
                                               final String locationSource) {
    appendLog(context, TAG, "updateLocationSource:entered:", locationId, ":", locationSource);
    SQLiteDatabase db = getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(LocationsContract.Locations.COLUMN_NAME_LOCATION_UPDATE_SOURCE, locationSource);
    values.put(LocationsContract.Locations.COLUMN_NAME_LAST_UPDATE_TIME_IN_MS, updateTime);

    db.updateWithOnConflict(
            LocationsContract.Locations.TABLE_NAME,
            values,
            LocationsContract.Locations._ID + "=" + locationId,
            null,
            SQLiteDatabase.CONFLICT_IGNORE);
    appendLog(context, TAG, "updateLocationSource:updated");
}
 

private void updateLocation(SQLiteDatabase db, Location location, Location locationInFile) {
    ContentValues values = prepareValues(location, locationInFile);
    if (values.size() == 0) {
        return;
    }
    appendLog(
            this,
            TAG,
            "update location:", location.getId());
    db.updateWithOnConflict(
            LocationsContract.Locations.TABLE_NAME,
            values,
            LocationsContract.Locations._ID +"=" + locationInFile.getId(),
            null,
            SQLiteDatabase.CONFLICT_IGNORE);
}
 

public void saveParamBoolean(int widgetId, String paramName, Boolean value) {
    SQLiteDatabase db = getWritableDatabase();

    try {
        Boolean oldValue = getParamBoolean(widgetId, paramName);

        ContentValues values = new ContentValues();
        Long valueToStore;
        if (value == null) {
            valueToStore = null;
        } else if (value) {
            valueToStore = 1l;
        } else {
            valueToStore = 0l;
        }
        values.put(WidgetSettingsContract.WidgetSettings.COLUMN_NAME_PARAM_LONG, valueToStore);
        if (oldValue == null) {
            values.put(WidgetSettingsContract.WidgetSettings.COLUMN_NAME_PARAM_NAME, paramName);
            values.put(WidgetSettingsContract.WidgetSettings.COLUMN_NAME_WIDGET_ID, widgetId);
            db.insert(WidgetSettingsContract.WidgetSettings.TABLE_NAME, null, values);
        } else {
            db.updateWithOnConflict(WidgetSettingsContract.WidgetSettings.TABLE_NAME,
                    values,
                    WidgetSettingsContract.WidgetSettings.COLUMN_NAME_WIDGET_ID + "=" + widgetId +
                            " AND " + WidgetSettingsContract.WidgetSettings.COLUMN_NAME_PARAM_NAME + "=\"" + paramName + "\"",
                    null,
                    SQLiteDatabase.CONFLICT_IGNORE);
        }
    } finally {
    }
}
 

public void sampleSQLiteDatabase(SQLiteDatabase db, String input) {
    db.beginTransaction();
    //
    db.compileStatement(input);
    //query
    db.query(false, input, null, null, null, null, null, null, null);
    db.query(false, input, null, null, null, null, null, null, null, null);
    db.query(input, null, null, null, null, null, null);
    db.query(input, null, null, null, null, null, null, null);
    //queryWithFactory
    db.queryWithFactory(null, false, input, null, null, null, null,null,null, null);
    db.queryWithFactory(null, false, input, null, null, null, null,null,null, null, null);
    //rawQueryWithFactory
    db.rawQueryWithFactory(null, input, null, null);
    db.rawQueryWithFactory(null, input, null, null, null);
    //delete
    db.delete(input, null, new String[] {"1","2"});
    db.delete(null, input, new String[] {"1","2"});
    //update
    db.update(input, null, null, null);
    //updateWithOnConflict
    db.updateWithOnConflict(input, null, null, null, SQLiteDatabase.CONFLICT_ROLLBACK);
    db.updateWithOnConflict(null, null, input, null, SQLiteDatabase.CONFLICT_ABORT);
    //execSQL
    db.execSQL(input);
    db.execSQL(input,null);

    db.endTransaction();
}
 

public void saveBooleanParam(Long voiceSettingId, int paramType, Boolean value) {
    SQLiteDatabase db = getWritableDatabase();

    try {
        ContentValues values = new ContentValues();
        Long valueToStore;
        if (value == null) {
            valueToStore = null;
        } else if (value) {
            valueToStore = 1l;
        } else {
            valueToStore = 0l;
        }
        values.put(VoiceSettingParameterContract.VoiceSettingParameters.COLUMN_NAME_PARAM_LONG_VALUE, valueToStore);
        if (!dbRecordExists(voiceSettingId, paramType)) {
            values.put(VoiceSettingParameterContract.VoiceSettingParameters.COLUMN_NAME_PARAM_TYPE_ID, paramType);
            values.put(VoiceSettingParameterContract.VoiceSettingParameters.COLUMN_NAME_VOICE_SETTING_ID, voiceSettingId);
            db.insert(VoiceSettingParameterContract.VoiceSettingParameters.TABLE_NAME, null, values);
        } else {
            db.updateWithOnConflict(VoiceSettingParameterContract.VoiceSettingParameters.TABLE_NAME,
                    values,
                    VoiceSettingParameterContract.VoiceSettingParameters.COLUMN_NAME_VOICE_SETTING_ID + "=" + voiceSettingId +
                            " AND " + VoiceSettingParameterContract.VoiceSettingParameters.COLUMN_NAME_PARAM_TYPE_ID + "=" + paramType,
                    null,
                    SQLiteDatabase.CONFLICT_IGNORE);
        }
    } finally {
    }
}
 

public void updateNickname(int locationOrderId, String locationNickname) {
    SQLiteDatabase db = getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(LocationsContract.Locations.COLUMN_NAME_LOCATION_NICKNAME, locationNickname);
    db.updateWithOnConflict(
            LocationsContract.Locations.TABLE_NAME,
            values,
            LocationsContract.Locations.COLUMN_NAME_ORDER_ID +"=" + locationOrderId,
            null,
            SQLiteDatabase.CONFLICT_IGNORE);
}
 

public void updateLocale(final long locationId, final String locale) {
    SQLiteDatabase db = getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(LocationsContract.Locations.COLUMN_NAME_LOCALE, locale);
    db.updateWithOnConflict(
            LocationsContract.Locations.TABLE_NAME,values,
            LocationsContract.Locations._ID +"=" + locationId,
            null,
            SQLiteDatabase.CONFLICT_IGNORE);
}
 

public void updateEnabled(long locationId, boolean enabled) {
    SQLiteDatabase db = getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(LocationsContract.Locations.COLUMN_NAME_ENABLED, enabled);

    db.updateWithOnConflict(
            LocationsContract.Locations.TABLE_NAME, values,
            LocationsContract.Locations._ID + "=" + locationId,
            null,
            SQLiteDatabase.CONFLICT_IGNORE);
}
 

public void deleteRecordFromTable(Location location) {
    int deletedOrderId = location.getOrderId();
    SQLiteDatabase db = getWritableDatabase();
    String selection = LocationsContract.Locations._ID + " = ?";
    String[] selectionArgs = {location.getId().toString()};
    db.delete(LocationsContract.Locations.TABLE_NAME, selection, selectionArgs);

    String[] projection = {
            LocationsContract.Locations._ID,
            LocationsContract.Locations.COLUMN_NAME_ORDER_ID
    };

    String sortOrder = LocationsContract.Locations.COLUMN_NAME_ORDER_ID;

    Cursor cursor = null;
    try {
        cursor = db.query(
            LocationsContract.Locations.TABLE_NAME,
            projection,
            LocationsContract.Locations.COLUMN_NAME_ORDER_ID + ">" + deletedOrderId,
            null,
            null,
            null,
            sortOrder
        );

        while (cursor.moveToNext()) {
            long itemId = cursor.getInt(cursor.getColumnIndexOrThrow(LocationsContract.Locations._ID));
            int orderId = cursor.getInt(cursor.getColumnIndexOrThrow(LocationsContract.Locations.COLUMN_NAME_ORDER_ID));
            ContentValues values = new ContentValues();
            values.put(LocationsContract.Locations.COLUMN_NAME_ORDER_ID, orderId - 1);
            db.updateWithOnConflict(
                    LocationsContract.Locations.TABLE_NAME,
                    values,
                    LocationsContract.Locations._ID +"=" + itemId,
                    null,
                    SQLiteDatabase.CONFLICT_IGNORE);
        }
    } finally {
        if (cursor != null) {
            cursor.close();
        }
    }
}