can we use truncate query in android sqlite
can we use truncate query in android database? otherwise 开发者_开发百科can we performing truncate operation using our java code?
If I have to truncate a table, I simply drop and recreate it.
Documentation ( http://www.sqlite.org/lang_delete.html ):
When the WHERE is omitted from a DELETE statement and the table being deleted has no triggers, SQLite uses an optimization to erase the entire table content without having to visit each row of the table individually. This "truncate" optimization makes the delete run much faster.
DELETE FROM tablename
VACUUM
Ex:
db.execSQL("DELETE FROM " + TABLE_NAME);
db.execSQL("VACUUM");
Note: this won't reset the row numbering for rows using AUTOINCREMENT. For that you'll need to drop the table and recreate it.
source: http://phpcode.mypapit.net/how-to-truncate-table-in-sqlite-database/49/
This is the code I used, just for illustration:
private static final String TABLE_SCHEMA = "CREATE TABLE " + TABLE_NAME + " (\"id\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , \"runtime\" INTEGER, \"timestamp\" DATETIME DEFAULT CURRENT_TIMESTAMP)";
db.execSQL("DROP TABLE " + TABLE_NAME);
db.execSQL(TABLE_SCHEMA);
As WarrenFaith pointed out, I am just giving the code for the logic as in Dec-2019
In Your Activity/Fragment
// Delete The Temp Table
public static void clearSQLiteTempImagesTable(Context context) {
// SQLite Variable
SQLiteDatabase mDatabase;
SQLiteHelper dbHelper = new SQLiteHelper(context);
mDatabase = dbHelper.getWritableDatabase();
mDatabase.beginTransaction();
try {
mDatabase.execSQL("DROP TABLE IF EXISTS "+TempImagesContract.tempImagesTable.TABLE_NAME);
TempImagesContract.createTempImagesTable(mDatabase);
mDatabase.setTransactionSuccessful();
} catch (Exception e) {
e.printStackTrace();
} finally {
mDatabase.endTransaction();
mDatabase.close();
}
}
SQLite Helper
public class TempImagesContract {
public TempImagesContract() {
}
// Create Table
public static void createTempImagesTable(SQLiteDatabase db) {
final String SQL_CREATE_TEMP_IMAGES_TABLE = "CREATE TABLE " +
tempImagesTable.TABLE_NAME + " (" +
tempImagesTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
tempImagesTable.COLUMN_IMAGE_URL + " TEXT NOT NULL " +
");";
db.execSQL(SQL_CREATE_TEMP_IMAGES_TABLE);
}
// Define Table
public static final class tempImagesTable
implements BaseColumns {
public static final String TABLE_NAME = "temp_images";
public static final String COLUMN_IMAGE_URL = "image_url";
}
}
精彩评论